MySQL设置主从同步及遇到问题处理

1.MySQL设置主从同步

首先在两个系统安装mysql，CentOS默认带的是mariadb，yum install mariadb -y

默认主库已经存在数据，在本例中要将test2021数据库同步到从库,主库系统：192.168.1.10，从库系统192.168.1.11

一、配置主库

1.在主库中创建账户并授权给从库服务器，刷新权限(账户在从库同步主库时用到)

GRANT REPLICATION SLAVE ON *.* to 'test'@'192.168.1.11' identified by 'test123456';

FLUSH PRIVILEGES;

2.主库开启二进制日志，在配置文件/etc/my.cnf中设置如下：

log-bin=/var/lib/mysql/logbin #开启二进制日志

server-id=1 #主库

binlog-do-db=test2021 #同步的数据库是test2021

log-bin-index=/var/lib/mysql/logbinindex.index #后续报错1236添加上了二进制日志index参数

保存退出，并重启mysql服务：systemctl restart mariadb

3.进入主库，查看master状态 SHOW MASTER STATUS;记录二进制日志的参数FILE及POSITION参数，从库设置需要使用

MariaDB [(none)]> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| logbin.000001 |      245 | test2021      |                  |
 

二、配置从库

1.设置配置文件/etc/my.cnf

server-id=2;

保存退出，重启mysql服务

2.mysql5.5以上不支持在配置文件设置参数，在mysql里面设置同步参数

CHANGE MASTER TO MASTER_HOST='192.168.1.10',

MASTER_USER='test',

MASTER_PASSWORD='test123456',

MASTER_PORT=3306,

MASTER_LOG_FILE='logbin.000001',

MASTER_LOG_POS=245;

3.启动从库并查看同步状态

start slave;

show slave status\G;

Slave_IO_Running和Slave_SQL_Running都为YES同步成功

三、备份主库数据传到从库

停止主库的数据更新操作：flush tables with read lock;

备份数据库：mysqldump -uroot -p123456 test2021 > test2021.sql

传输到从库：scp test2021.sql root@192.168.1.11:/root/

主库解锁：unlock tables;

从库创建同名数据库:create database test2021

停止同步：slave stop;

将SQL还原到test2021:mysql -uroot -p test2021 < test2021.sql

查看主库的master状态，修改从库参数，再开启从库

此时对主库进行增删改查，从库同步

四、报错调试

1.1236报错

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

停止从库同步：stop slave;

在主库配置文件增加bin-log-index;

重启主库服务

主库：flush logs;Show master status\G; #刷新主库的二进制日志，并查看当前的二进制日志和position

从库：CHANGE MASTER TO MASTER_LOG_FILE='logbin.000006',MASTER_LOG_POS=245; 修改从库同步的二进制日志参数和pos参数，数值与主库查看一致

从库：start slave;show slave status\G; #开启从库并查看同步状态

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

上面两个参数都为 YES,则主从同步正常

2.Last_SQL_Error报错

Last_SQL_Error: Error 'Table 'test619.tb_eventtest' doesn't exist' on query. Default database: 'test619'. Query:

'INSERT INTO tb_eventtest(user,createtime) VALUES('MySQL',NOW())'

解决办法：

从库：stop slave;

主库：flush logs;Show master status\G; #刷新主库的二进制日志，并查看当前的二进制日志和position

从库：CHANGE MASTER TO MASTER_LOG_FILE='logbin.000006',MASTER_LOG_POS=245; 修改从库同步的二进制日志参数和pos参数，数值与主库查看一致

从库：start slave;show slave status\G; #开启从库并查看同步状态

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

上面两个参数都为 YES,则主从同步正常
 

主从设置部分参考https://www.cnblogs.com/zhoujie/p/mysql1.html 作者：zhoujie