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

1. Preparatory work

(permanent address of this article: http://woymk.blog.51cto.com/10000269/1922438)

Configuration environment: centos6.9,mysql5.7

Install and configure two MySQL servers first

Master server IP:192.168.1.1

IP:192.168.1.2 from the server

For mysql installation, please refer to the previous article:

LAMP environment building (centos6.9+apache2.4+mysql5.7+php7.1)

two。 Configuration Master (master)

Vi / etc/my.cnf

Modify or add the following lines:

Server-id=1

Log-bin=mysql-bin # enable binary logging

Two optional parameters (2 to choose 1):

Binlog-do-db=db1,db2 # libraries to be synchronized

Binlog-ignore-db=db1,db2 # ignore out-of-sync libraries

Restart after saving

/ etc/init.d/mysqld restart

Run the mysql client

Mysql-uroot-p

Create a synchronization account

Grant replication slave on *. * to 'repl'@'192.168.1.2' identified by' 1234'

Flush privileges

Lock the table to prevent data from being written

Flush tables with read lock

Show master status

Record the binary log file name and location

Backup database

Execute on master

Mysqldump-uroot-p db1 > back.sql

Copy the backed-up data to the slave server

Scp back.sql 192.168.1.2 purl ~

3. Configure slave (slave)

Vi / etc/my.cnf

To modify or add:

Server-id=2 # this number cannot be the same as the master

Optional parameters (2 choose 1, these two parameters are set to the same as the master):

Replicate-do-db=db1,db2

Replicate-ignore-db=db1,db2

Restart after saving

/ etc/init.d/mysqld restart

Import the data backed up by the master server into the slave server

Execute on slave

[root@slave] # mysqldump-uroot-p db1

< back.sql 运行mysql客户端 mysql -uroot -p 执行以下命令 stop slave; (根据mysql官方手册的说明，5.5之前是slave stop，5.6之后改为stop slave) change master to master_host='192.168.1.1', master_user='repl', master_password='1234',master_log_file='mysql-bin.000001', master_log_pos=2757; (master_log_file和master_log_pos填上刚才记录下的二进制日志文件名和位置) start slave; 查看从服务器的状态： show slave status\G 完成后取消主服务器数据库的锁定 在主上执行 mysql>

Unlock tables

4. Verify the effect of master-slave replication

Create the database test_db on the primary server

Create database test_db

Create table test_tb on the primary server

Use test_db

Create table test_tb (id int (3), name char (10))

Write a row of records

Insert into test_tb values (001, "test")

View from the server

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | discuz |

| | mysql |

| | performance_schema |

| | sys |

| | test_db |

| | www |

+-+

7 rows in set (0.00 sec)

The database test_db has been created automatically

Mysql > select * from test_db.test_tb

+-+ +

| | id | name |

+-+ +

| | 1 | test |

+-+ +

1 row in set (0.00 sec)

The data in the table has also been copied automatically, indicating that the master-slave synchronization configuration is successful.

When configuring mysql master and slave, the following error is displayed:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

This is because the mysql is cloned, or the auto.cnf file is also copied when copying the entire data directory, resulting in the same master-slave mysql uuid, and Slave_IO cannot be started.

Solution: modify the value of uuid in the auto.cnf file in the mysql data directory, or delete the auto.cnf file and restart the mysql service to automatically generate a new UUID value.

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.