MySQL Replication Configuration

First, MySQL replication introduction

MySQL master-slave is also called replication, AB replication. Simply speaking is a and b two machines from the back, write the data on a, the other B will follow the writing data, the data in real-time synchronization;
MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave;
The master-slave process is roughly 3 steps:
1) The change operation is recorded in Binlog.
2) from synchronizing the main binlog event (SQL statement) to the machine and recording it in Relaylog
3) Execute sequentially from the SQL statements inside the Relaylog
The Lord has a log dump thread that is used to pass binlong to and from the I/O thread;
There are two threads from the top, where I/O threads are used to synchronize the main binlog and generate Relaylog, and another SQL thread is used to drop the SQL statements inside the Relaylog.

Second, configure the MySQL service

Configure master/slave requires two machines, or build one MySQL , use 3307 ports or other. This is built using a different machine MySQL .
Configuration MySQL can refer to previous blogs:
http://blog.51cto.com/3069201/2073238
Main ip:192.168.242.128
From ip:192.168.242.129

Third, configure the main 1, modify the configuration file

[[email protected] ~]# vim /etc/my.cnf          //按下面修改[mysqld]#skip-grant# Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M# Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin# These are commonly set, remove the # and set as requiredii.basedir = /usr/local/mysqldatadir = /data/mysqlport = 3306server_id = 128               #可自定义，这里就使用了ip末尾log_bin=zlinux01             #指定log前缀socket = /tmp/mysql.sock

2. See if it takes effect

[[email protected] ~]#/etc/init.d/mysqld Restart//restart mysqlshutting down MySQL. success! Starting MySQL. success! [[[email protected] ~]# ls-lt/data/mysql///Restart after the generation of two files prefixed with zlinu01, must have the total usage of these two files 110756-RW-RW----. 1 mysql mysql 50331648 April 2 19:47 ib_logfile0-rw-rw----. 1 mysql mysql 12582912 April 2 19:47 ibdata1-rw-rw----. 1 mysql mysql 128079 April 2 19:47 zlinux.err-rw-rw----. 1 MySQL MySQL 5 April 2 19:47 zlinux.pid-rw-rw----. 1 MySQL MySQL 120 April 2 19:47 ZLINUX01.000001-RW-RW----. 1 MySQL mysql 18 April 2 19:47 zlinux01.indexdrwx------. 2 mysql mysql 4096 March 17:25 zrlogdrwx------. 2 MySQL MySQL 96 March 20:54 db1drwx------. 2 mysql mysql 4096 March 20:49 db2drwx------. 2 mysql mysql 4096 March 21:09 mysqldrwx------. 2 mysql mysql 4096 March 21:09 performance_schema-rw-rw----. 1 MySQL MySQL 1802 March 21:03 localhost.localdomain.err-rw-rw----. 1 MySQL mysql 56 March 21:03 auto.cnf-rw-rW----. 1 mysql mysql 50331648 March 21:03 ib_logfile1drwx------. 2 MySQL MySQL 6 March 21:03 test

3. Establish a new database

[[email protected] ~]# mysqldump -uroot -pzlinux123456 mysql > /tmp/mysql.sql                 //备份一个数据库Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot -pzlinux123456 -e "create database rpeltest"                 //建立新数据库Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot -pzlinux123456 rpeltest < /tmp/mysql.sql                        //将备份的数据库导入新数据库Warning: Using a password on the command line interface can be insecure.

4. Establish user

[[email protected] ~]# mysql-uroot-pzlinux123456warning:using a password on the command line interface can be inse Cure.  Welcome to the MySQL Monitor. Commands End With;  or \g.your MySQL connection ID is 4Server version:5.6.36-log mysql Community Server (GPL) Copyright (c), Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> show databases, +--------------------+| Database |+--------------------+| Information_schema | | DB1 | | DB2 | | MySQL | | Performance_schema | | Rpeltest | | Test | | Zrlog |+--------------------+8 rows in Set (0.01 sec) mysql> Grant replication Slave on *. * to ' repl ' @ ' 192.            168.242.129 ' identified by ' zlinux123456 '; #创建用户 (IP is "from"IP) Query OK, 0 rows Affected (0.00 sec) mysql> flush tables with read lock;                        #锁定数据表 (The purpose is to temporarily make it unable to continue writing, keep the existing state for synchronization) Query OK, 0 rows affected (0.01 sec) mysql> Show master status; #记住file和position (used when setting master/slave synchronization) +-----------------+----------+--------------+------------------+------------------ -+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+-----------------+----------+--------------+------------------+-------------------+|   zlinux01.000003 |              120 |                  |                   | |+-----------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec)

Iv. configuration from 1, modify the configuration file

2. Synchronizing Data

[[email protected] ~]# SCP 192.168.242.128:/tmp/*.sql/tmp///Remote copy of the Lord's backup database to from the authenticity of Host ' 192.168.242.128 (192.168.242.128) ' can ' t be established. ECDSA key fingerprint is 22:fb:63:5d:8c:78:4e:74:99:f7:b1:b3:a3:70:8d:d3. Is you sure want to continue connecting (yes/no)? Yes warning:permanently added ' 192.168.242.128 ' (ECDSA) to the list of known hosts. [email protected] ' s password:mysql.sql 100% 648KB 647.7 KB/S 00:00 [[email protected] ~]# ls/tmp/mysql.sock mysql.sql systemd-private-54b5c27d65e84794bceab836e24705c            4-vmtoolsd.service-90ptak[[email protected] ~]# mysql-uroot-pzlinux123456-e "CREATE Database Rpeltest" In the database created from above and the same as the master warning:using a password on the command line interface can be insecure. [[email protected] ~]# mysql-uroot-pzlinux123456 rpeltest </tmp/mysql.sql//import the backup database from the database warning: Using a password on the COMmand line interface can be insecure. #该过程要保证主从数据库内容的一致 

3, realize master-slave synchronization (on the Slave)

mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> change master to master_host=‘192.168.242.128‘,master_user=‘repl‘,master_password=‘zlinux123456‘,master_log_file=‘zlinux01.000003‘,master_log_pos=120;   //IP为主的IPQuery OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave;Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G               //以下两个YES说明配置成功Slave_IO_Running: YesSlave_SQL_Running: Yes

Then unlock the table in the Lord:

mysql> unlock tables;

V. Test Master and slave

[[email protected] ~]# mysql-uroot-pzlinux123456//Lord warning:using a password on the command line Interfa Ce can be insecure.  Welcome to the MySQL Monitor. Commands End With;  or \g.your MySQL connection ID is 4Server version:5.6.36-log mysql Community Server (GPL) Copyright (c), Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> use rpeltest; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> show tables;+---------------------------+| Tables_in_rpeltest |+---------------------------+| Columns_priv | | db | | Event | | Func | | General_log | | Help_category | | Help_keyword | | help_relation | | Help_topic | | Innodb_index_stats | | Innodb_table_stats | | Ndb_binlog_index | | Plugin | | Proc | | Procs_priv | | Proxies_priv | | Servers | | Slave_master_info | | Slave_relay_log_info | | Slave_worker_info | | Slow_log | | Tables_priv | | Time_zone | | Time_zone_leap_second | | Time_zone_name | | time_zone_transition | | Time_zone_transition_type | | User |+---------------------------+28 rows in Set (0.00 sec) mysql> CREATE table testuser (' id ' int (                 4)); Lord create testuser Table query OK, 0 rows affected (0.04 sec) mysql> show tables;+---------------------------+| Tables_in_rpeltest |+---------------------------+| Columns_priv | | db | |  Event                   || Func | | General_log | | Help_category | | Help_keyword | | help_relation | | Help_topic | | Innodb_index_stats | | Innodb_table_stats | | Ndb_binlog_index | | Plugin | | Proc | | Procs_priv | | Proxies_priv | | Servers | | Slave_master_info | | Slave_relay_log_info | | Slave_worker_info | | Slow_log | | Tables_priv | | TestUser | | Time_zone | | Time_zone_leap_second | | Time_zone_name | | time_zone_transition | | Time_zone_transition_type | | User |+---------------------------+29 rows in Set (0.00 sec)

View from top:

Mysql> Show tables;+---------------------------+| Tables_in_rpeltest |+---------------------------+| Columns_priv | | db | | Event | | Func | | General_log | | Help_category | | Help_keyword | | help_relation | | Help_topic | | Innodb_index_stats | | Innodb_table_stats | | Ndb_binlog_index | | Plugin | | Proc | | Procs_priv | | Proxies_priv | | Servers | | Slave_master_info | | Slave_relay_log_info | | Slave_worker_info | | Slow_log | | Tables_priv | | Time_zone | | Time_zone_leap_second | | Time_zone_name | | time_zone_transition | | Time_zone_transition_type | |   User |+---------------------------+28 rows in Set (0.00 sec) mysql> Show tables;            One more table +---------------------------+| Tables_in_rpeltest |+---------------------------+| Columns_priv | | db | | Event | | Func | | General_log | | Help_category | | Help_keyword | | help_relation | | Help_topic | | Innodb_index_stats | | Innodb_table_stats | | Ndb_binlog_index | | Plugin | | Proc | | Procs_priv | | Proxies_priv | | Servers | | Slave_master_info | | Slave_relay_log_info | | Slave_worker_info | | Slow_log | | Tables_priv | | TestUser | | Time_zone | | Time_zone_leap_second | | Time_zone_name | | time_zone_transition | | Time_zone_transition_type | | User |+---------------------------+29 rows in Set (0.00 sec) 

MySQL Replication Configuration