MySQL CLI Backup/Restore

Tweet

Whether you are running some MySQL driven website, use MySQL database for various applications or experimenting with MySQL, it is EXTREMELY important to backup your data. Recently my development machine crashed, I am using version control system (Subversion aka SVN) to protect the source code and try to backup local databases regulary... I was developing new custom components and plug-ins for Joomla CMS and have done some changes to database, some inserts/updates. I did not backup database for several days, after the crash it took me almost full day to fully restore the database.

I will briefly describe how to backup and restore MySQL database(s) with command line. This will work for Windows and Unix/Linux environments. Hope someone will find this information useful.

Backup

In order to backup the database(s) do the following:

1. Run the terminal
2. Execute mysqldump command

Examples of mysqldump command:

All database on local machine

mysqldump --user=USERNAME --password=PASSWORD -A > /PATH/TO/BACKUP.SQL

Specific database on local machine

mysqldump --user=USERNAME --password=PASSWORD --databases DB_NAME_1 >  /PATH/TO/BACKUP.SQL

Specific database on local machine

mysqldump --user=USERNAME --password=PASSWORD --databases DB_NAME_1 DB_NAME_2 ... DB_NAME_X >   /PATH/TO/BACKUP.SQL

All databases on remote machine

mysqldump --host=IP_ADDRESS --user=USERNAME --password=PASSWORD -A >   /PATH/TO/LOCAL/BACKUP.SQL

Specific database on remote machine

mysqldump  --host=IP_ADDRESS --user=USERNAME --password=PASSWORD --databases DB_NAME_1 >    /PATH/TO/LOCAL/BACKUP.SQL

You can execute the dump command without specifying the password. Just do not specify the password as in example below, you will be prompted to enter the password. Benefit of this method is that password is not stored in history.  

mysqldump --user=USERNAME --password --databases DB_NAME_1 > /PATH/TO/BACKUP.SQL

More information on mysqldump can be found on MySQL Website.

Restore

There are several ways to restore the database(s) depending on the situation.

Restoring database when it does not exist:

mysql --verbose --user=USERNAME --password=PASSWORD DB_NAME < /PATH/TO/BACKUP.SQL

Restoring to existing database:

Restoring to local server

mysqlimport --user=USERNAME --password=PASSWORD DB_NAME_1 /PATH/TO/BACKUP.SQL

Restoring to remote server

mysqlimport --host=IP_ADDRESS --user=USERNAME  --password=PASSWORD DB_NAME_1 /PATH/TO/BACKUP.SQL

Restoring to local server without specifying the password in the statement, password will be prompted 

mysqlimport --user=USERNAME --password DB_NAME_1 /PATH/TO/BACKUP.SQL

More information on mysqlimport can be found on MySQL Website.