Importing large sql file into MySQL database

The easiest way to import an .sql file into MySQL database is use phpMyAdmin web interface.

  • Login into phpMyAdmin control panel
  • Select your database
  • Click on Import tab
  • Select your .sql file, click Go and you are done


Problem with this is that PHP configuration normally limits size of files you can upload. There should be a notice on phpMyAdmin Import tab telling what's the maximum file size you can upload. If you exceed this limit you will get an error similar to:
You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit.
or
No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration.

You have a few options to overcome this limitation:

Option 1 - Compress SQL file

Compress you .sql file using zip, gzip or bzip2 method. SQL files basically are plain text files so they compress quite well. If you are still exceeding the limit use one of the other options.

Option 2 - Increase PHP file size limit

Open your php.ini file. If you not sure where your php.ini is located, check here.

Find following lines
post_max_size
upload_max_filesize
and increase values to required size in MB. E.g.
post_max_size = 10M
upload_max_filesize = 10M

Save the file and restart apache:
/etc/init.d/apache2 restart

After import is completed you may want to restore original values.

Option 3 - use mysql command in terminal

You can bypass phpMyAdmin altogether and do the import via terminal.

Upload your sql file to the web server and run flowing command in terminal:
mysql -u <username> -p <database> < /path/file.sql
where:
<username> - your MySQL username. e.g. root
<database> - database you are importing to
/path/file.sql - full path to your .sql file

It will prompt you for your MySQL password and complete the import.

Tested on Ubuntu web server

Ubuntu Server Tips

Check remaining disk space

df -h
Reports free space on each mount.

Monitor web server in real time

tail -f /var/www/yoursite/logs/access.log
Reads Apache access long file and displays changes in real time. Your log file may be in different location depending on settings.

Check Ubuntu Server Linux version

Ubuntu Server doesn't have GUI, but you can find version using number of terminal commands

lsb_release -a

cat /etc/issue

cat /proc/version

uname -a

Set up Linux (Ubuntu) server to synchronise clock with NTP time server

ntupdate

ntupdate comes built in into Ubintu server. To manually adjust server time run:
sudo ntpdate ntp.ubuntu.com
You can choose another ntp server instead of  ntp.ubuntu.com

ntpd

To automatically keep server time up to date use ntp.

Install ntpd:
sudo apt-get install ntp

ntpd should start working immediately without any additional configuration. However you obviously can adjust settings if you wish.

Configuration

ntpd configuration file is located in /etc/ntp.conf

Default server is server ntp.ubuntu.com
To use additional servers just add them bellow server ntp.ubuntu.com. e.g.
# You do need to talk to an NTP server or two (or three).
server ntp.ubuntu.com
server pool.ntp.org


To enable logging uncomment:
statsdir /var/log/ntpstats/
logs will be placed in /var/log/ntpstats/

Useful commands

Reastart ntp
sudo /etc/init.d/ntp restart
Stop ntp
sudo /etc/init.d/ntp stop
Start ntp
sudo /etc/init.d/ntp start

List peers and status
ntpq -p

Check in ntpd process is running
ps ax | grep ntp

Ubuntu Server 10.10




Newsletter

Subscribe to receive occasional updates on new posts.
Your email will not be used for any other purpose and you can unsubscribe at any time.
Please wait