Shulou(Shulou.com)06/03 Report--

MySQL5.6 master-slave replication

Five steps to complete MySQL master-slave replication

1. / etc/my.cnf configuration of the master-slave server, set the unique ID to enable binary logging.

two。 Create a master-slave copy account and grant REPLICATION SLAVE permissions.

3. Query the status of master to get the binary log information of the main server.

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

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

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

1. Modify the / etc/my.cnf configuration files of the master server and slave server

Modify the primary server master:

Vi / etc/my.cnf

[mysqld]

Log-bin=mysql-bin # enables binary logging and sets the binary log file prefix

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

Note: the skip-networking parameter option cannot be used in the configuration file, otherwise the slave server will not be able to connect to the master server and copy the data.

Modify the slave server slave:

Vi / etc/my.cnf

[mysqld]

Log-bin=mysql-bin

Server-id=223

Note: if there are multiple slave servers, all server ID numbers must be unique.

The binary logging function of MySQL slave server does not need to be turned on.

However, you can also enable data backup and recovery by enabling the binary logging function of the slave server, and in some more complex topology environments, the MySQL slave server can also act as the master server of other slave servers.

After the modification is completed, restart the mysql of both servers

Service mysql restart

two。 Establish an account on the primary server and authorize slave:

Mysql-uroot-p123

Mysql > GRANT REPLICATION SLAVE ON *. * to 'mysync'@'%' identified by' 123456'

This account must have REPLICATION SLAVE permission, 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 mysql of the master server 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 shows the binary log file name, and Position is the current logging location, which is needed from the server's settings.

Note: do not operate the master server MYSQL after performing this step to prevent the status value of the master server from changing

To prevent other hosts from manipulating the master database, you can use read-only locking tables to prevent the database from being modified.

Mysql > flush tables with read lock

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)

Mysql > unlock tables

The flush tables with read lock; command performs read-only locking on all tables in all databases

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

Perform locking to prevent someone from modifying the data while viewing binary log information

Finally, an end operation is performed on the global lock using the unlock tables; statement.

Tip:

If a large amount of data already exists in the MySQL database system, you can use the mysqldump tool to make a backup on the master server, and then import the slave server.

(master) export

Mysqldump-u root-pendant 123'-- all-databases-- lock-all-tables > bak_mysql.sql

Import (from)

Mysql-u root-pendant 123456'

< bak_mysql.sql 4.配置从服务器Slave： 数据复制的关键操作是配置从服务器去连接主服务器进行数据复制，我们需要告知从服务器建立网络连接所有必要的信息。 使用CHANGE MASTER TO 语句即可完成该项工作， MASTER_HOST 指定主服务器主机名或IP地址， MASTER_USER 为主服务器上创建的拥有复制权限的账户名称， MASTER_PASSWORD 为该账户的密码， MASTER_LOG_FILE 指定主服务器二进制日志文件名称， MASTER_LOG_POS 为主服务器二进制日志当前记录的位置。 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; / / start the copy from server function

5. Check the replication function status 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 / / Master server address

Master_User: mysync / / authorized account name, avoid using root as much as possible

Master_Port: 3306 / / database port, which is not available in some versions

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 520 / / the location where the binary log is read synchronously, which is 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 be running normally, that is, the YES state, otherwise they are all in the wrong state (for example, one of the NO is wrong).

The configuration of the master and slave server is completed in the above operation process.

6. Master-slave server test:

The master server Mysql, set up the database, and insert a piece of data into the library by creating a table:

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)

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | test_db |

| | mysql |

+-+

4 rows in set (0.00 sec)

Query from server Mysql:

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 the specific data added on the master server

+-+ +

| | id | name |

+-+ +

| | 1 | bobu |

+-+ +

1 row in set (0.00 sec)

MySQL master-slave replication completed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.