mysql大库如何搭建备库_Mysql主备集群搭建

案例背景：

手头的的一个项目，之前生产环境数据库一直用的mysql单节点，虽然写了脚本，每天定时备份数据库，但还是不太保险。最近生产环境逐渐投入使用，于是决定再搭建一台Mysql 数据库，和当前的数据库做成主备集群。

本次案例分享主要包含两方面的内容：

1. Mysql主从备份的原理

2. Mysql主备集群搭建的具体步骤

通过本次案例分析，你将对Mysql主从备份有一个大体的了解，并能独立完成Mysql主备集群搭建。

一. Mysql主从备份的原理

mysql主备复制实现分成以下三个步骤：

1. 主服务器master将改变记录到二进制日志mysql-bin.xxx(binary log)中，这些记录叫做二进制日志事件，binary log events，可以通过show binlog events进行查看。

2. 从服务器slave的I/O线程使用专用帐号登陆到主服务器中读取该二进制文件binary log，并将文件内容写入到自己本地的中继日志relay-log文件中。

3. 从服务器的SQL线程会根据中继日志中的内容执行SQL语句。

在这个主从复制过程中，要求两台服务器有同样的初态。

二. Mysql主备集群搭建案例详解

主服务器：10.10.5.6

从服务器：10.10.5.7

操作系统版本：CentOS7.2.1511

Mysql数据库版本：5.6.33

主服务器10.10.5.6上Mysql是生产环境在用的单节点数据库，从服务器10.10.5.7上只装好了操作系统，mysql数据库还没有搭建。

由于Mysql把安装目录拷贝到另外一台服务器上可以直接启动，故打算将主服务器上的Mysql数据库目录直接copy到从数据库上启动，这样两台数据库服务器将有同样的初态，省去了导数据库的步骤。

1. 停止所有连接主服务器的应用

2. 停止主服务器上的mysql应用。

service mysql stop

3将主服务器上mysql安装目录压缩打包，在从服务器上用scp命令拉取压缩包，放到和主服务器相同的安装目录下，解压，授权。

4.查看主服务器10.10.5.6上my.cnf配置文件如下：

注意红框圈出来的内容，是一定要有的

5. 查看从服务器10.10.5.7上my.cnf配置文件，确认有如下配置

binlog_format=ROW

log_slave_updates=1

因为my.conf是从主服务器上copy过来的，所以这些配置都是一致的

将从数据库中my.cnf的server_id改成不同于主数据库的值，例如：

server_id = 2

6. 在主服务器和从服务器的my.cnf中加上sql_mode的设置：

sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式。

7. 在这里还需要注意一个问题，数据库中如果存在内存表，在主从同步时，会报错，使同步失败。这是因为无论是基于STATEMENT还是基于ROW复制，都要在二进制日志中包含改变的数据。这就要求在主从机上数据必须一致。当重启从库的时候，你就会丢失内存表的数据，复制中断。

顺便提下，内存表和临时表的区别如下：

临时表：表建在内存里，数据在内存里

内存表：表建在磁盘里，数据在内存里

为了解决这一问题，我们需要在复制的时候忽略内存表，使用选项replicate-ignore-table来对内存表进行忽略。

可以从information_schema记录的表的数据中，查询ENGINE是MEMORY的表，这些表即为内存表。

replicate-ignore-table=db.memory_table

其中内存表之间用“，”隔开

将排除内存表的配置加到从库10.10.5.7的my.cnf中，为了方便以后万一倒换主备，在主库10.10.5.6的my.cnf中也加上。

8. 启动主从数据库

service mysql start

9.在主数据库创建同步账号

GRANT REPLICATION SLAVE,FILE ON *.* TO 'repadmin'@'10.10.5.6' IDENTIFIED BY '123456' WITH GRANT OPTION;

FLUSH PRIVILEGES;

创建成功后可以在从数据库上登陆一下，看看能否登上。

10. 查看主数据库状态

在主数据库上敲下述指令：

show master status;

11.  建立主从复制slave

在从数据库上敲下述指令：

CHANGE MASTER TO

MASTER_HOST='10.10.5.6',

MASTER_USER='repadmin',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='bin_log.000012',

MASTER_LOG_POS=120,

MASTER_PROT=3306;

12. 启动SLAVE:

在从数据库上敲下述指令：

START SLAVE;

13. 检查从库slave状态

在从数据库上敲下述指令：

show slave status\G

可以看到

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

这说明主备状态同步正常了

14.数据库主备测试

在主库10.10.5.6上新建一个数据库，在从库10.10.6.7上看下新数据库有没有出现。

在主库上把这个数据库删掉，再到从库上看下这个数据库有没有被删除。

15.启动之前停掉的连接数据库的所有应用，再在应用层面测试一下

参考资料：

https://blog.csdn.net/u013256816/article/details/52536283

https://www.cnblogs.com/ahaii/p/6307648.html

https://www.cnblogs.com/jevo/p/3262227.html

https://www.cnblogs.com/jevo/p/3262227.html

https://www.cnblogs.com/louby/p/8979102.html

这次的案例分享就到这里了，要学的东西还很多，希望通过不断学习，量变引起质变。

下一篇：nginx配置的几个小知识点