2

I am trying to add a slave MySQL server to an existing server. I ran the following mysqldump on the master to prepare the data for the slave initially.

mysqldump.exe -u ... -p... --single-transaction --flush-logs --skip-lock-tables --master-data=2 > all-dbs.sql

But after a while, it gave the following error;

Program 'mysqldump.exe' failed to run: Exception of type 'System.OutOfMemoryException' was thrown.At line:1 char:1
+ .\mysqldump.exe -u ... -p... --skip-lock-tables --single-tran ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~.
At line:1 char:1
+ .\mysqldump.exe -u ... -p... --skip-lock-tables --single-tran ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ResourceUnavailable: (:) [], ApplicationFailedException
    + FullyQualifiedErrorId : NativeCommandFailed

The master MySQL server has 3.5GB memory, and the data is about 5GB. It is running MySQL 5.7. Any help would be greatly appreciated. Thanks.

4
  • depending from what memory You have free (not cached and not used by mysql) it could be variants, but why not make a dump from slave? mysqldump -u -p --host=master_ip
    – a_vlad
    Dec 9, 2016 at 9:37
  • What are you using to actually spawn the mysqldump command? Obviously you are not simply typing it at a shell prompt. One thought is that you need the --opt option, which should be enabled by default but might be disabled in local config. This option implies the poorly-named --quick option, which reduces memory consumption while creating a dump, and might also be disabled, so adding --quick might also be helpful. Dec 9, 2016 at 11:37
  • Please show us your my.cnf -- it sounds like something is set too high.
    – Rick James
    Dec 9, 2016 at 17:37
  • The server is Azure VM - 2 core with 3.5 GB. I used windows powershell to run the mysqldump command. After creating 4+GB output file, I received the error message. Since I was in a hurry, I changed the VM's config to 4-core and 7GB, and was successfully run the mysqldump. However, I was not able to import the file into the slave server. It complained that there are binary characters in the line 1. I ran mysqldump again on the master, but used windows cmd.exe (not powershell). This worked fine with no error, and also the output was only 2.5 GB.
    – Young
    Dec 10, 2016 at 20:50

1 Answer 1

1

Just like Michael - sqlbot suggested, --quick would help. Official MySQL Doc states:

--quick, -q

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_quick

Regarding the PowerShell issue, there's a note on MySQL Doc:

Note:

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:

shell> mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see Section 11.1.4, “Connection Character Sets and Collations”), so the dump file will not load correctly. To work around this issue, use the --result-file option, which creates the output in ASCII format:

shell> mysqldump [options] --result-file=dump.sql

http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .