42_03_2_实验:MySQL基于GTID的多线程复制

2015-10-02

主MySQL的配置文件

[root@node1 ~]# egrep -v '^$|#' /etc/my.cnf 
[mysqld] 
datadir = /mydata/data 
binlog_format=ROW 
port = 3306 
server_id = 10 
socket = /tmp/mysql.sock 
innodb_file_per_table = ON 
character_set_server = utf8 
rpl_semi_sync_master_enabled = ON 
log_bin = master_bin 
log_bin_index = master_bin.index 
ssl-ca=/usr/local/mysql/ssl/cacert.pem 
ssl-cert=/usr/local/mysql/ssl/master.crt 
ssl-key=/usr/local/mysql/ssl/master.key 
log-slave-updates=true 
gtid-mode=on 
enforce-gtid-consistency=true 
master-info-repository=TABLE    
sync-master-info=1 
slave-parallel-workers=2 
binlog-checksum=CRC32 
master-verify-checksum=1 
slave-sql-verify-checksum=1 
binlog-rows-query-log-events=1 
report-port=3306 
report-host=192.168.122.220 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

从MySQL的配置文件

[root@node2 ~]# egrep -v '^$|#' /etc/my.cnf 
[mysqld] 
datadir = /mydata/data 
port = 3306 
server_id = 11 
socket = /tmp/mysql.sock 
innodb_file_per_table = ON 
character_set_server = utf8 
relay_log = relay_log 
relay_log_index = relay_log.index 
rpl_semi_sync_slave_enabled = ON 
ssl_ca = /usr/local/mysql/ssl/cacert.pem 
ssl_cert = /usr/local/mysql/ssl/slave.crt 
ssl_key = /usr/local/mysql/ssl/slave.key 
binlog-format=ROW 
log-slave-updates=true 
gtid-mode=on 
enforce-gtid-consistency=true 
master-info-repository=TABLE 
relay-log-info-repository=TABLE 
sync-master-info=1 
slave-parallel-workers=2 
binlog-checksum=CRC32 
master-verify-checksum=1 
slave-sql-verify-checksum=1 
binlog-rows-query-log-events=1 
report-port=3306 
log-bin=mysql-bin.log 
report-host=192.168.122.14 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

mysql>GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.122.%' IDENTIFIED BY 'replpass'; 
mysql>FLUSH PRIVILEGES; 
 
 
 
mysql> change master to master_host='192.168.122.120', 
    -> master_user='repluser', 
    -> master_password='replpass', 
    -> master_auto_position=1; 
Query OK, 0 rows affected, 2 warnings (0.30 sec) 
 
 
mysql>start slave;

如果遇到mysql -urepluser -h 192.168.122.220 -p

出现失败 repluser@node1.test.com 说明出现了域名解析，那么，
skip_name_reslove=ON即可；；；

设置半同步

#On Master 
mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 
mysql>SHOW GLOBAL VARIABLES LIKE '%rpl%'; 
mysql>SET GLOBAL rpl_semi_sync_master_enabled = 1; 
mysql>SET GLOBAL rpl_semi_sync_master_timeout = 1000;   1000ms 
 
#On Slave 
mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 
mysql>SET GLOBAL rpl_semi_sync_slave_enabled = 1;　 
mysql>STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; 
 
在Master和Slave的my.cnf中编辑: 
 
#On Master 
[mysqld] 
rpl_semi_sync_master_enabled=1 
rpl_semi_sync_master_timeout=1000     #1 second 
 
#On Slave 
[mysqld] 
rpl_semi_sync_slave_enabled =1