mysqldump all databases from XAMPP on Windows

A quick note to hopefully save others the time I’ve just wasted. If you want to dump out all your databases, perhaps in preparation for an upgrade to XAMPP as I’m about to try:

  1. Open up a command prompt (in Win 8+ type ‘Command’ and press <Enter> from the Start screen);
  2. Type: cd\xampp\mysql\bin(or wherever your myslq.exe is located)
  3. Type: mysqldump –user=root –password=myrootpwd –all-databases > backup.sql

Other options for this command can be found on the MySQL site.

Note that you are doing this from the Windows Command prompt – don’t get distracted, as I was, by running the MySQL command line tool. If you try and run mysqldump from the:

mysql>

prompt, you’ll get various syntax errors such as:

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...' at line 1

However, if running the MySQL command line tool is what has brought you to this post, you launch this by following steps 1 & 2 above and then typing:

mysql --user=root --password=myrootpwd

When executing commands at the:

mysql>

prompt, don’t forget that it allows you to enter multiple lines at once and that clicking <Enter> will just give you a new line unless you finish your line with a semi-colon – this will tell MySQl that you want to execute the command.

Leave a Reply