Home » Miscellaneous

How to backup and restore mysql database backup

5 May 2013 No Comment

Here is how you backup the database from a SSH window

Here is how you can you backup mysql database in linux and restore them.

You’ll need to edit USERNAME, DATABASE_NAME, USER_PASSWORD, file.sql in the following commands.

Here is the command to back up the database:

mysqldump -u USERNAME -pUSER_PASSWORD DATABASE_NAME > backup.sql

And you will have backup.sql in the current directory.

Here are the commands to create a Mysql user, password and database then restore the SQL file:

Login as root for mysql:

mysql -p

Then enter the root password then edit and post the following.

CREATE DATABASE DATABASE_NAME;
CREATE USER ‘USERNAME‘@’localhost’ IDENTIFIED BY ‘USER_PASSWORD‘;
GRANT ALL PRIVILEGES ON `DATABASE_NAME`.* TO ‘USERNAME‘@’localhost’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
USE DATABASE_NAME;
SOURCE file.sql;

That will create the database, create the user with password and assign it to the database, then restore the backup file (better enter absolute path)

There is no need to memorize those, you should save your brain space for something better.
As Einstein said: “Never memorize something that you can look up.”

Your opinion matters!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.