mysql8 主从复制

本次实验使用一键脚本部署mysql8
脚本地址：https://blog.csdn.net/qq_34070818/article/details/131686314

实验环境
192.168.100.81 master
192.168.100.82 slave

master配置 192.168.100.81

1，my.cnf 文件配置

首先要在配置文件中开启server-id log-bin 这两个参数
[root@wscyunvm01 ~]# echo "server-id=1" >>/etc/my.cnf
[root@wscyunvm01 ~]# echo "log-bin=mysql-bin" >>/etc/my.cnf
[root@wscyunvm01 ~]# cat /etc/my.cnf |tail -2
server-id=1
log-bin=mysql-bin

查看server-id是多少
[root@wscyunvm01 ~]# mysql -uroot -p123456 -e "show variables like '%server_id%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+

实验环境关闭防火墙
[root@wscyunvm01 ~]# systemctl restart mysqld
[root@wscyunvm01 ~]# systemctl stop firewalld

2，创建slave连接账号

给slave同步账号
[root@wscyunvm01 ~]# mysql -uroot -p123456 -e "create user 'tongbu'@'192.168.100.82' identified with mysql_native_password by '654321';"
mysql: [Warning] Using a password on the command line interface can be insecure.

给账号replication 权限
[root@wscyunvm01 ~]# mysql -uroot -p123456 -e "grant replication slave on *.* to 'tongbu'@'192.168.100.82';"
mysql: [Warning] Using a password on the command line interface can be insecure.

刷新权限
[root@wscyunvm01 ~]# mysql -uroot -p123456 -e "flush privileges;"
mysql: [Warning] Using a password on the command line interface can be insecure.

查看master上pos点，从哪开始同步
[root@wscyunvm01 ~]# mysql -uroot -p123456 -e "show master status;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      857 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

slave配置 192.168.100.82

配置文件开启 server-id 参数，id 不能与master相同
[root@wscyunvm02 ~]# echo "server-id=2" >>/etc/my.cnf
[root@wscyunvm02 ~]# systemctl restart mysqld
[root@wscyunvm02 ~]# systemctl stop firewalld
[root@wscyunvm02 ~]# cat /etc/my.cnf |tail -1
server-id=2
[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "show variables like '%server_id%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 2     |
| server_id_bits | 32    |
+----------------+-------+

同步master命令
[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "change master to \
> master_host='192.168.100.81', \
> master_port=3306, \
> master_user='tongbu', \
> master_password='654321', \
> master_log_file='mysql-bin.000001', \
> master_log_pos=857;"
mysql: [Warning] Using a password on the command line interface can be insecure.

启动slave节点
[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "start slave;"
mysql: [Warning] Using a password on the command line interface can be insecure.

查看同步状态
[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "show slave status\G;" |grep -E "Slave_IO_Running|Slave_SQL_Running:"
mysql: [Warning] Using a password on the command line interface can be insecure.
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


**完整的状态**
[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "show slave status\G;"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.100.81
                  Master_User: tongbu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1221
               Relay_Log_File: wscyunvm02-relay-bin.000002
                Relay_Log_Pos: 690
        Relay_Master_Log_File: mysql-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: 1221
              Relay_Log_Space: 905
              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: 80b34453-21eb-11ee-bed5-000c29db7188
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 

验证主从

在master服务器上面创建数据库

[root@wscyunvm01 ~]# mysql -uroot -p123456 -e "create database aaa;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@wscyunvm01 ~]# mysql -uroot -p123456 -e "create database bbb;"
mysql: [Warning] Using a password on the command line interface can be insecure.

在slave服务器上验证是否同步过来

[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| aaa                |
| bbb                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

互为主从

根据以上实验环境继续做互为主从（）

1，首先配置 192.168.100.82（master）

开启log-bin参数
[root@wscyunvm02 ~]# echo "log-bin=mysql-bin" >>/etc/my.cnf
[root@wscyunvm02 ~]# cat /etc/my.cnf |tail -2
server-id=2
log-bin=mysql-bin

重启数据库
[root@wscyunvm02 ~]# systemctl restart mysqld

2，创建一个同步用户

[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "create user 'tongbu1'@'192.168.100.81' identified with mysql_native_password by '654321';"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "grant replication slave on *.* to 'tongbu1'@'192.168.100.81';"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "flush privileges;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "show master status;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1158 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3，在192.168.100.81配置同步 （slave）

[root@wscyunvm01 ~]# mysql -uroot -p123456 -e "change master to \
> master_host='192.168.100.82', \
> master_port=3306, \
> master_user='tongbu1', \
> master_password='654321', \
> master_log_file='mysql-bin.000001', \
> master_log_pos=1158;"
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@wscyunvm01 ~]# mysql -uroot -p123456 -e "start slave;"
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@wscyunvm01 ~]# mysql -uroot -p123456 -e "show slave status\G;"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.100.82
                  Master_User: tongbu1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1158
               Relay_Log_File: wscyunvm01-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-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: 1158
              Relay_Log_Space: 541
              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: 2
                  Master_UUID: 3d0e5103-21ec-11ee-832b-000c29142d7d
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 

3,验证在 192.168.100.82（master） 这台上面创建数据库，81（slave）这台服务器是否同步

[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "create database mmm;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "create database kkk;"
mysql: [Warning] Using a password on the command line interface can be insecure.

Mster节点查看数据库
[root@wscyunvm02 ~]# mysql -uroot -p123456 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| aaa                |
| bbb                |
| information_schema |
| kkk                |
| mmm                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Slave节点查看数据库
[root@wscyunvm01 ~]# mysql -uroot -p123456 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| aaa                |
| bbb                |
| information_schema |
| kkk                |
| mmm                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

注意 如果同步的数据库上面有数据，请先备份还原到slave上面再同步，这样数据就会一致