How to back up and restore MYSQL databaseDate posted: April 2, 2014 by: JasonDarkX2 Category: Tech Tutorials no comment.
Ah it's that time again, backing up your SQL database. While there are automated ways to back up databases, nothing beats the old fashion quick and easily command line sqldump. Backing up and Your MySQL database is easier than ever using the command line.The command Sqldump creates a snapshot of a database simply by taking all of tables and data in a database and turning them into SQL statements that can be stored into a file(s) that can be used later.
shell> sqldump -u[username] -p[password] [database_name] > [file_name].sql
For example if you want to back up a single database it would look something like this:
shell> mysqldump -ujohndoe -p123 primary_DB > ./primary_DB.sql
executing the mysqldump using the credential username "johndoe", password "123". Database name "primary_DB" then redirecting the output into a file called primary_DB.sql located in our current directory.
likewise if you like to backup multiple database, it would look some thing like this:
shell> mysqldump -ujohndoe -p123 primary_DB secondary_DB > ./DB_complete.sql
simply by adding more database names will cause sql dump them to dump them into into that same file.Otherwise if you want to create a complete backup of the database simply add the -all-database option like so:
shell> mysqldump -ujohndoe -p123 --all-database > ./DB_complete.sql
To restore a database simply execute mysql command telling it to execute the commands from the sql files into the correct database.
>shell> mysql -ujohndoe -p123 primary_DB < ./primary_DB.sql
If you are trying to restore a single database from dump of all the databases, simply add the option --one-database telling it to ignore other database except for the one defined in commmand like so:
shell> mysql -ujohndoe -p123 --one-database primary_DB < DB_complete.sql
Tags:MYSQL, SQL tutorial