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.