Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is MySQL master-slave replication". In daily operation, I believe that many people have doubts about what is MySQL master-slave replication. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what is MySQL master-slave replication"! Next, please follow the editor to study!

1. Introduction and principle of master-slave replication

Master-slave replication (also known as AB replication) means that one server acts as the master database server and one or more servers act as the slave database server, and the data in the master server is automatically replicated to the slave server. For multilevel replication, the database server can act as both a host and a slave. MySQL uses asynchronous replication by default.

The process and principle of master-slave replication can be summarized as follows:

The master server records the change of the data in the binary binlog log, and when the data on the master changes, it writes the change to the binary log.

The slave server detects the master binary log at a certain time interval to see if it has changed, and if so, starts an I/OThread request master binary event.

At the same time, the master node starts a dump thread for each Igamo thread, which is used to send binary events to it and save them to the local relay log of the slave node. The slave node will start the SQL thread to read the binary log from the relay log and replay it locally to make its data consistent with that of the master node.

two。 Configure master-slave replication based on binary file location

Master-slave replication based on binary file location can also be called traditional replication, that is, the slave server depends on the binlog file location of the master server. When the data of the master database changes, the binlog pos bit will grow, and the slave library will sense the change to complete the synchronization.

To configure master-slave replication, we first need to prepare at least two MySQL instances, one as the master server and the other as the slave server. Since master-slave replication depends on binlog, binlog must be enabled in the master database, and different server_id must be configured for master-slave. The following details show the configuration process:

2.1 confirm master-slave library configuration parameters

The MySQL master / slave server is recommended to have the following configuration. You can confirm it first. If it is not configured, you need to modify the configuration file and restart it.

# Master database parameter configuration should have the following parameters vim / etc/my.cnf [mysqld] log-bin = binlog / enable binary log server-id = 137 / / server unique ID, the default value is 1, generally set to the last number of IP address binlog_format = row / / bilog is set to row mode to prevent replication errors # it is recommended to configure the following parameters vim / etc/my.cnf [mysqld] relay-log = relay-binserver-id = 138from the slave library

2.2 determine the binary location of the main library and create a synchronous account

If the master and slave libraries have just been initialized and the master library has no operation, the slave database can directly determine the binlog location of the master database without synchronizing the data of the master library.

# View the binlog file location of the main library and create a synchronization account under show master status;# 'repl'@'%' identified by' 123456 grant replication slave on *. * to 'repl'@'%'

If the master database has been running for a period of time, there is business data, and the slave database has just been initialized, then you need to back up the data of the master database, and then import the slave database to make the master and slave data consistent.

# main database create synchronous account create user 'repl'@'%' identified by' 123456 grant grant replication slave on *. * to 'repl'@'%';# full standby master database data mysqldump-uroot-pxxxx-A-R-E-single-transaction-- master-data=2 > all_db.sql# restore mysql-uroot-pxxxx from the database end

< all_db.sql# 从备份文件中可以找到主库的binlog位置 2.3 进入从库，开启主从复制 找到主库二进制文件位置且完成主从数据一致后，我们就可以正式开启主从复制了。 # 进入从库MySQL命令行 执行change master语句连接主库# 二进制文件名及pos位置由上面步骤获得CHANGE MASTER TO MASTER_HOST='MySQL主服务器IP地址', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=154;# 开启主从复制 并坚持状态start slave;show slave status \G //查看slave状态 确保Slave_IO_Running: Yes Slave_SQL_Running: Yes3.基于GTID的主从复制 GTID是MySQL 5.6的新特性，其全称是Global Transaction Identifier，可简化MySQL的主从切换以及Failover。GTID用于在binlog中唯一标识一个事务。当事务提交时，MySQL Server在写binlog的时候，会先写一个特殊的Binlog Event，类型为GTID_Event，指定下一个事务的GTID，然后再写事务的Binlog。 在基于GTID的复制中，首先从服务器会告诉主服务器已经在从服务器执行完了哪些事务的GTID值，然后主库会有把所有没有在从库上执行的事务，发送到从库上进行执行，并且使用GTID的复制可以保证同一个事务只在指定的从库上执行一次，这样可以避免由于偏移量的问题造成数据不一致。也就是说，无论是级联情况，还是一主多从的情况，都可以通过GTID自动找位置，而无需像之前那样通过File_name和File_position找主库binlog位置了。 基于GTID的主从复制与上面基于二进制文件位置的主从复制搭建步骤类似，同样简单展示下搭建过程： 3.1 确认主从库配置，开启GTID # 主库参数配置 要有以下参数vim /etc/my.cnf [mysqld] server-id = 137log-bin = binlog binlog_format = row gtid-mode = ON //开启gtid模式enforce-gtid-consistency = ON //强制gtid一致性，用于保证启动gitd后事务的安全 # 从库建议配置以下参数vim /etc/my.cnf [mysqld] server-id = 138log-bin = binlog binlog_format = row gtid-mode = ON enforce-gtid-consistency = ON relay-log = relay-bin 3.2 创建同步账号，保持主从库数据一致 若主库刚初始化完成或者主库端保留有全部二进制文件，则从库无需手动同步数据。否则需要手动同步数据使得主从一致。 # 主库创建同步账号create user 'repl'@'%' identified by '123456';grant replication slave on *.* to 'repl'@'%';# 若主库刚初始化或保留有完整二进制文件 则无需执行下面步骤# 全备主库数据mysqldump -uroot -pxxxx -A -R -E --single-transaction >

All_db.sql# recovers mysql-uroot-pxxxx from the repository < all_db.sql

3.3 enter the slave library and enable master-slave replication

# enter the MySQL command line of the slave library and execute the change master statement to connect to the IP address of the master database CHANGE MASTER TO MASTER_HOST='MySQL master server, MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_AUTO_POSITION = 1 * enable master-slave replication and stick to the status start slave;show slave status\ G at this point, the study on "what is MySQL master-slave replication" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.