一.准备两台以上的数据库
数据库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;