Shulou(Shulou.com)05/31 Report--

MySQL about the master-slave synchronization should pay attention to which problems, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

On the basis that we have prepared two database servers

1. Must be specified in the master and slave server configuration file / etc/my.cnf

Server_id (arbitrary, recommended specification is not confusing, regular, logical)

Binlog log (generally unspecified time log name defaults to hostname-bin.00000x)

Binlog-format= "mixed" (specify log format, usually mixed format "mixed", depending on requirements)

Note: if the above is not specified, errors will be reported.

2. Authorization on the main database: allow users to copy the main database (replication: copy)

Grant replication slave on. To user name @ "Server address" identified by "password"

Must be authorized (slave library cannot specify master library and copy)

3 specify a group of servers: (none of them is indispensable)

Log into the database

Change master to master_host= "Master Server address"

Master_user= "authorized users on the main Library"

Master_password= "authorized account password"

Master_log_file= "Master Server binlog Log name"

Master_log_pos= "binlog log offset"

Finally, start the master-slave replication start slave

View the status of multiple servers

Slave_IO_Running: (responsible for io communication with the host)

Slave_SQL_Running: (responsible for your own slave database process)

If nothing goes wrong, the master-slave synchronization will be deployed successfully.

But the world is not peaceful, if the IO thread fails to start

Here are the problems I have encountered:

1. The firewall of the master server is not turned off, which causes the synchronization of the slave server to fail.

Solution: turn off the firewall

2. The data in the master-slave server database is inconsistent (when deploying the master-slave service)

First back up different parts to each other's database to ensure data consistency.

(it is not recommended to delete the list)

3. The binlog log offset is incorrect. The synchronization node cannot be found from the server.

Open the main server binlog log file, find the data offset, and re-specify it.

If the SQL thread fails to start:

The situation I encountered is as follows:

1. Last_SQL_Error: Error 'Operation DROP USER failed for' yy'@'192.168.4.10'' on query. Default database: 'alldb'. Query: 'drop user yy@192.168.4.10'

That is, the authorized user of the master library before synchronization, after deployment, found that there was no authorized user on the slave library before, and then I revoked it, and the SQL thread of the slave library was broken, so be careful.

Solution: generally either delete, revoke, or, of course, do the same authorization from the library.

2 、 Slave failed to initialize relay log info structure from the repository

When this error occurs: the general reason is that the default relay log relay _ log is occupied by another mysql slave on the server

Solution: 1. Initialize the relay log, that is, delete the relay-log.info relay log file

2. Specify the relay log name in the configuration file / etc/my.cnf

3. When configuring a highly available cluster, the SQL thread startup failure error is reported as follows

Master command COM_REGISTER_SLAVE failed: Access denied for user 'monitor'@'%' (using password: YES) (Errno: 1045)

When this kind of error occurs:

1 the cascade replication function of the master server is not enabled

Solution: log_slave_updates # allows cascading replication in the configuration file, restarts the service, and the main library must add authorized users.

2 and delete authorized users (not recommended).

To sum up: the deployment of master-slave synchronization has a high degree of unity to the database server.

After reading the above, have you mastered the methods of master-slave synchronization in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.