mysql5.7配置主从同步（master-slave）
准备工作
主服务器：192.168.5.130
从服务器：192.168.5.140
步骤
1. 主数据库配置修改
在标签
[mysqld]下配置
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id
binlog-do-db = test #需要同步的数据库
binlog-ignore-db = mysql #忽略同步的数据库
2. 主数据库新建同步账号
mysql> GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.5.140' identified by 'yourPassword';
mysql> FLUSH PRIVILEGES;
3. 重启mysql服务，查看master status
service mysqld restart
mysql -uroot -p
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000018 | 154 | test | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4. 配置从数据库
mysql> CHANGE MASTER TO MASTER_HOST='192.168.5.130',MASTER_PORT=3306,MASTER_USER='rep1',MASTER_PASSWORD='yourPassword',MASTER_LOG_FILE='mysql-bin.000018',MASTER_LOG_POS=154;
CHANGE MASTER TO
MASTER_HOST=‘192.168.5.130’, //主库IP
MASTER_PORT=3306, //主库端口
MASTER_USER=‘rep1’, //访问主库且有同步复制权限的用户
MASTER_PASSWORD=‘yourPassword’, //登录密码
//【关键处】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果
MASTER_LOG_FILE=‘mysql-bin.000018’,
//【关键处】从文件中指定位置开始读取，主库show master status返回结果
MASTER_LOG_POS=154;最后的两个值需要跟主服务器一致
最后的两个值需要跟主服务器一致
5. 启动slave模式并查看状态
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.5.130
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000018
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000018
Slave_IO_Running: No
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: 154
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 130
Master_UUID:
Master_Info_File: /usr/local/data/mysql/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: 210209 01:47:26
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
这里如果一切正常，
Slave_IO_Running和Slave_SQL_Running应该都是Yes，如果是No或者Connecting都不行，可查看mysql-error.log，以排查问题。这里报错是说主从数据库的UUID是一样的，因为这两个是同一个虚拟机克隆出来的，这里需要修改一下从数据库的UUID，解决办法是进入数据库的datadir数据目录，里面会有一个auto.cnf文件，先将这个文件改名备份一个mv auto.cnf auto.cnf.bak，然后重启mysqld服务就会生成新的UUID
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.5.130
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000018
Read_Master_Log_Pos: 601
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 767
Relay_Master_Log_File: mysql-bin.000018
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: 601
Relay_Log_Space: 978
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: 130
Master_UUID: e2b43918-1fd7-11eb-9a1e-000c2960bd29
Master_Info_File: /usr/local/data/mysql/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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
这里就表明主从同步配置成功了
6. 测试主从同步是否生效
从服务器：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> select * form test;
Empty set (0.00 sec)
主服务器：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> select * form test;
Empty set (0.00 sec)
mysql> insert into test (id) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
从服务器：
mysql> select * from test;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
以上便是mysql5.7搭建主从配置的全过程
补充
- 如果【主服务器】重启mysql服务，【从服务器】会等待与【主服务器】重连。当主服务器恢复正常后，从服务器会自动重新连接上主服务器，并正常同步数据。
- 如果某段时间内，【从数据库】服务器异常导致同步中断（可能是同步点位置不匹配），可以尝试以下恢复方法：进入【主数据库】服务器（正常），在bin-log中找到【从数据库】出错前的position，然后在【从数据库】上执行change master，将master_log_file和master_log_pos重新指定后，开始同步。
扫一扫
468
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
