MySQL replication environment setup

Welcome to follow the public account: Yijie IT
The blog posts on this site are published on the public account first.
Alt

Article directory

    • 1 Environment preparation
    • 2. Mysql8.0 installation
      • 2.2.1 Obtain rpm package
      • 2.2.2 Install and configure MySQL Server
        • 2.2.1 Install database
        • 2.2.2 Initialize database
        • 2.2.3 Remote login
        • 2.2.4 Compatible with old version verification
    • 3 firewall
    • 4. Disable Selinux
    • 5. Hosts

This article uses yum to deploy the replication environment of the Mysql database. For other methods, please refer to [
MySQL environment deployment (5.7 + 8.0) (Win + Linux)].

1 Environment preparation

Prepare the number of servers based on one master and one slave or multiple slaves.

MySQL needs to be installed on each virtual machine, and the master and slave database versions must be consistent.

Hostname IP OS mysql version
mysql80-master 192.168.2.80 Centos 7.8 Mysql 8.0.31
mysql80-slave01 192.168.2.81 Centos 7.8 Mysql 8.0.31
mysql80-slave02 192.168.2.82 Centos 7.8 Mysql 8.0.31

Note: I talked about how to clone a CentOS before. You can install MySQL on a CentOS and then clone a virtual machine containing MySQL.

Note: The cloning method requires modifying the newly cloned host: 1.MAC address 2.hostname 3.IP address 4. UUID.

In addition, for virtual machines (including MySQL Server) generated by cloning, the UUID of the cloned virtual machine MySQL Server is the same and must be modified, otherwise an error will be reported in some scenarios. For example, the following error is reported:

mysql> show slave status\G
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have
equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Modify the UUID method of MySQL Server:

vim /var/lib/mysql/auto.cnf
systemctl restart mysqld

2. Mysql8.0 installation

2.2.1 Obtain rpm package

Install online through yum.

Obtain the latest rpm package according to https://dev.mysql.com/downloads/repo/yum.

# Download and install the rpm package of MySQL. As of now, the rpm version is el7-7
wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
yum -y localinstall mysql80-community-release-el7-7.noarch.rpm

2.2.2 Install and configure MySQL Server

Install mysql server on three server nodes.

2.2.1 Install database
yum install -y mysql-community-server
2.2.2 Initialize database
# Initialization password
[root@mysql80-01 ~]# mysqld --initialize --user=mysql
[root@mysql80-01 ~]# cat /var/log/mysqld.log
2022-10-08T15:29:20.937483Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.30) initializing of server in progress as process 55360
2022-10-08T15:29:20.966525Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-08T15:29:21.362763Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-10-08T15:29:23.216780Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: #uv._rfYp4T4
#Log in for the first time, start the service, log in and enter the temporary password
[root@mysql80-01 ~]# systemctl start mysqld
[root@mysql80-01 ~]# mysql -uroot -p
#change Password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.01 sec)
2.2.3 Remote login
# If remote login is not set, the management server login node will report an error.
Shell.connect: Host '192.168.2.80' is not allowed to connect to this MySQL server (MySQL Error 1130)

#Allow remote login
use mysql;
select Host,User from user;
update user set host = '%' where user ='root';
flush privileges;
#`%` is a wildcard character. If Host=192.168.1.%, it means that any client with an IP address prefix of "192.168.1." can connect.
# If `Host=%`, it means that all IPs have connection permission. Production disabled.
2.2.4 compatible with old version verification

When using an old version of mysql client to connect to the mysql 8 server, the following error may be reported:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /home/mysql/mysql-5.6.14/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

There are two methods to solve this problem.

  • The first method is to set default_authentication_plugin='mysql_native_password' in the configuration file, and then restart the mysql server to make it take effect. default_authentication_plugin is a read-only system variable and cannot be modified dynamically.
  • The second method is to use an authentication method that is compatible with both old and new versions when creating a user, for example:
create user 'slave'@'%' identified with mysql_native_password by 'slave';
flush privileges;

The authentication plug-in corresponding to the user can be obtained through the following query:

mysql> select host,user,plugin from mysql.user;
 + ----------- + ------------------ + ------------------ ----- +
| host | user | plugin |
 + ----------- + ------------------ + ------------------ ----- +
| % | slave | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
 + ----------- + ------------------ + ------------------ ----- +
5 rows in set (0.00 sec)

3 Firewall

# 1. Turn off the firewall on each node
systemctl stop firewalld
systemctl disable firewalld
# 2. Or release port
# Check open port numbers
    firewall-cmd --list-all
    #Set the open port number
    firewall-cmd --add-service=http --permanent
    firewall-cmd --add-port=3306/tcp --permanent
    # Restart firewall
    firewall-cmd --reload

4. Disable Selinux

#Disable Selinux on all hosts
[root@mysql80-master ~]# cat /etc/sysconfig/selinux
SELINUX=disabled

5. Hosts

#Add hosts resolution to all hosts
[root@mysql80-master ~]# cat /etc/hosts
....
192.168.2.80 mysql80-master
192.168.2.81 mysql80-slave01
192.168.2.82 mysql80-slave01

Welcome to follow the public account: Yijie IT
The blog posts on this site are published on the public account first.
Alt