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

The following content mainly brings you the three steps of mysql replication function, 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.

There are three main steps in the replication function of mysql

The primary CVM records the changes in the binary log (these records are called binary log events)

Copy the binary log events of the master server to its relay log from the slave server

Redo the events in the relay log from the server.

The first part of the process is that the master server records the binary log. Before the update data of each transaction is completed, master records these changes in the binary log, and mysql writes the transaction serial to the binary log. After the event is written into the binary log, the master server notifies the storage engine to commit the transaction, and then it can receive requests from the slave server.

The next step is to copy the binary log of the master service from the server to its own relay log. First, a worker thread, the I / O thread, starts from the server, and the I / O thread opens a normal connection on the master server, and then starts binlog dump process (binary rollover thread) on the master node. Binlog dump process reads events from the binary log of the primary server, and if it has caught up with the primary server, it sleeps and waits for the primary server to generate new events, which are written by the binlog dump process O thread to the relay log.

The SQL slave thread processes the last step of the process, and the SQL thread reads the event from the relay log and replays the events to update the data of the slave service to make it consistent with the data in the master service. As long as the thread is consistent with the Ibank O thread, the relay log is usually in the cache of Os, so the overhead of the relay log is small.

Mysql realizes Master-Slave replication of Database

Environment preparation: 2 centos system servers, one user as mysql master server, one for mysql slave server, configured yum source, firewall off, clock service synchronization of each node, and each node can communicate with each other through host name.

Two: prepare step iptables-F & & sentenforce clear the firewall policy and turn off selinux

Start the mysql service of the two servers respectively to ensure that the service is normal.

[root@centos7] # yum install-y mariadb [root@centos7 ~] # yum install-y mariadb-server [root@centos7 ~] # systemctl restart mariadb [root@centos7 ~] # iptables-F [root@centos7 ~] # getenforce

Configure the master master server

Including opening binaries and specifying a unique server ID

Server-id # configure server-id so that the primary server has a unique ID number

Log-bin=mysql-bin # Open the mysql log in binary format

Skip-name-resolve # turn off name resolution (optional)

[root@centos7 ~] # vim / etc/ my.cnf [mysqld] server-id = 1log-bin = master-logskip_name_resolve = ON save exit [root@centos7 ~] # systemctl restart mariadb

View the status of the primary server

Create a copy account

Establish a backup account in the database of the master server, and each slave server connects to the master server with a standard mysql username and password to perform replication operations

MariaDB [(none)] > grant replication slave,replication client on *. * to 'slave'@'172.17.%.%' identified by' 123456'

Configure slave server

Configure the slave server, open the relay log, specify a unique server ID, set read-only permissions, and add the following values in the configuration file

Server-id=2 # configure server-id so that the slave server has a unique ID number

Relay_log = mysql-relay-bin # Open the Mysql log in binary format

Read_only = 1 # set read-only permission

Log_bin = mysql-bin # enable slave server binary log

Log_slave_updates = 1 # causes updated data to be written to the binary log

Server-id=2relay-log=mysql-relay-binread-only=1log-bin=mysql-binlog-slave-updates=1

Then restart the service

[root@centos7 ~] # systemctl restart mariadb

Start the slave server replication thread, let the slave server connect to the master server, and start to redo the master server

MariaDB [(none)] > change master to master_host='172.17.252.89',-> master_user='slave',-> master_password='123456',-> master_log_file='master-log.000006',-> master_log_pos=245

Then execute start slave; in the database to start the replication thread

View the status of the slave server

You can use SHOW SLAVE STATUS\ G to view the slave server status, as shown below, or you can use show processlist\ G to view the current replication status

Then we build a database on the main server

Then you can see the database on the slave server, and the master-slave replication is successful.

For the above three steps about mysql replication, 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.