Re: Recommended backup scripts for mysql databases
Thanks for all your help. I now have a nice backup script running. What I'm planning to do a little later this summer is to set up a RAID backup server on my DSL connection ftp downloading the mysql databases every night from my co-location server. This way I will have a nice archive of dumps on two locations to be even safer. In my setup I now have 3 my.sh scripts executed at the same time through a cronjob. Could it be problematic to do this at the same time or does it not matter? The databases aren't large. Cheers, Andreas
Re: Recommended backup scripts for mysql databases
I finally got the script working. Seems to run smooth on my FreeBSD 4.11system:
MYSQLDUMP=`which mysqldump 2/dev/null` ||
MYSQLDUMP=/usr/local/bin/mysqldump
MYSQLDUMP_ARGS=--opt --extended-insert -h localhost -umyuser -pmypassword
mydb
ARCHDIR=/backup/mysql
NAME=db_dump
# Remove archives older than 64 days
find ${ARCHDIR} -type f -mtime +64 | xargs rm -f
# Create new archives
cd ${ARCHDIR} ${MYSQLDUMP} ${MYSQLDUMP_ARGS} ${NAME}.`date +%Y%m%d`
I just have two more questions:
Should I use --extended-insert or not? I guess I can use both, but what is
recommended?
What would the correct script/way to restore the database?
Thanks,
Andreas
Re: Recommended backup scripts for mysql databases
Good morning Andreas -
The --opt flag implies --extended-insert, in addition to some other
options, to generate an optimized (fast) dump file. See man
mysqldump. You don't need both but having both shouldn't hurt.
To restore, pipe your dump file back into the mysql client, a la
mysql -u user -ppassword -D database db_dump20060330
Keep in mind that one dump file may contain data for multiple
databases depending on how you have things set up. Not really a
problem until you want to restore just one database or just one table
- then you have to get creative.
Dan
On 6/30/06, Andreas Widerøe Andersen [EMAIL PROTECTED] wrote:
I finally got the script working. Seems to run smooth on my FreeBSD 4.11system:
MYSQLDUMP=`which mysqldump 2/dev/null` ||
MYSQLDUMP=/usr/local/bin/mysqldump
MYSQLDUMP_ARGS=--opt --extended-insert -h localhost -umyuser -pmypassword
mydb
ARCHDIR=/backup/mysql
NAME=db_dump
# Remove archives older than 64 days
find ${ARCHDIR} -type f -mtime +64 | xargs rm -f
# Create new archives
cd ${ARCHDIR} ${MYSQLDUMP} ${MYSQLDUMP_ARGS} ${NAME}.`date +%Y%m%d`
I just have two more questions:
Should I use --extended-insert or not? I guess I can use both, but what is
recommended?
What would the correct script/way to restore the database?
Thanks,
Andreas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Recommended backup scripts for mysql databases
I am in the process of designing the backup procedures for a soon-to-be production DB. I have gone back and forth on mysqldump and mysqlhotcopy. As I see it (for MyISAM tables), the hot copy backs up faster, restores faster, but does not allow for selective restores (Hello, Support? I accidentally deleted student Tim Lucia... can you get him back). Mysqldump is slower to back up, slower to restore, but allows for selective restores. Are those the only things to consider? I know that piping mysqldump into gzip results in disk space savings of the dump file, whereas mysqlhotcopy requires n * 2 free bytes on your system. But at least you know in advance exactly how much space it will take... Tim -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 27, 2006 8:50 AM To: Andreas Widerøe Andersen Cc: mysql@lists.mysql.com Subject: Re: Recommended backup scripts for mysql databases Andreas, if you are only using MyISAM tables, the included mysqlhotcopy script may work for you. We used it at my previous employer with good results. We would run it to create a snapshot of our data files every day, then run a network backup utility that backed up the snapshot (but did not access the live data files). Other folks have recommended mysqldump, which is also a good solution, but could potentially take a long time to restore into the database, depending on your data. Dan Andreas Widerøe Andersen wrote: Hi, I have a few FreeBSD servers running various web/database things and I'm looking for a good a reliable backup script that I can run through a cronjob. I'm currently running the latest version of mysql323, but will upgrade to version 4.1 soon aswell as upgrade most of the servers to the latest FreeBSD version. Any good suggestions to a script that will back up my databases and make things ready for an easy restore if I need to? Thanks! Andreas -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recommended backup scripts for mysql databases
Tim Lucia wrote: I am in the process of designing the backup procedures for a soon-to-be production DB. I have gone back and forth on mysqldump and mysqlhotcopy. As I see it (for MyISAM tables), the hot copy backs up faster, restores faster, but does not allow for selective restores (Hello, Support? I accidentally deleted student Tim Lucia... can you get him back). Mysqldump is slower to back up, slower to restore, but allows for selective restores. Yes it does. Copy the table you want to a separate database ( directory ). INSERT INTO active.student SELECT * from backup.student WHERE name='Tim ucia'; Are those the only things to consider? I know that piping mysqldump into gzip resultThis on condition does not include the table being joined.s in disk space savings of the dump file, whereas mysqlhotcopy requires n * 2 free bytes on your system. But at least you know in advance exactly how much space it will take... Tim -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recommended backup scripts for mysql databases
Hi Tim - those are all important considerations, yes. In my mind neither mysqldump nor mysqlhotcopy really make selective restore of one or a few records truly easy. Unless you edit the SQL dump file (impratical in most text editors when it gets beyond several MB) you won't be able to restore a record any more easily than from mysqlhotcopy. It's certainly possible to accomplish a selective restore with backups from either method, though. I wouldn't say easy but it's not bad. Picture this: - you have database DATABASE, with tables TABLE1 and TABLE2 - you back them up, either to SQL dump or with hotcopy - somebody makes a mistake - you need to restore a record for STUDENT1 - you create database RESTORE_DB - you pipe the DATABASE SQL dump file into RESTORE_DB, creating TABLE1 and TABLE2 - OR, you place copies of the hot-copied TABLE1.* and TABLE2.* files into the RESTORE_DB directory (possibly faster than waiting for an SQL dump to load) - in the mysql client, perform a SQL operation such as INSERT INTO DATABASE.TABLE1 SELECT * from RESTORE_DB.TABLE1 WHERE RESTORE_DB.TABLE1.STUDENTNAME = 'STUDENT1' Make sense? Dan On 6/29/06, Tim Lucia [EMAIL PROTECTED] wrote: I am in the process of designing the backup procedures for a soon-to-be production DB. I have gone back and forth on mysqldump and mysqlhotcopy. As I see it (for MyISAM tables), the hot copy backs up faster, restores faster, but does not allow for selective restores (Hello, Support? I accidentally deleted student Tim Lucia... can you get him back). Mysqldump is slower to back up, slower to restore, but allows for selective restores. Are those the only things to consider? I know that piping mysqldump into gzip results in disk space savings of the dump file, whereas mysqlhotcopy requires n * 2 free bytes on your system. But at least you know in advance exactly how much space it will take... Tim -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 27, 2006 8:50 AM To: Andreas Widerøe Andersen Cc: mysql@lists.mysql.com Subject: Re: Recommended backup scripts for mysql databases Andreas, if you are only using MyISAM tables, the included mysqlhotcopy script may work for you. We used it at my previous employer with good results. We would run it to create a snapshot of our data files every day, then run a network backup utility that backed up the snapshot (but did not access the live data files). Other folks have recommended mysqldump, which is also a good solution, but could potentially take a long time to restore into the database, depending on your data. Dan Andreas Widerøe Andersen wrote: Hi, I have a few FreeBSD servers running various web/database things and I'm looking for a good a reliable backup script that I can run through a cronjob. I'm currently running the latest version of mysql323, but will upgrade to version 4.1 soon aswell as upgrade most of the servers to the latest FreeBSD version. Any good suggestions to a script that will back up my databases and make things ready for an easy restore if I need to? Thanks! Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recommended backup scripts for mysql databases
Thanks for the examples. I haven't been able to get them to work yet.
Here's what I tried:
MYSQL=`which mysql 2/dev/null` || MYSQL=/usr/local/bin/mysql
MYSQL_ARGS=--opt -uroot -pmypassword db
ARCHDIR=/backup/mysql
NAME=db_dump
# Remove archives older than 64 days
find ${ARCHDIR} -type f -mtime +64 | xargs rm -f
# Create new archives
cd ${ARCHDIR} mysqldump ${MYSQL} ${MYSQL_ARGS} ${NAME}.`date +%Y%m%d`
I only get:
mysqldump: Got error: 1045: Access denied for user: '[EMAIL PROTECTED]' (Using
password: NO) when trying to connect
While I can login to mysql easily with this user/pass from the command
prompt. I have also tried the specific user for this database. Same problem.
Any ideas?
Thanks,
Andreas
Re: Recommended backup scripts for mysql databases
Looks like your full command would end up being:
mysqldump /usr/local/bin/mysql --opt -uroot -pmypassword db
which makes little sense. (You don't need that ${MYSQL} in there).
Instead, maybe what you want is to use an environment variable for
mysqldump, a la
MYSQLDUMP=`which mysqldump 2/dev/null` ||
MYSQLDUMP=/usr/local/bin/mysqldump
then later
cd ${ARCHDIR} ${MYSQLDUMP} ${MYSQLDUMP_ARGS} ${NAME}.`date +%Y%m%d`
Dan
On 6/29/06, Andreas Widerøe Andersen [EMAIL PROTECTED] wrote:
Thanks for the examples. I haven't been able to get them to work yet.
Here's what I tried:
MYSQL=`which mysql 2/dev/null` || MYSQL=/usr/local/bin/mysql
MYSQL_ARGS=--opt -uroot -pmypassword db
ARCHDIR=/backup/mysql
NAME=db_dump
# Remove archives older than 64 days
find ${ARCHDIR} -type f -mtime +64 | xargs rm -f
# Create new archives
cd ${ARCHDIR} mysqldump ${MYSQL} ${MYSQL_ARGS} ${NAME}.`date +%Y%m%d`
I only get:
mysqldump: Got error: 1045: Access denied for user: '[EMAIL PROTECTED]' (Using
password: NO) when trying to connect
While I can login to mysql easily with this user/pass from the command
prompt. I have also tried the specific user for this database. Same problem.
Any ideas?
Thanks,
Andreas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recommended backup scripts for mysql databases
On 6/29/06, Tim Lucia [EMAIL PROTECTED] wrote: I am in the process of designing the backup procedures for a soon-to-be production DB. I have gone back and forth on mysqldump and mysqlhotcopy. As I see it (for MyISAM tables), the hot copy backs up faster, restores faster, but does not allow for selective restores (Hello, Support? I accidentally deleted student Tim Lucia... can you get him back). Mysqldump is slower to back up, slower to restore, but allows for selective restores. Are those the only things to consider? I know that piping mysqldump into gzip results in disk space savings of the dump file, whereas mysqlhotcopy requires n * 2 free bytes on your system. But at least you know in advance exactly how much space it will take... A few years back, we had a database that was worth a lot of money, monthly paid by 3 or 4 clients that wanted security and easily restore/crash recovery. So we studied a mysql fail-safe setup like this: We had 3 servers, 2 running on mirrors, 1 master, 2 slaves replicating. We scheduled the slave to be stopped and started so it would be a backup till yesterday, the second would be a mirror of the master in real time. We start one the slaves at midnight (low usage) and stop it at around 5am, so, the rest of the day its data would not be updated. We also setup cron jobs backing up the mirror slave everyday (no load on the master), and keeping the backups for 6 months. On a crash situation, we simply turned the hot mirror the new master and tried to fix the old master. In the event of lost data (your missing student) we would use the slave (starting it using no-network) to retrieve the rows (easier than getting it from a .sql), or we would setup a new server running on different port and populate it with one of the table separated backups, retrieving the data. It was efficient at that time, nowadays you have heartbeat and other tools, allied to a DNS, it can do a LOT more (even non-assisted crash recovery). But if you have a small dataset you can try this approach. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Recommended backup scripts for mysql databases
-Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Thursday, June 29, 2006 9:26 AM To: Tim Lucia Cc: mysql@lists.mysql.com Subject: Re: Recommended backup scripts for mysql databases Hi Tim - those are all important considerations, yes. In my mind neither mysqldump nor mysqlhotcopy really make selective restore of one or a few records truly easy. Unless you edit the SQL dump file (impratical in most text editors when it gets beyond several MB) you won't be able to restore a record any more easily than from mysqlhotcopy. It's certainly possible to accomplish a selective restore with backups from either method, though. I wouldn't say easy but it's not bad. Picture this: - you have database DATABASE, with tables TABLE1 and TABLE2 - you back them up, either to SQL dump or with hotcopy - somebody makes a mistake - you need to restore a record for STUDENT1 - you create database RESTORE_DB - you pipe the DATABASE SQL dump file into RESTORE_DB, creating TABLE1 and TABLE2 - OR, you place copies of the hot-copied TABLE1.* and TABLE2.* files into the RESTORE_DB directory (possibly faster than waiting for an SQL dump to load) - in the mysql client, perform a SQL operation such as INSERT INTO DATABASE.TABLE1 SELECT * from RESTORE_DB.TABLE1 WHERE RESTORE_DB.TABLE1.STUDENTNAME = 'STUDENT1' Make sense? Yes. Thanks to Dan and Daniel. I do expect that if you know the student that was accidentally (deleted, updated, ...) then you can find the appropriate data from the dump via fgrep, which can handle large files pretty quickly, and without having to attach the backup copy of the table to the server. Dan On 6/29/06, Tim Lucia [EMAIL PROTECTED] wrote: I am in the process of designing the backup procedures for a soon-to-be production DB. I have gone back and forth on mysqldump and mysqlhotcopy. As I see it (for MyISAM tables), the hot copy backs up faster, restores faster, but does not allow for selective restores (Hello, Support? I accidentally deleted student Tim Lucia... can you get him back). Mysqldump is slower to back up, slower to restore, but allows for selective restores. Are those the only things to consider? I know that piping mysqldump into gzip results in disk space savings of the dump file, whereas mysqlhotcopy requires n * 2 free bytes on your system. But at least you know in advance exactly how much space it will take... Tim -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 27, 2006 8:50 AM To: Andreas Widerøe Andersen Cc: mysql@lists.mysql.com Subject: Re: Recommended backup scripts for mysql databases Andreas, if you are only using MyISAM tables, the included mysqlhotcopy script may work for you. We used it at my previous employer with good results. We would run it to create a snapshot of our data files every day, then run a network backup utility that backed up the snapshot (but did not access the live data files). Other folks have recommended mysqldump, which is also a good solution, but could potentially take a long time to restore into the database, depending on your data. Dan Andreas Widerøe Andersen wrote: Hi, I have a few FreeBSD servers running various web/database things and I'm looking for a good a reliable backup script that I can run through a cronjob. I'm currently running the latest version of mysql323, but will upgrade to version 4.1 soon aswell as upgrade most of the servers to the latest FreeBSD version. Any good suggestions to a script that will back up my databases and make things ready for an easy restore if I need to? Thanks! Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recommended backup scripts for mysql databases
That's a good thought, Tim - file grep could provide a quick means to access the data from a mysqldump file. Probably a dumb suggestion because I'm sure you'll do this, but you should try it out ahead of time, before you need to actually restore something. The reason I say this is - if you use some of the more performance-oriented options with mysqldump, like --opt or --extended-insert, grepping may not easily find you just the row(s) you need, since your dump file ends up being a sea of concatenated insert values with (IIRC) no linebreaks in between. I think it's 6 of one, half a dozen of the other. Each method has slight advantages/disadvantages but both provide a sound means of taking a backup, so you have to decide if any of the slight advantages puts one out in front in your particular situation. Dan On 6/29/06, Tim Lucia [EMAIL PROTECTED] wrote: -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Thursday, June 29, 2006 9:26 AM To: Tim Lucia Cc: mysql@lists.mysql.com Subject: Re: Recommended backup scripts for mysql databases Hi Tim - those are all important considerations, yes. In my mind neither mysqldump nor mysqlhotcopy really make selective restore of one or a few records truly easy. Unless you edit the SQL dump file (impratical in most text editors when it gets beyond several MB) you won't be able to restore a record any more easily than from mysqlhotcopy. It's certainly possible to accomplish a selective restore with backups from either method, though. I wouldn't say easy but it's not bad. Picture this: - you have database DATABASE, with tables TABLE1 and TABLE2 - you back them up, either to SQL dump or with hotcopy - somebody makes a mistake - you need to restore a record for STUDENT1 - you create database RESTORE_DB - you pipe the DATABASE SQL dump file into RESTORE_DB, creating TABLE1 and TABLE2 - OR, you place copies of the hot-copied TABLE1.* and TABLE2.* files into the RESTORE_DB directory (possibly faster than waiting for an SQL dump to load) - in the mysql client, perform a SQL operation such as INSERT INTO DATABASE.TABLE1 SELECT * from RESTORE_DB.TABLE1 WHERE RESTORE_DB.TABLE1.STUDENTNAME = 'STUDENT1' Make sense? Yes. Thanks to Dan and Daniel. I do expect that if you know the student that was accidentally (deleted, updated, ...) then you can find the appropriate data from the dump via fgrep, which can handle large files pretty quickly, and without having to attach the backup copy of the table to the server. Dan On 6/29/06, Tim Lucia [EMAIL PROTECTED] wrote: I am in the process of designing the backup procedures for a soon-to-be production DB. I have gone back and forth on mysqldump and mysqlhotcopy. As I see it (for MyISAM tables), the hot copy backs up faster, restores faster, but does not allow for selective restores (Hello, Support? I accidentally deleted student Tim Lucia... can you get him back). Mysqldump is slower to back up, slower to restore, but allows for selective restores. Are those the only things to consider? I know that piping mysqldump into gzip results in disk space savings of the dump file, whereas mysqlhotcopy requires n * 2 free bytes on your system. But at least you know in advance exactly how much space it will take... Tim -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 27, 2006 8:50 AM To: Andreas Widerøe Andersen Cc: mysql@lists.mysql.com Subject: Re: Recommended backup scripts for mysql databases Andreas, if you are only using MyISAM tables, the included mysqlhotcopy script may work for you. We used it at my previous employer with good results. We would run it to create a snapshot of our data files every day, then run a network backup utility that backed up the snapshot (but did not access the live data files). Other folks have recommended mysqldump, which is also a good solution, but could potentially take a long time to restore into the database, depending on your data. Dan Andreas Widerøe Andersen wrote: Hi, I have a few FreeBSD servers running various web/database things and I'm looking for a good a reliable backup script that I can run through a cronjob. I'm currently running the latest version of mysql323, but will upgrade to version 4.1 soon aswell as upgrade most of the servers to the latest FreeBSD version. Any good suggestions to a script that will back up my databases and make things ready for an easy restore if I need to? Thanks! Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Recommended backup scripts for mysql databases
If it were so cut-and-dry, I would know which one to choose ;-) Obviously it's not. I'm leaning towards the mysqlhotcopy because it is way faster, and now I know how to selectively restore. I have a slight concern with the self-professed beta nature of it -- is that unfounded? Tim -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Thursday, June 29, 2006 6:01 PM To: Tim Lucia Cc: mysql@lists.mysql.com Subject: Re: Recommended backup scripts for mysql databases That's a good thought, Tim - file grep could provide a quick means to access the data from a mysqldump file. Probably a dumb suggestion because I'm sure you'll do this, but you should try it out ahead of time, before you need to actually restore something. The reason I say this is - if you use some of the more performance-oriented options with mysqldump, like --opt or --extended-insert, grepping may not easily find you just the row(s) you need, since your dump file ends up being a sea of concatenated insert values with (IIRC) no linebreaks in between. I think it's 6 of one, half a dozen of the other. Each method has slight advantages/disadvantages but both provide a sound means of taking a backup, so you have to decide if any of the slight advantages puts one out in front in your particular situation. Dan On 6/29/06, Tim Lucia [EMAIL PROTECTED] wrote: -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Thursday, June 29, 2006 9:26 AM To: Tim Lucia Cc: mysql@lists.mysql.com Subject: Re: Recommended backup scripts for mysql databases Hi Tim - those are all important considerations, yes. In my mind neither mysqldump nor mysqlhotcopy really make selective restore of one or a few records truly easy. Unless you edit the SQL dump file (impratical in most text editors when it gets beyond several MB) you won't be able to restore a record any more easily than from mysqlhotcopy. It's certainly possible to accomplish a selective restore with backups from either method, though. I wouldn't say easy but it's not bad. Picture this: - you have database DATABASE, with tables TABLE1 and TABLE2 - you back them up, either to SQL dump or with hotcopy - somebody makes a mistake - you need to restore a record for STUDENT1 - you create database RESTORE_DB - you pipe the DATABASE SQL dump file into RESTORE_DB, creating TABLE1 and TABLE2 - OR, you place copies of the hot-copied TABLE1.* and TABLE2.* files into the RESTORE_DB directory (possibly faster than waiting for an SQL dump to load) - in the mysql client, perform a SQL operation such as INSERT INTO DATABASE.TABLE1 SELECT * from RESTORE_DB.TABLE1 WHERE RESTORE_DB.TABLE1.STUDENTNAME = 'STUDENT1' Make sense? Yes. Thanks to Dan and Daniel. I do expect that if you know the student that was accidentally (deleted, updated, ...) then you can find the appropriate data from the dump via fgrep, which can handle large files pretty quickly, and without having to attach the backup copy of the table to the server. Dan On 6/29/06, Tim Lucia [EMAIL PROTECTED] wrote: I am in the process of designing the backup procedures for a soon- to-be production DB. I have gone back and forth on mysqldump and mysqlhotcopy. As I see it (for MyISAM tables), the hot copy backs up faster, restores faster, but does not allow for selective restores (Hello, Support? I accidentally deleted student Tim Lucia... can you get him back). Mysqldump is slower to back up, slower to restore, but allows for selective restores. Are those the only things to consider? I know that piping mysqldump into gzip results in disk space savings of the dump file, whereas mysqlhotcopy requires n * 2 free bytes on your system. But at least you know in advance exactly how much space it will take... Tim -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 27, 2006 8:50 AM To: Andreas Widerøe Andersen Cc: mysql@lists.mysql.com Subject: Re: Recommended backup scripts for mysql databases Andreas, if you are only using MyISAM tables, the included mysqlhotcopy script may work for you. We used it at my previous employer with good results. We would run it to create a snapshot of our data files every day, then run a network backup utility that backed up the snapshot (but did not access the live data files). Other folks have recommended mysqldump, which is also a good solution, but could potentially take a long time to restore into the database, depending on your data. Dan Andreas Widerøe Andersen wrote: Hi, I have a few FreeBSD servers running various web/database things and I'm looking for a good
Re: Recommended backup scripts for mysql databases
Those warnings always give me pause, too - but I used mysqlhotcopy in production for about 5 years at my previous place of employment. Probably like the warnings about threading in perl 5.8 - been using threads in production for nearly 2 years without a problem. Only 2 problems with mysqlhotcopy that I recall, both related to the same poor decision on my part: I hard-coded the list of databases to be backed up in a shell script run by a cron job. Problem #1: when people created new databases, they wouldn't be backed up until we remembered to add them to that shell script. Problem #2: when someone dropped a database, the nightly snapshot started failing until we removed that database name from the shell script. Would've been a lot better if I'd started off using a regex to specify all databases instead (something like --regexp=/./ ). Come to think of it, I don't know if using a regex was an option in 2001 ... I see it in the man page now. Neat-o. I only once had to restore a table in anger, after one of my staff made use of a poorly qualified delete statment. Worked fine. I did use the hot-copied files several times to set up test databases and later a replication server. Note: using hot-copied files to set up test databases will break replication. Bummer, but quite logical. Dan On 6/29/06, Tim Lucia [EMAIL PROTECTED] wrote: If it were so cut-and-dry, I would know which one to choose ;-) Obviously it's not. I'm leaning towards the mysqlhotcopy because it is way faster, and now I know how to selectively restore. I have a slight concern with the self-professed beta nature of it -- is that unfounded? Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recommended backup scripts for mysql databases
Hi, I have a few FreeBSD servers running various web/database things and I'm looking for a good a reliable backup script that I can run through a cronjob. I'm currently running the latest version of mysql323, but will upgrade to version 4.1 soon aswell as upgrade most of the servers to the latest FreeBSD version. Any good suggestions to a script that will back up my databases and make things ready for an easy restore if I need to? Thanks! Andreas
Re: Recommended backup scripts for mysql databases
On 6/27/06, Andreas Widerøe Andersen [EMAIL PROTECTED] wrote: Hi, I have a few FreeBSD servers running various web/database things and I'm looking for a good a reliable backup script that I can run through a cronjob. I'm currently running the latest version of mysql323, but will upgrade to version 4.1 soon aswell as upgrade most of the servers to the latest FreeBSD version. Any good suggestions to a script that will back up my databases and make things ready for an easy restore if I need to? 30 23 * * * date=`date -I` /usr/bin/mysqldump --opt -B -C -hhost -user -ppassword database | bzip2 -c path/to/backup/$date-database.sql.bz2 I guess it will work on a FreeBSD, its my own cron job here... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recommended backup scripts for mysql databases
On Tue, Jun 27, 2006 at 08:19:41AM -0300, Daniel da Veiga wrote:
On 6/27/06, Andreas Widerøe Andersen [EMAIL PROTECTED] wrote:
Hi,
I have a few FreeBSD servers running various web/database things and I'm
looking for a good a reliable backup script that I can run through a
cronjob. I'm currently running the latest version of mysql323, but will
upgrade to version 4.1 soon aswell as upgrade most of the servers to the
latest FreeBSD version.
Any good suggestions to a script that will back up my databases and make
things ready for an easy restore if I need to?
30 23 * * * date=`date -I` /usr/bin/mysqldump --opt -B -C -hhost
-user -ppassword database | bzip2 -c
path/to/backup/$date-database.sql.bz2
I guess it will work on a FreeBSD, its my own cron job here...
I would also suggest removing old archive copies:
MYSQL=`which mysql 2/dev/null` || MYSQL=/usr/local/bin/mysql
MYSQL_ARGS=--opt -h localhost -uuser -ppass db
ARCHDIR=/usr/local/db/blah
NAME=db_dump
# Remove archives older than 64 days
find ${ARCHDIR} -type f -mtime +64 | xargs rm -f
# Create new archives
cd ${ARCHDIR} mysqldump ${MYSQL} ${MYSQL_ARGS} ${NAME}.`date +%Y%m%d`
Cheers,
--
Timur Izhbulatov
OILspace, 26 Leninskaya sloboda, bld. 2, 2nd floor, 115280 Moscow, Russia
P:+7 495 105 7245 + ext.205 F:+7 495 105 7246 E:[EMAIL PROTECTED]
Building Successful Supply Chains - One Solution At A Time.
www.oilspace.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recommended backup scripts for mysql databases
Andreas, if you are only using MyISAM tables, the included mysqlhotcopy script may work for you. We used it at my previous employer with good results. We would run it to create a snapshot of our data files every day, then run a network backup utility that backed up the snapshot (but did not access the live data files). Other folks have recommended mysqldump, which is also a good solution, but could potentially take a long time to restore into the database, depending on your data. Dan Andreas Widerøe Andersen wrote: Hi, I have a few FreeBSD servers running various web/database things and I'm looking for a good a reliable backup script that I can run through a cronjob. I'm currently running the latest version of mysql323, but will upgrade to version 4.1 soon aswell as upgrade most of the servers to the latest FreeBSD version. Any good suggestions to a script that will back up my databases and make things ready for an easy restore if I need to? Thanks! Andreas -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
