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 [1].

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 [2].

Prerequisites

• 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"

Expected results

Content of the database is stored in SQL form in the specified file.

Password settings
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.

Triggers, functions, routines, events
If you use events and routines (procedures/functions) in your database, consider backup these as well by specifying --events and --routines. Triggers are dumped by default. To disable dumping triggers, events or routines, you can use options --skip-triggers, --skip-events, and --skip-routines.

Backup using copying binary files of data stack

Upstream documentation for performing backup of database using copying files is available at [3].

Prerequisites

• 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`"

Expected results

Content of the directory /var/lib/mysql/ is copied into the specified location.

Using mysqlhotcopy
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.