Mysql主从同步详解
Mysql数据库设置主从同步前言1.主库（主服务器）配置1.1修改主库配置文件，启用二进制日志1.2重启数据库服务1.3创建远程连接账户1.4查看master状态，记录二进制文件名(File)和位置(Position)：1.5停止主机数据库的写操作
2.从库（从机）配置2.1修改从库配置文件2.2重启从机Mysql服务2.3导入数据库2.4开始主从同步2.5查看从机状态2.6同步报错解决办法一2.7同步报错解决办法二
3.同步测试参考文献
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开始主从同步
重启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”数据库，同时导入了数据库报表。我们查看从机是否同步了新增的数据库： 可以看到，从机同步了主机新增的数据库，并将主库中的数据表也同步了过来。
参考文献
好文推荐
您阅读本篇文章共花了：
发表评论