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
#!/bin/bash
#
#http://crazytoon.com/2007/01/23/mysql-backups-using-mysqldump/
#
DATE=`date '+%u%H'` # this sets up weekly rotation of backup files
BACKUP_DIR="/admin/backups/domain.com/" # 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):
host 123.123.123.123
user loginname
pass loginpassword
To read more about mysqldump, see man mysqldump
Pingback: mysqldump tips by crazytoon - Rusty Razor Blade
Hello! Good Site! Thanks you! olpigectkjj
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?
Phil,
1) depends on what you are using 5.x for. Most times its safe to do a restore on 4.x version from 5.x unless you have been using stored procedures, views, cursors, etc (read changes here: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html )
2) you can specify individual tables for mysqldump:
mysqldump dbname table1 table2 …
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.
Phil
[source server is win2003 with iis.]
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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=83 ;
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.
Pingback: MySQL: How do I dump all tables in a database into separate files? | Technology: Learn and Share
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$DATE.new.sql.gz
rm ${BACKUP_DIR}mysql/$HOST$DATE.sql.gz > /dev/null 2>&1
mv ${BACKUP_DIR}mysql/$HOST$DATE.new.sql ${BACKUP_DIR}mysql/$HOST$DATE.sql.gz
thanks for all the tips about the mysqldump..
These are really impressivee ideas iin on the topic of
blogging. You have touched some pleasant things here.
Any way keep up wrinting.
For a majority of businesses, thee eaasiest way to save is to go through.
Just make sure that they’re in the driver’s seat on the campaign so they don’t feel like” Oh goodness, if I spend this money then I’m leaving it all to these experts.
Clash of Clans Hack Tool
iPhone 3G,4/4S,5/5S/5C,6/6S
Alle Samsung phones/tablets
Alle LG phones
Alle Motorola phone
What’s up, just wanted to mention, I enjoyed this
blog post. It was inspiring. Keep on posting!
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.
real-time multiplayer game starring the Royales, your favorite Clash of Clans characters and much, much more.
This is great site I am coming daily because it gives good information. Thanks by the way
I am actually grateful to the owner of this site who has shared this fantastic article at here.
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?
Quality posts is the important to invite the viewers to pay
a quick visit the site, that’s what this web site is providing.
Really appriciated msql article I got benefit. such a quality article and purely helpful.
I also don’t forget how fascinating it was to play with the band-
when a song finally came collectively.
Awsome post, toon! I’m having money generated with this tool its awesome glitch of GTA5 Money I ever seen!
GTA 5 Money Generator
Good article I got my information easily.
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
it.
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.
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.
1)I’m dumping from mySQL 5.0.45 and restoring to 4.0. Do you forsee any issues?
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
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.
Pingback: ??? ?????????? ?? ?????? ? ??????? ???????, ???????????? ??? ??????? ? ???????
Pingback: Reduslim precio Comprar en farmacias españolas (original, sitio oficial)
Pingback: ??? ?????????? ?? ??????? ????
Pingback: ??? ?????????? ?? ??????? ???? ? ?????
Pingback: How to buy a prescription drug without a prescription?
Pingback: ??? ?????????? ?? ??????? ????
Pingback: Buy Atarax 10 mg, 25 mg online
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.
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.