MySQL backups using mysqldump

MySQL backups are essential to running a site with MySQL backend. Generally you can get away with doing nightly backups but on our site, due to couple issues we had in past, we are forced to do hourly backups of our db.

Intially I was doing backup by using: mysqldump dbname > weekdayHour.dbname.sql hourly. This allowed us to have week worth of backups done every hour and auto overwriting old backups. Eventually we added stored procedures and triggers to the mix and all of the sudden this dump wasn’t getting all the stored procedures and triggers. So we started using mysqldump with -R parameter which in man states:

Dump stored routines (functions and procedures) from the dumped databases.

Recently when we started to see more and more traffic, we noticed that our server was under heavy load on the hour. Ofcourse we quickly found it was due to mysqldump running on the hour which was causing the lag. Eventually we had enough traffic on the site where this was causing connection problems with mysql. So I went through man mysqldump again to find solution. And thanks to those smart people in mysqldump dev team, there I found couple more parameters to make this backup process little less painful. At this point we are using: mysqldump -R -q –single-transaction > weekdayHour.dbname.sql This seems to have decreased the load on the server and we haven’t got errors connecting to mysql. Since we use innodb tables in this particular db, we could use single-transaction parameter. Here is what our friendly man mysqldump has to say about these two parameters:

-q …This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out…

–single-transaction …This option issues a BEGIN SQL statement before dumping data from the server. It is useful only with transactional tables such as InnoDB and BDB, because then it dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications…

If you are thinking about using these two parameters, please spend couple minutes reading through man mysqldump and look at other notes which might pertain to your setup.

One last but very important thing we do after our backups are run is to move the new backup files off the server just in case server dies. I achieved this by using ncftp package which includes ncftpput command line utility. With ncftp, you can store ip/login/pw in a file and tell ncftp to use that for login information. Lets look at the script as a whole:

NOTE: comments in this script is for information purposes only. You can remove them if you like

DATE=`date '+%u%H'` # this sets up weekly rotation of backup files
BACKUP_DIR="/admin/backups/" # this dir will be created if it doesn't exist
HOST=`hostname` #you may want to hard code this if you hostname returns wrong information
mkdir ${BACKUP_DIR}mysql/ -p
/usr/local/mysql/bin/mysqldump -R -q --single-transaction --databases dbname1 dbname2 -ppassword > ${BACKUP_DIR}mysql/$HOST$DATE.sql
rm ${BACKUP_DIR}mysql/$HOST$DATE.sql.gz > /dev/null 2>&1
gzip -9 ${BACKUP_DIR}mysql/$HOST$DATE.sql > /dev/null 2>&1
/usr/bin/ncftpput -R -f ${BACKUP_DIR}hostinfo / ${BACKUP_DIR}mysql/$HOST$DATE.sql.gz

This is what your hostinfo file should contain (just make sure you edit it and put your own server ip, username, and password):
user loginname
pass loginpassword

To read more about mysqldump, see man mysqldump

38 thoughts on “MySQL backups using mysqldump

  1. Pingback: mysqldump tips by crazytoon - Rusty Razor Blade

  2. Phil

    Two questions from a newbie:
    1)I’m dumping from mySQL 5.0.45 and restoring to 4.0. Do you forsee any issues?

    2)Can individual tables be dumped?

  3. Phil

    I was able to restore to the 4.0 database and to individual tables. Thanks.

    Is there a way to get the dump files to break into multiple files if they are over 10meg? That’s the limit my isp allows for imports.

    I manually broke up the larger table dumps and it worked but the process was painful.


    [source server is win2003 with iis.]

  4. Steve

    I use the CMS PHP-Fusion on my current website. It has an old version of PHP. I am trying to move my database to my new host which has PHP 5.

    I am getting the following error…

    Error at the line 27: ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=83 ;

    Query: CREATE TABLE `fusion_admin` (
    `admin_id` tinyint(2) unsigned NOT NULL auto_increment,
    `admin_rights` char(2) NOT NULL default ”,
    `admin_image` varchar(50) NOT NULL default ”,
    `admin_title` varchar(50) NOT NULL default ”,
    `admin_link` varchar(100) NOT NULL default ‘reserved’,
    `admin_page` tinyint(1) unsigned NOT NULL default ‘1’,
    PRIMARY KEY (`admin_id`)

    MySQL: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=83’ at line

    Since I do not know squat about databases etc…is there any quick answer of fix to this situation. I have no idea what this means.

  5. Pingback: MySQL: How do I dump all tables in a database into separate files? | Technology: Learn and Share

  6. Zerd

    Sure you don’t want to use a pipe? Saves space and time.

    /usr/local/mysql/bin/mysqldump -R -q –single-transaction –databases dbname1 dbname2 -ppassword | gzip -q -9 – > ${BACKUP_DIR}mysql/$HOST$
    rm ${BACKUP_DIR}mysql/$HOST$DATE.sql.gz > /dev/null 2>&1
    mv ${BACKUP_DIR}mysql/$HOST$ ${BACKUP_DIR}mysql/$HOST$DATE.sql.gz

  7. go here

    These are really impressivee ideas iin on the topic of
    blogging. You have touched some pleasant things here.

    Any way keep up wrinting.

  8. 5 Considerations To Make When Playing Online Kasino Games

    What the particular top ten toys phrases of of sales in the globe today?
    You would possibly be surprised to see some of the toys onto the list but according to my research,
    these the actual top ten best selling toys at present.

    Another guaranteed success. Anybody who plays Gran Turismo will grab the
    game. If you’re a fan, you’re a lover. The visuals continue to improve,
    HD replays, ’nuff said!

    Crack for ios games are for you to discover right now
    there are plenty of websites across the net that support you to obtain them for gratis.
    In through doing this you won’t have to quite anything at all for developing is to write codes or passwords will be allotted along
    with game maker or even gaming organization. You can get
    ion game hacks at no cost for any kind of game that consideration to
    have fun with. clashofclans is an actual famous game that’s also
    new to the world of internet takes on. Those who are avid gamers would love a challenge the
    game presents however people who’re not too competitively willing would love the aid
    of clash of clan bust.

    Audio: various.0: The worst voice acting I have ever heard,
    hands straight. The music is alright and the SFX work, but the voice acting is a regular reminder of
    nails on a chalkboard.

    Originally perceived as a trend, motion-controlled gaming is barreling forward.
    When the Wii first emerged into the world of gaming, the Wiimote motion controller
    was a sensation. Since then, Sony’s answered back with their interestingly shaped
    Move, and Microsoft just released their answer 5 Considerations To Make When Playing Online Kasino Games motion-sensitive gaming,
    the controller-free Kinect.

    Have you noticed that some jobs give that you higher level of experience for the amount of
    your energy you invest? There are some jobs that a person 2 xp for every 1 energy
    point acquire. Don’t waste your time on jobs that only give that you simply ratio of 1:1.

    websites likewise allows show you which of them jobs are
    the most effective for this amazing. See mine below if don’t fancy rooting.

    Bananagrams extra game in the neighborhood . extremely
    popular right actually. This is a word game for two or more players.

    Bananagrams consists of letter tiles stored in the cute little banana-shaped body.

    West and Zampella were the leads behind the creation of
    Call of Duty: Modern warfare 2. They were released for allegedly breaching contract
    and of insubordination. West and Zampella, in turn, filed suit against
    their former employers two days later.

  9. ?????????????? Top slim

    I was curious if you ever thought of changing the structure of your
    website? Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content so people could connect
    with it better. Youve got an awful lot of text for only having
    one or 2 pictures. Maybe you could space it out better?

  10. Norine

    Quality posts is the important to invite the viewers to pay
    a quick visit the site, that’s what this web site is providing.

  11. are you looking for paints?

    Hmm it looks like your website ate my first comment (it was super long) so I guess
    I’ll just sum it up what I had written and say, I’m thoroughly
    enjoying your blog. I too am an aspiring blog
    writer but I’m still new to the whole thing.

    Do you have any recommendations for rookie blog writers? I’d genuinely appreciate

  12. Juegos Nintendo 3DS

    I do agree with all the ideas you have introduced in your post.
    They are really convincing and can definitely work. Still,
    the posts are too short for starters. May just you please lengthen them a bit from subsequent time?
    Thank you for the post.

  13. chrome hearts

    Thank you a lot for giving everyone an exceptionally wonderful opportunity to read critical reviews from this web site. It can be so pleasing and packed with a great time for me and my office fellow workers to visit your web site really thrice weekly to read through the newest secrets you have got. And indeed, I am always motivated considering the exceptional advice you serve. Selected 1 facts in this article are in fact the very best we have ever had.

  14. Hero Hunters hack

    Great blog right here! Additionally your web site rather a lot up very
    fast! What host are you the use of? Can I get your affiliate link
    to your host? I want my web site loaded up as quickly as yours lol

  15. bitcoin

    Hi there, all the time i used to check website posts here early in the morning, for the reason that i enjoy to find
    out more and more.

  16. Pingback: ??? ?????????? ?? ?????? ? ??????? ???????, ???????????? ??? ??????? ? ???????

  17. Pingback: Reduslim precio Comprar en farmacias españolas (original, sitio oficial)

  18. Pingback: ??? ?????????? ?? ??????? ????

  19. Pingback: ??? ?????????? ?? ??????? ???? ? ?????

  20. Pingback: How to buy a prescription drug without a prescription?

  21. Pingback: ??? ?????????? ?? ??????? ????

  22. Pingback: Buy Atarax 10 mg, 25 mg online

  23. moviesda

    Moviesda is a website for movies which allows you to download and watch online movies including Tamil Movies, Tamil Dubbed Movies as well as Tamil Web Series. Additionally you can also view and download Tamil Dubbed films from this website for movies.

  24. Amazon ERC number

    An Amazon ERC number is a unique identifier that Amazon assigns to each seller. It is used to track sales and shipments, as well as to identify the seller when filing taxes. This number is required for sellers who want to use Amazon’s services, such as Fulfillment by Amazon and Seller Central. Having an ERC number helps sellers keep track of their transactions with Amazon, which can be useful for tax purposes. Additionally, it helps ensure that all sales are properly reported and that sellers are not missing out on any potential tax deductions or credits.

Leave a Reply

Your email address will not be published. Required fields are marked *