MySQL Master-slave replication

Description: Build the following MySQL master-slave replication environment for the production environment database hot standby

Build Environment Description:
System version:
Linux Test2 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 03:15:09 UTC x86_64 x86_64 x86_64 gnu/linux
Download Link: Http://mirror.nsc.liu.se/centos-store/6.5/isos/x86_64/CentOS-6.5-x86_64-bin-DVD1.iso
MySQL database version:
Mariadb-10.0.33-linux-x86_64.tar.gz
Download Link: http://mirrors.neusoft.edu.cn/mariadb//mariadb-10.0.33/bintar-linux-x86_64/mariadb-10.0.33-linux-x86_64.tar.gz

MARIADB Installation Steps

#新建用户
Useradd-s/sbin/nologin-m MySQL
Mkdir/data
Unzip #安装包放在/data Directory
TAR-ZXVF mariadb-10.0.33-linux-x86_64.tar.gz
#改一下名字
MV Mariadb-10.0.33-linux-x86_64 mariadb-10.0.33
#进入到mariadb -10.0.33 Catalog
CD mariadb-10.0.33
#执行安装脚本
./scripts/mysql_install_db--basedir=/data/mariadb-10.0.33/--datadir=/data/mariadb-10.0.33/data/--user=mysql
#拷贝配置文件
CP SUPPORT-FILES/MY-INNODB-HEAVY-4G.CNF/ETC/MY.CNF
#打开配置文件
Vim/etc/my.cnf
#添加以下两行
basedir=/data/mariadb-10.0.33
Datadir=/data/mariadb-10.0.33/data
#更新授权
Chown-r mysql:mysql/data/mariadb-10.0.33/
#拷贝MySQLD到初始化目录 Easy to use service startup
CP Support-files/mysql.server/etc/rc.d/init.d/mysqld
#拷贝mysql mysqldump to the/usr/bin directory
CP Bin/mysql/usr/bin
CP Bin/mysqldump/usr/bin
#mysql的root用户授权:

Mysql> Grant all privileges the to ' root ' @ '% ' of ' identified by ' tongda666 ';

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

Need to support MySQL master-slave replication requires the following files to be added in my.cnf as per the actual requirements

Configuration (Main Library)

Configuration (from library)

Main Library (MY.CNF)
Log-bin=master-bin? #主服务器二进制日志文件前缀名??
Log-bin-index=master-bin.index?? #索引文件??
innodb_file_per_table=?1????? #开启innodb的一表一个文件的设置??
Server-id?? =? IP address?????????? #必须是唯一的??
Datadir?=/mydata/data???????? #数据目录路径??
Binlog_format=mixed? #从复制的格式
Expire_logs_days=7? #二进制日志自动删除
slave_skip_errors=1062 #如: 1062 error refers to some primary key duplication
binlog-do-db= #指定binlog日志记录那些库的二进制日志
binlog-ignore-db= #不需要复制的库

From library (MY.CNF)
Relay-log-index?=?relay-log.index?? #中继日志索引
Innodb_file_per_table?=?1?? #开启innodb的一表一个文件的设置??
Server-id??????? =? IP address???? #id不要和主服务器的一样??
Datadir?=?/mydata/data? #mysql的数据目录?
Relay-log?=?relay-log??? #设置中继日志??
slave_skip_errors=1062 #如: 1062 error refers to some primary key duplication
Replicate-do-table=wishrp.sku_ma #库名. Table name
replicate_do_db= #需要复制库名
replicate_ignore_db= #不需要复制的数据库

Operations (Main Library)

CREATE USER ' slave ' @ ' from the Library IP address ' identified by ' slavepass '; #创建用户
GRANT REPLICATION SLAVE on . To ' slave ' @ ' from the library IP address '; #分配权限
Flush privileges; #刷新权限
SHOW MASTER STATUS; #查看master状态
Show Processlist \g #查看连接
Flush tables with read lock; #锁定所有的表

Unlock Tables #解锁所有表

Operation (from library)

Change Master to master_host= ' 182.92.172.80 ', master_user= ' main library created by user ', master_password= ' password ', master_log_file= ' Mysql-bin.000003 ', master_log_pos=73; #连接主库
Stop slave; #停止同步
Start slave; #开始同步
Reset slave; #删除master. info and Relay-log.info files;
Stop?slave; #停止同步
change?master?to?master_heartbeat_period?=?10; #设置发送心跳包间隔
set?global?slave_net_timeout?=?25;? #设置无数据网络超时检测间隔
Start?slave;? #开启复制
Show?status?like? ' Slave% '; #显示从库心跳状态
Show slave Status\g #查看从库连接状态
Show slave status\g view seconds_behind_master parameter The greater the delay the more #查看主从复制延时

Percona-toolkit Introduction

1) Pt-table-checksum is responsible for monitoring MySQL master-slave data consistency
2) Pt-table-sync is responsible for repairing the data when the master-slave data is inconsistent, allowing them to preserve the consistency of the data
3) Pt-heartbeat is responsible for monitoring MySQL master-slave synchronization delay

Dependency Packages
Yum Install Perl-io-socket-ssl perl-dbd-mysql perl-time-hires perl perl-dbi-y
Download Link: https://www.percona.com/downloads/percona-toolkit/2.2.7/RPM/percona-toolkit-2.2.7-1.noarch.rpm

First time use

Note Before using
Perform authorization in the main library (be sure to authorize the main library IP, authorized user name and password can be defined, but to ensure that this permission can be logged in both the main library and the slave library)
Mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION slave,create,delete,insert,update on . To ' root ' @ ' main Library IP address ' identified by ' 123456 ';
mysql> flush Privileges;
To perform authorization from the library
Mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE on . To ' root ' @ ' main Library IP address ' identified by ' 123456 ';
mysql> flush Privileges;
Error when there is no unique index or primary key

Detect synchronization-use example
Pt-table-checksum--nocheck-replication-filters--no-check-binlog-format--replicate=huanqiu.checksums-- Create-replicate-table?--Databases=huanqiu--tables=haha h=192.168.1.101,u=root,p=123456,p=3306

Common parameter Explanation:
(Don't forget to add the--create-replicate-table parameter to create a table named checksum when you first run it.)
--nocheck-replication-filters?: It is recommended to enable without checking the replication filter. You can use--databases later to specify the database you want to check.
--no-check-binlog-format: Do not check the copy of the Binlog mode, if the Binlog mode is row, will be an error.
--replicate-check-only?: Displays only information that is not synchronized.
--replicate=.: Writes the checksum information to the specified table, it is recommended to write directly to the database being inspected.
--databases=: Specifies the database that needs to be checked, and multiple are separated by commas.
--tables=.: Specifies the table to be inspected, separated by commas
H=?:master's address
U=?: User Name
p=: Password
P=?: Port

Execution results
Explain:
TS: Time to complete the check.
ERRORS: The number of errors and warnings that occurred while checking.
Diffs:0 is consistent, and 1 means inconsistent. When--no-replicate-check is specified, it will always be 0 when the specified--replicate-check-only displays different information.
Rows: The number of rows in the table.
CHUNKS: The number of blocks that are divided into tables.
Skipped: The number of blocks skipped due to errors or warnings or too large.
Time: The duration of the execution.
Table: The names of the tables being checked.

Fix synchronization-use example
Pt-table-sync--replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456-- Print

Parameter explanation:
--replicate=: Specifies the table obtained through Pt-table-checksum, which will almost always be used for all 2 tools.
--databases=: Specifies the database on which synchronization is performed.
--tables=: Specifies the table for which synchronization is performed, with multiple commas separated.
--sync-to-master: Specifies a DSN, which is the IP from which he will go through show processlist or show slave status to Auto Brahma.
H=: Server address, command has 2 IP, the first occurrence is the address of master, the 2nd time is the address of slave.
U=: Account number.
p=: Password.
--print?: print, but do not execute the command.
--execute?: Executes the command.

#自动检测数据是否一致脚本
#!/bin/bash
num=$ (/usr/bin/pt-table-checksum?--nocheck-replication-filters--no-check-binlog-format?--replicate= Huanqiu.checksums--databases=huanqiu? H=192.168.1.101,u=root,p=123456,p=3306|awk?-f ""? {print $} ' |sed?-n? ' 2p ')
If? [$NUM-eq?1];then
?? /usr/bin/pt-table-sync?--replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p= 123456--print
?? /usr/bin/pt-table-sync?--replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p= 123456--execute
Else
?? Echo? " Data is OK "
Fi

Overall operation Process

Begin

Configure MY.CNF from Library

     配置主库my.cnf

Create a master-slave replication user

Connecting the main library configuration from the library
Change MASTER to master_host= ' 182.92.172.80 ',
Master_user= ' main library created by user ',
master_password= ' Password ',
Master_log_file= ' mysql-bin.000003 ',
master_log_pos=73;

Create Percona-toolkit
User

Turn on replication
Start slave;

锁定主库所有表写操作

Flush tables with read lock;

View connection Status
Show Slave Status\g

View the main library Binlog status
SHOW MASTER STATUS;

Slave_io_running:yes
Slave_sql_running:yes

     解锁表

Unlock tables

MySQL Master-slave replication