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

This article will explain in detail how the master-slave asynchronous replication in the MYSQL architecture is carried out, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

/

Primary server

From the server

Ip

192.168.31,79

192.168.31.188

Hostname

Mysql

Mysql2

Mysql-version

5.7.23

5.7.23

Server-id

0ac92118-59f9-11ea-8806-080027c1c1ff

2516faba-65f4-11ea-82b6-080027b0e953

1. Main library modifies parameter file

[root@mysql soft] # vi / etc/my.cnf

The content is added as follows:

Server-id=79 # sets the ID of the master server (cannot be duplicated with other servers, at will)

The values that can be configured for innodb_flush_log_at_trx_commit=2 # are as follows: 0Unigram 2; 0, the most efficient and the least secure; 1, the most secure, but the least efficient; 2, the balance between security and efficiency, which will lose data if the server system dies

Sync_binlog=1 #, which can be set to 1,500,1000, and can be configured according to the test performance

Log-bin=mysql-bin # binlog log file name

Binlog-ignore-db=mysql # indicates that the mysql library is not synchronized

Binlog-ignore-db=information_schema

Binlog-ignore-db=oggmysql

Binlog-ignore-db=sys

Binlog-ignore-db=performance_schema

Binlog-do-db=TEST # means to synchronize TEST libraries

two。 Create a synchronization account in the main database

[root@mysql] # mysql-uroot-p

Mysql > create user 'sync'@'%' identified by' Oracle123'

Mysql > grant FILe on *. * to 'sync'@'192.168.31.188' identified by' Oracle123'

# Grant FILE permission to access the master library from the slave ip

Mysql > grant replication slave on *. * to 'sync'@'192.168.31.188' identified by' Oracle123'

# Grant master-slave synchronization permissions

Mysql > flush privileges

3. Restart the main library

[root@mysql ~] # service mysqld stop

[root@mysql ~] # service mysqld start

[root@mysql] # mysql-uroot-p

Mysql > show master status

Note: the FILE,POSTTION in the above screenshot needs to be used when preparing the library configuration.

4. Main database backup database

[root@mysql soft] # mysqldump-- single-transaction-uroot-pOracle123 TEST > TEST_20200310.sql

5. Restore the database from the library

Mysql > create database TEST

[root@mysql2 soft] # mysql-uroot-pOracle123 TEST

< TEST_20200310.sql 6.从库参数修改 [root@mysql2 mysql]# vi /etc/my.cnf 添加内容如下： server-id=188 #log-bin=mysql-bin #从库提高性能可以不开bin-log日志 replicate-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=oggmysql binlog-ignore-db=sys binlog-ignore-db=performance_schema binlog-do-db=TEST innodb_flush_log_at_trx_commit=2 # 可以配置的值：0/1/2; 0、效率最高，最不安全；1、最安全，但是效率最低；2、安全和效率平衡的取舍，在服务器系统挂掉的情况下会丢失数据； sync_binlog=1000 # 每进行n次事务提交之后，MySQL将binlog_cache中的数据强制写入磁盘。 slave_parallel_workers=4 #根据实际情况决定开启多少个线程用于主从复制 slave_parallel_type=logical_clock #基于组提交的并行复制方式 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON 7. 从库重启 [root@mysql2 data]# service mysql stop [root@mysql2 data]# service mysql start 8.从库配置 mysql>

Stop slave

Mysql > change master to master_host='192.168.31.79', master_user='sync',master_password='Oracle123', master_log_file='mysql-bin.00001', master_log_pos=154

Mysql > start slave

Mysql > show slave status\ G

9. Verification

1) insert data into the main database

Mysql > insert into TEST values (7 million Liuqi')

2) prepare database query

Mysql > select * from TEST

On the architecture of MYSQL master-slave asynchronous replication is how to share here, I hope the above content can be of some help to you, can 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.