MySQL Master master sync +keepalived

mysqlm-m Sync

A Basic Configuration

Master Master IP:192.168.100.123

from master IP:192.168.100.124

Do the same on both servers first

premise: Close iptables or add a policy otherwise will be error;

Vi/etc/sysconfig/iptables

-A input-m state–state new-m tcp-p tcp–dport 3306-j ACCEPT

Do the local source

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/FD/wKioL1Ty1mDTbVt6AAByymgB_p4519.jpg "style=" float: none; "title=" 1.png "alt=" Wkiol1ty1mdtbvt6aabyymgb_p4519.jpg "/>

install mysql- related packages and turn on

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/5A/01/wKiom1Ty1VHSU0_EAAGA101YIJk140.jpg "style=" float: none; "title=" 2.png "alt=" Wkiom1ty1vhsu0_eaaga101yijk140.jpg "/>

Two Host service Configuration

2.1 in the primary server configuration

/ETC/MY.CNF Add below

[Mysqld]

Log-bin=mysql-bin

Server-id=1

View the bin log file and the POS value using the following command ;

Mysql> Show master status;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

|      mysql-bin.000004 |              106 |                  | |

+------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

remark: ID consistent error;

Create a synchronization user and give the appropriate permissions

Grant Replication Slave on * * to ' replication ' @ '% ' identified by ' replication ';

through show grantsfor ' replication ' @ '% '; You can view the created users and permissions can be used for troubleshooting;

2.2 in Slave server configuration

Also add

[Mysqld]

Log-bin=mysql-bin

server-id=2

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/FD/wKioL1Ty1mHx-jt-AAAjGBX8mYY690.jpg "style=" float: none; "title=" 3.png "alt=" Wkiol1ty1mhx-jt-aaajgbx8myy690.jpg "/>

Mysql>change Master tomaster_host= ' 192.168.100.123 ', master_user= ' replication ', master_password= ' replication ', Master_log_file= ' mysql-bin.000005 ', master_log_pos=106;

then turn on slave.

Sql>start slave;

Three Service Testing

Whether the query can connect to the primary server

Can be used to debug a database on the Master Master by executing the command on the slave.

Mysql-u root-h 192.168.100.123-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 19

Server Version:5.1.66-log sourcedistribution

Copyright (c), and/or Itsaffiliates, Oracle. All rights reserved.

Oracle is a registered trademark of oraclecorporation and/or its

Affiliates. Other names trademarksof their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' toclear the current input statement.

Mysql>

proving that connectivity is normal;

Mysql> Show Slave Status\g

1. row***************************

Slave_io_state:waiting Formaster to send event

Master_host:192.168.100.123

Master_user:replication

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000004

read_master_log_pos:433

relay_log_file:mysqld-relay-bin.000004

relay_log_pos:251

relay_master_log_file:mysql-bin.000004

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:433

relay_log_space:552

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:

1 row in Set (0.01 sec)

Mysql>

The information displayed for bold red is Running The state is normal, or there may be a master-slave bin log is not synchronized or the main is never connected, and so on, can be checked by the above error command;

Synchronous Validation:

Create a library on the Master master and see if the table is synchronized from slave

validation omitted, if the above configuration is OK;

Master Master--Master Master mode synchronization

This mode as long as the master-slave server role in the exchange of master-slave configuration, can be achieved, authentication method ibid.

installing keepalived for dual-machine MySQL m-m

M1:192.168.100.222

m2:192.168.100.150

vip:192.168.100.200

A Prepare the software and install it.

mkdir/etc/keepalived/

./configure--prefix=/usr/local/keepalived

The following information is displayed as normal

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/5A/01/wKiom1Ty1VHxaBqVAAEj_A2dmYA276.jpg "style=" float: none; "title=" 4.png "alt=" Wkiom1ty1vhxabqvaaej_a2dmya276.jpg "/>

Make && make install

cp/usr/local/keepalived/etc/keepalived/keepalived.conf/etc/keepalived/

cp/usr/local/keepalived/etc/rc.d/init.d/keepalived/etc/rc.d/init.d/

cp/usr/local/keepalived/etc/sysconfig/keepalived/etc/sysconfig/

cp/usr/local/keepalived/sbin/usr/sbin/

Set boot up

Vi/etc/rc.local

Add to

#/etc/init.d/keepalived start

Vi/etc/keepalived/keepalived.conf

! Configuration File for Keepalived

Global_defs {

Notification_email {

[Email protected]

}

Notification_email_from [email protected]

Smtp_server 127.0.0.1

# Smtp_connect_timeout 30

router_id Lvs_devel

}

keepalived.conf File Contents

M1 Host

#VIP1

vrrp_instancevi_1 {

Statebackup

Interfaceeth1

Virtual_router_id51

priority100

Advert_int1

authentication{

Auth_typepass

auth_pass1111

}

virtual_ipaddress{

192.168.100.200

}

}

virtual_server192.168.100.200 3306 {

Delay_loop2

Lb_algowrr

Lb_kinddr

Persistence_timeout60

Protocoltcp

real_server192.168.100.222 3306 {

Weight3

notify_down/usr/local/mysql/bin/mysql.sh

tcp_check{

Connect_timeout10

Nb_get_retry3

Delay_before_retry3

connect_port3306

}

}

}

Service keepalived Stop

ifconfig eth0 up Activate the network adapter specified in the configuration file, or you cannot vip200 bind to Nic eth0

Two Login Test

install mysql client login virtual address test

Premise: Grant permissions to two libraries and shut down the firewall, respectively

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/5A/01/wKiom1Ty1VGjF006AADrdGnDOBw378.jpg "style=" float: none; "title=" 5.png "alt=" Wkiom1ty1vgjf006aadrdgndobw378.jpg "/>

Connection Successful

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/59/FD/wKioL1Ty1mGyn93IAAEWA8pkqKY509.jpg "style=" float: none; "title=" 6.png "alt=" Wkiol1ty1mgyn93iaaewa8pkqky509.jpg "/>

keepalived Switch Test

the Windows client always goes to Ping the VIP andthen shuts down the keepalived on the 192.168.100.222, under normal circumstances VIP you're going to switch to 192.168.100.150 .

turn on the keepalived on the 192.168.100.222, turn off the keepalived on the 192.168.100.150, See if you can automatically switch, under normal circumstances VIP will belong to 192.168.100.222

Mysql Switch Test

Turn off the MySQL service on 192.168.100.222 to see if the VIP will switch to 192.168.100.150

turn on MySQL and keepalived on the 192.168.100.222, and then turn off MySQL on the 192.168.100.150 , look Whether the VIP will switch to 192.168.100.222

turn off the MySQL service, the triggering mechanism also shuts down the keepalived, and transfers to another server after two timeout;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/59/FE/wKioL1Ty1mGRBcIeAACXzxJuYh8246.jpg "style=" float: none; "title=" 7.png "alt=" Wkiol1ty1mgrbcieaacxzxjuyh8246.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/5A/01/wKiom1Ty1VHggLR_AAE-Nv72cTk730.jpg "style=" float: none; "title=" 8.png "alt=" Wkiom1ty1vhgglr_aae-nv72ctk730.jpg "/>

can be used tail–f/var/log/message

View address 192.168.100.200 go to the 192.168.100.150 server's network card eth0;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/FE/wKioL1Ty1uLAos25AAN6qReOh9s580.jpg "title=" 9.png " alt= "Wkiol1ty1ulaos25aan6qreoh9s580.jpg"/>

Ok!

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://2367685.blog.51cto.com/2357685/1616296

MySQL Master master sync +keepalived