在Windows环境与Linux环境下搭建MySQL主从同步

CodeDevMaster

已于 2022-07-14 22:23:09 修改

阅读量1.1k

点赞数

分类专栏： # MySQL 文章标签： mysql 数据库

于 2021-02-07 09:26:41 首次发布

本文链接：https://blog.csdn.net/qq_38628046/article/details/113731875

版权

MySQL 专栏收录该内容

24 篇文章 2 订阅

订阅专栏

在Windows环境与Linux环境下搭建MySQL主从同步

MySQL主从同步之Windows版
MySQL主从同步之Linux版

MySQL主从同步之Windows版

准备Mysql实例

下载MySQL,解压得到两个MySQL实例，MySQL实例版本：5.7.33

主库：mysql-master
在这里插入图片描述
创建data目录与my.ini文件

配置my.ini

[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\Mysql\mysql-master
# 设置mysql数据库的数据的存放目录
datadir=D:\Mysql\mysql-master\data

初始化MySQL数据文件

生成临时密码:+VoXweWtn0nk

D:\Mysql\mysql-master\bin>mysqld --initialize --user=mysql --console
2021-02-06T14:13:47.509372Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-02-06T14:13:47.782394Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-02-06T14:13:47.830502Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-02-06T14:13:47.923373Z 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: 878fec60-6885-11eb-81a5-002324ce8b45.
2021-02-06T14:13:47.927616Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-02-06T14:13:49.170165Z 0 [Warning] CA certificate ca.pem is self signed.
2021-02-06T14:13:50.110769Z 1 [Note] A temporary password is generated for root@localhost: +VoXweWtn0nk

安装MySQL

无安装权限

D:\Mysql\mysql-master\bin>mysqld --install
Install/Remove of the Service Denied!

D:\Mysql\mysql-master\bin>

使用管理员操作

D:\Mysql\mysql-backup\bin>mysqld --install
Service successfully installed.

D:\Mysql\mysql-backup\bin>

启动MySQL

D:\Mysql\mysql-master\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

登录MySQL

使用初始化MySQL数据文件生成的临时密码+VoXweWtn0nk

D:\Mysql\mysql-master\bin>mysql -u root -P 3306 -h 127.0.0.1  -p
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33

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>

重置登录密码

任何操作将会出现:You must reset your password using ALTER USER statement before executing this statement 提示,要求必须修改密码

MySQL5.7.6以前版本：
mysql> SET PASSWORD = PASSWORD('123456'); 

MySQL5.7.6以后版本：
mysql> ALTER USER USER() IDENTIFIED BY '123456';

mysql> alter user user() identified by "123456";
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

从库：mysql-backup

从库：mysql-backup配置同主库：mysql-master类似。

my.ini

[mysqld]
#设置3307端口
port = 3307
# 设置mysql的安装目录
basedir=D:\Mysql\mysql-backup
# 设置mysql数据库的数据的存放目录
datadir=D:\Mysql\mysql-backup\data

在这里插入图片描述

修改主、从库的配置文件

主库

[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\Mysql\mysql-master
# 设置mysql数据库的数据的存放目录
datadir=D:\Mysql\mysql-master\data


#开启日志
log-bin=mysql-bin
#设置服务id，主从不能一致
server-id=1
#设置需要同步的数据库,不配置则同步全部数据库 
binlog-do-db=test
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

从库

[mysqld]
#设置3307端口
port = 3307
# 设置mysql的安装目录
basedir=D:\Mysql\mysql-backup
# 设置mysql数据库的数据的存放目录
datadir=D:\Mysql\mysql-backup\data


#开启日志
log-bin=mysql-bin
#设置服务id，主从不能一致
server-id=2
#设置需要同步的数据库,不配置则同步全部数据库 
binlog-do-db=test.%
#屏蔽系统库同步
binlog-ignore-db=mysql.%
binlog-ignore-db=information_schema.%
binlog-ignore-db=performance_schema.%
binlog-ignore-db=sys.%

重启主库和从库

主MySQL的数据(data)目录下有个文件auto.cnf，文件中定义了uuid，必须保证主从数据库实例的uuid不一样，若复制主库生成丛库需要删除掉，重启服务让其重新生成。

[auto]
server-uuid=bd22514d-6882-11eb-bfce-002324ce8b45

[auto]
server-uuid=878fec60-6885-11eb-81a5-002324ce8b45

D:\Mysql\mysql-master\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

D:\Mysql\mysql-master\bin>net stop mysql-backup
mysql-backup 服务正在停止..
mysql-backup 服务已成功停止。

D:\Mysql\mysql-master\bin>net start mysql
MySQL 服务正在启动 .
mysql-backup 服务已经启动成功。

D:\Mysql\mysql-master\bin>net start mysql-backup
mysql-backup 服务正在启动 .
mysql-backup 服务已经启动成功。

D:\Mysql\mysql-master\bin>

创建授权主从复制的账号

#登录主库
mysql ‐u root ‐P 3306 -p

#授权主备复制专用账号
GRANT REPLICATION SLAVE ON *.* TO 'dbSync'@'%' IDENTIFIED BY '123456';

#刷新权限
FLUSH PRIVILEGES;

#记录下file文件名以及Position位点
show master status;

D:\Mysql\mysql-master\bin>mysql -u root -P 3306 -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33-log 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> GRANT REPLICATION SLAVE ON *.* TO 'dbSync'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

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

mysql>

设置从库向主库同步/复制数据

#登录从库
mysql -h localhost -P3307 -uroot -p

#先停止同步
 STOP SLAVE;
 
#修改从库指向到主库，使用上一步记录的文件名以及位点
CHANGE MASTER TO
 master_host = 'localhost',
 master_user = 'dbSync',
 master_password = '123456',
 master_log_file = 'mysql-bin.000002',
 master_log_pos = 591;
 
#启动同步
START SLAVE;

#查看从库状态Slave_IO_Runing和Slave_SQL_Runing都为Yes说明同步成功

#如果不为Yes，查看error_log排查相关异常

show slave status\G

#如果之前此备库已有主库指向 需要先执行以下命令清空

STOP SLAVE IO_THREAD FOR CHANNEL '';
reset slave all;

D:\Mysql\mysql-backup\bin>mysql -h localhost -P3307 -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33-log 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> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO master_host = 'localhost', master_user = 'dbSync', master_password = '123456', master_log_file = 'mysql-bin.000002', master_log_pos = 591;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: dbSync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 591
               Relay_Log_File: DESKTOP-V48ROO6-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             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: 591
              Relay_Log_Space: 537
              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: 1
                  Master_UUID: 878fec60-6885-11eb-81a5-002324ce8b45
             Master_Info_File: D:\Mysql\mysql-backup\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>

测试主从是否生效

在主库修改数据，看从库是否能够同步成功。

主库
在这里插入图片描述
从库

修改主库

从库数据同步

MySQL主从同步之Linux版

MySQL安装

Linux环境下安装MySQL参考：在Linux与Windows环境下安装MySQL

主MySQL配置

修改配置文件：vim /etc/my.cnf

#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#设置需要复制的数据库
binlog-do-db=demo
#设置logbin格式;可选值：STATEMENT、ROW、MIXED
binlog_format=STATEMENT

从MySQL配置

修改配置文件：vim /etc/my.cnf

#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

重启

重启主MySQL与从MySQL服务

创建授权主从复制的账号

在主MySQL上建立帐户并授权slave账号

#登录主库
mysql ‐u root ‐P 3306 -p

#授权主备复制专用账号
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

#刷新权限
FLUSH PRIVILEGES;

#查询master的状态
show master status;

#记录下file文件名以及Position位点
show master status;

注意：执行完上述骤后不要再操作主服务器MySQL，防止主服务器记录的file文件名以及Position位点状态值变化

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

设置从库向主库同步/复制数据

#登录从库
mysql -h localhost -P3307 -uroot -p


#修改从库指向到主库，使用上一步记录的文件名以及位点
CHANGE MASTER TO
 master_host = 'IP',
 master_user = 'slave',
 master_password = '123456',
 master_log_file = 'mysql-bin.000050',
 master_log_pos = 994;
 
#启动同步
START SLAVE;

# 查看从库状态Slave_IO_Runing和Slave_SQL_Runing都为Yes说明同步成功
#如果不为Yes，查看error_log排查相关异常
show slave status\G

# 停止从服务复制功能
 STOP SLAVE;
 
#如果之前此备库已有主库指向 需要先执行以下命令清空
STOP SLAVE IO_THREAD FOR CHANNEL '';
reset slave all;

# 或者重新配置主从
stop slave;
reset master;

查看从服务器状态；Slave_IO_Runing和Slave_SQL_Runing都为Yes说明同步成功

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: IP
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000050
          Read_Master_Log_Pos: 994
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000050
             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: 994
              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: 1
                  Master_UUID: e61e3b94-467d-11ec-8bd1-00163e027319
             Master_Info_File: /var/lib/mysql/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新建库、新建表、插入数据

#登录主库
mysql -h localhost -P3306 -uroot -p

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE mytb(id INT,name VARCHAR(10));
ERROR 1046 (3D000): No database selected
mysql> select mydb;
ERROR 1054 (42S22): Unknown column 'mydb' in 'field list'
mysql> use mydb;
Database changed
mysql> CREATE TABLE mytb(id INT,name VARCHAR(10));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO mytb VALUES(1,"xiaobai");
Query OK, 1 row affected (0.01 sec)

mysql> select * from mytb;
+------+---------+
| id   | name    |
+------+---------+
|    1 | xiaobai |
+------+---------+
1 row in set (0.00 sec)

验证从MySQL是否复制了数据

#登录从库
mysql -h localhost -P3307 -uroot -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mycat              |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

mysql> use mydb;
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_mydb |
+----------------+
| mytb           |
+----------------+
1 row in set (0.00 sec)

mysql> select * from mytb;
+------+---------+
| id   | name    |
+------+---------+
|    1 | xiaobai |
+------+---------+
1 row in set (0.00 sec)