Introduction: Principle and thought the Gtid master-slave replication mode is chosen here to ensure the correctness, health and compatibility of master-slave replication. Here are two servers, a, b each has MySQL instance 3310, two instances between the master-slave replication mode using Gtid master-slave replication mode, the server A, A/b on the configuration of keepalived load balancer, through the VIP connection to the database, the purpose is that once there is a database outage, Keepalived will immediately build a VIP to perform another healthy database instance, to achieve fast switching, to avoid single point of failure, so as to ensure the normal operation of the business. This is only a two-master +keepalived, for the requirements of the high-read and write requirements of the environment, the recommended architecture is that the dual-master more from (can be achieved through the cluster management software MMM high-availability architecture) Read and write separation, of course, the cost is relatively high. Read/write Separation implementation method: 1, program implementation: Terminal reserved interface, when for already in the running environment, re-modify the program, seemingly not realistic 2, Mysql_proxy: An official way of implementation, but the official recommended not to use in the production environment, Not recommended 3, Amoeba software separation (recommended use)
Frame Composition:
First, environmental requirements:
| Server A: |
192.168.1.31 |
System environment: CetnOS6.5, Mysql-5.6.19 (Master-slave), keepalived (master) |
| Server B: |
192.168.1.32 |
System environment: CetnOS6.5, Mysql-5.6.19 (from Main), keepalived (back) |
Second, install MySQL and master-slave configuration(MySQL installation steps slightly) Here Server a configuration instance is 3310, Server B also has 3310 (the advantage of the same port number for the dual-master is that when the failover is over) the configuration file is modified after installing MySQL
1. Modify the MySQL configuration file on Server a my.cnfAdd parameters to the My.cnf file (be careful not to define the parameters repeatedly) Server-id = 201//The ID number here and the ID from the library or the main library must be guaranteed not unique LOG-BIN=MYSQ L-bin//Can be custom defined here as Log-bin=/data/log-bin/log-bin-3310binlog_format=row//master-slave replication mode log- Slave-updates=true//slave Update is logged Gtid-mode=on//enable Gtid type, otherwise the normal copy schema E Nforce-gtid-consistency=true//Mandatory Gtid consistency
Master-info-repository=table//Master Service Information logger = table/File
relay-log-info-repository=table//Relay log Information Record library Sync-master-info=1//Synchronize main library information slave-parallel-workers=4 The number of SQL threads from the server, to copy the same number of libraries BINLOG-CHECKSUM=CRC32//checksum, can be customized master-verify-checksum=1 Main service Check slave-sql-verify-checksum=1//from service check Binlog-rows-query-log_events=1//binary log verbose logging event report-port=3 310//Provide replication report port, current instance port number report-host=192.168.1.31//Provide replication report host, native IP address
2. Modify the MySQL configuration file on server B my.cnfAdd parameters to the My.cnf file (be careful not to define the parameters repeatedly) Server-id = 202//The ID number here and the ID from the library or the main library must be guaranteed not unique log-bin=m Ysql-bin//Can be customized here defined as Log-bin=/data/log-bin/log-bin-3310binlog_format=row//master-slave Copy mode L Og-slave-updates=true//slave Update is logged Gtid-mode=on//enable Gtid type, otherwise normal replication Schema Enforce-gtid-consistency=true//mandatory Gtid consistency
Master-info-repository=table//Master Service Information logger = table/File
relay-log-info-repository=table//Relay log Information Record library Sync-master-info=1//Synchronize main library information slave-parallel-workers=4 The number of SQL threads from the server, to copy the same number of libraries BINLOG-CHECKSUM=CRC32//checksum, can be customized master-verify-checksum=1 Main service Check slave-sql-verify-checksum=1//from service check Binlog-rows-query-log_events=1//binary log verbose logging event report-port=3 310//Provide replication report port, current instance port number report-host=192.168.1.32//Provide replication report host, native IP address
Replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=performance_schema.%
(can be added in the configuration file, the role of masking the library to be copied)
3. AuthorizationA server MySQL instance 3310:grant replication slave,replication Client on * * to [e-mail protected] ' 192.168.1.32 ' identified by ' 123 456 '; b server MySQL instance 3310:grant replication slave,replication Client on * * to [e-mail protected] ' 192.168.1.32 ' identified by ' 123 456 '; ================================================================================ a server MySQL instance 3310:change master to Master_host= ' 192.168.1.32 ', master_port=3310,master_user= ' slave ', master_password= ' 123456 ', master_auto_position= 1; Note: If the parameter master_port=3311 is not configured here, the default will be to find 3306 B server MySQL instance 3310:change master to master_host= ' 192.168.1.31 ', master_port=33 10,master_user= ' slave ', master_password= ' 123456 ', master_auto_position=1;======================================= =========================================== A, b server on the master-slave replication: start slave;
4. View statusA server: show slave status\g;show processlist; b server: show slave status\g; Show processlist; #show processlist;
5, to here A, B server-based Gtid master-slave replication mode has been configured, under testOn a server 3310 instance: Drop databse test;b on server 3310 View status: Show databases; on server 3310 instance: Create Database wll_test; A server on 3310 View status: Show databases;
Third, install the configuration keepalived load Balancer
1, official website download www.keepalived.orgInstall keepalived #tar zxvf keepalived-1.2.20.tar.gz #cd keepalived-1.2.20.tar.gz on a server and B server #./configure--sysconf=/etc--with-kernel-dir=/usr/local/src/kernels/2.6.32-573.el6.x86_64 #make &&make in Stall #ln-S/usr/local/sbin/keepalived/sbin/#ln-S/usr/local/keepalived-1.2.20/usr/local/keepalived #chkconfig--level keepalived on
2, Configuration keepalived#配置A服务器 #配置B服务器
3. #/etc/keepalived/change_mysql.sh script content for strong kill keepalived process
4, #配置好后开启两服务器 keepalived#/etc/init.d/keepalived Start #ps aux | grep keepalived
5. View status# IP A keepalived work mechanism is: every 2 seconds to check the status of Real_server monitoring, once the database down, the VIP will be transferred to another stage, so that the rapid failover of the fault, the user in the VIP connection through the basic can remain connected state.
Iv. Test SwitchingCheck that both MySQL instances and keepalived on Server A and Server B are turned on
1, at this time VIP on Server BOn the B server, on the MySQL instance 3310 stop #/etc/init.d/mysqld_3310 Stop, you can see the VIP switch to Server A at once 192.168.1.31 This platform
2. Client Connection DatabaseBasic will not have an impact on subsequent: Welcome to judge!
Enterprise-Class-mysql dual-master interoperable high-availability load-balancing architecture (based on Gtid master-slave replication mode)
