1. Mysql master-slave replication principle

1. The master records the operation statement into the binlog log, and then grants the remote connection permission to the slave (the master must enable the binlog binary log function; usually for data security considerations, the slave also enables the binlog function).

2. The slave starts two threads: IO thread and SQL thread. Among them: the IO thread is responsible for reading the binlog content of the master into the relay log; the SQL thread is responsible for reading the binlog content from the relay log and updating it to the slave database, so that the slave data and the master data can be kept agreed.

3. Mysql replication requires at least two Mysql services. Of course, Mysql services can be distributed on different servers, or multiple services can be started on one server.

4. For Mysql replication, it is best to ensure that the Mysql versions on the master and slave servers are the same (if the version consistency cannot be satisfied, then ensure that the version of the master master node is lower than the version of the slave slave node)

5. The time between master and slave nodes needs to be synchronized

Two, Centos7 install Mysql

1. Check the installation environment

1.1. Check whether mysql has been installed

	[root@localhost ~]# rpm -qa | grep mysql
	#If there is, use the rm -rf command to delete
	[root@localhost ~]# whereis mysql
	mysql: /usr/lib64/mysql /usr/share/mysql
	[root@localhost ~]# rm -rf /usr/lib64/mysql /usr/share/mysql #delete
	[root@localhost ~]# find / -name mysql
	/etc/selinux/targeted/active/modules/100/mysql
	[root@localhost ~]# rm -rf /etc/selinux/targeted/active/modules/100/mysql #delete

1.2. Check whether there is a mysql user

If the user and user group already exist, skip this step, if not, create it

[root@localhost ~]# id mysql
 id: mysql: no such user
[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd -r -g mysql mysql

1.3. Check whether the library file is installed

[root@localhost ~]# rpm -qa|grep libaio
libaio-0.3.109-13.el7.x86_64  #I have already installed it here, if there is no output, you need to install it with yum

yum install  libaio-devel.x86_64
# 
yum -y install numactl

2. Download address

https://dev.mysql.com/downloads/mysql/

After the download is complete, use the rz command to upload to the /soft/ directory

3. Installation

[root@localhost local]# tar -xvf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz  #decompress
[root@localhost local]# mv mysql-8.0.27-linux-glibc2.12-x86_64 mysql-8.0.27   #Rename
#Modify the user of all files in the mysql directory to be root and the group to be root
[root@localhost local]# chown -R root:root /soft/mysql-8.0.27/ `insert code slice here`
#Create a data storage directory to store database data
[root@localhost local]# mkdir data
#Modify the user and group to which the data storage directory belongs to mysql
[root@localhost local]# chown mysql:mysql /soft/mysql-8.0.27/data

4. Initialize the database

Please confirm that /etc/my.cnf does not exist before initialization, otherwise mysql will read the configuration file as initialization by default during initialization.
If the file exists, rename it as follows and keep it as a backup

[root@localhost mysql]# ll /etc/ | grep my.cnf
-rw-r--r--.  1 root root      570 Jun  8  2017 my.cnf
drwxr-xr-x.  2 root root       31 Dec 20 17:32 my.cnf.d

[root@localhost mysql]# mv /etc/my.cnf /etc/my.cnf.bak
[root@localhost mysql]# ll /etc/ | grep my.cnf
-rw-r--r--.  1 root root      570 Jun  8  2017 my.cnf.bak
drwxr-xr-x.  2 root root       31 Dec 20 17:32 my.cnf.d

#start initialization
[root@localhost mysql]# /soft/mysql-8.0.27/bin/mysqld --initialize --user=mysql  --basedir=/soft/mysql-8.0.27 --datadir=/soft/mysql-8.0.27/data

Note: Remember to save the initialization password! ! !

Record BUG!
Starting MySQL... ERROR! The server quit without updating PID file (/soft/mysql-8.0.27/data/mysql.pid).

Solution:
permissions
chmod -R 755 /soft/mysql-8.0.27/data
check process and kill
ps -ef|grep mysqld
kill -9 ****
restart mysql
service mysql start

5. Modify the configuration

#Enter the support-files file in the MySQL installation directory, create the mysql-default.cnf file and add permissions
[root@localhost mysql]# cd /soft/mysql-8.0.27/support-files/
[root@localhost support-files]# touch mysql-default.cnf
[root@localhost support-files]# chmod 777 mysql-default.cnf

#Copy it to /etc/ and rename it to my.cnf
[root@localhost support-files]# cp -a mysql-default.cnf /etc/my.cnf

vi /etc/my.cnf
#Add configuration in my.cnf
[mysqld]
basedir = /soft/mysql-8.0.27
datadir = /soft/mysql-8.0.27/data
socket = /tmp/mysql.sock
log-error = /soft/mysql-8.0.27/data/error.log
pid-file = /soft/mysql-8.0.27/data/mysql.pid
port = 3306
max_allowed_packet=32M

#Modify configuration file permissions
chmod 644 /etc/my.cnf

6. Set the boot to start automatically

#Copy the mysql service file to the /etc/init.d/ directory and rename it to mysql
[root@localhost support-files]# cp -a /soft/mysql-8.0.27/support-files/mysql.server /etc/init.d/mysql
#Add executable permissions
[root@localhost support-files]# chmod +x /etc/init.d/mysql

#registration service
chkconfig --add mysql  #register
chkconfig --list mysql  #detection

#/etc/ld.so.conf must configure the path, otherwise an error will be reported
[root@localhost support-files]# vim /etc/ld.so.conf
#Add the lib file of mysql in ld.so.conf
/soft/mysql-8.0.27/lib

#Configure environment variables
[root@localhost support-files]# vim /etc/profile
#Add at the end:
export PATH=$PATH:/soft/mysql-8.0.27/bin:/soft/mysql-8.0.27/lib
#Reload to make the environment variable take effect
[root@localhost support-files]# source /etc/profile

#start service
[root@localhost support-files]# service mysql start

7. Modify the default password

#Login with initial password
[root@localhost support-files]# mysql -uroot -p

If you forget the initial password:

https://zhuanlan.zhihu.com/p/458845050

# change Password
alter user 'root'@'localhost' identified by 'root';

8. Open remote connection

mysql> use mysql;
mysql> update user set host = '%' where user = 'root';
mysql> flush privileges;
mysql> GRANT ALL ON *.* TO 'root'@'%';
mysql> flush privileges;

#firewall settings
#Temporarily turn off the firewall
systemctl stop firewalld
#Open the specified port number
firewall-cmd --zone=public --add-port=3306/tcp --permanent
#remove open ports
firewall-cmd --zone=public --remove-port=3309/tcp --permanent
#Enable firewall settings to take effect
firewall-cmd --reload

3. Data synchronization

We need a master node, which is the master database Mysql (provides the update log for the slave database)

1. The master node opens the binary log

#Master node configuration my
#Add the following code in /etc/my.cnf
#Enable binary logging
log_bin=master_bin
#Set the global unique ID of the current node (set by yourself)
server-id=140

#Restart the mysql service
service mysql start
#See the file and location of the binary log to start copying from
mysql> show master status;

2. Create a user account with replication permissions in the master node library

create user repluser@'10.70.20.%' identified by 'password';
mysql> grant replication slave on *.* to repluser@'10.70.20.%';

3. Slave node configuration

#Set a globally unique ID number for the current node
server_id=98
#Enable binary logging
log-bin=slave_log
#Set read-only from the database (not valid for root users)
read_only=ON
#The file path of the relay log
relay_log=relay-log
#Default value hostname-relay-bin.index
relay_log_index=relay-log.index

2. Use a user account with replication permission to connect to the master server from the node and start the replication thread

mysql> change master to master_host='master node ip',master_port=3306,master_user='rep_username',master_password='password',master_log_file='master_bin.000001',MASTER_LOG_POS= 1827,get_master_public_key=140;

#rep_username: The username of the new user just registered by the host

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

The following figure shows success