Shulou(Shulou.com)06/01 Report--

With the increase of access volume, for some time-consuming database read operations, it is generally used to separate write and read operations to alleviate the pressure on the database, and the database engine generally adopts Master/Slave architecture. To realize the master-slave configuration of the mysql server, we can achieve the separation of read and write, and recover the data from the standby database after the collapse of the master database so as not to interrupt the access of the website. The following is a brief description of the process of mysql master-slave server configuration.

First of all, you need to install mysql services on two machines in the same local area network (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 CVM A first, execute the following command to grant slave permission, and execute multiple times if there are multiple cluster machines:

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

2. Open the my.cnf of CVM An and enter the following configuration parameters:

Server-id = 1 # host mark, integer log_bin = / var/log/mysql/mysql-bin.log # ensure that this file is writable read-only = 0 # host, read and write binlog-do-db = test # need to back up data, multiple write multiple lines binlog-ignore-db = mysql # databases that do not need backup, multiple write multiple lines

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

Server-id = 2log_bin = / var/log/mysql/mysql-bin.logmaster-host = 192.168.1.100master-user = backupmaster-pass = 123456master-port = 3306master-connect-retry=60 # if the slave server finds that the master server is down, the time difference between reconnecting (in seconds) replicate-do-db = test # only copies a library replicate-ignore-db=mysql # does not copy a library

4. Synchronize the database

After entering the above configuration, restart host An and slave B respectively, you can automatically achieve synchronization.

5. Verification

In Host A, mysql > show master status\ G

You can see something like this.

File: mysql-bin.000001

Position: 1374

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

In slave B, mysql > show slave status\ G

The display is shown in the following figure:

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

In addition, you can do some INSERT, UPDATE, DELETE operations in host A to see if host B has been modified.

Common mistakes

1. Master failed. After starting after repair, slave cannot synchronize with master.

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

Reason: after master restart, the binlog of mysql will be regenerated, and the corresponding recording location will be changed.

Solution:

-master:

Mysql > flush logs;mysql > show master status

Record the File and Positioning 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. Slave failed and was set up correctly, but failed to initialize

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

Solution:

-master:

Mysql > flush logs;mysql > show master status

Record the File and Positioning 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

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.