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 [email protected] | +-------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.107.18' | +-------------------------------------------------------------------------------+ mysql> show grants for slave@'172.16.107.20'; +-------------------------------------------------------------------------------+ | Grants for [email protected] | +-------------------------------------------------------------------------------+ | 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 [email protected]:/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
标签：slave,log,配置,master,mysql,sec,主从,kevin From： https://www.cnblogs.com/xue0123/p/17916897.html