mysql主从复制
- 主从复制原理
1.从库执行change master to 命令(主库的连接信息+复制的起点) 2.从库会将以上信息,记录到master.info文件 3.从库执行 start slave 命令,立即开启IO_T和SQL_T 4. 从库 IO_T,读取master.info文件中的信息，获取到IP,PORT,User,Pass,binlog的位置信息 5. 从库IO_T请求连接主库,主库专门提供一个DUMP_T,负责和IO_T交互 6. IO_T根据binlog的位置信息(mysql-bin.000004 , 444),请求主库新的binlog 7. 主库通过DUMP_T将最新的binlog,通过网络TP给从库的IO_T 8. IO_T接收到新的binlog日志,存储到TCP/IP缓存,立即返回ACK给主库,并更新master.info 9.IO_T将TCP/IP缓存中数据,转储到磁盘relaylog中. 10. SQL_T读取relay.info中的信息,获取到上次已经应用过的relaylog的位置信息 11. SQL_T会按照上次的位置点回放最新的relaylog,再次更新relay.info信息 12. 从库会自动purge应用过relay进行定期清理 补充说明: 一旦主从复制构建成功,主库当中发生了新的变化,都会通过dump_T发送信号给IO_T,增强了主从复制的实时性.
- 搭建主从复制
1.编辑主库配置文件，添加如下内容
[root@localhost pub]# vi /etc/my.cnf [mysqld] log_bin=master-bin #开启二进制日志，并给二进制日志文件起名 server_id=1 #配置服务器节点id，全局唯一 innodb_file_per_table=ON #开启每表单独存储 skip_name_resolve=on #关闭域名反解
2.查看二进制日志开启情况
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | ON | | log_bin_trust_function_creators | OFF | | sql_log_bin | ON | +---------------------------------+-------+ 3 rows in set (0.00 sec) MariaDB [(none)]> SHOW MASTER LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 30379 | | master-bin.000002 | 1038814 | | master-bin.000003 | 245 | +-------------------+-----------+ 3 rows in set (0.00 sec)
3.查看服务id
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ 1 row in set (0.00 sec)
4.创建一个拥有复制权限的账号，并将主库锁定，不能写入
MariaDB [mysql]> GRANT REPLICATION SLAVE , REPLICATION CLIENT ON *.* TO copy@'%' IDENTIFIED BY 'copy'; MariaDB [mysql]> FLUSH PRIVILEGES ; MariaDB [mysql]> FLUSH tables with read lock;
4.1在主库中备份出所有数据，在从库上恢复
#主库备份 [root@localhost ~]# mysqldump -uroot -p 数据库名称 > /root/备份库位置 Enter password: #从库恢复 [root@localhost ~]# mysql -uroot -p 数据库名称 < 数据库备份文件 Enter password:
5.编辑从库配置文件
[root@back ~]# vi /etc/my.cnf relay_log=relay-log #开启中继日志 server_id=2 #配置服务器节点id，全局唯一 innodb_file_per_table=ON #开启每表单存储 skip_name_resolve=ON #关闭域名反解
6.查看中继日志是否开启
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%relay_log'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | innodb_recovery_update_relay_log | OFF | | relay_log | relay-log | | sync_relay_log | 0 | +----------------------------------+-----------+ 3 rows in set (0.00 sec)
7.查看server_id
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%server_id%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+ 1 row in set (0.00 sec)
8.设置主库位置及其他信息
MariaDB [hellodb]> CHANGE MASTER TO
-> MASTER_HOST='172.17.148.255' , #主库地址
-> MASTER_USER='copy' , #复制用户
-> MASTER_PASSWORD='copy' , #用户密码
-> MASTER_LOG_FILE='master-bin.000003' , #从哪个二进制文件开始复制
-> MASTER_LOG_POS=245; #复制起始位置
9.开启复制线程
MariaDB [mysql]> START SLAVE ;
10.查看SLAVE状态
MariaDB [mysql]> SHOW SLAVE STATUS \G
11.主库解锁
MariaDB [mysql]> UNLOCK TABLES;
复制架构中应该注意的问题
- 1.限制从服务器为只读
在从服务器上限制read_only=ON，此限制对拥有super权限的用户均无效（mysql可以开启super_read_only限制super用户）
MariaDB [hellodb]> SET GLOBAL read_only=ON; Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec)
或是在配置文件中添加此项
[root@back ~]# vi /etc/my.cnf read_only=ON
- 2.如何保证主从复制的事务安全
在master节点启用参数
sync_binlog=ON #事务提交时，立即将内存中的二进制文件写入磁盘 sync_master_info=ON #马上同步这个文件内容至磁盘 innodb_flush_logs_at_trx_commit=ON #提交时，立即将日志写入磁盘 innodb_support_xa=ON #支持分布式
在slave节点启用参数
skip_slave_start=ON #关闭自动提交 sync_relay_log=ON #文件立即同步至磁盘 sync_relay_log_info=ON #立即同步至磁盘
- 3.延时复制：防止主库删除重要数据后，从库马上同步。
#原理：IO线程将数据写入relaylog中，SQL线程"慢点"运行；一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间 mysql>stop slave; mysql>CHANGE MASTER TO MASTER_DELAY = 300; mysql>start slave; mysql> show slave status \G SQL_Delay: 300 SQL_Remaining_Delay: NULL #排查思路 延时从库处理逻辑故障 1.2.1 延时从库的恢复思路 (1) 监控到数据库逻辑故障 (2) 停从库SQL线程,记录已经回放的位置点(截取日志起点) (3) 截取relaylog， 起点: show slave status \G Relay_Log_File ,Relay_Log_Pos 终点: drop之前的位置点：show relaylog events in '' (4) 模拟SQL线程回放日志 (5) 恢复业务 情况一: 就一个库的话 从库替代主库工作 情况二: 从库导出故障库,还原到主库中. #故障演练 主库 : create database delay charset utf8mb4; use delay; create table t1 (id int); insert into t1 values(1),(2),(3); commit; drop database delay; 从库: # 1.停止 从库SQL 线程,获取relay的位置点 mysql> stop slave sql_thread; mysql> show slave status \G Relay_Log_File: db01-relay-bin.000002 Relay_Log_Pos: 626 # 2. 找到relay的截取终点 mysql> show relaylog events in 'db01-relay-bin.000002'; | db01-relay-bin.000002 | 1299 | Query | 7 | 1228 | drop database delay 3. 截取relay [root@db01 data]# cd /data/3308/data/ [root@db01 data]# mysqlbinlog --start-position=626 --stop-position=1299 db01-relay-bin.000002 >/tmp/relay.sql 4. 恢复relay到从库 [root@db01 data]# mysql -uroot -p -S /data/3308/mysql.sock mysql> set sql_log_bin=0; mysql> source /tmp/relay.sql
- 4.过滤复制
#选择指定的数据库进行复制，减轻主库压力 #replicate_do_db：要复制的库 #replicate_ignore_db：不复制的库，只需要开启一项配置就行 #过滤复制应用：从库配置文件，加上要复制的库名 vim /etc/my.cnf replicate_do_db=wordpress [root@db01 data]# systemctl restart mysqld #查看从库复制状态 mysql> show slave status \G Replicate_Do_DB: wordpress Replicate_Ignore_DB:
*如果从库之前做过复制，需要清空之前的配置
#先停止复制
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#再清空状态
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
#重新配置
mysql> CHANGE MASTER TO \
-> MASTER_HOST='10.69.39.73',\
-> MASTER_USER='copy',\
-> MASTER_PASSWORD='copy',\
-> MASTER_LOG_FILE='mysql-bin.000002',\
-> MASTER_LOG_POS=106;
Query OK, 0 rows affected (0.01 sec)
#开启复制
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
*查看主从状态
#主库的Position不能为0
mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 106
Binlog_Do_DB: gz_namc
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
#从库的Slave_IO_Running，Slave_SQL_Running要为yes
mysql> SHOW SLAVE STATUS\G
#主库有关的信息(master.info):
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
*******************************
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 609
*******************************
#从库relay应用信息有关的(relay.info):
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
#从库线程运行状态(排错)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
#过滤复制有关的信息:
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
#从库延时主库的时间(秒):
Seconds_Behind_Master: 0
#延时从库:
SQL_Delay: 0
SQL_Remaining_Delay: NULL
#GTID复制有关的状态信息
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
*自动删除之前的二进制日志
#设置最多保留多长时间之前的日志 mysql> set GLOBAL expire_logs_days=30; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'expire_logs_days'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 30 | +------------------+-------+ 1 row in set (0.00 sec)
slave复制停了很久，重新倒库，重新reset slave出现relay log报错的解决办法
处理一则MySQL Slave环境出现ERROR 1201 (HY000): Could not initialize master info structure的案例。
冷备份方式复制一份新的slave，初始化参数中已经修改了相关文件路径及server_id等关联参数。
但在启动slave时发现error_log中出现下列错误信息：
120326 11:10:23 [ERROR] /usr/local/mysql//libexec/mysqld: File '/data/mysqldata/3306/binlog/mysql-relay-bin.000002' not found (Errcode: 2)
120326 11:10:23 [ERROR] Failed to open log (file '/data/mysqldata/3306/binlog/mysql-relay-bin.000002', errno 2)
120326 11:10:23 [ERROR] Failed to open the relay log '/data/mysqldata/3306/binlog/mysql-relay-bin.000002' (relay_log_pos 126074557)
120326 11:10:23 [ERROR] Could not open log file
120326 11:10:23 [ERROR] Failed to initialize the master info structure
由于新的slave改变了服务端口和文件路径，分析应该是由于mysql-relay-bin.index中仍然保存着旧relay日志文件的路径，而这些路径下又找不到合适的文件，因此报错。
对于这类问题解决起来是比较简单的，重置slave的参照即可，执行命令如下：
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to
-> master_host='10.0.0.101',
-> master_port=3306,
-> master_user='repl',
-> master_password='repl',
-> master_log_file='mysql-bin.000011',
-> master_log_pos=1;
ERROR 29 (HY000): File '/data/mysqldata/3306/binlog/mysql-relay-bin.000001' not found (Errcode: 2)
看来应该还是mysql-relay-bin.index的问题，删除该文件及关联的relay-bin文件。再次配置master：
mysql> change master to
-> master_host='10.0.0.101',
-> master_port=3306,
-> master_user='repl',
-> master_password='repl',
-> master_log_file='mysql-bin.000011',
-> master_log_pos=1;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
出现了新的错误，按照提示查看error_log也没发现更多错误信息,error_log中只是显示一条：
120326 11:14:27 [ERROR] Error reading master configuration
在操作系统端查看master/slave的配置文件，发现是两个0字节文件：
-rw-rw---- 1 mysql mysql 0 Mar 26 11:13 master.info
-rw-rw---- 1 mysql mysql 0 Mar 26 11:13 relay-log.info
会不会是这个原因呢，直接删除这两个文件，然后尝试重新执行change master：
mysql> change master to
-> master_host='10.0.0.101',
-> master_port=3306,
-> master_user='repl',
-> master_password='repl',
-> master_log_file='mysql-bin.000011',
-> master_log_pos=1;
Query OK, 0 rows affected (0.00 sec)
成功，启动slave并查看状态：
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 101
...........
故障解决。
主从延时监控及原因
5.6.1 主库方面原因 (1) binlog写入不及时 sync_binlog=1 (2) 默认情况下dump_t 是串行传输binlog ***** 在并发事务量大时或者大事务,由于dump_t 是串型工作的,导致传送日志较慢 如何解决问题? 必须GTID,使用Group commit方式.可以支持DUMP_T并行 (3) 主库极其繁忙 慢语句 锁等待 从库个数 网络延时 5.6.2 从库方面原因 (1) 传统复制(Classic)中 ***** 如果主库并发事务量很大,或者出现大事务 由于从库是单SQL线程,导致,不管传的日志有多少,只能一次执行一个事务. 5.6 版本,有了GTID,可以实现多SQL线程,但是只能基于不同库的事务进行并发回放.(database) 5.7 版本中,有了增强的GTID,增加了seq_no,增加了新型的并发SQL线程模式(logical_clock),MTS技术 (2) 主从硬件差异太大 (3) 主从的参数配置 (4) 从库和主库的索引不一致 (5) 版本有差异 5.6.3 主从延时的监控 show slave status\G Seconds_Behind_Master: 0 主库方面原因的监控 主库: mysql> show master status ; File: mysql-bin.000001 Position: 1373 从库 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1373 从库方面原因监控: 拿了多少: Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 691688 执行了多少: Relay_Log_File: db01-relay-bin.000004 Relay_Log_Pos: 690635 Exec_Master_Log_Pos: 691000 Relay_Log_Space: 690635
初学linux，每学到一点东西就写一点，如有不对的地方，恳请包涵！
