0

I have a master database and a slave one. Recently, the slave has not synced with the master. So, I tried running mysqldump command to the master:

mysqldump -h <master_host> -u <user> -p <database_name> --databases <database_name>--triggers --routines --master-data=1 --single-transaction >> MySQLData.sql

And I got a file with 80GB. Then, I ran mysql -u <user> -p <database_name> < MySQLData.sql to import the master data. But the disk capacity was nearly full. Do we have any solution for this issue to avoid the full disk problem?

2 Answers 2

0

You can compress the file when dumping.

sudo mysqldump -h <master_host> -u <user> -p <database_name> --databases <database_name>--triggers --routines --master-data=1 --single-transaction | gzip >/root/MySQLData.sql.gz

Then import the compressed file.

sudo gzip -dc /root/MySQLData.sql.gz | mysql -u root -p <database_name>

-d --decompress --uncompress

-c --stdout --to-stdout Write output on standard output; keep original files unchanged. If there are several input files, the output consists of a sequence of independently compressed members. To obtain better compression, concatenate all input files before compressing them.

1
  • (aka "gunzip"?)
    – Rick James
    Nov 15, 2022 at 1:08
0

I gather that the servers are so full of data that there is not enough room for a backup -- or close. That is a bad situation to get into. This also applies to ALTERing a big table, which cannot benefit from compression.

Plan A:

Don't have the dump on the same machine.

You already dump the data to some machine there than the Primary. Perhaps that should be a third machine, not the Replica.

mysqldump -h Primary ... > local.sql
mysql     -h Replica ... < local.sql 

Plan B: (A + compression)

To add compression, too, then be sure to do it on the appropriate side of the network. This avoids the network being the bottleneck, but could lead to a CPU problem. And it probably involves ssh or having a script that is run remotely.

Plan C:

Do both steps at the same time -- thereby avoiding the 80GB file.

mysqldump -h Primary ... |
mysql     -h Replica ...

(I suspect adding gzip would unnecessarily slow down this technique.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.