Tips&Tricks

How to backup all databases in MySQL / MariaDB

MariaDB and MySQL logos

MySQL is an open source relational database management system. MariaDB is a fork of MySQL.

Backup all databases to a local file:

mysqldump --all-database -uroot -p > backup.sql

Since the dump files are just SQL commands, you can restore the database backup to a file. And execute them by telling mysql to run the commands in it and put the data into the proper database.

cat backup.sql | mysql -uroot -p

Also you can combine it with ssh to store the backup in another host:

ssh user@host -C 'mysqldump --all-database -uroot -p' > host-$(date +Y-m-d).sql

It’s very lazy way. But it’s work!