5.1.mysql8.0主从复制的实现

5.1.主从复制的实现
环境:MySQL 8.0 、centos8

#主节点
[root@master ~]#yum install mysql-server -y

[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log-bin

[root@master ~]#systemctl restart mysqld

[root@master ~]# mysql -uroot

#查看二进制文件和位置
(root@localhost) [(none)]> show master logs;
+——————-+———–+———–+
| Log_name | File_size | Encrypted |
+——————-+———–+———–+
| master-bin.000001 | 681 | No |
+——————-+———–+———–+

1 row in set (0.00 sec)

#创建复制用户
(root@localhost) [(none)]>grant replication slave on *.* to repluser@’10.0.0.%’
identified by ‘Mmagedu0!’;

#如果是MySQL 8.0 需要分成下面两步实现
(root@localhost) [(none)]>create user ‘repluser’@’10.0.0.%’ identified by ‘Mmagedu0!’;
(root@localhost) [(none)]>grant replication slave on *.* to ‘repluser’@’10.0.0.%’;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

#从节点
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18

[root@slave ~]# systemctl restart mysqld

[root@slave ~]# mysql -uroot -pMmagedu0!

(root@localhost) [(none)]>help change master to

(root@localhost) [(none)]> change master to master_host=’10.0.0.8′,
-> master_user=’repluser’,
-> master_password=’Mmagedu0!’,
-> master_port=3306,
-> master_log_file=’master-bin.000001′,
-> master_log_pos=681;
Query OK, 0 rows affected, 9 warnings (0.02 sec)

(root@localhost) [(none)]> start slave;

(root@localhost) [(none)]> show slave status/G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 681
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 325
Relay_Master_Log_File: master-bin.000001
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: 681
Relay_Log_Space: 534
Until_Condition: None

(root@localhost) [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+
4 rows in set (0.00 sec)

主服务器建一个数据库看看效果
(root@localhost) [(none)]> create database hellodb;
Query OK, 1 row affected (0.00 sec)

(root@localhost) [(none)]> show master logs;
+——————-+———–+———–+
| Log_name | File_size | Encrypted |
+——————-+———–+———–+
| master-bin.000001 | 875 | No |
+——————-+———–+———–+
(root@localhost) [(none)]> show databases;
+——————–+
| Database |
+——————–+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+

从服务器瞄一眼
(root@localhost) [(none)]> show master logs;
+—————+———–+———–+
| Log_name | File_size | Encrypted |
+—————+———–+———–+
| binlog.000001 | 179 | No |
| binlog.000002 | 818 | No |
| binlog.000003 | 357 | No |
+—————+———–+———–+

(root@localhost) [(none)]> show databases;
+——————–+
| Database |
+——————–+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+