MySQL8主从复制之一主一从实战

MySQL8主从复制之一主一从实战

实战环境，Windows Server 2008R2+MySQL8.0.27

操作系统	MySQL版本	主/从库	IP	同步用户	同步用户密码
Windows Server 2008R2+	mysql-8.0.27-winx64.zip	master	192.168.3.246	synchadmin	123456
Windows Server 2008R2+	mysql-8.0.27-winx64.zip	slave	192.168.3.247	–	–

1. 下载与环境变量配置

截至到2021-11-29最新版本为Mysql-8.0.27

1. 官网下载：https://dev.mysql.com/downloads/
2. 安装文件：mysql-8.0.27-winx64.zip
3. 解压到D下：如：D:\MySqlServer\mysql-8.0.27-winx64
4. 环境变量配置

• 在系统变量中新增下面配置

变量名：MYSQL_HOME
变量值：D:\MySqlServer\mysql-8.0.27-winx64

• 在系统变量的path变量值后面新增下面代码

xxxx；%MYSQL_HOME%\bin

2. 主库安装与配置

1. 在D:\MySqlServer\mysql-8.0.27-winx64下新建my.ini，内容如下

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
 
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\\MySqlServer\\mysql-8.0.27-winx64
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=D:\\MySqlServer\\mysql-8.0.27-winx64 #8.0以下版本需要配置数据目录
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证解决客户端无法连接的问题
default_authentication_plugin=mysql_native_password

#主从-主库配置
#1.主服务器id唯一【必须】
server-id=1
#2.启用二进制日志【必须】
log-bin=mysql-bin
#3.主从，都可读可写
#read-only=0
#4.设置不要复制的数据库【可选】
binlog-ignore-db=mysql
#5.设置需要复制的数据库【可选】
#binlog-do-db=要复制的数据库名字

2. 进入D:\MySqlServer\mysql-8.0.27-winx64\bin目录下，以管理员身份打开cmd窗口，执行下面命令,产生root密码,牢记此密码

mysqld --initialize --console

cmd窗口中的内容如下

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

D:\MySqlServer\mysql-8.0.27-winx64\bin>mysqld --initialize --console
2021-11-29T07:09:43.953126Z 0 [Warning] [MY-010918] [Server] 'default_authentica
tion_plugin' is deprecated and will be removed in a future release. Please use a
uthentication_policy instead.
2021-11-29T07:09:43.953128Z 0 [System] [MY-013169] [Server] D:\MySqlServer\mysql
-8.0.27-winx64\bin\mysqld.exe (mysqld 8.0.27) initializing of server in progress
 as process 2056
2021-11-29T07:09:44.218752Z 1 [System] [MY-013576] [InnoDB] InnoDB initializatio
n has started.
2021-11-29T07:09:53.125000Z 1 [System] [MY-013577] [InnoDB] InnoDB initializatio
n has ended.
2021-11-29T07:09:59.234376Z 0 [Warning] [MY-013746] [Server] A deprecated TLS ve
rsion TLSv1 is enabled for channel mysql_main
2021-11-29T07:09:59.234377Z 0 [Warning] [MY-013746] [Server] A deprecated TLS ve
rsion TLSv1.1 is enabled for channel mysql_main
2021-11-29T07:09:59.359379Z 6 [Note] [MY-010454] [Server] A temporary password i
s generated for root@localhost: !pz0fpjAHg7M

D:\MySqlServer\mysql-8.0.27-winx64\bin>

# root@localhost: 后面就是生产的root密码
 root@localhost: !pz0fpjAHg7M

3. 进入到MySQL的bin目录下，执行下面命令

mysqld --install [服务名]（服务名可以不加默认为mysql）

D:\MySqlServer\mysql-8.0.27-winx64\bin>mysqld --install
Service successfully installed.

D:\MySqlServer\mysql-8.0.27-winx64\bin>

如果出现下面这,表示已经安装过mysql，则使用 命令sc delete mysql删除后重新安装

D:\mysql-8.0.23-winx64\bin>mysqld --install
The service already exists!

4. 安装完成后，启动mysql服务

#启动服务 
net start mysql
# 停止服务
net stop mysql

3. 从库安装与配置

1. 虚拟机中安装

如果在WMWare或VitiualBox中安装，

1. 则直接复制整个操作系统即可;
2. 复制开机后先将IP地址改一下
3. 将my.ini改一下即可完成从库的安装

2. 实体机中安装

1. 将从库my.ini中的server-id改一下，其他配置与主库无区别，如下：

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
 
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\\MySqlServer\\mysql-8.0.27-winx64
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=D:\\MySqlServer\\mysql-8.0.27-winx64 #8.0以下版本需要配置数据目录
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证解决客户端无法连接的问题
default_authentication_plugin=mysql_native_password

#主从-从库配置
#1.从库服务器id唯一,因主库为1,这里配置为2【必须】
server-id=2
#2.启用二进制日志【必须】
log-bin=mysql-bin
#3.主从，都可读可写
#read-only=0
#4.设置不要复制的数据库【可选】
binlog-ignore-db=mysql
#5.设置需要复制的数据库【可选】
#binlog-do-db=要复制的数据库名字

4. 一主一从配置

1.主库配置操作

1. 创建同步用户create user 'synchadmin'@'从库IP' identified by 'synchadmin';

mysql> create user 'synchadmin'@'192.168.3.247' identified by 'synchadmin';
Query OK, 0 rows affected (0.09 sec)

2. 授权

# 注意：如果不给同步用户synchadmin，则查看slave状态时 会出现 “Slave_IO_Running: Connecting”
mysql> grant replication slave on *.* to 'synchadmin'@'192.168.3.247';
Query OK, 0 rows affected (0.07 sec)

mysql>

3. 刷新权限

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

4. 查看master状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      446 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

5. 查看server_id

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set, 1 warning (0.04 sec)

mysql>

2. 从库配置操作

1. 使用root账户登录msyql数据库并执行下面命令

change master to 
master_host='192.168.3.246',
master_user='synchadmin',
master_password='synchadmin',
master_log_file='mysql-bin.000002',# 主库执行`show master status`命令 结果中的 File 值
master_log_pos=1151, # 主库执行`show master status`命令结果中的Position值
master_connect_retry=30;

2. 命令说明：

说明：
master_host ：主库IP,
master_port：Master的端口号
master_user：主库中创建的用于数据同步的用户(如:synchadmin)
master_password：主库中创建的用于同步的用户的密码(如:synchadmin)
master_log_file：指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos：从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒
在Slave 中的mysql终端执行show slave status \G;用于查看主从同步状态。

3. 具体执行结果如下：

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

D:\MySqlServer\mysql-8.0.27-winx64\bin>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 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.3.246',master_user='synchadmin',mas
ter_password='synchadmin',master_log_file='mysql-bin.000003',master_log_pos=624,m
aster_connect_retry=30;
Query OK, 0 rows affected, 8 warnings (0.17 sec)

mysql>

change master to master_host='192.168.3.246',master_user='synchadmin',master_password='synchadmin',master_log_file='mysql-bin000006',master_log_pos=156,master_connect_retry=30;

4. 查看状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.3.246
                  Master_User: synchadmin
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysqlbin.000002
          Read_Master_Log_Pos: 446
               Relay_Log_File: WIN-EC523ISE0QP-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysqlbin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 446
              Relay_Log_Space: 156
              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: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           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: 0
            Network_Namespace:
1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

mysql>

5. 启动从机服务器的复制功能

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

6. 再次查看slave状态,如果发现Slave_IO_Running: No；则说明没成功

show slave status\G;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.3.246
                  Master_User: synchadmin
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysqlbin.000003
          Read_Master_Log_Pos: 624
               Relay_Log_File: WIN-EC523ISE0QP-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysqlbin.000003
             Slave_IO_Running: No
            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: 624
              Relay_Log_Space: 156
              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: 13117
                Last_IO_Error: Fatal error: The slave I/O thread stops because m
aster and slave have equal MySQL server ids; these ids must be different for rep
lication to work (or the --replicate-same-server-id option must be used on slave
 but this does not always make sense; please check the manual before using it).
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID:
             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: 211129 16:41:58
     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: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

1. 停止主从复制

mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)

5. 首次登陆MySQL服务修改root密码

1. 使用下面命令登录MySQL服务

D:\MySqlServer\mysql-8.0.27-winx64\bin>mysql -u root -p
Enter password: ************ # 首次登陆输入安装过程中产生的root用户密码，如：!pz0fpjAHg7M
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27

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>

2. 修改root密码，举例:如密码改为 root

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

mysql> alter user 'root'@'localhost' identified with mysql_native_password  by 'root1';
Query OK, 0 rows affected (0.03 sec)
## alter user 'root'@'%' identified with mysql_native_password  by 'root';

6. 设置客户端可连接远程MySQL服务器

1. 用root账号登陆，切换至mysql库

mysql> use mysql;
Database changed
mysql>

2. 查看host字段

# localhost 表示只能在本地访问
mysql> use mysql;
Database changed
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql>

3. 修改host为值为”%“

# 修为改远程访问, % 远程任何主机可以访问root账户
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# 刷新下权限
mysql>flush privileges;

4. 再次使用客户端远程连接时则会连接成功

5. 授权语句5.7.x与8.x区别

# 5.7.x
grant all privileges on *.* to 'root'@'%' identified by 'root2' with grant option;
flush privileges;

# 8.x
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

7. 主从配置常见问题

1. 主从IP、网络配置是否正确
2. 防火墙是否未关闭
3. 同步用户是否授权了

1. Slave_IO_Running: No

• 出现问题后，查看从库的server_id,发现更主库id是一样的，所以问题就出现在这里

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set, 1 warning (0.18 sec)

• 解决方式，将从库的my.ini中的server_id 改为2 ，然后重启数据库即可解决

2. Last_IO_Errno: 13114

1. 问题描述：

Slave_IO_Running: No
Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

2. 分析

3. 主要查看data下的bin文件名是否正确，如mysql8中正确的bin文件名为mysql-bin.000001

4. 主库使用show master status 查看File与Position值

5. 解决方式

6. 改为正确的bin文件名；

7. 从库中重新使用主从链路 change master to …

8. 常见问题及处理方法

1. 安装Mysql8时提示 VCRUNTIME140.dll错误

• 解决方法

vc++官网下载：https://docs.microsoft.com/zh-CN/cpp/windows/latest-supported-vc-redist?view=msvc-170

1. X64快速下载地址：https://aka.ms/vs/17/release/vc_redist.x64.exe
2. X86快速下载地址：https://aka.ms/vs/17/release/vc_redist.x86.exe
3. ARM64快速下载地址：https://aka.ms/vs/17/release/vc_redist.arm64.exe

原因分析：

1. 缺少VC_redist.x64_v2015-2022.exe的安装
2. 版本号：14.30.30704
3. 最好下载一个最新版本，如果安装MySQL8时只安装了VC++2015，则会提示VCRUNTIME140_1.dll的错误
   

只安装了vc++2015，版本为14.0.xxx，则会提示下面问题，所以安装最高版本的VC++不会有这种问题

2. 提示没有创建目录的权限

此问题往往是my.ini中的配置错误导致的，如盘符指定错误、安装位置指定错误都有可能导致这种问题

D:\MySqlServer\mysql-8.0.27-winx64\bin>mysqld --initialize --console
mysqld: Can't create directory 'E:\MySqlServer\mysql-8.0.27-winx64\data\' (OS er
rno 13 - Permission denied)
2021-11-29T07:02:16.453126Z 0 [Warning] [MY-010918] [Server] 'default_authentica
tion_plugin' is deprecated and will be removed in a future release. Please use a
uthentication_policy instead.
2021-11-29T07:02:16.453128Z 0 [System] [MY-013169] [Server] D:\MySqlServer\mysql
-8.0.27-winx64\bin\mysqld.exe (mysqld 8.0.27) initializing of server in progress
 as process 2596
2021-11-29T07:02:16.453129Z 0 [ERROR] [MY-010338] [Server] Can't find error-mess
age file 'E:\MySqlServer\mysql-8.0.27-winx64\share\errmsg.sys'. Check error-mess
age file location and 'lc-messages-dir' configuration directive.
2021-11-29T07:02:16.453131Z 0 [ERROR] [MY-013236] [Server] The designated data d
irectory E:\MySqlServer\mysql-8.0.27-winx64\data\ is unusable. You can remove al
l files that the server added to it.
2021-11-29T07:02:16.453132Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-11-29T07:02:16.453134Z 0 [System] [MY-010910] [Server] D:\MySqlServer\mysql
-8.0.27-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.27)  MySQL Communit
y Server - GPL.

D:\MySqlServer\mysql-8.0.27-winx64\bin>

't find error-mess
age file 'E:\MySqlServer\mysql-8.0.27-winx64\share\errmsg.sys'. Check error-mess
age file location and 'lc-messages-dir' configuration directive.
2021-11-29T07:02:16.453131Z 0 [ERROR] [MY-013236] [Server] The designated data d
irectory E:\MySqlServer\mysql-8.0.27-winx64\data\ is unusable. You can remove al
l files that the server added to it.
2021-11-29T07:02:16.453132Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-11-29T07:02:16.453134Z 0 [System] [MY-010910] [Server] D:\MySqlServer\mysql
-8.0.27-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.27)  MySQL Communit
y Server - GPL.

D:\MySqlServer\mysql-8.0.27-winx64\bin>