Re: MySQL Integrity
Hi Marc, I have to open this case back :-D. I found in ur script got this line: mysqldump -u $MYSQLUSER -p$MYSQLPASSWD $DB -e -a -c --add-drop-table --add-locks -F -l > "$TMPDIR/db_exports/$DB.sql" May I know if I dont want to lock all tables ( -l )and around insert statement ( --add-locks ), can I remove those options?. It is because all my databases should not be locked for all times even a few seconds and it is always update. Marc Muehlfeld-2 wrote: > > You can't backup open database files and expect that they are fine after > you > recover them. > > > You have two ways: > > 1.) Shutdown mysql before you run amdump. Then your mysql server will be > offline during backup time. > > 2.) Export the databases to files e. g. using mysqldump and backup them. > Then > you have no downtime for your mysql server. > > > For the second way I wrote a script that exports the databases and tar.gz > them > together with my.cnf. If you use binary-logs then they are flushed and > backuped, too. You can find it here: > http://marc-muehlfeld.de/scripts/mybackup.sh > It's allready linked at the wiki, too: > http://mysqlbackup.zmanda.com/#Other_tools.2Fscripts_for_MySQL_backup > > Regards > Marc > > > -- > Marc Muehlfeld (Leitung Systemadministration) > Zentrum fuer Humangenetik und Laboratoriumsmedizin Dr. Klein und Dr. Rost > Lochhamer Str. 29 - D-82152 Martinsried > Telefon: +49(0)89/895578-0 - Fax: +49(0)89/895578-78 > http://www.medizinische-genetik.de > > -- View this message in context: http://www.nabble.com/MySQL-Integrity-tf4389486.html#a13524662 Sent from the Amanda - Users mailing list archive at Nabble.com.
Re: MySQL Integrity
>I solve this using a custom cron script that calls mysqldump on the remote >machine before calling amdump, and I save the generated dump file, not the >raw MySQL files. > >Big win, because it means that I can use this dump to restore the database on >_another_ machine without any problem (granted, I always use the same charset >for MySQL databases, so it's easier). Could you explain me more details on custom cron script and charset for MySQL databases? -- View this message in context: http://www.nabble.com/MySQL-Integrity-tf4389486.html#a12517443 Sent from the Amanda - Users mailing list archive at Nabble.com.
Re: MySQL Integrity
Hi, You can try mysql-zrm community edition. For more details see - http://www.zmanda.com/backup-mysql.html http://mysqlbackup.zmanda.com/ Taking raw backup with snapshot (you should have database on a snapshot volume) will help, this is done in mysql-zrm using mysqlhotcopy. Using mysql-zrm-scheduler, backup's can be scheduled. Adjust it before amdump. In this case, the destination directory of mysql-zrm-backup will be the disklist entry in amanda configuration. Thanks, Sachin On 9/6/07, Marc Muehlfeld <[EMAIL PROTECTED]> wrote: > > Hi, > > fedora schrieb: > > Does amanda can backup and restore databases without any error of its > > integrity? I encountered 2 times when recovering databases. I can't do > > databases check integrity (myisamchk or mysqlcheck) even all the backups > > returns no error. I tried to recover those databases many times but only > 2 > > times I cant do the integrity checking. Was it caused by amanda or my > > settings? > > > You can't backup open database files and expect that they are fine after > you > recover them. > > > You have two ways: > > 1.) Shutdown mysql before you run amdump. Then your mysql server will be > offline during backup time. > > 2.) Export the databases to files e. g. using mysqldump and backup them. > Then > you have no downtime for your mysql server. > > > For the second way I wrote a script that exports the databases and tar.gzthem > together with my.cnf. If you use binary-logs then they are flushed and > backuped, too. You can find it here: > http://marc-muehlfeld.de/scripts/mybackup.sh > It's allready linked at the wiki, too: > http://mysqlbackup.zmanda.com/#Other_tools.2Fscripts_for_MySQL_backup > > Regards > Marc > > > -- > Marc Muehlfeld (Leitung Systemadministration) > Zentrum fuer Humangenetik und Laboratoriumsmedizin Dr. Klein und Dr. Rost > Lochhamer Str. 29 - D-82152 Martinsried > Telefon: +49(0)89/895578-0 - Fax: +49(0)89/895578-78 > http://www.medizinische-genetik.de > -- Sachin Kale Zmanda Inc., amanda.zmanda.com http://www.zmanda.com/backup-mysql.html
Re: MySQL Integrity
Le jeudi 06 septembre 2007, fedora a écrit : > > Hi guys, > > I need u guys experiences who are close and familiar with amanda. I used > amanda to backup my databases in /var/lib/mysql. > Does amanda can backup and restore databases without any error of its > integrity? I encountered 2 times when recovering databases. I can't do > databases check integrity (myisamchk or mysqlcheck) even all the backups > returns no error. I tried to recover those databases many times but only 2 > times I cant do the integrity checking. Was it caused by amanda or my > settings? > Please advice guys. I solve this using a custom cron script that calls mysqldump on the remote machine before calling amdump, and I save the generated dump file, not the raw MySQL files. Big win, because it means that I can use this dump to restore the database on _another_ machine without any problem (granted, I always use the same charset for MySQL databases, so it's easier). -- Francis Galiegue, One2team - [EMAIL PROTECTED] [ATTENTION : CHANGEMENT DE COORDONNÉES !] +33178945570, +33683877875, http://www.one2team.com 40 avenue Raymond Poincaré - 75116 PARIS
Re: MySQL Integrity
Hi, fedora schrieb: Does amanda can backup and restore databases without any error of its integrity? I encountered 2 times when recovering databases. I can't do databases check integrity (myisamchk or mysqlcheck) even all the backups returns no error. I tried to recover those databases many times but only 2 times I cant do the integrity checking. Was it caused by amanda or my settings? You can't backup open database files and expect that they are fine after you recover them. You have two ways: 1.) Shutdown mysql before you run amdump. Then your mysql server will be offline during backup time. 2.) Export the databases to files e. g. using mysqldump and backup them. Then you have no downtime for your mysql server. For the second way I wrote a script that exports the databases and tar.gz them together with my.cnf. If you use binary-logs then they are flushed and backuped, too. You can find it here: http://marc-muehlfeld.de/scripts/mybackup.sh It's allready linked at the wiki, too: http://mysqlbackup.zmanda.com/#Other_tools.2Fscripts_for_MySQL_backup Regards Marc -- Marc Muehlfeld (Leitung Systemadministration) Zentrum fuer Humangenetik und Laboratoriumsmedizin Dr. Klein und Dr. Rost Lochhamer Str. 29 - D-82152 Martinsried Telefon: +49(0)89/895578-0 - Fax: +49(0)89/895578-78 http://www.medizinische-genetik.de
