MySQL主从复制

MySQL主从复制

MySQL主从复制原理

MySQL服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

主从复制架构图：

1.主节点必须启用二进制日志，记录任何修改数据库数据的事件。

2.从节点开启一个线程（I/o Thread)把自己扮演成MySQL的客户端，通过MySQL协议，请求主节点的二进制日志文件中的事件。

3.主节点启动一个线程（dump Thread)，检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。

4.从节点接收到主节点发送过来的二进制日志数据把它放置到中继日志（Relay log）文件中；并记录该次请求到主节点的具体哪个二进制日志文件的哪个位置。

5.从节点启动另外一个线程（sql Thread )，把replaylog中的事件读取出来，并在本地再执行一次。

MySQL主从复制的好处

• 实时备份，提高数据安全性，同时用于故障切换，避免影响业务。
• 读写分离（mysql-proxy），将读请求交给从服务器，实现负载分担。
• 数据库性能提高，通过增加从数据库组建集群，提高总性能。

主从配置（无数据）

需求： 搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明

数据库角色	IP地址	应用与系统版本	有无数据
主数据库	192.168.129.133	Redhat 8/ MySQL 5.7	有数据
从数据库	192.168.129.135	Redhat 7/ MySQL 5.7	无数据

新部署的主从的复制

• 安装MySQL 5.7
  • 主：

//推荐下载MySQL5.7.34的版本
[root@master ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz /usr/src
...

//创建用户和组
[root@master ~]# useradd -r -M -s /sbin/nologin mysql

//解压软件至/usr/local目录下，目录的位置可以是其他的地方
[root@master ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@master ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.34-linux-glibc2.12-x86_64  share

//创建软连接
[root@master ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
[root@master local]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 root root  46 8月  30 16:50 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 8月  30 16:48 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//修改修改目录/usr/local/mysql的属主属组
[root@master ~]# chown -R mysql.mysql /usr/local/mysql*
[root@master ~]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 mysql mysql  46 8月  30 16:50 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 8月  30 16:48 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//添加环境变量
[root@master ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master ~]# source /etc/profile.d/mysql.sh
[root@master ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

//建立数据存放目录
[root@master ~]# mkdir -p /opt/data/
[root@master ~]# chown -R mysql.mysql /opt/data/
[root@master ~]# ll /opt/data
总用量 0

//初始化数据库
[root@master ~]# mysqld --initialize --datadir=/opt/data/ --user=mysql
2021-08-30T08:58:44.064369Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-30T08:58:44.260222Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-30T08:58:44.324064Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-30T08:58:44.340907Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 7ad4fe53-0970-11ec-aba2-000c2931864c.
2021-08-30T08:58:44.341485Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-30T08:58:45.044616Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-30T08:58:45.105257Z 1 [Note] A temporary password is generated for root@localhost: i+sgbZnKA6?t
//注意这里有一个临时密码‘i+sgbZnKA6?t’一会登录的时候要用，这个临时密码是随机生成的

[root@master ~]#  echo 'i+sgbZnKA6?t' > pass

//生成配置文件
[root@master data]# vim /etc/my.cnf			#如果这个文件存在，请先备份再修改
[root@master data]# cat /etc/my.cnf 
[mysqld]
port = 3306
datadir = /opt/data/
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

//配置服务启动脚本
[root@master ~]# cp -a /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
[root@master ~]# vim /etc/init.d/mysqld 		#修改文件中的这两个地方
basedir=/usr/local/mysql
datadir=/opt/data

//启动mysql服务，并设置为开机自动启动
[root@master ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@master ~]# ss -antl|grep 3306
LISTEN    0         80                       *:3306                   *:* 
[root@master ~]# chkconfig --add mysqld
[root@master ~]# chkconfig --list

注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。 

      要列出 systemd 服务，请执行 'systemctl list-unit-files'。
      查看在具体 target 启用的服务请执行
      'systemctl list-dependencies [target]'。

mysqld          0:关    1:关    2:开    3:开    4:开    5:开    6:关

//使用临时密码登录mysql，然后修改密码
[root@master ~]# mysql -uroot -p'i+sgbZnKA6?t'
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
//如果出现这样的问题可以尝试通过yum安装ncurses-compat-libs这个库
[root@master ~]#  yum -y install ncurses-compat-libs

//设置新密码
[root@master ~]# mysql -uroot -p'i+sgbZnKA6?t'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password = password('redhat123!');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> quit
Bye

• 从：

//在master将mysql-5.7包传输到slave上
[root@master ~]# scp /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz  192.168.129.135:/usr/src/
The authenticity of host '192.168.129.135 (192.168.129.135)' can't be established.
ECDSA key fingerprint is SHA256:rp+LMeFjAIapu3y+MH6bQa8QZpl/XRlUd1PammVI9E0.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.129.135' (ECDSA) to the list of known hosts.
root@192.168.129.135's password: 
mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz               100%  635MB 102.6MB/s   00:06 

//创建用户和组
[root@slave ~]# useradd -r -M -s /sbin/nologin mysql

//解压软件至/usr/local目录下，目录的位置可以是其他的地方
[root@slave ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@slave ~]# ls /usr/local/
apr       bin  games  include  lib64    mysql-5.7.34-linux-glibc2.12-x86_64  share
apr-util  etc  httpd  lib      libexec  sbin                                 src

//创建软连接
[root@slave ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
[root@slave ~]# ll -d /usr/local/mysql*
lrwxrwxrwx 1 root root  46 8月  30 17:42 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x 9 root root 129 8月  30 17:40 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//修改修改目录/usr/local/mysql的属主属组
[root@slave ~]# chown -R mysql.mysql /usr/local/mysql*
[root@slave ~]# ll -d /usr/local/mysql*
lrwxrwxrwx 1 mysql mysql  46 8月  30 17:42 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x 9 mysql mysql 129 8月  30 17:40 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//添加环境变量
[root@slave ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@slave ~]# source /etc/profile.d/mysql.sh
[root@slave ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

//建立数据存放目录
[root@slave ~]# mkdir -p /opt/data/
[root@slave ~]# chown -R mysql.mysql /opt/data/
[root@slave ~]# ll /opt/data
总用量 0

//初始化数据库
[root@slave ~]# mysqld --initialize --datadir=/opt/data/ --user=mysql
2021-08-30T09:49:10.106673Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-30T09:49:10.290119Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-30T09:49:10.352997Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-30T09:49:10.369390Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 867c4c0f-0977-11ec-9322-000c299f4129.
2021-08-30T09:49:10.370302Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-30T09:49:11.303620Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-30T09:49:11.851213Z 1 [Note] A temporary password is generated for root@localhost: a-it4wtK&uN5
//注意这里有一个临时密码‘a-it4wtK&uN5’一会登录的时候要用，这个临时密码是随机生成的

[root@slave ~]# echo 'a-it4wtK&uN5' > pass

//生成配置文件
[root@slave ~]# vim /etc/my.cnf
[root@slave ~]# cat /etc/my.cnf
[mysqld]
port = 3306
datadir = /opt/data/
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

//配置服务启动脚本
[root@slave ~]# cp -a /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
[root@slave ~]# vim /etc/init.d/mysqld 
basedir=/usr/local/mysql
datadir=/opt/data

//启动mysql服务，并设置为开机自动启动
[root@slave ~]# service mysqld start
 Starting MySQL. SUCCESS!
[root@slave ~]# ss -antl|grep 3306
LISTEN    0         80                       *:3306                   *:*       
[root@slave ~]# chkconfig --add mysqld
[root@slave ~]# chkconfig --list

注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。 

      要列出 systemd 服务，请执行 'systemctl list-unit-files'。
      查看在具体 target 启用的服务请执行
      'systemctl list-dependencies [target]'。

mysqld          0:关    1:关    2:开    3:开    4:开    5:开    6:关

//使用临时密码登录mysql，然后修改密码
[root@slave ~]# mysql -uroot -p'a-it4wtK&uN5'
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

//如果出现这样的问题可以尝试通过yum安装ncurses-compat-libs这个库
[root@master ~]#  yum -y install ncurses-compat-libs

//设置新密码
[root@slave ~]# mysql -uroot -p'a-it4wtK&uN5'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password = password('redhat123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye

• 关闭防火墙和SElinux(两台都关闭)

[root@master ~]# systemctl disable --now firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@master ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@master ~]# setenforce 0

[root@slave ~]# systemctl disable --now firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@slave ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@slave ~]# setenforce 0

• 授权

//在主数据库上创建一个同步账号授权给从库使用
[root@master ~]# mysql -uroot -predaht123!

mysql> grant replication slave on *.* to 'hhr'@'192.168.129.135' identified by 'hhr123!';		#无需提前创建用户，执行授权的时候会自动创建用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

//检查从库是否可以登录主库
[root@slave ~]# mysql -uhhr -p'hhr123!' -h192.168.129.133
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

• 配置主数据库

[root@master ~]# vim /etc/my.cnf		#在[mysqld]这段的后面加上如下内容
log-bin = mysql_bin			#启用binlog日志
server-id = 10 				#数据库服务器的唯一标识符，主库的server-id必须比从库小

//重启MySQL服务
[root@master ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

//查看主库的状态
mysql> 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• 配置从数据库

[root@slave ~]# vim /etc/my.cnf       	#在[mysqld]这段的后面加上如下内容
server-id = 20							#数据库的唯一标识符，从库的server-id必须比主库大
relay-log = mysql_relay						#启动中继日志relay-log

//重启MySQL服务
[root@slave ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//配置并启动主从复制
[root@slave ~]# mysql -uroot -predhat123!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to
    -> master_host='192.168.129.133',		#主库的IP地址
    -> master_user='hhr',					#用于登录主库的用户
    -> master_password='hhr123!',			#用于登录主库用户的密码
    -> master_log_file='mysql_bin.000001',	#主库的binlog文件
    -> master_log_pos=154;					#请求主库binlog文件到154位置
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

//查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.129.133
                  Master_User: hhr
                  Master_Port: 3306
                Connect_Retry: 60					#连接重试的时间
              Master_Log_File: mysql_bin.000001		#主库的binlog日志
          Read_Master_Log_Pos: 154					#读取主库binlog日志到154的位置
               Relay_Log_File: mysql_relay.000002	#从库的中继日志
                Relay_Log_Pos: 320					#中继日志读取到了那个位置
        Relay_Master_Log_File: mysql_bin.000001		#从库的中继日志读取主库的binlog日志的名字
             Slave_IO_Running: Yes					#此处必须为Yes，表示I/O线程已经启动
            Slave_SQL_Running: Yes					#此处必须为Yes，表示SQL线程已经启动
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154					#执行主库binlog日志到154的位置
              Relay_Log_Space: 523
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0					#延迟
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 7ad4fe53-0970-11ec-aba2-000c2931864c
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

• 测试

//测试，在主服务器上创建数据库，然后在从服务器上查看是否会复制到从服务器
//主服务器创建数据库
mysql> create database hhr;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hhr                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

//从服务器成功复制数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hhr                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

主从配置（数据库中有数据）

主从复制配置步骤：

• 确保从数据库与主数据库里的数据一样
• 在主数据库里创建一个同步账号授权给从数据库使用
• 配置主数据库（修改配置文件）
• 配置从数据库（修改配置文件）

主数据库中有数据

• mysql安装
  master：略
  slave: 略
• 主数据库添加库与表

//创建库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use hhr;
Database changed

mysql> create table student (id int not null primary key auto_increment,name varchar(50) not null,age tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert student (name,age) values('tom',20),('jerry',20),('wnagermazi',15),('zhangsan',19),('lisi',25);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show tables;
+---------------+
| Tables_in_hhr |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
+----+------------+------+
5 rows in set (0.00 sec)

• 全备主库

//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
//此锁表的终端必须在备份完成以后才能退出

//当前数据库执行的任务
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  5 | root | localhost | hhr  | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)


//备份主库并将备份文件传送到从库
[root@master ~]# mysqldump -uroot -predhat123! --all-databases > all-$(date '+%Y%m%d').sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# ls
anaconda-ks.cfg       pass
all-20210830.sql  initial-setup-ks.cfg

[root@master ~]# scp all-20210830.sql 192.168.129.135:/root/
root@192.168.129.135's password: 
all-20210830.sql                                         100%  853KB 112.8MB/s   00:00

//在从库数据上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@slave ~]# ls
all-20210830.sql  anaconda-ks.cfg  pass

[root@slave ~]# mysql -uroot -predhat123! < all-20210830.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@slave ~]# mysql -uroot -predhat123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hhr                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

• 配置主数据库

[root@master ~]# vim /etc/my.cnf 
log-bin = mysql-bin			#启用binlog日志
server-id = 10 				#数据库服务器的唯一标识符，主库的server-id必须比从库小
log-error=/var/log/mysqld.log

//解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye
//在主数据库里创建一个同步账号授权给从数据库使用
mysql> grant replication slave on *.* to 'hhr'@'192.168.129.135'identified by 'hhr123!';
Query OK, 0 rows affected, 1 warning (4.12 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

//重启MySQL服务
[root@master ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@master ~]# ss -antl|grep 3306
LISTEN    0         80                       *:3306                   *:*   

//查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• 配置从数据库

[root@slave ~]# vim /etc/my.cnf
server-id = 20
relay-log=mysql_relay      //启用中继日志relay-log

//重启MySQL服务
[root@slave ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

//配置并启动主从复制
mysql> change master to
    -> master_host='192.168.129.133',
    -> master_user='hhr',
    -> master_password='hhr123!',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

//查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.129.133
                  Master_User: hhr
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: myrelay.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'hhr@192.168.129.133:3306' - retry-time: 60  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 210830 23:16:41
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

• 查看从服务器

//查看从数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hhr                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use hhr;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
+----+------------+------+
5 rows in set (0.00 sec)

• 测试主从是否可以正常工作

//在主数据库中的表中插入数据
mysql> use hhr;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
+----+------------+------+
4 rows in set (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_hhr |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec

mysql> delete from hhr.student where name = 'lisi';
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   20 |
|  2 | jerry    |   20 |
|  4 | zhangsan |   19 |
+----+----------+------+
3 rows in set (0.00 sec)

//查看从库中的数据是否同步成功
mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   20 |
|  2 | jerry    |   20 |
|  4 | zhangsan |   19 |
+----+----------+------+
3 rows in set (0.00 sec)