Read back dump file
You can read the dump file back into the server like this:
mysql db-name < backup-file.sql
To restore database called sales, first create the database sales:
$ mysql -u root -p
Now create database called sales using SQL statement:
mysql> CREATE DATABASE sales; mysql> quit;
Now restore database, enter:
$ mysql -u root -p sales < /path/to/sales-backup-file.sql
Backup and Restore MySQL Databases
It is a good idea to backup your MySQL data occasionally as a precautionary measure. It is also a good idea to create a backup before making any major changes, incase something goes wrong and you need to revert to the unmodified version. Database backups can also be used to transfer your database from one server to another if you change web hosts.
From a command prompt, you can backup your entire database using this line:
mysqldump -u user_name -p your_password database_name > File_name.sql
Let’s assume that:
Database Name: BobsData
mysqldump -u bobbyjoe -p happy234 BobsData > BobBackup.sql
This will backup the database to a file called BobBackup.sql
While automated backups are important, sometimes you just want to take a quick and dirty snapshot before making a change to your data. When changing files in Linux, you can simply cp a file to another name, and cp it back if your change does not work out. With MySQL, it is not quite that simple, but it is by no means difficult.
Creating A Backup
The mysqldump command is used to create textfile “dumps” of databases managed by MySQL. These dumps are just files with all the SQL commands needed to recreate the database from scratch.
If you want to back up a single database, you merely create the dump and send the output into a file, like so:
mysqldump database_name > database_name.sql
Multiple databases can be backed up at the same time:
mysqldump --databases database_one database_two > two_databases.sql
It is also simple to back up all of the databases on a server:
mysqldump --all-databases > all_databases.sql
Restoring a Backup
Since the dump files are just SQL commands, you can restore the database backup by telling mysql to run the commands in it and put the data into the proper database.
mysql database_name < database_name.sql
If you are trying to restore a single database from dump of all the databases, you have to let mysql know like this:
mysql --one-database database_name < all_databases.sql
Restoring Databases From cPanel Backups
There is no special wisdom to pass on to you regarding restoring databases from cPanel backups. If you do not wish to use cPanel’s automatic methods of restoring entire accounts, you can open up the individual backup on the command line, find the mysql dump file you wish to restore, and use the above commands to perform the restore.
As always, if you have any questions about how to use this on your Liquid Web server, do contact our Heroic Support.
Backup and restore mysql databases on the shell
One way to create a backup of a mysql database on the shell is to use the mysqldump command. Mysqldump creates a dump of the database in form of sql commands.
mysqldump -u root -p mydatabase > /tmp/backup_mydatabase.sql
This command creates a backup of the database with the name “mydatabase” in the file /tmp/backup_mydatabase.sql
To restore the backup, use the command:
mysql -u root -p mydatabase < /tmp/backup_mydatabase.sql