Configure MySQL's bidirectional master-slave and achieve high availability through keepalived

Configure MySQL's bidirectional master-slave and achieve high availability through keepalived

Description

This article is a reference to a good friend's document http://sunys.blog.51cto.com/8368410/1639820

Environment:

Version of MySQL: mysql-5.6.22

System: centos6.4

master:192.168.186.129

slave:192.168.186.128

vip:192.168.186.140 #虚拟ip, the IP of the Web server connection

Steps:

Configure MySQL's one-way master-Slave:

Note:mysql installation steps are omitted

Master: #开启二进制日志文件

Vi/usr/local/mysql/my.cnf

Under [Mysqld], add:

Server-id = 1

Log-bin =/opt/mysql/binlog/mysql-binlog #二进制日志文件路径随便设置, preferably placed in a single directory

Slave

Vi/usr/local/mysql/my.cnf

Under [Mysqld], add:

Server-id = 2

Master: #对slave授权

mysql-uroot-p123456
Grant Replication Slave on * * to 'slave' @ ' 192.168.186.128 ' identified by ' 123456 ';

Flush privileges;

Master: #备份数据

mysql-uroot-p123456

Flush tables with read lock; #锁表

Show master status; #查看binlog位置和pos值, this has to be recorded.

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-binlog.000044 |              412 |                  |                   | |

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

Open another terminal: Mysqldump-u root-p123456--all-databases >/tmp/mysqldump.sql

Back to the previous terminal: Unlock tables; #解表

Note: Lock table--View--backup--To note the table order

Scp/tmp/mysqldump.sql 192.168.186.128:/tmp/#把备份数据拷到slave

Slave: #导入数据

mysql-uroot-p123456 </tmp/mysqldump.sql

Slave: #开始同步

mysql-uroot-p123456

Change Master to master_host= '192.168.186.129', master_user= 'slave', master_password= '123456 ', master_log_file= 'mysql-binlog.000044', master_log_pos=412, master_port=3306;

Start slave;

show slave status\g; #查看是否成功, as follows: Two Yes succeeded

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.186.129

Master_user:slave

master_port:3306

Connect_retry:60

master_log_file:mysql-binlog.000044

read_master_log_pos:412

relay_log_file:scj-relay-bin.000002

relay_log_pos:286

relay_master_log_file:mysql-binlog.000044

Slave_io_running: Yes

Slave_sql_running: Yes

Note: master-Slave synchronization, can not be in the slave for any write operation, or the master and slave will be broken, all write operations are carried out in the Lord

If there is a problem with the slave (such as a sudden shutdown), the host data will continue to synchronize after normal

Configure MySQL's bidirectional master and Slave:

This article is from the "See" blog, please be sure to keep this source http://732233048.blog.51cto.com/9323668/1643376

Configure MySQL's bidirectional master-slave and achieve high availability through keepalived