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

This article mainly explains the use of MySQL master-slave replication, the content is clear, interested friends can learn, I believe it will be helpful after reading.

Brief introduction

The master-slave replication of MySQL is also called Replication and AB replication. At least two MySQL services are required (either on the same machine or between different machines).

For example, A server as master server, B server as slave server, update data on A server, synchronize to B server through binlog log records, and re-execute synchronized binlog data, so that the data of the two servers are consistent.

The master-slave replication scheme of MySQL database, similar to file-level replication using commands such as scp/rsync, is the remote transfer of data.

However, the master-slave replication of MySQL is its own function without the help of third-party tools, and the master-slave replication of MySQL is not a direct copy of the files on the database disk, but is copied to the local server through the logical binlog log, and then the SQL statement in the log is read by the local thread and reapplied to the MySQL database.

Action

1. Can be used for real-time disaster recovery for failover

two。 Separate read and write, provide query service, and realize load balancing

3. Hot data backup to avoid affecting the business.

Principle

1. The master server MySQL service records all writes in the binlog log, generates a log dump thread, and passes the binlog log to the Imando O thread of the slave server MySQL service.

2. Two threads are generated from the server MySQL service, one is the Igamot O thread and the other is the SQL thread.

3. The binlog log of the main library is requested from the library I _ handle O thread, and the files in the binlog log are written to relaylog (Relay Log).

4. The SQL thread of the slave database will read the contents of the relaylog and parse them into specific operations to achieve the consistency of master-slave operations and the final consistency of data between the two databases.

Note:-Master-slave replication is an asynchronous logical SQL statement-level replication;-when replicating, the master database has one Imax O thread, the slave database has two threads, and the Imax O and SQL threads;-the necessary condition for master-slave replication is that the master database enables the function of recording binlog;-the server-id of all replicated MySQL nodes cannot be the same. The-binlog file records only SQL statements that have changed the content of the data, not any query statements. Form one master and one slave

Master master replication

One master and many followers

Multi-master and one-slave (supported after 5.7)

Cascade replication

Practical requirements to realize one master and one slave replication mode Two MySQL instance environments of the same host Mac:10.15.1Docker:2.0.0.3// adopt docker to install MySQL, mainly for convenient management and maintenance, independent IP, Start the second MySQL-master:5.7.29// master server MySQL-master IP:172.17.0.3MySQL-slave:5.7.29// slave server MySQL-slave IP:172.17.0.4 step 1: prepare two MySQL servers mysql-master (master server): 1. Create directory structure: master/conf, master/data, master/logs2. Start and run the instance container: docker run-- name mysql-master >-p 3310 docker/master/conf:/etc/mysql/conf.d 3306 >-v / docker/master/conf:/etc/mysql/conf.d >-v / docker/master/data:/var/lib/mysql >-v / docker/master/logs:/var/log/mysql >-e MYSQL_ROOT_PASSWORD=123456 >-d mysql:5.73. Enter the container docker exec-it mysql-master bash4. Log in to MySQLmysql-uroot-pmysql-slave (slave server): 1. Create directory structure: slave/conf, slave/data, slave/logs2. Start and run the instance container: docker run-- name mysql-slave >-p 3310 docker/slave/conf:/etc/mysql/conf.d 3306 >-v / docker/slave/conf:/etc/mysql/conf.d >-v / docker/slave/data:/var/lib/mysql >-v / docker/slave/logs:/var/log/mysql >-e MYSQL_ROOT_PASSWORD=123456 >-d mysql:5.73. Enter the container docker exec-it mysql-slave bash4. Log in to MySQLmysql-uroot-p step 2: configure file (my.cnf) modify master server: [mysqld] port = 3306server-id = databases to be synchronized binlog-do-db = school# to generate binary log files master server must be turned on log-bin = mysql-bin restart MySQL: docker restart mysql-master slave server: [mysqld] port = 3306server-id = databases to be synchronized binlog-do-db = school# To generate a binary log file (optional from the slave server) log-bin = mysql-bin restart MySQL: docker restart mysql-slave step 3: create a master server to replicate users and related permissions create user 'slave'@'%' identified by' 123456' / / create user grant replication slave,replication client on *. * to 'slave'@'%';// set user rights flush privileges;// refresh permissions show grants for' slave'@'%';// view user rights step 4: data backup synchronization 1. Log in to master and perform the table lock operation mysql-uroot-pFLUSH TABLES WITH READ LOCK;2. Dump the db data that needs to be synchronized in master mysqldump-uroot-p school > school.dump3. Import data into slavemysql-uroot-h272.17.0.4-p school

< school.dump4. 解锁masterUNLOCK TABLES;第五步：主服务器复制状态1. 创建新数据表及增加数据create table user( id int(10) auto_increment, name varchar(30), primary key (id) )charset=utf8mb4;insert into user(name) values(222);2. 主服务器 binlog 记录状态mysql>

Show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000001 | 769 | school | +- -+

Step 6: copy 1 from the server. Connect to the master server and set the starting node of replication mysql > change master to master_host='172.17.0.3',- > master_port=3306,- > master_user='slave',- > master_password='123456',- > master_log_file='mysql-bin.000001',- > master_log_pos=769;2. Start copying start slave;3. Check the replication status mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 172.17.0.3Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 961Relay_Log_File: 87dc5224655d-relay-bin.000003Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: Yes / / indicates that the Slave_SQL_Running O thread read successfully: Yes / / indicates that the SQL thread executed successfully Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 961Relay_Log_Space: 892Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_ Server_Id: 1Master_UUID: 45540733-4e0c-11ea-b0ac-0242ac110003Master_Info_File: / var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log Waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:4. View the data table data mysql > show create table user\ gateway * 1. Row * * Table: userCreate Table: CREATE TABLE `user` (`id` int (10) NOT NULL AUTO_INCREMENT, `name` varchar (30) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4mysql > select * from user +-+-- +-+ | id | name | +-+-+ | 1 | 222nd | +-+-- +-+ commands related to replication control: stop salve / / stop slave connection reset slave / / reset slave connection start slave / / start slave connection stop master / / stop master connection reset master / / reset master connection start master / / Open master connection step 7: view the process of the master slave server:

Mysql-slave:

After reading the above content, do you have a further understanding of the use of MySQL master-slave replication? if you want to learn more, you are welcome to follow the industry information channel.

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.