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

mysql主从配置

news 2025/8/27 3:49:03

一、准备工作

准备两个版本一致的数据库。
确认主库开启二进制日志，并配置server-id。

$ ##将 mysql的配置文件/home/mysql2/mysql/my.cnf 中关于二进制日志的配置
$ cd /home/mysql2/mysql/
$ vi my.cnf 
修改如下
server-id = 11
#log settings
log_error = error.log
#slow_query_log = on
#slow_query_log_file = slow.log
expire_logs_days = 30
long_query_time = 3
log_bin = master-bin
sync_binlog = 1
binlog_format = row###修改配置需要重启mysql,启动命令根据自己实际情况更改
$ /etc/init.d/mysqld restart

#创建临时备份文件存储路径


mkdir –p /home/mysql2/backup/DUMP/`date +%Y%m%d`
##会创建一个当天日期的目录  /home/mysql2/backup/DUMP/20241008

二、主库创建主从复制账号

#192.168.*为备数据库的内网网段，根据实际情况修改。

create user 'reply'@'192.168.%' identified by '123456'; 
grant FILE on *.* to 'reply'@'192.168.%' identified by '123456';
grant replication slave on *.* to 'reply'@'192.168.%' identified by '123456';
flush privileges;

三、备库配置检查

在my.cnf中增加如下配置

# 服务器idserver-id=22#忽略掉不同步的表格replicate-wild-ignore-table=database1.table1replicate-wild-ignore-table=database1.table2replicate-wild-ignore-table=database2.table3

#修改配置需要重启服务,启动命令根据自己实际情况更改

$ /etc/init.d/mysqld restart

#按照主库的创建方法刷备库，因为上面有一些忽略掉不同步的表格（有些数据量大又不重要的表可以忽略掉，减轻同步的压力，和导库时间），不会从主库同步过来。根据自己产品部署文档刷库。

四、备份主库数据

只备份业务相关的数据库 --databases database1 database2

mysqldump -uroot -p -h127.0.0.1 -P3306 --databases database1 database2 --events --routines --triggers --single-transaction --quick --flush-logs --master-data=2 > /home/mysql2/backup/DUMP/YYYYMMDD/all_databases.sql

如果是备份全部库--all-databases，操作mysql自有库可能会有报错。

#另外主从同步操作mysql系统库也可能报错，可在主库添加这些配置忽略掉系统库的bin日志。

#ignore system table

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys。

建议只备份业务相关的，mysql用户名密码手动创建和主的保持一致。

如果某些表太大切不重要，备份的时候可以忽略。--ignore-table=database1.table1

备份的时候忽略的表在做主从的时候也要忽略，因为两边数据没有同步。

mysqldump -uroot -p -h127.0.0.1 -P3306 --databases database1 database2 --events --routines --triggers --single-transaction --quick --ignore-table=database1.table1 --ignore-table=database1.table2  --ignore-table=database2.table3 --flush-logs --master-data=2 > /home/mysql2/backup/DUMP/YYYYMMDD/all_databases.sql

将备份文件传到备库

scp all_databases.sql root@192.168.8.11:/home/mysql2/backup/DUMP/20230508

五、备库导入数据并启动主从

5.1 导入数据

$cd /home/mysql2/backup/DUMP/20241008
$mysql -uroot -p -h127.0.0.1 -P3306
mysql> source all_databases.sql
或者
mysql -uroot -p -h127.0.0.1 -P3306 <all_databases.sql

5.2配置主从

查看同步位置，在sql里面有两个关键的数据，当前的binlog和logpos 这两个数据后面配置主从需要用到。

# cat all_databases.sql |grep "CHANGE MASTER TO MASTER_LOG_FILE" |head -n1 
-- CHANGE MASTER TO MASTER_LOG_FILE='bin.000022', MASTER_LOG_POS=154;

主从配置

mysql>change master to master_host='192.168.8.10',master_user='reply',master_password='123456',MASTER_PORT=23306,master_log_file='bin.000004', master_log_pos=154;
mysql>start slave;
mysql>show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.8.10Master_User: replyMaster_Port: 3306Connect_Retry: 60Master_Log_File: bin.000022Read_Master_Log_Pos: 423Relay_Log_File: host2-relay-bin.000005Relay_Log_Pos: 624Relay_Master_Log_File: bin.000023Slave_IO_Running: YesSlave_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: 423Relay_Log_Space: 991Until_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: 11Master_UUID: a730e3e0-cf9c-11ed-8845-000c29085fd5Master_Info_File: /Lcdmp3_mysqldata/mysqldata/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave 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: 
1 row in set (0.00 sec)

5.3主从配置错误怎么重做

mysql>stop slave;
mysql>reset slave；