mysql的简单主从复制
主从复制打开后，各自开启的线程：
master端
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 4
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 5
User: kiosk
Host: server2:41404
db: NULL
Command: Binlog Dump #开启一个连接server2的Dump线程
Time: 714
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 3. row ***************************
Id: 6
User: kiosk
Host: server3:60038
db: NULL
Command: Binlog Dump #开启一个连接server3的Dump线程
Time: 260
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
3 rows in set (0.00 sec)
ERROR:
No query specified
slave端（两台slave开启的线程相同）
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 6
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 7
User: system user
Host:
db: NULL
Command: Connect
Time: 736
State: Waiting for master to send event #开启一个IO线程等待master发送event
Info: NULL
*************************** 3. row ***************************
Id: 8
User: system user
Host:
db: NULL
Command: Connect mysql> show processlist\G;
*************************** 1. row ***************************
Id: 6
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 7
User: system user
Host:
db: NULL
Command: Connect #开启有一个IO线程
Time: 736
State: Waiting for master to send event #等待master发送event
Info: NULL
*************************** 3. row ***************************
Id: 8
User: system user
Host:
db: NULL
Command: Connect #开启一个SQL线程
Time: 150
State: Slave has read all relay log; waiting for more updates #relay log已经读完，等待更多的数据
Info: NULL
3 rows in set (0.00 sec)
ERROR:
No query specified
日志文件
[root@server1 mysql]# pwd
/var/lib/mysql
[root@server1 mysql]# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.index mysql.sock.lock public_key.pem sys
ca-key.pem client-key.pem ib_logfile0 mysql mysqld_safe.pid performance_schema server-cert.pem test
ca.pem ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql.sock private_key.pem server-key.pem
日志文件mysql-bin.000001的查看方式
[root@server1 mysql]# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4 #pos号从4开始，到123结束（end_log_pos 123）
#170626 16:51:14 server id 1 end_log_pos 123 CRC32 0x81470229 Start: binlog v 4, server v 5.7.17-log created 170626 16:51:14 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
AstQWQ8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAACy1BZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ASkCR4E=
'/*!*/;
# at 123
#170626 16:51:15 server id 1 end_log_pos 154 CRC32 0x1b2dd997 Previous-GTIDs
# [empty]
# at 154
#170626 19:24:18 server id 1 end_log_pos 219 CRC32 0xfa85ae42 Anonymous_GTID last_committed=0 sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#170626 19:24:18 server id 1 end_log_pos 448 CRC32 0x2f4c5c11 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1498476258/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
GRANT REPLICATION SLAVE ON *.* TO 'kiosk'@'172.25.44.%' IDENTIFIED WITH 'mysql_native_password' AS '*17C56DEB4E9DB39B6523B6E79C6E99F3462FE761'
/*!*/;
# at 448
#170626 19:25:28 server id 1 end_log_pos 513 CRC32 0xd5b74785 Anonymous_GTID last_committed=1 sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 513
#170626 19:25:28 server id 1 end_log_pos 600 CRC32 0x90cfecc3 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1498476328/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
flush privileges
/*!*/;
# at 600
#170626 19:37:46 server id 1 end_log_pos 665 CRC32 0x5c6c5e78 Anonymous_GTID last_committed=2 sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 665
#170626 19:37:46 server id 1 end_log_pos 759 CRC32 0x432d0d44 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1498477066/*!*/;
create database test
/*!*/;
# at 759
#170626 19:38:55 server id 1 end_log_pos 824 CRC32 0x56adfbce Anonymous_GTID last_committed=3 sequence_number=4
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 824
#170626 19:38:55 server id 1 end_log_pos 968 CRC32 0x0dcfbcbf Query thread_id=4 exec_time=1 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1498477135/*!*/;
create table test(
id varchar(10) not null,
name varchar(10) not null)
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
last_committed表示事务提交的时候，上次事务提交的编号。
如果事务的last_committed值一样，就表示这些事物是一起提交的操作，可以进行并行的回放。
同时一个组里的last committed对应上一个事务的sequence number。
组提交只与last committed有关,这也是MySQL基于组提交(logic clock)的并行复制即使在gtid关闭情形下也能生效的原因
[root@server1 mysql]# mysqlbinlog mysql-bin.000001 |grep last_committed
#170626 19:24:18 server id 1 end_log_pos 219 CRC32 0xfa85ae42 Anonymous_GTID last_committed=0 sequence_number=1
#170626 19:25:28 server id 1 end_log_pos 513 CRC32 0xd5b74785 Anonymous_GTID last_committed=1 sequence_number=2
#170626 19:37:46 server id 1 end_log_pos 665 CRC32 0x5c6c5e78 Anonymous_GTID last_committed=2 sequence_number=3
#170626 19:38:55 server id 1 end_log_pos 824 CRC32 0x56adfbce Anonymous_GTID last_committed=3 sequence_number=4
基于gtid的mysql主从复制
server-uuid是对server的唯一标识，是用来生成gtid的，因为gtid的有一部分就是uuid。
gtid的组成：uuid+序列号
全局唯一的 server_uuid 的一个好处是：可以解决由 server_id 配置冲突带来的 MySQL 主备复制的异常终止。
[root@server1 mysql]# cat auto.cnf
[auto]
server-uuid=2b368933-5a43-11e7-86b6-525400670fe5
修改配置文件/etc/my.cnf（master和slave端都要配）
gtid-mode=ON
enforce-gtid-consistency=ON
slave端：
mysql> change master to master_host='172.25.44.1',master_user='kiosk',master_password='Lt@@0404',master_log_file='mysql-bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (1.43 sec)
关于sync_binlog参数
- sync_binlog=0 ## 表示在事物提交之后，MySQL不会调用fsync指令将binlog_cache中的信息刷新到磁盘，而是交给文件系统，决定是将数据刷新到磁盘。这种之后的性能是最好的，当然风险也最大。一旦系统Crash，binlog信息就都会丢失。
- sync_binlog=n ## 表示每体提交n次事物，MySQL就会强制调用fsync指令将binlog_cache中的信息写入磁盘。当然n=1的时，最安全同时系统性能损耗也最大，这样的话：即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.00 sec)
mysql的并行复制
配置slave端的配置文件
slave-parallel-type=DATABASE 基于库的并行复制方式
slave-parallel-type=LOGICAL_CLOCK 基于组提交的并行复制方式
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
查看开启的并行复制的线程
[root@server2 software]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server2 software]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show processlist\G; #除了原有的线程之外，还开启了16个并行线程
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 56
State: Slave has read all relay log; waiting for more updates
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 4. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 5. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 6. row ***************************
Id: 7
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 7. row ***************************
Id: 8
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 8. row ***************************
Id: 9
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 9. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 10. row ***************************
Id: 12
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 11. row ***************************
Id: 14
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 12. row ***************************
Id: 15
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 13. row ***************************
Id: 16
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 14. row ***************************
Id: 17
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 15. row ***************************
Id: 18
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 16. row ***************************
Id: 19
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 17. row ***************************
Id: 20
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 18. row ***************************
Id: 21
User: system user
Host:
db: NULL
Command: Connect
Time: 58
State: Waiting for an event from Coordinator
Info: NULL
*************************** 19. row ***************************
Id: 22
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
19 rows in set (0.00 sec)
ERROR:
No query specified
