一、主从复制原理 MySQL5.6 开始主从复制有两种方式：基于日志（binlog）、基于 GTID（全局事务标示符）
master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中
slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒
二、环境
Hostname
IP
ROLE
Master01
192.168.1.136
Master
Slave01
192.168.1.137
Slave
三、配置参数文件 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 ---主库 [mysqld] log-bin=mysql-bin server-id=1 ---从库 [mysqld] log-bin=mysql-bin server-id=2 ------------------------------------------------------------------------ binlog-do-db binlog-ignore-db Replicate_Do_DB replicate-ignore-db binlog-format=ROW ------------------------------------------------------------------------- server-id可以在线调整，开启binlog需要重启实例 [root@master01 ~]# mysqladmin -uroot -p shutdown [root@master01 ~]# mysqld_safe --user=mysql & show variables like '%log_bin%' ; [root@master01 ~]# ll /data/mysql/3306/data total 110688 -rw-rw---- 1 mysql mysql 56 Jun 12 2023 auto.cnf -rw-rw---- 1 mysql mysql 12582912 Mar 22 18:24 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Mar 22 18:24 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Jun 12 2023 ib_logfile1 -rw-rw---- 1 mysql mysql 12905 Mar 22 18:24 master01.err -rw-rw---- 1 mysql mysql 5 Mar 22 18:24 master01.pid drwx------ 2 mysql mysql 4096 Jun 12 2023 mysql -rw-rw---- 1 mysql mysql 120 Mar 22 18:24 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Mar 22 18:24 mysql-bin.index drwx------ 2 mysql mysql 4096 Jun 12 2023 performance_schema -rw-rw---- 1 mysql mysql 55062 Mar 22 17:52 server01.err drwx------ 2 mysql mysql 6 Jun 12 2023 test
四、主库创建复制用户 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 grant replication slave on * .* to repl@192 .168 .1 .137 ;mysql> grant replication slave on * .* to repl@192 .168 .1 .137 identified by 'repl' ; Query OK, 0 rows affected (0.00 sec) mysql> show grants for repl@192 .168 .1 .137 ; + | Grants for repl@192 .168 .1 .137 | + | GRANT REPLICATION SLAVE ON * .* TO 'repl' @'192.168.1.137' | + mysql> select user ,host from mysql.user; + | user | host | + | root | 127.0 .0 .1 | | repl | 192.168 .1 .137 | | root | ::1 | | | localhost | | root | localhost | | | server01 | | root | server01 | + 7 rows in set (0.00 sec)[root@slave01 ~ ]# mysql - urepl - prepl - h 192.168 .1 .136
五、获取主库备份 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 mysql> create database mydb; Query OK, 1 row affected (0.00 sec) mysql> use mydb Database changed mysql> create table t (id int ); Query OK, 0 rows affected (0.00 sec) mysql> insert into t values (1 ),(2 ),(3 ); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t; + | id | + | 1 | | 2 | | 3 | + 3 rows in set (0.00 sec)[root@master01 ~ ]# mysqldump - A - R - E - A：全备- R：备份存储过程- E：备份事件
六、将备份传至从库 1 2 [root@master01 ~]# scp /tmp/mysql_full_backup.sql 192.168.1.137:/tmp
七、恢复从库 1 2 3 4 5 6 7 8 9 10 11 [root@slave01 ~ ]# mysql < / tmp/ mysql_full_backup.sql [root@slave01 ~ ]# mysql - e 'select * from mydb.t' ; + | id | + | 1 | | 2 | | 3 | +
八、从库执行change master to 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 change master to master_host= '主节点Ip' ,master_port= 3306 ,master_user= '用户名' ,master_password= '密码' ,master_log_file= '主节点binlog日志文件名' ,master_log_pos= 偏移量; [root@slave01 ~ ]# grep - m 1 'CHANGE MASTER TO' / tmp/ mysql_full_backup.sql - m 1 如果有多个匹配，只取第一个change master to master_host= '192.168.1.136' , master_port= 3306 , master_user= 'repl@192.168.1.137' , master_password= 'repl' , master_log_file= 'mysql-bin.000001' , master_log_pos= 876 ;
九、开启主从同步 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 mysql> show slave status\G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * Slave_IO_State: Master_Host: 192.168 .1 .136 Master_User: repl@192 .168 .1 .137 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql- bin.000001 Read_Master_Log_Pos: 876 Relay_Log_File: slave01- relay- bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql- bin.000001 Slave_IO_Running: No Slave_SQL_Running: No 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: 876 Relay_Log_Space: 120 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: NULL 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: 0 Master_UUID: Master_Info_File: / data/ mysql/ 3306 / data/ master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec)mysql> start slave; Query OK, 0 rows affected (0.01 sec) stop slave change master to master_user= 'repl' ; change master to master_host= '192.168.1.136' ; mysql> stop slave mysql> change master to master_host= ' ' ; reset slave; reset salve all ;
十、主从复制常见错误 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 (1 ) The slave I/ O thread stops because master and slave have equal MySQL server UUIDs mysql> show slave status\G .................................... Last_IO_Errno: 1593 Last_IO_Error: Fatal error: The slave I/ O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. 找到auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启db即可。 mysql> show variables like '%server_uuid%' ; 修改主库或从库的UUID: (1 )随机生成一个UUID [root@slave01 ~ ]# uuidgen 06 c09616- df31-4 ae4-8 bb9- cc806495782a(2 )替换UUID [root@slave01 ~ ]# vi / data/ mysql/ 3306 / data/ auto.cnf (3 )重启mysql [root@slave01 ~ ]# mysqladmin - uroot - p shutdown [root@slave01 ~ ]# mysqld_safe mysql> show variables like '%uuid%' ; + | Variable_name | Value | + | server_uuid | 06 c09616- df31-4 ae4-8 bb9- cc806495782a | + 1 row in set (0.00 sec)(4 )启动从库复制 mysql> start slave;
