MySQL high-availability--keepalivd+ mutual primary from

Source: Internet
Author: User
Tags install openssl mysql update openssl reflector system log percona percona server pkill

Goal: Configure a keepalived dual-machine hot standby architecture and configure 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


First, Software Installation


can go to the official website http://www.keepalived.org/software/Download the latest version of Keepalived, the latest is the 1.2.13 version, as follows:
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 you will be prompted for a missing GCC and OpenSSL package during the compilation process, and install it with yum install.
RHEL6.4 can also be configured with the CentOS yum, the specific method is not
Yum Install GCC
Yum Install OpenSSL Openssl-devel
You will also be prompted to install the XXX dependency package, together with the benefits of Yum is easy to install, let the system automatically determine which packages need to automatically download and install, after the completion of the compilation, software installation is over


Second, configuration software parameters (VRRP)


After the software is loaded, the path to the default configuration file is:
/usr/local/keepalived/etc/keepalived/keepalived.conf


Open the configuration file on the main side, empty the original content, and add the following:


! Configuration File for Keepalived


Global_defs {--Global configuration
Notification_email {
[Email protected]--email to receive notifications
}
router_id aaron8219--can use letters, can also make numbers, can be consistent, can also be inconsistent, just a logo
}


vrrp_instance my_177{
State BACKUP--backup slave-side mode
Interface eth0
virtual_router_id 88--The default is 51, the value range in the 1~255 integer, the master and slave must be consistent to indicate that the same group
Priority
Advert_int 1-check interval, default 1s
Nopreempt--Set non-preemption mode,
Authentication {
Auth_type PASS
Auth_pass 1234
}
virtual_ipaddress {--Specify VIP,
192.168.17.166
}
}


# #关于vip的说明: VIP increases as the state changes and is added when the state is master and is deleted when the state is backupis mainly determined by the priority level., and the value set by the state does not have much to do with (the same condition), as to whether the VIP is in the main or from the end, but also with Nopreempt, where the VIP can set multiple IP addresses


# #关于nopreempt的说明:can only be set on a node with state backup, and the priority of this node must be higher than the other


Virtual_server 192.168.17.166 3306 {--Virtual server IP and port
Delay_loop 2
Lb_algo WRR--poll with weights
Lb_kind DR
Persistence_timeout 60
Protocol TCP


Real_server 192.168.17.177 3306 {--Real server IP and port
Weight 3--weight of 3
notify_down/opt/mysql/mysql.sh--Specify the path to the suicide script
Tcp_check {
Connect_timeout 10
Nb_get_retry 3
Delay_before_retry 3
Connect_port 3306
}
}
}


From the end of the configuration file is similar, as long as the IP and the instance name to their own, and set the slave to the priority of 90, and the main side is 100
One thing to keep in mind is thatboth the master and slave state are configured as backupBecauseusing the Nopreempt, i.e. non-preemption mode


For example, when the main side starts the MySQL instance and the keepalived, if the MySQL instance and the keepalived are also started at this point, then the VIP will not jump to the slave, even if its priority is 100, greater than the 90 of the main side.
And if not set nopreempt, then this time, there are 2 kinds of situations:


1.state is the same, that is, both master or backup
High priority, will occupy VIP, and role-independent


2.state different, i.e. master->backup or backup->master
High priority, will occupy VIP, and role-independent


The premise is different, the result is the same, namelypriority is the dominant, who has high priority, the VIP floats to who there


Create a suicide script to determine if the MySQL process is started
touch/opt/mysql/mysql.sh
Add the following content:
#!/bin.sh
Pkill keepalived--means kill keepalived process


Third, run the test


/usr/local/keepalived/sbin/keepalived-f/usr/local/keepalived/etc/keepalived/keepalived.conf-d


-F Specify keepalived parameter file
-D means detailed records are displayed in the operating system log


Determine if the keepalived process starts normally, just check the log in the/var/log/messages.
Tail-30f/var/log/message
Note that if you start keepalived without starting MySQL, the script specified in the previous Notify_down parameter will be executed, indicating that the MySQL process is not found and the keeplied own process is killed.


Jul 02:51:22 zlm188 keepalived[3440]: Starting keepalived v1.2.13 (07/22,2014)
Jul 02:51:22 zlm188 keepalived[3441]: Starting healthcheck child process, pid=3442
Jul 02:51:22 zlm188 keepalived[3441]: Starting VRRP child process, pid=3443
Jul 02:51:22 zlm188 keepalived_vrrp[3443]: NetLink Reflector reports IP 192.168.17.188 added
Jul 02:51:22 zlm188 keepalived_healthcheckers[3442]: NetLink Reflector reports IP 192.168.17.188 added
Jul 02:51:22 zlm188 keepalived_healthcheckers[3442]: NetLink Reflector reports IP fe80::a00:27ff:fe71:6b7b added
Jul 02:51:22 zlm188 keepalived_healthcheckers[3442]: registering Kernel netlink Reflector
Jul 02:51:22 zlm188 keepalived_vrrp[3443]: NetLink Reflector reports IP fe80::a00:27ff:fe71:6b7b added
Jul 02:51:22 zlm188 keepalived_healthcheckers[3442]: registering Kernel netlink command channel
Jul 02:51:22 zlm188 keepalived_vrrp[3443]: registering Kernel netlink Reflector
Jul 02:51:22 zlm188 keepalived_vrrp[3443]: registering Kernel netlink command channel
Jul 02:51:22 zlm188 keepalived_vrrp[3443]: Registering gratuitous ARP shared channel
Jul 02:51:22 zlm188 keepalived_vrrp[3443]: Opening file '/usr/local/keepalived/etc/keepalived/keepalived.conf '.
Jul 02:51:22 zlm188 keepalived_healthcheckers[3442]: Opening file '/usr/local/keepalived/etc/keepalived/ Keepalived.conf '.
Jul 02:51:22 zlm188 keepalived_healthcheckers[3442]: Configuration is using:11566 Bytes
Jul 02:51:22 zlm188 keepalived_vrrp[3443]: Configuration is using:62964 Bytes
Jul 02:51:22 zlm188 keepalived_vrrp[3443]: Using linkwatch kernel netlink reflector ...
Jul 02:51:22 zlm188 keepalived_vrrp[3443]: vrrp_instance (my_178) Entering BACKUP State
Jul 02:51:22 zlm188 keepalived_vrrp[3443]: VRRP sockpool: [IfIndex (2), Proto (), unicast (0), FD (10,11)]
Jul 02:51:22 zlm188 keepalived_healthcheckers[3442]: Using linkwatch kernel netlink reflector ...
Jul 02:51:22 zlm188 keepalived_healthcheckers[3442]: activating healthchecker for service [192.168.17.188]:3306
Jul 02:51:25 zlm188 keepalived_healthcheckers[3442]: TCP connection to [192.168.17.188]:3306 failed!!!
Jul 02:51:25 zlm188 keepalived_healthcheckers[3442]: removing service [192.168.17.188]:3306 from VS [192.168.17.166] : 3306
Jul 02:51:25 zlm188 keepalived_healthcheckers[3442]:executing [/opt/mysql/mysql.sh] for service[192.168.17.188]:3306 in VS [192.168.17.166]:3306
Jul 02:51:25 zlm188 keepalived_healthcheckers[3442]: Lost quorum 1-0=1 > 0 for VS [192.168.17.166]:3306
Jul 02:51:25 zlm188 keepalived[3441]:stopping keepalived v1.2.13(07/22,2014)


Boot keepalived from the end, trace log files can be found, ran after the automatic stopping, which also shows that the previous configuration is OK, otherwise it is necessary to check, where the configuration is wrong, especially to notevirtual_route_id must be consistent, while ROUTE_ID does not require consistency, and the instance name is not repeated


1. Inspection of VIP Status
#ip address Show or IP a show


2. Log in to the MySQL database with a VIP (provided that the mysqld and keepalived processes are turned on)
#mysql-H192.168.17.166-UAARON8219-PZLM


3. Close one end of the network card, test the whereabouts of the VIP, and whether the VIP can be properly logged in
#Ifdown eth0
#ip a show
#mysql-H192.168.17.166-UAARON8219-PZLM


4. Restart one end (host simulation host failure), test the whereabouts of the VIP, and whether the VIP can be properly logged in
#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 whether the VIP can be logged in normally
#Pkill keepalived
#ip a show
#mysql-H192.168.17.166-UAARON8219-PZLM


Iv. Configuring Database Synchronization


After installing the keepalived, only to ensure the high availability of MySQL database, but to truly mutual master from, also need to configure the MySQL master-slave replication mode, so that the database can achieve a consistent state


1. Configure the required parameters on both sides of the synchronization
Ensure that the Server-id is inconsistent with the slave, usually Server-id format can be set to IP end 2-3 bit + port number
Like my environment, Master's IP is 192.168.17.177, the port is 3306.
Then Server-id can be set to 1773306, accordingly, slave is set to 1883306
The following parameters are configured in the/etc/my.cnf file
server-id=1773306
Log-bin=percona-bin--Enable Binlog
Set-variable=binlog-ignore-db=mysql--Does not record the database MySQL update log, avoids the master's permission setting and so on to be synced to the slave


2. Add replication users at both ends
Mysql>Grant Repliecation Slave on * * to ' rep ' @ ' 192.168.17.% ' identified by ' rep ';


Global FILE and select permissions must be granted if you want to have permissions on the slave to execute the "load TABLE from master" or "Load DATA from master" statements:
Mysql> GRANTFile,select, REPLICATION SLAVE on * * to ' rep ' @ '% ' identified by ' rep ';


3. Set up sync
If it's a new library, reset the master's Binlog directly on both sides
Mysql>Reset Master;


(Otherwise, you need to export the Master library with mysqldump (or directly package compression), and then copy it to the slave host, the following steps:

①mysql> flush tables with read lock;



②mysql> mysqldump-uroot-p--all-databases-l-F >full_db.sql
SCP Full_db.sql [Email protected]:/data/mysql/percona_3306/data
Or
②cd/data/mysql/percona_3306
Tar zcvf data.tar.gz./data
SCP data.tar.gz [email protected]


③mysql> unlock tables;
Import a database from a library to the main library
Mysql> mysql-uroot-p </data/mysql/percona_3306/data/full_db.sql
Or
Mysql> Source/data/mysql/percona_3306/data/full_db.sql
)


4. Main Library query log status
Mysql> Show Master Status\g


5. Perform synchronization from the library based on the Binlog location and position of the main library

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= ' 120 ';



6. Start slave
mysql> start slave;


Error after startup
Slave I/o: Fatal error:the Slave I/O thread stops because master and Slave have equal MySQL server UUIDs; These uuids must is different for replication to work. error_code:1593


Since the library is created directly from the virtual machine copy image, the UUID is duplicated and the UUID is stored in
In the/data/mysql/percona_3306/data/auto.cnf file

This file can be deleted directly, or edit the file, modify the inside of the UUID and the main library can be different, normal, should be the following state:


(testing) [Email protected] [(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 have 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. Testing
Insert a row of data into the TB_ZLM table of the main library test database ZLM:


(testing) [Email protected] [(None)]> select * from ZLM.TB_ZLM;
+------+-----------+
| ID | name |
+------+-----------+
| 1 | aaron8219 |
| 2 | ZLM |
| 3 | ABC |
+------+-----------+
3 Rows in Set (0.00 sec)


(testing) [Email protected] [(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 library:


(testing) [Email protected] [(None)]> select * from ZLM.TB_ZLM;
+------+-----------+
| ID | name |
+------+-----------+
| 1 | aaron8219 |
| 2 | ZLM |
| 3 | ABC |
|4 | def|
+------+-----------+
4 rows in Set (0.00 sec)


The same configuration from the library, you can complete the main from, as long as the corresponding master from the Binlog POS location start change maseter can be


Conclusion: As long as there is a machine in the configured VRRP group to open the mysqld and keepalived process, any database connection through the VIP access, is normal, so no matter which node down, will not affect the availability of MySQL database, is one of the simplest MySQL high-availability architectures. Since then, through the keepalived to achieve mutual master from the dual-machine hot standby architecture is complete, if a little more complicated, install Lvpsadm to implement the virtual server configuration, it is a classic Keepalived+lvs architecture



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.