How to Dump All MySQL or MariaDB Database available

Posted on


As in the title of this article above, the main content is to show how to dump all MySQL or MariaDB database available in it. But not for all of the databases available in it. There are several database exceptions actually. The exceptions are the information_schema, performance_schema and the mysql database. Basically, every single database except the one where it is available after the end of the installation of MySQL or MariaDB.

After the installation of either MySQL or MariaDB is a success, there will be several default databases exist. Those are the information_schema, performance_schema, mysql and the test database. But for security reason, sometimes having the execution of ‘mysql_secure_installation’, the existence of the ‘test’ database will be no longer available. The ‘mysql_secure_installation’ command will erase the ‘test’ database. So, every single database is actually available. Except for the database above mentioned.

Dump All MySQL or MariaDB Database available

In order to get all databases available for backup in either MySQL or MariaDB, take a look at the following command pattern :

mysqldump -uusername -p --all-databases > database_file_name.sql

Using the above command pattern, just execute it in the following command :

[root@hostname ~]$ mysqldump -uroot -p --all-databases > alldb.sql
Enter password:
[root@hostname ~]$

The execution of the above command takes place in a command line interface. Make sure that the command ‘mysqldump’ is executable. In order to verify the very existence of all the available databases, just restore the dump file above. Restore it to another database runs MySQL or MariaDB. But not to forget to be able to check the version of the source and the destination of the databases. Because sometimes, the restore process can stumble and stop because of the compatibility issue. If the restore process is a success, all of the databases will be available in the destination as it exactly available from the source.

Leave a Reply