MySQL主从复制

MySQL1：配置mastesr

[root@mysql1 ~]# vim /etc/my.cnf
[root@mysql1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1
[root@mysql1 ~]# /etc/init.d/mysqld restart 

Shutting down MySQL...... SUCCESS! 
Starting MySQL.......................... SUCCESS! 

[root@mysql1 ~]# mysql -predhat
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log Source distribution

Copyright (c) 2000, 2023, 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> CREATE USER 'repl'@'%' IDENTIFIED BY 'redhat';
Query OK, 0 rows affected (0.22 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      595 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> EXIT;
Bye

MySQL2：配置salve

[root@mysql2 ~]# /etc/init.d/mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
 

[root@mysql2 ~]# mysql -predhat
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.44 Source distribution

Copyright (c) 2000, 2023, 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> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CHANGE MASTER TO  
    ->   MASTER_HOST='172.25.254.6',  
    ->   MASTER_USER='repl',  
    ->   MASTER_PASSWORD='redhat',  
    ->   MASTER_LOG_FILE='mysql-bin.000001',  
    ->   MASTER_LOG_POS=1422;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.02 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> 
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.6
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1422
               Relay_Log_File: mysql2-relay-bin.000002
                Relay_Log_Pos: 320
        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: 1422
              Relay_Log_Space: 528
              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: 830992bc-605c-11ef-b0f2-000c29c6c80f
             Master_Info_File: /data/mysql/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: 
1 row in set (0.02 sec)

测试：

mysql1

[root@mysql1 ~]# mysql -predhat
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.44-log Source distribution

Copyright (c) 2000, 2023, 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> CREATE DATABASE folian;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE TABLE folian.userlist (
    -> username varchar(20) not null,
    -> password varchar(20) not null
    -> );
Query OK, 0 rows affected (1.29 sec)

mysql> INSERT INTO folian.userlist VALUE ('folian','123');
Query OK, 1 row affected (0.63 sec)

mysql> SELECT * FROM folian.userlist;
+----------+----------+
| username | password |
+----------+----------+
| folian   | 123      |
+----------+----------+
1 row in set (0.01 sec)

mysql2

 [root@mysql2 ~]# mysql -predhat
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.44 Source distribution

Copyright (c) 2000, 2023, 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> SELECT * FROM folian.userlist;
+----------+----------+
| username | password |
+----------+----------+
| folian   | 123      |
+----------+----------+
1 row in set (0.02 sec)
 

遇到的问题：由于第一次配置slave出错了，但是后面修改后，一直报错；

报错：

最后成功解决：

解决方法：