mysql8 主从复制 一主一从 互为主从

前提需要2台linux服务器分别启动相同版本号的mysql8。需要使用的demo数据库master和slave都不要创建demo数据库！！！等实现主从复制以后再在master创建demo数据库，这样slave就会同步创建demo数据库。

主mysql服务器master的/etc/my.cnf配置
[必须]mysql服务器唯一ID：server-id=1
[必须]启用二进制日志，指明路径：log-bin=mysqllog-bin
[可选]需要复制的数据库，默认全部记录。binlog-do-db=数据库名字：binlog-do-db=demo
[可选]设置不要复制的数据库：binlog-ignore-db=test
[可选]设置binlog格式：binlog_format=STATEMENT

[mysqld]
server-id=1
log-bin=mysqllog-bin
binlog-do-db=demo
binlog_format=STATEMENT

port=3306
basedir=/usr/local/mysql-8.0.23
datadir=/usr/local/mysql-8.0.23/data
max_connections=10000
max_connect_errors=10
character-set-server=utf8
default_authentication_plugin=mysql_native_password
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[mysql]
default-character-set=utf8
[client]
port=3306
default-character-set=utf8

修改完master数据库配置以后重启mysql服务

systemctl restart mysqld

从mysql服务器slave的/etc/my.cnf配置
[必填]启用中继日志：relay-log=mysql-relay
[必须]mysql服务器唯一ID：server-id=2

[mysqld]
server-id=2
relay-log=mysql-relay

port=3306
basedir=/usr/local/mysql-8.0.23
datadir=/usr/local/mysql-8.0.23/data
max_connections=10000
max_connect_errors=10
character-set-server=utf8
default_authentication_plugin=mysql_native_password
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[mysql]
default-character-set=utf8
[client]
port=3306
default-character-set=utf8

修改完slave数据库配置以后重启mysql服务

systemctl restart mysqld

链接不上mysql要关防火墙
《linux彻底关闭防火墙》
创建用户并授权
《创建mysql用户并授权》
从机不能有主机的数据库，有的话，先删掉！！！

链接master数据库查看信息

show master status;

[root@node1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 |
+---------------------+----------+--------------+------------------+-------------------+
| mysqllog-bin.000001 |    16849 | demo         |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

master主要是下面的内容：

+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| mysqllog-bin.000001 |    16849 | demo         |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+

slave从机配置：
MASTER_HOST=主机IP
MASTER_USER=主机用户
MASTER_PASSWORD=主机用户密码
MASTER_LOG_FILE=对应上面的File的值
MASTER_LOG_POS=对应上面的Position的值

CHANGE MASTER TO MASTER_HOST='192.168.18.111',MASTER_USER='x',MASTER_PASSWORD='x',MASTER_LOG_FILE='mysqllog-bin.000001',MASTER_LOG_POS=16849;

[root@node1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> CHANGE MASTER TO MASTER_HOST='192.168.18.111',MASTER_USER='x',MASTER_PASSWORD='x',MASTER_LOG_FILE='mysqllog-bin.000001',MASTER_LOG_POS=16849;

进入mysql客户端：slave从数据库启动主从复制 mysql> START SLAVE;

START SLAVE;

之前有主从复制过的先停止slave同步数据，没有的则不用！！！

STOP SLAVE;

报：Slave failed to initialize relay log info structure from the repository
执行reset slave删除salve数据库的ralaylog日志文件，并重新启用新的relaylog文件。

reset slave all;

没有报错，则查看同步状态：

SHOW SLAVE STATUS\G;

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.18.111
                  Master_User: jettwork
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqllog-bin.000001
          Read_Master_Log_Pos: 16849
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 327
        Relay_Master_Log_File: mysqllog-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: 16849
              Relay_Log_Space: 532
              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: fccca93b-cc38-11ec-8c64-000c29604a81
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave 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:
1 row in set, 1 warning (0.01 sec)

出现这2行说明主从复制成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

---

一主一从 互为主从

就是简单的改改mysql的 /etc/my.cnf 文件即可。
server-id=1（主，从）
server-id=2（从，主）

server-id= 1 的my.cnf

[mysqld]
#master
server-id=1
log-bin=mysqllog-bin
binlog-do-db=demo
binlog_format=STATEMENT
#slave
relay-log=mysql-relay

port=3306
basedir=/usr/local/mysql-8.0.23
datadir=/usr/local/mysql-8.0.23/data
max_connections=10000
max_connect_errors=10
character-set-server=utf8
default_authentication_plugin=mysql_native_password
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[mysql]
default-character-set=utf8
[client]
port=3306
default-character-set=utf8

server-id= 2 的my.cnf

[mysqld]
#slave
server-id=2
relay-log=mysql-relay
#master
log-bin=mysqllog-bin
binlog-do-db=demo
binlog_format=STATEMENT

port=3306
basedir=/usr/local/mysql-8.0.23
datadir=/usr/local/mysql-8.0.23/data
max_connections=10000
max_connect_errors=10
character-set-server=utf8
default_authentication_plugin=mysql_native_password
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[mysql]
default-character-set=utf8
[client]
port=3306
default-character-set=utf8

先把之前的demo库给删掉，以及清空slave;
然后重复上面的主从复制操作即可得到互为主从的2台Mysql数据库。