Mysql-ab replication

AB "master-slave copy"

A is the main library, and B is the one from the library. Generally is AB copy, you can also AB copy.

Example:
What is the main function of implementation?

The CREATE DATABASE ABC is executed in the main library, and the ABC library is created automatically from the library
MySQL open log function, you create DATABASE ABC library in the library, this action will be recorded in the Bin-log log.
At this point I have a mechanism to download the Bin-log from the library, from the library also has the CREATE DATABASE ABC statement, and then I pass from the library local SQL thread, in I from the library and then execute the CREATE DATABASE ABC statement.
So the main library and the library did the same thing.

AB replication is implemented primarily through 2 threads:

From the library on the
I/o thread: Download Bin-log from the main library to the library and drop it into the relay-log from the library.
SQL Thread: Take the action in Relay-log and do it once from the library.

Attention:
(1), shut down the firewall strong, if the main library or from the library opened the firewall, will cause I/O thread can not connect.
(2), Production encountered SQL thread error, how to solve? Get some operations from the main library, do not execute from the library, by skipping those that do not. "The internet is not the only one."

Our data is based on the master library, in the production library to do AB replication, the main library must have some data, from the library is definitely a new library
"If the original from the library is not new, from the library has an ABC library, now in the main library to execute the CREATE TABLE ABC, must be an error."
We have two new virtual machines in the experimental environment, the main library has no data, we simulate the point data ourselves.
The experiment Manual is full and is written in the main library with data.

Main Library "Simulation data":
Create Database m;
Use m;
CREATE Table A (a int);
Insert into a values (1);

----------------------------------------
[[Email protected] ~]# service iptables stop//firewall off
[[email protected] ~]# Setenforce 0//temporarily off SELinux
Vi/etc/selinux/config//Permanent shutdown, to restart
Change Selinux=enforcing to Selinux=disabled

Steps

################### #1. ###################
Master
VIM/ETC/MY.CNF "configuration file, server_id value is small, Representative is the main"
[MySQL]
server_id = 1

Slave: "Configuration File"
Vim/etc/my.cnf
[MySQL]
server_id = 2

################### #2. ####################
Master
VIM/ETC/MY.CNF "Open Bin-log Log in main library"
[MySQL]
server_id = 1
Log-bin=binlog
Log-bin-index=binlog.index

################## #3. #####################
Slave
VIM/ETC/MY.CNF "Open Relay-log log from library"
[MySQL]
server_id = 2
Relay_log =/app/mysql/mysql-relay-bin
Relay_log_index=/app/mysql/mysql-relay-bin.index

################## #4. #####################
Master && Slave
Service MySQL Restart "Modified configuration file, restart Services configuration takes effect"

################## #5. #####################
Master
mysql> GRANT replication Slave on * * to ' liuxiaopeng ' @ ' percent ' identified by ' Liuxiaopeng '; "Create a user, give replication permission"
Query OK, 0 rows affected (0.05 sec)

mysql> flush Privileges; "Permissions take effect"
Query OK, 0 rows Affected (0.00 sec)

##################################################
Slave: "If you have data from the library, you have to kill the original data and keep the data from the library."

Service Mysqld Stop
Cd/app/mysql/data "Data Catalog"
RM-FR Delete Data only
Service MySQL Restart

Master: "There is some data on the main library, from the library is clean, to synchronize the data, two libraries at the same starting point to synchronize." So the old data of the main library is exported, then imported from the library, so that both sides of the data consistent "

Mysqldump-u Root-pmysql M >/tmp/full.sql
Scp/tmp/full.sql [email protected] from the library ip:/tmp/
scp/tmp/full.sql [Email protected]:/tmp/
Slave
Mysql-u Root-pmysql m </tmp/full.sql "Import old data from main library"

####################### #6. #######################
Master
Mysql> flush tables with read lock; "Add a read lock to the main library to prevent the operation from modifying the main library." We have synchronized the data on both sides of the master and slave library.
Mysql> Show master status; "View the Bin-log status, see where the log was written from, and synchronize from the current location from the library"
+---------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+---------------+----------+--------------+------------------+-------------------+
|      binlog.000001 |              402 |                  |                   | |
+---------------+----------+--------------+------------------+-------------------+

Mysql>unlock tables;

####################### #7. #############################
Slave
mysql> Change Master to master_host= ' main Library IP ', master_port=3306, master_user= ' Synchronous replication user ', master_password= ' sync copy password ', Master_log_file= ' binlog.000003 ', master_log_pos=295;

Change Master to master_host= ' 192.168.8.100 ', master_port=3306, master_user= ' Lipengfei ', master_password= ' Lipengfei ' , master_log_file= ' binlog.000001 ', master_log_pos=402;

mysql> start slave;

######################### #8. ##########################
Slave: "View ab Copy succeeded"
Mysql> Show Slave status \g
Slave_io_running:yes
Slave_sql_running:yes

Create a data test
Master
Create database slave;
use slave;
CREATE Table A (a int);
Insert into a values (1);
Insert into a values (2);

Slave
show databases;
use slave;
SELECT * from A;

#################################################################
Seconds_behind_master:0 "explains the synchronization efficiency is very good: the main library changes, how many seconds, from the library to update."

Slave: "The data from the library should be all synced from the main library, and no one should write data from the library"
Vim/etc/my.cnf
Read-only=1 "Read only, if you log in with the root user, you can write the data, other users cannot"

If the synchronization process from the library is stopped, will the operations of the main library also be synchronized from the library? Does it lose data?

Slave
Service MySQL Stop

Master
use slave;
Insert into a values (3);
Insert into a values (4);

Slave
Service MySQL Start
use slave;
SELECT * from A; We have no manual operation, the data is automatically synchronized, indicating that the main library-related configuration must be saved in a file "

Slave
More/app/mysql/data/master.info

This article is from the "Come Together" blog, please be sure to keep this source http://daxionglaiba.blog.51cto.com/11790757/1828095

Mysql-ab replication