mysql5.6 Replication Asynchronous Replication Setup

--mysql Copy and Build

Prepare the SQL2 server, using the method of replication SQL1

--## #sql2 resolve VM NIC Replication issues
Vi/etc/udev/rules.d/70-persistent-net.rules
1. Find the same line as Ifconfig-a's Mac
Change to "Name=eth0"

2, the above line name= ' eth0 ' delete

--SQL2 Sync MAC address modify IP address 192.168.33.191
Ifconfig
HWaddr 00:0c:29:d1:be:65

Vi/etc/sysconfig/network-scripts/ifcfg-eth0
Device= "Eth0"
bootproto= "Static"
Hwaddr= "00:0c:29:d1:be:65"
Nm_controlled= "Yes"
onboot= "Yes"
Type= "Ethernet"
Ipaddr= "192.168.33.191"
Gateway= "192.168.33.1"

--SQL2 Restart Effective
Reboot

--## #sql2 Remove source code compiled and installed MySQL
1. Close the MySQL service process
Su-mysql
Mysqladmin shutdown

2. Find MySQL installation directory
Su-root
Whereis MySQL
MySQL:/etc/mysql/usr/local/mysql

cd/etc/
RM-FR MySQL

cd/usr/local/
RM-FR MySQL

3. Find MySQL Related directory
Find/-name MySQL
/data/sql1/data/mysql

Cd/data
RM-FR SQL1

--SQL2 Modify machine name New Installation MySQL
See MySQL Standalone-single instance source compilation installation configuration. sql

--Database Online backup configuration
Installation and use of xtrabackup. sql

--## #配置mysql异步复制
MASTER:SQL1 192.168.33.190
slave:sql2192.168.33.191

--1, master Configuration

--2, make sure Master Log-bin is open
Mysql> Show variables like '%log_bin% ';
+---------------------------------+-----------------------------------+
| variable_name | Value |
+---------------------------------+-----------------------------------+
| Log_bin | On |

--3, master add server_id parameter more than IP
Su-mysql
Vi/etc/mysql/my.cnf
#replication
SERVER_ID = 10000

--4, master Create copy private account Usvr_replication Access source 192.168.33 network segment can connect master
CREATE USER [email protected] ' 192.168.33.% ' identified by ' 123456 ';

GRANT REPLICATION SLAVE On * * to [email protected] ' 192.168.33.% '; #--empowering replication Slave and can replicate all user databases

--5, restart Master
Mysqladmin shutdown
SH mysql_startup.sh

--## #slave配置
--1, test usvr_replication whether the user is logged in normally
Mysql-h192.168.33.190-p3306-uusvr_replication-p

--2, slave add server_id generally for IP convenience unique
Vi/etc/mysql/my.cnf
#replication
server_id = 10001

--3, make sure AUTO.CNF is not the same as master
More/data/sql2/data/auto.cnf

--4, ensure slave also open log-bin purpose: Primary and standby switchover and backup
Mysql> Show variables like '%log_bin% ';
+---------------------------------+-----------------------------------+
| variable_name | Value |
+---------------------------------+-----------------------------------+
| Log_bin | On |

--Data synchronization
By default, all the db under the user is replicated synchronously, and three methods specify the db to be copied
1, in the master on the/etc/my.inf through the parameters binlog-do-db, binlog-ignore-db set up the database to be synchronized.
2. When performing grant assignment permissions operation, limit the database
3, on the slave limit database use replicate-do-db=dbname #--limit sql_thread need to apply dbname

--5, restart Slave
Mysqladmin shutdown
SH mysql_startup.sh

--6, master online full backup slave on recovery
Installation and use of xtrabackup. sql

--7, positioning full backup time, Master_log_file and Master_log_pos on master
More/data/sql2/data Xtrabackup_binlog_pos_innodb
mysql-bin.000004346

--8, configure slave to master connection to execute carefully, this sentence will be executed multiple times will produce duplicate data!
Mysql> Change Master to
Master_host= ' 192.168.33.190 ',
master_port=3306,
Master_user= ' Usvr_replication ',
Master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000004 ',
master_log_pos=346;

Start sync on--9, slave
mysql> start slave;

--10, monitoring slave replication status if there are any errors, it is recommended
Show Slave Status\g

--Replication automatic connection after reboot

--## #配置半同步
"Mysql_ semi-synchronous replication configuration. sql"

--## #主备切换

Adjustment of each parameter

mysql5.6 Replication Asynchronous Replication Setup