MySQL主从设置

---

读写分离原理

MySQL要做到主从复制，其实依靠的是二进制日志。

举例:假设主服务器叫A，从服务器叫B;主从复制就是B跟着A学，A做什么，B就做什么。那么B怎么同步A的动作呢﹖现在A有一个日志功能,把自己所做的增删改(Insert、Delete、Update)的动作，全都记录在日志中，B只需要拿到这份日志，照着日志上面的动作施加到自己身上就可以了。这样就实现了主从复制。

配置主从

1. 主机MySQL配置

1. 修改MySQL my.cnf 文件或my.ini

[mysqld]
log-bin=mysql-bin #[必须]启用二进制日志
binlog_format=mixed  #二进制日志的格式，有三种
server_id=52 #[必须]和从数据库不能是同一ID
expire-logs-days=10  # [可选]binlog日志保留的天数，清除超过10天的日志 防止日志文件过大，导致磁盘空间不足

2. 重启MySQL服务

systemctl restart mysqld 

3. 分配账号

mysql> create user 'slave'@'%' identified  by 'slave123';  -- 创建用户
mysql> grant  REPLICATION SLAVE on *.* to slave@'%' with grant option;  -- 授权
# 如果授权出现异常：Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
mysql> grant system_user on *.* to 'root'; #则先执行给root，SYSTEM_USER权限然后在执行授权
mysql> grant  REPLICATION SLAVE on *.* to slave@'%' with grant option; 

• replication slave：分配的权限
• ‘.’：可操作的数据库
• ‘slave’： 用户名
• ‘%’：可以在那台电脑登录
• ‘slave123’：密码

4. 查看配置状态

mysql> show master status;

.

2. 从机MySQL配置

1. 修改MySQL my.cnf或my.ini

[mysqld]
log-bin=mysql-bin #[不是必须]启用二进制日志
binlog-do-db=test #同步的数据库,同步多个数据库就写多行
server_id=131 #唯一id，默认是1，不能与主数据库一致

2. 重启MySQL服务

systemctl restart mysqld 

3. 关闭slave(一定要先关闭)

mysql> stop slave

4. 开始配置

mysql> change master to
master_host='192.168.127.52',
master_user='slave',
master_password='slave123',
master_log_file='mysql-bin.000010',
master_log_pos=157;

• master_host：要连接的主服务器ip地址
• master_user：要连接主服务器用户名
• master_password：要连接主服务器密码
• master_log_file：要连接的主服务器的bin日志的日志名称，即配置主机MySql第四步可得到该信息
• master_log_pos：要连接的主服务器的bin日志的记录位置，即配置主机MySql第四步可得到该信息

5. 启动slave同步

mysql> start slave

6. 检查从服务器复制功能状态

mysql> show slave status;

结果如下即成功：

• Slave_IO_Running=Yes
• Slave_SQL_Running=Yes
  

配置踩坑

1. 主从数据库的加密插件要设置成一致
   如主数据库的默认加密插件是 ‘mysql_native_password’,则从数据库的默认加密插件也使用‘mysql_native_password’，否则同步数据的时候会出各种问题(亲身体验)。
2. 修改密码类型SQL

ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave123';