MySQL Master/Slave configuration in Linux

The Master/Slave configuration of the mysql server can be used to implement read/write splitting. You can also restore the master database from the slave database after the master database crashes. You need to install mysql on two machines, and the two machines must be interconnected.

The Master/Slave configuration of the mysql server can be used to implement read/write splitting. You can also restore the master database from the slave database after the master database crashes. You need to install mysql on two machines, and the two machines must be interconnected.

The master-slave configuration of the mysql server, which can implement read/write splitting or restore data from the slave database after the master database fails.

Two machines are required, mysql is installed, and the two machines must be in the same LAN.

Host A: 192.168.1.100

Slave B: 192.168.1.101

Multiple slave servers

1. log on to host A first.

Mysql> grant replication slave on *. * TO 'backup '@ '192. 168.1.101 'identified BY '123 ';

Grant permissions to the slave machine. If multiple slave machines exist, the slave machine is executed multiple times.

2. Open my. cnf of host A and enter

Server-id = 1 # host id, integer
Log_bin =/var/log/mysql/mysql-bin.log # Make sure this file is writable
Read-only = 0 # host, both read and write are allowed
Binlog-do-db = test # You need to back up data and write multiple rows.
Binlog-ignore-db = mysql # databases that do not need to be backed up, multiple write lines

3. Open my. cnf of slave machine B and enter

Server-id = 2
Log_bin =/var/log/mysql/mysql-bin.log
Master-host = 192.168.1.100
Master-user = backup
Master-pass = 123456
Master-port = 3306
Master-connect-retry = 60 # Time Difference (in seconds) when the master server is disconnected from the server)
Replicate-do-db = test # only copy a database
Replicate-ignore-db = mysql # do not copy a database

4. Synchronize Databases

You don't have to worry too much. You only need to start the master and slave databases to synchronize automatically. If you don't have to worry about it, you can export the content of the master database to SQL, and then run it again in the slave database.

5. Restart mysql of host A and mysql of slave B.

6. Verification

In host A, mysql> show master status \ G;

In slave machine B, mysql> show slave status \ G;

You can see the following content:

File: mysql-bin.000001
Position: 1374
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql

You can perform INSERT, UPDATE, and DELETE operations in host A to check whether the operation has been modified in host B.