MySQL server cluster experiment


Author: Blue Tears

The first master-slave backup experiment

Experimental environment: prepare 3 virtual machines, centos7.9 system, ip respectively

192.168.29.10192.168.29.20192.168.29.30

Environmental preparation

wget http://repo.mysql.com/mysql57-community-release-el7-9.noarch.rpmsudo rpm -ivh mysql57-community-release-el7-9.noarch.rpmyum -y install mysql mysql-server
iptables -Fsystemctl stop firewalldsystemctl disable firewalldsetenforce 0

Overall strategy: authorize on the master server, save authorization information on the slave server

1 Main server configuration 192.168.29.10
vim /etc/my.cnf

log-bin=mysql-binserver-id=10
systemctl restart mysqld

mysql -uroot -p123456

grant replication slave on *.* to [email protected]'192.168.29.20' identified by '123456';show master status;

2 Configure 192.168.29.20
vim /etc/my.cnf from the server

log-bin=mysql-binserver-id=10
systemctl restart mysqld

mysql -uroot -p123456

change master to master_user='slave',master_password='123456',master_host='192.168.29.20',master_log_file='mysql-bin.000004',master_log_pos=259;start slave;show slave status\G;


View authorization information

cat /var/lib/mysql/master.info
mysql -uslave -p123456 -h192.168.29.10telnet 192.168.29.10 3306
grant all privileges  on *.* to [email protected]'%' identified by "123456";FLUSH PRIVILEGES;set global validate_password_policy=0;set global validate_password_length=1;ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';show variables like 'server_id';show variables like 'server_uuid';show slave hosts;show variables like 'datadir';select uuid();


vim var/lib/mysql/auto.cnf
//Modify server-uuid

systemctl restart mysqld

Second master master backup experiment

1 Main server configuration 192.168.29.10
vim /etc/my.cnf

log-bin=mysql-binserver-id=10#replicate-do-db=testbinlog-ignore-db=mysql#binlog-ignore-db=information_schema#auto-increment-increment=2#auto-increment-offset=1
systemctl restart mysqld

2 Main server configuration 192.168.29.20
vim /etc/my.cnf

log-bin=mysql-binserver-id=20#replicate-do-db=testbinlog-ignore-db=mysql#binlog-ignore-db=information_schema#auto-increment-increment=2#auto-increment-offset=1
systemctl restart mysqld

10 is the main authorization, 20 is the receiving authorization from the
mysql -uroot -p123456

grant replication slave on *.* to [email protected]'192.168.29.20' identified by '123456';show master status;

mysql -uroot -p123456

change master to master_user='slave',master_password='123456',master_host='192.168.29.10',master_log_file='mysql-bin.000004',master_log_pos=259;start slave;show slave status\G;


20 is the main authorization, 10 is the receiving authorization from the
mysql -uroot -p123456

grant replication slave on *.* to [email protected]'192.168.29.10' identified by '123456';show master status;

mysql -uroot -p123456

change master to master_user='slave',master_password='123456',master_host='192.168.29.20',master_log_file='mysql-bin.000004',master_log_pos=259;start slave;show slave status\G;

Both 192.168.29.10 and 192.168.29.20 have slaves enabled, and they are mutually active, with 10 being the master and 20 being the slave; 10 being the slave and 20 being the master.

start slave;

The third one-master-multiple-slave experiment

1 Main server 192.168.29.10 configuration

mysql -uroot -p123456

stop slave;grant replication slave on *.* to [email protected]'192.168.29.30' identified by '123456';show master status;


2 Configure from the server 192.168.29.20

mysql -uroot -p123456

change master to master_user='slave',master_password='123456',master_host='192.168.29.10',master_log_file='mysql-bin.000004',master_log_pos=340;start slave;


3 Configure from the server 192.168.29.30

mysql -uroot -p123456

change master to master_user='slave',master_password='123456',master_host='192.168.29.10',master_log_file='mysql-bin.000004',master_log_pos=340;start slave;

The fourth multi-master one-slave experiment

1 The main server 192.168.29.10 configures
vim /etc/my.cnf

log-bin=mysql-binserver-id=10
systemctl restart mysqld

mysql -uroot -p123456

stop slave;mysql_upgrade -u root -p123456grant replication slave on *.* to [email protected]'192.168.29.30' identified by '123456';show master status;

2 Configure
vim /etc/my.cnf on the main server 192.168.29.20

log-bin=mysql-binserver-id=20

mysql -uroot -p123456

stop slave;mysql_upgrade -u root -p123456grant replication slave on *.* to [email protected]'192.168.29.30' identified by '123456';show master status;

3 Configure
vim /etc/my.cnf from the server 192.168.1.30

log-bin=mysql-binserver-id=30 [mysqld_multi]mysqld=/usr/bin/mysqld_safemysqladmin=/usr/bin/mysqladminlog=/tmp/multi.log [mysqld10]port=3306datadir=/var/lib/mysqla/pid-file=/var/lib/mysqla/mysqld.pidsocket=/var/lib/mysqla/mysql.sockuser=mysqlserver-id=30 [mysqld20]port=3307datadir=/var/lib/mysqlb/pid-file=/var/lib/mysqlb/mysqld.pidsocket=/var/lib/mysqlb/mysql.sockuser=mysqlserver-id=30

Initialize the database, generate directories mysqla, mysqlb

mysql_install_db --datadir=/var/lib/mysqla --user=mysqlmysql_install_db --datadir=/var/lib/mysqlb --user=mysql
yum install libaio numactlmysqld --initialize --datadir=/var/lib/mysqla --user=mysql
chown -R mysql /var/lib/mysqla/chown -R mysql /var/lib/mysqlb/


Start the slave thread

mysqld_multi --defaults-file=/etc/my.cnf start 10mysqld_multi --defaults-file=/etc/my.cnf start 20netstat -antp

Log in and save authorization information

mysql -uroot -P 3306 -S /var/lib/mysqla/mysql.sockchange master to master_user='slave',master_password='123456',master_host='192.168.29.10',master_log_file='mysql-bin.000004',master_log_pos=340;start slave; mysql -uroot -P 3307 -S /var/lib/mysqla/mysql.sockchange master to master_user='slave',master_password='123456',master_host='192.168.29.20',master_log_file='mysql-bin.000004',master_log_pos=340;start slave;

The fifth amoeba middleware experiment-read-write separation

The role of middleware: maximize the performance of the
database The middleware of the database are: mysql proxy, atlas, amoeba.

1 Configuration of amoeba server: 192.168.29.10

rpm -qa | grep javarpm -e --nodeps java-1.8.0-openjdk-headless-1.8.0.65-3.b17.el7.x86_64rpm -e --nodeps java-1.8.0-openjdk-1.8.0.65-3.b17.el7.x86_64


vim /etc/profile

export JAVA_HOME=/usr/local/jdk1.8.0_291export PATH=$JAVA_HOME:/bin:$PATHexport CLASSPATH=.:$JAVA_HOME/bin/tools.jar:$JAVA_HOME/lib/dt.jar:$CLASSPATH
source /etc/profilejava -version


Install amoeba software

yum -y install gcc*unzip amoeba.zip -d /usr/local/amoebachmod -R +x /usr/local/amoeba/bin/


Configure amoeba software parameters
vim amoeba/conf/amoeba.xml

<property name="defaultPool">write</property><property name="writePool">write</property><property name="readPool">read</property>


vim /usr/local/amoeba/conf/dbServers.xml

server区域 user root password 123456dbServerList区域dbserver write server1dbserver read server1,server2defaultpool writewritepool writereadpool read


Start amoeba
vim /usr/local/amoeba/bin/amoeba
Xss128 and modify it to Xss256

nohup bash -x /usr/local/amoeba/bin/amoeba &/usr/local/amoeba/bin/amoeba start &


Authorize to the specified user on the master and slave server

grant all on web.* to [email protected]'192.168.29.10' identified by '123456';


2 Configuration of the main server: 192.168.29.20
vim /etc/my.cnf

log-bin=mysql-binserver-id=20

mysql -uroot -p123456

stop slave;grant replication slave on *.* to [email protected]'192.168.29.30' identified by '123456';show master status;


3 Configuration of the slave server: 192.168.29.30
vim /etc/my.cnf

log-bin=mysql-binserver-id=30

mysql -uroot -p123456

change master to master_user='slave',master_password='123456',master_host='192.168.29.20',master_log_file='mysql-bin.000004',master_log_pos=340;start slave;

4 Test read and write separation

./amoeba stop./amoeba start &
mysql -uamoeba -p -h 192.168.29.10 -P 8066  #主服务器和从服务器
mysql -uamoeba -p123456 -h 192.168.29.20  #amoeba服务器mysql -uamoeba -p123456 -h 192.168.29.30  #amoeba服务器mysql -uamoeba -p123456 -h 192.168.29.10 -P 8066 #amoeba服务器

Additional Information

Shang Silicon Valley Learning Materials Website

https://shimo.im/docs/pQjP3JhCchCQPy3J/read