Principle of master-slave replication

1. The Master records the data changes in the binary log, that is, the file specified in the configuration file log bin,
   These records are called binary log events
2. Slave reads binary log events in the Master through I/O threads and writes them to its relay log
3. Slave redoes the events in the relay log and executes the event information in the relay log one by one locally
   The data is stored locally, so as to reflect the changes to its own data (data replay)

matters needing attention

1. The operating system version and number of bits of the master-slave server are consistent
2. The versions of Master and Slave databases should be consistent
3. The data in the Master and Slave databases should be consistent
4. The Master starts the binary log, and the Master and Slave servers_ ID must be unique in LAN

Master slave replication configuration steps

Configuration on Master

1. mount this database
2. Modify the database configuration file to indicate the server_id, enable binary log (log bin)
3. Start the database and check the current log and position number
4. Log in to the database and authorize the data replication user (the IP address is the slave IP address. If it is a two-way master-slave, here you also need to authorize the local IP address. At this time, your own IP address is the slave IP address)
5. Back up the database (remember to lock and unlock)
6. Transfer backup data to Slave
7. Start database

Configuration on Slave

1. Install database;
2. Modify the database configuration file to indicate the server_id (if you are building a two-way master-slave, you should also turn on binary
   Log bin);
3. Start the database and restore the backup;
4. Check the current log and position number (one-way master-slave is not required, two-way master-slave is required);
5. Specify the address, user, password and other information of the Master;
6. Turn on synchronization to view the status

Install MySQL 8

Download address:
http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar
Decompression:
tar -xf mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar
Uninstall the system's own Mariadb:
rpm -qa | grep mariadb
RPM - E -- Mariadb found by nodeps
Uninstall MySql from the system:
rpm -qa | grep mysql
RPM - E -- mysql found by nodeps
Installation:
rpm -Uvh *.rpm --nodeps --force
MySQL 8 common configurations:

[client]
default-character-set=utf8
socket=/home/mysql/data/mysql.sock

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
max_connections=5000
expire_logs_days=7
lower_case_table_names=1
datadir=/home/mysql/data
socket=/home/mysql/data/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

[mysql]
default-character-set=utf8

Master slave replication configuration

Enable binlog on the master node

vi /etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1

Specifies that the slave node is globally unique

vi /etc/my.cnf

[mysqld]
server-id=2

Start MySql

systemctl start mysqld
systemctl status mysqld

Turn on remote login

View initial password:

grep 'temporary password' /var/log/mysqld.log

Reset password:
Modify the password to match the rules first:

ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Konglcwqy123@';

Modify password policy again:

set global validate_password.policy=LOW;
set global validate_password.policy=0;
set global validate_password.length=4;
ALTER USER 'user name'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'New password';

Turn on remote login:

create user 'user name'@'%' identified by 'password';
grant all on *.* to 'user name'@'%';

Sign in:

mysql -uroot -p

Set up remote login

The master node creates users for master-slave replication

mysql> CREATE USER 'user name'@'Slave node address' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'user name'@'Slave node address';
mysql> flush privileges;

Gets the location of the primary node binlog

mysql> SHOW MASTER STATUS;

Set the information of the master node at the slave node

mysql> CHANGE MASTER TO
MASTER_HOST='Master node address',
MASTER_USER='Master slave copy user name',
MASTER_PASSWORD='Master slave copy password',
MASTER_LOG_FILE='Query above File',
MASTER_LOG_POS=Query above Position;

View master-slave synchronization status

mysql> show slave status\G;

Turn on master-slave synchronization

mysql>start slave;

View status again

mysql> show slave status\G;