MySQL : Ways to take Database backup

Ways to take MySQL database backup

1. Getting backup of a MySQL database using mysqldump.

Use following command line for taking backup of your MySQL database using mysqldump utility.

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

Example:

mysqldump –-user ktpot–-password=ktpot db_test > db_test.sql or mysqldump –uktpot –pktpot db_test > db_test.sql

2. Backup multiple databases in MySQL.

mysqldump –u[user name] –p[password] [database name 1] [database name 2] .. > [dump file]

Example:

mysqldump –-user ktpot–-password=ktpot db_test db_second db_third > db_test.sql

3. Backup all databases in MySQL.

shell> mysqldump –u[user name] –p[password] –all-databases > [dump file]

4. Backup a specific table in MySQL.

shell> mysqldump --user [username] --password=[password] [database name] [table name] \ > /tmp/sugarcrm_accounts_contacts.sql

Example:

shell> mysqldump --user ktpot --password=ktpot db_test customers \ > db_test_customers.sql

5. Restoring MySQL database.

The mysqldump utility is used only to take the MySQL dump. To restore the database from the dump file that you created in previous step, use mysql command.

shell> mysql --u [username] --password=[password] [database name] < [dump file]

Example:

shell> mysql --user ktpot --password=ktpot new_db < db_test.sql

If you are moving your data to a new server, or you have removed the old database completely you can restore it using the code below. This will only work if the database does not already exist:

mysql - u ktpot -p ktpot database_name < file_name.sql

Or using our example from the previous page:

mysql - u ktpot -p ktpot ktpotdb < ktpotdb.sql

If your database already exists and you are just restoring it, try this line instead:

mysqlimport -u user_name -p your_password database_name file_name.sql

Or using our example again:

mysqlimport -u ktpot -p ktpot ktpotdb ktpot.sql

To load the text file ktpot.txt into the ktpottest table, use this statement:

mysql> LOAD DATA LOCAL INFILE '/path/ktpot.txt' INTO TABLE ktpottest;

If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this statement instead:

mysql> LOAD DATA LOCAL INFILE '/path/ktpot.txt' INTO TABLE ktpottest

    -> LINES TERMINATED BY '\r\n';

Useful links :

http://www.noupe.com/how-tos/10-ways-to-automatically-manually-backup-mysql-database.html

http://www.mysqldiary.com/as-restoring-a-dump-table-into-the-mysql-master-you-better-get-some-sleep/

  

https://dev.mysql.com/doc/refman/5.5/en/loading-tables.html