MySQL架构备份之双机热备

共计 4951 个字符，预计需要花费 13 分钟才能阅读完成。

1、可以降低 master 读压力

2、可以对数据库做“热备”，热备只能解决硬件 master 硬件故障，软件故障等重大故障问题，但无法解决人为误操作导致的逻辑故障（列如输入错误的 SQL 语句把重要的记录删除了），所以常规的备份是必须。

环境准备及要求：

1、关闭防火墙和 selinux

2、hosts 文件中两台服务器主机名和 ip 地址一一对应起来

3、系统时间需要同步

4、master和 slave 的数据库版本保持一致（系统版本保持一致）

思路：

1、master必须开启二进制日志

2、slave必须开启中继日志

3、master和 slave 的server-id 必须不一样范围（2^23-1）

4、master和 slave 的初始数据一致

环境：

master：192.168.1.166

slave：192.168.1.114

1、环境准备

# master：
[root@master ~]# vim /etc/hosts
192.168.1.114   slave

# slave：
[root@slave ~]# vim /etc/hosts
192.168.1.166   master

2、修改配置文件

# master：
root@master ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id = 1

# slave：
[root@slave ~]# vim /etc/my.cnf
server-id = 2
relay-log=/data/DB/relay.log

# 扩展说明：有时候，我们只是想 master 向 slave 上面同步某一个库，那么就可以使用下面的参数，同样是加在 /etc/my.conf 配置文件里面：binlog-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
binlog-ignore-db=不需要复制的数据库苦命，如果复制多个数据库，重复设置这个选项即可


示例：master 的/etc/my.conf
server-id = 1
log-bin=mysql-bin     #设定生成的 log 文件名；binlog-do-db=demo    #需要同步的二进制数据库名；binlog-ignore-db=information_schema    #不同步的二进制数据库名, 如果不设置可以将其注释掉;
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=test
log-slave-update    #把更新的记录写到二进制文件中；slave 的/etc/my.conf
server-id = 2    # 如果有多个 依次添加即可，不能相同
log-bin=mysql-bin
relay-log=/data/DB/relay.log
# 如果想要做级联架构，也就是 master 复制到 slave 上面，slave 再复制到 slave2 上面，同样在 slave 上面也可以控制需要复制的数据库，replicate-ignore-db=mysql
replicate-do-db=demo
log-slave-update    # 级联 中级 slave 必须开启此参数

# MySQL 主从复制参数大全 参考：https://www.cnblogs.com/qianniao12/p/8011222.html

针对单个数据库配置主从

3、数据初始化一致

情况 1：两边都没有业务数据，直接初始化 master 和 slave 的数据库

情况 2：master 上面有业务数据

masteer：1>停止数据库
[root@master ~]# /etc/init.d/mysqld stop

2>物理级别备份
[root@master ~]# cd /data/DB/ && ll
[root@master DB]# tar -cvzf /tmp/mysql.tar.gz *
[root@master DB]# scp /tmp/mysql.tar.gz 192.168.1.114:/tmp

slave：3>slave 端进行数据恢复
[root@slave ~]# cd /data/DB/ && ll
[root@slave DB]# tar -xf /tmp/mysql.tar.gz -C .
[root@slave DB]# /etc/init.d/mysqld start

master 和 slave：4>查看 slave 和 master 上面的数据是否一样
[root@master DB]# echo "show databases;" | mysql -p123 
[root@slave DB]# echo "show databases;" | mysql -p123

示例 master 上面有业务数据

4、在 master 上面创建授权用户及查看 binlog 信息

master：1>锁表，先加锁，防止两边数据不一致; 如果业务还未上线，这个就没有必要了
mysql> flush tables with read lock;

2>创建授权用户
mysql> grant replication slave on *.* to 'slave'@'192.168.1.114' identified by '123';

3>查看 binglog 信息，只有打开二进制日志，这句命令才有结果，表示当前数据库的二进制日志写到什么位置
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 331
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.02 sec)
# 注意：此时千万不要往里面写东西

5、在 slave 上配置同步信息

slave：mysql> change master to master_host='192.168.1.166', master_user='slave', master_password='123', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=331;
参数说明：master_host：master ip
    master_user：同步用户
    master_password：密码
    master_port：端口
    master_log_file：master 上面查看到的二进制日志名
    master_log_pos：master 上面查看到的 POS 值
    
mysql> show slave status \G
查看 slave 发现报错
解决：1. 检查配置文件里 server-id 是否不一致
    2. 检查数据目录里的 auto.cnf 文件的 uuid 是否一样，如果一样将 slave 中的 auto.cnf 删除后将 master 和 slave 的服务重启。mysql> show slave status \G;
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

6、测试验证

测试一：先查看 master 和 slave 的 ceshi 库中 users 表的信息
    master：mysql> select * from ceshi.users;
    +----+-------+----------+-----------------+
    | id | name  | password | address         |
    +----+-------+----------+-----------------+
    |  1 | zhang | 1234     | NULL            |
    |  2 | wang  | 4321     | 湖北武汉        |
    |  3 | li    | 5678     | 北京海淀        |
    |  4 | liu   | 1122     | NULL            |
    |  5 | zou   | 4311     | 湖南长沙        |
    |  6 | zhou  | 6789     | 北京八宝山      |
    |  7 | ding  | 7891     | 深圳西丽        |
    +----+-------+----------+-----------------+
    7 rows in set (0.00 sec)

    slave：mysql> select * from ceshi.users;
    +----+-------+----------+-----------------+
    | id | name  | password | address         |
    +----+-------+----------+-----------------+
    |  1 | zhang | 1234     | NULL            |
    |  2 | wang  | 4321     | 湖北武汉        |
    |  3 | li    | 5678     | 北京海淀        |
    |  4 | liu   | 1122     | NULL            |
    |  5 | zou   | 4311     | 湖南长沙        |
    |  6 | zhou  | 6789     | 北京八宝山      |
    |  7 | ding  | 7891     | 深圳西丽        |
    +----+-------+----------+-----------------+
    7 rows in set (0.11 sec)

    在 master 上面删除数据，然后回到 slave 上面查看
    master：mysql> delete from ceshi.users where id>=3;
    mysql> select * from ceshi.users;
    +----+-------+----------+--------------+
    | id | name  | password | address      |
    +----+-------+----------+--------------+
    |  1 | zhang | 1234     | NULL         |
    |  2 | wang  | 4321     | 湖北武汉     |
    +----+-------+----------+--------------+
    2 rows in set (0.00 sec)

    slave：mysql> select * from ceshi.users;
    +----+-------+----------+--------------+
    | id | name  | password | address      |
    +----+-------+----------+--------------+
    |  1 | zhang | 1234     | NULL         |
    |  2 | wang  | 4321     | 湖北武汉     |
    +----+-------+----------+--------------+
    2 rows in set (0.00 sec)


测试二：停止 slave 上的服务
    [root@slave ~]# /etc/init.d/mysqld stop
    
    在 master 上面插入数据并查看
    mysql> insert into ceshi.users(name, password, address) values("ceshi","666666","广东深圳");
    Query OK, 1 row affected (0.04 sec)

    mysql> select * from ceshi.users;
    +----+-------+----------+--------------+
    | id | name  | password | address      |
    +----+-------+----------+--------------+
    |  1 | zhang | 1234     | NULL         |
    |  2 | wang  | 4321     | 湖北武汉     |
    |  8 | ceshi | 666666   | 广东深圳     |
    +----+-------+----------+--------------+
    3 rows in set (0.00 sec)
    
    再次启动 slave 并查看是否有更新
    [root@slave ~]# /etc/init.d/mysqld start
    mysql> select * from ceshi.users;
    +----+-------+----------+--------------+
    | id | name  | password | address      |
    +----+-------+----------+--------------+
    |  1 | zhang | 1234     | NULL         |
    |  2 | wang  | 4321     | 湖北武汉     |
    |  8 | ceshi | 666666   | 广东深圳     |
    +----+-------+----------+--------------+
    3 rows in set (0.00 sec)