QA:Testcase MySQL MariaDB Backup data

From FedoraProject

Revision as of 08:15, 19 April 2013 by Hhorak (Talk | contribs)

Jump to: navigation, search

This page describes how to backup data from existing installation of MySQL or MariaDB. Backup documentation is available at [1].

Contents

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"

Notes

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

Notes

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.