Multilevel replication of MySQL high-availability scenarios

In most scenarios we are using MySQL master-slave replication to achieve database redundancy, here is the multi-level replication to deal with, multi-level replication can quickly and easily deal with the database failure, the database has a, B, C server, under normal circumstances A-based, b for a From, C for B from.

A-->b-->c

When a problem occurs, B is set as the main, C is b from, a normal after the C from

B-->c-->a

When b problems, C-based, a for C from, b for a from, so repeatedly can quickly solve the problem

Role	Ip	Host Name	Database version
Main	192.168.2.241	Db1	5.6.29
Preparation	192.168.2.242	Db2	5.6.29
Preparation	192.168.2.243	Db3	5.6.29

Note that in such scenarios, the database version must be consistent, or it may cause problems due to incompatibility between versions

1. Create a copy Account

2. Configure Database Configuration
   

3. Back up the main library and import it into the standby library

4. Configure Master-Slave

   
   

1. Create a copy Account

Mysql>grant repication Slave on * * to ' repl ' @ ' 192.168.2.% ' identified by ' repl '; mysql>flush privileges;

2. Turn on database Binlog, set Server-id and enable log_slave_updates

Description: Log_slave_updates is a record of updates received from the server from the primary server into its own binary log file from the server

If the log_slave_updates is not turned on, then in the a-->b-->c scenario, C will not be able to get the data from B

In MySQL configuration file/etc/my.cnf, under [mysqld], add the following statement

log-bin=mysqlbinserver-id=241 #这里每台服务器都必须不一致, preferably the last segment of IP log_slave_updates=1expire_logs_days=7

Remember to restart the database

3. Back up the main library and import it into the standby library

Lock table

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)

Note: It is not possible to exit the MySQL command line session, and then open a window to export the database because the lock table is automatically dismissed as soon as it exits the session lock table.

[Email protected] ~]# mysqldump-uroot-p--all-database--add-drop-table >all_database.sql

Import the above exported all_database.sql into other DB2, DB3

[Email protected] ~]# mysql-uroot-p <all_database.sql

[Email protected] ~]# mysql-uroot-p <all_database.sql

4. Turn on master-slave replication

On the 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 |         |          | |+---------------+----------+--------------+------------------+-------------------+

On the 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;

Then perform the show slave status\g on DB2 and DB3 respectively; see if there are any errors

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: 0master_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: 01 row  in set  (0.00 SEC)

Can see

Slave_io_running:yes

Slave_sql_running:yes

Yes to indicate that replication is normal

Then test it:

Insert data into the database and then query on DB1, DB2, DB3

If there is a problem can show slave status\g; see if there are errors

If you encounter an error like 1062, you can ignore it, you can directly

mysql> Stop slave;mysql> SET GLOBAL sql_slave_skip_counter = 1;mysql> start slave;

After running for a period of time, db1 problem, causing unrecoverable failure, then only need to execute stop slave on DB2;

After the DB1 recovery, export the data from DB3 and record the points, then change master to DB3

If you want to prevent accidental writes from the library, you can also include read_only = 1 in the configuration file from the database

This article is from "Maple Night" blog, please be sure to keep this source http://fengwan.blog.51cto.com/508652/1744304

Multilevel replication of MySQL high-availability scenarios