Share This Article:

Subscribe to Kavoir:

Yes you can log into phpmyadmin, backup your database by simply using Export and recover it by using Import. However all lamp servers come with a max upload file size, limited by the smaller of upload_max_filesize and post_max_size while also restricted by php.ini configurations such as memory_limit and max_input_time. What if you’ve got a huge database of hundreds of thousands of records taking up a few dozens of megabytes or even more?

Use MySQL command line to fulfill the same task.

Backup a database to sqlfile.sql:

mysqldump -h localhost -u username -p dbname > sqlfile.sql

After dumping it as a SQL text file, you may want to first zip it using the following command:

tar zcf sqlfile.sql.tar.gz sqlfile.sql

And download it to your local computer to be kept safe. When an emergency occurs, upload sqlfile.sql.tar.gz to your web server and unzip it:

tar zxf sqlfile.sql.tar.gz

Now that you have sqlfile.sql, you may proceed to restore it back into database, using the command below.

Recover a database from sqlfile.sql:

mysql -h localhost -u username -p dbname < sqlfile.sql

Beware of the less than / greater than mark that’s representing operation directions.

Change localhost to your own mysql server address, username to your own mysql account username and dbname to the name of the database to be backed up or recovered. Straight enough.

Also notice that MySQL user password is not specified in the command line but you will be prompted to enter it after hitting enter.

Share This Article:

Subscribe to Kavoir:

You should also read:

• mysql command line character set option for importing SQL files encoded in UTF8
• MySQL: How to backup ALL databases as root with mysqldump at once?
• Essential SSH – 19 Linux SSH Commands You Simply Cannot Live Without
• MySQL: LOAD DATA LOCAL INFILE only imports 1 or 2 rows?
• MySQL: Get the exact size of a database by SQL query