Backup And Restore A MySQL Database Through Command Line

Most of the time, you can easily manage your database with web-based tools such as phpMyAdmin. Unfortunately there are times when you need to restore a large amount of data which can’t be done through phpMyAdmin because if PHP’s limits (either the maximum time a PHP script can use or the maximum file size upload).

 

Using MySQL Command Line

Whether you are using MySQL on Linux or MS Windows, you can use the command line to perform most tasks. If you are using MS Windows, make the path to the command line binaries is included in your system path. This will make things easier for you otherwise you’ll have to type the full path to each command.

How To Backup A MySQL Database

MySQL provides a utility called “mysqldump”. Basically what this tool does it it creates a flat file containing the SQL instructions to restore your database. Here are a few usage examples ofmysqldump:

Creating a simple database backup

mysqldump -uusername-pdatabase_name > file.sql

This will create a file containing all the SQL statements to create tables and restore data into an existing database. If the target database contains tables with the same names, they will be overwritten. If you want the existing tables to be dropped and recreated, use theadd-drop-tableoption:

mysqldump –add-drop-table -uusername-pdatabase_name > file.sql

You could also choose to drop the whole database before recreating it and restoring data:

mysqldump –add-drop-databases -uusername-pdatabase_name > file.sql

Backing Up Multiple Databases

You can backup multiple databases to a single file using thedatabasesoption:

mysqldump -uusername-p –databasesdatabase1 database2 database3 > file.sql

Creating a backup of all databases can be achieved using theall-databasesoption:

mysqldump -uusername-p –all-databases> file.sql

Backing Up InnoDB Tables

If you database has InnoDB tables, you will need to deactivate referential integrity while restoring data. Unfortunately, this can’t be done using the mysqldump utility. To do so, backup your database as you would normally. When done, open the SQL file and add the following statement at the very beginning:

SET FOREIGN_KEY_CHECKS=0;

…and add the following at the end of the file:

SET FOREIGN_KEY_CHECKS=1;

Compressing MySQL Dump File

If you are usingmysqldumpin a Linux shell, you can pipe it throughgzipto compress the dump file (assuming you havegzipinstalled):

mysqldump -uusername-pdatabase_name |gzip -cfile.sql.gz

Restoring A MySQL Backup

To restore a backup created withmysqldump, you will need to use themysqlcommand. If your SQL dump file does not contain any “create database” statement, you can use the following command:

mysql -uusername-pdatabase_name<file.sql

But if it does, simply use the same command without specifying the database name:

mysql -uusername-p <file.sql

More information on mysqldump can be foundhere.

  • 30 Users Found This Useful
Was this answer helpful?

Related Articles

How can I create a database from an sql backup file ?

First, create the database.. /path/to/bin/mysqladmin -u $mysqlusername -p$mysqlpassword create...

How can I see all running processes from my server?

Type the following command from shell prompt.It will display the currently running processes. ps...

Connecting to your Windows server

To connect to your Windows VPS or Dedicated server, you use Remote Desktop Connection. Go to:...

Connecting to your Linux server

To connect to your Linux VPS or Dedicated Server, you need a ssh client.  We recommend...

Memory usage seems too high inside a VPS

There are cases when you may notice that the memory usage is too high inside your VPS without a...