Environment: 2 VM virtual machines centos7 x86? 64 bit system
Configuration environment requirements:
I. two virtual machines need to turn off iptables, turn off firewalld, turn off selinux and turn on time synchronization parameters to ensure that the time of the two virtual machines is consistent
2. The sshd service of the two virtual machines needs to open port 22 (only port 22 is supported in Xenon code for ssh to access each other)
3. The startup user of Xenon service and mysql service must be the same user. The simulation demonstration of this example uses mysql as the system user
IV. mysql users of two virtual machine systems should be allowed to log in by shell, and mysql users of two virtual machines can access each other without secret key
V. the version of mysql installed on the two virtual machine systems must be above mysql 5.7 (including mysql 5.7), and the parameters of semi synchronous replication (Xenon is based on semi synchronous replication) should be enabled for the two mysql systems
6. SSH pass software should be installed on both virtual machine systems
7. In the xenon.json configuration file of the two virtual machine systems, the shell command should be called to execute the relevant commands. Since the xenon service is started by mysql, a common user of the system, when executing command line commands through the xenon.json file,
You need to authorize the sudo permission of mysql system users to execute system commands
VIII. Several account permissions should be configured in the xenon.json configuration file to enable the xenon service to automatically create the master-slave replication relationship of mysql, and automatically switch the mysql replication relationship and automatic failure recovery
Tip: when configuring mysql copy account and password in the xenon.json configuration file, the xenon service can automatically create the copy account, and the permission given is%. And automatically create a replication relationship. However, an error will be reported in the test, indicating that the permission to copy the account is incorrect.
So in the process of this demonstration, I directly create the same copy account on 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 to read and write on the master database, so in the mysql Cluster maintained by xenon, the slave database is not allowed to write. After the master database fails, the node slave database will switch to the master database, so the original read-only permission will be changed to read-write through the account login database modification permission
grant all on *.* to codeuser@'172.16.0.%' identified by 'rrtestjianwei';flush privileges;
##Allow the account, password and IP address of the code connection library. It is recommended to directly give the service ip 172.16.0.100 bound to the network card here as the only code connection library address
IX. IP address and binding / etc/hosts of two virtual machines
System IP address 10.0.0.130 172.16.0.130 10.0.0.131 172.16.0.131
Tip: when testing on two physical machines, at the beginning, because the two physical machines have different network interfaces (one is at network interface 2 and the other is at network interface 3), the network cards configured with intranet ip address are also different. In the xenon.json configuration file of the two machines, the network card em1 bound with service ip is bound.
Although in the later test, the xenon services on both machines can be started successfully, and the respective mysql services can also be pulled up. Add the opposite node mysql on / data/xenon/bin/xenoncli cluster add ip:8801 on both machines.
And the service ip can also be successfully bound to the preset em1. Then the following problems arise: on the machine with service ip binding, you can log in to the database through the service ip and its own intranet ip, but on the machine without service ip binding, you can log in to the opposite mysql service through the service ip
Unexpectedly, I was refused to log in to mysql service (the permission to log in to mysql service is OK)
So let the computer room plug these two internal network cables into the same network card again, and then configure their respective internal network ip addresses on the same network card. At the same time, specify the binding service ip in the xenon.json configuration file to the network card of the internal network cable. At this time, the above problems are solved
Bind / etc/hosts:
[root@mgr01 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 172.16.0.130 mgr01 172.16.0.131 mgr03 [root@mgr03 ~]# cat /etc/hosts 172.16.0.130 mgr01 172.16.0.131 mgr03
The following describes the specific configuration steps
Tip: the following configuration steps are to be performed on both virtual machines
First, install the binary version 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 mysql echo "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 //Start mysql: mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf & [root@mgr01 ~]# cat .my.cnf [client] socket = /tmp/mysql.sock user=root password=123456 prompt="(\\u@\\'mgr01':\\p)[\\d]>"
Log in to mysql for account authorization:
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;
Note: the above steps are performed on both machines to install mysql. It is required that the / data/mysql/mysql3306/my3306.cnf configuration file should enable the parameters of MySQL semi synchronous replication
The following parameters are to be written to the / data/mysql/mysql3306/my3306.cnf configuration file:
plugin-load="semisync_master.so;semisync_slave.so" rpl_semi_sync_master_enabled=OFF rpl_semi_sync_slave_enabled=ON rpl_semi_sync_master_wait_no_slave=ON rpl_semi_sync_master_timeout=1000000000000000000 ##Parameter purpose is to prevent semi synchronous replication from being converted to asynchronous replication
Second, install the xenon service
Introduction:
Xenon is a self-contained binary that does not require other system libraries at the operating system level. It's built on Linux. There are no prompts for MS Windows and OS / x, and the 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 MySQL version is preferably 5.7 or higher. For more information, see my.cnf
Address: https://github.com/radondb/xenon/blob/master/docs/config/MySQL.md
The following MySQL semi synchronous replication parameters should be written to the / data/mysql/mysql3306/my3306.cnf configuration file
plugin-load="semisync_master.so;semisync_slave.so" rpl_semi_sync_master_enabled=OFF rpl_semi_sync_slave_enabled=ON rpl_semi_sync_master_wait_no_slave=ON rpl_semi_sync_master_timeout=1000000000000000000
Because the xenon service is developed in go language, the go environment is required to run, and the go version 1.8 or higher is required
2.1 install go environment
Go version 1.8 or higher is required (sudo apt install golang for ubuntu and yum install golang for centOS / redhat). Using binary to install golang, the version is go1.9.3.linux-amd64.tar.gz go binary package download address: wget https://storage.googleapis.com/golang/go1.9.3.linux-amd64.tar.gz tar xf go1.11.linux-amd64.tar.gz -C /usr/local/ [root@mgr01 ~]# tail -2 /etc/profile export GOROOT=/usr/local/go export PATH=$PATH:$GOROOT/bin [root@mgr01 ~]# go version go version go1.11 linux/amd64
2.2 install the xenon service
1 Download: git clone https://github.com/radondb/xenon.git cd xenon 2.Compiling and constructing make build ls bin/ xenon xenoncli 3.To 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 that the account running xenon must be consistent with the mysql account. For example, using the ubuntu account to start xenon requires the permission of ubuntu mysql and mysql directory.
This is different from the traditional mysql place. No mysql account is needed. The colleague running the xenon account is the mysql account.
Note: the following is a summary of the command line examples. For simplicity, let's assume that xenon is in 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. Make sure to use - c or -- config to specify the location of the xenon ﹣ config ﹣ file.
2.3. 2 machines create system user mysql
Two machines create system user mysql, and the two machines realize the access of MySQL users without secret key, and the MySQL system account can only be accessed through port 22
useradd mysql
passwd mysql
Authorizing the sudo permission of mysql user can execute the following commands:
[root@mgr01 ~]# tail -1 /etc/sudoers mysql ALL=(ALL) NOPASSWD: /usr/sbin/ip
The system user mysql between the two virtual machines is mutually keyless:
ssh-keygen -t rsa .ssh/authorized_keys chmod 600 .ssh/authorized_keys
2.4. Start the xenon service:
The content of xenon.json of 172.16.0.130 machine is as follows: (to change the IP address of 172.16.0.131 configuration file to the address of 172.16.0.131 internal network card)
[mysql@mgr01 ~]$ cat /etc/xenon/xenon.json
{
"server":
{
"endpoint":"172.16.0.130:8801"
},
"raft":
{
"meta-datadir":"raft.meta",
"heartbeat-timeout":1000,
"election-timeout":3000,
"leader-start-command":"sudo /usr/sbin/ip a a 172.16.0.100/16 dev eth0 && arping -c 3 -A 172.16.0.100 -I eth0",
"leader-stop-command":"sudo /usr/sbin/ip a d 172.16.0.100/16 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 command:
172.16.0.130 machine operation:
[mysql@mgr01 ~]$ /data/xenon/bin/xenon -c /etc/xenon/xenon.json > /data/xenon/xenon.log 2>&1 &
To add a xenon node:
[mysql@mgr01 xenon]$ /data/xenon/bin/xenoncli cluster add 172.16.0.131:8801 2019/12/08 23:13:29.937943 [WARNING] cluster.prepare.to.add.nodes[172.16.0.131:8801].to.leader[] 2019/12/08 23:13:29.938024 [WARNING] cluster.canot.found.leader.forward.to[172.16.0.130:8801] 2019/12/08 23:13:29.950792 [WARNING] cluster.add.nodes.to.leader[].done
To view the xenon node:
[mysql@mgr01 ~]$ /data/xenon/bin/xenoncli cluster status +-------------------+--------------------------------+---------+---------+--------------------------+---------------------+----------------+-------------------+ | ID | Raft | Mysqld | Monitor | Backup | Mysql | IO/SQL_RUNNING | MyLeader | +-------------------+--------------------------------+---------+---------+--------------------------+---------------------+----------------+-------------------+ | 172.16.0.130:8801 | [ViewID:16 EpochID:1]@LEADER | RUNNING | ON | state:[NONE] | [ALIVE] [READWRITE] | [true/true] | 172.16.0.130:8801 | | | | | | LastError: | | | | +-------------------+--------------------------------+---------+---------+--------------------------+---------------------+----------------+-------------------+ | 172.16.0.131:8801 | [ViewID:16 EpochID:1]@FOLLOWER | RUNNING | ON | state:[NONE] | [ALIVE] [READONLY] | [true/true] | 172.16.0.130:8801 | | | | | | LastError: | | | | +-------------------+--------------------------------+---------+---------+--------------------------+---------------------+----------------+-------------------+ (2 rows) //After 6s of operation, read only becomes a [READONLY and a READWRITE
172.16.0.131 machine operation:
[mysql@mgr03 ~]$ /data/xenon/bin/xenon -c /etc/xenon/xenon.json > /data/xenon/xenon.log 2>&1 &
[mysql@mgr03 ~]$ /data/xenon/bin/xenoncli cluster add 172.16.0.130:8801 [mysql@mgr03 ~]$ /data/xenon/bin/xenoncli cluster status +-------------------+--------------------------------+---------+---------+--------------------------+---------------------+----------------+-------------------+ | ID | Raft | Mysqld | Monitor | Backup | Mysql | IO/SQL_RUNNING | MyLeader | +-------------------+--------------------------------+---------+---------+--------------------------+---------------------+----------------+-------------------+ | 172.16.0.131:8801 | [ViewID:16 EpochID:1]@FOLLOWER | RUNNING | ON | state:[NONE] | [ALIVE] [READONLY] | [true/true] | 172.16.0.130:8801 | | | | | | LastError: | | | | +-------------------+--------------------------------+---------+---------+--------------------------+---------------------+----------------+-------------------+ | 172.16.0.130:8801 | [ViewID:16 EpochID:1]@LEADER | RUNNING | ON | state:[NONE] | [ALIVE] [READWRITE] | [true/true] | 172.16.0.130:8801 | | | | | | LastError: | | | | +-------------------+--------------------------------+---------+---------+--------------------------+---------------------+----------------+-------------------+ //After 6s of operation, read only becomes a [READONLY and a READWRITE
At the same time, the service IP 172.16.0.100 address is bound to the 10.0.0.130 machine
[mysql@mgr01 xenon]$ ip a|grep 172.16.0.100
inet 172.16.0.100/16 scope global eth0Log in mysql through the service ip, and then create a test library:
[mysql@mgr01 xenon]$ mysql -ucodeuser -h172.16.0.100 -p'rrtestjianwei' -e "create database test03;show databases;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test01 | | test02 | | test03 | +--------------------+ [mysql@mgr01 xenon]$
Third, the demonstration of xenon management mysql one master one slave
Perform the failure demonstration, kill the main database, try to connect the database through the service ip, about 25s can link the main database successfully, and then delete the data in the new main database after the connection
The main database can be switched in 25s
Perform a failure demonstration,
1. Log in to the master database, execute shutdown to shut down the master database, try to connect to the database through the service ip, about 17s to 20s before rebinding the service ip to the machine network card of the master database, and then connect to the master database through the service ip successfully, and then you can operate to view, write and update the data of the database
(physical server tested several times)
2. Log in to the server of the master database, kill-9 to 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, and then bind the service ip to the network card of the master, and then successfully connect to the master database through the service ip, so that the database can be viewed, written and updated
(physical server tested several times)
3. Log in to the server of the master database, remove the data directory data, and close the mysql service of the master database. At this time, although the service ip has drifted to another mysql after about 1m minutes, this database can only read, not write. It will be rammed when writing (it is tested by physical server many times)
4. If you just shut down the slave database, write a record remotely through the service ip address and then shut down the mysql instance;
The command is as follows:
[root@slavedb 3306]# mysql -ucodeuser -h192.168.1.100 -p'rrtestjianwei' -e "INSERT INTO test01.test1(username,password,create_time) values('tomcat', 'xiaohuahua',now());"; mysql -e "shutdown;After executing the shutdown command, immediately execute the following command to insert the record:
mysql -ucodeuser -h192.168.1.100 -p'rrtestjianwei' -e "INSERT INTO test01.test1(username,password,create_time) values('tomcat', 'xiaohuahua',now());"At this time, it will be rammed. If it is executed repeatedly, it will not work. The duration is 10-15s, and then it can be written normally. But at this time, the same record will be written to the master database twice. (physical server tested several times)
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 17:25:32 | | 38 | tomcat | xiaohuahua | 2019-12-12 17:25:45 | +----+----------+------------+---------------------+
5. If you remove the data directory of the slave database, 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]# ls
binlog data_bak logs my.cnf tmp
[root@slavedb ~]# mysql -ucodeuser -h192.168.1.100 -p'rrtestjianwei' -e "INSERT INTO test01.test1(username,password,create_time)values('tomcat', 'xiaohuahua',now());";mysql -e "shutdown;"
[mysql@localhost xenon]$ mysql -ucodeuser -h192.168.1.100 -p'rrtestjianwei' -e "INSERT INTO test01.test1(username,password,create_time) values('tomcat', 'xiaohuahua',now());"The continuous tamping time is between 10-15s before normal writing, but at this time, the same record will be written to the master database twice. (physical server tested several times)
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 (0.00 sec)
To create a test table and insert a 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`) #Primary 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:8801,192.168.1.182:8801 /data/xenon/bin/xenoncli cluster remove 192.168.1.105:8801 sudo /usr/sbin/ip a a 192.168.1.100/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;
Here is the content of the xenon.json configuration file of one of the Dell R620 physical machines:
[root@slavedb ~]# cat /etc/xenon/xenon.json
{
"server":
{
"endpoint":"192.168.1.39:8801"
},
"raft":
{
"meta-datadir":"raft.meta",
"heartbeat-timeout":1000,
"election-timeout":3000,
"leader-start-command":"sudo /usr/sbin/ip a a 192.168.1.100/32 dev em3 && arping -c 3 -A 192.168.1.100 -I em3",
"leader-stop-command":"sudo /usr/sbin/ip a d 192.168.1.100/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 brief introduction and demonstration, recorded here, convenient for self reference, and hope to help the netizens in need