Insight into doing large backup with mysqldump

down vote

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 Order the list of tables by the size of each tables ascending or descending. Using separate process, run "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" before launching mysqldumps. Kill this process after mysqldumps are complete. Save the mysqldumps in dated folders and rotate out old backup folders. 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 MAATKIT (parallel dump/restore scripts, from Percona [Deprecated but great]) XtraBackup (TopNotch Snapshot Backup from Percona) CDP R1Soft (MySQL Module Option that takes point-in-time snapshots) 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.