案例背景:

手头的的一个项目,之前生产环境数据库一直用的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 '' WITH GRANT OPTION;

FLUSH PRIVILEGES;

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

10. 查看主数据库状态

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

show master status;

11.  建立主从复制slave

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

CHANGE MASTER TO

MASTER_HOST='10.10.5.6',

MASTER_USER='repadmin',

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.启动之前停掉的连接数据库的所有应用,再在应用层面测试一下

参考资料:

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

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