Mysql master/slave database synchronization configuration and common errors

As the number of visits increases, for some time-consuming database read operations, it is generally adopted to separate the write and read operations to alleviate the pressure on the database. The database engine generally adopts the Master/Slave architecture. Implementing the master-slave configuration of the MySQL server can achieve read-write separation. In addition, after the main database crashes, data can be restored from the standby database to avoid interruption of website access. The following is a brief description of the MySQL master-slave server configuration process.

First, you need to install the MySQL service on two machines in the same LAN (of course, you can also use one machine to virtualize two machines).

Host A: 192.168.1.100

Slave B: 192.168.1.101

There can be multiple slaves.

1. Log in to host A first, and execute the following command to grant slave permissions. If there are multiple cluster machines, execute multiple times:

mysql>GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.1.101' IDENTIFIED BY '123456';

2. Open my.cnf of host A and enter the following configuration parameters:

server-id = 1 #Host ID, integer log_bin = /var/log/mysql/mysql-bin.log #Ensure that this file is writable read-only = 0 #Host, both read and write are possible binlog-do-db = test #Data needs to be backed up, multiple write multiple lines binlog-ignore-db = mysql #Databases that do not need to be backed up, multiple write multiple lines

3. Open my.cnf of slave B and enter the following configuration parameters:

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 #If the slave server finds that the master server is disconnected, the time difference (in seconds) to reconnect
replicate-do-db =test #Replicate only a certain database replicate-ignore-db=mysql #Do not replicate a certain database

4. Synchronize database

After the above configuration, restart the host A and slave B respectively to achieve automatic synchronization.

5. Verification

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

You can see roughly these contents

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

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

The displayed content is as shown below:

You can see that the Slave_IO_State item is Waiting for master to send event and both items in the red circle are Yes, so it can be basically determined that the configuration is successful.

In addition, you can perform some INSERT , UPDATE , and DELETE operations on host A to see whether the data has been modified on host B.

Common Mistakes

1. The master fails. After being repaired and restarted, the slave cannot synchronize with the master.

Error: Got fatal error 1236 from master when reading data from binary log

Reason: After the master is restarted, the MySQL binlog will be regenerated and the corresponding record position will change

Solution:

-master:

mysql > flush logs;
mysql > show master status;

Record the File and Position values

-slave:

mysql > stop slave;
mysql > CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000049',MASTER_LOG_POS=1359;
mysql> start slave;
mysql > show slave status\G;

2. The slave fails, the settings are correct, but it cannot be initialized

Error: ERROR 1201 (HY000): Could not initialize master

Solution:

-master:

mysql > flush logs;
mysql > show master status;

Record the File and Position values

-slave:

mysql > reset slave;
mysql > change master to master_host='192.168.10.100',master_user='test',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=106;
mysql> start slave;
mysql > show slave status\G;

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links