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

Introduction to 1.MySQL Master-Slave replication

MySQL database supports replication of different business scenarios such as one-way, two-way, chain cascade, ring, and so on. In the process of replication, one server acts as a master server (Master), receiving content updates from users, while one or more other servers act as slave servers (Slave), receiving the log contents of the binlog files of the autonomous server, parsing the SQL, and re-updating to the slave database to make the data of the master and slave servers consistent.

If chain cascading replication is set, the slave server itself acts not only as a slave server, but also as a master server below it, which is similar to A-> B-> C replication. Here, only the master-> slave configuration scheme is introduced. As for being the master and slave of each other, the chain cascades will have the opportunity to discuss again in the future.

The main scenarios of master-slave replication of MySQL are as follows:

1) Real-time data backup of slave server as master server

2) Master-slave server to achieve read-write separation (master-write-slave read), and slave server to achieve load balancing

3) split multiple slave servers according to business importance (split from servers according to business)

Introduction to the principle of 2.MySQL Master-Slave replication

The master-slave replication of MySQL is an asynchronous replication process (generally felt to be real-time). The data will be copied from one MySQL database (Master) to another MySQL database (Slave). The whole process of master-slave replication between Master and Slave is completed by three threads. There are two threads (the SQL thread and the I / O thread) on the Slave side, and the other thread (the Imax O thread) on the Master side.

In order to realize the master-slave replication of MySQL, the binlog recording function on Masterside must be turned on first, otherwise it cannot be realized. Because the whole replication process is actually that the Slave side gets the binlog log from the Master side, and then performs the various SQL operations recorded in the acquired binlog log on the Slave in the same order.

To turn on the binlog recording function of MySQL, you can do this by adding the "log-bin" parameter to the mysqld module in the MySQL configuration file my.cnf (the parameter section after [mysqld] identification).

3. Configure MySQL master-slave replication

The master-slave replication of this experiment has one-way master-slave replication, a Master and a Slave. Both MySQL servers have been built according to the previous method, and both have opened their corresponding ports.

(1) in the configuration of the main library Master, open the / etc/my.cnf file with vim and add server_id and log-bin parameters

[mysqld] server_id = 52 # cannot be the same as the slave database log-bin = / usr/local/mysql/data/mysql-bin

Restart the database after save and exit

/ etc/init.d/mysqld restart

(2) Log in to the main database, check the parameters, and see that log-bin has been opened.

(3) set up an account on the master database for master-slave replication, and check that the account is established successfully.

Grant replication slave on *. * to 'rep'@'192.168.31.%' identified by' pcm123456';flush privileges

(4) lock the table in the main database, and then back up the database.

Flush table with read lock

Check the status of the main database after locking the table, which needs to be recorded. It is from this location that the master database will be copied later.

Open a new window, back up the data with mysqldump, and then transfer it to the slave library.

Mkdir-p / server/backup # create a folder to save backup files mysqldump-uroot-p 'pcm123456'-- events-A-B | gzip > / server/backup/mysql_bak.$ (date +% F). Sql.gz # back up and compress scp / server/backup/mysql_bak.2018-04-04.sql.gz root@192.168.31.53:/root/ # copy to the slave library with scp

After the backup, unlock the main library and resume reading and writing

(5) operate from the library, set up server-id and turn off binlog function.

The server-id on the slave library cannot be the same as the serverid of the master library. Here we set the server-id of the master library to be turned off by default.

Save exit after editing with vim, and then restart the database.

/ etc/init.d/mysqld restart

Log in to mysql to check these two parameters and confirm that they are correct

(6) operate from the database to restore the database exported from the master database to the slave database.

Mysql-uroot-p

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.