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

Project environment:

System: CentOS 7.6

Database version: MySQL 5.7

Close:firewalld

Close:selinux

MySQL Master-Slave Replication Process Introduction

1. Master: A statement that records all changed data and puts it in the binlog on masert;

2. From: io thread--After using start slave, responsible for pulling binlog contents from masert, returning io thread, and finally putting them into their relay log;

3. From: sql execution thread reads relay log--executes statements in relay log.

Note: it is from the server to the master server to grab the binary log and put it into its own relay log, sql thread will always read its own relay log.

Master Library: 192.168.1.31

Prepare a library (material)

mysql> create test database; #Create a library for testing

mysql> use test; #Select test library

mysql> create table emp (empno numeric(4) not null,ename varchar(10),job varchar(9),mgr numeric(4),hiredate datetime,sal numeric(7,2),comm numeric(7,2),deptno numeric(2)); #Create a table in test

mysql> insert into emp values(7369, 'SMITH', 'CLERK', 7902, '1980-12- 17', 800, null, 20); #insert a statement

Write configuration files

[root@MySQLDB1 ~]# vim /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

socket=/usr/local/mysql/mysql.sock

#symbolic-links=0

#master and slave

log-bin=mysql-bin-master

server-id=1

binlog-do-db=test

binlog-ignore-db=mysql

[mysqld_safe]

log-error=/usr/local/mysql/logs/error.log

pid-file=/usr/local/mysql/mysql.pid

[client]

socket=/usr/local/mysql/mysql.sock

restart the database

[root@MySQLDB1 ~]# /etc/init.d/mysqld restart

Login Create user (slave from database) Grant access within database

mysql> grant replication slave on *.* to slave@192.168.1.32 identified by "123456";

mysql> flush privileges; #Refresh privileges

mysql> select * from mysql.user where user='slave'\G; #View permissions

Repl_slave_priv: Y #Copy permissions

mysql> show master status; #Check status information

+-------------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------------+----------+--------------+------------------+-------------------+

| mysql-bin-master.000004 | 601 | test | mysql | |

+-------------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

From Library: 192.168.1.32

Write a profile from the library

[root@MySQLDB2 ~]# vim /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

socket=/usr/local/mysql/mysql.sock

symbolic-links=0

server-id=2

[mysqld_safe]

log-error=/usr/local/mysql/logs/error.log

pid-file=/usr/local/mysql/mysql.pid

[client]

socket=/usr/local/mysql/mysql.sock

restart the database

[root@MySQLDB2 ~]# /etc/init.d/mysqld restart

Test login master database slave account successfully!

[root@MySQLDB2 ~]# mysql -uslave -p123456 -h292.168.1.31

mysql> exit;

Log in to your root account to synchronize

[root@MySQLDB2 ~]# mysql -uroot -p123456

mysql> stop slave; #Stop slave account first

Authorize the master to access

mysql> change master to master_host='192.168.1.31',master_port=3306,master_user='slave',master_password='123456',master_log_file='mysql-bin-master.000004',master_log_pos=601;

Open Slave User

mysql> start slave;

mysql> show slave status \G #Check whether synchronization succeeded.

Slave_IO_Running: Yes #These two words appear, indicating that the master-slave synchronization is successful

Slave_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.