mysql主从复制搭建

在主库中导出所有的库表结构

mysqldump -uroot -p123456 -S /mnt/mysql/mysql.sock --single-transaction --source-data=2 -E -R --triggers -A > /opt/full_backup.sql

主库的my.conf配置文件

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/mnt/mysql
socket=/mnt/mysql/mysql.sock
log-error=/mnt/mysql/mysql.err
pid-file=/mnt/mysql/mysql.pid
log-bin = mysql-bin
server_id=1
port=3306
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
max_connections=10000
default_authentication_plugin=mysql_native_password  # 关键修复
[mysql]
socket=/mnt/mysql/mysql.sock
default-character-set=utf8mb4
[client]
socket=/mnt/mysql/mysql.sock

从库my.cnf配置文件

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/mnt/mysql
socket=/mnt/mysql/mysql.sock
log-error=/mnt/mysql/mysql.err
pid-file=/mnt/mysql/mysql.pid
server_id=2
port=3306
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
max_connections=10000
default_authentication_plugin=mysql_native_password  # 关键修复
[mysql]
socket=/mnt/mysql/mysql.sock
default-character-set=utf8mb4
[client]
socket=/mnt/mysql/mysql.sock

在主库执行sql，创建复制用户及密码

mysql> CREATE USER 'repl'@'10.0.0.133' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.133';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;

建立主从复制关系（在从库中执行）

CHANGE MASTER TO
 MASTER_HOST='10.0.0.124',   主库的主机信息
 MASTER_USER='repl',    #复制用户
 MASTER_PASSWORD='123456', 密码
 MASTER_LOG_FILE='mysql-bin.000005',   这个在导出的sql文件中用grep索取 grep -m 1 "CHANGE MASTER TO"
 MASTER_LOG_POS=548,
 GET_MASTER_PUBLIC_KEY = 1;

开启主从复制 重点关注两列的输出： Slave_IO_Running 和 Slave_SQL_Running，两个均为”Yes“代表主从复制搭建成功

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.124
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 548
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000005
             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: 548
              Relay_Log_Space: 540
              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: 5cfaedb0-306b-11f0-b436-000c296b6206
             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: 1
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

常见报错

解决办法
需要如下操作

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

从库报这个·错误的解决方法

解决办法

在my.cnf文件添加这个配置文件
gtid_mode=ON
enforce_gtid_consistency=ON
在重启mysql数据库 还得查看SHOW VARIABLES LIKE 'gtid_mode'; 主从库是否一直
从库中需执行下列sql语句

STOP SLAVE; 停止运行

CHANGE MASTER TO
MASTER_HOST='主库IP',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION = 1;

start slave; 启动即可