How to backup big MySQL database on Linux

There is a number of possile backup methods described in the MySQL docs. In your case I'd clearly recommend the method described in the "Making Backups Using a File System Snapshot" section where you issue a FLUSH TABLES WITH READ LOCK, make a filesystem snapshot (LVM or otherwise) and UNLOCK TABLES. After that, you can just copy off the database files to backup media.

The other options are clearly inferior. Mysqldump will create lengthy load spikes and table locks which will prevent your INSERTs for a long time, especially with very large tables.

Replication is an even less elegant solution as MySQL has no mechanisms to ensure that your replica set is identical - you will just have to hope that this is the case - really not what you would want for a backup.

Your best choice is to create another MySQL server and configure them in master/master mode. Not only will this provide you with a real-time backup solution but also a failover.

INSIGHT INTO DOING BACKUPS WITH mysqldump

IMHO Doing backups has become more of an art form if you know just how to approach it

You have options

Option 1 : mysqldump an entire mysql instance

This is the easiest one, the no-brainer !!!

mysqldump -h... -u... -p... --routines --triggers --all-databases | gzip > MySQLData.sql.gz

Everything written in one file: table structures, indexes, triggers, stored procedures, users, encrypted passwords. Other mysqldump options can also export different styles of INSERT commands, log file and position coordinates from binary logs, database creation options, partial data (--where option), and so forth.

Option 2 : mysqldump separate databases into separate data files

Start by creating a list of databases (2 techniques to do this)

Technique 1

mysql -h... -u... -p... -A --skip-column-names -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

Technique 2

mysql -h... -u... -p... -A --skip-column-names -e"SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

Technique 1 is the fastest way. Technique 2 is the surest and safest. Technique 2 is better because, sometimes, users create folders for general purposes in /var/lib/mysql (datadir) which are not database related. The information_schema would register the folder as a database in the information_schema.schemata table. Technique 2 would bypass folders that do not contain mysql data.

Once you compile the list of databases, you can proceed to loop through the list and mysqldump them, even in parallel if so desired.

for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
done
wait

If there are too many databases to launch at one time, parallel dump them 10 at a time:

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Option 3 : mysqldump separate tables into separate data files

Start by creating a list of tables

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfTables.txt

Then dump all tables in groups of 10

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    mysqldump -h... -u... -p... --triggers ${DB} ${TB} | gzip > ${DB}_${TB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Option 4 : USE YOUR IMAGINATION

Try variations of the aforementioned Options plus techniques for clean snapshots

Examples

1. Order the list of tables by the size of each tables ascending or descending.
2. Using separate process, run "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" before launching mysqldumps. Kill this process after mysqldumps are complete.
3. Save the mysqldumps in dated folders and rotate out old backup folders.
4. Load whole instance mysqldumps into standalone servers.

CAVEAT

Only Option 1 brings everything. The drawback is that mysqldumps created this way can only be reloaded into the same majot release version of mysql that the mysqldump was generated. In other words, a mysqldump from a MySQL 5.0 database cannot be loaded in 5.1 or 5.5. The reason ? The mysql schema is total different among major releases.

Options 2 and 3 do not include saving usernames and passwords.

Here is the generic way to dump the SQL Grants for users that is readble and more portable

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql

Option 3 does not save the stored procedures, so you can do the following

mysqldump -h... -u... -p... --no-data --no-create-info --routines > MySQLStoredProcedures.sql &

Another point that should be noted is concerning InnoDB. If your have a large InnoDB buffer pool, it makes sense to flush it as best you can before performing any backups. Otherwise, MySQL spends the time flushing tables with leftover dirty page out of the buffer pool. Here is what I suggest:

ABout 1 hour before performing the backup run this SQL command

SET GLOBAL innodb_max_dirty_pages_pct = 0;

In MySQL 5.5 default innodb_max_dirty_pages_pct is 75. In MySQL 5.1 and back, default innodb_max_dirty_pages_pct is 90. By setting innodb_max_dirty_pages_pct to 0, this will hasten the flushing of dirty pages to disk. This will prevent or at least lessen the impact of cleaning up any incomplete two-phase commits of InnoDB data prior to performing any mysqldump against any InnoDB tables.

FINAL WORD ON mysqldump

Most people shy away from mysqldump in favor of other tools and those tools are indeed good.

Such tools include

1. MAATKIT (parallel dump/restore scripts, from Percona [Deprecated but great])
2. XtraBackup (TopNotch Snapshot Backup from Percona)
3. CDP R1Soft (MySQL Module Option that takes point-in-time snapshots)
4. MySQL Enterprise Backup (formerly InnoDB Hot Backups [commercial])

If you have the spirit of a true MySQL DBA, you can embrace mysqldump and have the complete mastery over it that can be attained. May all your backups be a reflection of your skills as a MySQL DBA.