MySQL 5.7 基于GTID搭建主从复制

MySQL 5.7 基于GTID搭建主从复制

一、搭建过程
1.1 准备三个MySQL实例

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/3307/data/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/3308/data/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/3309/data/

1.2 server_id, server_uuid 不同

mysql -uroot -p123 -e "select @@server_id"mysql -uroot -p123 -e "select @@server_uuid"

1.3 gtid_mode 是否开启：

mysql -uroot -p123 -e "select @@gtid_mode"mysql -uroot -p123 -e "select @@server_gtid"vi 330{7..9}/my.cnfgtid_mode=ONenforce_gtid_consistency=ONlog_slave_updates=ON

1.4 确认binlog开启

mysql -uroot -p123 -e "select @@log_bin"

1.5 备份主库数据到从库通过远程方式

mysqldump -uroot -p123 -h 192.168.84.30 -P 3307 > /tmp/full.sql

1.6 恢复数据

mysql> source /tmp/full.sql

1.7 主库创建复制用户

 grant replication slave on *.* to repl@'192.168.84.30' identified by '123';

1.8 从库启动复制

 帮助：mysql> help change master to......找到配置模板：CHANGE MASTER TO  MASTER_HOST='master2.example.com',  MASTER_USER='replication',  MASTER_PASSWORD='password',  MASTER_PORT=3306,  MASTER_LOG_FILE='master2-bin.001',  MASTER_LOG_POS=4,  MASTER_CONNECT_RETRY=10;    3308，3309使用如下配置：CHANGE MASTER TO  MASTER_HOST='192.168.84.30',  MASTER_USER='repl',  MASTER_PASSWORD='123',  MASTER_PORT=3307,  master_auto_position=1;  开始配置3308mysql> CHANGE MASTER TO    ->   MASTER_HOST='192.168.84.30',    ->   MASTER_USER='repl',    ->   MASTER_PASSWORD='123',    ->   MASTER_PORT=3307,    ->   master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.00 sec)开始配置3309mysql> CHANGE MASTER TO    ->   MASTER_HOST='192.168.84.30',    ->   MASTER_USER='repl',    ->   MASTER_PASSWORD='123',    ->   MASTER_PORT=3307,    ->   master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.00 sec)

1.9 查看状态：

mysql> show slave status \G;  查看slave状态mysql> show slave status \G;*************************** 1. row ***************************               Slave_IO_State:                   Master_Host: 192.168.84.30                  Master_User: repl                  Master_Port: 3307                Connect_Retry: 60              Master_Log_File:           Read_Master_Log_Pos: 4               Relay_Log_File: open_source-relay-bin.000001                Relay_Log_Pos: 4        Relay_Master_Log_File:              Slave_IO_Running: No            Slave_SQL_Running: No              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: 0              Relay_Log_Space: 154              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: NULLMaster_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: 0                  Master_UUID:              Master_Info_File: /mysql/3308/data/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State:            Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 1         Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specified

2.0 启动和关闭复制：

start slave;stop slave;

备注：5.7 MGR 新出的亮点，8.0.17建议用MGR