1、主库数据库配置
[root@master ~]# cat /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #GTID server_id = 1 gtid_mode = on enforce_gtid_consistency = on #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1 #不配置binlog_group_commit从库无法做到基于事物的并行复制 binlog_group_commit_sync_delay = 100 binlog_group_commit_sync_no_delay_count = 10 #为了数据安全再配置 sync_binlog=1 innodb_flush_log_at_trx_commit =1 #这个参数控制binlog写入 磁盘的方式。设置为1时，表示每次commit;都写入磁盘。这个刷新的是redo log 即ib_logfile0，而不是binlog #开启半同步复制 (超时时间为1s) plugin-load=rpl_semi_sync_master=semisync_master.so rpl_semi_sync_master_enabled = 1 rpl_semi_sync_master_timeout = 1000
数据库配置
配置后，记得重启mysql服务 [root@mysql-master ~]# systemctl restart mysqld 登录mysql，并查看master状态， 发现多了一项"Executed_Gtid_Set " mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show global variables like '%uuid%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 317e2aad-1565-11e9-9c2e-005056ac6820 | +---------------+--------------------------------------+ 1 row in set (0.00 sec) 查看确认gtid功能打开 mysql> show global variables like '%gtid%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-------+ 8 rows in set (0.00 sec) 查看确认binlog日志功能打开 mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) 授权slave复制用户(replication client)和(replication slave)权限，并刷新权限 mysql> GRANT replication client,replication slave on *.* to 'slave'@'172.16.107.18' identified by "slave@123"; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> GRANT replication client,replication slave on *.* to 'slave'@'172.16.107.20' identified by "slave@123"; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%'; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> flush privileges; Query OK, 0 rows affected (0.04 sec) mysql> flush privileges; Query OK, 0 rows affected (0.04 sec) mysql> show grants for slave@'172.16.107.18'; +-------------------------------------------------------------------------------+ | Grants for slave@172.16.107.18 | +-------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.107.18' | +-------------------------------------------------------------------------------+ mysql> show grants for slave@'172.16.107.20'; +-------------------------------------------------------------------------------+ | Grants for slave@172.16.107.20 | +-------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.107.20' | +-------------------------------------------------------------------------------+ mysql> show grants for slave@'%'; +-------------------------------------------------------------------+ | Grants for slave@% | +-------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%' | +-------------------------------------------------------------------+ 再次查看master状态 mysql> show master status; +-------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000001 | 622 | | | 317e2aad-1565-11e9-9c2e-005056ac6820:1-2 | +-------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) 这里需要注意一下： 启动配置之前，同样需要对从服务器进行初始化。对从服务器初始化的方法基本和基于日志点是相同的，只不过在启动了GTID模式后， 在备份中所记录的就不是备份时的二进制日志文件名和偏移量了，而是记录的是备份时最后的GTID值。 需要先在主数据库机器上把目标库备份一下，假设这里目标库是kevin（为了测试效果，下面手动创建） mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.02 sec) mysql> use kevin; Database changed mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL); Query OK, 0 rows affected (0.27 sec) mysql> insert into kevin.haha values(1,"congcong"),(2,"huihui"),(3,"grace"); Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from kevin.haha; +----+----------+ | id | name | +----+----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | +----+----------+ 3 rows in set (0.00 sec) 把kevin库备份出来 [root@mysql-master ~]# mysqldump --single-transaction --master-data=2 --triggers --routines --databases kevin -uroot -p123456 > /root/kevin.sql 这里稍微注意下： mysql5.6使用mysqldump备份时，指定备份的具体库，使用--database mysql5.7使用mysqldump备份时，指定备份的具体库，使用--databases [root@mysql-master ~]# ls /root/kevin.sql /root/kevin.sql [root@mysql-master ~]# cat /root/kevin.sql -- MySQL dump 10.13 Distrib 5.7.24, for Linux (x86_64) -- -- Host: localhost Database: kevin -- ------------------------------------------------------ -- Server version 5.7.24-log ............. ............. -- -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED='317e2aad-1565-11e9-9c2e-005056ac6820:1-5'; 然后把备份的/root/kevin.sql文件拷贝到mysql-slave1/mysql-slave2从数据库服务器上 [root@mysql-master ~]# scp /root/kevin.sql root@172.16.60.212:/root/ ============================================================== mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 3 | | 3306 | 1 | e4657691-67de-11ee-bf31-525400f6b33e | | 2 | | 3306 | 1 | 7b1d0918-6ccf-11ee-a29d-525400507ca0 | +-----------+------+------+-----------+--------------------------------------+
从库slave1配置
[root@slave1 ~]# cat /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #GTID: server_id = 2 gtid_mode = on enforce_gtid_consistency = on #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1 read_only = on #relay_log_purpe = 0 #MTS slave-parallel-type = LOGICAL_CLOCK #开启逻辑时钟的复制 slave-parallel-workers = 4 #最大线程16 master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = on # 开启半同步复制 plugin-load=rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_slave_enabled=1
数据库操作：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> source /root/kevin.sql; mysql> select * from kevin.haha; +----+----------+ | id | name | +----+----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | +----+----------+ 3 rows in set (0.00 sec) 在从数据库里，使用change master 配置主从复制 mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.26 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.60.211 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1357 Relay_Log_File: mysql-slave1-relay-bin.000002 Relay_Log_Pos: 417 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ................ ................ Executed_Gtid_Set: 317e2aad-1565-11e9-9c2e-005056ac6820:1-5 Auto_Position: 1 由上面可知，mysql-slave1节点已经和mysql-master节点配置了主从同步关系 3） mysql-master主数据库上进行状态查看和测试测试插入 mysql> show master status; +-------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000001 | 1357 | | | 317e2aad-1565-11e9-9c2e-005056ac6820:1-5 | +-------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) mysql> +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 2 | | 3306 | 1 | 2c1efc46-1565-11e9-ab8e-00505688047c | +-----------+------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec) mysql> insert into kevin.haha values(4,"beijing"),(5,"hefei"),(10,"xihu"); Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> delete from kevin.haha where id<4; Query OK, 3 rows affected (0.10 sec) mysql> select * from kevin.haha; +----+---------+ | id | name | +----+---------+ | 4 | beijing | | 5 | hefei | | 10 | xihu | +----+---------+ 3 rows in set (0.00 sec) 4）mysql-slave1从数据库上查看 mysql> select * from kevin.haha; +----+---------+ | id | name | +----+---------+ | 4 | beijing | | 5 | hefei | | 10 | xihu | +----+---------+ 3 rows in set (0.00 sec) 发现mysql-slave1从数据库已经将新插入的数据同步过来了，由此，基于GTID的主从同步复制关系已经正常部署完成了！ 重置复制关系 mysql> reset slave; mysql> change master to master_host='172.16.107.16',master_user='slave',master_password='123456',master_auto_position=1; mysql> start slave;
从库slave2配置
[root@slave2 ~]# cat /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #GTID: server_id = 3 gtid_mode = on enforce_gtid_consistency = on #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1 read_only = on #MTS slave-parallel-type = LOGICAL_CLOCK #开启逻辑时钟的复制 slave-parallel-workers = 4 #最大线程16 master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = on # 开启半同步复制 plugin-load=rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_slave_enabled=1
数据库操作同slave1