mysql数据库主从复制（读写分离）

• 前言

随着用户量的增多，数据库操作往往会成为一个系统的瓶颈所在，而且一般的系统“读”的压力远远大于“写”，因此我们可以通过实现数据库的读写分离来提高系统的性能。

• 主从复制的作用：

一是确保数据安全；做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据的丢失。
二是提升I/O性能；随着日常生产中业务量越来越大,I/O访问频率越来越高，单机无法满足，此时做多库的存储，有效降低磁盘I/O访问的频率，提高了单个设备的I/O性能。
三是读写分离，使数据库能支持更大的并发；在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

• 实现思路

通过设置主从数据库实现读写分离，主数据库负责“写操作”，从数据库负责“读操作”，根据压力情况，从数据库可以部署多个提高“读”的速度，借此来提高系统总体的性能。

• 基础知识

要实现读写分离，就要解决主从数据库数据同步的问题,在主数据库写入数据后要保证从数据库的数据也要更新

主服务器master记录数据库操作日志到Binary log，从服务器开启i/o线程将二进制日志记录的操作同步到relay log（存在从服务器的缓存中），另外sql线程将relay log日志记录的操作在从服务器执行。
记住这张图，接下来基于这个图实际设置主从数据库

主从数据库设置的具体步骤

• 打开mysql数据库配置文件

vim /etc/my.cnf

• 在主服务器master上配置开启Binary log，主要是在[mysqld]下面添加：

server-id=1
log-bin=master-bin
log-bin-index=master-bin.index

• 重启mysql服务

service mysqld restart

• 检查配置效果，进入主数据库并执行

mysql> SHOW MASTER STATUS;
这里的 File 和 Position 在从数据库配置会用到

• 配置从服务器的 my.cnf

server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

• 重启从数据库

接下来配置两个数据库的关联

• 主数据库创建用户：

mysql> create user repl; (create user ‘myl’@‘%’ IDENTIFIED BY ‘123456’)
mysql> GRANT REPLICATION SLAVE ON . TO ‘repl’@‘从xxx.xxx.xxx.xx’ IDENTIFIED BY ‘mysql’;
mysql> flush privileges;

这个配置的含义就是创建了一个数据库用户repl，密码是mysql, 在从服务器使用repl这个账号和主服务器连接的时候，就赋予其REPLICATION SLAVE的权限， . 表面这个权限是针对主库的所有表的，其中xxx就是从服务器的ip地址。

• 进入从数据库后执行：

mysql> change master to master_host=‘主xxx.xxx.xxx.xx’,master_port=3306,master_user=‘repl’,master_password=‘mysql’,master_log_file=‘master-bin.000001(上面主库中File)’,master_log_pos=0(上面主库中Position);

• 执行start slave启动配置

mysql> start slave;

• 查看状态命令

mysql> show slave status \G;

• 可以看到状态如下：
  

这里看到从数据库已经在等待主库的消息了，接下来在主库的操作，在从库都会执行了。我们可以主库负责写，从库负责读（不要在从库进行写操作），达到读写分离的效果。

• 测试：

在主数据库中创建一个新的数据库：
mysql> create database testsplit;

在从数据库查看数据库：
mysql> show databases;

可以看到从数据库也有testsplit这张表了，这里就不上图了，亲测可用。在主数据库插入数据，从数据库也可以查到。
至此已经实现了数据库主从同步

• docker 高级篇也进行了容器中主从复制（数据库的读写分离）

• 遇到的问题：

错误1：
mysql同步复制报Slave can not handle replication events with the checksum that master 错误
同步的时候报错了：
Slave_IO_Running: No
Slave_SQL_Running: Yes
解决：
在master1中设置binlog_checksum =none；
重启mysql
重新查询master状态，show master status\G;
set global binlog_checksum=‘NONE’;
show variables like ‘%checksum%’;
添加 my.cnf 配置文件中添加如下设置，下次重启就可以不用做步骤1，直接生效了。
binlog_checksum=NONE
重新查询master状态：show master status\G;

错误2：
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work
解决：
1.查看/etc/my.cnf中server-id=1是否一致，如果一致需要修改； show variables like ‘server_id’;
2.查看/www/server/auto.cnf(这是我服务器目录)中，service-uuid是否一致，如果一致需要删除从库的，这个文件中的是自己生成，不需要自己去修改。

错误3：
主从同步遇到 Got fatal error 1236 from master when reading data from binary log: 'Could not find first log…
解决：
1.打开主服务器，进入mysql
2.执行flush logs；//这时主服务器会重新创建一个binlog文件；
3.在主服务上执行SHOW MASTER STATUS;;显示：File 和 Position
4.来到从服务器的mysql；
5.stop slave;
6.change master to master_log_file=‘mysql-bin.000012’,master_log_pos=154;//这里的file和pos都是上面主服务器master显示的。
7.start slave;//这时候就应可以了
8.show slave status \G;