mysql主从配置
一、准备工作
-
准备两个版本一致的数据库。
-
确认主库开启二进制日志,并配置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;