GTID实现主从复制
---恢复内容开始---
环境：centos6.5 mariadb：10.1.13-MariaDB
GTID:GTID是有服务器的UUID和事务序号组成的唯一事务序号 ---UUID:N
作用：1.用来追踪主从之间的事务传输。
2.进行多线程复制：master服务器中启动一个binlog线程来相应slave端的I/O线程。
一个I/O线程对主服务器进行mysqldump请求，多个SQL线程对slave中的中继日志中的不同事务通过不同的线程进行应用。
master：192.168.88.147
slave：192.168.88.148
一、主从复制：
1.master和slave上的配置：vim /etc/my.cnf
1 mstaer端：
2 binlog-format=ROW #二进制日志的格式，有row、statement和mixed几种类型:需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；
3 log-bin=slave-bin
4 #gtid-mode=on #在MariaDB上不需要
5 #enforce-gtid-consistency=true #在MariaDB上不需要
6 log-slave-updates=true
7 master-info-repository=TABLE
8 relay-log-info-repository=TABLE
9 sync-master-info=1
10 slave-parallel-threads=2 #在mysql5.6以上版本须写为slave-parallel-workers
11 binlog-checksum=CRC32
12 master-verify-checksum=1
13 slave-sql-verify-checksum=1
14 binlog-rows-query-log_events=1
15 server-id=10
16 report-port=3306
17 port=3306
18 datadir=/mydata/data
19 socket=/tmp/mysql.sock
20 report-host=www.master.com
21
22
23 slave端：
24 binlog-format=ROW
25 log-bin=master-bin
26 log-slave-updates=true
27 master-info-repository=TABLE
28 relay-log-info-repository=TABLE
29 sync-master-info=1
30 slave-parallel-threads=2
31 binlog-checksum=CRC32
32 master-verify-checksum=1
33 slave-sql-verify-checksum=1
34 binlog-rows-query-log_events=1
35 server-id=20
36 report-port=3306
37 port=3306
38 datadir=/mydata/data
39 socket=/tmp/mysql.sock
40 report-host=www.master.com
41
42
43 上述选项详解：
44 binlog-format：二进制日志的格式，有row、statement和mixed几种类型；
45 需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；
46 log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于启动GTID及满足附属的其它需求；
47 master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能；
48 sync-master-info：启用之可确保无信息丢失；
49 slave-parallel-workers：设定从服务器的SQL线程数；0表示关闭多线程复制功能；MariaDB上是slave-parallel-threads
50 binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能；
51 binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度；
52 log-bin：启用二进制日志，这是保证复制功能的基本前提；
53 server-id：同一个复制拓扑中的所有服务器的id号必须惟一；2.在master上授权复制用户：
1 MariaDB [(none)]> grant replication slave,replication client on *.* to 'daixiang'@'192.168.88.%' identified by 'daixiang';
2 MariaDB [(none)]> flush privileges;3.在slave从服务器上设置将从服务器指向主服务器，并启动复制线程：
MariaDB [(none)]> change master to MASTER_HOST='192.168.88.147',MASTER_USER='daixiang',MASTER_PASSWORD='daixiang',MASTER_USE_GTID=current_pos;
注意：在mysql5.6以后的版本，需要使用此命令指定主服务器：mysql> CHANGE MASTER TO MASTER_HOST='master.magedu.com', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;
如果没有启用GTID功能，则需要使用下面的命令：slave> CHANGE MASTER TO MASTER_HOST='172.16.100.6',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_LOG_FILE='master-bin.000003',
-> MASTER_LOG_POS=1174;MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.88.147
Master_User: daixiang
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 808
Relay_Log_File: www-relay-bin.000002
Relay_Log_Pos: 799
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 808
Relay_Log_Space: 1099
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: 10
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Current_Pos
Gtid_IO_Pos: 0-10-3
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)4.查看从服务器上的线程情况：
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| 10 | system user | | NULL | Connect | 2853 | Waiting for master to send event | NULL | 0.000 |
| 11 | system user | | NULL | Connect | 2646 | Waiting for work from SQL thread | NULL | 0.000 |
| 12 | system user | | NULL | Connect | 2853 | Waiting for work from SQL thread | NULL | 0.000 |
| 13 | system user | | NULL | Connect | 2853 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |
| 15 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
5 rows in set (0.00 sec)
这几个线程分别是：
15号：主进程
13号：I/O线程
12号、11号：SQL线程
10号：监听的守护进程5.查看主服务器上的线程情况：
MariaDB [(none)]> show processlist;
+----+----------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+----------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| 7 | daixiang | 192.168.88.148:42475 | NULL | Binlog Dump | 3315 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
| 9 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
+----+----------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
2 rows in set (0.00 sec)
---恢复内容结束---
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
