MySQL Getting Started 04-mysql master-slave configuration

• 1. master node Authorized synchronization User
• 2. master node Modify configuration file
• 3. Modifying a configuration file from a node
• 4. View status after the primary node is locked
• 5. Configure and initiate synchronization, view status from the node
• 6. The master node unlocks and verifies that the slave node is properly synchronized

Environment: CentOS 6.7 + MySQL 5.6.30
Master node: 192.168.56.102
From node: 192.168.56.103
have installed the standalone MySQL separately, now configure two MySQL master-slave replication.

1. master node Authorized synchronization User

The official documentation is carried out in two steps:

mysql> CREATE USER ' repl ' @ '%.mydomain.com ' identified by ' slavepass ';

Mysql> GRANT REPLICATION SLAVE on . To ' repl ' @ '%.mydomain.com ';

In fact, a single command can:

grant replication slave,reload,super on *.* to ‘repl‘@‘192.168.56.103‘ identified by ‘asdfjkl‘;

2. master node Modify configuration file

Vi/data/mysqldata/3306/my.cnf

Add content, in fact, is mainly server-id=102, in addition, for InnoDB, the official file suggested adding two parameters:

[mysqld]server-id=102#binlog-ignore-db = mysql#replicate-do-db = xxdb#InnoDBinnodb_flush_log_at_trx_commit=1sync_binlog=1

Master node restart MySQL:

shell> mysqladmin shutdownshell> mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf &

3. Modifying a configuration file from a node

Vi/data/mysqldata/3306/my.cnf

Add to:

[mysqld]server-id=103#replicate-do-db = xxdb

To restart MySQL from a node:

shell> mysqladmin shutdownshell> mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf &

4. View status after the primary node is locked

Master node lockout:

WITH READ LOCK;

Master node View status:

([email protected])[(none)]>  show master status    -> ;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000011 | 432 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

5. Configure and initiate synchronization, view status from the node

From the node configuration:

TO    MASTER_HOST=‘192.168.56.102‘,    MASTER_USER=‘repl‘,    MASTER_PASSWORD=‘asdfjkl‘,    MASTER_LOG_FILE=‘mysql-bin.000011‘, MASTER_LOG_POS=432;

The log file and the current location are filled in according to the status of the master node found in the previous step.

Start slave:

START SLAVE;

View from node State:

status \G

6. The master node unlocks and verifies that the slave node is properly synchronized

Master node Unlock:

mysql> UNLOCK TABLES;

Master Node Build library:

mysql> create database xxdb;

To see if the synchronization is synchronized from the node:

mysql> show databases;
Ext.: http://www.cnblogs.com/jyzhao/p/5552077.html

MySQL Getting Started 04-mysql master-slave configuration