Backup and recover a MySQL database under command line

• Tweet

• Tweet

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.