在Windows环境与Linux环境下搭建MySQL主从同步
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)
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/137076.html
