QA:Testcase MySQL MariaDB Backup data

From FedoraProject

(Difference between revisions)
Jump to: navigation, search
 
Line 16: Line 16:
 
<pre>shell> mysqldump --all-databases > "dump-`date +%y%m%d%H%M%S`.sql"</pre>
 
<pre>shell> mysqldump --all-databases > "dump-`date +%y%m%d%H%M%S`.sql"</pre>
  
=== Notes ===
+
=== Expected results ===
  
If you've set a password for a MySQL/MariaDB user that you are running <code>mysqldump</code> under, you need to specify -p option in <code>mysqldump</code> command. The utility will then ask for a password interactively.
+
Content of the database is stored in SQL form in the specified file.
  
If you use events and routines (procedures/functions) in your database, consider backup these as well by specifying <code>--events</code> and <code>--routines</code>. Triggers are dumped by default. To disable dumping triggers, events or routines, you can use options <code>--skip-triggers</code>, <code>--skip-events</code>, and <code>--skip-routines</code>.
+
{{admon/note| Password settings | If you've set a password for a MySQL/MariaDB user that you are running <code>mysqldump</code> under, you need to specify -p option in <code>mysqldump</code> command. The utility will then ask for a password interactively. }}
 +
 
 +
{{admon/note| Triggers, functions, routines, events |If you use events and routines (procedures/functions) in your database, consider backup these as well by specifying <code>--events</code> and <code>--routines</code>. Triggers are dumped by default. To disable dumping triggers, events or routines, you can use options <code>--skip-triggers</code>, <code>--skip-events</code>, and <code>--skip-routines</code>.}}
  
 
== Backup using copying binary files of data stack ==
 
== Backup using copying binary files of data stack ==
Line 35: Line 37:
 
<pre>shell> cp -r /var/lib/mysql/ "/safe/backup/mysql-backup-`date +%y%m%d%H%M%S`"</pre>
 
<pre>shell> cp -r /var/lib/mysql/ "/safe/backup/mysql-backup-`date +%y%m%d%H%M%S`"</pre>
  
=== Notes ===
+
=== Expected results ===
 +
 
 +
Content of the directory /var/lib/mysql/ is copied into the specified location.
 +
 
  
It is possible to use <code>mysqlhotcopy</code> to perform binary backup of database files, but mind that it doesn't work for some database engines like <code>innodb</code>. Hence, this is not a recommended way.
+
{{admon/note| Using mysqlhotcopy |It is possible to use <code>mysqlhotcopy</code> to perform binary backup of database files, but mind that it doesn't work for some database engines like <code>innodb</code>. Hence, this is not a recommended way.}}

Latest revision as of 20:57, 23 April 2013

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

Contents

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

[edit] Prerequisites

  • running MySQL/MariaDB server
  • read access to all databases you need to backup -- usually done using root account

[edit] Dumping all databases

shell> mysqldump --all-databases > "dump-`date +%y%m%d%H%M%S`.sql"

[edit] Expected results

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

Note.png
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.
Note.png
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.

[edit] Backup using copying binary files of data stack

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

[edit] 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

[edit] Copying database files

shell> cp -r /var/lib/mysql/ "/safe/backup/mysql-backup-`date +%y%m%d%H%M%S`"

[edit] Expected results

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


Note.png
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.