Linux operations, architecture-mysql Master-slave replication

First, MySQL master-slave replication schematic diagram

MySQL master-slave replication principle: The master-slave replication principle is three threads completed, the main I/O thread, the I/O thread and the SQL thread

1, first the main library db01 need to open Binlog, authorize a replication client account, lock the table for a full data preparation

2, from the library db02, db03 import Master Library full data to achieve master-slave data consistency, from the library to perform change master to the host, port, user name, password, binlog file name, binlog location point in Master.info

3, from the library start slave, first by the i/from the library o Thread carry master.info information to ask the main library, verify the rep account, according to Binlog file and location point, the new Binlog file sent back, while carrying the sent to the file and location point, received from the library to the Master.info, received binlog log stored in Reply-bi n file, by the SQL from the library execution, first parse the Binlog file before the database, the execution of information into the repay.info. From the library will always ask whether the main library has new data, the main library has new data will be returned to from the library.

Second, MySQL master-slave replication deployment

1. Deployment environment

[Email protected] tools]# cat/etc/redhat-Release CentOS release6.9(Final) [[email protected] tools]# uname-R2.6. +-696. El6.x86_64[[email protected] tools]# ll/application/ Total4lrwxrwxrwx1Root root -  .-Ten- -  One: -MySQL-/application/mysql-5.6. the/drwxr-xr-x -MySQL MySQL4096  .-Ten- -  One: -mysql-5.6. the

2. Server Planning

Server name	External Network IP	Intranet IP	Host Name
MySQL database db01	172.19.5.51/24	172.16.1.51/24	Db01
MySQL database db02	172.19.5.52/24	172.16.1.52/24	Db02
MySQL database db03	172.19.5.53/24	172.16.1.53/24	Db03

3. Installation and Deployment

Useradd-s/sbin/nologin-M MYSQLCD/server/Toolstar XF MySQL-5.6. the-LINUX-GLIBC2.5-X86_64.tar.gzmkdir-p/application/MV/server/tools/mysql-5.6. the-*-x86_64/application/mysql-5.6. theLN-s/application/mysql-5.6. the//application/Mysqlchown-R mysql.mysql/application/mysql//application/mysql/scripts/mysql_install_db--basedir=/application/mysql-- Datadir=/application/mysql/data--user=MYSQL\CP/application/mysql/support-files/mysql.server/etc/init.d/Mysqldchmod+x/etc/init.d/mysqldsed-I.'s#/usr/local/mysql#/application/mysql#g'/application/mysql/bin/mysqld_safe/etc/init.d/MYSQLD\CP/application/mysql/support-files/my-default. cnf/etc/my.cnf/etc/init.d/mysqld startlsof-I.:3306Echo'export Path=/application/mysql/bin: $PATH'>>/etc/Profilesource/etc/Profilechkconfig--add mysqld chkconfig mysqld on/application/mysql/bin/mysqladmin-u Root Password'123456'

4, modify the DB01, DB02, db03 database configuration file/etc/my.conf

" log_bin|server_id " /etc/= mysql-bin  #主库开启log_bin1        #server_id一定不能相同 ~]# grep server_id/etc/2~]# grep server_id/etc/3

5, the main library DB01 operation

Mysql-uroot-p123456#登录数据库grant replication slave on*. * To'Rep'@'172.16.1.%'Identified by'123456'; #授权从库用户flush table with readLock; #锁表Show master status; #查看主库binlog和位置点+------------------+----------+--------------+------------------+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+| Mysql-bin.000001|326|                  |                   | |+------------------+----------+--------------+------------------+-------------------+1Rowinch Set(0.00Sec
Mysqldump-uroot-p123456-a-B--master-data=2|gzip >/tmp/mysql_$ (date +%f). sql.gz#全备主库数据
[Email protected] ~]# scp-rp/tmp/mysql_2017-10-24.sql.gz 172.16.1.52:/tmp/#分发主库全备数据到从库
[Email protected] ~]# scp-rp/tmp/mysql_2017-10-24.sql.gz 172.16.1.53:/tmp/
mysql> unlock table;  #解锁表

Linux operations, architecture-mysql Master-slave replication