MySQL主从复制(也称A/B复制)的原理
(1) Master将数据改变记录到二进制日志(binary log)中,也就是配置文件log-bin指定的文件,这些记录叫做二进制日志事件(binary log events);
(2) Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log);
(3) Slave重做中继日志中的事件,把中继日志中的事件信息一条一条的在本地执行一次,完成数据在本地的存储,从而实现将改变反映到它自己的数据(数据重放)。
主从配置需要注意的点
(1)主从服务器操作系统版本和位数一致;
(2) Master和Slave数据库的版本要一致;
(3) Master和Slave数据库中的数据要一致;
(4) Master开启二进制日志,Master和Slave的server_id在局域网内必须唯一;
1. master服务器配置
1.1 在master 服务器授权一个账户,拥有slave权限
mysql > GRANT REPLICATION SLAVE ON *.* TO 'username'@'IP' IDENTIFIED BY 'password';
mysql > FLUSH PRIVILEGES;
★ 其中 IP 可以为模糊匹配,如192.168.1.% 或具体某个IP
1.2. 修改mysql配置文件,my.ini (windows)或 my.cnf(Linux)找到[mysqld] 标记,下方添加
// 如果server-id-0 则会拒绝所有从库链接
server-id=1 // 一般用当前服务器ip的最后ip端标记
// binlog日志
log-bin=master-bin
// binlog的索引文件(此项不配置也可,mysql会自动根据上面配置,在后缀加上.index)
log-bin-index=master-bin.index
# 复制过滤:需要备份的数据库,输出binlog 多个数据库,添加多条即可
binlog-do-db=test
# 复制过滤:不需要备份的数据库,不输出(mysql库一般不同步,多个数据库,添加多条即可)
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
# 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存(可不配)
binlog_cache_size=1M
# 主从复制的格式(mixed,statement,row,默认格式是statement)
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days=7
1.3 可以查看日志
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 1285 | | |
+-------------------+----------+--------------+------------------+
1.4 重启master 的mysql服务
2. 在另一个mysql上配置从服务器
2.1 找到my.ini (windows) 或 my.cnf (linux),找到[mysqld]标记,更改如下配置
// 一般同理,是当前及其的ip最后段
server-id=2
// 从库日志索引
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
// 从库要执行同步的数据库,多个数据库写多个replicate_do_db
replicate-do-db=test
replicate-ignore-db=mysql
replicate-do-table
2.2 重启mysql 执行
start slave,
可以使用 show slave status查看运行状态
2.3 执行从库配置 每次重启mysql均需要执行(如果没有开启自动启动主从)
然后在每个从库服务器上执行如下语句:
mysql> change master to master_host='192.168.1.205',master_user='username',master_password='password',master_port=3306,master_log_file='edu-mysql-bin.00000x',master_log_pos=xx,master_connect_retry=30;
解释:
// master的ip地址 mysql> change master to master_host='192.168.1.205', // master授权的用户 master_user='username', // master的授权用户密码 master_password='password', // master的访问端口 不要带引号,必须是整型,否则会报错 master_port=3306, // master的binlog日志名称,这里使用上述“1.3”命令搜索出来的为准 master_log_file='edu-mysql-bin.00000x', // master的日志位置 这里使用上述“1.3”命令搜索出来的为准,不能带引号,必须是整型,否则会报错 master_log_pos=xx, // 重试时间、单位秒 master_connect_retry=30;
2.4 启动主从进程
mysql > start slave;
2.5 可以检查从库状态
mysql > show slave status \G
只要出现如下两个yes,即表示主从通信正常
主从同步可能遇到的问题
基于局域网的Master/Slave机制在通常情况下已经可以满足“实时”备份的要求了。如果延迟比较大,可以从以下几个因素进行排查:
(1) 网络延迟;
(2) Master负载过高;
(3) Slave负载过高;
laravel项目中配置主从同步,官方文档为: