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

This article introduces how to achieve master-slave replication in mysql. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

0. Why do I need master-slave replication?

1. In a system with complex business, there is a scenario where a sql statement needs to lock the table, resulting in the temporary inability to use the read service, which will affect the running business. Use master-slave replication, and let the master database be responsible for writing and the slave database for reading. In this way, even if the master database locks the table, the normal operation of the business can be ensured by reading the slave database.

2. Make hot backup of data

3. The expansion of the architecture. Due to the increasing volume of business, the access frequency of Iamp O is too high to be satisfied by a single machine. At this time, the storage of multiple databases can be done to reduce the access frequency of disk Iamp O and improve the performance of single machine.

1. What is the master-slave replication of mysql?

MySQL master-slave replication means that data can be replicated from one MySQL database server master node to one or more slave nodes. MySQL uses asynchronous replication by default, so that the slave node does not have to access the master server all the time to update its own data. The data update can be carried out on the remote connection, and the slave node can copy all the databases in the master database or specific databases, or specific tables.

2. The principle of mysql replication

Principle:

(1) the master server records the changes of the data in the binary binlog log. When the data on the master changes, the changes are written to the binary log.

(2) the slave server will detect whether the master binary log has changed within a certain time interval, and if so, start an I/OThread request master binary event

(3) at the same time, the master node starts a dump thread for each I/OThread O thread, which is used to send binary events to it and save it to the local relay log of the slave node. The slave node will start SQL thread to read the binary log from the relay log and replay it locally, so that its data is consistent with that of the master node. Finally SQL and SQL will go to sleep and wait for the next time to wake up.

In other words:

Two threads are generated from the library, one is an Icano thread and the other is a SQL thread.

The Icano thread requests the binlog of the main library and writes the resulting binlog to the local relay-log (Relay Log) file.

The master library generates a log dump thread, which is used to pass binlog to the slave library Imaco thread.

The SQL thread reads the logs in the relay log file and parses them into sql statements to execute one by one

Note:

1--master records the operation statement in the binlog log, and then grants slave permission to connect remotely (master must enable binlog binary log function; usually for data security reasons, slave also enables binlog feature). 2--slave starts two threads: the IO thread and the SQL thread. The IO thread is responsible for reading the binlog content of the master to the relay log relay log; the SQL thread is responsible for reading the binlog content from the relay log log and updating it to the slave database, so as to ensure that the slave data is consistent with the master data. 3--Mysql replication requires at least two Mysql services. Of course, Mysql services can be distributed on different servers, or multiple services can be started on one server. 4--Mysql replication is best to ensure that the version of Mysql on the master and slave servers is the same (if the version cannot be met, the version of the master master node is lower than that of the slave slave node) the time between 5--master and slave nodes needs to be synchronized

Specific steps:

1. The slave library connects to the master database by executing change master to statements manually, providing all the conditions of the connected users (user, password, port, ip), and letting the slave database know the starting position of the binary log (file name position number); start slave

2. Establish a connection between the IO thread of the slave library and the dump thread of the master library.

3. According to the file name and position number provided by the change master to statement, the IO thread initiates the binlog request to the master database.

4. The master library dump thread sends the local binlog to the slave library IO thread in the form of events according to the request of the slave library.

5. Receive the binlog events from the library IO thread and store it in the local relay-log. The transmitted information will be recorded in the master.info.

6. Apply relay-log from the library SQL thread, and record the applied relay to relay-log.info. By default, the applied relay will be cleaned automatically.

Mysql master-slave replication installation configuration 1, basic settings preparation # operating system: centos6.5#mysql version: 5.The two virtual machines: node1:192.168.85.11 (master) node2:192.168.85.12 (slave) 2, install mysql database # detailed installation and uninstall steps refer to the corresponding document 3, create databases in the two databases-- note that both must perform create database msb 4. Configure the master (node1) server as follows: # modify the configuration file, execute the following command to open the mysql configuration file vi / etc/my.cnf#, add the following configuration information to the mysqld module: log-bin=master-bin # binary file name binlog-format=ROW # binary log format, there are three formats: row, statement, mixed. Row refers to copying the changed content, rather than executing the command on the slave server. Statement refers to the SQL statement executed on the master server and the same statement executed on the slave server. MySQL uses statement-based replication by default, which is more efficient. Mixed refers to statement-based replication by default, and row-based replication as soon as it is found that statement-based replication cannot be accurately replicated. Server-id=1 # requires that the id of each server must be different. Binlog-do-db=msb # synchronized database name 5. Configure the account authorization to log in to the master server from the server-- authorize the operation set global validate_password_policy=0;set global validate_password_length=1;grant replication slave on *. * to 'root'@'%' identified by' 123456database-refresh permission flush privileges 6. From the configuration of the server # modify the configuration file, execute the following command to open the mysql configuration file vi / etc/my.cnf# to add the following configuration information to the mysqld module: the name of the log-bin=master-bin# binary file, the format of the binlog-format=ROW# binary file, the id7 of the server-id=2# server, restart the mysqld service of the master server # restart the mysql service service mysqld restart# login to the mysql database mysql-uroot-p # to view the status show master status of master 8. Restart the slave server and make related configuration # restart mysql service service mysqld restart# login mysqlmysql-uroot-p # connect to the master server change master tomaster_host='192.168.150.11',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=334 # start slavestart slave# to check the status of slave show slave status\ G (note that there is no semicolon) this is about how to implement master-slave replication in mysql. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.