Mysql主从环境配置

采用Gtid的主从同步方式

操作步骤

主从配置前准备

需要打开如下配置

# GTID:gtid_mode = onenforce_gtid_consistency = on

导出单个数据库

[root@server01 ~]# mysqldump -uroot -p --databases manage_db > 1.sqlEnter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don‘t want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

向slave库输入数据

[root@server02 ctchat]# mysql -uroot -p111111 < 1.sql mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

如果出现上面情况，需要在当前数据库执行（清空本机gtid信息,此参数慎用）

mysql> reset master;

再次执行导入

[root@server02 ctchat]# mysql -uroot -p111111 < 1.sql mysql: [Warning] Using a password on the command line interface can be insecure.

可以查看gtid信息（非必须）

mysql> SELECT * FROM mysql.gtid_executed;+--------------------------------------+----------------+--------------+| source_uuid | interval_start | interval_end |+--------------------------------------+----------------+--------------+| 51b51b0d-2626-11ea-88f0-000c2982a1b2 | 1 | 8 || bbaf780c-eb3a-11e9-94eb-6c92bf7e28c6 | 3 | 12881 || fa2f988c-eb3c-11e9-942f-6c92bf7dac7c | 1 | 33188709 |+--------------------------------------+----------------+--------------+3 rows in set (0.00 sec)

配置主从

 在master上创建主从复制用户

mysql> create user ‘slave1‘@‘%‘ identified by ‘123456‘;mysql> grant replication slave on *.* to ‘slave1‘@‘%‘;mysql> flush privileges;

 slave配置主从

mysql> CHANGE MASTER TOMASTER_HOST = ‘10.4.7.3‘, MASTER_PORT = 1342, MASTER_USER = ‘slave1‘, MASTER_PASSWORD = ‘123456‘, MASTER_AUTO_POSITION = 1 for channel ‘db01‘;mysql> start slave;mysql> show slave status\G;