Export and Import all MySQL databases at one time

mysql database-backups mysql-backup
NewUser picture NewUser · Feb 29, 2012 · Viewed 469.7k times · Source

I want to keep a backup of all my MySQL databases. I have more than 100 MySQL databases. I want to export all of them at the same time and again import all of them into my MySQL server at one time. How can I do that?

Answer

Shakti Singh picture Shakti Singh · Feb 29, 2012

Export:

mysqldump -u root -p --all-databases > alldb.sql

Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments:

mysqldump -u root -p --opt --all-databases > alldb.sql
mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql

Import:

mysql -u root -p < alldb.sql

Related questions

mysqldump not creating create database syntax

I am using mysql database. I am using mysqldump to create a dump of my database. I want the database to be dumped such that there is a drop database if exists command followed by create database command. I am …

Read More
How to take MySQL database backup using MySQL Workbench?

How to take database backup using MySQL Workbench? Can we take backup in the following ways- Backup file(.sql) contains both Create Table statements and Insert into Table Statements Backup file(.sql) contains only Create Table Statements, not Insert into …

Read More
Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)

I have a MySQL user called dump with the following perms: GRANT USAGE ON *.* TO 'dump'@'%' IDENTIFIED BY ... GRANT SELECT, LOCK TABLES ON `mysql`.* TO 'dump'@'%' GRANT SELECT, LOCK TABLES ON `myschema`.* TO 'dump'@'%' …

Read More