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.
4 thoughts on “Backup and recover a MySQL database under command line”
Good tips, also you can write those lines to a .php file and run the file in your browser.
By the way, you can leave some comments in my blog http://www.liveserv.org too. Thanks.
Great tips. I do not have a lot of knowledge about this kind of thing but I am trying to learn.
ummm – you have to make a database before you can dump into it I’m trying to do that from the command line so i can make it into a script
But mysqldump is not working for MySql Command Line client in windows server 2008
..Can anyone give me better solution for that.
Comments are closed.