---恢复内容开始---
环境：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 statusG *************************** 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)
---恢复内容结束---