MySQL configuration master-slave server (one master and multiple slaves)

This article mainly introduces the configuration of MySQL master-slave server (one master and multiple slaves). If you are interested, you can learn more about it.

Current Environment
Centos 7.6
MySQL 5.7

Centos 7.6 installation of MySQL 5.7 please refer to: https://www.jb51.net/article/99965.htm

Ideas

Host Configuration

Modify conf

vim /etc/my.cnf

Add the configuration after [mysqld], save and exit

server-id=1
log-bin=master-bin
log-bin-index=master-bin.index

Restart

systemctl restart mysqld.service

# Check the status systemctl status mysqld.service

test

mysql -u root -p
SHOW MASTER STATUS; 

Slave 1 Configuration

Modify conf

vim /etc/my.cnf

Add the configuration after [mysqld], save and exit

server-id=2
relay-log=slave-01-relay-bin
relay-log-index=slave-01-relay-bin.index

Restart

systemctl restart mysqld.service
# Check the status systemctl status mysqld.service

Slave 2 Configuration

Modify conf

vim /etc/my.cnf

Add the configuration after [mysqld], save and exit

server-id=3
relay-log=slave-02-relay-bin
relay-log-index=slave-02-relay-bin.index

Restart

systemctl restart mysqld.service
# Check the status systemctl status mysqld.service

Configuring master-slave relationships

Host Configuration

Log in to the host MySQL console, create a user, authorize and refresh.

mysql -u root -p

CREATE USER 'repl'@'%' IDENTIFIED BY 'Mysql57*';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'Mysql57*';
flush privileges;

Slave Configuration

mysql -u root -p

# My host IP here is 192.168.1.8
change master to master_host='192.168.1.8',master_port=3306,master_user='repl',master_password='Mysql57*',master_log_file='master-bin.000001',master_log_pos=0;

start slave;
#Stop master-slave synchronization#stop slave;
# \G means line break to view show slave status \G; 

If you check the master-slave status, you will see the message "The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work." Please refer to the FAQ

Test data synchronization

Here the author uses the visualization tool Navicat, readers can also use other tools, just a simple test.

Add a new library test-for-repl in the master library, and check whether there is a new library in slave libraries 01 and 02

Add a new table test-create-table in the master library test-for-repl to check whether there is a table in the slave library 01 and 02. Add a new table test-create-table in the master library test-for-repl to check whether there is data in the slave library 01 and 02.

Frequently asked questions

The slave machine checks the master-slave synchronization status show slave status \G; and reports an error

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

reason

Here, the author used VMware software to create a virtual machine to install Centos7.6 + MySQL5.7, and then used the virtual machine to clone two other virtual machines as slave machines, resulting in the same UUID being generated after MySQL on the three virtual machines is started.

Solution

Find the UUID of MySQL, modify one character, restart the service, then log in to the MySQL console and enable the master-slave

find / -name 'auto.cnf'
vim /var/lib/mysql/auto.cnf
systemctl restart mysqld.service
mysql -u root -p
start slave;
# \G means line break to view show slave status \G; 

This is the end of this article about configuring MySQL master-slave server (one master and multiple slaves). For more relevant MySQL master-slave server content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!