Search code examples
mysqlwampwampserver

How to create dump of a single database?

I am using wampserver 64 bit. I started the MySQL console via the wamp tray menu icon and executed this command as seen in the documentation.

mysqldump magento_live > dump.sql;

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump magento_live > dump.sql' at line 1

I also tried it like in this answer:

mysqldump -u root -p root -h localhost magento_live > magentoLiveDump.sql;

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -u root -p root -h localhost magento_live > magentoLiveDump.sql' at line 1

I am using MySQL 5.7

I tried it via CMD by using this command:

C:\wamp64\bin\mysql\mysql5.7.21\bin>C:\wamp64\bin\mysql\mysql5.7.21\bin\mysql.exe mysqldump magento_live > test.sql

A file is created, but this is the content:

C:\wamp64\bin\mysql\mysql5.7.21\bin\mysql.exe Ver 14.14 Distrib 5.7.21, for Win64 (x86_64) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Usage: C:\wamp64\bin\mysql\mysql5.7.21\bin\mysql.exe [OPTIONS] [database] -?, --help Display this help and exit. -I, --help Synonym for -? --auto-rehash Enable automatic rehashing. One doesn't need to use 'rehash' to get table and field completion, but startup and reconnecting may take a longer time. Disable with --disable-auto-rehash. (Defaults to on; use --skip-auto-rehash to disable.) -A, --no-auto-rehash

[...]

Solution

  • The command mysqldump will not run from MySQL CLI, you will have to run it from the windows command prompt (CMD) or from the Powershell.

    You have to start a CMD or Powershell and navigate to the bin folder inside the MySQL directory (wamp64\bin\mysql\mysql5.7.21\bin).

    Then you have to execute the command like this:

    mysqldump -u[USERNAME] -p target_database > target_dump_name.sql

    Important: There is no space allowed between the option and the value.

    WRONG: mysqldump -u root -p target_database > target_dump_name.sql

    CORRECT: mysqldump -uroot -p target_database > target_dump_name.sql

    Example

    CMD:

    mysqldump -uroot -p magento_live > magento_live_dump.sql

    Powershell:

    .\mysqldump -uroot -p magento_live > magento_live_dump.sql

    Note: In Powershell you will have to add .\ before the command.