重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
mysql主从复制架构及实现
MySQL主从复制架构及实现
mysql主从复制配置
主节点：
创新互联是专业的广安网站建设公司，广安接单;提供成都网站建设、成都做网站,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行广安网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!
启动二进制日志
为当前节点设置一个全局唯一的ID号（server-id）
- 创建有复制权限的用户账号（replication slave，replication client）
从节点：
启动中继日志
为当前节点设置一个全局唯一的ID号
- 使用有复制权限的用户账号连接至主服务器，并启动复制线程
具体步骤
主节点（192.168.182.130）
编辑配置文件/etc/my.cnf，添加以下内容
[mysqld] log_bin=1 server_id=1 innodb_file_per_table=on- 创建有复制权限的用户账号
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.132' IDENTIFIED BY 'replpass'; MariaDB [(none)]> FLUSH PRIVILEGES;
从节点（192.168.182.132）
编辑配置文件/etc/my.cnf，添加以下内容
[mysqld] relay_log=relay_log relay_log_index=relay_log.index server_id=7 skip_name_resolve=1使用有复制权限的用户账号连接至主服务器
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000075',MASTER_LOG_POS=245;启动复制线程
MariaDB [(none)]> START SLAVE;- 查看slave的状态
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.182.130 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 245 Relay_Log_File: 1.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000075 Slave_IO_Running: Yes ##io线程启动了 Slave_SQL_Running: Yes ## sql线程启动了 Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 245 Relay_Log_Space: 809 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1
mysql主从复制中应该要注意的问题
限制从服务器为只读
限制从服务器为只读，在从服务器上设置read_only=on，想要永久有效，就写在配置文件中，但是此限制对拥有SUPER权限的用户均无效
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ MariaDB [(none)]> SET GLOBAL read_only=1;- 如果想要限制所有用户不能够进行写操作，那么启动一个连接加读锁，并且不要退出该连接
MariaDB [(none)]> FLUSH TABLE WITH READ LOCK;
如何保证主从复制的事务安全？
在master节点启用参数
sync_binlog=如果用到的是InnoDB存储引擎
innodb_flush_log_at_trx_commit innodb_support_xa- 在slave节点上，以下参数表示在从服务器启动的时候，是否自动启动复制线程，on表示不用自动启动，使用手动启动的方式
skip_slave_start=on
slave节点上的两个文件
master.info文件中保存了slave连接至master时的相关信息，例如主服务器的ip地址，复制使用的用户，密码，端口，以及当前同步的二进制日志文件和位置
- relay-log.info文件中保存了当前slave节点已经复制的二进制日志文件和位置，复制的内容保存到了哪个中继日志的哪个位置
[root@backserver data]# cat relay-log.info ./1.000002 693 mysql-bin.000075 409
复制的监控和维护：
清理日志：使用PURGE命令，清理之前确保数据已经备份过了
复制监控
MariaDB [(none)]> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 483 | | mysql-bin.000002 | 264 | | mysql-bin.000003 | 264 | | mysql-bin.000004 | 264 | | mysql-bin.000005 | 990 | | mysql-bin.000006 | 514 | | mysql-bin.000007 | 264 | | mysql-bin.000008 | 245 | | mysql-bin.000009 | 245 | +------------------+-----------+ MariaDB [(none)]> SHOW BINLOG EVENTS; +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.45-MariaDB-log, Binlog ver: 4 | | mysql-bin.000001 | 245 | Query | 1 | 315 | BEGIN | | mysql-bin.000001 | 315 | Intvar | 1 | 343 | INSERT_ID=9 | | mysql-bin.000001 | 343 | Query | 1 | 456 | use `S_SC_C`; INSERT INTO S (sname,sdept) VALUES ('xiao','MA') | | mysql-bin.000001 | 456 | Xid | 1 | 483 | COMMIT /* xid=43 */ | +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+ MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000009 | 245 | | | +------------------+----------+--------------+------------------+ MariaDB [(none)]> SHOW PROCESSLIST; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ | 5 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 | | 6 | system user | | NULL | Connect | 3850 | Waiting for master to send event | NULL | 0.000 | | 7 | system user | | NULL | Connect | 3806 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ MariaDB [(none)]> SHOW SLAVE STATUS\G从服务器是否落后与主服务器，在从服务器的salve有Seconds_Behind_Master可以查看
MariaDB [(none)]> SHOW SLAVE STATUS -> \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.182.130 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000079 Read_Master_Log_Pos: 334 Relay_Log_File: 1.000006 Relay_Log_Pos: 618 Relay_Master_Log_File: mysql-bin.000079 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: MYDB Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 334 Relay_Log_Space: 1182 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 #从服务器落后与主服务器多长时间 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1确定主从节点数据是否一致，使用一些工具，例如percona-tools
- 如果数据不一致，那么就要将从节点的所有数据删除，将主节点的数据备份一份导入到从节点上，重新复制
在主节点上有一个参数，设置为1表示每一次dump事件到从节点的时候，本地的master.info信息要立即同步到磁盘上，让从节点的master.info及时得到更新，
MariaDB [(none)]> SET GLOBAL sync_master_info=1; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'sync_master_info'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | sync_master_info | 1 | +------------------+-------+- 在从节点上有关于relay_log的参数，例如sync_relay_log_info，表示每一次写操作立即同步到磁盘文件上，还有sync_relay_log
双主模型
遇到的问题：
数据不一致，这时，我们可能就需要将数据可靠性比较高的服务器留下来，根据保留的mysql服务器重新再做一个从服务器
对于自动增长的字段，如果一个主节点是1，2，3，...，另一个主节点也是1，2，3，...，那么合并的时候就会出现问题，所以，我们可以让一个节点的自动增长的字段使用偶数id，另一个主节点使用奇数id，这样合并的时候就不会出现问题
奇数id的设置
auto_increment_offset=1 #表示从1开始 auto_increment_increment=2 #表示一次增长2个- 偶数id的设置
auto_increment_offset=2 auto_increment_increment=2
配置步骤
各节点使用一个唯一的server_id
都启动binary log和relay log
创建拥有复制权限的用户账号
- 定义自动增长id字段的数值范围为奇偶
具体步骤
其中一个节点为:
编辑配置文件
log_bin=1 server_id=1 innodb_file_per_table=on relay_log=relay-log relay_log_index=relay-log.index auto_increment_offset=1 auto_increment_increment=2创建用户账号
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.132' IDENTIFIED BY 'replpass'; MariaDB [(none)]> FLUSH PRIVILEGES;- 使用有复制权限的用户账号连接至主服务器并开启slave线程
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.182.132',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=245; MariaDB [(none)]> START SLAVE;
在另一个主节点上的操作：
编辑配置文件
[mysqld] bin_log=1 relay_log=1 relay_log_index=relay-log.index server_id=7 auto_increment_offset=2 auto_increment_increment=2创建具有复制权限的用户账号
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.130' IDENTIFIED BY 'replpass'; MariaDB [(none)]> FLUSH PRIVILEGES;- 使用具有复制权限的用户账号连接至主节点并启用slave线程
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000075',MASTER_LOG_POS=665; MariaDB [(none)]> START SLAVE;
半同步复制
需要用到一个插件，如果是rpm包安装的插件路径在/usrl/lib64/mysql/plugin，我使用二进制格式安装的插件路径是/usr/local/mysql/lib/plugin
主节点上的操作
编辑配置文件
[mysqld] log_bin=1 server_id=1 innodb_file_per_table=on plugin_dir=/usr/local/mysql/lib/plugin添加具有复制权限的用户账号
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.132' IDENTIFIED BY 'replpass'; MariaDB [(none)]> FLUSH PRIVILEGES;安装插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';查看有哪些插件
MariaDB [(none)]> SHOW PLUGINS;查看半同步相关的变量
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+rpl_semi_sync_master_enabled：为OFF表示禁用为半同步复制的主节点
rpl_semi_sync_master_timeout：表示等待从服务器应答的超时时长，默认是10s，如果超过这个时间从服务器没有给主服务器应答，那么就降级为异步方式运行，不再等待
rpl_semi_sync_master_trace_level：表示跟踪级别
rpl_semi_sync_master_wait_no_slave：表示在没有从节点的时候是否要等待，on为等待
- 启用半同步
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;
从节点上的操作：
编辑配置文件
[mysqld] relay_log=1 relay_log_index=relay-log.index server_id=7 skip_name_resolve=1 plugin_dir=/usr/local/mysql/lib/plugin/使用具有复制权限的用户账号连接至主节点
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='rpluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000078',MASTER_LOG_POS=245;安装插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';查看半同步相关的变量
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+启用半同步
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;- 启用slave线程
MariaDB [(none)]> START SLAVE;
- 以上操作就可以完成一个半同步的复制，可以在主节点上查看相关的状态变量，查看是否有半同步的slave节点
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ # Rpl_semi_sync_master_clients为0表示没有，为1表示有
复制过滤器
让从节点仅复制指定的数据库，或指定数据库的指定表
有两种实现方式:
主服务器仅向二进制日志中记录与特定数据库（特定表）相关的事件，但是可能会导致时间点还原无法实现（毕竟二进制日志记录的事件是不完全的），不建议使用
binlog_do_db #指定只记录哪些数据库的相关修改操作到二进制日志文件中，可以指定一个列表，使用逗号隔开 binlog_ignore_db #指定只忽略哪些不记录，其他都记录，可以指定一个列表，使用逗号隔开从服务器SQL_THREAD在replay中继日志中的事件时，仅读取与特定数据库（特定表）相关的事件并应用与本地；但是会造成网络及磁盘io浪费
replicate_do_db= #该处指定的数据库的相关事件都要进行复制 replicate_ignore_db= replicate_do_table= replicate_ignore_table= replicate_wild_do_table= #在指定表的时候可以使用通配符 replcate_wild_ignore_table=- 示例：复制时只复制MYDB数据库的相关事件
#在从服务器上进行的操作 MariaDB [(none)]> SET GLOBAL replicate_do_db='MYDB'; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%replicate%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | replicate_annotate_row_events | OFF | | replicate_do_db | MYDB | | replicate_do_table | | | replicate_events_marked_for_skip | replicate | | replicate_ignore_db | | | replicate_ignore_table | | | replicate_wild_do_table | | | replicate_wild_ignore_table | | +----------------------------------+-----------+ MariaDB [(none)]> SHOW SLAVE STATUS\G;
网站名称：mysql主从复制架构及实现
链接分享：http://cqcxhl.cn/article/gciiec.html