mysql8.4:搭建主从复制(mysql 8.4.1)

一，主库上：修改配置文件

1,配置文件:

/etc/my.cnf中，增加:

server-id = 1

说明：无需指明log bin的值,因为它的默认值就是打开的,

SHOW VARIABLES LIKE 'log_bin';

返回:

说明：主库的server-id要和从库的server-id区分开，一般主库用1，其他各从库用2及以后的数字

二,主库上：创建备份账号

1,创建一个同步账号，并授权:

mysql> create user 'backup'@'121.122.123.134' identified by 'backuppassword';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to [email protected];
Query OK, 0 rows affected (0.00 sec)

2,检查创建是否成功?

mysql> select * from mysql.user where User='backup';

3,检查当前用户获得的授权

mysql> show grants for [email protected];
+--------------------------------------------------------------+
| Grants for [email protected]                            |
+--------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `backup`@`121.122.123.134` |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

4,使新建账号生效

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

三，主库上：导出指定库:

1,在mysql中锁定表并查看当前位置:

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      158 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2,导出数据表:

[root@web mysql]# mysqldump -u root -p mybase > mybase_all.sql
Enter password:

3,解锁:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

四，从库上: 配置文件

[root@os3 ~]# vi /etc/my.cnf

主要配置以下各项:

server-id = 2
replicate-do-db = 要复制的库名
read_only         = 1

五，复制备份的sql文件到从库服务器，导入到数据库

[root@backup ~]# mysql -u root -p my_base < my_base_all.sql
Enter password: 

六，在从库上启动复制:

1,执行sql对复制进行设置

CHANGE REPLICATION SOURCE TO SOURCE_HOST='主库ip', SOURCE_USER='主库上复制用户', SOURCE_PASSWORD='主库上复制用户的密码', 
                   SOURCE_PORT=3306, SOURCE_LOG_FILE='mysql-bin.000002', SOURCE_LOG_POS=158, SOURCE_SSL=1;

2,启动复制:

START replica;

3,在从库上查看复制状态:

SHOW replica STATUS;

七，需要注意的地方:

1,show master status; 不能用了

# mysql 8.4版本前使用这条命令查看
show master status; 

# MySQL 8.4版本后使用这条命令查看
SHOW BINARY LOG STATUS;

2,change master to不能用了

# MSQL 8.23前
CHANGE MASTER TO MASTER_HOST='192.168.6.133', MASTER_USER='remote', MASTER_PASSWORD='yourpassword', MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=158;
​
# MSQL 8.23后
CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.6.133', SOURCE_USER='remote', SOURCE_PASSWORD='yourpassword', SOURCE_LOG_FILE='binlog.000003', SOURCE_LOG_POS=158;
​
CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.6.136', SOURCE_USER='remote', SOURCE_PASSWORD='yourpassword', SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=158,GET_SOURCE_PUBLIC_KEY=1;

3,start slave不能用了

# 开启同步
start replica ; #8.0.22之后 
start slave ; #8.0.22之前

4,show slave status不能用了

# 查看状态,\G表示行转列，便于查看
show replica status\G ; #8.0.22之后 
show slave status\G ; #8.0.22之前