Mysql数据库主从同步详解

Mysql数据库设置主从同步

MySQL数据库的主从复制是一种常见的数据备份和高可用性解决方案。通过配置主从复制，可以实现将数据从一个MySQL服务器（主服务器）同步到另一个（从服务器）。

前言

mysql数据库的主从同步设置需要修改主库和从库的配置文件，并执行同步指令，步骤并不复杂。但是在部署过程中还是遇到一些问题，找了很久好像没人遇到相同的问题，因此将部署流程与遇到的问题分享出来，希望遇到同样问题时有参考依据。

1.主库（主服务器）配置

1.1修改主库配置文件，启用二进制日志

数据库配置文件所在目录：
CentOS7安装mysql后，配置文件默认路径为：/etc/my.cnf
Windows安装mysql后，配置文件默认路径为：C:\ProgramData\MySQL\MySQL Server 5.7
本次安装的主服务器CentOS7中，因此需要进入到/ect目录下，修改my.cnf配置文件：

#进入etc目录下
cd /etc
#编辑mysql配置文件
vi my.cnf

在配置文件[mysqld]最后一行，添加以下内容：

#mysql主库配置
server_id = 88               #集群唯一标识，主库从库不能重复(值为数据库IP)
log_bin = mysql-bin          #开启二进制日志
expire_logs_days = 7         #日志有效期（天）

内容说明：
#集群唯一标识，主库从库不能重复，建议值取数据库IP，避免重复（该项必须配置）
server_id = 88
#开启mysql二进制日志（该项必须配置）
log_bin = mysql-bin
#设置同步日志有效期（天），到期自动清理，避免磁盘占用空间过大（该项建议配置）
expire_logs_days = 7

1.2重启数据库服务

Mysql数据库修改my.cnf配置文件后，需要重启数据库才能使修改的配置文件生效：

systemctl restart mysqld

重启数据库后，可以查看数据库的运行状态，确保数据库正常运行(active)：

systemctl status mysqld

1.3创建远程连接账户

从库同步主库的数据，那么从库需要先连接到主库。初始的root账户只能在主库服务器中登录，在从库所在服务器中使用该账户是无法登录的，因此需要创建一个远程连接的账户：
登录数据库：mysql -uroot -p数据库密码
在Windows中直接通过命令行登录到mysql，需要将mysql添加到环境变量。
本次创建一个远程连接账户’slave’，密码为’test’(可以根据自己需要创建不同的账户密码)，赋予账户只有复制权限：

方法一（分步执行）：

创建mysql账户：

CREATE USER 'slave'@'%' IDENTIFIED BY 'test';

%表示账户开通远程连接，允许所有IP通过该账户登录数据库。

授权该账户只有复制的权限：

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

REPLICATION SLAVE 表示仅给该账户复制的权限，*.*表示可以该账户的权限对所有的数据库和数据表都有效，可以复制所有数据库和表，%表示所有IP都可以通过该账户连接到数据库。

刷新权限：

flush privileges; 

方法二（合并执行）：

创建一个账户只有复制权限(slave)，权限对所有数据库和表生效(*.*)，‘slave’账号，密码为’test’:

grant replication slave on *.* to 'slave'@'%' identified by 'test';

1.4查看master状态，记录二进制文件名(File)和位置(Position)：

复制主机的数据库，需要先查询主机数据库二进制日志的文件名和文件所在位置的，命令如下：

SHOW MASTER STATUS;

1.5停止主机数据库的写操作

注意：从机开始同步之前，主机不能再进行写操作，如果主机仍在进行写操作，会导致同步失败，导致同步无法继续执行。
因此，建议在执行同步之前，先把所有连接到mysql数据库的jar、tomcat、中间件、exe程序全部停止，停止程序往主机继续写入数据，同步时确保数据的一致性。

2.从库（从机）配置

2.1修改从库配置文件

本次从机安装在Windows系统中，需要进入mysql的配置文件目录下，修改配置文件
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
{如果从机安装在CentOS7下，则需要修改/etc/my.cnf配置文件，添加以下内容}

简单的配置只需要需改从机的id即可完成。修改server-id，改成与主机不冲突的值（建议取从机的IP，避免冲突）。
进阶配置如下图所示：

#mysql从机配置
#从机唯一标识，与主库不能重复(值取IP地址)
server-id=66
#设置日志保存时长
expire_logs_days=7
#数据库宕机后自动恢复日志，从库建议开启，有利于数据一致性
relay_log_recovery=1

2.2重启从机Mysql服务

本次从机安装在Windows系统中，安装Mysql后会出现在系统服务中。修改配置文件后，需要重启系统服务才能适用新的改动：

如果从机安装在CentOS7下，则通过命令重启Mysql:

systemctl restart mysqld

2.3导入数据库

注意：如果已停止所有程序对数据库的写入操作，则可以直接将数据库全部同步过来，无需创建导入数据库，此步骤可忽略进入下一步。直接看下一个步骤：2.4开始主从同步。

由于本次同步时，未停止程序对数据库写入操作，导致在使用中的数据库无法同步到从机中，因此需要手动导入数据库。虽然设置了主从同步，但是如果主库未停止写入，从库并不会将主机在使用中的数据库直接同步复制到从机。因此必须先将主机的数据库导出，再导入到从机的数据库中。从机先创建数据库，新创建的数据库名字、字符集、排序规则必须和主库原有的数据库相同。然后再执行下一步。

数据库创建好后，将主机中导出的数据库导入到从机中：

2.4开始主从同步

重启Mysql后，可以设置从机的数据库同步到主机。设置同步到的主机信息（连接的IP和账户），执行从机执行同步命令。

登录从机数据库：mysql -uroot -p数据库密码

登录成功后，在从机数据库命令行执行以下指令，设置主机的连接参数。使从机可以连接到主机：

change master to master_host='主机IP',master_user='数据库账户',master_password='数据库密码',master_log_file='数据库二进制文件',master_log_pos=文件位置参数;

从机连接到主机后，开始同步，从机复制主机的二进制日志：

start slave;

2.5查看从机状态

注意：从机开始同步之前，主机不能再进行写操作，如果主机仍在进行写操作，会导致同步失败，Slave_SQL_Running栏显示为NO，Last_Error会重复出现报错，导致同步无法继续执行。

#查看从机状态
show slave status;

如果在数据库命令行中执行该命令，回显的信息是没有分行的，行显示的数据错乱，可以通过增加“\G”分行展示从机状态：

#分行查看从机状态
show slave status\G;

如果从机的状态如下图所示，则说明同步是成功的：

2.6同步报错解决办法一

注意：从机开始同步之前，主机不能再进行写操作。如果主机仍在进行写操作，会导致同步失败，Slave_SQL_Running栏显示为NO，Last_Error会重复出现报错代码1032，导致同步无法继续执行。

那么怎么停止主机的写操作呢？停止主机的写操作，一定要在主机上停止所有的连接到数据库的程序和中间件，让程序不再往数据库中写入数据即可。

主机中往数据库写的操作停止后，从机再重新执行同步操作：

start slave;

2.7同步报错解决办法二

查阅了许多资料，大佬们的做法是这样的，从机登录数据库后执行以下命令：

 #sql_slave_skip_counter =1表示跳过1步错误，后面的数字可变，出现多少个报错则把数字改成这个数量可以跳过报错继续同步
stop slave;
set global sql_slave_skip_counter =1;  
start slave;

但是我按照大佬们的操作，还是没解决问题。如果错误比较多，或者中途还是会出现报错，导致无法完成主从同步，那么建议修改从机的数据库配置文件，跳过所有1032代码的错误：

在配置文件中[mysqld]添加以下内容：

#该方法用于跳过所有1032错误
slave-skip-errors=1032

如果想跳过其他的错误代码，可以把slave-skip-errors后面的代码替换为出现的错误代码。更粗暴的是，可以跳过所有的错误代码：

#该方法用于跳过所有错误代码
slave-skip-errors=all

修改配置文件之后，重新启动mysql数据库。

重启数据库之后，从机中重新执行主从同步命令：

start slave;

3.同步测试

在主机中新建数据库，在数据库中添加数据表，查看从库中是否将主库修改的内容同步过来：

主机新增了“ibms_ksy”数据库，同时导入了数据库报表。我们查看从机是否同步了新增的数据库：

可以看到，从机同步了主机新增的数据库，并将主库中的数据表也同步了过来。

参考文献