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

Mysql's Replication is an asynchronous replication process (the above version of mysql5.1.7 is divided into asynchronous replication and semi-synchronous modes), replicating from one Mysql instace (we call it Master) to another Mysql instance (we call it Slave). The whole replication process between Master and Slave is mainly completed by three threads, of which two threads (Sql thread and IO thread) are on the Slave side, and the other thread (IO thread) is on the Master side.

1.1. The principle of master and slave of mysql

1.1.1. Replication thread

Mysql's Replication is an asynchronous replication process (the above version of mysql5.1.7 is divided into asynchronous replication and semi-synchronous modes), replicating from one Mysql instace (we call it Master) to another Mysql instance (we call it Slave). The whole replication process between Master and Slave is mainly completed by three threads, of which two threads (Sql thread and IO thread) are on the Slave side, and the other thread (IO thread) is on the Master side.

In order to realize the Replication of MySQL, we must first turn on the Binary Log (mysql-bin.xxxxxx) function on the Master side, otherwise it cannot be realized. Because the whole replication process is actually Slave takes the log from the Master side and then performs the various operations recorded in the log in full sequence on its own. You can open the Binary Log of MySQL by using the "- log-bin" parameter option during the startup of MySQL Server, or by adding the "log-bin" parameter item to the mysqld parameter group in the my.cnf configuration file (the parameter section after [mysqld] identification).

1.1.2. The basic process of MySQL replication:

The IO thread above the 1.Slave connects to the Master and requests the log contents from the specified location of the specified log file (or from the beginning of the log)

After Master receives the request from the IO thread of Slave, the IO thread responsible for replication reads the log information after the specified location of the log according to the request information, and returns it to the IO thread on the Slave side. In addition to the information contained in the log, the returned information also includes the name of the Binary Log file on the Master side and the location in the Binary Log.

After receiving the information, the IO thread of Slave writes the received log contents to the end of the Relay Log file (mysql-relay-bin.xxxxxx) on the Slave side, and records the file name and location of the read bin-log on the Masterside into the master- info file, so that it can clearly Master at a high speed on the next read. "where do I need to start the log content in a certain bin-log? please send it to me."

As soon as the SQL thread of Slave detects a new addition to the Relay Log, it parses the contents of the Log file into executable Query statements that are actually executed on the Master side, and executes the Query on its own. In this way, the same Query is actually executed on both the Master side and the Slave side, so the data on both sides is exactly the same.

1.2. Db01

1.2.1. Binlog logs opened on the main library

[root@db01 ~] # cat / etc/ my.cnf [mysqld] log-binserver-id=160

1.2.2. Restart the database

[root@db01 ~] # systemctl restart mysqld

1.2.3. Authorization to allow hosts that can connect remotely (replicaiton)

Mysql > grant replication slave, replication client on *. * to 'repl'@'172.16.1.52' identified by' All123.com'

1.2.4. Export the current data of the main database

[root@db01] # mysqldump-uroot-pBgx123.com\-- all-databases\-- single-transaction\-- master-data=1\-- flush-logs > / root/db-$ (date +% F)-all.sql

1.2.5. Copy the backup data to the slave library

[root@db01] # scp db-2018-10-23-all.sql root@172.16.1.52:/tmp

1.3. Db02

Start the database on the slave library and pour in the data

1.3.1. Check if you can log in using a remote account

[root@slave] # mysql-h 172.16.1.51-urep-pRep123.com

1.3.2. Modify the configuration file / etc/my.cnf to enable binlog as needed

Server-id=161

1.3.3. Restart MySQL's database service

Systemctl restart mysqld

1.3.4. Log in to the MySQL database [enter the password filtered in the previous step]

[root@web02 ~] # mysql-uroot-p$ (awk'/ temporary password/ {print $NF}'/ var/log/mysqld.log)

1.3.5. Change the database password again

Mysql > ALTER USER 'root'@' localhos`t' IDENTIFIED BY 'Bgx123.com'

1.3.6. Import data and chase the master's bin_log

[root@db02] # mysql-uroot-pBgx123.com

Change master tomaster_host='172.16.1.51',master_user='repl',master_password='All123.com'

1.3.8. Start the slave thread

Mysql > start slave

1.3.9. Check the master-slave status of MySQL

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.1.51Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: db01-bin.000002Read_Master_Log_Pos: 6671Relay_Log_File: Db02-relay-bin.000004Relay_Log_Pos: 6882Relay_Master_Log_File: db01-bin.000002Slave_IO_Running: YesSlave_SQL_Running: Yes

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.