(Created page with "This page describes how to backup data from existing installation of MySQL or MariaDB. Backup documentation is available at [[http://dev.mysql.com/doc/refman/5.5/en/backup-met...") |
No edit summary |
||
| Line 1: | Line 1: | ||
This page describes how to backup data from existing installation of MySQL or MariaDB. Backup documentation is available at | This page describes how to backup data from existing installation of MySQL or MariaDB. Backup documentation is available at [http://dev.mysql.com/doc/refman/5.5/en/backup-methods.html]. | ||
== Backup using mysqldump == | == Backup using mysqldump == | ||
| Line 5: | Line 5: | ||
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. | 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 <code>mysqldump</code> is available at | Upstream documentation for using <code>mysqldump</code> is available at [http://dev.mysql.com/doc/refman/5.5/en/using-mysqldump.html]. | ||
=== Prerequisites === | === Prerequisites === | ||
| Line 24: | Line 24: | ||
== Backup using copying binary files of data stack == | == Backup using copying binary files of data stack == | ||
Upstream documentation for performing backup of database using copying files is available at | Upstream documentation for performing backup of database using copying files is available at [http://dev.mysql.com/doc/refman/5.5/en/backup-methods.html]. | ||
=== Prerequisites === | === Prerequisites === | ||
Revision as of 08:15, 19 April 2013
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"
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.