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

Mysql master-slave replication

With the development of technology, in the actual production environment, a single MySQL database server can not meet the actual needs. At this time, the database cluster solves this problem very well. Using MySQL distributed cluster, we can build a cluster server with high concurrency and load balance. Before that, we have to ensure that the data in each MySQL server is synchronized. Data synchronization can be easily accomplished through MySQL internal configuration, mainly master-slave replication and master-master replication.

There are two ways for MySQL5.6 to start master-slave replication: log-based (binlog) and GTID-based (global transaction identifier). This article is a log-based configuration step.

Master-slave copy schematic diagram

Environment description master database IP:192.168.1.1slave database IP:192.168.1.2mysql version: 5.5.38 master-slave replication

1. In the master-slave database configuration file, the MySQL configuration file in general Linux is in / etc/my.cnf (the configuration file in windows is mysql.ini).

Here we take the synchronous test library as an example, and the configuration is as follows:

[mysqld] # Database ID number, 1 indicates Master, where master_id must be a positive integer value between 1 and 232-1, and master-slave server-id cannot be the same; server-id=1# enables binary logging; log-bin=mysql-bin sync-binlog=1# requires synchronous binary database name; binlog-do-db=test

Save the file and restart MYSQL.

two。 Build master-slave replication

Step 1: create a 192.168.1.2 (slave) login MySQL user in 192.168.1.1 (master). Take the mysql119 user as an example.

Mysql > CREATE USER 'mysql119'@'@' IDENTIFIED BY' 123456 accounts make MySQL > GRANT REPLICATION SLAVE ON *. * TO 'mysql119'@'192.168.1.%' IDENTIFIED BY' 123456 accounts make MySQL > FLUSH PRIVILEGES

Step 2:

View the 192.168.1.1 primary MySQL server binary file name and location.

Mysql > SHOW MASTER STATUS

Step 3: tell the binary file name and location of the slave library, which is executed in 192.168.1.2:

Mysql > CHANGE MASTER TO > MASTER_HOST='192.168.95.11', > MASTER_USER='mysql119', > MASTER_PASSWORD='123456', > MASTER_LOG_FILE='mysql-bin.000007', > MASTER_LOG_POS=525; completes the master-slave replication configuration here.

3. Test master replication, in 192.168.1.2

Mysql > SLAVE START; # enable replication mysql > SHOW SLAVE STATUS\ G # check whether master-slave replication is configured successfully

As shown in the figure:

The state is normal when you see Slave_IO_Running: YES and Slave_SQL_Running: YES.

Actual test:

View the test_table of the master test library, then insert a piece of data into the master test library, and observe the data from the slave test library, as shown in the figure:

Note:

1. It is best to keep the main mysql version consistent to avoid unpredictable problems due to version differences.

two。 There is a problem of not connecting to the master database from the slave database to see if it is authorized.

3. When configuring master-slave replication, lock the table before backing up master data to ensure data consistency.

If you have any questions, please correct them.

Reference article:

Https://www.cnblogs.com/phpstudy2015-6/p/6485819.html

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.