摘要

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），
而从数据库处理 SELECT 查询操作。 数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
至少两台数据库服务器，可以分别设置主服务器和从服务器，对主服务器的任何操作都会同步到从服务器上

原理

mysql 中有一种日志，叫做 bin 日志（二进制日志），会记录下所有修改过数据库的 sql 语句。 主从复制的原理实际是多台服务器都开启 bin 日志，然后主服务器会把执行过的sql 语句记录到 bin 日志中，之后把这个 bin 日志发给从服务器， 在从服务器再把 bin 日志中记录的 sql 语句同样的执行一遍。 这样从服务器上的数据就和主服务器相同了。

纸上得来终觉浅，还是实际操作一下。

测试环境

主服务器

主服务器：127.0.0.1:3333
主服务器在docker上，3306映射到本地的3333端口。

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.33-log |
+------------+
1 row in set (0.00 sec)

从服务器

从服务器：127.0.0.1:3306
win下本地的mysql作为从服务器。

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.12 sec)

配置

bin-log日志的使用

首先我们来认识几个与bin-log相关的MySQL命令：

flush logs; 会多一个最新的bin-log日志
show master status; 查看最后一个bin-log日志的相关信息
reset master; 清空所有的bin-log日志

这些指令等下用得着。

主服务器配置

mysql的配置文件在linux下文件名为my.cnf。

> whereis my.cnf
my: /etc/my.cnf

在my.cnf里面加上如下代码

# vim /etc/my.cnf
server-id = 1  //数据库ID号
log-bin=mysql-bin  //启用二进制日志
#log-bin-index=mysql-bin.index  //二进制日志名称

这里注意不要放在文件的末尾，要放在前面，即[mysqld]后，

配置完成后，查看状态：

#登录 mysql
> mysql -hlocalhost -uroot -proot12345
## 退出
# \q 
# 登录后（mysql命令必须以;结束）
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录下File和Position两个内容，从库配置的时候会用到这个。

开启后，会生成mysql-bin.*文件，可以看一下存放地址

> find / -name mysql-bin.*
/www/server/data/mysql-bin.000005
/www/server/data/mysql-bin.000003
/www/server/data/mysql-bin.000004
/www/server/data/mysql-bin.index
/www/server/data/mysql-bin.000001
/www/server/data/mysql-bin.000002

从服务器配置

理论配置：

log-bin = mysql-bin
server-id = 2

我从服务器是win10的phpstudy，在phpstudy可以直接配置：

配置完成后查看my.ini文件

关联主从库

登录从库的MySQL命令行，执行以下代码，主要是关联主库的一些信息。

mysql> change master to master_host='127.0.0.1', #Master 服务器Ip
master_port=3333,
master_user='root',
master_password='root12345', 
master_log_file='mysql-bin.000005',  #Master日志文件名
master_log_pos=154; #Master日志同步开始位置
Query OK, 0 rows affected (0.08 sec)

# `master_log_file`和`master_log_pos`是刚才执行`show master status;`的两个值。

注意是否执行成功，如果执行失败就好好检查下代码，看看哪里写错了。
如果执行正常，就启动从库slave，并查看下连接状态。

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G; //查看slave连接状态

# 在`Navicate`中命令行查询语句带`\g`或`\G`格式化显示报错，所以，我这边没有格式化，执行的是：`show slave status;`,

我输出的结果有点乱，但相关的信息还是可以看到的，

这两个状态必须为Yes才算成功，如果不是，则检查上面步骤那一步配置错误。

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

配置的参数文件在phpstudy_pro\Extensions\MySQL5.7.26\data\master.info。
查看master.info里面的内容：

问题

Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user ‘xxx’@’xxx’ (using password: YES) (Errno: 1045)

原因

这是因为主库的账号密码错误;

可以在从库中测试连接:

# 以下是主库的ip和账号密码
mysql -h[ip] -u[账号] -p[密码]

如果连接失败,那就是账号密码问题了.
如果是宝塔用户的root账号,是无法在外部连接的,解决方法如下:

修改完在测试就可以了.

Last_Error: Error ‘Duplicate entry ‘4684533’ for key ‘PRIMARY’’ on query. Default database: ‘xxx’. Query: ‘INSERT INTO

原因

这是因为 File 和 Position,太久了,插入数据发生了索引冲突,
重新在主库中运行

show master status;

拿到最新的File 和 Position,

关闭

stop slave;

重新配置连接

change master to master_host='xxx',
master_port=xxx,
master_user='xxx',
master_password='xxx', 
master_log_file='xxx', 
master_log_pos=xxx;

重启

start slave;

查看

show slave status\G;

Mysql主从同步时Slave_IO_Running：Connecting ; Slave_SQL_Running：Yes的情况故障排除

Mysql主从同步时Slave_IO_Running：Connecting ; Slave_SQL_Running：Yes的情况故障排除

测试