Different Ways of Back up Mysql Databases

(A)  Backup a single database:

Using mysqldump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use mysqldump to backup and restore. For the impatient, here is the quick snippet of how backup and restore MySQL database using mysqldump:

Backup: #mysqldump –u root –p[root password] [database name] >dumpfilename.sql

Restore: #mysql –u root –p[root password] [databasename] <dumpfilename.sql

This example takes a backup of sampledb database and dumps the output to dbdump.sql

[mysql@localhost]# /usr/bin/mysqldump –u root –pmysql sampled > /tmp/dbdump.sql

[mysql@localhost]# mysqldump –u root –p[rootpassword]  [databasename] > /tmp/dbdump.sql

After you press enter to execute the command, the backup will be generated. If you browse to this path: /tmp, then you should see dbdump.sql in there.

(B) Backup multiple databases:

If you want to backup multiple databases, first identify the databases that you want to backup using the show databases as shown below:

# mysql -u root -ppassword

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| newdb            |

| mysql              |

| sampledb            |

For example, if you want to take backup of both sampledb and newdb database, execute the mysqldump as shown below:

[mysql@localhost]# mysqldump –u root –p[rootpassword]  --databases sampledbnewdb> /tmp/dbdump.sql

[root@INVRLX55SAAS10 ~]# mysqldump -u root -p --databases information_schema QA_TEST ac ad dqwhiodlogwh ma mysqlperformance_schema source test > /tmp/mysql_backup/alldbs.sql

Enter password:

mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

[root@INVRLX55SAAS10 ~]# mysqldump --single-transaction -u root -p --databases information_schema QA_TEST ac addqwhiodlogwh mamysqlperformance_schema source test > /tmp/mysql_backup/alldbs.sql

Enter password:

Here we have space between db’s

Verify the dbdump.sqldump file contains both the database backup.

#grep –I “Current database:” /tmp/dbdump.sql

--Current database: ’newdb’

--Current database:’sampledb’

(C) Backup all the databases:

The following example takes a backup of all the database of the MySQL instance.

# mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql