How to prepare MySQL Replication

1. Create an account dedicated to replication

First, the replication operation involves two important permissions, which are described here:
The replication client privilege enables the use of show Master Status and show slave status.
Replication client allows you to use the show Master Status and show slave status commands, that is, this permission is used to authorize the account to monitor the replication status.
The replication slave privilege shoshould be granted to accounts that are used by slave servers to connect to the current server as their master. without this privilege, the slave cannot request updates that have been made to databases on the master server.
Replication slave is a required and basic permission. It grants the slave server the right to perform the replicate operation after the account connects to the master.

In general, we will create an account dedicated to replication on the master server separately. This account must have the replication slave permission, and there is no need to add unnecessary permissions to ensure that the user has a single responsibility. Assume that the account we want to create is repl and the password is REPL. The command for this operation is as follows:
Mysql> grant replication slave on *. * To 'repl' @ '2017. 192.% 'identified by 'repl ';
Specifically, 192.168.0.% indicates the server where the repl user is located. Here, % is a wildcard, indicating that all servers with 192.168.0.0-192.168.0.255 can log on to the master server as the repl user. If no wildcard is used and the accessed server is not in the above configuration, you will not be able to use this account to replicate the master server from your server.
In addition, the user permission settings in hight performance MySQL are different. We recommend that you configure the repl account on both the host and slave, and grant the replication slave and replication client permissions at the same time, the command is as follows:
Mysql> grant replication slave, replication client on *. * To repl @ '2017. 192.% 'identified by 'repl ';
The author explains the benefits of doing so: on the one hand, it is very convenient to use the same account to monitor and manage replication, and there is no need to distinguish between slave and master, on the other hand, the repl account has the same configuration on slave and master, so that if we switch between slave and master, the account does not need to be changed.

2. Configure the Master/Slave Server

The Master/Slave server configuration is completed by modifying the my. CNF/My. ini file.
The following are required configuration items for the Master/Slave Server:
Configuration items required by the Host:
Log-bin // custom, such as log-bin = mysql-bin
Server_id // a unique ID for the server. The default value is 1. We recommend that you use the last section of the IP address.
Configuration items required for the slave:
Server_id // a unique ID for the server. The default value is 1. We recommend that you use the last IP address.

Note: In general, we will also set log-bin for the slave machine. This is because the default log-binfile is named based on the host name. Once the host name is changed, a problem occurs, in addition, it is also convenient to switch the master and slave servers to the slave servers!

Optional host configuration items: (used to configure which databases on the host will make binary logs for replicate)
BINLOG-do-DB
BINLOG-ignore-DB
Optional slave configuration items: (used to configure database and table replicate opportunities)
Replicate-do-DB, replicate-ignore-DB
Replicate-do-table, replicate-ignore-table
Replicate-wild-do-table
Replicate-wild-ignore-table
Note: We recommend that you do not configure master_host and other options in my. CNF/My. ini, but use the change master to command to set them dynamically!

 

For the master end, I only need to set server_id and log_bin. For the slave end, I only need to set server_id, but there are some Recommended settings. The following are the recommended slave settings in hight performance MySQL.

# Slave-end replication-related configuration.
# The only required option for slave-end is server_id.
# The other options are recommanded on P 349 of hight performance MySQL
Server_id = 234
Log_bin = mysql_bin_log
Relay_log = mysql_relay_bin_log
Log_slave_updates = 1
Read_only = 1

 

3. Connect the slave server to the master server for Replicate

By entering the change master to command on the slave server, the slave service can be connected to a master server for replication.
Mysql> change master to master_host = '192. 168.0.246 ',
-> Master_user = 'repl ',
-> Master_password = 'repl ',
-> Master_log_file = 'mysql-bin.000001 ',
-> Master_log_pos = 0;

After you enter the preceding command, all configurations are completed:
Start slave;
Start replication from the service, so that the master and slave servers start synchronization. You can use:
Show slave status/g;
Command to view the slave server status. If the slave_io_state item is displayed as waiting for Master to send event, all work is ready.