Locally set up mysql on command line for large sql imports

When you need to restore a live database to a local environment, you'll often come across the problem of uploads timing out due to size restrictions and the resources required to process a large SQL file. One option is to update your php.ini settings to remove the restrictions that will lead to timeouts, the other option is to do it via the command line. 

In this post we're going to look at setting up MySQL on command line for a local machine (i.e. one using server software like WAMPSERVER or XAMPP), and then importing a large SQL file. 

Setup MySQL on command line for WAMPSERVER / XAMPP

Pre-requisites

To set this up you're going to need to know your MySQL executable file path, this will vary depending on your server software (i.e. WAMP / XAMPP) and the version, for WAMP the default configuration will normally be stored here:

C:\wamp\bin\mysql\mysql5.5.24\bin

For xampp:

c:\xampp\mysql\bin

Make a note of this location for the later steps.

Step by step instructions

  • From the start menu right click 'My Computer' and click 'Properties'
  • On the left hand side choose 'Advanced System Settings'
  • Click the 'Environment Variables' button at the bottom
  • Find the 'Path' Variable and click 'Edit'
  • In the 'Variable value:' at the end of the field append a semi colon (;) followed by your mysql exectutable path (note, don't include the actual file name, just the path)
  • Hit 'OK' on all the open dialogs to save and close

What we've just done here is told the windows command line to search in the path you supplied when looking for executable files, which will include 'mysql.exe'. To test if this works open up a new command line and type 'mysql'.

Importing files via command line

  • Open a command line
  • Login to mysql as root (assuming no password locally)

    mysql -u root
  • Create your database

    CREATE DATABASE `my_database`;
  • Select the database you want to import to:

    USE `my_database`;
  • Run the import (adjusting the path to your SQL file):

    SOURCE c:\database.sql

 

Gotchas / Troubleshooting


ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)

Check that XAMPP / WAMP is actually switched on and that you can connect to it normally via phpMyAdmin. This connection error means it is using your mysql.exe file fine, but it isn't able to use your local mysql installation. If your server is running fine, try locating your 'mysqld.exe' (note the 'd') file in the same folder as your 'mysql.exe' file, right click and use the second 'run as...' option.

Syntax error when the query looks fine!

Make sure you're using the right quotes on the command line, ` is different to '. In the example below, the first query will execute fine, the second will fail because of the quote types:

CREATE DATABASE `my_database`
CREATE DATABASE 'my_database'

Sign Up
comments powered by Disqus
Sign Up

Popular Tags

350x250

Need a web developer?

If you'd like to work with code synthesis on your next project get in touch via the contact page.