Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to backup the database nightly.

Due to my comcast connection, oftentimes if I am running a dump manually my connection to the server will timeout before the dump is complete causing me to have to rerun the dump. [I currently run a cron that does the dump nightly, this is just for dumps that I run manually.]

Is there a way to speed up the dumps for the connection timeout issue, but also to limit the time the server is occupied with this process?

BTW, I am currently working on reducing the size of the overall database to resolve this issue.

share|improve this question
1  
What parameters (if any) are you passing to the mysqldump command? –  Toby Jan 3 '11 at 21:16
    
Adding --compact may be an option for you. –  Toby Jan 3 '11 at 21:18
    
nothing really -- mysqldump [database] -u[user] -p'[password]' > db_backup.sql –  Patrick Jan 3 '11 at 21:18
2  
A simple alternative to screen for your situation would be to use nohup, it will allow your command to continue running on the server, even if your connection gets dropped. E.g. nohup mysqldump [options] > backup.sql 2> backup.err &. If you do not provide an output file for nohup, it will create nohup.out by default. –  dabest1 Oct 14 '11 at 3:37
    
It turns out that my connection dropping was not a function of Comcast internet, but the firewall at my office. It kills any connection that sits idle for too long. –  Patrick May 11 '12 at 19:29
show 1 more comment

6 Answers

up vote 42 down vote accepted

The main bottleneck in the dump like this is drive I/O. You are reading a load of data and writing it again. You can speed this up in a number of ways:

  • Make sure your output is going to a different drive(s) than the one(s) the database files are stored on - this will make a massive difference with spinning disks as the drive heads will not be constantly flicking between the location being read from and the location being written to.
  • The output of mysqldump will be very compressible, so if you can not separate the output from the input as mentioned above pipe the output through gzip or similar. This will reduce the amount of writing being done (so reduce the overall IO load, and the amount of head movement) at the expense of some CPU time (which you may have a lot of spare at these times anyway). Also, pass the output through a pipe utility (like pv) that supports large write buffers to group blocks written to the drives together more, again to reduce the effect of head-movement latency - this will make quite a difference if using the --quick option to reduce the RAM impact of backing up large tables).
  • Only run your backup process when IO load is otherwise low.

You may be fixing the wrong issue though: it might be easier to address the connection drops instead (though reducing the I/O load imposed by your backups will help reduce the effect you have on other users so is worth trying anyway). Could you run your manual backups through screen (or similar tools like tmux)? That way if your connection to the server drops you can just reconnect and reattach to the screen session without any processes getting interrupted.

If you are sending the data directly over the connection (i.e. you are running mysqldump on your local machine against a remote database, so the dump appears locally) you might be better off running the dump on the server first, compressing as needed, then transferring the data over the network using a tool (such as rsync) which supports partial transfers so you can resume the transfer (instead of restarting) if a connection drop interrupts it.

As part of your "reducing the size of the overall database to resolve this issue" I would guess that a large chunk of your data does not change. You might be able to move a large chunk of the 1.2Gb from that main table off into another and remove that from those that are copied by the mysqldump call. You don't need to backup this data every time if it never changes. Splitting data between tables and databases this way is usually referred to as data partitioning and can also allow you to spread the data and I/O load over multiple drives. High-end database have built in support for automatic partitioning, though in mysql you will probably have to do it manually and alter your data access layer to account for it.

Straying off-topic for this site (so you should probably nip over to ServerFault or SuperUser to ask if you need more detail): If you seem to be losing connections due to inactivity, check the options in your SSH server and SSH client to make sure keep-alive packets are enabled and being sent often enough. If seeing drops even if the connection is active you could also try using OpenVPN or similar to wrap the connection - it should handle a short drop, even a complete drop if your entire connection is down for a few seconds, such that the SSH client and server don't notice.

share|improve this answer
    
I wish I could reduce the number of dropped ssh connections to my servers. If I expect to not use the terminal for longer than ~60 seconds I run top to ensure the connection doesn't drop. (And I'm pretty sure it's the comcast connection as we're only using a standard WRT router & firewall at work and my home comcast connection never drops) –  Patrick Jan 3 '11 at 22:12
    
I've added a short note specific to the SSH connections. –  David Spillett Jan 3 '11 at 22:25
    
Depth and insight in this answer. You should get a +3 for this. Sorry, I can only give you +1. –  RolandoMySQLDBA Jun 23 '11 at 4:02
add comment

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... --hex-blob --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... --hex-blob --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... --hex-blob --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... --hex-blob --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. This is helpful if a database contains both InnoDB and MyISAM
  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.

share|improve this answer
    
+1 for being detailed as usual... –  StanleyJohns Jun 9 '11 at 20:14
1  
+1 for nice use of mysqldump as well as for: 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....Great lines!!! –  Abdul Manaf May 17 '12 at 6:45
    
In InnoDB, dumping tables individually will give you an inconsistent backup. –  Alain Collins Nov 13 '12 at 7:10
    
@AlainCollins this is why I run mysqldumps on a replication slave that is read only . Once Seconds_Behind_Master is 0, you run STOP SLAVE. Now you have a consistent point in time for doing mysqldumps in any of the aforementioned styles. I have done this for online trading companies over the past 5 years without so much as a single complaint to me or my company owners. As of this moment, I do parallel mysqldumps every 10 minutes for this client. I also do this for other clients to provide faster backup periods. –  RolandoMySQLDBA Nov 13 '12 at 7:29
    
I have a 32GB db so option 3 is exactly what I had in mind! thanks! –  Raymond Jan 9 at 3:10
add comment

I used to have problems with timeouts during dumps of big databases as well. I finally solved if by sending individual commands for every table in the db and appending everthing to one file like this:

TABLES=`mysql -u $USER -p$PWD -Bse 'show tables' $DB`
for TABLE in $TABLES
do
    mysqldump -u $USER -p$PWD $DB $TABLE >> dump.sql
done
share|improve this answer
add comment

Plan A: See also Xtrabackup from Percona. This allows online backup of InnoDB, without any significant locking.

Plan B: A Slave can be stopped, and you can take a consistent backup by any of several means (copy files, mysqldump, xtrabackup, etc)

Plan C: LVM Snapshot. After some cryptic setup, the downtime for a backup is less than a minute, regardless of the size of the database. You stop mysqld, do the snapshot, restart mysqld, then copy the snapshot. The last step can take a long time, but MySQL is not down.

Plan D: Snapshot of a Slave -- zero downtime.

share|improve this answer
    
Hoorah to all four plans. I can only give +0.25 per answer !!! +1 (4 x 0.25) –  RolandoMySQLDBA Jun 8 '11 at 17:03
add comment

A few admin points first: Are you connecting to do an ftp or are you ssh'ed in and it's dying? If ssh, then be sure to use screen so that you can resume after the comcast crash. If ftp, then make sure you're compressing it/tar before the send.

Also try the --opt parameter or --quick

--opt This option turns on a set of additional options to make the dump and reload operations more efficient. Specifically, it's equivalent to using the --add-drop-table, --add-locks, --all, --quick, --extended-insert, --lock-tables, and --disable-keys options together. Note that this option makes the output less portable and less likely to be understood by other database systems.

--quick This option tells mysqldump to write dump output as it reads each row from the server, which might be useful for large tables. By default, mysqldump reads all rows from a table into memory before writing the output; for large tables, this requires large amounts of memory, possibly causing the dump to fail.

share|improve this answer
1  
Will --opt not increase the size of the file that will eventually get output? –  Toby Jan 3 '11 at 21:25
    
It will add some - I meant to add --quick which is more in answer to his problem .... editing now. Thanks! –  David Hall Jan 3 '11 at 21:29
    
+1 for screen, which avoids this problem altogether –  Gaius Jan 10 '11 at 10:06
    
+1 for a very nice and concise answer for mysqldump's --opt and --quick explanations. –  RolandoMySQLDBA Jun 23 '11 at 4:00
    
--opt is on by default. –  Jordan Jan 25 '13 at 21:39
add comment

Have a look at mysql replication master to slave . It allows you to clone the database of master to another database server with same database. That include the master and slave identities. Slave make itself the exact copy of the master database server and or its databases. There may be one-one, one-many, many-one relation among master(s) and slave(s).

Slave continously reads the binary log at master (binlog store the queries written at master database server) and get input to its slave database server. ( this means you're master database whont be afected at all )

The good news is that it whont affect you're mysql server too much as in you whont notice any downtimes or slow query responses . We use it for 10Gb databases and it works like a charm without any downtime .

MySQL Replication On The Same Machine

share|improve this answer
    
while that would work for me, I think it might be a little overkill. I currently don't need that level of backup, although I will keep this in mind should the requirements of the application change. –  Patrick Jan 3 '11 at 22:09
3  
+1 for backing up a replica to remove the IO load of the backup from the main DB, and reduce potential locking related issues, with one significant caveat: be careful with the "replica on the same machine" option as your operations on the slave may compete with the master for IO bandwidth - make sure the slave's data files are a different drive/array than the master in order to mitigate this issue. –  David Spillett Jan 3 '11 at 22:16
    
Ditto on David Splllet's Comment. I setup and maintain dozens of Master/Slaves with mysqldump backups on the slaves for My Web Hosting Employer. +1 from me as well. –  RolandoMySQLDBA Jun 23 '11 at 3:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.