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.
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:
Make a note of this location for the later steps.
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'.
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.
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'