MySQL master-slave replication _ MySQL

If the MySQL master-slave replication database has only one copy, it is the single point of data storage. for services that require reliability, there is a possibility of single point of failure. at this time, we need to copy the image to solve single point of failure. Another advantage of replication is that the read/write splitting can be performed based on the master/slave. in this way, MySQL's concurrent performance will not be reduced because of table locking during writing, therefore, MySQL replication is a basic operation in MySQL.

How to configure Master

First, you need to determine a Master. for MySQL Server acting as the Master, some specific configurations are required. one is to enable binlog, and the other is to set the server-id.


[mysqld]
log_bin = mysql-bin
server-id = 1
Configure Slave

To configure Slave, you must configure a unique server-id. This id cannot be the same as the Master, and multiple Slave cannot be the same. Restart After configuration.


[mysqld]
server-id = 2
Create a User

Create a user on the Master for replication purposes. Because on Slave, the user and password will be stored in plain text, so the user's permissions should be as small as possible, which must be different from that of the Super user.


create user repl@'your.domain' identified by 'password';
grant replication slave on *.* to repl@'your.domain';


Note: Here are the 2nd statements, which must be granted permissions *. * because the replication slave permission is a global privileges, this is required. if you want to restrict the permission to a relatively small range and do not want to copy all the databases, you can go to my. cnf adds the configuration items replicate-do-db and binlog-do-db to limit the database range.

Obtain the Master location

In the Master database, the command flush tables with read lock; statement is used, and the command "show master status" is used. at this time, the current binlog file name and the SQL statement position of the current row are displayed. Record the file name and execution location.

In this case, if the content of the Master database is not empty, you should use mysqldump to export data. If the database is empty, there is no need to do anything.

Establish a connection

In the case of an empty database above, it is very simple. stop slave, directly use the change master to statement on slave, after setting the parameters, you can execute start slave.

If the Master has data, the dump file generated just now should be transferred to the slave, then stop the slave first, then import the dump data, and then execute the change master to statement, set the binfile and location recorded before dump correctly before starting slave.

Status Check

In an ideal world, our work is over, but the world is not ideal. due to various reasons, this replication connection is often interrupted. Therefore, check the contact from time to time.

You can run show Master status on the master to see the same things as shown above.

You can run show Slave status on slave to view a lot of information and error messages. Generally, this is correct. If an error occurs, you should obtain the relevant information here to solve the problem.