Mysql master replication and mysql Replication

Mysql master replication and mysql Replication

The customer needs high availability in the project, and the database is a key part of high availability. I have studied the high availability of many mysql instances, using mysql cluster and mysql master-slave master-master replication.

The mysql cluster is too complex and has five nodes. Of course, it should be deployed on at least two machines. All the focuses on Master-master replication.

1. configuration file my. cnf

Node

Log-bin = mysql-bin
Server-id = 1
Binlog-do-db = Name of the database to be copied in the test master database. If multiple databases are copied, set this option again.
Binlog-ignore-db = the mysql master database does not need to be copied. If you copy multiple databases, set this option repeatedly.
Replicate-do-db = Name of the database to be copied from the test slave database. If multiple databases are copied, set this option again.
Replicate-ignore-db = the mysql slave database does not need to be copied. If you copy multiple databases, set this option again.
Log-slave-updates
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 1

Node B:

Log-bin = mysql-bin
Server-id = 2
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Log-slave-updates
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 2

After the configuration is changed, you must start mysql before proceeding to the next step.

Each AB node creates an account accessed by the other Party:

Grant replication slave on *. * to 'repl' @ '192. 168.10.131 'identified by 'Password ';

It is recommended that no business occurs before the operation. If yes, you can use the master database to lock the table.
Flush tables with read lock;

View the show master status on the AB node;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000005 | 261 |
Record the value of FILE and Position, which will be used for subsequent slave server operations.

The last two parameters of the AB node are the same as those of the other node.

Change master to master_host = '192. 168.21.10 ', master_user = 'repl', master_password = '000000', master_log_file = 'mysqlmaster-bin.000001', master_log_pos = 192;

Start slave on the AB node;

AB node check:

Show slave status \ G
Slave_IO_State: Waiting for master to send event
Master_Host: 1xx. 3.4x.2xx
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000120 // BinaryLog name of the master database
Read_Master_Log_Pos: 974015581 // BinaryLog offset of the master database, which increases as long as there is data update.
Relay_Log_File: mysqld-relay-bin.000020 // slave database relay log name
Relay_Log_Pos: 320839203 // log offset of slave database Relay
Relay_Master_Log_File: mysql-bin.000120 // relay the current master database BinaryLog
Slave_IO_Running: Yes // receives the Update Status
Slave_ SQL _Running: Yes // execution status
Replicate_Do_DB: nq // copy the database
Replicate_Ignore_DB: // exclude
Replicate_Do_Table: // copy a single table
Replicate_Ignore_Table: // exclusive table
Replicate_Wild_Do_Table: // copy a table across Databases
Replicate_Wild_Ignore_Table:
Last_Errno: 0 // The error code of the previous execution
Last_Error: // error details
Skip_Counter: 0 // you can skip Last_Errno
Exec_Master_Log_Pos: 974015581 // execution offset
Relay_Log_Space: 320839203 // relay offset
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
1 row in set (0.00 sec)

The values of Slave_IO_Running and Slave_ SQL _Running must be YES to indicate that the status is normal. If any error occurs, check the error log first.

Cancel master database lock
Mysql> unlock tables;

The rest is verification.

I have tested how long the cache will be retained after I shut down the mysql service on one machine and operate on the other machine.