MySQL Master master sync +keepalived

1. Install the database, configure the master, and then keepalived
Database Installation Complete
Yum install mysql-server yum install Mysql-devel
========================================================
[[email protected] ~]# rpm-qa|grep MySQL
Mysql-devel-5.1.73-3.el6_5.x86_64
Mysql-libs-5.1.73-3.el6_5.x86_64
Mysql-5.1.73-3.el6_5.x86_64
Mysql-server-5.1.73-3.el6_5.x86_64



Mysqladmin-uroot Password yaoshao123
Primary master Synchronization
Http://www.cnblogs.com/xiaoit/p/3977843.html
Http://www.linuxidc.com/Linux/2013-05/83784.htm
(1) Sync account
Yao Yaoshaoyun
Grant Replication Slave on * * to ' yao ' @ '% ' identified by ' yaoshao123 ';
Create Database Zabbix;
(2) vi/etc/my.conf
Main
Port = 3306
Log_bin=/var/lib/mysql/mysql-binlog
Server-id=1
Binlog_do_db=zabbix
From
server-id=2
master-host=192.168.31.154
Master-user=yao
Master-password=yaoshao123
master-port=3306
Master-connect-retry=5
Replicate-do-db=zabbix
(3) Add the following to the master new

# # # #server-id=1 Originally the main ########
#[mysqld]
master-host=192.168.31.154
Master-user=yao
Master-password=yaoshao123
master-port=3306
Master-connect-retry=5
Replicate-do-db = Zabbix
Log-slave-updates
Sync_binlog=1
Auto_increment_offset=1
auto_increment_increment=2
# # # #server-id=2 was originally prepared ########
#[mysqld]

Log_bin=/var/lib/mysql/mysql-binlog
Binlog_do_db=zabbix
Log-slave-updates
Sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2

To start syncing, it is best to lock the table so that the synchronization location is located from the server. After the initial sync is complete, remember to unlock it.
Flush tables with read lock;
Unlock tables;
View the two mysql>show slave status separately to see
Slave_io_running:yes
Slave_sql_running:yes
Description Synchronization Succeeded


Here are two my.conf.
A 192.168.31.154
#---------------Byyao-------------
Port = 3306
Log_bin=/var/lib/mysql/mysql-binlog
Server-id=1
Binlog_do_db=zabbix
#------------ZhuZhu--------------
master-host=192.168.31.109
Master-user=yao
Master-password=yaoshao123
master-port=3306
Master-connect-retry=5
Replicate-do-db = Zabbix
Log-slave-updates
Sync_binlog=1
Auto_increment_offset=1
auto_increment_increment=2
#--------------Byyao--------------

B192.168.31.109
#--------------Byyao------------
server-id=2
master-host=192.168.31.154
Master-user=yao
Master-password=yaoshao123
master-port=3306
Master-connect-retry=5
Replicate-do-db=zabbix
#-------------ZhuZhu------------
Log_bin=/var/lib/mysql/mysql-binlog
Log-slave-updates
Sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
#--------------Byyao------------

Primary master Synchronization

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6B/8A/wKiom1Uv4bqwQx_VAATCJIqHDkg193.jpg "style=" float: none; "title=" status. png "alt=" wkiom1uv4bqwqx_vaatcjiqhdkg193.jpg "/>

To create a new table from the database Yaoshao, view the primary database

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6B/8A/wKiom1Uv4bujD2oFAAN5Hr8rzIE469.jpg "style=" float: none; "Title=" from the new table, the master receives the. png "alt=" wkiom1uv4bujd2ofaan5hr8rzie469.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6B/86/wKioL1Uv4xGwiGUKAAN_5_rR7cc111.jpg "style=" float: none; "title=" Primary master sync. png "alt=" wkiol1uv4xgwigukaan_5_rr7cc111.jpg "/>

----------------------------------------keepalived Configuration-----------------------------------

Vi/etc/keepalived/keepalived.conf

Main AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Vrrp_instance Vi_1 {
State backup #两台配置此处均是BACKUP
Interface eth0
VIRTUAL_ROUTER_ID 51
Priority #优先级, another set to 90
Advert_int 1
Nopreempt #不抢占, only set on high priority machines, low priority machines not set
Authentication {
Auth_type PASS
Auth_pass 1111
}
virtual_ipaddress {
192.168.31.122
}
}

Virtual_server 192.168.31.122 3306 {
Delay_loop 6
Lb_algo WRR
Lb_kind DR
Persistence_timeout #会话保持时间
Protocol TCP

Real_server 192.168.31.154 3306 {
Weight 3
notify_down/usr/shell/killkeepalived.sh #检测到mysql服务挂了就执行这个脚本 (script
Write it yourself, huh?
Tcp_check {
Connect_timeout #连接超时时间
Nb_get_retry 3 #重连次数
Delay_before_retry 3 #重连间隔时间
Connect_port 3306 #健康检查端口
}
}
}

Prepared BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

Vrrp_instance Vi_1 {
State backup
Interface eth0
VIRTUAL_ROUTER_ID 51
Priority 90
Advert_int 1
Authentication {
Auth_type PASS
Auth_pass 1111
}
virtual_ipaddress {
192.168.31.122
}
}

Virtual_server 192.168.31.122 3306 {
Delay_loop 6
Lb_algo WRR
Lb_kind DR
Persistence_timeout 50
Protocol TCP

Real_server 192.168.31.109 3306 {
Weight 3
notify_down/usr/shell/killkeepalived.sh
Tcp_check {
Connect_timeout 10
Nb_get_retry 3
Delay_before_retry 3
Connect_port 3306
}
}
}

------------------------Scripts-All two are the same-----shut down directly, then try restarting mysqld and keepalived in Corntab--------------

[Email protected] ~]# cat/usr/shell/killkeepalived.sh
#/bin/sh
/sbin/service keepalived Stop

-----------------or try restarting MySQL before you turn off keepalived--------

#!/bin/bash
#PATH =/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
A= ' ps-c mysqld--no-header|wc-l '
If [$A-eq 0];then
/etc/init.d/mysqld Start # #这个地方写你nginx命令的路径
Sleep 3
If [' Ps-c mysqld--no-header|wc-l '-eq 0];then
Killall keepalived
Fi
Fi

------------------------Test Effect--------------------
Both machines are open, mysql-h 192.168.32.122-uroot-pyaoshao123.

Mysql> show databases;
ERROR (HY000): Lost connection to MySQL server during query
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect ...
Connection id:   
Current Database: * * * NONE * * *

+--------------------+
| database           |
+--------------------+
| information_schema |
| b                  |
| test               |
| zabbix             |
+--------------------+
4 rows in Set (0.04 sec)

=========== Close B this one ================

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect ...
Connection id:143
Current database: * * * NONE * * *

+--------------------+
| Database |
+--------------------+
| Information_schema |
| A |
| Test |
| Zabbix |
+--------------------+

Slightly delayed

OK, it's done .....

This article is from the "Cloud Notepad" blog, please be sure to keep this source http://youngcloud.blog.51cto.com/2873694/1633695

MySQL Master master sync +keepalived