configuring MySQL master-slave replication

Description

MySQL version is 5.6.26

The installation of the MySQL step is omitted.

MySQL Master-slave replication note points

1 , on the master side, to enable the Binlog log. Slave end without enabling Binlog log

2 , master and slave, or Server-id.

The difference between master and slave my.cnf configuration

Server-id is not listed, the settings are different. is mainly the difference of [mysqld] Region

Master

Log-bin =/data/mysql_log/mysql-bin

Expire-logs-days = 14

Sync-binlog = 1

Binlog_format = row

Slave

Relay-log =/data/mysql_log/mysql-relay.log

Relay-log-index =/data/mysql_log/mysql-relay.index

Architecture diagram

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/80/4E/wKioL1c9jg_wuzdsAAAWzyOmnuY835.png "title=" 1.png " alt= "Wkiol1c9jg_wuzdsaaawzyomnuy835.png"/>

System environment

# Cat/etc/redhat-release

CentOS Release 6.6 (Final)

# UNAME-RM

2.6.32-504.el6.x86_64 x86_64

Create a database on the main library and insert data

mysql> Create Databasekevin;

Query OK, 1 row Affected (0.00 sec)

Mysql> use Kevin

Database changed

Mysql> Create Tablestudent (

-ID Int (4) NOT NULL auto_increment,

, name char (a) is not NULL,

Primary key (ID),

Key Index_name (name)

) Auto_increment=1;

Query OK, 0 rows affected (0.10 sec)

mysql> Insert Intostudent VALUES (1, ' Kevin '), (2, ' Dave '), (3, ' Danny '), (4, ' Jenny '), (5, ' Jerry ');

Query OK, 5 rows Affected (0.02 sec)

Records:5 duplicates:0 warnings:0

Create Master-slave replication

Working on Master

Create a synchronized user

Mysql> GRANT replicationslave on * * to ' repuser ' @ ' 192.168.56.% ' identified by ' 123456 ';

Query OK, 0 rows Affected (0.00 sec)

mysql> FLUSH privileges;

Query OK, 0 rows Affected (0.00 sec)

Mysql> FLUSH LOGS;

Query OK, 0 rows Affected (0.00 sec)

Mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

|      mysql-bin.000001 | 2189 |

|       mysql-bin.000002 | 323 |

+------------------+-----------+

2 rows in Set (0.00 sec)

Mysql> \q

Bye

Backing Up the database

[[Email protected] ~] #innobackupex--user=root--password=123456/backup/

Prepare for backup

[[Email protected] ~] #innobackupex--apply-log/backup/2016-05-18_19-19-38/

When inserting a piece of data

Mysql> use Kevin

Database changed

mysql> Insert Intostudent VALUES (6, ' Lucy ');

Query OK, 1 row affected (0.14 sec)

Mysql> SELECT * Fromstudent\g

1. Row ***************************

Id:3

Name:danny

2. Row ***************************

Id:2

Name:dave

3. Row ***************************

Id:4

Name:jenny

4. Row ***************************

Id:5

Name:jerry

5. Row ***************************

Id:1

Name:kevin

6. Row ***************************

Id:6

Name:lucy

6 rows in Set (0.00 sec)

Mysql> Show Masterstatus\g

Copy the backup to the slave library

[Email protected] ~]# scp-r/backup/2016-05-18_19-19-38 [email protected]:/backup/

slave Action on

Delete raw Data

[Email protected] ~]# cd/data/mysql_data/

[Email protected]_data]# rm-fr *

[[Email protected]_data]# CD]

Recovering data

[[Email protected] ~] #innobackupex--copy-back/backup/2016-05-18_19-19-38/

[[Email protected] ~] #chown-R mysql.mysql/data/mysql_data/

Start MySQL

[[email protected] ~]#/etc/init.d/mysqld start

Validating data

[[Email protected] ~] #mysql-uroot–p

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Kevin |

| MySQL |

| Performance_schema |

+--------------------+

4 rows in Set (0.04 sec)

Mysql> use Kevin;

Database changed

Mysql> SELECT * Fromstudent\g

+----+-------+

| ID | name |

+----+-------+

| 3 | Danny |

| 2 | Dave |

| 4 | Jenny |

| 5 | Jerry |

| 1 | Kevin |

+----+-------+

5 rows in Set (0.07 sec)

Binlog from the library to read the main library

mysql> Change Master tomaster_host= ' 192.168.56.12 ', master_port=3306,master_user= ' repuser ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000001 ', master_log_pos=2189;

Start slave

mysql> start slave; or execute start slaveio_thread; and start SLAVE sql_thread;

mysql> SELECT * fromstudent;

+----+-------+

| ID | name |

+----+-------+

| 3 | Danny |

| 2 | Dave |

| 4 | Jenny |

| 5 | Jerry |

| 1 | Kevin |

| 6 | Lucy |

+----+-------+

6 rows in Set (0.00 sec)

Data is now fully synchronized

Perform exactly the same operation as the slave1 on the slave2.

As a result of the limitations of personal technology, you are also requested to point out. The author can be found through the following two groups.

Beijing Linux operation and maintenance recruitment group: 153677549

Linux OPS Development Group: 298324302

This article is from the "Chang Jie listen to the Wind People" blog, please make sure to keep this source http://kevinhao.blog.51cto.com/5204735/1775132

configuring MySQL master-slave replication