MySQL Backup and Restore – Command Line

MySQL is everywhere and I love it, and PHPMyadmin Made it super simple for web developers and Website Administrators but Backup and Restore is not that well and I myself had issue with PHPMyAdmin backup and restore specially with database more than 50mb. so i needed to know the best ways with MySQL itself and command line was the best although backup proccess with a simple shell script make it super simple for backup but what if don’t want to use a shell or it’s in another sever and you can do it and for other reasons.

command line is awesome and super simple but looks scary so now I collected around all possible command you might need for backup and restore a or multiple databases in MySQL:

so let’s start

MySQL backup All Database (Dump)

mysqldump -u Powered-User-Name -pYourPassword --all-databases > all-databases-backup.sql

MySQL Not All but multiple Databases

mysqldump -u root -pYourPassword --databases database1 database2 > database1-database2-backup.sql

MySQL Backup Single Database

mysqldump -u username -ppassword database  > whole_database_backup.sql

MySQL backup Single Table from a Database

mysqldump -u username -ppassword database_name table_name > single_table_dump.sql

Specific Rows from a Table using Where

mysqldump -u username -ppassword database_name table_name --where="date_created='2013-06-25'" > few_rows_dump.sql

MySQL Backup Remote server

its exactly like the way you use in local just add a new attribute and value you should use -h
attribute and space and the IP or Hostname of the remote server.

mysqldump -h remoteiporhostname-u username -ppassword database_name table_name --where="date_created='2013-06-25'" > few_rows_dump.sql

MySQL Restore

mysql -u username –-password=your_password database_name < file.sql 

MySQL Restore with mysqlimport

mysqlimport -u root -pYourPassword database_name < file.sql

MySQL Restore with Source command in MySQL

just login to MySQL use the database you want and use the source command and the address of the sql file, it just run the sql command that contains the sql file, it can be the whole database restore or single row up or delete command.

use below command to login to database

mysql -u username -pYourpassword -hHostnameORip
mysql> use databasename
mysql> source D:\backup\backup.sql