MySQL主从复制

MySQL主从复制

在开始实际讲解MySQL主从复制如何实现之前，我们可以先思考一个问题！那就是，我们为什么要用数据库主从复制呢？它能为我们解决什么业务问题？在思考过后我们开始进入正题吧！

1.MySQL主从复制是什么？
主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

2.单台MySQL服务器的局限性
无热备数据库，当数据库服务器故障后，所有服务都将暂停，同时可能丢失未能及时存储的数据；
同样，随着业务的扩展，业务量越来越大，I/O频率过高，这使得数据库的性能越来越差。
3.主从复制解决的问题
3.1数据备份
提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据；

3.2高可用
因为数据库服务器中的数据都是相同的，当Master挂掉后，可以指定一台Slave充当Master继续保证服务的运行，因为数据是一致性的（如果当插入时Master就挂掉，可能不一致，因为同步也需要时间）当然这种配置不是简单的把一台Slave充当Master，毕竟还要考虑后续的Slave的数据同步到Master。
在主服务器上执行写入和更新，在从服务器上向外提供读功能，达到读写分离的效果，也可以动态地调整从服务器的数量，从而调整整个数据库的性能；读写分离详见另一篇文章
在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。
4.主从复制的原理

Master服务器将数据的改变记录二进制日志，当Master上的数据发生改变时，则将其改变写入二进制日志中，Salve服务器会在一定时间间隔内对Master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求Master二进制事件，同时主节点为每个I/O线程启动一个线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

5.配置实现主从复制
环境如下：

名称 版本 MySQL版本 IP
主数据库 Centos7.3 5.7 192.168.xx.xx
从数据库 Centos7.3 5.7 192.168.yy.yy
5.1主数据库配置
修改MySQL配置
查看数据库配置文件vi /etc/my.cnf，在[mysqld]下插入如下两行：

[mysqld]
log-bin=mysql-bin # 开启二进制日志
server-id=1 #设置server-id，需要唯一

可选参数如下(注意：使用时二选一)：

#不同步哪些数据库，除此之外别的都同步
binlog-ignore-db = information_schema 
binlog-ignore-db = mysql

#只同步哪些数据库，除此之外别的都不同步
binlog-do-db = test

重启MySQL，创建用于同步的用户账号
进入mysql，创建用户(此用户的目的是让从数据库登录主服务器)并授权：
用户名：MySlave，密码：password；

systemctl restart mysqld #重启mysql
mysql -u root -p #进入数据库


#创建用户，其中用户名后的为从数据ip地址
mysql> CREATE USER 'MySlave'@'192.168.yy.yy' IDENTIFIED BY 'password'；
#分配权限  
mysql> GRANT REPLICATION SLAVE ON *.* TO 'MySlave'@'192.168.yy.yy'；
#刷新权限
mysql>FLUSH PRIVILEGES；

查看Master状态，记录二进制文件名和位置
可以看到当前记录的二进制文件为mysql-bin.000007，位置为1539

5.2从数据库配置
修改mysql配置
查看数据库配置文件vi /etc/my.cnf，在[mysqld]下插入如下两行：

[mysqld]
log-bin=mysql-bin # 开启二进制日志
server-id=2 #设置server-id，需要唯一

重启mysql，并进入mysql，进行手动同步

mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.xx.xx',
    MASTER_USER='MySlave',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000007',
    MASTER_LOG_POS=1539;

启动salve同步进程

mysql > start slave；

查看slave状态
当Slave_IO_Running: Yes，Slave_SQL_Running: Yes时说明两个线程启动成功，主从复制配置完成了！

mysql > show slave status\G；

6.测试
主数据库新建jerry表：

然后在从数据库中刷新，可以看到新建的表：

注意：
若我们在进行数据库主从复制前，为以下情况的一种：

第一种情况：Master中无任何数据，Slave中也同样无任何数据
第二种情况：Master中已有表和数据，Slave是无表和无数据的
若为第一种情况，直接配置即可。

若为第二种情况，会有一个问题(踩过这坑=-=)，那就是如果我们之前有数据，而我们同步时输入的是最新的偏移位置，是会忽略以前的数据操作的，为此如果这个时候我们在Master以前的表中插入新数据，你会发现并不会在Slave中看到该数据，而会产生错误，这时候如果没有在Master的mysql配置文件my.cnf中配置忽略错误slave-skip-errors=1时，Slave_SQL_Running线程会关闭，我们再查看状态会发现其为Slave_SQL_Running: NO。
这种情况的解决办法是：手动导出sql文件并在Slave中运行，使得两者为一致状态；然后我们再重新手动同步。