mysql 多级备份_MySQL高可用方案之多级复制

在大部分场景中我们都是用MySQL主从复制来实现数据库的冗余，这里是用多级复制来处理，多级复制可以快速简单的处理数据库的故障，数据库有A、B、C服务器，正常情况下A为主、B为A的从、C为B的从。

A-->B-->C

当A出现问题时，将B设为主，C为B的从，A正常后就为C的从

B-->C-->A

当B出问题后，C为主，A为C的从，B为A的从，如此反复可以快速解决问题

角色IP主机名数据库版本

主192.168.2.241db15.6.29

备192.168.2.242db25.6.29

备192.168.2.243db35.6.29

注意在这样的场景下数据库的版本必须为一致，否则会因为版本之间不兼容导致出现问题

创建复制账户

配置数据库配置

备份主库，然后导入到备库

配置主从

1.创建复制账户mysql>grant repication slave on *.* to 'repl'@'192.168.2.%' identified by 'repl';

mysql>flush privileges;

2.开启数据库binlog，设置server-id和启用log_slave_updates

说明：log_slave_updates是将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中

如果没有开启log_slave_updates则在A-->B-->C场景中，C将无法从B中获取到数据

在MySQL配置文件/etc/my.cnf中的[mysqld]下添加如下语句log-bin=mysqlbin

server-id=241      #这里每台服务器都必须不一致，最好是IP的末段

log_slave_updates=1

expire_logs_days=7

记得重启下数据库

3.备份主库，然后导入到备库中

锁表mysql> flush tables with read lock;

mysql> show master status;

+---------------+----------+--------------+------------------+-------------------+

| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000002 |    409 |         |             |             |

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

注意：这里不能退出mysql命令行会话，另外再开一个窗口将数据库导出，因为锁表的时候，只要退出会话锁表自动解除[root@db1 ~]# mysqldump -uroot -p --all-database --add-drop-table >all_database.sql

将上面导出的all_database.sql导入到其他的db2、db3中[root@db2 ~]# mysql -uroot -p 

4.开启主从复制

在db2上：mysql> change master to master_host='192.168.2.241',master_user='repl',master_password='repl',master_log_file='binlog.000002',master_log_pos=409;

mysql> start slave;

mysql> show master status;

+---------------+----------+--------------+------------------+-------------------+

| File     | Position| Binlog_Do_DB| Binlog_Ignore_DB| Executed_Gtid_Set|

+---------------+----------+--------------+------------------+-------------------+

| binlog.000002| 647569 |       |         |          |

+---------------+----------+--------------+------------------+-------------------+

在db3上:mysql> change master to master_host='192.168.2.242',master_user='repl',master_password='repl',master_log_file='binlog.000002',master_log_pos=647569;

mysql> start slave;

然后分别在db2和db3上执行show slave status\G;查看是否有错mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.242

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000002

Read_Master_Log_Pos: 647569

Relay_Log_File: db3-relay-bin.000002

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 647569

Relay_Log_Space: 451

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 242

Master_UUID: 25a2315a-d9f0-11e5-9aa9-000c296e3855

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

可以看到

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

为yes，说明复制正常

接着测试一下:

在数据库中插入数据，然后在db1、db2、db3上查询即可

如有问题可以show slave status\G;查看是否有错误

如果遇到类似1062的错误的话可以忽略，则可以直接mysql> stop slave;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

mysql> start slave;

在运行一段时间后，db1出现问题，导致无法恢复的故障，则只需要在db2上执行stop slave;

然后db1恢复后，从db3导出数据并记录点，然后change master到db3上

如果为了防止在从库意外写入，也可以在从数据库的配置文件中加入read_only = 1