Qingyun Xenon manages mysql master-slave replication test 01/15 Update SLTechnology News&Howtos

Qingyun Xenon manages mysql master-slave replication test

2025-01-15 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

Shulou(Shulou.com)06/03 Report--

Environment: 2 VM virtual machines CentOS7 X86bilateral 64-bit system

Configure the environment requirements:

One or two virtual machines need to turn off iptables, turn off firewalld, turn off selinux, and enable time synchronization parameters to ensure that the time of the two virtual machines is the same.

Port 22 is required for the sshd service of two virtual machines (only port 22 is supported for ssh access to each other in the Xenon code)

Third, the Xenon service startup user must be the same user as the mysql service startup user. This example simulation demonstration is the system user mysql.

Four, two virtual machine system user mysql, to allow shell login, and two virtual machine mysql users can avoid secret key access to each other

5. The version of mysql installed on 2 virtual machine systems must be above mysql5.7 version (including mysql5.7), and 2 mysql must enable semi-synchronous replication parameters (Xenon is based on semi-synchronous replication)

Sshpass software should be installed on six or two virtual machine systems.

Seven, two virtual machine systems on the Xenon.json configuration file to call the shell command to execute the relevant commands. Because the xenon service is started by mysql, an ordinary user of the system, when executing command-line commands through the xenon.json file

You need to authorize sudo permissions for relevant commands of mysql system users to execute system commands

8. Several account permissions should be configured in the xenon.json configuration file to enable xenon services to automatically create master-slave replication relationships for mysql, as well as automatically switch mysql replication relationships and automatic fault recovery and other operations.

Tip: when configuring mysql replication accounts and passwords in the xenon.json configuration file, the xenon service can automatically create replication accounts with% permission. And automatically create replication relationships. However, it is found that there will be an error in the test, indicating that the permissions of the copy account are incorrect.

So in the course of this demonstration, I created the same replication account directly on the two mysql instances.

The command is as follows:

Grant replication slave on *. * to repuser@'172.16.0.%' identified by 'repuser9slave'; flush privileges; # # copy account and password grant all on *. * to root@'127.0.0.1' identified by' rrtestjianwei';flush privileges

# # in the HA+ mysql cluster built by xenon, it is recommended that both reads and writes are on the master database, so in the mysql cluster maintained by xenon, the slave library is not allowed to write. When the master master library fails, the node slave library will be switched to the master database, so the original read-only permission should be changed to read-write through this account.

Grant all on *. * to codeuser@'172.16.0.%' identified by 'rrtestjianwei';flush privileges

# # allow the account and password of the code connection library, as well as the IP address. It is recommended that you directly give the service ip 172.16.0.100 bound to the network card as the only address of the code connection library.

9. IP address and binding / etc/hosts of 2 virtual machines

System IP address 10.0.0.130 172.16.0.13010.0.131 172.16.0.131

Tip: when testing on two physical machines, at the beginning, due to the different network ports of the two physical machines plugging in the intranet cable (one in the network port 2 and the other in the network port 3), the network card configured with the intranet ip address is also different. Bind the service ip to the network card em1 in the xenon.json configuration file of the two machines.

Although in the later tests, the xenon services on both machines can be started successfully, and the respective mysql services can also be pulled up to add each other's node mysql on the two machines / data/xenon/bin/xenoncli cluster add ip:8801.

And the service ip can also be successfully bound to the preset em1. Then the following problem arises: through this service ip and his own intranet ip, he can log in to the database on the machine bound with the service ip, but log in to the peer mysql service through this service ip on the ip machine without the binding service.

Unexpectedly refused to log in to the mysql service (no problem with the permission to log in to the mysql service)

So let the computer room re-plug the two internal network cables into the same network card, then configure their respective internal network ip addresses on the same network card, and at the same time specify in the xenon.json configuration file to bind the service ip to the network card of the plug-in network cable. At this time, the above problems can be solved.

Bind / etc/hosts:

[root@mgr01 ~] # cat / etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4::1 localhost localhost.localdomain localhost6 localhost6.localdomain6172.16.0.130 mgr01172.16.0.131 mgr03 [root@mgr03 ~] # cat / etc/hosts172.16.0.130 mgr01172.16.0.131 mgr03 describes the specific configuration steps below

Tip: the following configuration steps are to be performed on both virtual machines

First, install the binary version of mysql5.7.24

Tar xf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz-C / usr/local/cd / usr/local/ln-sv mysql-5.7.24-linux-glibc2.12-x86_64 mysqlecho "export PATH=$PATH:/usr/local/mysql/bin" > / etc/profile.d/mysql.sh source / etc/profile initialize mysql:mysqld-- defaults-file=/data/mysql/mysql3306/my3306.cnf-- initialize launch mysql:mysqld- -defaults-file=/data/mysql/mysql3306/my3306.cnf & [root@mgr01 ~] # cat .my.cnf [client] socket = / tmp/mysql.sockuser=rootpassword=123456prompt= "(\\ u@\\ 'mgr01':\\ p) [\\ d] >"

Grant replication slave on *. * to repuser@'172.16.0.%' identified by 'repuser9slave'; flush privileges;grant all on *. * to root@'127.0.0.1' identified by' rrtestjianwei';flush privileges;grant all on *. * to codeuser@'172.16.0.%' identified by 'rrtestjianwei';flush privileges

Tip: perform the above steps on both machines to install mysql. Parameters that require / data/mysql/mysql3306/my3306.cnf configuration files to enable semi-synchronous replication of MySQL

The following parameters are written to the / data/mysql/mysql3306/my3306.cnf configuration file:

The plugin-load= "semisync_master.so;semisync_slave.so" rpl_semi_sync_master_enabled=OFFrpl_semi_sync_slave_enabled=ONrpl_semi_sync_master_wait_no_slave=ONrpl_semi_sync_master_timeout=1000000000000000000 # # parameter is designed to prevent semi-synchronous replication from being converted to asynchronous replication.

Second, install the xenon service

Introduction:

Xenon is a self-contained binary file that does not require other system libraries at the operating system level. It is built on Linux. There are no prompts for MS Windows and OS / X, and this version is not compatible with Windows and OS / X.

It is a stand-alone application. When configured to run with the MySQL backend, mysqld is required.

Xenon uses GTID semi-synchronous parallel replication technology, and the MySQL version is preferably version 5.7 or later. For more information, see my.cnf

Address: https://github.com/radondb/xenon/blob/master/docs/config/MySQL.md

The following semi-synchronous replication parameters for mysql are written to the / data/mysql/mysql3306/my3306.cnf configuration file

Plugin-load= "semisync_master.so;semisync_slave.so" rpl_semi_sync_master_enabled=OFFrpl_semi_sync_slave_enabled=ONrpl_semi_sync_master_wait_no_slave=ONrpl_semi_sync_master_timeout=1000000000000000000

Because xenon services are developed in the go language, a go environment is required to run, and Go version 1.8 or later is required

2.1 install the go environment

Go version 1.8 or later is required ("sudo apt install golang" for ubuntu and "yum install golang" for centOS / redhat). Install golang in binary, version of which is go1.9.3.linux-amd64.tar.gzgo binary package download address: wget https://storage.googleapis.com/golang/go1.9.3.linux-amd64.tar.gztar xf go1.11.linux-amd64.tar.gz-C / usr/local/ [root@mgr01 ~] # tail-2 / etc/profileexport GOROOT=/usr/local/goexport PATH=$PATH:$GOROOT/bin [root@mgr01 ~] # go versiongo version go1.11 linux/amd64

2.2 install the xenon service

Download: git clone https://github.com/radondb/xenon.gitcd xenon2. Compile and build make build ls bin/xenon xenoncli3. Configure config cp xenon/conf/xenon-sample.conf.json / etc/xenon/xenon.json [mysql@mgr01 ~] $cat / data/xenon/bin/config.path/etc/xenon/xenon.json

It should be noted here that the account under which xenon is running must be the same as the mysql account, for example, to start xenon with the ubuntu account, which requires the startup of ubuntu mysql and the permissions of the mysql directory.

Unlike traditional mysql places, you don't need a mysql account, and the colleague who runs the xenon account is the mysql account.

Note: the following is a summary of the command line example. For simplicity, let's assume that xenon is under your specified path. If not, replace xenon with / path/to/xenon.

In the xenon command path, you need to have a file named config.path, which is the absolute path to the xenon.json file. Be sure to specify the location of the xenon_config_file using-c or-- config.

2.3,2 machines to create the system user mysql

Two machines create a system user mysql and mysql users do not have key access between the two machines, and the mysql system account can only be accessed at port 22.

Useradd mysql

Passwd mysql

To authorize the mysql user sudo privileges, you can execute the following command:

[root@mgr01 ~] # tail-1 / etc/sudoersmysql ALL= (ALL) NOPASSWD: / usr/sbin/ip

The system user mysql between the two virtual machines is exempt from secret key operation each other:

Ssh-keygen-t rsa .ssh / authorized_keys chmod 6000.ssh / authorized_keys

Start the xenon service:

The contents of the 172.16.0.130 machine xenon.json are as follows: (the 172.16.0.131 configuration file needs to replace the IP with the address of your own internal network card 172.16.0.131)

[mysql@mgr01 ~] $cat / etc/xenon/xenon.json {"server": {"endpoint": "172.16.0.130 etc/xenon/xenon.json 8801"}, "raft": {"meta-datadir": "raft.meta", "heartbeat-timeout": 1000, "election-timeout": 3000 "leader-start-command": "sudo / usr/sbin/ip an a 172.16.0.100dev eth0 16 dev eth0 & & arping-c 3-A 172.16.0.100-I eth0", "leader-stop-command": "sudo / usr/sbin/ip ad 172.16.0.100 dev eth0"}, "mysql": {"admin": "root", "passwd": "rrtestjianwei" "host": "127.0.0.1", "port": 3306, "basedir": "/ usr/local/mysql", "defaults-file": "/ data/mysql/mysql3306/my3306.cnf", "ping-timeout": 1000, "master-sysvars": "super_read_only=0 Read_only=0;sync_binlog=default;innodb_flush_log_at_trx_commit=default "," slave-sysvars ":" super_read_only=1;read_only=1;sync_binlog=1000 Innodb_flush_log_at_trx_commit=2 "}," replication ": {" user ":" repuser "," passwd ":" repuser9slave "}," backup ": {" ssh-host ":" 172.16.0.130 "," ssh-user ":" mysql "," ssh-passwd ":" rrtestjianwei669 "," ssh-port ": 22 "backupdir": "/ data/mysql/mysql3306/data", "xtrabackup-bindir": "/ usr/bin", "backup-iops-limits": 100000, "backup-use-memory": "1GB", "backup-parallel": 2}, "rpc": {"request-timeout": 500}, "log": {"level": "INFO"}}

Start the command:

172.16.0.130 Machine Operation:

[mysql@mgr01] $/ data/xenon/bin/xenon-c / etc/xenon/xenon.json > / data/xenon/xenon.log 2 > & 1 &

Add a xenon node:

[mysql@mgr01 xenon] $/ data/xenon/bin/xenoncli cluster add 172.16.0.131 cluster.canot.found.leader.forward.to 8801 2019-12-08 23 Fringe 29.937943 [WARNING] cluster.prepare.to.add.nodes [172.16.0.131: 8801] .to.leader [] 2019-12-08 2338024 [WARNING] cluster.canot.found.leader.forward.to [172.16.0.13014] 2019-12-08 23:13:29.950792 [WARNING] cluster.add.nodes.to.leader [] .done

View the xenon node:

172.16.0.131 Machine Operation:

At the same time, the service IP172.16.0.100 address is bound to the machine of 10.0.0.130

[mysql@mgr01 xenon] $ip a | grep 172.16.0.100 inet 172.16.0.100 Universe 16 scope global eth0

To demonstrate the failure, kill dropped the main library, tried to link the main library through the service ip connection library for about 25 seconds, and then deleted the data in the connected new main library.

The switching of the main library can be completed in 25s.

Conduct a fault demonstration

1. Log in to the master library, execute shutdown to shut down the main library, try to re-bind the service ip to the machine network card of the master library through the service ip connection library, and then successfully connect the main library through this service ip, and then you can operate to view, write, and update the data of the database.

(from multiple tests on the physical server)

two。 Log in to the server of the master library, kill-9 drop the mysql process, and then the xenon on the master machine will automatically pull up the mysql service of the master machine within 4s to 11s, then on the network card that binds the service ip to master, and then successfully connect to the main library through this service ip, you can operate the database to view, write, and update data.

(from multiple tests on the physical server)

3.。 Log in to the server of the master library, remove the data directory data, and disable the mysql service of the master library. Although the service ip has floated to another mysql after about 1m minutes, the library can only be read, not written. It will be rammed when writing (physical server has been tested many times)

4. If only shutdown shuts down the slave library, write a record remotely through the service ip address and then close the mysql instance

The command is as follows:

[root@slavedb 3306] # mysql-ucodeuser-h292.168.1.100-psamrtestjianwei'- e "INSERT INTO test01.test1 (username,password,create_time) values ('tomcat',' xiaohuahua',now ());"; mysql-e "shutdown

Immediately after executing the shutdown command, execute the following command to insert the record:

Mysql-ucodeuser-h292.168.1.100-pairrtestjianwei'- e "INSERT INTO test01.test1 (username,password,create_time) values ('tomcat',' xiaohuahua',now ())"

At this point, it will be rammed, and it will not work if it is executed many times in a row, and the duration will be 10-15s before it can be written normally, but at this time the same record will be written to the master library twice. (from multiple tests on the physical server)

As follows:

| | 31 | tomcat | xiaohuahua | 2019-12-12 17:20:11 | | 32 | tomcat | xiaohuahua | 2019-12-12 17:23:28 | 33 | tomcat | xiaohuahua | 2019-12-12 17:23:28 | 34 | tomcat | xiaohuahua | 2019-12-12 17:23:38 | | 35 | tomcat | xiaohuahua | 2019-12-12 17:23:40 | 36 | tomcat | xiaohuahua | 2019-12-12 17:25:32 | 37 | tomcat | xiaohuahua | 2019-12-12 | 12 17:25:32 | | 38 | tomcat | xiaohuahua | 2019-12-12 17:25:45 | +-- +

5. If you remove the data directory of the slave library, write a record remotely through the service ip address and then close the mysql instance

The command is as follows:

[root@slavedb ~] # mv data data_ bak [root @ slavedb 3306] # lsbinlog data_bak logs my.cnf tmp [root@slavedb ~] # mysql-ucodeuser-h292.168.1.100-pamphrtestjianwei'- e "INSERT INTO test01.test1 (username,password,create_time) values ('tomcat',' xiaohuahua',now ());"; mysql-e "shutdown "[mysql@localhost xenon] $mysql-ucodeuser-h292.168.1.100-pairrtestjianwei'- e" INSERT INTO test01.test1 (username,password,create_time) values ('tomcat',' xiaohuahua',now ()); "

The continuous tamping time is between 10 and 15 seconds before it can be written normally, but at this time the same record will be written to the master library twice. (from multiple tests on the physical server)

39 | tomcat | xiaohuahua | 2019-12-12 17:37:31 | | 40 | tomcat | xiaohuahua | 2019-12-12 17:37:31 | 41 | tomcat | xiaohuahua | 2019-12-12 17:37:42 | | 42 | tomcat | xiaohuahua | 2019-12-12 17:37:43 | +-+ 42 rows in set (sec)

Create a test table and insert the test statement:

CREATE TABLE `test1` (`id` int (8) NOT NULL AUTO_INCREMENT, `username` varchar (20) COLLATE utf8_unicode_ci NOT NULL, `password` varchar (20) COLLATE utf8_unicode_ci NOT NULL, `create_ time` varchar (20) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) # key ID) ENGINE=innodb AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;INSERT INTO test1 (username,password,create_time) values ('tomcat',' xiaohuahua',now ()) INSERT INTO test1 (username,password,create_time) values ('tomcat',' xiaohuahua',now ())

Here are the configuration commands on the dellR620 physical machine:

/ data/xenon/bin/xenon-c / etc/xenon/xenon.json > / data/xenon/xenon.log 2 > & 1 & / data/xenon/bin/xenoncli cluster status / data/xenon/bin/xenoncli cluster add 192.168.1.39 data/xenon/bin/xenoncli cluster status 8801 / data/xenon/bin/xenoncli cluster remove 192.168.1.105 sudo / usr/sbin/ip a 192.168.1.100 sudo 32 dev em3 & & arping-c 3-A 192.168 .1.100-I em3 sudo / usr/sbin/ip a d 192.168.1.100 32 dev em3

The following is the command to create a database account on the dellR620 physical machine:

Grant replication slave on *. * to repl@'192.168.1.%' identified by 'repl4slave'; flush privileges;grant all on *. * to root@'127.0.0.1' identified by' rrtestjianwei';flush privileges;grant all on *. * to codeuser@'192.168.1.%' identified by 'rrtestjianwei';flush privileges

The following is the contents of the xenon.json configuration file for one of the dellR620 physical machines:

[root@slavedb ~] # cat / etc/xenon/xenon.json {"server": {"endpoint": "192.168.1.39 endpoint"}, "raft": {"meta-datadir": "raft.meta", "heartbeat-timeout": 1000, "election-timeout": 3000 "leader-start-command": "sudo / usr/sbin/ip an a 192.168.1.100 dev em3 32 dev em3 & & arping-c 3-A 192.168.1.100-I em3", "leader-stop-command": "sudo / usr/sbin/ip ad 192.168.1.100 dev em3 32 dev em3"}, "mysql": {"admin": "root", "passwd": "rrtestjianwei" "host": "127.0.0.1", "port": 3306, "basedir": "/ usr/local/mysql", "defaults-file": "/ data/mysql/3306/my.cnf", "ping-timeout": 1000, "master-sysvars": "super_read_only=0 Read_only=0;sync_binlog=default;innodb_flush_log_at_trx_commit=default "," slave-sysvars ":" super_read_only=1;read_only=1;sync_binlog=1000 Innodb_flush_log_at_trx_commit=2 "}," replication ": {" user ":" repl "," passwd ":" repl4slave "}," backup ": {" ssh-host ":" 192.168.1.39 "," ssh-user ":" mysql "," ssh-passwd ":" rrtestjianwei669 "," ssh-port ": 22 "backupdir": "/ data/mysql/3306/data", "xtrabackup-bindir": "/ usr/bin", "backup-iops-limits": 100000, "backup-use-memory": "1GB", "backup-parallel": 2}, "rpc": {"request-timeout": 500}, "log": {"level": "INFO"}}

The above is a simple introduction and demonstration, recorded here, easy for yourself to consult, and hope to help netizens in need.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.