MySQL Categories: Abbrlink: 60AE3a4D Date: 2019-05-13 10:12:36

Install MySQL5.7, install MySQL5.7, install MySQL5.7, install MySQL5.7, install MySQL5.7, install MySQL5.7, install MySQL5.7, install MySQL5.7, install MySQL5.7, install MySQL5.7

The preparatory work

I have a simple diagram to show you how MySQL master-slave works:

Here, we prepare two machines:

  • Host: 192.168.248.128
  • From machine: 192.168.248.139

Host configuration

The configuration of the host is in three steps, which is relatively easy:

1. Authorize the slave server

GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.248.139' identified by '123';
FLUSH PRIVILEGES;
Copy the code

The user name rep1, password 123, and address 192.168.248.139 must be used to log in to the slave machine. After successful login, you can operate any table in any library. If you do not need to restrict the login address, you can change the IP address to a %.

2. Modify the master database configuration file, enable binlog, and set server-id. Each modification takes effect only after the MySQL service is restarted

vi /etc/my.cnf
Copy the code

The modified file content is as follows:

[mysqld]
log-bin=/var/lib/mysql/binlog
server-id=128
binlog-do-db = cmdb
Copy the code

The diagram below:

  • Log-bin: synchronized log path and file name. Note that MySQL has permission to write to this directory.
  • Binlog-do-db: specifies the name of the database to be synchronized. After the slave server is connected to the host, only the database configured in this parameter will be synchronized.
  • Server-id: unique identifier of the MySQL server in the master/slave environment. The value can be any number. The value cannot be the same as that of the slave server.

Restart the MySQL server after the configuration:

systemctl restart mysqld
Copy the code

3. View the current binary log name and offset of the primary server. The purpose of this operation is to restore the data from this point after starting from the database:

show master status;
Copy the code

The host configuration is complete.

From the machine configuration

From the configuration of the machine is also relatively simple, we look at step by step:

1. Add the following configuration to /etc/my.cnf:

Note On the slave server, you only need to configure the server id.

Note: If the slave machine is copied from the host, that is, the MySQL instance is obtained by copying the CentOS VM. In this case, the UUID of the two MySQL instances is the same (they will not be the same during normal installation). In this case, you need to manually change the UUID of the two MySQL instances/var/lib/mysql/auto.cnfFor example, change the length of the UUID.

2. Run the following command to configure the slave machine:

change master to master_host='192.168.248.128',master_port=3306,master_user='rep1',master_password='123',master_log_file='binlog.000001',master_log_pos=120;
Copy the code

The host IP address, port number, and user name and password for logging in to the host from the host are configured. Ensure that the last two parameters are the same as those in master.

3. Start the slave process

start slave;
Copy the code

Check the slave state after startup:

show slave status\G;
Copy the code

4. Check the status of the slave

If the following two values are set to YES, the configuration is correct:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Copy the code

At this point, the configuration is complete, the host creates a library, adds data, and the slave machine automatically synchronizes.

If either of the two values is not YES, it indicates that the primary and secondary environment fails to be set up. In this case, you can read logs to view the cause of the error and solve the problem in detail.

conclusion

This article mainly and everyone said that MySQL master-slave environment setup, this several steps songo repeatedly operate many times, small partners as long as according to songo’s steps generally successful, have any questions welcome comment discussion.

Pay attention to the public account [Jiangnan little Rain], focus on Spring Boot+ micro service and front and back end separation and other full stack technology, regular video tutorial sharing, after attention to reply to Java, get Songko for you carefully prepared Java dry goods!