MYSQL Backups

There are plenty of options to back up mysql DBs.

One method is the following:

Create a backup user in mysql/mariadb like the following:


# /usr/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 253404
Server version: 5.1.54-1ubuntu4 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> GRANT LOCK TABLES, SHOW VIEW, SELECT ON *.* TO 'backupsonly'@'%' IDENTIFIED BY 'P@$$w0RD';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye


Then, create a script “my_backup.conf” that has the following in it:


Then, run the following script to export the data.


mysqldump --defaults-extra-file=my_backup.conf -u backupsonly databasename --single-transaction --quick --lock-tables=false | gzip > databasename_full-backup-$(date +%F).sql.gz



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at

Up ↑

%d bloggers like this: