CentOS7.5下安装Mysql 8.0并配置主从复制

最新推荐文章于 2023-10-09 20:58:57 发布

嘉禾嘉宁papa

最新推荐文章于 2023-10-09 20:58:57 发布

阅读量4.3k

点赞数 1

分类专栏： MySQL数据库文章标签： mysql8.0主从复制 centos7安装mysql mysql主从复制搭建

本文链接：https://blog.csdn.net/Alian_1223/article/details/129481005

版权

MySQL数据库专栏收录该内容

11 篇文章 1 订阅

订阅专栏

一、系统准备

1.1、防火墙

首先检查centos 7里面的防火墙，我这里是虚拟机，所以我为了方便就把防火墙给关了，实际工作中肯定是去配置防火墙开关端口。

// 并且把防火墙给关了（或者配置一下3306端口）
systemctl stop firewalld.service
// 设置防火墙开机自动关闭
systemctl disable firewalld.service

1.2、卸载mysql（如有）

首先检查centos 7里面的Mysql安装包和依赖包

[root@localhost ~]# rpm -qa |grep mysql
mysql-community-common-8.0.32-1.el7.x86_64
mysql-community-client-8.0.32-1.el7.x86_64
mysql-community-server-8.0.32-1.el7.x86_64
mysql80-community-release-el7-3.noarch
mysql-community-client-plugins-8.0.32-1.el7.x86_64
mysql-community-libs-8.0.32-1.el7.x86_64
mysql-community-icu-data-files-8.0.32-1.el7.x86_64
mysql-community-libs-compat-8.0.32-1.el7.x86_64

然后卸载，我只一次卸载如下几个就都卸载完了

[root@localhost ~]# yum remove mysql-community-common-8.0.32-1.el7.x86_64
[root@localhost ~]# yum remove mysql80-community-release-el7-3.noarch
[root@localhost ~]# yum remove mysql-community-client-plugins-8.0.32-1.el7.x86_64
[root@localhost ~]# yum remove mysql-community-icu-data-files-8.0.32-1.el7.x86_64

继续检查一下是否存在Mariadb，若是存在直接删除Mariadb

[root@localhost ~]# rpm -qa |grep mariadb

如果有就

rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

删除Mysql的配置文件，可以使用下面的命令查找Msqyl配置文件的路径

[root@localhost ~]# find / -name mysql
/var/lib/mysql
/var/lib/mysql/mysql
/usr/lib64/mysql
[root@localhost ~]# rm -rf /var/lib/mysql
[root@localhost ~]# rm -rf /var/lib/mysql/mysql
[root@localhost ~]# rm -rf /usr/lib64/mysql
[root@localhost ~]#

1.3、yum源

如果没有 wget 就先安装一个

yum install wget

进入到yum源的目录，备份原yum源，然后配置成阿里云的yum源，然后通过 wget 命令下载Mysql 8的repo源，并且执行安装。

#创建一个备份目录
mkdir /etc/yum.repos.d/backup

#把所有的repo文件都移动到backup目录
mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/backup

# 配置阿里的源（我这里是CentOS7.5所以用的Centos-7.repo）
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

# 使用wget命令下载Mysql 8的repo源，并且执行安装
wget https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
sudo yum -y install mysql80-community-release-el7-3.noarch.rpm

安装完后会在 /etc/yum.repos.d/ 目录下生成最下面的两个文件，说明安装成功了：

[root@localhost yum.repos.d]# ll
总用量 40
drwxr-xr-x. 2 root root   220 3月  12 09:42 backup
-rw-r--r--. 1 root root  2523 8月   4 2022 CentOS-Base.repo
-rw-r--r--. 1 root root 26024 4月  24 2019 mysql80-community-release-el7-3.noarch.rpm
-rw-r--r--. 1 root root  2076 4月  24 2019 mysql-community.repo
-rw-r--r--. 1 root root  2108 4月  24 2019 mysql-community-source.repo

清除缓存，并更新yum源

# 更新yum源
yum clean all
yum makecache
# 查看yum仓库中的Mysql
yum list | grep mysql

二、密钥

2.1、地址

https://dev.mysql.com/doc/refman/8.0/en/checking-gpg-signature.html

2.2、密钥

mysql_pubkey.asc

-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: SKS 1.1.6
Comment: Hostname: pgp.mit.edu

mQINBGG4urcBEACrbsRa7tSSyxSfFkB+KXSbNM9rxYqoB78u107skReefq4/+Y72TpDvlDZL
mdv/lK0IpLa3bnvsM9IE1trNLrfi+JES62kaQ6hePPgn2RqxyIirt2seSi3Z3n3jlEg+mSdh
AvW+b+hFnqxo+TY0U+RBwDi4oO0YzHefkYPSmNPdlxRPQBMv4GPTNfxERx6XvVSPcL1+jQ4R
2cQFBryNhidBFIkoCOszjWhm+WnbURsLheBp757lqEyrpCufz77zlq2gEi+wtPHItfqsx3rz
xSRqatztMGYZpNUHNBJkr13npZtGW+kdN/xu980QLZxN+bZ88pNoOuzD6dKcpMJ0LkdUmTx5
z9ewiFiFbUDzZ7PECOm2g3veJrwr79CXDLE1+39Hr8rDM2kDhSr9tAlPTnHVDcaYIGgSNIBc
YfLmt91133klHQHBIdWCNVtWJjq5YcLQJ9TxG9GQzgABPrm6NDd1t9j7w1L7uwBvMB1wgpir
RTPVfnUSCd+025PEF+wTcBhfnzLtFj5xD7mNsmDmeHkF/sDfNOfAzTE1v2wq0ndYU60xbL6/
yl/Nipyr7WiQjCG0m3WfkjjVDTfs7/DXUqHFDOu4WMF9v+oqwpJXmAeGhQTWZC/QhWtrjrNJ
AgwKpp263gDSdW70ekhRzsok1HJwX1SfxHJYCMFs2aH6ppzNsQARAQABtDZNeVNRTCBSZWxl
YXNlIEVuZ2luZWVyaW5nIDxteXNxbC1idWlsZEBvc3Mub3JhY2xlLmNvbT6JAlQEEwEIAD4W
IQSFm+jXxYb1OEMLGcJGe5QtOnm9KQUCYbi6twIbAwUJA8JnAAULCQgHAgYVCgkICwIEFgID
AQIeAQIXgAAKCRBGe5QtOnm9KUewD/992sS31WLGoUQ6NoL7qOB4CErkqXtMzpJAKKg2jtBG
G3rKE1/0VAg1D8AwEK4LcCO407wohnH0hNiUbeDck5x20pgS5SplQpuXX1K9vPzHeL/WNTb9
8S3H2Mzj4o9obED6Ey52tTupttMF8pC9TJ93LxbJlCHIKKwCA1cXud3GycRN72eqSqZfJGds
aeWLmFmHf6oee27d8XLoNjbyAxna/4jdWoTqmp8oT3bgv/TBco23NzqUSVPi+7ljS1hHvcJu
oJYqaztGrAEf/lWIGdfl/kLEh8IYx8OBNUojh9mzCDlwbs83CBqoUdlzLNDdwmzu34Aw7xK1
4RAVinGFCpo/7EWoX6weyB/zqevUIIE89UABTeFoGih/hx2jdQV/NQNthWTW0jH0hmPnajBV
AJPYwAuO82rx2pnZCxDATMn0elOkTue3PCmzHBF/GT6c65aQC4aojj0+Veh787QllQ9FrWbw
nTz+4fNzU/MBZtyLZ4JnsiWUs9eJ2V1g/A+RiIKu357Qgy1ytLqlgYiWfzHFlYjdtbPYKjDa
ScnvtY8VO2Rktm7XiV4zKFKiaWp+vuVYpR0/7Adgnlj5Jt9lQQGOr+Z2VYx8SvBcC+by3XAt
YkRHtX5u4MLlVS3gcoWfDiWwCpvqdK21EsXjQJxRr3dbSn0HaVj4FJZX0QQ7WZm6WLkCDQRh
uLq3ARAA6RYjqfC0YcLGKvHhoBnsX29vy9Wn1y2JYpEnPUIB8X0VOyz5/ALv4Hqtl4THkH+m
mMuhtndoq2BkCCk508jWBvKS1S+Bd2esB45BDDmIhuX3ozu9Xza4i1FsPnLkQ0uMZJv30ls2
pXFmskhYyzmo6aOmH2536LdtPSlXtywfNV1HEr69V/AHbrEzfoQkJ/qvPzELBOjfjwtDPDeP
iVgW9LhktzVzn/BjO7XlJxw4PGcxJG6VApsXmM3t2fPN9eIHDUq8ocbHdJ4en8/bJDXZd9eb
QoILUuCg46hE3p6nTXfnPwSRnIRnsgCzeAz4rxDR4/Gv1Xpzv5wqpL21XQi3nvZKlcv7J1IR
VdphK66De9GpVQVTqC102gqJUErdjGmxmyCA1OOORqEPfKTrXz5YUGsWwpH+4xCuNQP0qmre
Rw3ghrH8potIr0iOVXFic5vJfBTgtcuEB6E6ulAN+3jqBGTaBML0jxgj3Z5VC5HKVbpg2DbB
/wMrLwFHNAbzV5hj2Os5Zmva0ySP1YHB26pAW8dwB38GBaQvfZq3ezM4cRAo/iJ/GsVE98dZ
EBO+Ml+0KYj+ZG+vyxzo20sweun7ZKT+9qZM90f6cQ3zqX6IfXZHHmQJBNv73mcZWNhDQOHs
4wBoq+FGQWNqLU9xaZxdXw80r1viDAwOy13EUtcVbTkAEQEAAYkCPAQYAQgAJhYhBIWb6NfF
hvU4QwsZwkZ7lC06eb0pBQJhuLq3AhsMBQkDwmcAAAoJEEZ7lC06eb0pSi8P/iy+dNnxrtiE
Nn9vkkA7AmZ8RsvPXYVeDCDSsL7UfhbS77r2L1qTa2aB3gAZUDIOXln51lSxMeeLtOequLME
V2Xi5km70rdtnja5SmWfc9fyExunXnsOhg6UG872At5CGEZU0c2Nt/hlGtOR3xbt3O/Uwl+d
ErQPA4BUbW5K1T7OC6oPvtlKfF4bGZFloHgt2yE9YSNWZsTPe6XJSapemHZLPOxJLnhs3VBi
rWE31QS0bRl5AzlO/fg7ia65vQGMOCOTLpgChTbcZHtozeFqva4IeEgE4xN+6r8WtgSYeGGD
RmeMEVjPM9dzQObf+SvGd58u2z9f2agPK1H32c69RLoA0mHRe7Wkv4izeJUc5tumUY0e8Ojd
enZZjT3hjLh6tM+mrp2oWnQIoed4LxUw1dhMOj0rYXv6laLGJ1FsW5eSke7ohBLcfBBTKnMC
BohROHy2E63Wggfsdn3UYzfqZ8cfbXetkXuLS/OM3MXbiNjg+ElYzjgWrkayu7yLakZx+mx6
sHPIJYm2hzkniMG29d5mGl7ZT9emP9b+CfqGUxoXJkjs0gnDl44bwGJ0dmIBu3ajVAaHODXy
Y/zdDMGjskfEYbNXCAY2FRZSE58tgTvPKD++Kd2KGplMU2EIFT7JYfKhHAB5DGMkx92HUMid
sTSKHe+QnnnoFmu4gnmDU31i
=Xqbo
-----END PGP PUBLIC KEY BLOCK-----

创建文件 mysql_pubkey.asc ，内容就是上面的密钥，我这里密钥放的路径是用户根目录，比如我这里用的root安装mysql，就在 /root/ 目录下

[root@localhost ~]# ll
总用量 36
-rw-------. 1 root root  1393 3月  12 05:33 anaconda-ks.cfg
-rw-r--r--. 1 root root 26024 4月  24 2019 mysql80-community-release-el7-3.noarch.rpm
-rw-r--r--. 1 root root  3267 3月  12 10:05 mysql_pubkey.asc

2.3、导入密钥

[root@localhost ~]# gpg --import mysql_pubkey.asc
gpg: 已创建目录‘/root/.gnupg’
gpg: 新的配置文件‘/root/.gnupg/gpg.conf’已建立
gpg: 警告：在‘/root/.gnupg/gpg.conf’里的选项于此次运行期间未被使用
gpg: 钥匙环‘/root/.gnupg/secring.gpg’已建立
gpg: 钥匙环‘/root/.gnupg/pubring.gpg’已建立
gpg: /root/.gnupg/trustdb.gpg：建立了信任度数据库
gpg: 密钥 3A79BD29：公钥“MySQL Release Engineering <mysql-build@oss.oracle.com>”已导入
gpg: 合计被处理的数量：1
gpg:           已导入：1  (RSA: 1)
[root@localhost ~]# rpm --import mysql_pubkey.asc

这一步主要是为安装mysql作准备

三、安装mysql

3.1、安装mysql

如果你配置了gpg密钥就执行如下的安装

yum -y install mysql-community-server

如果你不想配置密钥就可以使用如下命令安装。

# 禁用GPG，增加参数：--nogpgcheck
yum -y install mysql-community-server --nogpgcheck

3.2、启动Mysql

接着启动Mysql，并检查Mysql的状态

[root@localhost ~]# systemctl start mysqld.service
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 日 2023-03-19 03:39:54 EDT; 16s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 123313 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 123336 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─123336 /usr/sbin/mysqld

3月 19 03:39:42 localhost.localdomain systemd[1]: Starting MySQL Server...
3月 19 03:39:54 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost ~]#

看到上面的 Active: active (running) 就是OK了

四、配置mysql

4.1、修改密码

安装完成后，我们通过如下命令可以查到mysql的root用户的初始密码

[root@localhost ~]# grep "password" /var/log/mysqld.log
2023-03-12T04:47:54.873320Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: RzZM)*W:r474

通过上面的密码登录后，修改root账户的登录密码为我们想要设置的密码

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Admin@0810';
Query OK, 0 rows affected (0.28 sec)

至此我们mysql服务就安装完毕了，另外一台也是一样，当然在虚拟机中也可以通过克隆的方式搭建。

五、主从配置

我们现有两台安装了mysql服务的虚拟机

	从库	主库
ip地址	192.168.19.129	192.168.19.130
服务id	129	130
是否创建同步用户	否	是

5.1、master配置my.cnf

首先我们配置mysql主库的配置

[root@localhost ~]# vim /etc/my.cnf

添加如下配置：

# 配置编码为utf8
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'

# 启用二进制日志（很重要）
log-bin=mysql-bin
# Master的id，在主从中唯一
server-id=130

# 配置要给Slave同步的数据库
# binlog-do-db=test

# 不用给Slave同步的数据库，一般是Mysql自带的数据库就不用给Slave同步了
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# 自动清理30天前的log文件
expire_logs_days=30

5.2、master创建用户

管理员账户

mysql> create user 'alian'@'%' identified by 'Alian@0810';
Query OK, 0 rows affected (0.12 sec)

mysql> grant all privileges on *.* to 'alian'@'%';
Query OK, 0 rows affected (0.46 sec)

同步账户

mysql> create user 'myslave'@'%' identified by 'Myslave@0810';
Query OK, 0 rows affected (0.01 sec)

mysql> grant file on *.* to 'myslave'@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> grant REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'myslave'@'%';
Query OK, 0 rows affected (0.01 sec)

重启我们的服务

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]#

通过我们新创建的账户alian登录查看log_bin日志

[root@localhost ~]# mysql -ualian -pAlian@0810
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 8
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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 variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.27 sec)

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 |      157 |              | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.12 sec)

5.3、slave配置my.cnf

然后修改mysql从库的配置

[root@localhost ~]# vim /etc/my.cnf

配置如下：

# 配置从服务器的ID，唯一的
server-id=129

# 配置编码为utf8
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'

# 不需要同步的数据库
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys

# relay_log其他配置
read_only = 1
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
relay_log_recovery=on
sync_relay_log=1
sync_relay_log_info=1

重启从服务器的mysql服务

systemctl restart mysqld

执行下面的命令

# master_host是Master的ip
# master_log_file就是配置之前查看Master状态时显示的File信息
# master_log_pos就是配置之前查看Master状态时显示的Position信息
# get_master_public_key解决认证问题
change master to 
master_host='192.168.19.130',
master_port=3306,
master_user='myslave',
master_password='Myslave@0810',
master_log_file='mysql-bin.000001',
master_log_pos=157,
get_master_public_key=1;

执行的结果如下：

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.19.130',master_port=3306,master_user='myslave',master_password='Myslave@0810',master_log_file='mysql-bin.000001',master_log_pos=157,get_master_public_key=1;
Query OK, 0 rows affected, 10 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.19.130
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 157
               Relay_Log_File: relay-log-bin.000003
                Relay_Log_Pos: 373
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
           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: 157
              Relay_Log_Space: 917
              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: 130
                  Master_UUID: 650b043a-c0ca-11ed-bcf0-000c29bbd701
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 1
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

mysql>

六、验证

6.1、从库的数据

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

6.2、主库创建库表

用我们master创建的管理员账户（alian）去创建数据库和表

create database test default character set utf8mb4 collate utf8mb4_general_ci;

use test;

CREATE TABLE `tb_subject` (
  `id` tinyint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sub_code` int NOT NULL DEFAULT '0' COMMENT '科目编号',
  `sub_name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '科目名称',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sub_code` (`sub_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='科目表';

执行的结果如下：

[root@localhost ~]# mysql -ualian -pAlian@0810
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 26
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database test default character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE `tb_subject` (
    ->   `id` tinyint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    ->   `sub_code` int NOT NULL DEFAULT '0' COMMENT '科目编号',
    ->   `sub_name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '科目名称',
    ->   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    ->   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `uk_sub_code` (`sub_code`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='科目表';
Query OK, 0 rows affected (0.32 sec)

6.3、主库插入数据

insert into tb_subject(sub_code,sub_name) values(2001,'语文');
insert into tb_subject(sub_code,sub_name) values(2002,'数学');
insert into tb_subject(sub_code,sub_name) values(2003,'英语');

效果图如下：
在这里插入图片描述

6.4、主库状态查看

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000002 |     2138 |              | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.01 sec)

6.5、从库查看

从库查看（随便你用root或者其他账户，我这里是使用的root查看的）

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

mysql> use test;
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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_subject     |
+----------------+
1 row in set (0.00 sec)

mysql> select * from tb_subject;
+----+----------+----------+---------------------+---------------------+
| id | sub_code | sub_name | create_time         | update_time         |
+----+----------+----------+---------------------+---------------------+
|  1 |     2001 | 语文     | 2023-03-19 09:40:42 | 2023-03-19 09:40:42 |
|  2 |     2002 | 数学     | 2023-03-19 09:40:55 | 2023-03-19 09:40:55 |
|  3 |     2003 | 英语     | 2023-03-19 09:41:04 | 2023-03-19 09:41:04 |
+----+----------+----------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>

从上面我们看到，从库可以查询到主库创建的库，表及插入的数据，说明我们的同步生效了。

在这里插入图片描述
通过命令查看从库的状态

show slave status \G

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.19.130
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2138
               Relay_Log_File: relay-log-bin.000003
                Relay_Log_Pos: 2354
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
           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: 2138
              Relay_Log_Space: 2898
              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: 130
                  Master_UUID: 650b043a-c0ca-11ed-bcf0-000c29bbd701
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 1
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

mysql>

七、主从复制原理

主从复制原理图如下：
在这里插入图片描述

Master库中有数据更新的时候，会按照 binlog 格式，将更新的事件类型写入到主库的 binlog 文件中，并创建一个 dump线程 ，并通知Slave，主库中存在数据更新
Slave库执行 start slave 命令后，会创建一个 IO线程 与Master库建立连接，并请求主库更新的 binlog ，收到从库IO线程的请求后，dump线程会读取并发送binlog，IO线程接收到后会把它写到 relay log 中（Master会返回binlog的名称以及当前数据更新的位置、binlog文件位置的副本）
SQL线程 也是在Slave中创建的，当Slave检测到 relay log 有更新，就会将更新的内容同步到Slave数据库中，从而保证数据的同步