From Fedora Project Wiki
(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 |
||
| (One intermediate revision by the same user not shown) | |||
| 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 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> | ||
=== | === Expected results === | ||
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 == | ||
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 === | ||
| 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> | ||
=== | === 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].
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
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
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
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.