MySQL Master-slave replication Introduction and configuration

1.MySQL Master-Slave Replication Introduction

MySQL database supports the replication of one-way, two-way, chained cascade, ring-like, and different business scenarios. During the replication process, one server acts as the primary server (master), receives content updates from the user, and one or more other servers act as slave servers (Slave), receive log content from the primary server binlog files, parse out the SQL, and re-update to the slave database, Match the data of the winner from the server.

If chained cascade replication is set, then from the server itself, in addition to acting from the server, it will also act as the primary server under it from the server, chained cascade replication similar to the A->b->c copy form. Here, only master-to-form configuration scenarios are described. As for the inter-master from, the chain Cascade has the opportunity to discuss later.

The main scenarios for MySQL's master-slave replication are as follows:

1) Real-time data backup from the server as the primary server

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

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

2.MySQL Master-Slave Replication Principle Introduction

MySQL's master-slave replication is an asynchronous replication process (which normally feels real-time), and the data is copied from one MySQL database (Master) to another MySQL database (Slave), The process of implementing the entire master-slave replication between master and slave is done by three threads. There are two threads (SQL thread and I/O thread) on the slave side, and another thread (I/O thread) on the master side.

To achieve MySQL master-slave replication, you must first open the master side of the Binlog recording function, otherwise it will not be implemented. Because the entire replication process is actually the slave side obtains the Binlog log from the master side, and then executes the various SQL operations that are recorded in the Binlog log in the same order on slave.

To turn on the Binlog logging feature of MySQL, you can do this by adding "log-bin" parameters to the Mysqld module ([mysqld] in the parameters section of the my.cnf) in the MySQL configuration file.

3. Configure MySQL master-slave replication

The master-slave replication of this experiment has a one-way master-slave copy, a primary and a slave. The two MySQL servers are set up according to the previous method, and the corresponding ports are also open.

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

[mysqld]server_id = #不能和从数据库的一样log-bin =/usr/local/mysql/data/mysql-bin

Restart database after saving exit

/etc/init.d/mysqld restart

(2) Log in to the main database, check the parameters of the situation, see Log-bin has been opened.

(3) Set up an account for master/slave replication on the main library to check the success of account creation.

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

(4) Main Library lockout table, then back up the database

Flush table with read lock;

After locking the table to view the state of the main library, it needs to be documented that the main library will be copied from this location.

A new window is opened, the data is backed up with mysqldump, and then transferred to the slave library.

Mkdir-p/server/backup #创建保存备份文件的文件夹mysqldump-uroot-p ' pcm123456 '--events-a-B |gzip >/server/backup/mysql_bak. $ (date +%f). sql.gz #备份并压缩scp/server/backup/mysql_bak.2018-04-04.sql.gz [email protected]:/root/#用scp复制到从库上

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

(5) operate from the library, set the Server-id and turn off the Binlog function.

The Server-id from the library cannot be the same as the ServerID of the main library, where we set the Server-id of the main library to be closed by default for the 53,binlog function.

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

/etc/init.d/mysqld restart

Log in to MySQL check these two parameters, confirm the error

(6) operation from the library, restore the database exported from the main library to the library

Mysql-uroot-p <mysql_bak.2018-04-04.sql

(7) operation from the library, configure the replication parameters.

Change MASTER tomaster_host= ' 192.168.31.52 ', master_port=3306,master_user= ' rep ', master_password= ' pcm123456 ', Master_log_file= ' mysql-bin.000009 ', master_log_pos=120;

This operation is actually to write the user, password and other information of the main library to the Master.info file from the library.

(8) Turn on the sync switch from the library to test the master-slave replication configuration

View the status of the slave, note the status of the slave process, the number of seconds of delay in the primary library replication

Here, MySQL's master-slave configuration is basically complete. Let's add a database pcm_test on the main library to see if the library will be updated together. Confirmed.

MySQL Master-slave replication Introduction and configuration