Multi-level replication of MySQL high availability solution

Multi-level replication of MySQL high availability solution

In most scenarios, we use MySQL master-slave replication to achieve database redundancy. Here we use multi-level replication to handle the problem. Multi-level replication can quickly and easily handle database faults, the database has A, B, and C servers. Normally, A is the master database, B is the slave database, and C is the slave database of B.

A --> B --> C

When A encounters A problem, Set B as the master node, C as the slave node of B, and A as the slave node of C after A is normal.

B --> C -->

When B has A problem, C is the primary node, A is the slave node of C, and B is the slave node of A. This problem can be quickly solved through repetition.

Role	IP	Host Name	Database Version
Master	192.168.2.241	Db1	5.6.29
Backup	192.168.2.242	Db2	5.6.29
Backup	192.168.2.243	Db3	5.6.29

Note that in this scenario, the database versions must be consistent. Otherwise, problems may occur due to incompatibility between versions.

Create a duplicate account

Configure Database Configuration

Back up the master database and import it to the slave database.

Configure Master/Slave

1. Create a duplicate account

mysql>grantrepicationslaveon*.*to'repl'@'192.168.2.%'identifiedby'repl';mysql>flushprivileges;

2. enable database binlog, set server-id and enable log_slave_updates

Note: log_slave_updates records the updates received from the master server to the binary log file of the slave server.

If log_slave_updates is not enabled, C cannot obtain data from B in A --> B --> C scenario.

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

Log-bin = mysqlbinserver-id = 241 # Here each server must be inconsistent, preferably the last segment of the IP address log_slave_updates = 1expire_logs_days = 7

Remember to restart the database

3. Back up the master database and import it to the slave database.

Lock table

mysql>flushtableswithreadlock;mysql>showmasterstatus;+---------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+---------------+----------+--------------+------------------+-------------------+|binlog.000002|409||||+---------------+----------+--------------+------------------+-------------------+1rowinset(0.00sec)

Note: you cannot exit the mysql command line session. In addition, you can open another window to export the database, because when you lock the table, you only need to exit the session lock table and automatically remove it.

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

Import the exported all_database. SQL to other db2 and db3 databases.

[[Email protected] ~] # Mysql-uroot-p
[[Email protected] ~] # Mysql-uroot-p <all_database. SQL <pre = ""> <p> 4. enable master-slave replication </p> <p> On db2: </p> <pre class = "brush: SQL; toolbar: false"> mysql> changemastertomaster_host = '2017. 168.2.241 ', master_user = 'repl', master_password = 'repl', master_log_file = 'binlog. 000002 ', master_log_pos = 409; mysql> startslave; mysql> showmasterstatus; + --------------- + ---------- + ------------ + strong + | File | Position | strong | + ----------------- + ---------- + ------------ + strong + | binlog.w.02 | 647569 | | + --------------- + ---------- + -------------- + -------------------- + ------------------- + </pre> <p> On db3: </p> <pre class = "brush: SQL; toolbar: false"> mysql> changemastertomaster_host = '2017. 168.2.242 ', master_user = 'repl', master_password = 'repl', master_log_file = 'binlog. 000002 ', master_log_pos = 647569; mysql> startslave; </pre> <p> then run show slave status \ G on db2 and db3 respectively; check whether there are errors </p> <pre class = "brush: SQL; toolbar: false"> mysql> showslavestatus \ G; * *************************** 1. row ************************* Slave_IO_State: WaitingformastertosendeventMaster_Host: 192.168.2.242Master _ User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: Drawing _ Master_Log_Pos: Drawing: db3-relay-bin.000002Relay_Log_Pos: Drawing _ IO_Running: Drawing: Last_Errno: 0Last_Error: Skip_Counter: Drawing: usage: Until_Log_Pos: Usage: Master_SSL_Cert: Usage: Master_SSL_Key: Seconds_Behind_Master: Usage: Master_Server_Id: 242Master_UUID: Usage: /var/lib/mysql/master. infoSQL_Delay: direction; waitingfortheslaveI/Direction: direction: Master_SSL_Crl: Master_SSL_Crlpath: direction: Executed_Gtid_Set: Auto_Position: 01 rowinset (0.00sec) </pre> <p> You Can See </p> <p> Slave_IO_Running: Yes </p> <p> Slave_ SQL _Running: Yes </p> <p> yes, the replication is normal. </p> <p> test: </p> <p> insert data into the database, then you can query on db1, db2, and db3 </p> <p> if you have any questions, show slave status \ G; check for errors </p> <p> if you encounter errors similar to 1062, you can ignore them directly </p> <pre class = "brush: SQL; toolbar: false "> mysql> stopslave; mysql> SETGLOBALSQL_SLAVE_SKIP_COUNTER = 1; mysql> startslave; </pre> <p> db1 becomes unrecoverable after a period of time, you only need to execute stop slave on db2. </p> <p> after db1 is restored, the data is exported from db3 and recorded, then change master to db3 </p> <p> if you want to prevent accidental write in the slave database, you can also add read_only = 1 </p> <p>> </p> </all_database. SQL <>