MySQL主从复制

不听话的北北

已于 2023-01-28 16:51:50 修改

阅读量104

点赞数

文章标签： mysql 数据库 sql

于 2023-01-28 16:34:18 首次发布

本文链接：https://blog.csdn.net/qq_45768236/article/details/128779045

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章目录

1. 服务器准备

1.1.说明

本文档适用于windows server 服务器，数据库适用MySQL5.6或5.7,未测试MySql8.0版本

1.2.主库

主机：42.192.6.7
端口：3307
数据库：MySql5.6
账号：root
密码: XXXXX
数据库对外备份账号:backup
数据库对外备份账号密码：public@XXXXXX

1.3.从库

主机：124.222.83.104
端口：3306
数据库：MySql5.7
账号:backup
密码:backup@XXXXXX

1.4.工具

Navicat for MySQL或Navicat Premium 16

2. 主库配置

使用远程桌面连接连接主库。按win+R键，输入mstsc回车。在弹出对话框中输入远程主库的IP地址和账号密码。
找到主库的配置文件。配置文件如图所示。
添加以下配置,下面配置可放在配置文件中任意位置。要注意server_id已经存在了。只需要修改它的值就行了。如图所示。

# 配置要给Slave同步的数据库，如果没有就注释掉，默认没有，此时同步所有数据库，Test表示表名称
binlog-do-db=test
# 不用给Slave同步的数据库，一般是Mysql自带的数据库就不用给Slave同步了
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 自动清理30天前的log文件
expire_logs_days=30
# 启用二进制日志
log-bin=mysql-bin
# Master的id，这个要唯一，唯一是指在主从中唯一，注意server_id在配置文件中已存在
server-id=1

请注意，保存的编码格式是ANSI，不是utf-8。如果碰到Mysql重启失败的情况，可能是保存文件的时候保存为utf-8编码，重新保存为ANSI编码就行。
在修改完配置文件之后一定要重启MYSQL服务。重启MySql服务器有两种方式。

A. 以管理员身份运行命令行提示符输入net stop mysql56提示关闭成功后，输入net start mysql56。请注意net stop和net start后面跟的是服务名称，要停止服务执行net stop 服务名称,要重启服务执行net start 服务名称。
重启MySQL服务
B. 在搜索栏搜索服务，找到mysql56服务，重新启动。
重启Mysql服务器

3. 主库检验

使用1.4中的MySql连接工具连接MySql主库。
在主库中执行查询show master status;得到如图所示查询结果。File和Position不一定同本图保持一致，Binlog_Do_DB是指定备份的数据库，当前为空表示备份全部数据库。Binlog_Ignore_DB表示忽略的数据库，当前忽略系统自带的数据库。
如果在执行查询后得到上述一条数据，恭喜你，成功了一半，如果查询结果为空，则配置失败，请按步骤执行上述步骤。
show variables like ‘%log_bin%’;执行本命令之后可以查看数据库日志是否配置成功，如图所示位置出现ON表示成功。如果出现OFF表示配置失败，请在主库中启用二进制日志。

4. 主库账号准备

4.1. 创建一个账号用来给从库连接

在使用MySQL连接工具登录主库，新建查询，复制粘贴创建语法,创建语法为

create user 'backup'@'%' IDENTIFIED BY 'public@123456';
grant file on *.* to 'backup'@'%';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'backup'@'%';

Create user ‘backup’@’%’表示创建一个backup用户，允许所有人连接。%如果指定IP地址，则该IP主机才能连接。
IDENTITFIED By ‘public@123456’表示指定连接密码为public@123456
grant file on . to ‘backup’@‘%’;授权所有的数据库文件给到’backup’@’%’账号
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . to ‘backup’@‘%’;表示授权’backup’@’%’账号备份数据库的权限

5. 主库从库数据同步

在开始从库配置前需要先确保两台服务器的数据已经同步。
使用MySQL连接工具连接主库和从库。
如果主库里面有数据，先将主库里面的数据备份到从库，确保两个库里面的数据保持一致，如果没有数据，请确保主库的表结构和从库的表结构保持一致。
A. 可右键数据库转存SQL文件->选择结构和数据或仅结构。将结构或数据导出为SQL文件
B. 或新建备份，将备份复制粘贴到从库
在执行完步骤4账号准备之后，以只读锁定主表flush tables with read lock，确保在配置完主库和从库主从备份完成之前数据一致。
在主从库配置完成之后，执行UNLOCK TABLES解锁表。

6. 从库配置

使用远程桌面连接从库。按win+r输入mstsch回车，在弹出对话框中输入远程IP、账号和密码。点击连接。
找到从库的my.ini配置文件
添加以下配置,该配置允许添加到配置文件的任意位置。请注意server_id不能与主库一致，且默认已经被设置为1。

# 配置从服务器的ID，唯一的，不能与主库中的ID一致
server-id=2
# 也启用二进制日志
log_bin=slave_bin
log_bin_index=slave_bin.index
# slave将复制事件写进自己的二进制日志
log_slave_updates=1
relay_log=relay_bin
relay_log_index=relay_bin.index
# 加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错。加上一下参数后，从库会变成只读库，read_only=1表示普通用户只读,super_read_only=1
read_only = 1
super_read_only=1
# 这两个是启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。
master_info_repository=TABLE
relay_log_info_repository=TABLE

保存文件的格式是ANSI，不是utf-8。同主库的保存一致。
保存文件后需要重启MySql服务器。重启方式同上。
在配置完上述步骤后在从库里面执行命令

change master to master_host='42.192.6.7',master_port=3307,master_user='backup',master_password='public@123456',master_log_file='mysql-bin.000002',master_log_pos=750; bbbbbbbb bbbnnnnnnnnnnnnnnn

请注意：Master_host是主机IP，master_port是主机端口号，master_user是用户名，master_password是用户密码,master_log_file是配置的日志文件与master_log_pos同为主库中执行show master status中查询结果一致。

7. 从库检验

执行show slave status ,得到如图所示结果，如果Slave_IO_Running和Slave_SQL_Running都为Yes则从库配置成功。最终结果为两个都为Yes,主库从库配置成功，你可以在主库中添加一条数据验证。如果有一个为Yes，另一个为No，重新执行6.5，目的是为了确保master_log_file和master_log_pos与主库保持一致。如果都为No，从库配置失败，重新检查配置。如果确定配置成功了，但是都为No,执行命令stop slave;然后执行start slave;