Re: backup of databases which have a mix of MyISAM- and InnoDB-tables
Am 22.08.2014 um 19:40 schrieb Lentes, Bernd: i've been already reading the documentation the whole day, but still confused and unsure what to do. We have two databases which are important for our work. So both are stored hourly. Now I recognized that each database has a mixture of MyISAM- and InnoDB-tables. A backup of this mix does not seem to be easy. Until now it was dumped using mysqldump --opt -u root --databases mausdb What I understand until now is that --opt is not necessary because it is default. It includes, among others, --lock-tables which is senseful for saving MyISAM-tables. For InnoDB-tables --single-transaction is useful. But both are mutually exclusive (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). The dump of both take about 10 seconds. If the db is locked for that period I can live with. When I use --single-transaction only the InnoDB-tables are consistent. Using --lock-tables the MyISAM-tables are stored consistently. What is about --lock-tables in conjunction with InnoDB-tables ? Are they stored consistently ? Are they locked during the dumping ? As I said, I could live with a small lock period ( 30 sec). Would --lock-all-tables be better ? Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables (from the manpage). I can live with a global read lock for the duration of the whole dump. --lock-tables causes any pending transactions to be committed implicitly (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). Is that a problem for the InnoDB tables ? Our system is: mysql-5.0.26-12.29.1 on a SLES 10 SP4 64 bit host why that complex? just setup replication because you have a lot of benefits: * in case your master crashs and the FS got damaged you have a real-time backup * for backups you can stop the slave, tar the whole datadir and start the slave * after it is restarted it pulls any change happened on the master due backup * the backup is likely smaller than verbose sql dumps * you do not need to care about table types and what not else signature.asc Description: OpenPGP digital signature
Re: backup of databases which have a mix of MyISAM- and InnoDB-tables
XTrabackup can handle both InnoDB and MyISAM in a consistent way while minimizing lock time on MyISAM tables ... http://www.percona.com/doc/percona-xtrabackup/2.1/ -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Backup Error while backing up MySQL Cluster
On 10/24/2012 11:57 AM, Bheemsen Aitha wrote: Hi, After following the steps at the following website, I tried to do an online backup of the cluster. http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-backup-using-management-client.html It is a plain vanilla command which is below. The cluster is almost an empty database, but backup is crashing at least one data node and was never successful. ndb_mgm -e START BACKUP WAIT COMPLETED Did anyone have this kind of error before? I tried searching on web but could not find a solution. Here is the error I received. Connected to Management Server at: localhost:1186 Waiting for completed, this may take several minutes Backup failed * 3001: Could not start backup *Backup aborted due to node failure: Permanent error: Internal error ALERT-- Node 2: Backup 2 started from 49 has been aborted. Error: 1326 Here is little background about our setup. OS: Redhat Linux 5.8 Cluster: MySQL 5.5, NDB 7.2.7 Cluster was installed and set up on two hosts using MCM, one host hosting mysqld, ndb_mgmd and the other hosting ndbmtd1 and ndbmtd2. I even tried by setting up the following parameters, but got the same error again. set BackupDataBufferSize:ndbmtd=256M attcluster; set BackupLogBufferSize:ndbmtd=256M attcluster; set BackupMemory:ndbmtd=512M attcluster; set BackupWriteSize:ndbmtd=32M attcluster; set BackupMaxWriteSize:ndbmtd=128M attcluster; Here are some links I found on web similar to my error. http://forums.mysql.com/read.php?25,230891,230959#msg-230959 http://grokbase.com/t/mysql/cluster/0578z8cj71/backup-error http://bugs.mysql.com/bug.php?id=66104 At the bottom of the bug you found, it says: [7 Sep 6:31] Ole John Aske This bug has been fixed in MySQL CLuster 7.2.8 which is now available on http://dev.mysql.com/downloads/cluster/ You need to upgrade to receive this fix. Let us know if that works. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Backup Error while backing up MySQL Cluster
Just for others to know, it was the memory problem. I re-set the memory parameters for ndbmtd (two nodes) to minimum. Then I could run the backup successfully. Thanks BA On Wed, Oct 24, 2012 at 11:57 AM, Bheemsen Aitha pgb...@motorola.comwrote: Hi, After following the steps at the following website, I tried to do an online backup of the cluster. http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-backup-using-management-client.html It is a plain vanilla command which is below. The cluster is almost an empty database, but backup is crashing at least one data node and was never successful. ndb_mgm -e START BACKUP WAIT COMPLETED Did anyone have this kind of error before? I tried searching on web but could not find a solution. Here is the error I received. Connected to Management Server at: localhost:1186 Waiting for completed, this may take several minutes Backup failed * 3001: Could not start backup *Backup aborted due to node failure: Permanent error: Internal error ALERT-- Node 2: Backup 2 started from 49 has been aborted. Error: 1326 Here is little background about our setup. OS: Redhat Linux 5.8 Cluster: MySQL 5.5, NDB 7.2.7 Cluster was installed and set up on two hosts using MCM, one host hosting mysqld, ndb_mgmd and the other hosting ndbmtd1 and ndbmtd2. I even tried by setting up the following parameters, but got the same error again. set BackupDataBufferSize:ndbmtd=256M attcluster; set BackupLogBufferSize:ndbmtd=256M attcluster; set BackupMemory:ndbmtd=512M attcluster; set BackupWriteSize:ndbmtd=32M attcluster; set BackupMaxWriteSize:ndbmtd=128M attcluster; Here are some links I found on web similar to my error. http://forums.mysql.com/read.php?25,230891,230959#msg-230959 http://grokbase.com/t/mysql/cluster/0578z8cj71/backup-error http://bugs.mysql.com/bug.php?id=66104 mcm show status -r attcluster; ++--+-+-+---+-+ | NodeId | Process | Host| Status | Nodegroup | Package | ++--+-+-+---+-+ | 49 | ndb_mgmd | ut06sandboxdb01 | running | | 7.2.7 | | 50 | mysqld | ut06sandboxdb01 | running | | 7.2.7 | | 1 | ndbmtd | ut06sandboxdb02 | failed | 0 | 7.2.7 | | 2 | ndbmtd | ut06sandboxdb02 | running | 0 | 7.2.7 | ++--+-+-+---+-+ 4 rows in set (0.07 sec) mcm I see the core dump in DataDir of node 1. [root@ut06sandboxdb02 data]# ls -ltr /opt/app/mcm-1.1.6_64-linux-rhel5-x86/mcm_data/clusters/attcluster/1/data total 16949760 -rw-rw-r-- 1 mysql mysql 0 Oct 19 12:23 ndb_1_out.err -rw-rw-r-- 1 mysql mysql 1 Oct 21 04:02 ndb_1_trace.log.next -rw-rw-r-- 1 mysql mysql568 Oct 21 04:02 ndb_1_error.log -rw-rw-r-- 1 mysql mysql 12202 Oct 21 04:02 ndb_1_trace.log.1_t4 -rw-rw-r-- 1 mysql mysql 923467 Oct 21 04:02 ndb_1_trace.log.1_t3 -rw-rw-r-- 1 mysql mysql 923489 Oct 21 04:02 ndb_1_trace.log.1_t2 -rw-rw-r-- 1 mysql mysql 934663 Oct 21 04:02 ndb_1_trace.log.1_t1 -rw-rw-r-- 1 mysql mysql 948989 Oct 21 04:02 ndb_1_trace.log.1 -rw--- 1 mysql mysql 4104044544 Oct 23 11:04 core.21529 -rw--- 1 mysql mysql 5880332288 Oct 23 18:22 core.8108 -rw--- 1 mysql mysql 4538155008 Oct 23 23:56 core.1124 -rw--- 1 mysql mysql 2924789760 Oct 24 00:32 core.9176 -rw-rw-r-- 1 mysql mysql 460826 Oct 24 00:33 ndb_1_out.log Here is more info from mcmd.log. I really appreciate any help on this. 2012-10-23 18:09:58.193: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] WARNING -- Node 2: Failed to memlock pages, error: 12 (Cannot allocate memory) [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Waiting 30 sec for nodes 1 to connect, nodes [ all: 1 and 2 connected: 2 no-wait: ] 2012-10-23 18:09:58.193: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Waiting 30 sec for nodes 1 to connect, nodes [ all: 1 and 2 connected: 2 no-wait: ] 2012-10-23 18:09:58.286: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Communication to Node 1 opened [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Node 1 Connected 2012-10-23 18:09:58.347: (message) last message repeated 1 times 2012-10-23 18:09:58.347: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 1: Node 2 Connected [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 1: Node 2: API mysql-5.5.25 ndb-7.2.7 [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: CM_REGCONF president = 1, own Node = 2, our dynamic id = 0/13 [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Node 1: API mysql-5.5.25 ndb-7.2.7 [1,ndb_mgmd,0]:
Re: Backup Policy
Interestingly, this page does not say anything about MySQL Enterprise Backups. On Mar 15, 2011, at 8:48 AM, a.sm...@ukgrid.net wrote: Hi, there is a lot of info on different backup methods here: http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html For example, for incremental backups see Making Incremental Backups by Enabling the Binary Log, cheers Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=a...@apple.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: Backup Policy
Hi, What storage engine are you using? Peter Boros On 03/15/2011 02:12 PM, Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. I research a lot and find techniques to perform it in Mysql. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. Any suggestions will always Welcome. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup Policy
xtrabackup, mysqlhotcopy for myisam, incremental backup using zamanda. Krishna On Tue, Mar 15, 2011 at 9:09 PM, petya pe...@petya.org.hu wrote: Hi, What storage engine are you using? Peter Boros On 03/15/2011 02:12 PM, Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. I research a lot and find techniques to perform it in Mysql. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. Any suggestions will always Welcome. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: Backup Policy
Hi, there is a lot of info on different backup methods here: http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html For example, for incremental backups see Making Incremental Backups by Enabling the Binary Log, cheers Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup Policy
- Original Message - From: Krishna Chandra Prajapati prajapat...@gmail.com incremental backup using zamanda. I'm running Zmanda on about two dozen hosts, and it comes well-recommended. It doesn't do anything that you can't do yourself, but it's easy to set up, reports well and backs up in what are basically tarfiles, so if all else fails you can still do manual restores. If you have plenty of servers, the enterprise version adds a web interface that the free doesn't have - not a huge thing, but very nice to have. Support is reasonably responsive, albeit at times a bit... well, prone to asking the obvious :-) -- 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: Backup Policy
Hi! Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. [[...]] Correct. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. RAID is no backup! A RAID system may give you protection against a single disk drive failing (depending on the RAID level you configure), but it doesn't protect you against misuse, operator error, malware (virus, trojan), wilful deletion, ... RAID is no backup! (This can't be repeated often enough.) Doing a backup means to take the data (including schema, privileges, passwords, triggers, ...) to some independent media where it is safe from all misfunction on the original machine. IMNSHO, a backup must be taken offline or write-protected in some other way, so that even a misfunction of the backup machine does not damage your backup. Old tape drives (or newer tape cartridges) with their physical write protection (ring, slider, ...) did provide such protection, it is a pity that they are too slow and too small for today's data (or too expensive for most people). With disks, my solution is: - Have the backup disks on a separate machine, via the network. - Have external backup disks, which are powered off if not is use. - Have two (or more) and use them alternating, so that even in case of a misfunction or drive failure (affecting the backup disk currently in use) the previous backup (on the other disk) remains safe. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. If you can afford downtime (shutting down the database), dirvish is a good means to take a file system backup (all your data areas). Check it at www.dirvish.org There are plenty of alternatives, but I didn't try most of them. What I did try was rsnapshot, but I found it too inflexible for my purposes. I can't comment on the other approaches. Whatever approach you take: Make sure the backup gets stored os some separate, protected media. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 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: Backup Policy
On Tue, March 15, 2011 12:36, Joerg Bruehe wrote: Hi! Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. [[...]] Correct. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. RAID is no backup! A RAID system may give you protection against a single disk drive failing (depending on the RAID level you configure), but it doesn't protect you against misuse, operator error, malware (virus, trojan), wilful deletion, ... RAID is no backup! (This can't be repeated often enough.) Doing a backup means to take the data (including schema, privileges, passwords, triggers, ...) to some independent media where it is safe from all misfunction on the original machine. IMNSHO, a backup must be taken offline or write-protected in some other way, so that even a misfunction of the backup machine does not damage your backup. Old tape drives (or newer tape cartridges) with their physical write protection (ring, slider, ...) did provide such protection, it is a pity that they are too slow and too small for today's data (or too expensive for most people). With disks, my solution is: - Have the backup disks on a separate machine, via the network. - Have external backup disks, which are powered off if not is use. - Have two (or more) and use them alternating, so that even in case of a misfunction or drive failure (affecting the backup disk currently in use) the previous backup (on the other disk) remains safe. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. If you can afford downtime (shutting down the database), dirvish is a good means to take a file system backup (all your data areas). Check it at www.dirvish.org There are plenty of alternatives, but I didn't try most of them. What I did try was rsnapshot, but I found it too inflexible for my purposes. I can't comment on the other approaches. Whatever approach you take: Make sure the backup gets stored os some separate, protected media. HTH, J�rg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com Another advantage of external drives is they can be taken OFF site and stored away from the building. We use three. One on site, one in transit and one that mirrors (off site of course) the images of the other two. We dump nightly and then backup that. We also backup the binary logs which get rotated every two days (restore is nightly back followed by the binary logs). The only only restore we have had to do is the nephew who knows html. The disks are raided, but as was stated, that is to protect against single point failure. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup Policy
You might want to look into replication (http://dev.mysql.com/doc/refman/5.5/en/replication.html). You can set up a replication slave to follow the master DB in real time, or offset by minutes, hours, days, or weeks, or whatever. That way you have a copy already served up waiting in the wings, very accessible. It's best to have at least one slave that is offset by at least a day IMHO because the problem could be human error and this will be faithfully replicated to the slave. MySQL replication works very well. Doing a dump is useful in some situations but we are more and more looking to more convenient ways, as storage and hardware is pretty cheap but time in a critical failure is not cheap. Jim McNeely On Mar 15, 2011, at 1:51 PM, Wm Mussatto wrote: On Tue, March 15, 2011 12:36, Joerg Bruehe wrote: Hi! Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. [[...]] Correct. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. RAID is no backup! A RAID system may give you protection against a single disk drive failing (depending on the RAID level you configure), but it doesn't protect you against misuse, operator error, malware (virus, trojan), wilful deletion, ... RAID is no backup! (This can't be repeated often enough.) Doing a backup means to take the data (including schema, privileges, passwords, triggers, ...) to some independent media where it is safe from all misfunction on the original machine. IMNSHO, a backup must be taken offline or write-protected in some other way, so that even a misfunction of the backup machine does not damage your backup. Old tape drives (or newer tape cartridges) with their physical write protection (ring, slider, ...) did provide such protection, it is a pity that they are too slow and too small for today's data (or too expensive for most people). With disks, my solution is: - Have the backup disks on a separate machine, via the network. - Have external backup disks, which are powered off if not is use. - Have two (or more) and use them alternating, so that even in case of a misfunction or drive failure (affecting the backup disk currently in use) the previous backup (on the other disk) remains safe. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. If you can afford downtime (shutting down the database), dirvish is a good means to take a file system backup (all your data areas). Check it at www.dirvish.org There are plenty of alternatives, but I didn't try most of them. What I did try was rsnapshot, but I found it too inflexible for my purposes. I can't comment on the other approaches. Whatever approach you take: Make sure the backup gets stored os some separate, protected media. HTH, J�rg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com Another advantage of external drives is they can be taken OFF site and stored away from the building. We use three. One on site, one in transit and one that mirrors (off site of course) the images of the other two. We dump nightly and then backup that. We also backup the binary logs which get rotated every two days (restore is nightly back followed by the binary logs). The only only restore we have had to do is the nephew who knows html. The disks are raided, but as was stated, that is to protect against single point failure. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=j...@newcenturydata.com
Re: Backup onle one procedure
sorry, my bad. Its -R and not -p. regards anandkl On Thu, Feb 10, 2011 at 1:58 PM, Singer X.J. Wang w...@singerwang.comwrote: Remember that procedure is defined per database, mysqldump -u[user] -p[pass] --where=db=`whatyouwant` and name=`whatyouwant` mysql proc On Thu, Feb 10, 2011 at 02:55, Ananda Kumar anan...@gmail.com wrote: there is -p option please used that. On Thu, Feb 10, 2011 at 12:47 PM, Johan De Meersman vegiv...@tuxera.be wrote: On Thu, Feb 10, 2011 at 7:43 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: I am researching all the ways to backup in mysql and donot able to find a command that take individual backup of only one procedure in mysql. Have a look at the SHOW CREATE PROCEDURE syntax. It's not mysqldump, but it will yield a statement that can recreate the given procedure from scratch. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- The best compliment you could give Pythian for our service is a referral.
Re: Backup onle one procedure
Hmm, I haven't seen the mail from Singer, yet. On Thu, Feb 10, 2011 at 9:33 AM, Ananda Kumar anan...@gmail.com wrote: On Thu, Feb 10, 2011 at 1:58 PM, Singer X.J. Wang w...@singerwang.comwrote: mysqldump -u[user] -p[pass] --where=db=`whatyouwant` and name=`whatyouwant` mysql proc Yes, I thought of that, too; but the manual explicitly states that manual manipulation of the proc procedure isn't supported (which i presume to mean there is no equivalent to *flush privileges*), so that's gonna take manual reassembly to restore. The privileges to the procedure (creator/invoker and so) are also in a separate table, procs_priv. *Show create procedure* should yield a complete, executable statement that recreates the procedure as-is. Something to keep in mind, incidentally, is that security is *creator* by default - the proc runs with the creator's privileges. If you migrate the procedure to a host where that same user does not exist or has different permissions, it might mysteriously not work. IMO, security should be set to *invoker* by default, as that is also the more secure option - if you don't have permission on the tables, I don't want you running stored procs on them. That's for MySQL to change, though :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Backup onle one procedure
On Thu, Feb 10, 2011 at 7:43 AM, Adarsh Sharma adarsh.sha...@orkash.comwrote: I am researching all the ways to backup in mysql and donot able to find a command that take individual backup of only one procedure in mysql. Have a look at the SHOW CREATE PROCEDURE syntax. It's not mysqldump, but it will yield a statement that can recreate the given procedure from scratch. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Backup onle one procedure
there is -p option please used that. On Thu, Feb 10, 2011 at 12:47 PM, Johan De Meersman vegiv...@tuxera.bewrote: On Thu, Feb 10, 2011 at 7:43 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: I am researching all the ways to backup in mysql and donot able to find a command that take individual backup of only one procedure in mysql. Have a look at the SHOW CREATE PROCEDURE syntax. It's not mysqldump, but it will yield a statement that can recreate the given procedure from scratch. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Backup
Do keep in mind that what you get there is going to be useless if your database doesn't already contain all the previous data. The inserts will work, of course, but any data modification may fail because the rows you modify aren't there when you restore. Make sure you know exactly what you want and what you're doing before implementing this. On Mon, Oct 11, 2010 at 5:10 PM, kranthi kranthikiran@gmail.com wrote: I get, I use following mysqlbinlog --start-date=2010-10-09 9:55:00 \ --stop-date=2010-10-10 10:05:00 \ /var/log/mysql/bin.123456 \ /tmp/mysql_restore.sql Thanks all From: andrew.2.mo...@nokia.com [mailto:andrew.2.mo...@nokia.com] Sent: Sunday, October 10, 2010 4:46 AM To: kranthikiran@gmail.com Subject: Re: Backup hmm a yesterday backup? MySqldump using where clauses? Why do you only want yesterday? -- Sent from my HTC Desire on 3 -- - Reply message - From: ext kranthi kranthikiran@gmail.com Date: Sun, Oct 10, 2010 04:51 Subject: Backup To: bac...@lists.mysql.com bac...@lists.mysql.com Hi , My database size is 900GB.i don't want full database backup. I need only yesterday backup. how can I take, please help me. Thanks Regards, Kranti -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Backup
Hi kranthi, Take a look at LVM and xtrabackup. http://www.mysqlperformanceblog.com/2009/02/24/xtrabackup-open-source-alternative-for-innodb-hot-backup-call-for-ideas/ http://marcus.bointon.com/archives/87-MySQL-backups-with-Perconas-XtraBackup.html Krishna CGI (cgi.com) On Sun, Oct 10, 2010 at 10:30 AM, kranthi kranthikiran@gmail.comwrote: Hi , My database size is 900GB.i don't want full database backup. I need only yesterday backup. how can I take, please help me. Thanks Regards, Kranti
Re: Backup
Hey Kranthi, If you have binlogs enabled, do a binary logs backup everyday i.e going to be your everyday backup which consists of the sql modified statements. On Sun, Oct 10, 2010 at 11:13 AM, yung inno...@gmail.com wrote: 2010/10/10 kranthi kranthikiran@gmail.com: Hi , My database size is 900GB.i don't want full database backup. I need only yesterday backup. how can I take, please help me. How about the document there: http://dev.mysql.com/doc/refman/5.1/en/backup-strategy-example.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: Backup
I get, I use following mysqlbinlog --start-date=2010-10-09 9:55:00 \ --stop-date=2010-10-10 10:05:00 \ /var/log/mysql/bin.123456 \ /tmp/mysql_restore.sql Thanks all From: andrew.2.mo...@nokia.com [mailto:andrew.2.mo...@nokia.com] Sent: Sunday, October 10, 2010 4:46 AM To: kranthikiran@gmail.com Subject: Re: Backup hmm a yesterday backup? MySqldump using where clauses? Why do you only want yesterday? -- Sent from my HTC Desire on 3 -- - Reply message - From: ext kranthi kranthikiran@gmail.com Date: Sun, Oct 10, 2010 04:51 Subject: Backup To: bac...@lists.mysql.com bac...@lists.mysql.com Hi , My database size is 900GB.i don't want full database backup. I need only yesterday backup. how can I take, please help me. Thanks Regards, Kranti
Re: Backup
2010/10/10 kranthi kranthikiran@gmail.com: Hi , My database size is 900GB.i don't want full database backup. I need only yesterday backup. how can I take, please help me. How about the document there: http://dev.mysql.com/doc/refman/5.1/en/backup-strategy-example.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: backup from a slave
One way to do this would be to issue a 'stop slave;' on the slave you are taking a backup from just before the backup starts. Then issue a 'show slave status\G' to get the master log file and position. You can use this to setup the new slave properly. ok, thanks, I was thinking it'd be a better solution. btw, I have to get the Master_Log_File and Read_Master_Log_Pos or Relay_Master_Log_File and Exec_Master_Log_Pos to start the new slave correctly !? may be my slave is not updated in 'realtime' thank you in advance MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: backup from a slave
One way to do this would be to issue a 'stop slave;' on the slave you are taking a backup from just before the backup starts. Then issue a 'show slave status\G' to get the master log file and position. You can use this to setup the new slave properly. ok, thanks, I was thinking it'd be a better solution. btw, I have to get the Master_Log_File and Read_Master_Log_Pos or Relay_Master_Log_File and Exec_Master_Log_Pos to start the new slave correctly !? (my slave is not always updated in 'realtime') thank you in advance MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: backup from a slave
One way to do this would be to issue a 'stop slave;' on the slave you are taking a backup from just before the backup starts. Then issue a 'show slave status\G' to get the master log file and position. You can use this to setup the new slave properly. ok, thanks, I was thinking it'd be a better solution. btw, I have to get the Master_Log_File and Read_Master_Log_Pos or Relay_Master_Log_File and Exec_Master_Log_Pos to start the new slave correctly !? may be my slave is not updated in 'realtime' thank you in advance MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: backup from a slave
MAS! wrote: btw, I have to get the Master_Log_File and Read_Master_Log_Pos or Relay_Master_Log_File and Exec_Master_Log_Pos to start the new slave correctly !? If the Master_Log_File and Exec_Master_Log_Pos are not equivalent, you'll want to note the Exec_Master_Log_Pos value as that is the value which determines where in the binary logs you're slave is currently at. -- Josh Miller, RHCE/VCP Seattle, WA Linux Solutions Provider Website: http://itsecureadmin.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: backup from a slave
Are the values of these variables all accessible via the command: show variables? Josh Miller wrote: MAS! wrote: btw, I have to get the Master_Log_File and Read_Master_Log_Pos or Relay_Master_Log_File and Exec_Master_Log_Pos to start the new slave correctly !? If the Master_Log_File and Exec_Master_Log_Pos are not equivalent, you'll want to note the Exec_Master_Log_Pos value as that is the value which determines where in the binary logs you're slave is currently at. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: backup from a slave
Lawrence Sorrillo wrote: Are the values of these variables all accessible via the command: show variables? If the Master_Log_File and Exec_Master_Log_Pos are not equivalent, you'll want to note the Exec_Master_Log_Pos value as that is the value which determines where in the binary logs you're slave is currently at. These values are accessible via 'show slave status\G'; -- Josh Miller, RHCE/VCP Seattle, WA Linux Solutions Provider Website: http://itsecureadmin.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: backup from a slave
MAS! wrote: I'd like to use that backup to setup a new slave (from the same (and unique) master); the problem is I don't know how set-up this new slave, since I don't know the right master binary-log num and position; in the backup I have the slave's binary-log/pos and not the master ones :( One way to do this would be to issue a 'stop slave;' on the slave you are taking a backup from just before the backup starts. Then issue a 'show slave status\G' to get the master log file and position. You can use this to setup the new slave properly. Remember to issue a 'start slave;' after taking the backup, but also after you obtain the master log file and position. HTH, -- Josh Miller, RHCE/VCP Seattle, WA Linux Solutions Provider Website: http://itsecureadmin.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: Backup table structure, not data
# mysqldump --help look for the flag --no-data Ben Esbach, Brandon wrote: Is there any way to backup a complete database structure (tables/fields/indexes/etc), without the data? Or even get a creation script per table? At present the only way I can think of is to restore a backup to another server and just delete records (a legacy database with data hitting over 12GB, might take some time) - but there's gotta be an easier way to do it... * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup table structure, not data
Is there any way to backup a complete database structure (tables/fields/indexes/etc), without the data? Or even get a creation script per table? At present the only way I can think of is to restore a backup to another server and just delete records (a legacy database with data hitting over 12GB, might take some time) - but there's gotta be an easier way to do it... mysqldump has a --no-data option. Try man mysqldump (assuming your MySQL server is Unix based. -- Richard Heyes http://www.websupportsolutions.co.uk Knowledge Base and Helpdesk software hosted for you - no installation, no maintenance, new features automatic and free -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backup table structure, not data
I ended up using mysqldump, worked well enough for what I was doing. I have to admit, I'm really surprised there's no way to do this directly in the admin gui that comes with MySQL though :). Thanks all for the replies! -Original Message- From: Richard Heyes [mailto:[EMAIL PROTECTED] Sent: 18 February 2008 11:24 To: Esbach, Brandon Cc: MySQL User Group Subject: Re: Backup table structure, not data Is there any way to backup a complete database structure (tables/fields/indexes/etc), without the data? Or even get a creation script per table? At present the only way I can think of is to restore a backup to another server and just delete records (a legacy database with data hitting over 12GB, might take some time) - but there's gotta be an easier way to do it... mysqldump has a --no-data option. Try man mysqldump (assuming your MySQL server is Unix based. -- Richard Heyes http://www.websupportsolutions.co.uk Knowledge Base and Helpdesk software hosted for you - no installation, no maintenance, new features automatic and free -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backup table structure, not data
This works fine for me: http://www.mysql.com/products/tools/administrator/ -Original Message- From: Esbach, Brandon [mailto:[EMAIL PROTECTED] Sent: Monday, February 18, 2008 12:22 PM To: [EMAIL PROTECTED] Cc: MySQL User Group Subject: RE: Backup table structure, not data I ended up using mysqldump, worked well enough for what I was doing. I have to admit, I'm really surprised there's no way to do this directly in the admin gui that comes with MySQL though :). Thanks all for the replies! -Original Message- From: Richard Heyes [mailto:[EMAIL PROTECTED] Sent: 18 February 2008 11:24 To: Esbach, Brandon Cc: MySQL User Group Subject: Re: Backup table structure, not data Is there any way to backup a complete database structure (tables/fields/indexes/etc), without the data? Or even get a creation script per table? At present the only way I can think of is to restore a backup to another server and just delete records (a legacy database with data hitting over 12GB, might take some time) - but there's gotta be an easier way to do it... mysqldump has a --no-data option. Try man mysqldump (assuming your MySQL server is Unix based. -- Richard Heyes http://www.websupportsolutions.co.uk Knowledge Base and Helpdesk software hosted for you - no installation, no maintenance, new features automatic and free -- 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: backup questions
Hi, Option 2 will not work. InnoDB has background threads that continue to change data even when the database is quiet. This is a simplification. The details are too complicated to write in this thread, but there's an entire chapter on this topic in the book I'm writing right now, High Performance MySQL 2nd Edition :-) Suffice to say you *can* take backups by copying files with InnoDB, but you have to know what you're doing. You should use an LVM snapshot to get a consistent point-in-time snapshot of the files. You might try using mylvmbackup. Baron On Jan 28, 2008 8:43 AM, Michaël de Groot [EMAIL PROTECTED] wrote: Hi Alex, I've used this method to start a replication slave without using MySQLdump to get the data from one machine to another. Option 1 works for sure, Options 3 and 4 do not work for sure (if a .MYI, .MYD or ibdatax file is changed while you are copying, you get a broken file on the other end). I'm not sure about option 2, but I think it should work. Best regards, Michael -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Namens Alex K Verzonden: maandag 28 januari 2008 14:34 Aan: MySQL General List Onderwerp: backup questions Hi guys, Is it safe to perform a backup of a database by copying its mysql files if the database is: 1) MyISAM and the database is not being used (mysql server turned off)? 2) InnoDB and and the database is not being used? 3) MyISAM and the database is being used? 4) InnoDB and and the database is being used? I know I can use mysqhotcopy or mysqldump but I'm just curious if it's possible to perform a backup by simply copying the files over in the conditions described above. Thank you so much, Alex -- 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: backup questions
Hi Alex, I've used this method to start a replication slave without using MySQLdump to get the data from one machine to another. Option 1 works for sure, Options 3 and 4 do not work for sure (if a .MYI, .MYD or ibdatax file is changed while you are copying, you get a broken file on the other end). I'm not sure about option 2, but I think it should work. Best regards, Michael -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Namens Alex K Verzonden: maandag 28 januari 2008 14:34 Aan: MySQL General List Onderwerp: backup questions Hi guys, Is it safe to perform a backup of a database by copying its mysql files if the database is: 1) MyISAM and the database is not being used (mysql server turned off)? 2) InnoDB and and the database is not being used? 3) MyISAM and the database is being used? 4) InnoDB and and the database is being used? I know I can use mysqhotcopy or mysqldump but I'm just curious if it's possible to perform a backup by simply copying the files over in the conditions described above. Thank you so much, Alex -- 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: backup InnoDB db to another server
-Original Message- From: js [mailto:[EMAIL PROTECTED] Sent: Saturday, December 01, 2007 8:11 PM To: Jeff Mckeon Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server You might want to use --single-transaction option when mysqldumping innodb We have a mix of InnoDB and MyIsam tables so that's really not an option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: Osvaldo Sommer [mailto:[EMAIL PROTECTED] Sent: Saturday, December 01, 2007 8:23 AM To: 'Jeff Mckeon'; 'David Campbell'; mysql@lists.mysql.com Subject: RE: backup InnoDB db to another server Jeff: Mysqldump don't back up your index, that's your data only. Osvaldo Sommer Actually I think it's more than that. We have cleaning scripts put place to delete records older than 3 months from certain tables. I think the users have been running these without optimizing the tables afterwards and therefore never reclaiming the space the created with the deletes. These tablename_Old tables were huge. On the main systems I did a mysqldump of just these tables, then dropped the originals from the db and restored them. The entire db size went from 65G to 20G. The database was already screwed up and I have another master running for our applications so there was no risk if I screwed something up. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
Jeff: Mysqldump don't back up your index, that's your data only. Osvaldo Sommer -Mensaje original- De: Jeff Mckeon [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 30 de Noviembre de 2007 03:24 p.m. Para: 'David Campbell'; mysql@lists.mysql.com Asunto: RE: backup InnoDB db to another server -Original Message- From: David Campbell [mailto:[EMAIL PROTECTED] Sent: Friday, November 30, 2007 11:29 AM To: mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server Jørn Dahl-Stamnes wrote: On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or scp dump.sql [EMAIL PROTECTED]:. Onliner mysqldump DB1 -uroot -ppassword dump.sql | ssh 10.10.0.2 cat dump.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The Mysqldump has finished but I've only got a 10gig .sql file. The db is about 65gig in raw size. Does this sound right? Is there a filesize limit for mysqldump .sql files? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.10/1160 - Release Date: 29/11/2007 08:32 p.m. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup InnoDB db to another server
You might want to use --single-transaction option when mysqldumping innodb On Dec 1, 2007 1:20 AM, Jeff Mckeon [EMAIL PROTECTED] wrote: -Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Friday, November 30, 2007 11:16 AM To: mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or mysqldump DB1 -uroot -ppassword dump.sql scp dump.sql [EMAIL PROTECTED]:. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Ok so on 10.10.0.2 (destination server) issue a: % mysqldump DB1 -h10.10.0.1 -C -uroot -ppassword /DATA/DB01bacup.sql -- 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: backup InnoDB db to another server
Jørn Dahl-Stamnes wrote: On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or scp dump.sql [EMAIL PROTECTED]:. Onliner mysqldump DB1 -uroot -ppassword dump.sql | ssh 10.10.0.2 cat dump.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: David Campbell [mailto:[EMAIL PROTECTED] Sent: Friday, November 30, 2007 11:29 AM To: mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server Jørn Dahl-Stamnes wrote: On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or scp dump.sql [EMAIL PROTECTED]:. Onliner mysqldump DB1 -uroot -ppassword dump.sql | ssh 10.10.0.2 cat dump.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The Mysqldump has finished but I've only got a 10gig .sql file. The db is about 65gig in raw size. Does this sound right? Is there a filesize limit for mysqldump .sql files? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup InnoDB db to another server
On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or mysqldump DB1 -uroot -ppassword dump.sql scp dump.sql [EMAIL PROTECTED]:. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Baron Schwartz Sent: Friday, November 30, 2007 11:06 AM To: Jeff Mckeon Cc: mysql list Subject: Re: backup InnoDB db to another server On Nov 30, 2007 10:55 AM, Jeff Mckeon [EMAIL PROTECTED] wrote: I'm trying to use mysqldump to backup an innoDB based db from one server to an sql file on another. It doesn't seem to be working however... Here is the command I'm using on the source server mysqldump DB1 -uroot -ppassword | mysql --compress -h 10.10.0.1 - uroot -ppassword DB1 /DATA/DB1backup.sql I see a /DATA/DB1backup.sql file created on the source server with 0 size, but nothing on the destination server. What am I screwing up here? Your command is actually telling mysql on 10.01.0.1 to execute the dumped output. It is doing so without creating any ouput, but the is creating an output file anyway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup InnoDB db to another server
On Nov 30, 2007 10:55 AM, Jeff Mckeon [EMAIL PROTECTED] wrote: I'm trying to use mysqldump to backup an innoDB based db from one server to an sql file on another. It doesn't seem to be working however... Here is the command I'm using on the source server mysqldump DB1 -uroot -ppassword | mysql --compress -h 10.10.0.1 -uroot -ppassword DB1 /DATA/DB1backup.sql I see a /DATA/DB1backup.sql file created on the source server with 0 size, but nothing on the destination server. What am I screwing up here? Your command is actually telling mysql on 10.01.0.1 to execute the dumped output. It is doing so without creating any ouput, but the is creating an output file anyway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Friday, November 30, 2007 11:16 AM To: mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or mysqldump DB1 -uroot -ppassword dump.sql scp dump.sql [EMAIL PROTECTED]:. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Ok so on 10.10.0.2 (destination server) issue a: % mysqldump DB1 -h10.10.0.1 -C -uroot -ppassword /DATA/DB01bacup.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup solution.
I am not sure if you can restore just one table from a dump with the mysql client, you could however just copy the table entries out of you dump into a new file and restore that On 5/15/07 12:28 AM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I have take a mysqldump of my entire database, is it possible to restore just one table from this mysqldump. Also, i have taken binary backup of my database, and one of the datafile is corrupt, is it possible to restore just this file from backup and start the mysqldb. Mysql database is running in innodb engine. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup solution.
Hi All, The table is close to 5 GB in size. regards anandkl On 5/15/07, Olaf Stein [EMAIL PROTECTED] wrote: I am not sure if you can restore just one table from a dump with the mysql client, you could however just copy the table entries out of you dump into a new file and restore that On 5/15/07 12:28 AM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I have take a mysqldump of my entire database, is it possible to restore just one table from this mysqldump. Also, i have taken binary backup of my database, and one of the datafile is corrupt, is it possible to restore just this file from backup and start the mysqldb. Mysql database is running in innodb engine. regards anandkl
Re: backup solution.
On 5/15/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I have take a mysqldump of my entire database, is it possible to restore just one table from this mysqldump. Yes thats possible. cat your-dump-filename | grep tablename u want to restore mysql -u user -ppassword should do it. Also, i have taken binary backup of my database, and one of the datafile is corrupt, is it possible to restore just this file from backup and start the mysqldb. Mysql database is running in innodb engine. If all files are from the same cold binary backup, then you will be able to do a restore. Else it will be highly unlikely. ~Alex
Re: backup solution.
Hi Alex, Thanks for the info, For the second question, do you mean i should restore the entire backup or just that one file from my backup. regards anandkl On 5/15/07, Alex Arul Lurthu [EMAIL PROTECTED] wrote: On 5/15/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I have take a mysqldump of my entire database, is it possible to restore just one table from this mysqldump. Yes thats possible. cat your-dump-filename | grep tablename u want to restore mysql -u user -ppassword should do it. Also, i have taken binary backup of my database, and one of the datafile is corrupt, is it possible to restore just this file from backup and start the mysqldb. Mysql database is running in innodb engine. If all files are from the same cold binary backup, then you will be able to do a restore. Else it will be highly unlikely. ~Alex
Re: backup solution.
On 5/15/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Alex, Thanks for the info, For the second question, do you mean i should restore the entire backup or just that one file from my backup. All the files should be from the same backup. AFAIK, MySQL doesnt have an option to recover only one datafile. Hence restore the entire backup. ~Alex
Re: backup stratergy
Juan, InnoDB Hot Backup is non-free. A 1-year license costs 390 euros + VAT, and a perpetual license 990 euros + VAT. http://www.innodb.com/order.php The Perl script innobackup can be used to make consistent backups of MyISAM tables also, but those backup require the locking of MyISAM tables, and are not in that sense 'hot' or 'online'. InnoDB Hot Backup works with all MySQL versions up to 5.1. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL ... Hi Juan, Thanks a lot for the quick reply. Any idea how much it would cost for ibbackup for innodb. Will mysql be providing this with any of their new release. regards anandkl On 2/23/07, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Ananda, For Innodb the best is Innodb Hot Backup ( www.innodb.com (US$) ) For MyISAM you can use a simple backup ( copy/paste) of your files. Also, you can do snapshots using mysqldump. Also, you can use Zmanda ( www.zmanda.com). Regards, Juan Eduardo On 2/23/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, Can you please direct me to any good documentation for a good backup and recovery stratergy for MyISAM and INNODB in mysql. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup stratergy
Whats wrong with using the --single-transaction switch for backing up InnoDB tables? What does the Hot Backup product do that this doesn't? Thanks, -Ryan Juan Eduardo Moreno wrote: Ananda, For Innodb the best is Innodb Hot Backup ( www.innodb.com (US$) ) For MyISAM you can use a simple backup ( copy/paste) of your files. Also, you can do snapshots using mysqldump. Also, you can use Zmanda ( www.zmanda.com). Regards, Juan Eduardo On 2/23/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, Can you please direct me to any good documentation for a good backup and recovery stratergy for MyISAM and INNODB in mysql. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup stratergy
Ananda, For Innodb the best is Innodb Hot Backup ( www.innodb.com (US$) ) For MyISAM you can use a simple backup ( copy/paste) of your files. Also, you can do snapshots using mysqldump. Also, you can use Zmanda ( www.zmanda.com). Regards, Juan Eduardo On 2/23/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, Can you please direct me to any good documentation for a good backup and recovery stratergy for MyISAM and INNODB in mysql. regards anandkl
Re: backup stratergy
Hi Juan, Thanks a lot for the quick reply. Any idea how much it would cost for ibbackup for innodb. Will mysql be providing this with any of their new release. regards anandkl On 2/23/07, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Ananda, For Innodb the best is Innodb Hot Backup ( www.innodb.com (US$) ) For MyISAM you can use a simple backup ( copy/paste) of your files. Also, you can do snapshots using mysqldump. Also, you can use Zmanda ( www.zmanda.com). Regards, Juan Eduardo On 2/23/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, Can you please direct me to any good documentation for a good backup and recovery stratergy for MyISAM and INNODB in mysql. regards anandkl
Re: Backup: Copy MYSQL Directory or use MysqlDump
On 1/23/07, Alex Arul [EMAIL PROTECTED] wrote: Hi, MySQL Dump is logical. Hence it is mostly slower than locking the MyISAM tables and copying them or shutting down the server and taring the entire MySQL directory if you are using innodb. If you are using innodb tables only you can run mysqldump with --single-transaction option to take a consistent backup even when the server is running. Hence both has advantages and disadvantages. It will more of depend on your use case. Alex is right. Depending on how you'll want to be able to restore, and how big your database is you may use different methods for backup. I usually use mysqldump along with bzip2 for maximum compression, but I have used tar in the past. cp -av retains permissions, making it easy for restore, but its not a good option, as you can't compress data nor restore pieces of information in an easy way. You'll have to test and choose from multiple options in this matter. -- 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: Backup: Copy MYSQL Directory or use MysqlDump
I used mysqlhotcopy and all is fine. Daniel da Veiga wrote: On 1/23/07, Alex Arul [EMAIL PROTECTED] wrote: Hi, MySQL Dump is logical. Hence it is mostly slower than locking the MyISAM tables and copying them or shutting down the server and taring the entire MySQL directory if you are using innodb. If you are using innodb tables only you can run mysqldump with --single-transaction option to take a consistent backup even when the server is running. Hence both has advantages and disadvantages. It will more of depend on your use case. Alex is right. Depending on how you'll want to be able to restore, and how big your database is you may use different methods for backup. I usually use mysqldump along with bzip2 for maximum compression, but I have used tar in the past. cp -av retains permissions, making it easy for restore, but its not a good option, as you can't compress data nor restore pieces of information in an easy way. You'll have to test and choose from multiple options in this matter. -- 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] -- View this message in context: http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8522321 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: Backup: Copy MYSQL Directory or use MysqlDump
Hi, Hope I have faced this: If we copy the files with 'cp' command, the permissions will not be retained. You have to assign it on restoring. But in the mysqldump utility, everything are retained as it is. Hope, mysqldump utility provides more options related to db than that of 'cp' command. Thanks Visolve DB Team. - Original Message - From: altendew [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 6:10 AM Subject: Backup: Copy MYSQL Directory or use MysqlDump I want to create a backup for my MySQL database every single night. I am currently running on a linux box. I have a backup drive located as /backup Would it be more effecient to use mysqldump tool, or use the unix command dump or cp Andrew -- View this message in context: http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8513292 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup: Copy MYSQL Directory or use MysqlDump
Hi, MySQL Dump is logical. Hence it is mostly slower than locking the MyISAM tables and copying them or shutting down the server and taring the entire MySQL directory if you are using innodb. If you are using innodb tables only you can run mysqldump with --single-transaction option to take a consistent backup even when the server is running. Hence both has advantages and disadvantages. It will more of depend on your use case. Thanks Alex On 1/23/07, ViSolve DB Team [EMAIL PROTECTED] wrote: Hi, Hope I have faced this: If we copy the files with 'cp' command, the permissions will not be retained. You have to assign it on restoring. But in the mysqldump utility, everything are retained as it is. Hope, mysqldump utility provides more options related to db than that of 'cp' command. Thanks Visolve DB Team. - Original Message - From: altendew [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 6:10 AM Subject: Backup: Copy MYSQL Directory or use MysqlDump I want to create a backup for my MySQL database every single night. I am currently running on a linux box. I have a backup drive located as /backup Would it be more effecient to use mysqldump tool, or use the unix command dump or cp Andrew -- View this message in context: http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8513292 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup locking tables on 5.0.24
On 8/23/06, matt_lists [EMAIL PROTECTED] wrote: We did not see this on 5.0.19, with 5.0.24 our backup jobs lock the tables for selects the backup takes 3 hours, so the site is down the whole time I'm using this backup line mysqldump -d -f --quote-names --skip-add-locks database outfile mysqldump -t -f --skip-add-locks database outfile when I kill the dump, I see it trying to issue an unlock tables sql and erroring due to the connection being killed is this not the proper way to keep it from locking any table? All we do is selects, there are no inserts/replaces/updates going on, during a backup The whole table has to be locked so you get a consistent state for the table. Not sure why you're only seeing this after your upgrade though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup locking tables on 5.0.24
chris smith wrote: On 8/23/06, matt_lists [EMAIL PROTECTED] wrote: We did not see this on 5.0.19, with 5.0.24 our backup jobs lock the tables for selects the backup takes 3 hours, so the site is down the whole time I'm using this backup line mysqldump -d -f --quote-names --skip-add-locks database outfile mysqldump -t -f --skip-add-locks database outfile when I kill the dump, I see it trying to issue an unlock tables sql and erroring due to the connection being killed is this not the proper way to keep it from locking any table? All we do is selects, there are no inserts/replaces/updates going on, during a backup The whole table has to be locked so you get a consistent state for the table. Not sure why you're only seeing this after your upgrade though. My understanding was it was a read lock, so concurrent selects would still work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup locking tables on 5.0.24
matt_lists wrote: chris smith wrote: On 8/23/06, matt_lists [EMAIL PROTECTED] wrote: We did not see this on 5.0.19, with 5.0.24 our backup jobs lock the tables for selects the backup takes 3 hours, so the site is down the whole time I'm using this backup line mysqldump -d -f --quote-names --skip-add-locks database outfile mysqldump -t -f --skip-add-locks database outfile when I kill the dump, I see it trying to issue an unlock tables sql and erroring due to the connection being killed is this not the proper way to keep it from locking any table? All we do is selects, there are no inserts/replaces/updates going on, during a backup The whole table has to be locked so you get a consistent state for the table. Not sure why you're only seeing this after your upgrade though. My understanding was it was a read lock, so concurrent selects would still work Do you have any stored procedures? I wonder if any bugs were fixed regarding those. I could select my_procedure(); and it does an update of a table's view count or something (think forum post views type scenario). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup Errors in MySQL Cluster (5.0.24)
Dilipkumar wrote: Hi, While taking backup in MySQL 5.0.24 for (ndbcluster tables) i am getting the following errors : mysqldump: Error 1296: Got error 241 'Invalid schema object version' from ndbcluster when dumping table `iib_candidate_tracking` at row: 0 When i checked out using ndberror : NDB error code 1296: No message slogan found (please report a bug if you get this error code): Unknown: Unknown It's telling you what to do, log a bug report: http://bugs.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup SQL
On Friday 04 August 2006 04:04 am, Kaushal Shriyan wrote: mysql -u kaushal -h example.com -p drupal /home/kaushal/drupal/new/a-l.sql and then do mysql -u kaushal -h example.com -p drupal /home/kaushal/drupal/new/m-s.sql Better would be: mysql -u kaushal -h example.com -p drupal ~/drupal/new/a-l.sql mysql -u kaushal -h example.com -p drupal ~/drupal/new/m-s.sql Couple of things, first off ~/ expands to /home/kaushal assuming you're running this as the kaushal user. If root, you can do ~kaushal/ to achieve the same effect. Next is which means run the next command only if the first command finishes successfully. To answer what I think was your original question, no, m-s.sql wouldn't interfere with a-l.sql's insertions because they run in sequence, first a-l, then m-s. The only time I'd really see this as an issue is if you were trying to run both at once, which in my opinion is a Bad Idea(tm). -- Chris White PHP Programmer/DBeer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup SQL
On 8/4/06, Chris White [EMAIL PROTECTED] wrote: On Friday 04 August 2006 04:04 am, Kaushal Shriyan wrote: mysql -u kaushal -h example.com -p drupal /home/kaushal/drupal/new/a-l.sql and then do mysql -u kaushal -h example.com -p drupal /home/kaushal/drupal/new/m-s.sql Better would be: mysql -u kaushal -h example.com -p drupal ~/drupal/new/a-l.sql mysql -u kaushal -h example.com -p drupal ~/drupal/new/m-s.sql Couple of things, first off ~/ expands to /home/kaushal assuming you're running this as the kaushal user. If root, you can do ~kaushal/ to achieve the same effect. Next is which means run the next command only if the first command finishes successfully. To answer what I think was your original question, no, m-s.sql wouldn't interfere with a-l.sql's insertions because they run in sequence, first a-l, then m-s. The only time I'd really see this as an issue is if you were trying to run both at once, which in my opinion is a Bad Idea(tm). What if each .sql contains a DROP TABLE IF EXISTS statement at the start? Something to be carefull if its the program that generated the backup likes to add this tags. -- 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: Backup SQL
On Friday 04 August 2006 10:35 am, Daniel da Veiga wrote: What if each .sql contains a DROP TABLE IF EXISTS statement at the start? Something to be carefull if its the program that generated the backup likes to add this tags. What if my website code breaks? This train of what if type questions can easily be answered by a quick skim through the sql before running it. If you believe that to be tiresome, think of how long it's going to take you to get order from chaos when things go down. -- Chris White PHP Programmer/DBirth Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup SQL
On 8/4/06, Chris White [EMAIL PROTECTED] wrote: On Friday 04 August 2006 10:35 am, Daniel da Veiga wrote: What if each .sql contains a DROP TABLE IF EXISTS statement at the start? Something to be carefull if its the program that generated the backup likes to add this tags. What if my website code breaks? This train of what if type questions can easily be answered by a quick skim through the sql before running it. If you believe that to be tiresome, think of how long it's going to take you to get order from chaos when things go down. I dont know exactly what's the purpose of your message. I was just warning the OP that he should take a look at the SQL before running it, not taking your advice blindly: To answer what I think was your original question, no, m-s.sql wouldn't interfere with a-l.sql's insertions because they run in sequence, first a-l, then m-s. You're wrong, they may run in sequence and still, m-s can interfer in what a-l has done, it all depends on the program used to generate the sql and the defaults it put (create tables, drop tables if exist or a simple sequence of the sql generated in a-l). Think better before you hit send. -- 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: Backup SQL
If you're using Myphpadmin, you can turn this option off when generating the dump file. -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Friday, August 04, 2006 12:14 PM To: mysql@lists.mysql.com Subject: Re: Backup SQL On Friday 04 August 2006 10:35 am, Daniel da Veiga wrote: What if each .sql contains a DROP TABLE IF EXISTS statement at the start? Something to be carefull if its the program that generated the backup likes to add this tags. What if my website code breaks? This train of what if type questions can easily be answered by a quick skim through the sql before running it. If you believe that to be tiresome, think of how long it's going to take you to get order from chaos when things go down. -- Chris White PHP Programmer/DBirth Interfuel -- 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: Backup SQL
On Friday 04 August 2006 11:26 am, Daniel da Veiga wrote: Think better before you hit send. Dude -- -- Chris White PHP Programmer/DBarn Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backup problem from 5.0 to mysql 4.1
From: Amir Bukhari Sent: 18 July 2006 09:23 I have local mysql 5.0 and I have developed an arabic site. The database encoding is utf8-bin. Localy everything work fine, all arabic text are displayed OK. Now I want to move it to a server in internet. The server has mysql 4.1 and as I restored the database there, some special arabic character are not displayed correctly. I don't know why only some character are inserted differently from others. Localy I have also tried to have both mysql server 4.1 5.0 to play with the backup and store, but without success, always the same result. Moving the binary database files (*.frm ...) from 5.0 to 4.1 doesn't work, it seem they are not compatible. Moving binary files between versions is generally a bad idea, use mysqldump instead. It creates a sql file you can run against the 4.1 server and should move all the chars over ok. E.g. if your database was called foodb then locally run: $ mysqldump -u amir -p foodb foodb.sql Then load this file into the server: $ mysql --host=server.name -u amir -p foodb foodb.sql (Or you could copy the dump file up to the server and then run the commmand locally.) Note the above will create all the tables and overwrite any existing tables and data with the new set. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backup problem from 5.0 to mysql 4.1
-Original Message- From: Addison, Mark [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 18, 2006 1:22 PM To: Amir Bukhari; mysql@lists.mysql.com Subject: RE: Backup problem from 5.0 to mysql 4.1 From: Amir Bukhari Sent: 18 July 2006 09:23 I have local mysql 5.0 and I have developed an arabic site. The database encoding is utf8-bin. Localy everything work fine, all arabic text are displayed OK. Now I want to move it to a server in internet. The server has mysql 4.1 and as I restored the database there, some special arabic character are not displayed correctly. I don't know why only some character are inserted differently from others. Localy I have also tried to have both mysql server 4.1 5.0 to play with the backup and store, but without success, always the same result. Moving the binary database files (*.frm ...) from 5.0 to 4.1 doesn't work, it seem they are not compatible. Moving binary files between versions is generally a bad idea, use mysqldump instead. It creates a sql file you can run against the 4.1 server and should move all the chars over ok. E.g. if your database was called foodb then locally run: $ mysqldump -u amir -p foodb foodb.sql Then load this file into the server: $ mysql --host=server.name -u amir -p foodb foodb.sql I have tried this also, but this produce those bad character. I have used exactly what you have done above. When restoring the database which was backuped using mysqldump into mysql 5.0 everything is OK, but when restoring it to mysql 4.1 then there is those bad characters. I have also tried to backup and restore with both version of mysql mysqldump included in 5.0 and 4.1, but without success. -Amir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup problem from 5.0 to mysql 4.1
Amir Bukhari wrote: -Original Message- From: Addison, Mark [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 18, 2006 1:22 PM To: Amir Bukhari; mysql@lists.mysql.com Subject: RE: Backup problem from 5.0 to mysql 4.1 From: Amir Bukhari Sent: 18 July 2006 09:23 I have local mysql 5.0 and I have developed an arabic site. The database encoding is utf8-bin. Localy everything work fine, all arabic text are displayed OK. Now I want to move it to a server in internet. The server has mysql 4.1 and as I restored the database there, some special arabic character are not displayed correctly. I don't know why only some character are inserted differently from others. Localy I have also tried to have both mysql server 4.1 5.0 to play with the backup and store, but without success, always the same result. Moving the binary database files (*.frm ...) from 5.0 to 4.1 doesn't work, it seem they are not compatible. Moving binary files between versions is generally a bad idea, use mysqldump instead. It creates a sql file you can run against the 4.1 server and should move all the chars over ok. E.g. if your database was called foodb then locally run: $ mysqldump -u amir -p foodb foodb.sql Then load this file into the server: $ mysql --host=server.name -u amir -p foodb foodb.sql I have tried this also, but this produce those bad character. I have used exactly what you have done above. When restoring the database which was backuped using mysqldump into mysql 5.0 everything is OK, but when restoring it to mysql 4.1 then there is those bad characters. I have also tried to backup and restore with both version of mysql mysqldump included in 5.0 and 4.1, but without success. There is a special commandline option for the mysqldump included in 5.0 to produce dumps suitable for 4.1. Try that. Bye Racke -- LinuXia Systems = http://www.linuxia.de/ Expert Interchange Consulting and System Administration ICDEVGROUP = http://www.icdevgroup.org/ Interchange Development Team -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
Its the same program, just Improved :-) People hate things they do not understand. You're probably right, and if I had memorized all the commands to be able to edit text, and had gotten used to it, I may like it. I've just gotten used to free-format editing w/o having to enter any commands to do it. Yeah, you'll probably be better sticking with your old program. Don't even look at GVIM, its VIM with a Graphical Interface, and that is VI Improved with a lot of features. So, you wouldn't like it. I downloaded and installed it, since it was pretty small. You're right, it's pretty much VI for Windows in a GUI. Probably not something that I'll use that often. What can I say?! Its a taste thing! But the fact that you had problems with your editor may indicate you'll have more problems in the future. VI is in every distro of Linux I've ever used so far, so, it was best to learn it in order to admin my servers in a better way, porting it for Windows saved me a lot of work (learning a new, win tool). Besides, what simple, fast and reliable editor you know can: VI is kind of like the old edlin program in DOS. I really HATED that one too. Talk about a bad editor, but that was a bad editor. You had to edit line-by-line. At least with VI, you can edit the whole thing at one time, and go back and forth. It's really a safe editor too, you have to do something to change the text, which is kind of good. I use Linux very infrequently, but I can always rely on VI to allow me to edit a configuration file, or whatever. 1) Automatically backup files. 2) Auto-ident code. 3) Highlight code from at least 100 languages (including SQL, C, PHP, Java, HTML). 4) Show you differences between files (oh, that helped me a lot with my.cnf) 5) Keep versions of old edited files 6) Remember the position where you left editing the file 7) Line numbering, jump to, copy paste visual or command, delete lines by number, delete multiple and much more editing features. 8) Much more stuff I don't use/know Multi-Edit does all of these things that you mentioned above, and much more! That's one of the reasons I like it so much. I've never really ran into a problem with this before, and it's possible that it's just a setting in my editor some where. There are SO MANY settings. Anyway, resolved for now, and I'll never throw away old faithful Multi-Edit (unless something better comes along). Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
Jesse wrote: my editor forces a hard line break at column position 16384, which, of course, corrupts the restore. I don't know if there are other text editors that will not do this, Funny, i've never seen one that does? What system/editor are you using? or even better, if there is a way to add a hard line break after each inserted record. I realize this will increase the size of the backup file, but to me, it's worth it. try to use the --max_allowed_packet= option - afaik mysqldump will create lines as long as max_allowed_packet so if you set this to 8k you should get nice short lines Any ideas? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
On 7/3/06, Jesse [EMAIL PROTECTED] wrote: I'm trying to determine the best way to back up my MySQL databases in such a way that they can be easily restored, flexible, and complete. I've experimented with physically copying the files (with disastrous results). MySQLDump seems to be the way to go, but I've got two problems with that which I don't know how to overcome: 1) MySQLDump does not appear to get Procedures, Functions, and Triggers. What I'm doing right now for this is manually copying those to another file which I've got, then if I have to restore a database, I have to manually add the procedures, functions, and triggers back into the database. There are options called: --routines, -R and --triggers But you need latest version of MySQL Client tools. 2) sometimes, I like to copy just a single table or so out of the backup file, and restore just that. That's why I really like the way MySQLDump works, because it creates SQL code that I can simply run to re-create and restore the records. Problems is that a table/create/insert is one continuous line, with no line breaks, So, if I've got a table with 100,000 records, I cannot copy that table out, because my editor forces a hard line break at column position 16384, which, of course, corrupts the restore. I don't know if there are other text editors that will not do this, or even better, if there is a way to add a hard line break after each inserted record. I realize this will increase the size of the backup file, but to me, it's worth it. Change Editor ;-) I personally use VIM and never had such problems. Ok, now, to the point: --extended-insert (-e) will put multiple VALUES() statments in a single line. Disable this feature (it is default for --opt) and will get a single INSERT for each row. Any ideas? Yes! Windows: Mysql Manual - Mysqldump section Linux: man mysqldump It's all there... -- 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: Backup questions
Funny, i've never seen one that does? What system/editor are you using? Multi-Edit version 8.0i. This is an older version of the editor. Maybe a newer one wouldn't, but for the most part, it does a very good job for me. try to use the --max_allowed_packet= option - afaik mysqldump will create lines as long as max_allowed_packet so if you set this to 8k you should get nice short lines This didn't seem to make any difference in the length of the line. Even if it did, I have a feeling it would go to the 8K column, and just chop it right there, the same way it is at the 16384th column. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
Jesse wrote: Funny, i've never seen one that does? What system/editor are you using? Multi-Edit version 8.0i. This is an older version of the editor. Maybe a newer one wouldn't, but for the most part, it does a very good job for me. Ok never heard of multiedit... if your system is windows, try to use EditPlus. Under any *nix variant, ofcourse just use vim. try to use the --max_allowed_packet= option - afaik mysqldump will create lines as long as max_allowed_packet so if you set this to 8k you should get nice short lines This didn't seem to make any difference in the length of the line. Even if it did, I have a feeling it would go to the 8K column, and just chop it right there, the same way it is at the 16384th column. in that case you need to throw that editor as far as way as you can and never ever use it again ;) Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
--routines, -R and --triggers Thanks, these did the trick, and it's put my procedures and triggers into the back up file. However, it has commented them out so that they will not be created if I do a restore to a new database. Not sure why... Change Editor ;-) I personally use VIM and never had such problems. I've been using Multi-Edit for years, and have been very happy with it. VIM sounds like a Linux editor?? I'm working in Windows XP. --extended-insert (-e) will put multiple VALUES() statments in a single line. Disable this feature (it is default for --opt) and will get a single INSERT for each row. I thought this may be the way I needed to go, but wasn't sure. I will give this a try and see if it works. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
2) sometimes, I like to copy just a single table or so out of the backup file, and restore just that. You might want to try out MySQL Administrator which can often be used to restore backups from mysqldump. It can _selectively_ restore tables from a backup file. http://dev.mysql.com/doc/administrator/en/mysql-administrator-restore.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
On 7/3/06, Jesse [EMAIL PROTECTED] wrote: --routines, -R and --triggers Thanks, these did the trick, and it's put my procedures and triggers into the back up file. However, it has commented them out so that they will not be created if I do a restore to a new database. Not sure why... Security reasons maybe, this would make the dump not compatible with older versions of mysql that do not support triggers, procedures or functions. I guess there must be a way for mysqldump to uncomment this. Change Editor ;-) I personally use VIM and never had such problems. I've been using Multi-Edit for years, and have been very happy with it. VIM sounds like a Linux editor?? I'm working in Windows XP. There's GVIM for Windows, its the same program of Linux, I use it when there's no way but using Windows. Get it at www.vim.org ! Its a blast having the same (powerful, easy, fast and reliable) tool in windows and linux. But you'll find it kinda hard to learn at first, because of the command mode. --extended-insert (-e) will put multiple VALUES() statments in a single line. Disable this feature (it is default for --opt) and will get a single INSERT for each row. I thought this may be the way I needed to go, but wasn't sure. I will give this a try and see if it works. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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: Backup questions
There's GVIM for Windows, its the same program of Linux, I use it when there's no way but using Windows. Get it at www.vim.org ! Its a blast having the same (powerful, easy, fast and reliable) tool in windows and linux. But you'll find it kinda hard to learn at first, because of the command mode. If it's anything like the VI editor in Linux, then I don't even want to think about it. I've used the VI editor several times, and I HATE IT! VIM sounds like the same sort of thing if it's got a command mode. I've found a way to backup so that it wraps the lines correctly now, so I can use my old editor, which I'm used to. I may look into GVIM, though, just for grins-and-giggles. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
Oh, OK. I use MySQL Administrator frequently, but didn't realize that it could selectively restore. I don't like to use it for backups, because it doesn't get --routines and --triggers, and also doesn't backup in the format that I use. But I'll consider that. Thanks, Jesse - Original Message - From: Brad Jahnke [EMAIL PROTECTED] To: Jesse [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com Sent: Monday, July 03, 2006 11:06 AM Subject: Re: Backup questions 2) sometimes, I like to copy just a single table or so out of the backup file, and restore just that. You might want to try out MySQL Administrator which can often be used to restore backups from mysqldump. It can _selectively_ restore tables from a backup file. http://dev.mysql.com/doc/administrator/en/mysql-administrator-restore.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
On 7/3/06, Jesse [EMAIL PROTECTED] wrote: There's GVIM for Windows, its the same program of Linux, I use it when there's no way but using Windows. Get it at www.vim.org ! Its a blast having the same (powerful, easy, fast and reliable) tool in windows and linux. But you'll find it kinda hard to learn at first, because of the command mode. If it's anything like the VI editor in Linux, then I don't even want to think about it. I've used the VI editor several times, and I HATE IT! VIM sounds like the same sort of thing if it's got a command mode. Its the same program, just Improved :-) People hate things they do not understand. I've found a way to backup so that it wraps the lines correctly now, so I can use my old editor, which I'm used to. I may look into GVIM, though, just for grins-and-giggles. Yeah, you'll probably be better sticking with your old program. Don't even look at GVIM, its VIM with a Graphical Interface, and that is VI Improved with a lot of features. So, you wouldn't like it. What can I say?! Its a taste thing! But the fact that you had problems with your editor may indicate you'll have more problems in the future. VI is in every distro of Linux I've ever used so far, so, it was best to learn it in order to admin my servers in a better way, porting it for Windows saved me a lot of work (learning a new, win tool). Besides, what simple, fast and reliable editor you know can: 1) Automatically backup files. 2) Auto-ident code. 3) Highlight code from at least 100 languages (including SQL, C, PHP, Java, HTML). 4) Show you differences between files (oh, that helped me a lot with my.cnf) 5) Keep versions of old edited files 6) Remember the position where you left editing the file 7) Line numbering, jump to, copy paste visual or command, delete lines by number, delete multiple and much more editing features. 8) Much more stuff I don't use/know OK, I'll stop VIM stuff here, I just love it, but I'm pretty sure I can't convince most people, but I like to try. -- 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: Backup questions
GVIM is a great transition from vi to more 'modern' (read slower and cumbersome!) editors. It allows all the normal vi commands, but also 'standard' Windoze features like Ctrl-C Ctrl-V for copy and paste, drag and drop selection etc. Like all good editors, it takes time to get the most out of it, but at least it is usable out of the box (unlike vi!) Another one I like is ConTEXT, from www.context.cx. Not sure if its got a max line length, though. Quentin -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Tuesday, 4 July 2006 8:44 a.m. To: mysql@lists.mysql.com Subject: Re: Backup questions There's GVIM for Windows, its the same program of Linux, I use it when there's no way but using Windows. Get it at www.vim.org ! Its a blast having the same (powerful, easy, fast and reliable) tool in windows and linux. But you'll find it kinda hard to learn at first, because of the command mode. If it's anything like the VI editor in Linux, then I don't even want to think about it. I've used the VI editor several times, and I HATE IT! VIM sounds like the same sort of thing if it's got a command mode. I've found a way to backup so that it wraps the lines correctly now, so I can use my old editor, which I'm used to. I may look into GVIM, though, just for grins-and-giggles. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup and recovery problems
Paul Nowosielski wrote: Dear all, I've been testing our backup and recovery strategies here at work. When dumping all the databases I'm using this command: mysqldump --all-databases --force -u root -p -h 192.168.45.7 all.sql When this command is run I receive these error messages: mysqldump: mysqldump: Couldn't execute 'show create table `help_category`': Can't find file: './mysql/help_category.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_keyword`': Can't find file: './mysql/help_keyword.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_relation`': Can't find file: './mysql/help_relation.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_topic`': Can't find file: './mysql/help_topic.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `proc`': Can't find file: './mysql/proc.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `procs_priv`': Can't find file: './mysql/procs_priv.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone`': Can't find file: './mysql/time_zone.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_leap_second`': Can't find file: './mysql/time_zone_leap_second.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_name`': Can't find file: './mysql/time_zone_name.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition`': Can't find file: './mysql/time_zone_transition.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition_type`': Can't find file: './mysql/time_zone_transition_type.frm' (errno: 13) (1017) When I recover the databases using this command: mysql --force -u root -p -h localhost all.sql Tables are missing (and not the ones the dump is complaining about) and relations are broken. I'm assuming that the dump is dying before all the data is received. Can anyone give me advice on how to obtain a clean dump with all the tables so I may sleep well at night? I'm using MySQL 4.0.26 client and sever for these databases. I had thought that using the --force switch would alleviate my concerns. Unfortunately not... Best regards, perror 13 Error code 13: Permission denied Mysql does not have permiission to read its own tables. Perhaps you have been testing recovery strategies as root, and have changed the permissions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup and recovery problems
On Thursday 25 May 2006 12:09, you wrote: Paul Nowosielski wrote: Dear all, I've been testing our backup and recovery strategies here at work. When dumping all the databases I'm using this command: mysqldump --all-databases --force -u root -p -h 192.168.45.7 all.sql When this command is run I receive these error messages: mysqldump: mysqldump: Couldn't execute 'show create table `help_category`': Can't find file: './mysql/help_category.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_keyword`': Can't find file: './mysql/help_keyword.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_relation`': Can't find file: './mysql/help_relation.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_topic`': Can't find file: './mysql/help_topic.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `proc`': Can't find file: './mysql/proc.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `procs_priv`': Can't find file: './mysql/procs_priv.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone`': Can't find file: './mysql/time_zone.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_leap_second`': Can't find file: './mysql/time_zone_leap_second.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_name`': Can't find file: './mysql/time_zone_name.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition`': Can't find file: './mysql/time_zone_transition.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition_type`': Can't find file: './mysql/time_zone_transition_type.frm' (errno: 13) (1017) When I recover the databases using this command: mysql --force -u root -p -h localhost all.sql Tables are missing (and not the ones the dump is complaining about) and relations are broken. I'm assuming that the dump is dying before all the data is received. Can anyone give me advice on how to obtain a clean dump with all the tables so I may sleep well at night? I'm using MySQL 4.0.26 client and sever for these databases. I had thought that using the --force switch would alleviate my concerns. Unfortunately not... Best regards, perror 13 Error code 13: Permission denied Mysql does not have permiission to read its own tables. Perhaps you have been testing recovery strategies as root, and have changed the permissions? Ok this is very strange. I checked the permissions on the data directory and they where set to 660(rw rw X) and owned by mysql. I became the mysql user and listed the data/mysql directory: [EMAIL PROTECTED]:/usr/local/src/mysql_current/data ll mysql/ and I got: /bin/ls: mysql/procs_priv.MYD: Permission denied /bin/ls: mysql/procs_priv.MYI: Permission denied /bin/ls: mysql/procs_priv.frm: Permission denied /bin/ls: mysql/help_keyword.MYD: Permission denied /bin/ls: mysql/help_keyword.MYI: Permission denied /bin/ls: mysql/help_keyword.frm: Permission denied /bin/ls: mysql/func.MYD: Permission denied /bin/ls: mysql/func.MYI: Permission denied /bin/ls: mysql/func.frm: Permission denied /bin/ls: mysql/columns_priv.MYD: Permission denied /bin/ls: mysql/columns_priv.MYI: Permission denied /bin/ls: mysql/columns_priv.frm: Permission denied /bin/ls: mysql/tables_priv.MYD: Permission denied /bin/ls: mysql/tables_priv.MYI: Permission denied /bin/ls: mysql/tables_priv.frm: Permission denied /bin/ls: mysql/help_topic.MYD: Permission denied /bin/ls: mysql/help_topic.MYI: Permission denied /bin/ls: mysql/help_topic.frm: Permission denied /bin/ls: mysql/time_zone_transition_type.MYD: Permission denied /bin/ls: mysql/time_zone_transition_type.MYI: Permission denied /bin/ls: mysql/time_zone_transition_type.frm: Permission denied /bin/ls: mysql/time_zone_leap_second.MYD: Permission denied /bin/ls: mysql/time_zone_leap_second.MYI: Permission denied /bin/ls: mysql/time_zone_leap_second.frm: Permission denied /bin/ls: mysql/db.MYD: Permission denied /bin/ls: mysql/db.MYI: Permission denied /bin/ls: mysql/db.frm: Permission denied /bin/ls: mysql/host.MYD: Permission denied /bin/ls: mysql/host.MYI: Permission denied /bin/ls: mysql/host.frm: Permission denied /bin/ls: mysql/help_relation.MYD: Permission denied /bin/ls: mysql/help_relation.MYI: Permission denied /bin/ls: mysql/help_relation.frm: Permission denied /bin/ls: mysql/time_zone.MYD: Permission denied /bin/ls: mysql/time_zone.MYI: Permission denied /bin/ls: mysql/time_zone.frm: Permission denied /bin/ls: mysql/proc.MYD: Permission denied /bin/ls: mysql/proc.MYI: Permission denied /bin/ls: mysql/proc.frm: Permission denied /bin/ls: mysql/user.MYD: Permission denied /bin/ls: mysql/user.MYI: Permission denied /bin/ls:
Re: Backup and recovery problems
Paul Nowosielski wrote: On Thursday 25 May 2006 12:09, you wrote: Paul Nowosielski wrote: Dear all, I've been testing our backup and recovery strategies here at work. When dumping all the databases I'm using this command: mysqldump --all-databases --force -u root -p -h 192.168.45.7 all.sql When this command is run I receive these error messages: mysqldump: mysqldump: Couldn't execute 'show create table `help_category`': Can't find file: './mysql/help_category.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_keyword`': Can't find file: './mysql/help_keyword.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_relation`': Can't find file: './mysql/help_relation.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_topic`': Can't find file: './mysql/help_topic.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `proc`': Can't find file: './mysql/proc.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `procs_priv`': Can't find file: './mysql/procs_priv.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone`': Can't find file: './mysql/time_zone.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_leap_second`': Can't find file: './mysql/time_zone_leap_second.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_name`': Can't find file: './mysql/time_zone_name.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition`': Can't find file: './mysql/time_zone_transition.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition_type`': Can't find file: './mysql/time_zone_transition_type.frm' (errno: 13) (1017) When I recover the databases using this command: mysql --force -u root -p -h localhost all.sql Tables are missing (and not the ones the dump is complaining about) and relations are broken. I'm assuming that the dump is dying before all the data is received. Can anyone give me advice on how to obtain a clean dump with all the tables so I may sleep well at night? I'm using MySQL 4.0.26 client and sever for these databases. I had thought that using the --force switch would alleviate my concerns. Unfortunately not... Best regards, perror 13 Error code 13: Permission denied Mysql does not have permiission to read its own tables. Perhaps you have been testing recovery strategies as root, and have changed the permissions? Ok this is very strange. I checked the permissions on the data directory and they where set to 660(rw rw X) and owned by mysql. Which is wrong. You need 770 or you have no execute (search) privilege on the directory. I became the mysql user and listed the data/mysql directory: [EMAIL PROTECTED]:/usr/local/src/mysql_current/data ll mysql/ and I got: /bin/ls: mysql/procs_priv.MYD: Permission denied /bin/ls: mysql/procs_priv.MYI: Permission denied /bin/ls: mysql/procs_priv.frm: Permission denied /bin/ls: mysql/help_keyword.MYD: Permission denied /bin/ls: mysql/help_keyword.MYI: Permission denied /bin/ls: mysql/help_keyword.frm: Permission denied /bin/ls: mysql/func.MYD: Permission denied /bin/ls: mysql/func.MYI: Permission denied /bin/ls: mysql/func.frm: Permission denied /bin/ls: mysql/columns_priv.MYD: Permission denied /bin/ls: mysql/columns_priv.MYI: Permission denied /bin/ls: mysql/columns_priv.frm: Permission denied /bin/ls: mysql/tables_priv.MYD: Permission denied /bin/ls: mysql/tables_priv.MYI: Permission denied /bin/ls: mysql/tables_priv.frm: Permission denied /bin/ls: mysql/help_topic.MYD: Permission denied /bin/ls: mysql/help_topic.MYI: Permission denied /bin/ls: mysql/help_topic.frm: Permission denied /bin/ls: mysql/time_zone_transition_type.MYD: Permission denied /bin/ls: mysql/time_zone_transition_type.MYI: Permission denied /bin/ls: mysql/time_zone_transition_type.frm: Permission denied /bin/ls: mysql/time_zone_leap_second.MYD: Permission denied /bin/ls: mysql/time_zone_leap_second.MYI: Permission denied /bin/ls: mysql/time_zone_leap_second.frm: Permission denied /bin/ls: mysql/db.MYD: Permission denied /bin/ls: mysql/db.MYI: Permission denied /bin/ls: mysql/db.frm: Permission denied /bin/ls: mysql/host.MYD: Permission denied /bin/ls: mysql/host.MYI: Permission denied /bin/ls: mysql/host.frm: Permission denied /bin/ls: mysql/help_relation.MYD: Permission denied /bin/ls: mysql/help_relation.MYI: Permission denied /bin/ls: mysql/help_relation.frm: Permission denied /bin/ls: mysql/time_zone.MYD: Permission denied /bin/ls: mysql/time_zone.MYI: Permission denied /bin/ls: mysql/time_zone.frm: Permission denied /bin/ls: mysql/proc.MYD: Permission denied /bin/ls: mysql/proc.MYI: Permission denied /bin/ls: mysql/proc.frm: Permission denied /bin/ls: mysql/user.MYD:
Re: Backup and recovery problems
Gerald, Thank you that worked. now I'm receiving this error: dev:/tmp # /usr/local/mysql/bin/mysqldump -u root -p -h 192.168.45.7 --force --all-databases all.sql Enter password: /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `help_category` (File '/usr/local/src/mysql_current/share/mysql/charsets/?.conf' not found (Errcode: 2)) /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `help_keyword` (File '/usr/local/src/mysql_current/share/mysql/charsets/?.conf' not found (Errcode: 2)) /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `help_topic` (File '/usr/local/src/mysql_current/share/mysql/charsets/?.conf' not found (Errcode: 2)) /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `proc` (File '/usr/local/src/mysql_current/share/mysql/charsets/?.conf' not found (Errcode: 2)) /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `procs_priv` (Can't open file: 'procs_priv.MYI'. (errno: 138)) /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `time_zone_name` (File '/usr/local/src/mysql_current/share/mysql/charsets/?.conf' not found (Errcode: 2)) Any ideas? Thank you, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 On Thursday 25 May 2006 13:24, gerald_clark wrote: Paul Nowosielski wrote: On Thursday 25 May 2006 12:09, you wrote: Paul Nowosielski wrote: Dear all, I've been testing our backup and recovery strategies here at work. When dumping all the databases I'm using this command: mysqldump --all-databases --force -u root -p -h 192.168.45.7 all.sql When this command is run I receive these error messages: mysqldump: mysqldump: Couldn't execute 'show create table `help_category`': Can't find file: './mysql/help_category.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_keyword`': Can't find file: './mysql/help_keyword.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_relation`': Can't find file: './mysql/help_relation.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_topic`': Can't find file: './mysql/help_topic.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `proc`': Can't find file: './mysql/proc.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `procs_priv`': Can't find file: './mysql/procs_priv.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone`': Can't find file: './mysql/time_zone.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_leap_second`': Can't find file: './mysql/time_zone_leap_second.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_name`': Can't find file: './mysql/time_zone_name.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition`': Can't find file: './mysql/time_zone_transition.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition_type`': Can't find file: './mysql/time_zone_transition_type.frm' (errno: 13) (1017) When I recover the databases using this command: mysql --force -u root -p -h localhost all.sql Tables are missing (and not the ones the dump is complaining about) and relations are broken. I'm assuming that the dump is dying before all the data is received. Can anyone give me advice on how to obtain a clean dump with all the tables so I may sleep well at night? I'm using MySQL 4.0.26 client and sever for these databases. I had thought that using the --force switch would alleviate my concerns. Unfortunately not... Best regards, perror 13 Error code 13: Permission denied Mysql does not have permiission to read its own tables. Perhaps you have been testing recovery strategies as root, and have changed the permissions? Ok this is very strange. I checked the permissions on the data directory and they where set to 660(rw rw X) and owned by mysql. Which is wrong. You need 770 or you have no execute (search) privilege on the directory. I became the mysql user and listed the data/mysql directory: [EMAIL PROTECTED]:/usr/local/src/mysql_current/data ll mysql/ and I got: /bin/ls: mysql/procs_priv.MYD: Permission denied /bin/ls: mysql/procs_priv.MYI: Permission denied /bin/ls: mysql/procs_priv.frm: Permission denied /bin/ls: mysql/help_keyword.MYD: Permission denied /bin/ls: mysql/help_keyword.MYI: Permission denied /bin/ls: mysql/help_keyword.frm: Permission denied /bin/ls: mysql/func.MYD: Permission denied /bin/ls: mysql/func.MYI: Permission denied /bin/ls: mysql/func.frm: Permission denied /bin/ls: mysql/columns_priv.MYD: Permission denied /bin/ls: mysql/columns_priv.MYI: Permission denied /bin/ls: mysql/columns_priv.frm: Permission denied
Re: Backup / Restore database with foreign keys
Daniel Kasak wrote: Greetings. I've just hit an interesting problem. Luckily I don't actually *need* to restore from a backup right now - I'm just trying to create a database dump to submit an unrelated bug report. Anyway ... I'm using the command: mysqldump -K DATABASE_NAME db.sql -p However when I create a new database and try to load the dump file: mysql NEW_DATABASE db.sql -p I get an error when I hit an InnoDB table that has a relationship set up with a table that hasn't yet been created. How do I get around this? Before loading the file, SET FOREIGN_KEY_CHECKS = 0; after loading the file, SET FOREIGN_KEY_CHECKS = 1; Better yet, edit the dump file to place those as the first line and last lines, respectively. Even better, upgrade to a newer mysql (4.1.1+), where they are automatically added to the dump file for you. See the manual for more http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html (way down at the end). Michael Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup / Restore database with foreign keys
i think you can use -K on your mysqldump and it'll put the hints in there for the mysql command to use as well - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 29, 2005 7:45 PM Subject: Backup / Restore database with foreign keys Greetings. I've just hit an interesting problem. Luckily I don't actually *need* to restore from a backup right now - I'm just trying to create a database dump to submit an unrelated bug report. Anyway ... I'm using the command: mysqldump -K DATABASE_NAME db.sql -p However when I create a new database and try to load the dump file: mysql NEW_DATABASE db.sql -p I get an error when I hit an InnoDB table that has a relationship set up with a table that hasn't yet been created. How do I get around this? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- 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: Backup / Restore database with foreign keys
Michael Stassen wrote: Before loading the file, SET FOREIGN_KEY_CHECKS = 0; after loading the file, SET FOREIGN_KEY_CHECKS = 1; That's it! Thanks :) Even better, upgrade to a newer mysql (4.1.1+), where they are automatically added to the dump file for you. Not until the client libraries are ready. I don't feel 'right' about hacking up the place with --old-password options and such. Also, my Gentoo server ( stable branch ) insists that 4.0.x is the latest that I can expect to install without breaking things. After doing some testing on my workstation, I tend to agree - getting everything compiled against 4.1.x is a major pain, and certainly not something I'm about to do at the moment with no real advantages on offer - I'm just likely to break something and be very sorry. Once all the questions about: Client does not support authentication protocol requested by server; consider upgrading MySQL client have died down, *then* it's time to upgrade the server. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup and restore a database in a query #65311;
shuming wang wrote: Hi, Could we do a database dump/backup in a query like below ? mysqldump.exe --default-character-set=gb2312 --opt --host 192.168.0.1 -u root -p -C mydbmydbfile or restore a database in a query like below ? mysql.exe -h 192.168.0.1 -u root -p -C mydbmydbfile Then we can do backup and restore in GUI mode without call mysqldump.exe,mysql.exe in character mode . Best regard! Shuming Wang _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ Hi shuming i did not look at all your options , but ignoring the options it most def is possible. i am currently doing backups like this mysqldump -uuser -ppassword -hIPAddress -n -t dumpfile i then retrieve the files via ftp and then import them as follow mysql -uuser -ppassword -hIPAddress dumpfile hope this helps. contact me if you need any help. -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup and restore a database in a query ？
Could we do a database dump/backup in a query like below ? mysqldump.exe --default-character-set=gb2312 --opt --host 192.168.0.1 -u root -p -C mydbmydbfile or restore a database in a query like below ? mysql.exe -h 192.168.0.1 -u root -p -C mydbmydbfile Then we can do backup and restore in GUI mode without call mysqldump.exe,mysql.exe in character mode . Ah okay now i see what you are trying to do. The [mysqldump] is a utility that sits outside of the main mysql engine. You cannot invoke this from within the mysql shell from the best of my knowledge. As for pulling in backups from the mysql shell, then yes that is possible using a number of techniques: % mysql SOURCE [path to your file] or % mysql LOAD DATA [path to your file] Links to more information: http://dev.mysql.com/doc/mysql/en/load-data.html http://blog.spikesource.com/mysql_hotbackup.htm hope this helps, alan -- Alan Williamson, Technology Evangelist SpikeSource Inc. t: 650 249 4279 b: http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup and restore a database in a query #65311;
Arno Coetzee [EMAIL PROTECTED] wrote on 09/02/2005 04:37:48 AM: shuming wang wrote: Hi, Could we do a database dump/backup in a query like below ? mysqldump.exe --default-character-set=gb2312 --opt --host 192.168.0.1 -u root -p -C mydbmydbfile or restore a database in a query like below ? mysql.exe -h 192.168.0.1 -u root -p -C mydbmydbfile Then we can do backup and restore in GUI mode without call mysqldump.exe,mysql.exe in character mode . Best regard! Shuming Wang _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ Hi shuming i did not look at all your options , but ignoring the options it most def is possible. i am currently doing backups like this mysqldump -uuser -ppassword -hIPAddress -n -t dumpfile i then retrieve the files via ftp and then import them as follow mysql -uuser -ppassword -hIPAddress dumpfile hope this helps. contact me if you need any help. -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 The alternative to using mysqldump ... dumpfile is to use the -r option to specify a file. That avoids needing to redirect the console output of mysqldump by telling mysqldump where you want it to put the data directly. In a console window, call up mysqldump --help and it will show you all of the options it accepts. Or, if that is too cryptic, you could always RTFM : http://dev.mysql.com/doc/mysql/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Backup and Maintenance Strategies
Selon James Tu [EMAIL PROTECTED]: What have people done in the past regarding backup strategies? Is it adequate enough to rely on filesystem backups for mysql? Basically such that we can restore MySQL to the last filesystem backup. Is there a reason not to do this? I don't have any mission critical data and data that is lost since the last backup is acceptable. -James Hi, Filesystem backup for Mysql is similar to the one for all other RDBMS. You can't do it online, otherwise your database will not be coherent. the checkpoint times must be the same for all the datafiles. SO YOU CAN DO IT OFFLINE. you can also do a filesystem backup ONLINE if the RDBMs can put thetablespace sin Backup status. This is not done in pre-4 release of mysql. That's why replication master-slave is a backup solution. You can see innobackup which can do online backup since it can manage transactions during backup operation. If your data are not crtical (as you say), the database availability is not also. You can stop it and take an offline filesystem backup. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup and Maintenance Strategies
I don't know how big your tables are, and if you can withstand any downtime. Because we're using MyISAM tables, we use mysqlhotcopy, which locks the database as it copies the tables to another location. Once that's been done you can rely on your filesystem backup to keep copies of the data files but know that they'll be a point-in-time snapshot. We also use mysqldump, which works for all table types and can do the job if your tables aren't too large. Again, if you dump your tables to another folder/disk, the fs backup is a good way to keep copies of that data. Mike On Jun 25, 2005, at 1:32 AM, James Tu wrote: What have people done in the past regarding backup strategies? Is it adequate enough to rely on filesystem backups for mysql? Basically such that we can restore MySQL to the last filesystem backup. Is there a reason not to do this? I don't have any mission critical data and data that is lost since the last backup is acceptable. -James Mike Kruckenberg [EMAIL PROTECTED] ProMySQL Author http://www.amazon.com/exec/obidos/ASIN/159059505X -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup at client side
if you are accessing mysql server using phpmyadmin (http://sourceforge.net/projects/phpmyadmin/), then you have an option to export the databases in many formats, I guess this should work in your case... Kishore Jalleda On 6/21/05, Alex Aris [EMAIL PROTECTED] wrote: How do backup at the client side? I don't have an account, nor a shell on the server side. When I give the path, it thinks it is the path on the server. How do I tell mysql that it is a path on the client side? thanks, alex Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.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: backup at client side
Hello. The situation is not clear for me. Are you able to connect and execute queries on server using mysql command line client? Alex Aris [EMAIL PROTECTED] wrote: How do backup at the client side? I don't have an account, nor a shell on the server side. When I give the path, it thinks it is the path on the server. How do I tell mysql that it is a path on the client side? thanks, alex Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup database with MyISAM and InnoDB tables together
Hello. Among other suggestions think about such way. If you MyISAM and InnoDB tables are used by different applications or consistent state between them doesn't play big value, and the size of MyISAM tables is low enough, you could perform the dump in two steps listing the tables of the same type in command line: mysqldump --options_for_innodb database list_of_innodb_tables mysqldump --options_for_MyISAM database list_of_MyISAM tables As the size of MyISAM tables is not large the dump would be quick and tables would be locked for a short period of time. Scott Plumlee [EMAIL PROTECTED] wrote: I'm not clear on best practice to use on a database containing both MyISAM and InnoDB tables. For the MyISAM tables, it seems better to use mysqldump --opt, thus getting the --lock-tables option, but for the InnoDB the --single-transaction is preferred. Since they are mutually exclusive, is there a best practice to get consistent state of the tables when the database dump is performed? Would `mysqldump --opt --skip-lock-tables --single-transaction` be best for a database that is mostly InnoDB tables, but does have a few MyISAM tables? WOuld I be better off locking the database from any updates/inserts, and specifying particular commands for individual tables? Any advice appreciated, including RTFMs with links. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backup database with MyISAM and InnoDB tables together
If you are runing binary log and do a FLUSH LOGS mysqldump --opt --skip-lock-tables MyISAM table names FLUSH LOGS mysqldump --opt --single-transaction INNODB table names You have a recoverable state with the combination of the mysqldump file and the binary log file that was started by the 1st FLUSH LOGS command. The recovered database wil be restored to the date time that the 2nd FLUSH LOGS command was issued instead of the start time of the backup, but you won't have to lock all of your tables and it wil give you a consistent state across a mixed INNODB MyISAM environment. The downside is -you have 3 files to deal with -you have to maintain the table names in the mysqldump commands -you have a small risk of a change ocurring in the MyISAM tables between time the 2nd FLUSH LOGS is executed and the 2nd mysqldump command is executed -Original Message- From: Scott Plumlee [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 10:21 AM To: mysql@lists.mysql.com Subject: Backup database with MyISAM and InnoDB tables together I'm not clear on best practice to use on a database containing both MyISAM and InnoDB tables. For the MyISAM tables, it seems better to use mysqldump --opt, thus getting the --lock-tables option, but for the InnoDB the --single-transaction is preferred. Since they are mutually exclusive, is there a best practice to get consistent state of the tables when the database dump is performed? Would `mysqldump --opt --skip-lock-tables --single-transaction` be best for a database that is mostly InnoDB tables, but does have a few MyISAM tables? WOuld I be better off locking the database from any updates/inserts, and specifying particular commands for individual tables? Any advice appreciated, including RTFMs with links. -- 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: Backup database with MyISAM and InnoDB tables together
-Original Message- From: Scott Plumlee [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 10:21 AM To: mysql@lists.mysql.com Subject: Backup database with MyISAM and InnoDB tables together I'm not clear on best practice to use on a database containing both MyISAM and InnoDB tables. For the MyISAM tables, it seems better to use mysqldump --opt, thus getting the --lock-tables option, but for the InnoDB the --single-transaction is preferred. Since they are mutually exclusive, is there a best practice to get consistent state of the tables when the database dump is performed? Would `mysqldump --opt --skip-lock-tables --single-transaction` be best for a database that is mostly InnoDB tables, but does have a few MyISAM tables? WOuld I be better off locking the database from any updates/inserts, and specifying particular commands for individual tables? Any advice appreciated, including RTFMs with links. Gordon Bruce wrote: If you are runing binary log and do a FLUSH LOGS mysqldump --opt --skip-lock-tables MyISAM table names FLUSH LOGS mysqldump --opt --single-transaction INNODB table names You have a recoverable state with the combination of the mysqldump file and the binary log file that was started by the 1st FLUSH LOGS command. The recovered database wil be restored to the date time that the 2nd FLUSH LOGS command was issued instead of the start time of the backup, but you won't have to lock all of your tables and it wil give you a consistent state across a mixed INNODB MyISAM environment. The downside is -you have 3 files to deal with -you have to maintain the table names in the mysqldump commands -you have a small risk of a change ocurring in the MyISAM tables between time the 2nd FLUSH LOGS is executed and the 2nd mysqldump command is executed Thanks for the tip. I haven't looked into binary logs too much, just learned about them the other day when I had to correct my own mistake and restore a table. Is is best practice to go with tables of all one sort to allow for consistent state when doing backups like this, or are mixed tables the norm in most databases? I went with the InnoDB in order to not have to do row level locking on the tables as transactions were performed and I've been very pleased with the results. I might consider just going with all InnoDB to make it easy, as those are the majority of my tables in this case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backup database with MyISAM and InnoDB tables together
There are 3 things [that are exclusivly MyISAM}.
Full Text index
autoincrement column as the last column in a multi column primary key
MERGE tables
Tables which don't require these features can be INNODB tables. We have
a few tables that use these, otherwise we are exclusively INNODB.
There is another way to do backups. It's what we use.
Capture the file names in your database
Extract the .frm files and build select into outfile and coresponding
load data infile statements for each file name {i.e.table}
Sample
select * into outfile '/usr/data/mailprint/day/user.txt' from
mysql.user;
load data infile 'user.txt' ignore into table user;
put the select statements in a file with flush logs before and after the
set of select statements
run the file through a CRON as mysql -h ... filename
Now we have a text file for each table in a directory which we can
zip/tar, move to a different machine snd selectively restore the tables
via the load data commands, restore a single table in a test database
and recover/rebuild a specific tabel in the live database, etc.
Our 4GB database takes 5 minutes to save every night. A full restore
takes 30 minutes including moving the data files to the right place. We
keep 1 month of the nightly copies and day 1 of each month for a year.
We have development, stage and live servers and started doing this to
give us better granularity for selectively synching tables or parts of
tables. {I need these 500 rows from this table to move the dev project
to staging}. It has also been invaluable in the time when a developer
was on the wrong server and inadvertantly corupted an entire column of
the user table. We did not want to take down the site and do a
restore/rollforward to right before the stupid command was executed.
We just needed to fix the data in this one column in one table.
Sorry I started to ramble.
Oh I almost forgot, we also periodically dump the structure with
mysqldump --no-datato capture the structure.
Our table defintitions are relatively stable so we don't do it every
night. You could put it in the cron job to do it with the backup.
-Original Message-
From: Scott Plumlee [mailto:[EMAIL PROTECTED]
Sent: Friday, June 17, 2005 12:36 PM
To: mysql@lists.mysql.com
Subject: Re: Backup database with MyISAM and InnoDB tables together
-Original Message-
From: Scott Plumlee [mailto:[EMAIL PROTECTED]
Sent: Friday, June 17, 2005 10:21 AM
To: mysql@lists.mysql.com
Subject: Backup database with MyISAM and InnoDB tables together
I'm not clear on best practice to use on a database containing both
MyISAM and InnoDB tables. For the MyISAM tables, it seems better to
use
mysqldump --opt, thus getting the --lock-tables option, but for the
InnoDB the --single-transaction is preferred. Since they are
mutually
exclusive, is there a best practice to get consistent state of the
tables when the database dump is performed?
Would `mysqldump --opt --skip-lock-tables --single-transaction` be
best
for a database that is mostly InnoDB tables, but does have a few
MyISAM
tables?
WOuld I be better off locking the database from any updates/inserts,
and
specifying particular commands for individual tables?
Any advice appreciated, including RTFMs with links.
Gordon Bruce wrote:
If you are runing binary log and do a
FLUSH LOGS
mysqldump --opt --skip-lock-tables MyISAM table names
FLUSH LOGS
mysqldump --opt --single-transaction INNODB table names
You have a recoverable state with the combination of the mysqldump
file
and the binary log file that was started by the 1st FLUSH LOGS
command.
The recovered database wil be restored to the date time that the 2nd
FLUSH LOGS command was issued instead of the start time of the backup,
but you won't have to lock all of your tables and it wil give you a
consistent state across a mixed INNODB MyISAM environment.
The downside is
-you have 3 files to deal with
-you have to maintain the table names in the mysqldump commands
-you have a small risk of a change ocurring in the MyISAM
tables between time the 2nd FLUSH LOGS is executed and the 2nd
mysqldump command is executed
Thanks for the tip. I haven't looked into binary logs too much, just
learned about them the other day when I had to correct my own mistake
and restore a table.
Is is best practice to go with tables of all one sort to allow for
consistent state when doing backups like this, or are mixed tables the
norm in most databases? I went with the InnoDB in order to not have to
do row level locking on the tables as transactions were performed and
I've been very pleased with the results. I might consider just going
with all InnoDB to make it easy, as those are the majority of my tables
in this case.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED
