英文:
How to synchronize replicationDB with masterDB in MariaDB
问题
我创建了一个 replicaDB，但遇到了一个问题。在创建 replicaDB 的过程中，我执行了以下步骤：
- 在 masterDB 上执行了 "FLUSH TABLES WITH READ LOCK;" 命令。
- 使用 mysqldump 命令备份了数据。
- 将备份导入到 replicaDB 中。
- 在 replicaDB 上执行了 "RESET SLAVE;"、"CHANGE MASTER" 和 "START SLAVE" 命令。
- 在 masterDB 上执行了 "UNLOCK TABLES;" 命令。
通过这些步骤，replicaDB 中的数据与 masterDB 中的数据相同。然而，由于定时任务的原因，在执行完 "UNLOCK TABLES;" 命令后，masterDB 中插入了一条新记录（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
有人可以帮助我吗？
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论