Five steps to complete MySQL master-slave replication

MySQL5.6 Master-slave replication

Five steps to complete MySQL master-slave replication

1./ETC/MY.CNF configuration of master and slave server, set unique ID to enable binary log.

2. Create a master-slave copy of the account and authorize replication slave permissions.

3. Query the status of master to obtain the primary server binary log information.

4. Configure the server to connect to the master server for data replication.

5. Check the status of the replication function from the server and test the master-slave replication.

Reference article http://manual.blog.51cto.com/3300438/1372378

1. Modify the configuration file of the master server and the/etc/my.cnf from the server

To modify the master server master:

Vi/etc/my.cnf

[Mysqld]

Log-bin=mysql-bin #启用二进制日志 and set the binary log file prefix

server-id=222 #[must be] server unique ID, must be an integer between 1 and 232-1

Note: You cannot use the skip-networking parameter option in the configuration file, or the server will not be able to connect to the primary server and replicate the data.

Modify the slave from the server:

Vi/etc/my.cnf

[Mysqld]

Log-bin=mysql-bin

server-id=223

Note: If you have more than one slave server, all server ID numbers must be unique.

MySQL from the server on the binary log function is not required to open.

However, you can also enable data backup and recovery by enabling binary logging from the server, and in some more complex topologies, MySQL from the server can also play other slave servers from the primary server.

After the modification is complete, restart the MySQL for both servers

Service MySQL Restart

2. Establish an account on the primary server and authorize slave:

Mysql-uroot-p123

Mysql>grant REPLICATION SLAVE on * * to ' mysync ' @ ' percent ' identified by ' 123456 ';

This account must have replication slave permissions, you can create different accounts and passwords for different slave servers, or you can use a unified account and password.

3. Log in to the master server for MySQL and query the status of master

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|   mysql-bin.000008 |              520 |                  |                   | |

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

1 row in Set (0.00 sec)

The file column displays the binary log file name, position is the current log record location, which is required from the server's settings.

Note: Do not re-operate the master server MySQL after performing this step to prevent changes in the primary server state value

Flush tables with read lock; The purpose of the command is to perform a read-only lock on all tables in all databases,

Write operations for all databases are rejected after a read-only lock, but read operations can continue.

Performing a lock prevents someone from modifying the data while viewing the binary log information,

finally use unlock tables; Statement to perform an end operation on a global lock.

Tips:

If a large amount of data already exists in the MySQL database system, you can use the Mysqldump tool to back up the primary server and then import it from the server.

(master) Export

Mysqldump-u root-p ' 123 '--all-databases--lock-all-tables > Bak_mysql.sql

Import (from)

Mysql-u root-p ' 123456 ' < Bak_mysql.sql

4. Configure the slave from the server:

The key operation of data replication is to configure the server to connect to the primary server for data replication, and we need to tell all necessary information from the server to establish a network connection.

Use the change MASTER to statement to complete the work.

Master_host Specify the host name or IP address of the primary server,

Master_user the name of the account that has the copy permission created on the primary server,

Master_password is the password for the account,

MASTER_LOG_FILE Specifies the primary server binary log file name,

Master_log_pos the location of the current record for the primary server binary log.

Mysql-u root-p ' 123456 ' #进入MySQL

Mysql>change Master to master_host= ' 192.168.1.100 ', master_user= ' Mysync ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000008 ', master_log_pos=520;

Mysql>start slave; To start the Copy from Server feature

5. Check the status of the replication function from the server:

Mysql> Show Slave Status\g

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

Slave_io_state:waiting for Master to send event

MASTER_HOST:192.168.2.100//Primary server address

Master_user:mysync//Authorization account name, try to avoid using root

master_port:3306//Database port, some versions do not have this line

Connect_retry:60

master_log_file:mysql-bin.000008

READ_MASTER_LOG_POS:520//synchronously reads the location of the binary log, greater than or equal to the primary server

relay_log_file:ddte-relay-bin.000003

relay_log_pos:251

relay_master_log_file:mysql-bin.000008

Slave_io_running:yes//This status must be Yes

Slave_sql_running:yes//This status must be Yes

......

Note: The slave_io and slave_sql processes must function normally, that is, the Yes state, otherwise it is an error state (e.g., one of the No is an error).

The above operation process, the master and slave server configuration is complete.

6. master-Slave Server testing:

master server MySQL, build the database, and create a table in this library to insert a piece of data:

mysql> CREATE DATABASE test_db;

Query OK, 1 row Affected (0.00 sec)

mysql> use test_db;

Database changed

Mysql> CREATE TABLE test_db (ID int (3), name char (10));

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO test_db values (001, ' Bobu ');

Query OK, 1 row Affected (0.00 sec)

mysql> show databases;

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

| Database |

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

| Information_schema |

| test_db |

| MySQL |

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

4 rows in Set (0.00 sec)

MySQL Query from server:

mysql> show databases;

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

| Database |

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

| Information_schema |

| test_db |

| MySQL |

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

4 rows in Set (0.00 sec)

Mysql> Use test_db

Database changed

Mysql> select * from test_db; View new specific data on the primary server

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

| ID | name |

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

| 1 | Bobu |

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

1 row in Set (0.00 sec)

MySQL master-slave replication is complete.

Five steps to complete MySQL master-slave replication