QA:Testcase MySQL MariaDB Backup data
This page describes how to backup data from existing installation of MySQL or MariaDB. Backup documentation is available at .
Backup using mysqldump
The purpose of this backup is to create a text file with SQL commands, that will be used to re-create content of all databases eventually.
Upstream documentation for using
mysqldump is available at .
- running MySQL/MariaDB server
- read access to all databases you need to backup -- usually done using root account
Dumping all databases
shell> mysqldump --all-databases > "dump-`date +%y%m%d%H%M%S`.sql"
If you've set a password for a MySQL/MariaDB user that you are running
mysqldump under, you need to specify -p option in
mysqldump command. The utility will then ask for a password interactively.
If you use events and routines (procedures/functions) in your database, consider backup these as well by specifying
--routines. Triggers are dumped by default. To disable dumping triggers, events or routines, you can use options
Backup using copying binary files of data stack
Upstream documentation for performing backup of database using copying files is available at .
- stopped MySQL/MariaDB server or (in case you cannot stop the server) locked and flushed tables (see upstream documentation above)
- read access to database files, which are by default located under /var/lib/mysql
Copying database files
shell> cp -r /var/lib/mysql/ "/safe/backup/mysql-backup-`date +%y%m%d%H%M%S`"
It is possible to use
mysqlhotcopy to perform binary backup of database files, but mind that it doesn't work for some database engines like
innodb. Hence, this is not a recommended way.