Question :
I want to keep a backup of all my MySQL databases. I have more than 100 MySQL databases. I want export all of them them at the same time and again import the all of them into my MySQL server at one time. How can I do that?
Answer :
Follow these steps :
1 – open a command window in the bin folder of MySQL install Folder: (mysqlbin) :
2 – type this command Line :
——–mysqldump -u Username -p Password –all-databases (greater than sign) location/filename.sql
3 – That’s All !
Look up the documentation for mysqldump (http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html).
You may want to use other options.
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.
mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the –single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.
To reload a dump file, you must have the privileges required to execute the statements that it contains, such as the appropriate CREATE privileges for objects created by those statements.
mysqldump output can include ALTER DATABASE statements that change the database collation. These may be used when dumping stored programs to preserve their character encodings. To reload a dump file containing such statements, the ALTER privilege for the affected database is required.
If you are performing a backup on the server and your tables all are MyISAM tables, you can also use mysqlhotcopy for this purpose.