High Availability of MySQL-keepalivd + mutual master and slave, mysqlkeepalivd
Objective: to configure a keepalived Hot Standby architecture and master-slave Replication
Planning:
Master1 zlm177 192.168.17.177
Master2 zlm188 192.168.17.188
Vip 192.168.17.166
Environment: Red Hat Enterprise Linux 6.4
Percona Server 5.6.15
I. Software Installation
Go to the official website:
Keepalived-1.2.13.tar
Cp keepalived-1.2.13.tar/usr/local/src
Cd/usr/local/src
Tar zxvf keepalived-1.2.13.tar-C/opt/
Cd/opt/keepalived-1.2.13
./Configure -- prefix =/usr/local/keepalived
Make; makeinstall or make & makeinstall
Note that the gcc and openssl packages are missing during compilation. you can install them with yum install.
You can configure CentOS yum in RHEL6.4.
Yum install gcc
Yum install openssl-devel
You will also be prompted that the xxx dependency package needs to be installed and installed together. The advantage of using yum is that it is easy to install, so that the system can automatically determine which packages are needed, automatically download and install them. After compilation, the software installation is complete.
2. Configure software parameters (VRRP)
After the software is installed, the path of the configuration file is:
/Usr/local/keepalived/etc/keepalived. conf
Open the configuration file on the master end, clear the original content, and then add the following content:
! Configuration File for keepalived
Global_defs {-- global configuration
Notification_email {
Aaron8219@xxx.xxx -- mailbox for receiving notifications
}
Router_id mongoon8219 -- it can contain letters, numbers, and can be consistent or different. It is only an identifier.
}
Vrrp_instance my_177 {
State BACKUP -- BACKUP slave mode
Interface eth0
Virtual_router_id 88 -- the default value is 51, and the value range is 1 ~ An integer of 255. The master and slave sides must be consistent to indicate that they are in the same group.
Priority 90
Advert_int 1 -- check interval, 1 s by default
Nopreempt -- set the non-preemption mode,
Authentication {
Auth_type PASS
Auth_pass 1234
}
Virtual_ipaddress {-- specify vip,
192.168.17.166
}
}
# Vip Description: The vip is added and deleted as the state changes. It is added when the state is master and deleted when the state is backup, it is mainly determined by the priority. It has little to do with the value set by state (when the state is the same). As for whether the vip is on the master or slave end, it is also related to nopreempt, here, you can set multiple IP addresses for vip addresses.
# Nopreempt Description: it can only be set on a node whose state is backup, and the priority of this node must be higher than that of other nodes.
Virtual_server 192.168.17.166 3306 {-- Virtual Server ip address and port
Delay_loop 2
Lb_algo wrr -- Weighted Round Robin
Lb_kind DR
Persistence_timeout 60
Protocol TCP
Real_server 192.168.17.177 3306 {-- Real Server ip address and port
Weight 3 -- weight is 3
Yy_down/opt/mysql. sh -- specify the path of the suicide script
TCP_CHECK {
Connect_timeout 10
Nb_get_retry 3
Delay_before_retry 3
Connect_port 3306
}
}
}
The slave configuration file is similar. You only need to change the IP address and Instance name to your own, and set the priority of the slave end to 90, while that of the master end to 100.
One thing to note is that the state at both the master and slave ends is configured as backup, because nopreempt is used, that is, the non-preemption mode.
For example, after the master starts the mysql instance and keepalived, if the slave also starts the mysql instance and keepalived, the vip will not jump to the slave, even if its priority is 100, it must be greater than 90 on the master end
If nopreempt is not set, there are two cases:
1. The state is the same, that is, both master and backup
The vip address is assigned with a high priority and is irrelevant to the role.
2. Different States, namely, master-> backup or backup-> master
The vip address is assigned with a high priority and is irrelevant to the role.
If the premise is different, the results are the same, that is, the priority is dominant. If the priority is high, the vip will be moved to the vip.
Create a suicide script to determine whether the mysql process is started
Touch/opt/mysql. sh
Add the following content:
#! /Bin. sh
Pkill keepalived -- indicates kill the keepalived Process
Iii. Run the test
/Usr/local/keepalived/sbin/keepalived-f/usr/local/keepalived/etc/keepalived. conf-D
-F: Specifies the parameter file of keepalived.
-D indicates that detailed records are displayed in the operating system logs.
Check whether the keepalived process is started properly. You only need to check the logs in/var/log/messages.
Tail-30f/var/log/message
NOTE: If keepalived is started before mysql is started, the script specified in the policy_down parameter is executed, indicating that the mysql process is not found and keeplied's own process is killed.
Jul 25 02:51:22 zlm188 Keepalived [3440]: Starting Keepalived v1.2.13 (07/22, 2014)
Jul 25 02:51:22 zlm188 Keepalived [3441]: Starting Healthcheck child process, pid = 3442
Jul 25 02:51:22 zlm188 Keepalived [3441]: Starting VRRP child process, pid = 3443
Jul 25 02:51:22 zlm188 Keepalived_vrrp [3443]: Netlink reflector reports IP 192.168.17.188 added
Jul 25 02:51:22 zlm188 Keepalived_healthcheckers [3442]: Netlink reflector reports IP 192.168.17.188 added
Jul 25 02:51:22 zlm188 Keepalived_healthcheckers [3442]: Netlink reflector reports IP fe80: a00: 27ff: fe71: 6b7b added
Jul 25 02:51:22 zlm188 Keepalived_healthcheckers [3442]: Registering Kernel netlink reflector
Jul 25 02:51:22 zlm188 Keepalived_vrrp [3443]: Netlink reflector reports IP fe80: a00: 27ff: fe71: 6b7b added
Jul 25 02:51:22 zlm188 Keepalived_healthcheckers [3442]: Registering Kernel netlink command channel
Jul 25 02:51:22 zlm188 Keepalived_vrrp [3443]: Registering Kernel netlink reflector
Jul 25 02:51:22 zlm188 Keepalived_vrrp [3443]: Registering Kernel netlink command channel
Jul 25 02:51:22 zlm188 Keepalived_vrrp [3443]: Registering gratuitous ARP shared channel
Jul 25 02:51:22 zlm188 Keepalived_vrrp [3443]: Opening file '/usr/local/keepalived/etc/keepalived. conf '.
Jul 25 02:51:22 zlm188 Keepalived_healthcheckers [3442]: Opening file '/usr/local/keepalived/etc/keepalived. conf '.
Jul 25 02:51:22 zlm188 Keepalived_healthcheckers [3442]: Configuration is using: 11566 Bytes
Jul 25 02:51:22 zlm188 Keepalived_vrrp [3443]: Configuration is using: 62964 Bytes
Jul 25 02:51:22 zlm188 Keepalived_vrrp [3443]: Using LinkWatch kernel netlink reflector...
Jul 25 02:51:22 zlm188 Keepalived_vrrp [3443]: VRRP_Instance (my_178) Entering BACKUP STATE
Jul 25 02:51:22 zlm188 Keepalived_vrrp [3443]: VRRP sockpool: [ifindex (2), proto (112), unicast (0), fd ()]
Jul 25 02:51:22 zlm188 Keepalived_healthcheckers [3442]: Using LinkWatch kernel netlink reflector...
Jul 25 02:51:22 zlm188 Keepalived_healthcheckers [3442]: Activating healthchecker for service [192.168.17.188]: 3306
Jul 25 02:51:25 zlm188 Keepalived_healthcheckers [3442]: TCP connection to [192.168.17.188]: 3306 failed !!!
Jul 25 02:51:25 zlm188 Keepalived_healthcheckers [3442]: Removing service [192.168.17.188]: 3306 from VS [192.168.17.166]: 3306
Jul 25 02:51:25 zlm188 Keepalived_healthcheckers [3442]: Executing [/opt/mysql. sh] for service [192.168.17.188]: 3306 in VS [192.168.17.166]: 3306
Jul 25 02:51:25 zlm188 Keepalived_healthcheckers [3442]: Lost quorum 1-0 = 1> 0 for VS [192.168.17.166]: 3306
Jul 25 02:51:25 zlm188 Keepalived [3441]: Stopping Keepalived v1.2.13 (07/22, 2014)
Start keepalived on the slave end, and track the log file to find that the Ping is automatically stopped after it is run. This also indicates that the previous configuration is OK. Otherwise, check it, where the configuration is incorrect, especially the virtual_route_id must be consistent, while the route_id is not forcibly required to be consistent, and the Instance name should not be repeated.
1. Check the vip status
# Ip address show or ip a show
2. log on to the mysql database with vip (provided that the mysqld and keepalived processes are enabled)
# Mysql-h192.168.17.166-uaaron8219-pzlm
3. disable a network card at one end and test the destination of the vip and whether the vip can be accessed normally.
# Ifdown eth0
# Ip a show
# Mysql-h192.168.17.166-uaaron8219-pzlm
4. Restart one end (host simulation host failure) to test the whereabouts of the vip and whether the vip can be properly logged on through the vip
# Init 6
# Ip a show
# Mysql-h192.168.17.166-uaaron8219-pzlm
5. kill the keepalived process directly, test the whereabouts of the vip, and determine whether the vip can be properly logged on through the vip.
# Pkill keepalived
# Ip a show
# Mysql-h192.168.17.166-uaaron8219-pzlm
4. Configure Database Synchronization
After keepalived is installed, it only ensures the high availability of the mysql database. To ensure mutual master and slave, you also need to configure the MySQL master-slave replication mode so that the database can be consistent.
1. parameters required for synchronization at both ends
Make sure that the server-id and slave are inconsistent. Generally, the server-id format can be set to 2-3 digits at the end of the ip address + port number.
For example, in my environment, the master ip address is 192.168.17.177 and the port is 3306.
The server-id can be set to 1773306. Accordingly, the server-id is set to 1883306.
The following parameters are all configured in the/etc/my. cnf file.
Servers-id = 1773306
Log-bin = percona-bin -- enable binlog
Set-variable = binlog-ignore-db = mysql -- do not record the Update log of the database mysql, so that the permission settings on the Master node are not synchronized to the Slave.
2. Add duplicate users to both ends
Mysql> grant repliecation slave on *. * to 'rep '@ '2017. 192. %' identified by 'rep ';
If you want to have the permission to execute the "load table from master" or "load data from master" Statement on Slave, you must grant the Global FILE and Select permissions:
Mysql> grant file, Select, replication slave on *. * TO 'rep '@' % 'identified BY 'rep ';
3. Set Synchronization
If the database is new, binlog of the master is reset on both sides.
Mysql> reset master;
(Otherwise, you need to export the master database using mysqldump (or directly package and compress it) and then copy it to the slave Database Host. The general steps are as follows:
① Mysql> flush tables with read lock;
② Mysql> mysqldump-uroot-p -- all-databases-l-F> full_db. SQL
Scp full_db. SQL root@192.168.17.188:/data/mysql/percona_3306/data
Or
② Cd/data/mysql/percona_3306
Tar zcvf data.tar.gz./data
Scp data.tar.gz root@192.168.17.188
③ Mysql> unlock tables;
Import the database from the master database
Mysql> mysql-uroot-p </data/mysql/percona_3306/data/full_db. SQL
Or
Mysql> source/data/mysql/percona_3306/data/full_db. SQL
)
4. Log status query of the master database
Mysql> show master status \ G
5. The slave database performs Synchronization Based on the binlog location and position of the master database.
Mysql> change master to master_host = '192. 168.17.177 ', master_user = 'rep', master_password = 'rep ',
Master_log_file = 'percona-bin.000001 ', master_log_pos = '20140901 ';
6. Start slave
Mysql> start slave;
Error reported after startup
Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UIDs must be different for replication to work. Error_code: 1593
Because the slave database is created directly by copying an image from a VM, the UUID is duplicated and the UUID is stored in
In the/data/mysql/percona_3306/data/auto. cnf file
You can delete the file directly, or edit the file, and modify the UUID in it to be different from that in the master database. Normally, it should be in the following status:
(Testing) root @ localhost [(none)]> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.17.177
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: percona-bin.000011
Read_Master_Log_Pos: 540
Relay_Log_File: node78-relay-bin.000018
Relay_Log_Pos: 285
Relay_Master_Log_File: percona-bin.000011
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 540
Relay_Log_Space: 459
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 773306
Master_UUID: 917ecbfc-10dc-11e4-b624-080027267b03
Master_Info_File:/data/mysql/percona_3306/data/master.info
SQL _Delay: 0
SQL _Remaining_Delay: NULL
Slave_ SQL _Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_ SQL _Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
7. Test
Insert a row of data into the tb_zlm table of the zlm Test Database of the master database:
(Testing) root @ localhost [(none)]> select * from zlm. tb_zlm;
+ ------ + ----------- +
| Id | name |
+ ------ + ----------- +
| 1 | python8219 |
| 2 | zlm |
| 3 | abc |
+ ------ + ----------- +
3 rows in set (0.00 sec)
(Testing) root @ localhost [(none)]> insert into zlm. tb_zlm values (4, 'def ');
Query OK, 1 row affected (0.03 sec)
Query the zlm. tb_zlm table from the database:
(Testing) root @ localhost [(none)]> select * from zlm. tb_zlm;
+ ------ + ----------- +
| Id | name |
+ ------ + ----------- +
| 1 | python8219 |
| 2 | zlm |
| 3 | abc |
| 4 | def |
+ ------ + ----------- +
4 rows in set (0.00 sec)
Perform the same configuration on the slave database to achieve mutual master and slave. You only need to change the maseter from the pos position of the binlog of the corresponding master.
Conclusion: as long as the mysqld and keepalived processes are enabled on one machine in the configured VRRP group, it is normal for any database connection access via vip, so no matter which node is down, it does not affect the availability of the mysql database. It is the simplest mysql high-availability architecture. Since then, keepalived has been used to implement a master-slave dual-machine Hot Standby architecture. If it is more complicated to install lvpsadm to implement virtual server configuration, it is a classic keepalived + lvs architecture.
I am a beginner. I am currently studying the mysql high availability solution. I have two questions. Come and help me solve them.
1. The connection fails because of account settings, that is, Account link permissions.
2. This is not the case for mutual hot backup. You should use the mysql cluster installation method.
Because the latest mysql supports clusters.
How can we achieve high availability of mysql cross-Data Center replication?
One master database and one slave database of China Telecom are connected to one slave database using semi-synchronous replication of mysql5.5, and amoaba implements read/write splitting. However, there are many problems, cross-network connections are unstable, and many update operations often report a 2013 error. You can only create high-speed optical fiber cables in different machine rooms
