MySQL单节点变更为主从节点
环境说明：
操作系统：CentOS 7.6
数据库版本：5.7
为了实验方便，同一台主机部署了两个实例，3306、3307 部署方案可参考 MySQL多实例部署：mysql多实例部署 - 太阳的阳ฅ - 博客园 (cnblogs.com)
一、主库配置
1、备份原先的配置文件，增加主从配置
vim /home/mysql/3306/my.cnf
[mysqld] bind-address=0.0.0.0 port=3306 user=mysql basedir=/home/mysql datadir=/home/mysql/3306/data socket=/home/mysql/3306/mysql.sock log-error=/home/mysql/3306/mysql.err pid-file=/home/mysql/3306/mysql.pid slow_query_log = ON slow_query_log_file = /home/mysql/3306/slow.log long_query_time = 1 #character config character_set_server=utf8mb4 symbolic-links=0 #新增配置 server-id=1 gtid_mode=ON enforce_gtid_consistency=ON binlog_format=row expire_logs_days=7 max_binlog_size=512M log-bin=/home/mysql/3306/logs/mysql-bin log_bin_index=/home/mysql/3306/logs/mysql-bin.index log-slave-updates = ON
2、创建对应的 logs 目录
# cd /home/mysql/ # mkdir {3306,3307}/logs # chown mysql .mysql -R *
3、重启服务
守护进程方式启动的，杀掉进程重启
./bin/mysqld_safe --defaults-file=/home/mysql/3306/my.cnf &
4、登录数据库，配置主从同步账号
# ./bin/mysql -uroot -p -S /home/mysql/3306/mysql.sock mysql> GRANT REPLICATION SLAVE ON *.* to 'repl'@'192.168.53.%' IDENTIFIED BY 'repl'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------+ | mysql-bin.000001 | 448 | | | 1c68d59d-6646-11ed-963e-000c298a317b:1 | +------------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE '%gtid%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 8 rows in set (0.01 sec)
二、从库配置
1、配置文件
vim /home/mysql/3307/my.cnf
[mysqld] bind-address=0.0.0.0 port=3307 user=mysql basedir=/home/mysql datadir=/home/mysql/3307/data socket=/home/mysql/3307/mysql.sock log-error=/home/mysql/3307/mysql.err pid-file=/home/mysql/3307/mysql.pid slow_query_log = ON slow_query_log_file = /home/mysql/3307/slow.log long_query_time = 1 #character config character_set_server=utf8mb4 symbolic-links=0 #新增配置 server-id=151 gtid_mode = ON enforce_gtid_consistency = ON skip-slave-start = true expire_logs_days = 7 max_binlog_size = 512M read_only = ON log-bin=/home/mysql/3307/logs/mysql-bin log_bin_index = /home/mysql/3307/logs/mysql-bin.index relay-log = /home/mysql/3307/logs/relay-log relay-log-index = /home/mysql/3307/logs/relay-log-index relay-log-info-file = /home/mysql/3307/logs/relay-log.info log-slave-updates=true master-info-repository = table relay-log-info-repository = table
2、参考主库重启命令，重新启动从库
3、配置slave 同步
# ./bin/mysql -uroot -p -S /home/mysql/3307/mysql.sock mysql> CHANGE MASTER TO MASTER_HOST='192.168.53.123',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASSTER_AUTO_POSITION=1; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.53.123 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 448 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 661 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 448 Relay_Log_Space: 862 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: 0 Master_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: 1 Master_UUID: 1c68d59d-6646-11ed-963e-000c298a317b Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 1c68d59d-6646-11ed-963e-000c298a317b:1 Executed_Gtid_Set: 1c68d59d-6646-11ed-963e-000c298a317b:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
配置完成
PS：如果出现sql 线程或者IO线程NO，可尝试将主库暂时锁表，说有数据备份出来导入从库，解锁再观察，注意先 stop slave;
mysqldump -S /home/mysql/3306/mysql.sock -u root -p --lock-all-tables --master-data=2 --flush-logs --all-databases --triggers --routines --events > full.sql
./bin/mysql -uroot -p -S /home/mysql/3307/mysql.sock < full.sql
