Mycat distributed database architecture solution -- build MySQL read-write separation environment -- one master and many slaves

Echo editor, welcome to reprint, reprint please state the source of the article. Welcome to add echo wechat (wechat: t2421499075) for communication and learning Never lose in a hundred battles, never claim to win, never lose in a hundred defeats, and strive to move forward. ——This is really powerful!!!

The main purpose of this paper is to record the operation of MySQL read-write separation. It is my operation record. I did not explain the principle and operation settings in detail. If you need to explain in detail, it is not recommended to read.

Environmental preparation:

linux system version Corresponding server address data base Edition
CentOS7 192.168.222.132 MySQL 5.6
CentOS7 192.168.222.133 MySQL 5.6
CentOS7 192.168.222.134 MySQL 5.6

Step 1: establish a time synchronization environment

  • Install npt using yum
yum install ntp –y
  • Modify the configuration file corresponding to ntp after installation
vi /etc/ntp.conf
  • Add the following configuration: the address in the picture corresponds to the address of our own server
restrict 192.168.222.132 mask 255.255.255.0 nomodify notrap
server 127.127.1.0
fudge 127.127.1.0 stratum 8

  • Start ntp service
systemctl start ntpd
  • Check whether ntp is installed and running successfully. If it works normally, we can get the following information
ntpq -p

Make the above configuration for all three of our services. After the configuration, we can start to set up the main mysql server

Step 2: configure MySQL master server

  • Open my.cnf configuration file and configure as follows
# Open file
vi /etc/my.cnf

Locate the section in the configuration file and modify it as follows

server-id=132   # Use ip backend as our primary service id
log_bin=mysql-bin  # Turn on the MySQ L binary log system.
  • Log in to mysql and get the database list
mysql -uroot -P3306 -p123456 -h192.168.222.132

show databases;

binlog-do-db=test  # The name of the database to be synchronized is test. Other databases are not synchronized.
binlog-ignore-db=mysql   #The mysql system database is not synchronized, and each database corresponds to a row.
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
  • Configuration complete, duplicate service
service mysql restart
  • See if our configuration is successful
show master status;

If the result is consistent with our configuration, our master server configuration is successful! Can be configured from the server

Configure slave server

  • Open my.cnf configuration file and configure as follows
# Open file
vi /etc/my.cnf

Locate the section in the configuration file and modify it as follows

server-id=133   # Use ip backend as our primary service id
log_bin=mysql-bin  # Turn on the MySQ L binary log system.
  • Log in to mysql and get the database list
mysql -uroot -P3306 -p123456 -h192.168.222.133

show databases;

binlog-do-db=test  # The name of the database to be synchronized is test. Other databases are not synchronized.
binlog-ignore-db=mysql   #The mysql system database is not synchronized, and each database corresponds to a row.
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
  • Configuration complete, duplicate service
service mysql restart

Key operations to be done from the server

  • Create an account for the slave server that can log in to the master server.
# Log in to the main server and create a new account
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.222.133' IDENTIFIED BY '123456';
# Refresh authority
FLUSH PRIVILEGES;
  • Then use the root user to connect to mysql and execute the following statement
change master to
master_host='192.168.222.132',
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000011',
master_log_pos=1243;
  • After configuration is complete, start the slave server
start slave;
  • View start from server status
show slave status \G;


The above two "Yes" will prove that the configuration is successful

  • If the configuration is not correct, you need to stop the slave server first
stop slave;
  • Then reset the slave server
reset slave;

Pit settlement


If there is an error reported, we need to stop from the server first. The reason for this problem is that when I installed the database before, I didn't find that their versions were inconsistent. Just stop updating the version and follow the steps below again.

# Stop the slave server first
stop slave;
# Reset slave
reset slave;
# Then check the configuration and reconfigure it again
change master to
master_host='192.168.222.132',
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000011',
master_log_pos=709;
# Start again
start slave;
# View server status
show slave status \G;

Try to create a table to see if it is synchronized

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Self increment id',
  `user_name` varchar(255) DEFAULT NULL,
  `pass_word` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

We've made it here!

Be careful:

When we configure the third service, we need to create a new account of the primary server for the third server to access. At the same time, because we are a read-write separation environment installed on the local virtual machine, every time we start mysql or shut down the virtual machine, the position of mysql will change, so we need to follow the configuration changes, check each time before starting the read-write environment, and modify the configuration and the steps above to solve the mining pit.

Be a blogger with a bottom line

Tags: Java MySQL Database yum Linux

Posted on Mon, 04 Nov 2019 18:40:30 -0800 by holladb