英文:
How to synchronize replicationDB with masterDB in MariaDB
问题
我创建了 replicaDB，并且遇到了一个问题。
在创建它的过程中，我执行了以下步骤：
- 在 masterDB 上执行 FLUSH TABLES WITH READ LOCK;
- 使用 mysqldump 命令创建备份
- 导入备份到 replicaDB
- 执行 RESET SLAVE;，执行 CHANGE MASTER 命令，并在 replicaDB 上执行 START SLAVE
- 在 masterDB 上执行 UNLOCK TABLES;
通过这些步骤，replicaDB 中的数据与 masterDB 相同。然而，由于定时任务，masterDB 中在 "UNLOCK TABLES;" 之后插入了一个新记录（id = 159），但在 replicaDB 中没有同步。从 id = 160 开始，replicaDB 中的记录是同步的。
我应该如何解决这个问题？
masterDB: 192.168.30.123
replicationDB: 192.168.30.131
数据库：todo
复制用户：repli4
masterDB 的设置：
[mysqld]
bind-address = 0.0.0.0
binlog_format = ROW
max_binlog_size = 128M
expire_logs_days = 7
log-error=/var/log/mysql/error.log
log-bin-trust-function-creators = true
log-bin
binlog-format = mixed
log-basename =master
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
replicationDB 的设置：
[mysqld]
bind-address = 0.0.0.0
binlog_format = ROW
max_binlog_size = 128M
expire_logs_days = 7
log-error=/var/log/mysql/error.log
log-bin-trust-function-creators = true
server_id = 5
slave-skip-errors = all
replicate-do-db = todo
read_only = ON
log_bin = mysql-bin
rpl_semi_sync_slave_enabled=ON
replicaDB 中的记录
SHOW SLAVE STATUS/G
请问有人能帮助我吗？
英文:
I created replicaDB and I've got a problem.
While I was creating it, I did some steps below
- FLUSH TABLES WITH READ LOCK; on masterDB
- mysqldump command as well to make a backup
- import backup into replicaDB
- RESET SLAVE; ,did CHANGE MASTER command, and START SLAVE on replicaDB
- UNLOCK TABLES; on masterDB
Through those steps, the replicaDB has got the same data as masterDB has.
However, a new record(id = 159), due to cron job, was inserted into masterDB after "UNLOCK TABLES;" and it wasn't synchronized in replicaDB.
From id = 160, records are synchronized in replicaDB now.
How should I solve this problem?
masterDB:192.168.30.123
replicationDB:192.168.30.131
database:todo
replication user:repli4
masterDB's set up
[mysqld]
bind-address = 0.0.0.0
binlog_format = ROW
max_binlog_size = 128M
expire_logs_days = 7
log-error=/var/log/mysql/error.log
log-bin-trust-function-creators = true
log-bin
binlog-format = mixed
log-basename =master
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
replicationDB's set up
[mysqld]
bind-address = 0.0.0.0
binlog_format = ROW
max_binlog_size = 128M
expire_logs_days = 7
log-error=/var/log/mysql/error.log
log-bin-trust-function-creators = true
server_id = 5
slave-skip-errors = all
replicate-do-db = todo
read_only = ON
log_bin = mysql-bin
rpl_semi_sync_slave_enabled=ON
Could anyone help me, please?
答案1
得分: 1
看起来您的副本仍然可以从从属状态中正常读取，只是数据不同步的问题。在这里最简单的做法是重新备份和恢复。恢复后，您的副本应该与主数据库数据匹配，并且您的复制设置不应受影响。
- 在主数据库上运行
FLUSH TABLES WITH READ LOCK; - 在主数据库上通过
mysqldump -u用户名 -p 数据库名 > master_backup.sql备份主数据库 - 通过
mysql -u用户名 -p 数据库名 < master_backup.sql将备份导入副本数据库 - 在主数据库上运行
UNLOCK TABLES;
英文:
It seems like your replica is still reading just fine from the slave status, just a data de-sync issue. The simplest thing to do here is to redo the backup and restore. After the restore, your replica should match with your master data and your replication setup should be unaffected.
FLUSH TABLES WITH READ LOCK;on masterDB- backup master on masterDB via
mysqldump -uusername -p database_name > master_backup.sql - import backup into replicaDB via
mysql -uusername -p database_name < master_backup.sql UNLOCK TABLES;on masterDB
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论