MySQL主从复制实现读写分离
MySQL主从复制实现读写分离
1. 读写分离原理
1.1 什么是主从同步
指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。其实就是从服务器重放主服务器的操作步骤，实现主从数据一致。
1.2 为什么要实现主从同步
（1）实现服务器负载均衡
通过主、从服务器之前切分处理客户查询符合，从而得到更好的客户相应时间。主要方案有：
- master处理记录的新增、修改、删除的功能，把查询交给slave去做。
- master处理记录的新增、修改、删除的功能，并且master还要承担部分查询任务，而slave只负责查询数据。当主服务器比较忙时，部分查询请求会自动发送到从服务器重，以降低主服务器的工作负荷。
（2）实现数据的异地备份
定期的将数据从master 服务器上复制到slave服务器上，实现了异地备份。相对于将数据备份在本地，这种方式可以避免降低master服务器的性能，同时降低了因为master服务器硬盘损坏、服务器异常而导致数据无法恢复的危险。
（3）提高数据库系统的可用性
当主服务器出现问题时，可以马上让从服务器作为主服务器，用来数据的更新与查询服务。
1.3 怎么实现主从同步
mysql支持的复制类型：
- 基于语句的复制： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时， 会自动选着基于行的复制。
- 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
- 混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。
主从复制工作原理剖析
1.Master 数据库只要发生变化，立马记录到Binary log 日志文件中
2.Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志
3.Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中。
4.Slave 有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据
2. 数据库环境准备
这里我准备了两台服务器（centos7），在上面都安装了mysql5.7.30，两台服务器网络畅通：
主服务器（master）：192.168.0.101
从服务器（slave）：192.168.0.105
将主库要同步的数据库数据先同步到从库。
3. master配置
3.1 修改mysql配置文件
在msyql配置文件中添加一下配置(主要配置server-id、log-bin和binlog-do-db)：vim /etc/my.cnf
server-id= 1 # 节点ID，确保唯一
# log config
log-bin= mysql-bin #开启mysql的binlog日志功能
binlog-do-db=oyc #二进制日志记录的数据库（多数据库用逗号，隔开）
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
sync_binlog= 1 #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format= mixed #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days= 7 #binlog过期清理时间
max_binlog_size= 100m #binlog每个日志文件大小
binlog_cache_size= 4m #binlog缓存大小
max_binlog_cache_size= 512m #最大binlog缓存大
auto-increment-offset= 1 # 自增值的偏移量
auto-increment-increment= 1 # 自增值的自增量
slave-skip-errors= all #跳过从库错误
3.2 重启mysql
systemctl restart mysql
或者 service mysqld restart
3.3 创建同步用户并授权
1.进入master的数据库，为master创建复制用户
create user oyc identified by '123456';
2.赋予该用户复制的权利
grant replication slave on *.* to 'oyc'@'192.168.0.105' identified by '123456';
flush privieges;
3.查看master的状态
show master status;
4. slave配置
4.1 修改从库配置文件
同步表配置说明：
replicate-do-db 设定需要复制的数据库（多数据库使用逗号，隔开） replicate-ignore-db
设定需要忽略的复制数据库 （多数据库使用逗号，隔开） replicate-do-table 设定需要复制的表
replicate-ignore-table 设定需要忽略的复制表 replicate-wild-do-table
同replication-do-table功能一样，但是可以通配符 replicate-wild-ignore-table
同replication-ignore-table功能一样，但是可以加通配符
[mysqld]
server-id = 2 # server_id是必须的，而且唯一。
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-do-db=oyc
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
4.2 配置从库连接信息
让slave连接master，并开始重做master二进制日志中的事件。
CHANGE MASTER TO MASTER_HOST='192.168.0.100',MASTER_USER='oyc',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;
4.3 启动Salve并查看从库状态
START SLAVE;
SHOW SLAVE STATUS;
看到Slave_IO_Running=Yes Slave_SQL_Running=Yes，说明成功了。
5.测试效果
6.同步错误解决
6.1 忽略错误继续同步
该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况。
stop slave;
# 表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
start slave;
# 查询同步情况
show slave status\G
6.2 忽略不需要的表
replicate-ignore-table=table1，table2，oyc_%
6.3 重新同步数据再实现同步
1.先进入主库，进行锁表，防止数据写入
使用命令：
mysql> flush tables with read lock;
注意：该处是锁定为只读状态，语句不区分大小写
2.进行数据备份
把数据备份到mysql.bak.sql文件
[aaa@qq.com mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql
这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者python脚本，都比较方便，确保数据万无一失
3.查看master 状态
mysql> show master status;
+——————-+———-+————–+——————————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————————-+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+——————-+———-+————–+——————————-+
1 row in set (0.00 sec)
4.把mysql备份文件传到从库机器，进行数据恢复
使用scp命令
[aaa@qq.com mysql]# scp mysql.bak.sql aaa@qq.com:/tmp/
5.停止从库的状态
mysql> stop slave;
6.然后到从库执行mysql命令，导入数据备份
mysql> source /tmp/mysql.bak.sql
7.设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项
change master to master_host = ‘192.168.1.206’, master_user = ‘rsync’, master_port=3306, master_password=”, master_log_file = ‘mysqld-bin.000001’, master_log_pos=3260;
8.重新开启从同步
mysql> start slave;
9.查看同步状态
mysql> show slave status\G 查看同步情况