How backup and restore mysql databases

I thought to write about this topic as there are many developers around who want to take a dump of mysql database from the live server to the local testing server.

They then search the web if they dont know how to do it.This article is obviously dedicated to those people.

so lets get started..........

first step take a dump of the whole mysql database using below command on CLI

mysqldump –-user [user name] –-password=[password] [database name] > [dump file]

Let's take a look at each of the arguments that can be passed to the mysqldump utility, as shown above:

* --user [user name]: The --user flag followed by a valid MySQL username tells MySQL the username of the account that we want to use to perform the database dump. MySQL user accounts are stored in the "user" table of the "mysql" database. You can view a list of users and their permissions for your MySQL server by using the following code at the MySQL console application:

use mysql;

select * from user;
* --password=[password]: The password for the user account mentioned above.
* [database name]: The name of the database that we would like the mysqldump utility to backup. Instead of specifying one single database name, we could use either --databases or --all-databases to backup every single database on our MySQL server.
* > [dump file]: If you're familiar with DOS and batch files, then you will know that the ">" symbol specifies that we are directing output to a stream, port, or file. For the mysqldump utility, we prepend a ">" to the filename we would like our database to be backed up to. If no path is specified for the file, then it will be created in the current directory.

Lets take a example

lets say i want to back up a database named rohitdata on my live server into rohitdata.txt for subsequent import to my local server.

The syntax for this is as follows

mysqldump –-user='rohitdsouza' –-password='rohitdsouza' rohitdata > rohitdata.txt

Only catch here is one should create a database first if it doesn't exists before executing the above command.

for that us create database rohitdata;

Ok after we execute this command successfully and we have our dump file its just a matter of one more command on the CLI on our local server to restore the database

mysql --user='rohit' --password='rohit' rohitdata < rohitdata.txt

the dump file should be in the current directory if no path is specified.

Thats all.Keep rocking people.
also check my cool blog www.rohitdsouza.blogspot.com