当前位置：首页 > news >正文

mysql主从复制搭建

news 2025/6/7 11:31:31

主库MASTER	10.0.0.124
从库WORK	10.0.0.133

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

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 TOMASTER_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 eventMaster_Host: 10.0.0.124Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 548Relay_Log_File: localhost-relay-bin.000002Relay_Log_Pos: 326Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: Yes  Slave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 548Relay_Log_Space: 540Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 5cfaedb0-306b-11f0-b436-000c296b6206Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 1Network_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; 启动即可