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

The following content mainly brings you mysql 5.7.20 master-slave configuration process handouts, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

Master-slave replication is used to establish a database environment that is exactly the same as the master database, which is called the slave database. The master database is generally a real-time business database, and there are several roles and uses of the slave database:

As a backup database, after the failure of the master database cloud server, you can switch to the slave database to continue to work; you can do backup and data statistics in the slave database, so as not to affect the performance of the master database.

Main library:

1. Add the following two lines to the configuration file

Add [mysqld] module in vi / etc/my.cnf

```

Server-id=1

Log-bin=MySQL-bin

2. Create an account `grant replication client,replication slave on *. * to rep@' from the library ip' identified by 'root' `3. Backup the main library `mysqldump-- master-data=2-- single-transaction-R-- triggers-A > all.sql` where-master-data=2 represents the backup time to record the Binlog location of master and Position,--single-transaction means to obtain consistent snapshots,-R means backup stored procedures and functions,-triggres means backup trigger, and-A represents backup of all libraries. For more information, please mysqldump-- help View 4. The binlog name and location when viewing the backup of the main library include MASTER_LOG_FILE and MASTER_LOG_POS:

[root@test ~] # head-n 30 all.sql | grep 'CHANGE MASTER TO'

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=112

[root@test ~] #

Remember MASTER_LOG_FILE and MASTER_LOG_POS. Copy the backup to the standby library `mysql < / data/ all.sql` / / use mysql to import the data from the master database: 1. Configuration file plus the following lines ```server-id=2 relay-log=relay-bin read-only = 1 replicate-ignore-db = libraries that mysql does not copy replicate-ignore-db = libraries that test do not copy replicate-ignore-db = libraries that information_schema do not copy Replicate-wild-do-table = database name. Table name / / A single table of the database to be synchronized. Multiple rows can be added.

2. Associate the master library with the slave library

CHANGE MASTER TO MASTER_HOST='192.168.0.50',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=112

3. Then turn on slave mode

Start slave

4. Check the status of allocated libraries.

[root@test ~] # mysql-e 'show slave status\ G' | egrep' Slave_IO | Slave_SQL' Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes Slave_SQL_Running: Yes [root@test ~] #

Note: note that the server-id of the master-slave mysql database must be different when configuring the master-slave database.

For the above handout on mysql 5.7.20 master-slave configuration process, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.