MySQL builds a master-slave project
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
