MYSQL Backups

Posted on

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:

[mysqldump]
user=backupsonly
password="P@$$w0RD"

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

 

 

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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