Re: Mysql backup for large databases
Hello, Just one more suggestion to do full backups in large databases: - Dedicated slave (either physical machine, a disk cabinet using iscsi connections from a machine just with a bunch of RAM etc) - Get the slave delayed a certain time (ie: 1 hour, 2 hours...depends on your workload) using Percona Toolkit ( http://www.percona.com/doc/percona-toolkit/2.1/pt-slave-delay.html) - Make sure that slave has (*log*-*slave*-*updates) *in my.cnf. - Make sure the master keeps the necessary logs for a point in time recovery if needed. If something goes wrong you have a full copy of your database, just delayed a few hours. If you want to recover from a drop database (or any big fail executed from MySQL CLI) in the master, you can just sync up your delayed slave to that specific statment - go thru your master binlog, locate the drop database statement and set START SLAVE SQL_THREAD UNTIL master_log_file = 'mysql-bin.XXX', master_log_pos=XXX; to the position right before the bad one. That way you'd have your database as it was just before the wrong statement execution. As Rick said - if you're thinking about doing snapshots, make sure you stop your mysql daemon in the delayed slave to make sure everything is committed to disk. Otherwise can end up having a corrupted DB which won't boot when you need it. And lastly, but probably most importanttest your backups periodically!! Hope this helps Manuel. 2012/11/1 Karen Abgarian a...@apple.com Hi, For doing backups on the primary database, I know nothing better than have your tables in InnoDB and use Innobackup (or MySQL Enterprise backup). This, however, still has the possibility of hanging as it is using FLUSH TABLES WITH READ LOCK for taking backups of MyISAM tables.One may want to script it to kill the backup if the wait exceeds some threshold. The backup taken this way has incremental backups feature which may reduce the impact. For offloading the backups to a replica, there exist more options because the replica can be frozen and/or shut down. For an InnoDB database, it has to be shut down for taking a consistent backup. If it is not, it will result in cute little inconsistencies unless a DBA is one lucky guy and always wins playing roulette. Combining the two, I like the idea of doing EM backup on a replica and having all tables in InnoDB. After a backup has been taken, it will eventually need to be restored unless someone just likes taking them. For this reason, it will have to be brought to the recovered system. Unless somebody knows in advance when the database would need to be recovered (f.e. it is known that a bad guy always corrupts it on Monday mornings), the backup will need to be available for restore always. These considerations usually imply things like shared filesystems between primary and replica, rejecting backups for recoveries across datacenters and the like. Backing up binary logs allows providing continuous coverage for recovery instead of discrete. Cheers Karen On 01.11.2012, at 8:53, machiel.richa...@gmail.com wrote: Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.netmailto: h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave
Re: Mysql backup for large databases
of discrete. Cheers Karen On 01.11.2012, at 8:53, machiel.richa...@gmail.com wrote: Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.netmailto: h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime ‹¢ÒÒ‹¤◊5  vVæW Â Ö ˆÆˆær Ƙ7@‹¤f÷ Ƙ7B 6∫˜fW3¢ ΩGG ¢òöƘ7G2æ◊˜7 Âæ6öÒö◊˜7 À‹¥Fò Vç7V'67ˆS¢ΩGG ¢òöƘ7G2æ◊˜7 Âæ6öÒö◊˜7 À‹ ‹ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Manuel Aróstegui Systems Team tuenti.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql backup for large databases
On 01/11/2012 11.28, Machiel Richards - Gmail wrote: [...] I am busy investigating some options relating to the backup for MySQL databases when they get quite large. When using the MySQL enterprise, there is the option to use the MySQL enterprise backup as it is part of the Enterprise license. However, when using the GA (freely available) versions, the options for backups on large databases seems to be a bit limited. [...] Hi Machiel, I'm currently evaluating Percona xtrabackup for the same reasons. A few notes: - backup/restore times will be definitely shorter compered to mysqldump - MyISAM tables will be locked for consistency during the backup (just like with MEB or mysqldump). This is optional, but it's required if you want a consistent backup - if I read the documentation correctly, the only way to restore a single database in a consolidated environment is to use the -export option AND percona server as an import server Hope this helps Dimitre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql backup for large databases
Am 01.11.2012 11:28, schrieb Machiel Richards - Gmail: Using mysqldump and restores on an 80-100GB database seems a bit unpractical as the restore times seems to get quite long as well as the backup times. * setup a master/slave configuration * stop the slave * rsync the raw datadir to whatever backup-medium/location * start the salve again signature.asc Description: OpenPGP digital signature
RE: Mysql backup for large databases
Full backup: * Xtrabackup (Backup: slight impact on source; more if you have MyISAM (as mentioned)) * Slave (Backup: zero impact on Master -- once replication is set up) * LVM -- a minute of server down; see below Full restore: * Xtrabackup - Takes time * Slave - minute(s) to failover, mostly dealing with clients pointing to the new master. * LVM -- a minute? see below With LVM you are taking a filesystem snapshot. This requires a brief restart of mysqld to assure that anything cached is sync'd to disk. After the snapshot is taken, you are at liberty to copy the snapshot to somewhere else. (This must be done before you fill up the volume used for copy-on-write stuff, etc.) The snapshot can be used to instantly restore the entire system on this or some other server. For partial backups... * Xtrabackup - already discussed * Slave -- You are free to construct whatever slicing and dicing, even changing engines to MyISAM and copying files. * LVM -- probably not useful. Consider using PARTITIONing. With it, you could split up a table according to time, copy (row by row) the oldest partition to somewhere else, DROP PARTITION (instantaneous). Restoring is no better than LOAD DATA. In the near future (5.6.x?), you can disconnect a partition from a table and move it to another table; this will greatly speed up archiving. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Thursday, November 01, 2012 4:47 AM To: mysql@lists.mysql.com Subject: Re: Mysql backup for large databases Am 01.11.2012 11:28, schrieb Machiel Richards - Gmail: Using mysqldump and restores on an 80-100GB database seems a bit unpractical as the restore times seems to get quite long as well as the backup times. * setup a master/slave configuration * stop the slave * rsync the raw datadir to whatever backup-medium/location * start the salve again -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql backup for large databases
On Thu, Nov 1, 2012 at 11:34 AM, Rick James rja...@yahoo-inc.com wrote: Full backup: * Xtrabackup (Backup: slight impact on source; more if you have MyISAM (as mentioned)) * Slave (Backup: zero impact on Master -- once replication is set up) * LVM -- a minute of server down; see below Why do you need downtime? Full restore: * Xtrabackup - Takes time * Slave - minute(s) to failover, mostly dealing with clients pointing to the new master. * LVM -- a minute? see below With LVM you are taking a filesystem snapshot. This requires a brief restart of mysqld to assure that anything cached is sync'd to disk. After the snapshot is taken, you are at liberty to copy the snapshot to somewhere else. (This must be done before you fill up the volume used for copy-on-write stuff, etc.) The snapshot can be used to instantly restore the entire system on this or some other server. For partial backups... * Xtrabackup - already discussed * Slave -- You are free to construct whatever slicing and dicing, even changing engines to MyISAM and copying files. * LVM -- probably not useful. Consider using PARTITIONing. With it, you could split up a table according to time, copy (row by row) the oldest partition to somewhere else, DROP PARTITION (instantaneous). Restoring is no better than LOAD DATA. In the near future (5.6.x?), you can disconnect a partition from a table and move it to another table; this will greatly speed up archiving. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Thursday, November 01, 2012 4:47 AM To: mysql@lists.mysql.com Subject: Re: Mysql backup for large databases Am 01.11.2012 11:28, schrieb Machiel Richards - Gmail: Using mysqldump and restores on an 80-100GB database seems a bit unpractical as the restore times seems to get quite long as well as the backup times. * setup a master/slave configuration * stop the slave * rsync the raw datadir to whatever backup-medium/location * start the salve again -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql backup for large databases
Am 01.11.2012 16:36, schrieb Singer Wang: On Thu, Nov 1, 2012 at 11:34 AM, Rick James rja...@yahoo-inc.com mailto:rja...@yahoo-inc.com wrote: Full backup: * Xtrabackup (Backup: slight impact on source; more if you have MyISAM (as mentioned)) * Slave (Backup: zero impact on Master -- once replication is set up) * LVM -- a minute of server down; see below Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime signature.asc Description: OpenPGP digital signature
Re: Mysql backup for large databases
Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) S S On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.netwrote: Am 01.11.2012 16:36, schrieb Singer Wang: On Thu, Nov 1, 2012 at 11:34 AM, Rick James rja...@yahoo-inc.commailto: rja...@yahoo-inc.com wrote: Full backup: * Xtrabackup (Backup: slight impact on source; more if you have MyISAM (as mentioned)) * Slave (Backup: zero impact on Master -- once replication is set up) * LVM -- a minute of server down; see below Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime
Re: Mysql backup for large databases
good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime signature.asc Description: OpenPGP digital signature
Re: Mysql backup for large databases
Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime
Re: Mysql backup for large databases
as said: use a replication slave dedicated for backups you can even let a slave write a binlog and sync another slave with this one * rsync backups working with diff * they are extremly fast after the first time * a dedicated backup-slave has ZERO impact i am doing rsync-backups of 1.5 TB data over a WAN link since years each day and the real traffic is between 2 and 5 GB each day Am 01.11.2012 16:53, schrieb machiel.richa...@gmail.com: Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime signature.asc Description: OpenPGP digital signature
RE: Mysql backup for large databases
Are both Masters writeable? If so, you have even worse problems. If not, then consider switching to one master with 2 slaves and use MHA for failover, etc. With that, you can seamlessly and nearly instantly switch to any slave. It does not involve a restore, but gets you alive very quickly by switching to another machine. For your situation: Master (local), another Slave somewhere else but nearby, Slave in South Africa. Then, during failover, prefer the nearby slave. After failing over, worry about 'fixing' the dead Master at your leisure. What is the purpose of the restore? 1. Recovery from dead Master? -- MHA may be best 2. Recovery to some previous point-in-time? -- much of the discussion has centered around this obscure use 3. Building staging/qa/dev/etc machine that matches the master at some point in time? There are different solutions to each of these. #3 is probably best done by having a slave hanging off the Master, then disconnecting it when you want it. If you muck with the data, it will need a long restore; if not, it is a matter of reconnecting and letting replication catch up. -Original Message- From: machiel.richa...@gmail.com [mailto:machiel.richa...@gmail.com] Sent: Thursday, November 01, 2012 8:54 AM To: Reindl Harald; mysql@lists.mysql.com Subject: Re: Mysql backup for large databases Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime
Re: Mysql backup for large databases
Hi, For doing backups on the primary database, I know nothing better than have your tables in InnoDB and use Innobackup (or MySQL Enterprise backup). This, however, still has the possibility of hanging as it is using FLUSH TABLES WITH READ LOCK for taking backups of MyISAM tables.One may want to script it to kill the backup if the wait exceeds some threshold. The backup taken this way has incremental backups feature which may reduce the impact. For offloading the backups to a replica, there exist more options because the replica can be frozen and/or shut down. For an InnoDB database, it has to be shut down for taking a consistent backup. If it is not, it will result in cute little inconsistencies unless a DBA is one lucky guy and always wins playing roulette. Combining the two, I like the idea of doing EM backup on a replica and having all tables in InnoDB. After a backup has been taken, it will eventually need to be restored unless someone just likes taking them. For this reason, it will have to be brought to the recovered system. Unless somebody knows in advance when the database would need to be recovered (f.e. it is known that a bad guy always corrupts it on Monday mornings), the backup will need to be available for restore always. These considerations usually imply things like shared filesystems between primary and replica, rejecting backups for recoveries across datacenters and the like. Backing up binary logs allows providing continuous coverage for recovery instead of discrete. Cheers Karen On 01.11.2012, at 8:53, machiel.richa...@gmail.com wrote: Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime ‹¢ÒÒ‹¤◊5ÂvVæWÂֈƈærƘ7@‹¤f÷Ƙ7B6∫˜fW3¢ΩGG¢òöƘ7G2æ◊˜7Âæ6öÒö◊˜7À‹¥FòVç7V'67ˆS¢ΩGG¢òöƘ7G2æ◊˜7Âæ6öÒö◊˜7À‹ ‹ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL Backup solution for non-technical user
Hi everybody! Dotan Cohen wrote: Is there a simple browser-based MySQL backup solution for non-technical users. [[...]] The main features needed are: 1) Automatic scheduled off-site backups (via SSH or FTP) Off-site = good (for reliability purposes). 2) Backup multiple databases and all their tables Definitely a must have. 3) Single-table recovery via GUI (the user simply chooses which database and which table to recover) If your backup/recovery tool has this feature and your users ever go that route, you (your DBA / your authorities) must be aware that this will break any dependencies between that recovered table and all other, un-recovered ones. Example: Assume a new entry is added to the customer table, then (at least) one order is entered for this customer. Before, during, or after that, some garbage change is done to the customer table, it is detected, and someone decides let's recover the customer table from the last good backup. This will get rid of the garbage, but will also make the orders for new customer be pointing to nowhere. IOW: As soon as you have relationships crossing table boundaries, a single-table recovery is a very risky operation, and it will violate any referential integrity constraints involving that table. [[...]] Regards, Joerg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Backup solution for non-technical user
On Fri, May 13, 2011 at 10:21, Joerg Bruehe joerg.bru...@oracle.com wrote: Hi everybody! Dotan Cohen wrote: Is there a simple browser-based MySQL backup solution for non-technical users. [[...]] The main features needed are: 1) Automatic scheduled off-site backups (via SSH or FTP) Off-site = good (for reliability purposes). 2) Backup multiple databases and all their tables Definitely a must have. 3) Single-table recovery via GUI (the user simply chooses which database and which table to recover) If your backup/recovery tool has this feature and your users ever go that route, you (your DBA / your authorities) must be aware that this will break any dependencies between that recovered table and all other, un-recovered ones. Example: Assume a new entry is added to the customer table, then (at least) one order is entered for this customer. Before, during, or after that, some garbage change is done to the customer table, it is detected, and someone decides let's recover the customer table from the last good backup. This will get rid of the garbage, but will also make the orders for new customer be pointing to nowhere. IOW: As soon as you have relationships crossing table boundaries, a single-table recovery is a very risky operation, and it will violate any referential integrity constraints involving that table. [[...]] Regards, Joerg Thanks Joerg for that insight. In fact, this is a very simple installation with no joins but I will keep that in mind for the future. Terrific point. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Backup solution for non-technical user
On Tue, May 10, 2011 at 22:58, Michael Heaney mhea...@jcvi.org wrote: Check out Zmanda: http://zmanda.com/zrm-mysql-enterprise.html Michael Heaney JCVI On Wed, May 11, 2011 at 10:00, Johan De Meersman vegiv...@tuxera.be wrote: Zmanda ZRM backup, although the fancy webinterface is only available in the commercial version. Backups are stored on the host that runs the server, and of course it serves multiple MySQL machines. Webinterface is annoyingly slow, though :-) Thanks, I passed the suggestion on. Might be what he is looking for. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Backup solution for non-technical user
Zmanda ZRM backup, although the fancy webinterface is only available in the commercial version. Backups are stored on the host that runs the server, and of course it serves multiple MySQL machines. Webinterface is annoyingly slow, though :-) - Original Message - From: Michael Heaney mhea...@jcvi.org To: mysql@lists.mysql.com Sent: Tuesday, 10 May, 2011 9:58:43 PM Subject: Re: MySQL Backup solution for non-technical user Check out Zmanda: http://zmanda.com/zrm-mysql-enterprise.html Michael Heaney JCVI -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Backup solution for non-technical user
On 5/10/2011 3:55 PM, Dotan Cohen wrote: Is there a simple browser-based MySQL backup solution for non-technical users. The server is running Red Hat Enterprise Linux. The main features needed are: 1) Automatic scheduled off-site backups (via SSH or FTP) 2) Backup multiple databases and all their tables 3) Single-table recovery via GUI (the user simply chooses which database and which table to recover) 4) FOSS-license a big plus, but other licenses considered I have ruled out cron/mysqldump for the GUI (browser-based) recovery requirement.I found phpMyBackupPro which looks like a possible solution, and I'd really appreciate other MySQL users' input on the topic. Check out Zmanda: http://zmanda.com/zrm-mysql-enterprise.html Michael Heaney JCVI -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL-Backup incremental Backups in a Master/Slave Environment
Hi, first thanks for your response Are you using InnoDb or MyISAM? If you are using Inno, I would add --single-transaction to your mysqldump command within the Perl script and flush the logs prior to doing the backup and not backup the new bin-log file until the next. No, there is a mixture beetween MyISAM and InnoDB Tables. You suggest, changing the Order of FLUSH LOGS and place it before the Dumps are generated. Also, have you thought about doing the hourly backups against the master instead of a slave and do full backups off the slave since they naturally take longer? You mean saving the binlogs from the master ? Well currently that's not the case. I want to do the Backup from the Slave, also the hourly Backup of the Binlogs. As for your missing data, and I am not sure this will make sence, the logs you replayed on the test box where until that point you were doing the comparison and not just to the next full backup? Could it be that i need to execute a FLUSH TABLES before doing the mysqldumps on the Slave to force Data in the Cache to be written into the DB ? Thank you, Werner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql backup scheduler.
Manivannan - In a word, no. It's database software, not, um, cron. :) There are scheduled events in 5.1, but that is more of a trigger-like functionality than what you need, which is a scheduled task to save your data somewhere. http://dev.mysql.com/doc/refman/5.1/en/events.html You could look at establishing a central backup server that would do nothing but perform scheduled backups of your remote databases, thus simplifying your backup management and using only a single platform to do the backups. On Windows, you can use its built-in Scheduled Tasks function, which I have found to work passably well in recent versions of Windows Server. There are also other cron-like solutions out there - one I have used in the past is nncron lite - http://www.nncron.ru/ HTH, Dan On 9/13/07, Manivannan Palanichamy [EMAIL PROTECTED] wrote: Hi, Is there any built-in backup scheduler for mysql database server? Yes, I can use cron-tab in linux, but my env is windows. Also, I will be running mysql server in different platforms. So, is there any built-in backup scheduler? -- Manivannan Palanichamy http://mani.gw.googlepages.com/index.html -- View this message in context: http://www.nabble.com/Mysql-backup-scheduler.-tf4436845.html#a12658327 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql backup methods
Vitaliy Okulov a écrit : Здравствуйте, mysql. Hi all. I searching mysql backup script, which detect type of table use mysqlhotcopy for MyISAM or mysqldump --single-transaction for InnoDB. Also save structure of table in file. And support recovery for dumped db an tables. Somebody meet such type of mysqlbackup script? It doesn't put the structure in a separate file (yet), but you may want to have a look at ZRM (Zmanda Recovery Manager) for mysql here http://www.zmanda.com/backup-mysql.html This is the most complete free solution that I know off! -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Backup/Dump
It is recommended that you upgrade to 4.0.x first from 3.23.xx, because of any changes to the grant tables in the mysql database, make sure you read the upgrade notes before upggrading, here's a good link to upgrade, http://dev.mysql.com/doc/mysql/en/upgrade.html I find it very simple to zip all the databases to be dumped from the old server, and unzip them into the new box instead of a mysqlimport etc and hopefully it should work, # zip -r backup.zip database1 database2 ... . the go to data dir and #unzip backup.zip Kishore On 6/14/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: different box, different versions, use export/import (backup table or LOAD DATA) Mathias Selon Kory Wheatley [EMAIL PROTECTED]: I want to backup our entire Mysql database structure for mysql 3.23.58 and dump it in the newly installed mysql 4.1.10 What is the best command to do a backup and dump everything into the new MYSQL version on a different box? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Backup/Dump
I just moved my databases to 4.1 from 3.23. It went pretty smoothly. First I used mysqldump to back up the data. Then installed 4.1. First thing did was import only the mysql table. Then ran /mysql_fix_privilege_tables command. After that I was able to login to mysql with my old root password. On 6/15/05, Kishore Jalleda [EMAIL PROTECTED] wrote: It is recommended that you upgrade to 4.0.x first from 3.23.xx, because of any changes to the grant tables in the mysql database, make sure you read the upgrade notes before upggrading, here's a good link to upgrade, http://dev.mysql.com/doc/mysql/en/upgrade.html I find it very simple to zip all the databases to be dumped from the old server, and unzip them into the new box instead of a mysqlimport etc and hopefully it should work, # zip -r backup.zip database1 database2 ... . the go to data dir and #unzip backup.zip Kishore On 6/14/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: different box, different versions, use export/import (backup table or LOAD DATA) Mathias Selon Kory Wheatley [EMAIL PROTECTED]: I want to backup our entire Mysql database structure for mysql 3.23.58 and dump it in the newly installed mysql 4.1.10 What is the best command to do a backup and dump everything into the new MYSQL version on a different box? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sharif Islamhttp://www.sharifislam.com Research Programmer University of Illinois, Urbana-Champaign Library Systems Office217-244-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Backup/Dump
different box, different versions, use export/import (backup table or LOAD DATA) Mathias Selon Kory Wheatley [EMAIL PROTECTED]: I want to backup our entire Mysql database structure for mysql 3.23.58 and dump it in the newly installed mysql 4.1.10 What is the best command to do a backup and dump everything into the new MYSQL version on a different box? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- 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: Mysql Backup/Dump
If you are on Windows then you can use a GUI like SQLyog (www.webyog.com) to do the job. I think it will be the easiest way to do that. Karam --- Kory Wheatley [EMAIL PROTECTED] wrote: I want to backup our entire Mysql database structure for mysql 3.23.58 and dump it in the newly installed mysql 4.1.10 What is the best command to do a backup and dump everything into the new MYSQL version on a different box? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Discover Yahoo! Use Yahoo! to plan a weekend, have fun online and more. Check it out! http://discover.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL backup not backing up all tables
I assume you are running this with cron. Do you get an error message from cron? Do you have enough room on the destination disk for all 88 tables? How big is the backup file? For completeness, what is your OS, and what is your mysql version? Michael Danny Smitherman wrote: I am having trouble with a nightly backup of our MySQL database. Using the mysqldump command, we dump our entire database to a backup directory. But consistently the backup file contains only 33 of the 88 tables in the database. The 33 getting backed up are the first 33 of the 88 as sorted alphabetically by table name. Here are the relevant lines in the backup script: mysqldump --opt ACA /home/sites/home/users/admin/dump/ACA/`date --date '1 day ago' +%m%d`.all.ACA mysqldump mysql /home/sites/home/users/admin/dump/ACA/`date --date '1 day ago' +%m%d`.mysql I'm a newbie with MySQL, so I don't know what other info to provide. I appreciate your help. Danny S. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql backup script
Thanks to all that responded. The answer was a little different from the one below. So what it came out to was this: while [ -f $mysqlpid ] do sleep 1 /etc/rc.d/init.d/mysqld stop done For those that recommended webmin. Although I have webmin installed, because of the type of backups that I needed and how they were to be done, webmin was not an option. Thanks though. Steve At 11:29 AM 6/15/2004, Brian Reichert wrote: On Tue, Jun 15, 2004 at 08:04:00AM -0500, Steve Buehler wrote: I am having a problem with a backup script that is written in a shell (/bin/sh) script to backup my mysql databases. For some reason on any day with an even number I get the following error: MySQL could not be stopped, exiting... It is really weird because it will run on odd number days without a problem. Should I put some kind of a wait in the script after it stops the mysqld and before it checks to make sure the pid file is still there? If so, does anybody know how? I am not sure that that will solve the problem though because it exits afterward and the mysql daemon is still running without having to restart it. Below is the relevant part of the script. The script is run from cron with this line 0 1 * * * /root/backup/backup.sh /dev/null 21 But since the logs do show it running, That shouldn't be the problem. Thanks Steve # Perform myisamchk #mysqladmin -p$MYSQLPWD shutdown /etc/rc.d/init.d/mysqld stop if [ -f $mysqlpid ]; then Does /etc/rc.d/init.d/mysqld (however indirectly) remove the $mysqlpid file when it exits? Is $mysqlpid the same as '/etc/rc.d/init.d/mysqld start' would create? Couldn't you check for an exit status of '/etc/rc.d/init.d/mysqld stop' instead? I've played stupid games like this, to work around weak management scripts (pseudo-code): while( -f $mysqlpid ) sleep 1 end There are risks with that, as well, of course, but you see what it's trying to do... -- Brian Reichert [EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql backup script
On Tue, Jun 15, 2004 at 08:04:00AM -0500, Steve Buehler wrote: I am having a problem with a backup script that is written in a shell (/bin/sh) script to backup my mysql databases. For some reason on any day with an even number I get the following error: MySQL could not be stopped, exiting... It is really weird because it will run on odd number days without a problem. Should I put some kind of a wait in the script after it stops the mysqld and before it checks to make sure the pid file is still there? If so, does anybody know how? I am not sure that that will solve the problem though because it exits afterward and the mysql daemon is still running without having to restart it. Below is the relevant part of the script. The script is run from cron with this line 0 1 * * * /root/backup/backup.sh /dev/null 21 But since the logs do show it running, That shouldn't be the problem. Thanks Steve # Perform myisamchk #mysqladmin -p$MYSQLPWD shutdown /etc/rc.d/init.d/mysqld stop if [ -f $mysqlpid ]; then Does /etc/rc.d/init.d/mysqld (however indirectly) remove the $mysqlpid file when it exits? Is $mysqlpid the same as '/etc/rc.d/init.d/mysqld start' would create? Couldn't you check for an exit status of '/etc/rc.d/init.d/mysqld stop' instead? I've played stupid games like this, to work around weak management scripts (pseudo-code): while( -f $mysqlpid ) sleep 1 end There are risks with that, as well, of course, but you see what it's trying to do... -- Brian Reichert [EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Backup advice
Hi Ware Adams, thanks for your answer. Here's another question: Is it possible to set MySQL to save all the changes that can happen, for example, if some one insert a new record into table X, MySQL save a log about this task performed and all data that was inserted. I another person update one field from table Y, MySQL save all the information about this change. So, If something happens after the backup, we can recover the database without go back and perform again the insert and the update? If yes, where can I find documentation about this? Thansk a lot. Andre On Wed, 3 Mar 2004, Ware Adams wrote: Andre MATOS wrote: What is the best way to make a good and trustable backup from a live database, in other words, without shutdown the database? Is there any free open source tool for this also? There's no free/open source tool that makes a true hot backup when you're using InnoDB. mysqldump can be scripted to only dump one table at a time, but that means the tables will not be consistent. You can dump the entire dataset, but that will lock out other users. mysqldump files are easily readable and if you backup a table at a time you can restore only a single table. InnoDB Hot Backup makes a consistent backup across all tables without disturbing users. It's not free and you can only restore the entire data set (to which you could then apply binlogs from the backup time forward to bring the data set up to current time). You also cannot view or edit the files with a text editor. Also, InnoDB Hot Backup only backs up the InnoDB table space and logs. You must also back up your table definitions and binlogs separately. This is all described in the InnoDB manual at http://www.innodb.com/ We use both methods as they are helpful under different circumstances. Good luck, Ware Adams -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Backup advice
Andre MATOS wrote: Is it possible to set MySQL to save all the changes that can happen, for example, if some one insert a new record into table X, MySQL save a log about this task performed and all data that was inserted. I another person update one field from table Y, MySQL save all the information about this change. So, If something happens after the backup, we can recover the database without go back and perform again the insert and the update? Yes. The basic steps are: 1) Turn on the binary log in mysql: http://www.mysql.com/doc/en/Binary_log.html 2) Take a consistent point in time backup using InnoDB Hot Backup and the perl script ibbackup that InnoDB provides: http://www.innodb.com/manual.php#backup.myisam 3) wait until you need the backup 4) Restore the data set using InnoDB Hot Backup http://www.innodb.com/manual.php#restore 5) The restore will note the position in the binlog at which the backup was taken, use this position to apply the binlogs, also from: http://www.mysql.com/doc/en/Binary_log.html Obviously try this before relying on it. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Backup advice
Hi Andre, I think you are looking for this: http://www.mysql.com/doc/en/Binary_log.html Hope it helps, Ken - Original Message - From: Andre MATOS [EMAIL PROTECTED] To: Ware Adams [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, March 04, 2004 12:09 PM Subject: Re: MySQL Backup advice Hi Ware Adams, thanks for your answer. Here's another question: Is it possible to set MySQL to save all the changes that can happen, for example, if some one insert a new record into table X, MySQL save a log about this task performed and all data that was inserted. I another person update one field from table Y, MySQL save all the information about this change. So, If something happens after the backup, we can recover the database without go back and perform again the insert and the update? If yes, where can I find documentation about this? Thansk a lot. Andre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Backup advice
Andre MATOS wrote: What is the best way to make a good and trustable backup from a live database, in other words, without shutdown the database? Is there any free open source tool for this also? There's no free/open source tool that makes a true hot backup when you're using InnoDB. mysqldump can be scripted to only dump one table at a time, but that means the tables will not be consistent. You can dump the entire dataset, but that will lock out other users. mysqldump files are easily readable and if you backup a table at a time you can restore only a single table. InnoDB Hot Backup makes a consistent backup across all tables without disturbing users. It's not free and you can only restore the entire data set (to which you could then apply binlogs from the backup time forward to bring the data set up to current time). You also cannot view or edit the files with a text editor. Also, InnoDB Hot Backup only backs up the InnoDB table space and logs. You must also back up your table definitions and binlogs separately. This is all described in the InnoDB manual at http://www.innodb.com/ We use both methods as they are helpful under different circumstances. Good luck, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql backup problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Binay -- Saw your post over in php-general but it's better over here :-) ...and then Binay said... % % Hi all ! Hi! % % I have two systems. % one with mysql-version: 3.23.37 % other with mysql-version: 3.23.58 OK. % % now i want to take the backup of 3.23.37 data and copy to 3.23.58 ... % % i know i can take backup using mysqldump command ... and then copy using mysql commands I don't know about copy using mysql commands but I would definitely agree that mysqldump will work for you. % % but as mysql create folder for each database . % is it possible to copy the particular folder(database) to 3.23.58 and then running it smoothly ... Probably. Back up your .58 system, try it, and see! % % Any body got any idea ??? % % Please help me out.. % % Thanks in advance % % Binay HTH HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/xzw1Gb7uCXufRwARAqdSAKDDrQSaDzn1PO0LF5JEiHIw8mX6mwCgm889 oAy5z9tGBMVIlFvdcO5vJHE= =bCwO -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Backup
-Original Message- From: Tom McCay (Ramesys - Nottingham) [mailto:[EMAIL PROTECTED]] Sent: 3. september 2002 11:59 To: [EMAIL PROTECTED] Subject: MySQL Backup Hi, can someone point me in the right direction please regarding backing up a MySQL database using Veritas 8.6 on a Windows 2000 server? Is there a script I can use to dump the database to a cold file where I can back it up off-line or are there agents available to enable me to back the database up live. I usually just copy the complete DB directory from one computer to another one. With fast ethernet it takes around 5-10 minutes (30 minutes if I have to backup the table where I store images). Only thing is that mysql can't write to the table currently being copied. But that's not much of a problem, since I do the copy in the wee hours of the night, when almost no one uses the server. Is this a 'stupid' thing to do? I've done this for some time and nothing has gone wrong, no data corrupted either. Running source compiled 4.0.2-alpha: Uptime: 70 days 6 hours 42 min 24 sec Threads: 11 Questions: 215514424 Slow queries: 613 Opens: 87 Flush tables: 1 Open tables: 59 Queries per second avg: 35.492 Erlend Stromsvik - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Backup
Hello! Erlend Hopsø Strømsvik wrote: -Original Message- From: Tom McCay (Ramesys - Nottingham) [mailto:[EMAIL PROTECTED]] Sent: 3. september 2002 11:59 To: [EMAIL PROTECTED] Subject: MySQL Backup Hi, can someone point me in the right direction please regarding backing up a MySQL database using Veritas 8.6 on a Windows 2000 server? Is there a script I can use to dump the database to a cold file where I can back it up off-line or are there agents available to enable me to back the database up live. I usually just copy the complete DB directory from one computer to another one. With fast ethernet it takes around 5-10 minutes (30 minutes if I have to backup the table where I store images). Only thing is that mysql can't write to the table currently being copied. But that's not much of a problem, since I do the copy in the wee hours of the night, when almost no one uses the server. If you are copying the data from one computer to the other, why not installing a little mysql on the backup machine and replicate to that mysql? With only inserts/deletes/updates done on the replication machine, there is no high load to be expected and you have an up-to-date copy all the time. Is this a 'stupid' thing to do? I've done this for some time and nothing has gone wrong, no data corrupted either. Running source compiled 4.0.2-alpha: Uptime: 70 days 6 hours 42 min 24 sec Threads: 11 Questions: 215514424 Slow queries: 613 Opens: 87 Flush tables: 1 Open tables: 59 Queries per second avg: 35.492 Greetings Ralf -- Ralf Narozny Besuchen Sie uns auf der DMS-Expo. SAP, Dokumenten- management oder das komplette Office ins Portal einbinden? Wir zeigen es Ihnen - vom 3. bis 5.9. auf der Messe Essen Halle 3, Stand 3255 SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Backup
I usually just copy the complete DB directory from one computer to another one. With fast ethernet it takes around 5-10 minutes (30 minutes if I have to backup the table where I store images). Only thing is that mysql can't write to the table currently being copied. But that's not much of a problem, since I do the copy in the wee hours of the night, when almost no one uses the server. If you are copying the data from one computer to the other, why not installing a little mysql on the backup machine and replicate to that mysql? With only inserts/deletes/updates done on the replication machine, there is no high load to be expected and you have an up-to-date copy all the time. This 'manual' copy is for the drive I take home. To provide a backup in case the office burns down or someone decides to 'remove' the server. Also, replication does not provide cover for human mistakes, done with intention or just by being clueless. A delete from table.this is just as devestating on both master and slave. Going to buy this one soon: http://www.allmediait.com/html/araid.html Just to test out how to do an easy and complete systembackup. Also replication does not take care of the backup needed of all the other data and programs on the server. erlend stromsvik - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: MySQL Backup and Recovery
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 03, 2002 1:53 PM To: Chetan Lavti Subject: Re: RE: MySQL Backup and Recovery Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Hi, Thank u very much for positive reply !! Actually, what I am wanting to do is that instead of manual backup and recovery, there should be two buttons at the page as 'BACKUP' and 'RECOVERY'. As I click on the 'BACKUP' button the script for taking backup should run and similarly, in case of DB crash the recovery script should run when, I click on the 'RECOVERY' button. Will it be feasible..? =20 So, is it possible, then what is the correct way to do this??( Any reference material for doing the same..??) and if error occurs while doing this then what can be done ?? ( How to handle them..?? ) please, give me detailed information or links for this.( If it is possible for you!) Thanks and regards, Chetan Lavti - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Backup and Recovery
On Fri, Mar 01, 2002 at 09:26:36AM +0530, Chetan Lavti wrote: Hi, I am planning to have MySQL database backup and recovery from the web interface. Is it possible to do this. If yes, then what procedure I should follow. If anybody have any idea about this then please, do help me. It's our requirement. Backup and recovery is a common topic on this list. Have you scanned the archives yet? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 23 days, processed 764,260,032 queries (376/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Backup and Recovery
Having worked with Oracle, and MS SQL databases, the only backup/recovery I usually deal with after the admins have their dumpdb run to a secondary transaction database is tape backup of some form. Most of the sites I have worked with however use 3 disk mirrors, and disk cloning, and disk snapshots every so often as their needs demand. The key they find is the brief database lock, and the quiessing of the databases just prior to the spanshot or breaking off of the clone allowing for the backup with nor performance degradation of their active database. I don;t know your budjet or application or even if I am making any sense, but this is how we have been working the issue of backups of databases. Mark - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Chetan Lavti [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, March 02, 2002 9:31 PM Subject: Re: MySQL Backup and Recovery On Fri, Mar 01, 2002 at 09:26:36AM +0530, Chetan Lavti wrote: Hi, I am planning to have MySQL database backup and recovery from the web interface. Is it possible to do this. If yes, then what procedure I should follow. If anybody have any idea about this then please, do help me. It's our requirement. Backup and recovery is a common topic on this list. Have you scanned the archives yet? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 23 days, processed 764,260,032 queries (376/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL] Backup error
Unfortunately, you're using MySQL on a Microsoft product, and you've probably run into one of those issues where Redmond decided what's best for you (changing the 'case' of the filenames). BUT, in both the Microsoft and UNIX realm of products, 'packaging' files (on UNIX using 'tar' and 'gzip', and on Windows-XX using "pkzip" or "winzip" or GNU's tar and Gzip for MS products) before moving or backing them up should help 'protect' them (since thought the 'package' might change, the files within are 'protected'). Tim Thorburn wrote: Hello, I have a box running Win2k and MySQL 3.23.36, I had made a backup of all my databases/tables to CD-ROM last week. When I tried to access them again from the CD-ROM, I noticed that all file names had been altered to be displayed in uppercase and now they will not function in the php scripts I had generated. I've tried renaming the files to lowercase letters with no luck. Fortunately, these were only test databases so there won't be much loss. My question is in the future, is there a recommended way of backing up the databases that will not cause any errors? Also, would anyone know of a fix to my current problem? As I said, it was only a test database that was lost, but it was one that I would like back without going through another week of tweeking to get it back the way it was. Thanks -Tim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL] Backup error
Fortunately, these were only test databases so there won't be much loss. My question is in the future, is there a recommended way of backing up the databases that will not cause any errors? I'm using mysqldump to create backups.. but that's on linux, I'm not sure about windooz version. Probably the same.. Bye, B. To satisfy the new spam filter: You may query our database and buy products.. like books on sql. Not that we sell any.. but is does satisfy the filter :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql backup and restore.
You dont need a script or command to restore the database, mysqlhotcopy just creates a copy of your database files (.MYI, etc.) in the /pathToTheBackupDirectory/. It basicaly performs the same function as the unix command cp, only it does the necessary locking, etc. John Barton Unix Systems Administrator Primary Networks, Inc. [EMAIL PROTECTED] On Tue, 20 Mar 2001, Zhu George-CZZ010 wrote: Hi, If I use "mysqlhotcopy database/pathToTheBackupDirectory/" to backup the whole database, what's the script/command to restore it? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
