MySQL 8.0.18主从搭建

一.准备两台以上的数据库

数据库1(主服务器):172.16.20.20
数据库2(从服务器):172.16.30.20

二.配置主服务器

2.1 配置

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

#binlog-do-db=isosign     #待同步的数据库
binlog-ignore-db=mysql    #不同步的数据

2.2 增加用户并且授权

CREATE USER 'sync'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
grant replication slave on *.* to 'sync'@'%';
flush privileges;

2.3 查看主服务器的状态和位移

PS:因为要停机,反正没有数据进入,所以以下面重启的位移为准

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

三.配置从服务器

3.1 配置

# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
server-id       = 2

# binary logging - not required for slaves, but recommended
log-bin=mysql-bin

#binlog-do-db=isosign     #待同步的数据库
binlog-ignore-db=mysql    #不同步的数据

四.同步数据并且配置服务

4.1 同步主服务器数据文件至从服务器

[root@v1 srv]# systemctl stop nginx
[root@v1 srv]# systemctl stop mysqld
[root@v1 srv]# cp -r mysql mysql-bak2
[root@v1 srv]# systemctl start mysqld
[root@v1 srv]# 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.18 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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 master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@v1 srv]# systemctl start nginx
[root@v1 srv]# tar czvf mysql-data.tar.gz mysql-bak2
[root@v1 srv]# scp mysql-data.tar.gz 172.16.30.20:/srv/

4.2 恢复数据至从服务器,并且指定主服务器

[root@v2 srv]# tar xzvf mysql-data.tar.gz
[root@v2 srv]# mv mysql-bak2 mysql
[root@v2 srv]# nano -w /srv/mysql/auto.cnf #修改uuid与主服务器不同
[root@v2 srv]# chown -R mysql.mysql mysql

4.3 第三步中配置的从服务器配置先注释掉,然后启动

[root@v2 srv]# systemctl start mysqld
[root@v2 mysql]# mysql -u root -p
mysql> stop slave;
mysql> change master to master_host='172.16.20.20',master_user='sync',master_password='password',master_log_file='mysql-bin.000003',master_log_pos=155;
mysql> start slave;
mysql> show slave status \G;

关于Zeno Chen

本人涉及的领域较多,杂而不精 程序设计语言: Perl, Java, PHP, Python; 数据库系统: MySQL,Oracle; 偶尔做做电路板的开发,主攻STM32单片机
此条目发表在MySQL分类目录。将固定链接加入收藏夹。