如果Mysql复制时Master和Slave有不同的数据库,如何重新同步Mysql DB?

发布于 2024-08-24 03:09:08 字数 306 浏览 6 评论 0原文

Mysql Server1 作为 MASTER 运行。
Mysql Server2 作为SLAVE 运行。

现在数据库复制正在从MASTERSLAVE进行。

Server2 已从网络中删除,并在 1 天后重新连接。此后,主从数据库中就会出现不匹配。

如何重新同步数据库,因为将数据库从主数据库恢复到从数据库后也无法解决问题?

Mysql Server1 is running as MASTER.
Mysql Server2 is running as SLAVE.

Now DB replication is happening from MASTER to SLAVE.

Server2 is removed from network and re-connect it back after 1 day. After this there is mismatch in database in master and slave.

How to re-sync the DB again as after restoring DB taken from Master to Slave also doesn't solve the problem ?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(13

忘羡 2024-08-31 03:09:08

这是从头开始重新同步主从复制的完整分步过程:

在主服务器上:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

并将最后一个命令的结果值复制到某处 。

在不关闭与客户端的连接(因为这会释放读锁)的情况下,发出命令来获取主服务器的转储:

mysqldump -u root -p --all-databases > /a/path/mysqldump.sql

现在您可以释放锁,即使转储尚未结束。为此,请在 MySQL 客户端中执行以下命令:

UNLOCK TABLES;

现在使用 scp 或您喜欢的工具将转储文件复制到从属服务器。

在从服务器上:

打开与 mysql 的连接并键入:

STOP SLAVE;

使用此控制台命令加载主服务器的数据转储:

mysql -uroot -p < mysqldump.sql

同步从服务器和主服务器日志:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

其中上述字段的值是您之前复制的值。

最后,输入:

START SLAVE;

要检查一切是否再次正常工作,输入:

SHOW SLAVE STATUS;

您应该看到:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

就是这样!

This is the full step-by-step procedure to resync a master-slave replication from scratch:

At the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

And copy the values of the result of the last command somewhere.

Without closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump -u root -p --all-databases > /a/path/mysqldump.sql

Now you can release the lock, even if the dump hasn't ended yet. To do it, perform the following command in the MySQL client:

UNLOCK TABLES;

Now copy the dump file to the slave using scp or your preferred tool.

At the slave:

Open a connection to mysql and type:

STOP SLAVE;

Load master's data dump with this console command:

mysql -uroot -p < mysqldump.sql

Sync slave and master logs:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

Where the values of the above fields are the ones you copied before.

Finally, type:

START SLAVE;

To check that everything is working again, after typing:

SHOW SLAVE STATUS;

you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

That's it!

挽容 2024-08-31 03:09:08

不幸的是,MySQL 站点上的相关文档已经过时了,并且充满了各种实战内容(例如 Interactive_timeout)。作为主导出的一部分,发出 FLUSH TABLES WITH READ LOCK 通常仅在与存储/文件系统快照(例如 LVM 或 zfs)协调时才有意义。

如果要使用 mysqldump,则应该依赖 --master-data 选项来防止人为错误并尽快释放主服务器上的锁。

假设主服务器是 192.168.100.50,从服务器是 192.168.100.51,每台服务器都配置了不同的 server-id,主服务器具有二进制日志记录,并且从服务器在 my.cnf 中具有 read-only=1

。能够在导入转储后立即开始复制,发出 CHANGE MASTER 命令但省略日志文件名和位置:

slaveserver> CHANGE MASTER TO MASTER_HOST='192.168.100.50', MASTER_USER='replica', MASTER_PASSWORD='asdmk3qwdq1';

在主服务器上发出 GRANT 供从服务器使用:

masterserver> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.100.51' IDENTIFIED BY 'asdmk3qwdq1';

使用压缩导出主服务器(在屏幕中)并自动捕获正确的日志文件二进制日志坐标:

mysqldump --master-data --all-databases --flush-privileges | gzip -1 > replication.sql.gz

将replication.sql.gz 文件复制到从站,然后使用zcat 将其导入到从站上运行的MySQL 实例:

zcat replication.sql.gz | mysql

通过向从站发出命令来启动复制:

slaveserver> START SLAVE;

可以选择更新/root/.my。在从服务器上创建一个 .cnf 来存储与主服务器相同的 root 密码。

如果您使用的是 5.1+,最好先将 master 的 binlog_format 设置为 MIXED 或 ROW。请注意,对于缺少主键的表,行记录的事件速度很慢。这通常比 binlog_format=statement(在主站上)的替代(默认)配置更好,因为它不太可能在从站上产生错误的数据。

如果您必须(但可能不应该)过滤复制,请使用从属选项replicate-wild-do-table=dbname.% 或replicate-wild-ignore-table=badDB.% 并仅使用binlog_format=row

此过程将在 mysqldump 命令期间保持主服务器上的全局锁定,但不会以其他方式影响主服务器。

如果您想使用 mysqldump --master-data --all-databases --single-transaction (因为您只使用 InnoDB 表),那么使用 MySQL Enterprise Backup 或名为 xtrabackup 的开源实现可能会更好(由佩尔科纳)

The documentation for this at the MySQL site is woefully out of date and riddled with foot-guns (such as interactive_timeout). Issuing FLUSH TABLES WITH READ LOCK as part of your export of the master generally only makes sense when coordinated with a storage/filesystem snapshot such as LVM or zfs.

If you are going to use mysqldump, you should rely instead on the --master-data option to guard against human error and release the locks on the master as quickly as possible.

Assume the master is 192.168.100.50 and the slave is 192.168.100.51, each server has a distinct server-id configured, the master has binary logging on and the slave has read-only=1 in my.cnf

To stage the slave to be able to start replication just after importing the dump, issue a CHANGE MASTER command but omit the log file name and position:

slaveserver> CHANGE MASTER TO MASTER_HOST='192.168.100.50', MASTER_USER='replica', MASTER_PASSWORD='asdmk3qwdq1';

Issue the GRANT on the master for the slave to use:

masterserver> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.100.51' IDENTIFIED BY 'asdmk3qwdq1';

Export the master (in screen) using compression and automatically capturing the correct binary log coordinates:

mysqldump --master-data --all-databases --flush-privileges | gzip -1 > replication.sql.gz

Copy the replication.sql.gz file to the slave and then import it with zcat to the instance of MySQL running on the slave:

zcat replication.sql.gz | mysql

Start replication by issuing the command to the slave:

slaveserver> START SLAVE;

Optionally update the /root/.my.cnf on the slave to store the same root password as the master.

If you are on 5.1+, it is best to first set the master's binlog_format to MIXED or ROW. Beware that row logged events are slow for tables which lack a primary key. This is usually better than the alternative (and default) configuration of binlog_format=statement (on master), since it is less likely to produce the wrong data on the slave.

If you must (but probably shouldn't) filter replication, do so with slave options replicate-wild-do-table=dbname.% or replicate-wild-ignore-table=badDB.% and use only binlog_format=row

This process will hold a global lock on the master for the duration of the mysqldump command but will not otherwise impact the master.

If you are tempted to use mysqldump --master-data --all-databases --single-transaction (because you only using InnoDB tables), you are perhaps better served using MySQL Enterprise Backup or the open source implementation called xtrabackup (courtesy of Percona)

奢华的一滴泪 2024-08-31 03:09:08

除非您直接写入从属服务器(Server2),否则唯一的问题应该是 Server2 丢失了自断开连接以来发生的任何更新。只需使用“START SLAVE;”重新启动从站即可应该让一切恢复正常。

Unless you are writing directly to the slave (Server2) the only problem should be that Server2 is missing any updates that have happened since it was disconnected. Simply restarting the slave with "START SLAVE;" should get everything back up to speed.

度的依靠╰つ 2024-08-31 03:09:08

我很晚才回答这个问题,但是我确实遇到了这个问题,经过多次搜索,我从布莱恩·肯尼迪那里找到了以下信息: http://plusbryan.com/mysql-replication-without-downtime

在 Master 上进行如下备份:
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql

现在,检查文件头并记下 MASTER_LOG_FILE 和 MASTER_LOG_POS 的值。您稍后将需要它们:
头转储.sql -n80 | grep "MASTER_LOG"

将“dump.sql”文件复制到从站并恢复它:
mysql -u mysql-user -p ~/dump.sql

连接到 Slave mysql 并运行如下命令:
将 MASTER 更改为 MASTER_HOST='master-server-ip', MASTER_USER='replication-user', MASTER_PASSWORD='slave-server-password', MASTER_LOG_FILE='上面的值', MASTER_LOG_POS=上面的值; START SLAVE;

查看 Slave 的进度:
SHOW SLAVE STATUS;

如果一切正常,Last_Error将为空,Slave_IO_State将报告“等待master发送事件”。
查找 Seconds_Behind_Master,它指示落后了多远。
YMMV。 :)

I am very late to this question, however I did encounter this problem and, after much searching, I found this information from Bryan Kennedy: http://plusbryan.com/mysql-replication-without-downtime

On Master take a backup like this:
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql

Now, examine the head of the file and jot down the values for MASTER_LOG_FILE and MASTER_LOG_POS. You will need them later:
head dump.sql -n80 | grep "MASTER_LOG"

Copy the "dump.sql" file over to Slave and restore it:
mysql -u mysql-user -p < ~/dump.sql

Connect to Slave mysql and run a command like this:
CHANGE MASTER TO MASTER_HOST='master-server-ip', MASTER_USER='replication-user', MASTER_PASSWORD='slave-server-password', MASTER_LOG_FILE='value from above', MASTER_LOG_POS=value from above; START SLAVE;

To check the progress of Slave:
SHOW SLAVE STATUS;

If all is well, Last_Error will be blank, and Slave_IO_State will report “Waiting for master to send event”.
Look for Seconds_Behind_Master which indicates how far behind it is.
YMMV. :)

尐籹人 2024-08-31 03:09:08

我认为,Maatkit utilits 对您有帮助!您可以使用 mk-table-sync。请参阅此链接:http://www.maatkit.org/doc/mk-表同步.html

I think, Maatkit utilits helps for you! You can use mk-table-sync. Please see this link: http://www.maatkit.org/doc/mk-table-sync.html

心奴独伤 2024-08-31 03:09:08

这是当 mysql 从站不同步时我通常会做的事情。我看过 mk-table-sync 但认为风险部分看起来很可怕。

在 Master 上:

SHOW MASTER STATUS

输出的列(文件、位置)很快就会对我们有用。

在从机上:

STOP SLAVE

然后转储主数据库并将其导入到从数据库。

然后运行以下命令:

CHANGE MASTER TO
  MASTER_LOG_FILE='[File]',
  MASTER_LOG_POS=[Position];
START SLAVE;

其中 [File] 和 [Position] 是上面运行的“SHOW MASTER STATUS”输出的值。

希望这有帮助!

Here is what I typically do when a mysql slave gets out of sync. I have looked at mk-table-sync but thought the Risks section was scary looking.

On Master:

SHOW MASTER STATUS

The outputted columns (File, Position) will be of use to us in a bit.

On Slave:

STOP SLAVE

Then dump the master db and import it to the slave db.

Then run the following:

CHANGE MASTER TO
  MASTER_LOG_FILE='[File]',
  MASTER_LOG_POS=[Position];
START SLAVE;

Where [File] and [Position] are the values outputted from the "SHOW MASTER STATUS" ran above.

Hope this helps!

甚是思念 2024-08-31 03:09:08

Master:

mysqldump -u root -p --all-databases --master-data | gzip > /tmp/dump.sql.gz  

scp master:/tmp/dump.sql.gz Slave:/tmp/ 将转储文件移动到从服务器

Slave:

STOP SLAVE;

zcat /tmp/dump.sql.gz | zcat /tmp/dump.sql.gz | mysql -u root -p

START SLAVE;
SHOW SLAVE STATUS;  

注意
在 master 上,您可以运行 SET GLOBAL expire_logs_days = 3 来将 binlog 保留 3 天,以防出现从属问题。

Master:

mysqldump -u root -p --all-databases --master-data | gzip > /tmp/dump.sql.gz  

scp master:/tmp/dump.sql.gz slave:/tmp/ Move dump file to slave server

Slave:

STOP SLAVE;

zcat /tmp/dump.sql.gz | mysql -u root -p

START SLAVE;
SHOW SLAVE STATUS;  

NOTE:
On master you can run SET GLOBAL expire_logs_days = 3 to keep binlogs for 3 days in case of slave issues.

最丧也最甜 2024-08-31 03:09:08

跟进大卫的回答...

使用 SHOW SLAVE STATUS\G 将给出人类可读的输出。

Following up on David's answer...

Using SHOW SLAVE STATUS\G will give human-readable output.

哆啦不做梦 2024-08-31 03:09:08

这是一个完整的答案,希望能够帮助其他人...


我想使用主服务器和从服务器设置 mysql 复制,因为我唯一知道的是它使用日志文件进行同步,如果从服务器脱机并获取不同步,理论上它应该只需要连接回其主设备并从其停止的位置继续读取日志文件,正如用户 malonso 提到的那样。

所以这里是配置主从后的测试结果,如下所示: http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html ...

如果您使用推荐的主/从配置并且不写入从机,他我是对的(就 mysql-server 5.x 而言)。我什至不需要使用“START SLAVE;”,它只是追上了它的主人。但默认情况下,每 60 秒重试 88000 次,所以我猜如果你耗尽了,你可能必须启动或重新启动从属服务器。无论如何,对于像我这样想知道从属设备离线并再次备份是否需要手动干预的人……不,不需要。

也许原始海报的日志文件已损坏?但很可能不仅仅是服务器离线一天。


从 /usr/share/doc/mysql-server-5.1/README.Debian.gz 中提取,这对于非 debian 服务器可能也有意义:

* FURTHER NOTES ON REPLICATION
===============================
If the MySQL server is acting as a replication slave, you should not
set --tmpdir to point to a directory on a memory-based filesystem or to
a directory that is cleared when the server host restarts. A replication
slave needs some of its temporary files to survive a machine restart so
that it can replicate temporary tables or LOAD DATA INFILE operations. If
files in the temporary file directory are lost when the server restarts,
replication fails.

您可以使用类似 sql 的内容:显示诸如“tmpdir”之类的变量;找出答案。

Here is a complete answer that will hopefully help others...


I want to setup mysql replication using master and slave, and since the only thing I knew was that it uses log file(s) to synchronize, if the slave goes offline and gets out of sync, in theory it should only need to connect back to its master and keep reading the log file from where it left off, as user malonso mentioned.

So here are the test result after configuring the master and slave as mentioned by: http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html ...

Provided you use the recommended master/slave configuration and don't write to the slave, he and I where right (as far as mysql-server 5.x is concerned). I didn't even need to use "START SLAVE;", it just caught up to its master. But there is a default 88000 something retries every 60 second so I guess if you exhaust that you might have to start or restart the slave. Anyways, for those like me who wanted to know if having a slave going offline and back up again requires manual intervention.. no, it doesn't.

Maybe the original poster had corruption in the log-file(s)? But most probably not just a server going off-line for a day.


pulled from /usr/share/doc/mysql-server-5.1/README.Debian.gz which probably makes sense to non debian servers as well:

* FURTHER NOTES ON REPLICATION
===============================
If the MySQL server is acting as a replication slave, you should not
set --tmpdir to point to a directory on a memory-based filesystem or to
a directory that is cleared when the server host restarts. A replication
slave needs some of its temporary files to survive a machine restart so
that it can replicate temporary tables or LOAD DATA INFILE operations. If
files in the temporary file directory are lost when the server restarts,
replication fails.

you can use something sql like: show variables like 'tmpdir'; to find out.

怕倦 2024-08-31 03:09:08

添加到流行的答案以包含此错误:

"ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO",

从从站一次性复制:

在一个终端窗口中:

mysql -h <Master_IP_Address> -uroot -p

连接后,

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

状态显示如下:请注意,位置号不同!

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      98  | your_DB      |                  |
+------------------+----------+--------------+------------------+

导出转储,类似于他描述的“使用另一个终端”!

退出并连接到您自己的数据库(即从属数据库):

mysql -u root -p

输入以下命令:

STOP SLAVE;

如上所述导入转储(当然,在另一个终端中!)并输入以下命令:

RESET SLAVE;
CHANGE MASTER TO 
  MASTER_HOST = 'Master_IP_Address', 
  MASTER_USER = 'your_Master_user', // usually the "root" user
  MASTER_PASSWORD = 'Your_MasterDB_Password', 
  MASTER_PORT = 3306, 
  MASTER_LOG_FILE = 'mysql-bin.000001', 
  MASTER_LOG_POS = 98; // In this case

登录后,设置 server_id 参数(通常,对于新的/非复制的数据库,默认情况下未设置),

set global server_id=4000;

现在,启动从属服务器。

START SLAVE;
SHOW SLAVE STATUS\G;

输出应该与他描述的相同。

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

注意:复制后,主从共享相同的密码!

Adding to the popular answer to include this error:

"ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO",

Replication from slave in one shot:

In one terminal window:

mysql -h <Master_IP_Address> -uroot -p

After connecting,

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The status appears as below: Note that position number varies!

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      98  | your_DB      |                  |
+------------------+----------+--------------+------------------+

Export the dump similar to how he described "using another terminal"!

Exit and connect to your own DB(which is the slave):

mysql -u root -p

The type the below commands:

STOP SLAVE;

Import the Dump as mentioned (in another terminal, of course!) and type the below commands:

RESET SLAVE;
CHANGE MASTER TO 
  MASTER_HOST = 'Master_IP_Address', 
  MASTER_USER = 'your_Master_user', // usually the "root" user
  MASTER_PASSWORD = 'Your_MasterDB_Password', 
  MASTER_PORT = 3306, 
  MASTER_LOG_FILE = 'mysql-bin.000001', 
  MASTER_LOG_POS = 98; // In this case

Once logged, set the server_id parameter (usually, for new / non-replicated DBs, this is not set by default),

set global server_id=4000;

Now, start the slave.

START SLAVE;
SHOW SLAVE STATUS\G;

The output should be the same as he described.

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

Note: Once replicated, the master and slave share the same password!

浅忆 2024-08-31 03:09:08

奴隶一下

有时你只需要经常踢

stop slave;    
reset slave;    
start slave;    
show slave status;

,奴隶们,他们只是被卡住了:)

sometimes you just need to give the slave a kick too

try

stop slave;    
reset slave;    
start slave;    
show slave status;

quite often, slaves, they just get stuck guys :)

超可爱的懒熊 2024-08-31 03:09:08

使用LVM重建从站

这是我们使用Linux LVM重建MySQL从站的方法。这保证了快照的一致性,同时要求主服务器的停机时间非常短。

在 MySQL 主服务器上将 innodb 最大脏页百分比设置为零。这将强制 MySQL 将所有页面写入磁盘,从而显着加快重启速度。

set global innodb_max_dirty_pages_pct = 0;

要监视脏页数,请运行命令。

mysqladmin ext -i10 | grep dirty

一旦脏页数停止减少,您就可以继续了。接下来重置master以清除旧的bin日志/中继日志:

RESET MASTER;

执行lvdisplay以获取LV路径

lvdisplay

输出将如下所示

--- Logical volume ---
LV Path                /dev/vg_mysql/lv_data
LV Name                lv_data
VG Name                vg_mysql

使用命令关闭master数据库

service mysql stop

下一步拍摄快照,mysql_snapshot将是新的逻辑卷名称。如果二进制日志位于操作系统驱动器上,则也需要对其进行快照。

lvcreate --size 10G --snapshot --name mysql_snapshot /dev/vg_mysql/lv_data

使用命令再次启动 master

service mysql start

将脏页设置恢复为默认值

set global innodb_max_dirty_pages_pct = 75;

再次运行 lvdisplay 以确保快照存在且可见

lvdisplay

输出:

--- Logical volume ---
LV Path                /dev/vg_mysql/mysql_snapshot
LV Name                mysql_snapshot
VG Name                vg_mysql

挂载快照

mkdir /mnt/mysql_snapshot
mount /dev/vg_mysql/mysql_snapshot /mnt/mysql_snapshot

如果您有现有的 MySQL 从属设备正在运行,则需要停止它

service mysql stop

接下来您需要清除 MySQL 数据文件夹

cd /var/lib/mysql
rm -fr *

回到主人那里。现在将快照 rsync 到 MySQL 从机

rsync --progress -harz /mnt/mysql_snapshot/ targethostname:/var/lib/mysql/

一旦 rsync 完成,您可以卸载并删除快照

umount /mnt/mysql_snapshot
lvremove -f /dev/vg_mysql/mysql_snapshot

如果旧复制用户不存在或密码未知,则在主服务器上创建复制用户

GRANT REPLICATION SLAVE on *.* to 'replication'@'[SLAVE IP]' identified by 'YourPass';

验证 /var/lib/mysql 数据文件是否拥有由 mysql 用户执行,如果是这样,您可以省略以下命令:

chown -R mysql:mysql /var/lib/mysql

接下来记录 binlog 位置

ls -laF | grep mysql-bin

您将看到类似的内容

..
-rw-rw----     1 mysql mysql  1073750329 Aug 28 03:33 mysql-bin.000017
-rw-rw----     1 mysql mysql  1073741932 Aug 28 08:32 mysql-bin.000018
-rw-rw----     1 mysql mysql   963333441 Aug 28 15:37 mysql-bin.000019
-rw-rw----     1 mysql mysql    65657162 Aug 28 16:44 mysql-bin.000020

这里,主日志文件是序列中的最高文件编号,bin 日志位置是文件大小。记录这些值:

master_log_file=mysql-bin.000020
master_log_post=65657162

接下来启动从站 MySQL

service mysql start

通过执行以下命令在从站上执行更改主命令:

CHANGE MASTER TO 
master_host="10.0.0.12", 
master_user="replication", 
master_password="YourPass", 
master_log_file="mysql-bin.000020", 
master_log_pos=65657162; 

最后启动从站

SLAVE START;

检查从站状态:

SHOW SLAVE STATUS;

确保从站 IO 正在运行并且没有连接错误。祝你好运!

我最近在我的博客上写了这篇文章,可以在这里找到...那里有一些更多的细节,但故事是一样的。

http://www.juhavehnia.com/ 2015/05/rebuilding-mysql-slave-using-linux-lvm.html

Rebuilding the slave using LVM

Here is the method we use to rebuild MySQL slaves using Linux LVM. This guarantees a consistent snapshot while requiring very minimal downtime on your master.

Set innodb max dirty pages percent to zero on the master MySQL server. This will force MySQL to write all the pages to the disk which will significantly speed up the restart.

set global innodb_max_dirty_pages_pct = 0;

To monitor the number of dirty pages run the command

mysqladmin ext -i10 | grep dirty

Once the number stop decreasing you have reach the point to continue. Next reset the master to clear the old bin logs / relay logs:

RESET MASTER;

Execute lvdisplay to get LV Path

lvdisplay

Output will look like this

--- Logical volume ---
LV Path                /dev/vg_mysql/lv_data
LV Name                lv_data
VG Name                vg_mysql

Shutdown the master database with command

service mysql stop

Next take a snaphot, mysql_snapshot will be the new logical volume name. If binlogs are place on the OS drive those need to be snapshot as well.

lvcreate --size 10G --snapshot --name mysql_snapshot /dev/vg_mysql/lv_data

Start master again with command

service mysql start

Restore dirty pages setting to the default

set global innodb_max_dirty_pages_pct = 75;

Run lvdisplay again to make sure the snapshot is there and visible

lvdisplay

Output:

--- Logical volume ---
LV Path                /dev/vg_mysql/mysql_snapshot
LV Name                mysql_snapshot
VG Name                vg_mysql

Mount the snapshot

mkdir /mnt/mysql_snapshot
mount /dev/vg_mysql/mysql_snapshot /mnt/mysql_snapshot

If you have an existing MySQL slave running you need to stop it

service mysql stop

Next you need to clear MySQL data folder

cd /var/lib/mysql
rm -fr *

Back to master. Now rsync the snapshot to the MySQL slave

rsync --progress -harz /mnt/mysql_snapshot/ targethostname:/var/lib/mysql/

Once rsync has completed you may unmount and remove the snapshot

umount /mnt/mysql_snapshot
lvremove -f /dev/vg_mysql/mysql_snapshot

Create replication user on the master if the old replication user doesn't exist or password is unknown

GRANT REPLICATION SLAVE on *.* to 'replication'@'[SLAVE IP]' identified by 'YourPass';

Verify that /var/lib/mysql data files are owned by the mysql user, if so you can omit the following command:

chown -R mysql:mysql /var/lib/mysql

Next record the binlog position

ls -laF | grep mysql-bin

You will see something like

..
-rw-rw----     1 mysql mysql  1073750329 Aug 28 03:33 mysql-bin.000017
-rw-rw----     1 mysql mysql  1073741932 Aug 28 08:32 mysql-bin.000018
-rw-rw----     1 mysql mysql   963333441 Aug 28 15:37 mysql-bin.000019
-rw-rw----     1 mysql mysql    65657162 Aug 28 16:44 mysql-bin.000020

Here the master log file is the highest file number in sequence and bin log position is the file size. Record these values:

master_log_file=mysql-bin.000020
master_log_post=65657162

Next start the slave MySQL

service mysql start

Execute change master command on the slave by executing the following:

CHANGE MASTER TO 
master_host="10.0.0.12", 
master_user="replication", 
master_password="YourPass", 
master_log_file="mysql-bin.000020", 
master_log_pos=65657162; 

Finally start the slave

SLAVE START;

Check slave status:

SHOW SLAVE STATUS;

Make sure Slave IO is running and there are no connection errors. Good luck!

I recently wrote this on my blog which is found here... There are few more details there but the story is the same.

http://www.juhavehnia.com/2015/05/rebuilding-mysql-slave-using-linux-lvm.html

天气好吗我好吗 2024-08-31 03:09:08

我们正在使用 MySQL 的主-主复制技术,如果一台 MySQL 服务器说 1 从网络中删除,它会在连接恢复后重新连接,并且传输在网络中的服务器 2 中提交的所有记录到恢复后失去连接的服务器1。
默认情况下,MySQL 中的从线程每 60 秒重试连接到其主线程。该属性可以更改,因为 MySQL 有一个标志“master_connect_retry=5”,其中 5 是以秒为单位。这意味着我们希望每 5 秒后重试一次。

但是您需要确保失去连接的服务器不会在数据库中进行任何提交,因为您会收到重复的密钥错误错误代码:1062

We are using master-master replication technique of MySQL and if one MySQL server say 1 is removed from the network it reconnects itself after the connection are restored and all the records that were committed in the in the server 2 which was in the network are transferred to the server 1 which has lost the connection after restoration.
Slave thread in the MySQL retries to connect to its master after every 60 sec by default. This property can be changed as MySQL ha a flag "master_connect_retry=5" where 5 is in sec. This means that we want a retry after every 5 sec.

But you need to make sure that the server which lost the connection show not make any commit in the database as you get duplicate Key error Error code: 1062

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文