Import and Export Large MySQL Database

For most of the time, web-based database management control panel such as phpMyAdmin does the job well for importing and exporting backups. However, as your database grows larger and larger, it will reach to a point where it can no longer be done using any web-based control panels anymore.

For instance, our database has reached 500mb just a couple of months ago and phpMyAdmin would stop halfway when doing the job and showing a timeout message. It’s a standard for web server to stop loading a page and declare as timeout once it waited for a certain amount of time. You can of course change the web server configuration to make the timeout really really long but that is not practical and may break your web server.

A better way would be using the command line tools came together with MySQL. Let’s try to import a large database using the command line tool. The first thing you need to do is to open up the command prompt, or terminal. For Windows operating system, you need to access to the bin folder in your MySQL installation directory, for example:

cd C:\xampp\mysql\bin

Then, use the following command to import the SQL file to your database.

mysql -u username -p database_name < file.sql

Then, a message will pop out and ask for the database password. Insert the correct password and wait for a while until all the data is being loaded to the database. If you see your mysql directory is showing in the next line, it means the operation has be done. Your command prompt should look something like this:

Next, we will export our database using the command line tool. This time, we will be using the mysqldump utility. To export your database, type in this command line:

mysqldump -u username -p database_name > file.sql

If you don’t specify a target destination for the file, it will be saved to the bin folder, in this case, C:\xampp\mysql\bin.

That’s all! It’s very easy and quick once you’re familiar with the command line.

Set MySQL Time Zone

Sometimes your MySQL database’s timezone is set to UTC time by default and does not follow your local timezone. In this case, you’ll need to manually change its configuration.

The configuration file can usually be found in mysql folder, which, depends on the operating system you’re running, could be installed in different directory.

Linux & Mac:

  • /usr/local/mysql/support-files/my.cnf
  • /etc/my.cnf
  • /etc/mysql/my.cnf

Windows:

  • C:/AppServ/MySQL/my.cnf
  • C:/Program Files/MySQL/MySQL Server 4.1/my.ini

Once you found the config file, open it up with text editor and add this line to the end of the file:

default-time-zone = '+08:00'

In this example, I’m change the time zone to GMT+8.

Next, you need to restart MySQL in order to see the effect. You could do so by opening up command prompt and type in this command:

Linux:

sudo service mysql restart

Windows:

net stop MySQL
net start MySQL

Mac:

sudo /usr/local/mysql/support-files/mysql.server restart

Now your MySQL should run in the correct time zone!