Naser Sobhan

Accomplished Software Engineer and Solution Architect

  • Home

MySQL Backup and Restore – Command Line

14/03/2019

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
Posted in: Database, MySQL, Technicals Tagged: backup, MySQL, Mysqldump, PHPMySQL, Restore

Blog Posts

  • How to Design and Implement Software Architecture Patterns: 5 Best Practices
  • Unveiling the Top Traits of a Successful Software Engineer – What it takes to Excel in the Competitive World of Tech
  • Decoding Software Architecture: Your Guide to the Design Process
  • How to improve your soft skills as a software developer?
  • 12 Soft Skills That Every Developer Must Learn to Succeed

Copyright © 2025 Naser Sobhan.

Me WordPress Theme by themehall.com