主从备份介绍

就是保持两个数据库的数据自动同步，起着实时备份的作用，也可以读写分离，减轻网站的负载，容灾，主数据库GG了，从数据库立马顶上去。

mysql 备份工作原理

就是把主数据库（Master）的sql语句对从数据库（Slave）执行一遍。记录sql语句（除了show和select）就是binlog（Binarylog ）日志！它是已二进制的形式存储，速度快，开销小。关于binlog日志具体在文章Centos7mysql5.7使用mysqldump与binlog进行数据备份与恢复。举个栗子：

1.Master把每一次改动的sql语句记录到Binarylog 中。

2.Slave用Master允许的账号登录Master，读取Binarylog写入到自己的中继日志 Relaylog。

3.Slave的SQL slave thread（sql线程）会负责读取这个中继日志，更新Slave的数据，保持自己和Master数据一致（中继日志通常会位于OS的缓存中，所以中继日志的开销很小）。

废话不多说，操作一波。

主从备份实践

建议2台数据库版本一致，如果不一致很容易出错，mysql的配置文档my.cnf中加入binlog_checksum =none参数。

删除所有的外键，表与表的依赖关系最容易导致主从复制失败。

一.创建Master账号

登录Master数据库

mysql> CREATE USER 'backup'@'%' IDENTIFIED BY 'xxxxx';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'xxx.xxx.xxx.xxx' ; # Slave的IP，只允许Slave库登录，安全一点

mysql> flush privileges;

二.Master开启binlog

[root@s132-148-242-150 ~]# vim /etc/my.cnf #编辑mysql配置文件

[mysqld]

log-bin = /var/lib/mysql/data/mysql-bin

binlog_format = row

server-id = 1

binlog_do_db = mall

#binlog-ignore-db=mysql

#binlog-ignore-db=information_schema

#binlog-ignore-db=performance_schema

#auto_increment_increment=5

#auto_increment_offset=1

前三个参数在以前文章有过说明，不在赘述。

binlog_do_db ：数据库的改动记录到binlog日志中，可以多写，我这以mall为例子。

binlog-ignore-db：忽略的数据库。我这把mysql自带的数据库忽略了

auto_increment_increment和auto_increment_offset用于多机异地热备份的，因为每台服务器的数据库都可能在同一个表中插入数据，如果表有一个自动增长的主键，那么就会在多服务器上出现主键冲突；auto_increment_increment=2表示需要5台服务器做备份，auto_increment_offset=1表示这台服务器的序号，从1开始，不超过auto-increment-increment。

这样做后，在auto_increment_offset=1的服务器插入第一个id = 1，第二个id = 6；auto_increment_offset=2的服务器插入第一个id = 2，第二个id = 7；以此类推。这样就不会出现主键冲突了。

binlog_do_db和binlog-ignore-db参数相互对立，写一种就可以了；

三 .同步两台数据库

为什么做这步？如果不同步，主从备份之前数据就不一致，还谈什么备份。

Master执行锁表命令

mysql> flush tables with read lock;

Slave执行mysqldump全量备份

mysql> mysqldump -hxxx.xxx.xxx.xxx -uroot -p -B -F -R --master-data=2 mall|gzip >ops_$(date +%F).sql.gz #备份数据；写Master的ip，我这以mall数据库为例子，输出文件名年月日的gz格式

解压后；Master解除锁定

mysql> unlock tables；

Slave备份数据

mysql> mysql -uroot -proot mall< ops_xxxx-xx-xx.sql；

四.查看Master的binlog日志位置

mysql> show master status;

现在记录到mysql-bin。000006，pos是120（记住，等下会用到）

五.Slave配置

编辑mysql配置文件my.cnf

log-bin=/var/lib/mysql/data/mysql-bin

binlog_format = row

server-id = 2

replicate-do-db=mall

#replicate-ignore-db=mysql

#replicate-ignore-db=information_schema

#replicate-ignore-db=performance_schema

relay_log=mysql-relay-bin

log-slave-updates=OFF

前三行不说了。

replicate-do-db：以指定需要复制的数据库

replicate-ignore-db：复制时需要排除的数据库，这里排除了自带的数据库

relay_log：中继日志的名字

log-slave-updates：中继日志执行之后，这些变化是否需要计入自己的binarylog。当Slave需要作为另外一个服务器的Master的时候需要打开。就是多机异地热备份，我们不需要，不开。

保存，重启mysql。

[root@UsaOfficeLuke] systemctl restart mysqld；

六.Slave开启同步

在Slave登录数据库，执行下列语句：

mysql> CHANGE MASTER TO

       ->                 MASTER_HOST='xxx.xxx.xxx.xxx'，

         ->                  MASTER_USER='backup'，

         ->                  MASTER_PASSWORD='xxxxx'，

         ->              MASTER_LOG_FILE='mysql-bin.000006'，

         ->                  MASTER_LOG_POS=120;

就是Master的ip，注册的用户名，密码以及刚刚记录的binlog日志和位置。

重启mysql，查看slave线程开启

mysql> show slave status\G;

参数说明：

Master_host ： Master的地址

Master_user： Master用来复制的用户。 Slave会用此账号来登录主服务。进行复制

Master_log_file ： Master的日志文件名

Read_Master_log_pos ：就Master的日志记录位置， Slave根据这两个条件来选择复制的文件和位置

Slave_IO_Running：指的就是Slave上负责Master的线程工作状态。 Slave用这个专门的线程链接到Master上，并把日志拷贝回来。

Slave_SQL_Running:：指的就是专门执行sql的线程。它负责把复制回来的Relaylog执行到自己的数据库中。

图中的红框，两个都是Yes，就开启成功了。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果有No的话，就查看日志，看看什么问题，具体解决。

CentOS7 mysql5.7主从备份