Skip to content

Daily backup MySQL databases

Published by tex on November 7, 2016

I will use ‘mysql_config_editor’ for storing authentication credentials in an encrypted login path file named ‘.mylogin.cnf’. The file location is the ‘~/.mylogin.cnf’ (user home). The file can be read later by MySQL client programs to obtain authentication credentials for connecting to MySQL Server.

The unencrypted format of the .mylogin.cnf login path file consists of option groups, similar to other option files. Each option group in .mylogin.cnf is called a “login path,” which is a group that permits only certain options: host, user, password, port and socket. Think of a login path option group as a set of options that specify which MySQL server to connect to and which account to authenticate as. Here is an unencrypted example:

‘~/.my.cnf’

[client]
user = 'username'
password = 'my_password'

When you invoke a client program to connect to the server, the client uses .mylogin.cnf in conjunction with other option files. Its precedence is higher than other option files, but less than options specified explicitly on the client command line.

Programs use groups in the login path file as follows:

– mysql_config_editor operates on the client login path by default if you specify no –login-path=name option to indicate explicitly which login path to use.
– With a –login-path option, client programs additionally read the named login path from the login path file. The option groups read from other option files remain the same.

Consider this command:

shell> mysql --login-path=mypath

First time, i will generate the file ‘.mylogin.cnf’ with login path ‘local’

root@ns:~# mysql_config_editor set --login-path=local --host=localhost --user=root --password
Enter password:
root@ns:~#

Backup script:

#!/bin/sh
# mysql daily backup
# www.unixteacher.org

BACKUP_DIR="/home/backup/dbs"
TODAY="`date +%Y.%m.%d`"
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
BZIP2="/bin/bzip2"

mkdir $BACKUP_DIR/$TODAY
cd $BACKUP_DIR/$TODAY

for i in `$MYSQL --login-path=local -e "show databases"|tail --lines=+2`
  do
	$MYSQLDUMP --login-path=local --skip-add-locks --single-transaction --quick $i > $i.sql
	$BZIP2 $i.sql
  done

Everyday, a new folder will be created. All sql files will be compressed.

root@ns:~# ls /home/backup/dbs/
2016.11.07

Notes:

– Using a password on the command line interface can be insecure.
– The mysql_config_editor utility is available as of MySQL 5.6.6

Published inDatabasesLinux