MySQL高可用方案之多级复制

42313

在大部分场景中我们都是用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.241	db1	5.6.29
备	192.168.2.242	db2	5.6.29
备	192.168.2.243	db3	5.6.29

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

• 创建复制账户
  
• 配置数据库配置
  
  
• 备份主库，然后导入到备库
  
• 配置主从
  
  
  

1.创建复制账户

1 2	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]下添加如下语句

1 2 3 4

log-bin=mysqlbin server-id=241      #这里每台服务器都必须不一致，最好是IP的末段 log_slave_updates=1 expire_logs_days=7

记得重启下数据库

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

1 2 3 4 5 6 7 8

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命令行会话，另外再开一个窗口将数据库导出，因为锁表的时候，只要退出会话锁表自动解除

1	[iyunv@db1 ~]# mysqldump -uroot -p --all-database --add-drop-table >all_database.sql

将上面导出的all_database.sql导入到其他的db2、db3中

1	[iyunv@db2 ~]# mysql -uroot -p <all_database.sql

1	[iyunv@db3 ~]# mysql -uroot -p <all_database.sql

4.开启主从复制
在db2上：

1 2 3 4 5 6 7 8

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上:

1 2	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;查看是否有错

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

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的错误的话可以忽略，则可以直接

1 2 3

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