复制原理:复制需要二进制日志记录数据库上的改变
slave的IO线程复制把master上的Binary log读取到本地的relay log里
SQL线程负责把relay log恢复到数据库数据里
show slave status\G; Slave_IO_State: Waiting for master to send event Master_Host: 10.105.7.108 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 630 Relay_Log_File: mysqld-relay-bin.000006 Relay_Log_Pos: 793 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes关闭slave的io线程 stop slave io_thread; mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Master_Host: 10.105.7.108 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 630 Relay_Log_File: mysqld-relay-bin.000006 Relay_Log_Pos: 793 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: No Slave_SQL_Running: Yes到mastermysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 143 || mysql-bin.000002 | 367 || mysql-bin.000003 | 120 || mysql-bin.000004 | 630 |+------------------+-----------+4 rows in set (0.00 sec)日志轮换mysql> flush logs;Query OK, 0 rows affected (0.02 sec)mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 143 || mysql-bin.000002 | 367 || mysql-bin.000003 | 120 || mysql-bin.000004 | 677 || mysql-bin.000005 | 120 |+------------------+-----------+mysql> use hk;Database changedmysql> show tables;+--------------+| Tables_in_hk |+--------------+| test |+--------------+1 row in set (0.01 sec)mysql> select * from test;+----+---------------------+| id | time |+----+---------------------+| 1 | 2017-02-04 10:04:12 || 2 | 2017-02-04 13:53:35 || 3 | 2017-02-08 19:46:49 |+----+---------------------+3 rows in set (0.00 sec)mysql> insert into test values(4,now());Query OK, 1 row affected (0.05 sec)mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 143 || mysql-bin.000002 | 367 || mysql-bin.000003 | 120 || mysql-bin.000004 | 677 || mysql-bin.000005 | 344 |+------------------+-----------+5 rows in set (0.00 sec)mysql> show binlog events in 'mysql-bin.000005';+------------------+-----+-------------+-----------+-------------+--------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+-----+-------------+-----------+-------------+--------------------------------------------+| mysql-bin.000005 | 4 | Format_desc | 108 | 120 | Server ver: 5.6.35-log, Binlog ver: 4 || mysql-bin.000005 | 120 | Query | 108 | 203 | BEGIN || mysql-bin.000005 | 203 | Query | 108 | 313 | use `hk`; insert into test values(4,now()) || mysql-bin.000005 | 313 | Xid | 108 | 344 | COMMIT /* xid=110 */ |+------------------+-----+-------------+-----------+-------------+--------------------------------------------+可以看到一件记录到二进制日志里mysql> select * from test where id=4;+----+---------------------+| id | time |+----+---------------------+| 4 | 2017-02-08 20:34:49 |+----+---------------------+到从库Database changedmysql> select * from test;+----+---------------------+| id | time |+----+---------------------+| 1 | 2017-02-04 10:04:12 || 2 | 2017-02-04 13:53:35 || 3 | 2017-02-08 19:46:49 |+----+---------------------+查看此时 Relay_Log_File: mysqld-relay-bin.000006mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Master_Host: 10.105.7.108 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 630 Relay_Log_File: mysqld-relay-bin.000006 Relay_Log_Pos: 793 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: No Slave_SQL_Running: Yes开启io线程mysql> start slave io_thread;Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.105.7.108 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 344 Relay_Log_File: mysqld-relay-bin.000009 Relay_Log_Pos: 507 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes发现中继日志发生变化Relay_Log_File: mysqld-relay-bin.000009mysql> select * from test where id=4;+----+---------------------+| id | time |+----+---------------------+| 4 | 2017-02-08 20:34:49 |+----+---------------------+1 row in set (0.00 sec)此时sql进程 从中继日志 读取数据 写入数据库
在mysql外执行
mysqlbinlog mysqld-relay-bin.000009
[root@bogon mysql]# mysqlbinlog mysqld-relay-bin.000009/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#170208 20:39:46 server id 163 end_log_pos 120 CRC32 0xbdb725c4 Start: binlog v 4, server v 5.6.35 created 170208 20:39:46BINLOG 'khGbWA+jAAAAdAAAAHgAAABAAAQANS42LjM1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcQlt70='/*!*/;# at 120#170208 20:39:46 server id 108 end_log_pos 0 CRC32 0x0649f72c Rotate to mysql-bin.000005 pos: 4# at 167#170208 20:33:47 server id 108 end_log_pos 120 CRC32 0x045c4ff1 Start: binlog v 4, server v 5.6.35-log created 170208 20:33:47BINLOG 'KxCbWA9sAAAAdAAAAHgAAAAAAAQANS42LjM1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAfFPXAQ='/*!*/;# at 283#170208 20:34:49 server id 108 end_log_pos 203 CRC32 0xb10230cc Query thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1486557289/*!*/;SET @@session.pseudo_thread_id=9/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1075838976/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;SET @@session.time_zone='SYSTEM'/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 366#170208 20:34:49 server id 108 end_log_pos 313 CRC32 0x59ee4c4e Query thread_id=9 exec_time=0 error_code=0use `hk`/*!*/;SET TIMESTAMP=1486557289/*!*/;insert into test values(4,now())/*!*/;# at 476#170208 20:34:49 server id 108 end_log_pos 344 CRC32 0x1735772f Xid = 110COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
复制特点
1.sql更新,没写binary log 不可能复制2.正在执行的sql更新,也不可能被复制3.只有执行完成而且成功的语句才会被复制