Mysql – Securing MySQL backup process

MySQLmysqldumppercona

I'm running my MySQL backups through cron scheduled every night, but the thing is in cron the username and password is given for it run, is there any other way I can securely run the backups? where I can credentials remain secure only to the DBAs and not to anyone else? Btw, I use percona server. But I use mysqldump for backups, just that it has to be secure.

Thanks!

Best Answer

You can create a .my.cnf file in your home directory then store the user/password info on the [mysqldump] section.

vi ~/.my.cnf

[mysqldump]
user = dump_user
password = dump_password

Then, with your mysqldump command, use the --defaults-extra-file option to specify explicitly your conf file has your are in crontab.

/usr/bin/mysqldump --defaults-extra-file=/home/max/.my.cnf  --single-transaction  --all-databases >/space/mydump.sql

Max.

Related Solutions

MySQL – Separate Databases for Front-End and Back-End

From your comments, one solution might be to make a Master-Slave replication setup (link to mysql replication here)

I would make the backend the Master, and the front-end the slave. If your front-end needs to write (contact forms, tracking etc) you would update your code in the Front-end to read from the slave, and write to the master.

The downside is, depending on the load, your backend-writes might be delayed a few seconds or more. But as an anecdote, I've got a server that handles 120 commands /second (averaged, as reported by munin) and the slave server isn't behind by more than 3 seconds.

Options to Include with mysqldump for Hybrid Databases Backup

It would make more sense in my opinion to do a backup by table instead of by database. Here is a script I use to make backups of databases that contain MyISAM and InnoDB tables.

#!/bin/bash
USER="some_user"
PASSWD="some_password"
STAMP=$(date +"%d-%m-%y-%H:%M:%S")
HOST="some_host"
db="some_database"
FILESTAMP=$(date +"%d%m%y")
FILE="/some_directory/some_file-$STAMP.sql.gz"
Port=3306

TABLE_DUMP=$(mysql -u $USER -P$PASSWD -h $HOST -Bse "SELECT table_name FROM information_schema.tables where table_schema = '$db' and engine is not null order by table_type, table_name;")
for table_name in $TABLE_DUMP
do      
    table_engine=$(mysql -u $USER -P$PASSWD -h $HOST -Bse "SELECT engine FROM information_schema.tables where table_schema = '$db' and table_name = '$table_name';")
    case "$table_engine" in
        InnoDB ) 
            echo "$table_name InnoDB\n" 
            mysqldump --skip-opt --disable-keys --create-options --single-transaction --quick --extended-insert --add-drop-table -P $Port -u $USER -P$PASSWD -h $HOST $db $table_name | gzip -9 >> $FILE
        ;;
        MyISAM) 
            echo "$table_name MyISAM\n" 
            mysqldump --opt -P $Port -u $USER -P$PASSWD -h $HOST $db $table_name | gzip -9 >> $FILE
    esac

    #register all table names
    skiptables="$skiptables --ignore-table=$db.$table_name" 
done

# View
mysqldump -P $Port -u $USER -P$PASSWD -h $HOST $db $skiptables | gzip -9 >> $FILE

Best Answer

Related Solutions

MySQL – Separate Databases for Front-End and Back-End

Options to Include with mysqldump for Hybrid Databases Backup

Related Question