How To: Back Up MySQL Databases From The Command Line

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.

  • 2 Користувачі, які знайшли це корисним
Ця відповідь Вам допомогла?

Схожі статті

How To Install MariaDB on CentOS 6

MariaDB is a drop-in replacement for MySQL. It is easy to install, offers many speed and...

How to Display (List) All Jobs in Cron / Crontab

View Root’s Cron Jobs crontab -l  View a User’s Cron Jobs crontab -u username -l Example with...

How To: Automate Server Scripts With Cron

Servers can automatically perform tasks that you would otherwise have to perform yourself, such...

How to find sending spam emails and enable mail header in php.ini

- In Linux servers if there are a lot of emails in the queue "over 100" emails. you can check if...

Install memcache

Step 1: Login into your WHM panel and using easyapache enable Memcache Step 2: SSH into...