Re: Slave Bin Log Question
After you stop the slave and start mysqldump, execute the below on slave db. show slave status\G. Note down the Master_Log_File and Exec_Master_Log_Pos. This will be the point from which you need to do the recovery. regards anandkl On 9/25/07, Eric Frazier [EMAIL PROTECTED] wrote: Boyd Hemphill wrote: I have executed a strategy for backup where I stop a slave and do a mysqldump with --master-data. Both master and slave are 4.1.20 My assumption was that the log coordinates in the dump file would provide me with the place to replay the log for a point in time recovery. What I learned today however is that it appears the binary log only captures statements run directly on the slave. Any SQL run by the SQL thread of replication seems only to go in the relay log. This effectively renders the --master-data useless for my purpose. So, I have two questions. 1. Can someone verify that the binary log on the slave is not capturing SQL from the replication SQL thread. It sounds like you need --log-slave-updates http://dev.mysql.com/doc/refman/5.0/en/replication-options.html 2. If the above is really true, what strategies are you using to backup InnoDB without InnoDB hot backup? Thanks for your time! Peace Boyd CONFIDENTIALITY NOTICE: This email attached documents may contain confidential information. All information is intended only for the use of the named recipient. If you are not the named recipient, you are not authorized to read, disclose, copy, distribute or take any action in reliance on the information and any action other than immediate delivery to the named recipient is strictly prohibited. If you have received this email in error, do not read the information and please immediately notify sender by telephone to arrange for a return of the original documents. If you are the named recipient you are not authorized to reveal any of this information to any other unauthorized person. If you did not receive all pages listed or if pages are not legible, please immediately notify sender by phone. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookmarks and keywords
Baron Schwartz schrieb: SELECT parent.bookmark_url as pbu, parent.bookmark_keyword as pbk FROM bookmarks AS child JOIN bookmarks AS parent ON parent.bookmark_keyword = child.bookmark_keyword WHERE child.bookmark_url='http://www.redhat.com'; [..] no, you didn't, you just switched the names ... :-/ but from his original point of view the row with the given URL was his parent and he wants to have all childs (identified by the same keywords) - now you have just switched the names - making the childs his parents and the parents his childs - this is confusing just tell him: first the table which 'filters': FROM parent WHERE ... than the table he wants to select from: SELECT ... FROM child and now join them on the identifying field: SELECT child FROM parent JOIN child USING (keyword) WHERE parent... -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookmarks and keywords
Baron Schwartz schrieb: You have one final problem, which isn't really causing you trouble with THIS query, but will likely bite you in the future: you are selecting non-grouped columns in a GROUP BY query. SELECT DISTINCT will help too, of course (at least in similar cases) only if required (instead of GROUP BY) -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
more elegant way to store/find phone numbers
hi listers we have a mysql based application, wherein phone numbers may be stored and searched for. it is not the primary goal of this application to handle phone numbers. phone numbers usually are entered in a form like 099 999 99 99 or 099-999-99-99, or substings thereof. actually, the application stores the phone numbers as they are entered in a text field. a user searching an entry by phone number, on a first sight, cannot know, how the number is stored. i therefore leave him the freedom to enter the (sub) search like 99 99 or 99-99. as i am in php, no problem to extract the space or the dash using regular expressions. regular expressions in mysql, however, do not return collected subresults, they only return, whether there was a match or not. so, in order to compare the above (sub) search with the stored phone number, i constructed the following ugly query: select columns from table where REPLACE(REPLACE(phone, '-', ''), ' ', '') REGEXP sub_search; does anyone have a nicer solution for this? thanks in advance suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: more elegant way to store/find phone numbers
hi listers we have a mysql based application, wherein phone numbers may be stored and searched for. it is not the primary goal of this application to handle phone numbers. phone numbers usually are entered in a form like 099 999 99 99 or 099-999-99-99, or substings thereof. actually, the application stores the phone numbers as they are entered in a text field. a user searching an entry by phone number, on a first sight, cannot know, how the number is stored. i therefore leave him the freedom to enter the (sub) search like 99 99 or 99-99. as i am in php, no problem to extract the space or the dash using regular expressions. regular expressions in mysql, however, do not return collected subresults, they only return, whether there was a match or not. so, in order to compare the above (sub) search with the stored phone number, i constructed the following ugly query: select columns from table where REPLACE(REPLACE(phone, '-', ''), ' ', '') REGEXP sub_search; does anyone have a nicer solution for this? You could create an extra 'standardised' column that is used for searches. I discussed this in another post last week - http://lists.mysql.com/mysql/209197 Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: more elegant way to store/find phone numbers
does anyone have a nicer solution for this? How about comparing ereg_replace( [[:punct:]],, $colvalue ) with ereg_replace( [[:punct:]],, $comparisonvalue )? PB mysql wrote: hi listers we have a mysql based application, wherein phone numbers may be stored and searched for. it is not the primary goal of this application to handle phone numbers. phone numbers usually are entered in a form like 099 999 99 99 or 099-999-99-99, or substings thereof. actually, the application stores the phone numbers as they are entered in a text field. a user searching an entry by phone number, on a first sight, cannot know, how the number is stored. i therefore leave him the freedom to enter the (sub) search like 99 99 or 99-99. as i am in php, no problem to extract the space or the dash using regular expressions. regular expressions in mysql, however, do not return collected subresults, they only return, whether there was a match or not. so, in order to compare the above (sub) search with the stored phone number, i constructed the following ugly query: select columns from table where REPLACE(REPLACE(phone, '-', ''), ' ', '') REGEXP sub_search; does anyone have a nicer solution for this? thanks in advance suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
csv to mysql
hi all : i,m looking for a solution for my PDA that Doesn't have a DB Solution installed on it : so im having to write to CSV Files for my Forms , i'm needing a way that when i sink my PDA with my wireless network it Moves the Entire CSV File into a MySQL database :any Suggestions :? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: csv to mysql
[snip] : i,m looking for a solution for my PDA that Doesn't have a DB Solution installed on it : so im having to write to CSV Files for my Forms , i'm needing a way that when i sink my PDA with my wireless network it Moves the Entire CSV File into a MySQL database :any Suggestions :? [/snip] LOAD DATA INFILE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ordering by unrelated column in a GROUP BY
Suppose I have a table: CREATE TABLE `t1` ( `id` int(11) NOT NULL auto_increment, `data1` varchar(50) , `data2` varchar(50) , `data3` varchar(50) , `occurance` datetime , PRIMARY KEY (`id`) ) And I want to pull the most recent entry of each set of unique combinations of `data1` and `data2`. Per the mysql manual: If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL: What is the mysql syntax (if such syntax exists) to order within a group by another field, in this example `occurance`? Is there a better solution to this using sub selects? Am I on crack for considering doing something like: CREATE TEMPORARY TABLE `tmpt1` ( `id` int(11) NOT NULL auto_increment, `data1` varchar(50) , `data2` varchar(50) , `data3` varchar(50) , `occurance` datetime , PRIMARY KEY (`id`), UNIQUE(`data1`,`data2`) ) ; INSERT IGNORE INTO `tmpt1`( `id`, `data1`, `data2`, `data3`, `occurance` ) SELECT * FROM `t1` ORDER BY `occurance` DESC -- Rob Wultsch [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ordering by unrelated column in a GROUP BY
You might like to compare the performance of ... SELECT t1.data1, t1.data2, MAX(t1.occurrence) FROM t1 GROUP BY data1,data1 ORDER BY occurrence; with... SELECT t1.data1, t1.data2,t1.occurrence FROM t1 LEFT JOIN t1 AS t2 ON t1.data1=t2.data2 AND t1.data2=t2.data2 AND t1.occurrence t2.occurrence WHERE t2.id IS NULL ORDER BY occurrence; PB - Rob Wultsch wrote: Suppose I have a table: CREATE TABLE `t1` ( `id` int(11) NOT NULL auto_increment, `data1` varchar(50) , `data2` varchar(50) , `data3` varchar(50) , `occurance` datetime , PRIMARY KEY (`id`) ) And I want to pull the most recent entry of each set of unique combinations of `data1` and `data2`. Per the mysql manual: If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL: What is the mysql syntax (if such syntax exists) to order within a group by another field, in this example `occurance`? Is there a better solution to this using sub selects? Am I on crack for considering doing something like: CREATE TEMPORARY TABLE `tmpt1` ( `id` int(11) NOT NULL auto_increment, `data1` varchar(50) , `data2` varchar(50) , `data3` varchar(50) , `occurance` datetime , PRIMARY KEY (`id`), UNIQUE(`data1`,`data2`) ) ; INSERT IGNORE INTO `tmpt1`( `id`, `data1`, `data2`, `data3`, `occurance` ) SELECT * FROM `t1` ORDER BY `occurance` DESC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ugly sql optimization help?
I have the following horrible sql. I need one result that has all the data in one row. I am currently using 3 sub queries and figure it must be a better way... SELECT 'FS_DEV', ifnull(a.severity, 0) AS aseverity, ifnull(a.eventid, 0) AS aeventid, ifnull(a.description, 'None') AS adescription, ifnull(a.hostid, 0) AS ahostid, ifnull(a.color,'#E5E0DC') as acolor, ifnull(a.fontcolor,0) as afontcolor, ifnull(a.severitydesc,'None') as aseveritydesc, ifnull(p.severity, 0) AS pseverity, ifnull(p.eventid, 0) AS peventid, ifnull(p.description, 'None') AS pdescription, ifnull(p.hostid, 0) AS phostid, ifnull(p.color,'#E5E0DC') as pcolor, ifnull(p.fontcolor,0) as pfontcolor, ifnull(p.severitydesc,'None') as pseveritydesc, ifnull(s.severity, 0) AS sseverity, ifnull(s.eventid, 0) AS seventid, ifnull(s.description, 'None') AS sdescription, ifnull(s.hostid, 0) AS shostid, ifnull(s.color,'#E5E0DC') as scolor, ifnull(s.fontcolor,0) as sfontcolor, ifnull(s.severitydesc,'None') as sseveritydesc FROM (SELECT e.severity, e.eventid, e.description, e.hostid, fs.color, fs.fontcolor, fs.severitydesc FROM fs_events e, fs_severity fs WHERE hostid in (21,22,23,24,15,16) and e.severity = fs.severityid AND e.event_perf = 1 AND e.time_stamp = date_sub(now(), INTERVAL 30 DAY) AND e.acknowledged in ( 0,1) ORDER BY e.severity DESC, e.time_stamp DESC LIMIT 0, 1) p, (SELECT e.severity, e.eventid, e.description, e.hostid, fs.color, fs.fontcolor, fs.severitydesc FROM fs_events e, fs_severity fs WHERE hostid in (21,22,23,24,15,16) and e.severity = fs.severityid AND e.event_avail = 1 AND e.time_stamp = date_sub(now(), INTERVAL 30 DAY) AND e.acknowledged in ( 0,1) ORDER BY e.severity DESC, e.time_stamp DESC LIMIT 0, 1) a, (SELECT e.severity, e.eventid, e.description, e.hostid, fs.color, fs.fontcolor, fs.severitydesc FROM fs_events e, fs_severity fs WHERE hostid in (21,22,23,24,15,16) and e.severity = fs.severityid AND e.event_sec = 1 AND e.time_stamp = date_sub(now(), INTERVAL 30 DAY) AND e.acknowledged in ( 0,1) ORDER BY e.severity DESC, e.time_stamp DESC LIMIT 0, 1) s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ordering by unrelated column in a GROUP BY
Peter, Thank you for your reply. MAX(t1.occurrence ) will pull the max of the occurrence column out of the group, but the other collumns (like data3 or id) would still be sorted by the GROUP BY. I will try your second solution, but the tables I am working are thousands of row and your solution look to me like it would be expensive as heck. I am also considering creating a new table that would hold the values of the most recent entries. It would something like: CREATE TABLE `t2` ( `id` int(11) , `data1` varchar(50) , `data2` varchar(50) , UNIQUE KEY (`data1`,`data2`) ) which I would keep up to date with triggers and join against the previously mentioned t1. On 9/25/07, Peter Brawley [EMAIL PROTECTED] wrote: You might like to compare the performance of ... SELECT t1.data1, t1.data2, MAX(t1.occurrence) FROM t1 GROUP BY data1,data1 ORDER BY occurrence; with... SELECT t1.data1, t1.data2,t1.occurrence FROM t1 LEFT JOIN t1 AS t2 ON t1.data1=t2.data2 AND t1.data2=t2.data2 AND t1.occurrence t2.occurrence WHERE t2.id IS NULL ORDER BY occurrence; PB - Rob Wultsch wrote: Suppose I have a table: CREATE TABLE `t1` ( `id` int(11) NOT NULL auto_increment, `data1` varchar(50) , `data2` varchar(50) , `data3` varchar(50) , `occurance` datetime , PRIMARY KEY (`id`) ) And I want to pull the most recent entry of each set of unique combinations of `data1` and `data2`. Per the mysql manual: If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL: What is the mysql syntax (if such syntax exists) to order within a group by another field, in this example `occurance`? Is there a better solution to this using sub selects? Am I on crack for considering doing something like: CREATE TEMPORARY TABLE `tmpt1` ( `id` int(11) NOT NULL auto_increment, `data1` varchar(50) , `data2` varchar(50) , `data3` varchar(50) , `occurance` datetime , PRIMARY KEY (`id`), UNIQUE(`data1`,`data2`) ) ; INSERT IGNORE INTO `tmpt1`( `id`, `data1`, `data2`, `data3`, `occurance` ) SELECT * FROM `t1` ORDER BY `occurance` DESC -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ouch! ibdata files deleted. Why no catastrophe?
Greetings. I've just returned from holidays, and it seems that all but 1 ibdata file ( there were 10! ) have been deleted by a co-worker. He apparently was able to delete them with nautilus ( he was looking to reclaim some space and these were 1GB files each ... and yes, the Trash was emptied as well ). I would have assumed these would be protected from being deleted while mysql was up, but this clearly wasn't the case ( or was it? perhaps they are still around? ). The astonishing thing is that mysql doesn't seem to care about this. It happened 1 week ago, and no-one has complained about any database-related problems since. I've got a nightly backup script which does a 'mysqldump' on each database, and then restarts the server ( which gives me daily transaction logs ). Tonight, I obviously plan on doing a complete reinstall from an old backup, and running the transaction logs. But, just out of curiosity ... what the hell is going on? Why is mysql not complaining bitterly, crashing, and worse? Note the lack of ibdata1 ... ibdata9 screamer mysql # ls -l total 2885424 drwx-- 2 mysql mysql 480 Nov 26 2006 Assets drwx-- 2 mysql mysql 168 Sep 21 14:57 EPricing drwx-- 2 mysql mysql 12512 Sep 25 21:04 NUS drwx-- 2 mysql mysql 656 Nov 26 2006 Timekeeper drwx-- 2 mysql mysql 1216 Jul 4 08:46 dbmail drwx-- 2 mysql mysql 648 Aug 13 14:18 dspam drwx-- 2 mysql mysql 16584 Sep 18 09:32 ebills drwx-- 2 mysql mysql 824 Sep 24 14:45 energy -rw-rw 1 mysql mysql 5242880 Sep 26 09:08 ib_logfile0 -rw-rw 1 mysql mysql 5242880 Sep 26 09:08 ib_logfile1 lrwxrwxrwx 1 root root 15112077312 Sep 26 09:08 ibdata10 drwx-- 2 mysql mysql 1848 Nov 26 2006 mysql drwx-- 2 mysql mysql 648 Dec 20 2006 roundcubemail drwx-- 3 mysql mysql 6240 Sep 26 08:54 sales -rw-rw 1 mysql mysql 239845598 Sep 14 20:40 screamer-bin.000201 -rw-rw 1 mysql mysql 591774120 Sep 17 20:44 screamer-bin.000202 -rw-rw 1 mysql mysql 289226895 Sep 18 20:38 screamer-bin.000203 -rw-rw 1 mysql mysql 281415790 Sep 19 20:36 screamer-bin.000204 -rw-rw 1 mysql mysql 286245722 Sep 20 20:37 screamer-bin.000205 -rw-rw 1 mysql mysql 331061564 Sep 21 20:43 screamer-bin.000206 -rw-rw 1 mysql mysql 411650198 Sep 24 20:38 screamer-bin.000207 -rw-rw 1 mysql mysql 309576301 Sep 25 20:37 screamer-bin.000208 -rw-rw 1 mysql mysql 140007700 Sep 26 09:08 screamer-bin.000209 -rw-rw 1 mysql mysql 3520 Sep 25 20:37 screamer-bin.index -rw-rw 1 mysql mysql 60432327 Sep 26 09:06 screamer-slow.log drwx-- 2 mysql mysql80 Sep 20 11:16 test screamer mysql # The only way I can explain the fact that things are still working is that these files are in fact *not* deleted, and are still in use by mysql, but not visible to anything else. But that's ridiculous as well. So what's going on? -- 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: Ouch! ibdata files deleted. Why no catastrophe?
Did the space become available when deleted? try: lsof | grep deleted see if they're still running in memory. if so you might be able to save them. Daniel Kasak wrote: Greetings. I've just returned from holidays, and it seems that all but 1 ibdata file ( there were 10! ) have been deleted by a co-worker. He apparently was able to delete them with nautilus ( he was looking to reclaim some space and these were 1GB files each ... and yes, the Trash was emptied as well ). I would have assumed these would be protected from being deleted while mysql was up, but this clearly wasn't the case ( or was it? perhaps they are still around? ). The astonishing thing is that mysql doesn't seem to care about this. It happened 1 week ago, and no-one has complained about any database-related problems since. I've got a nightly backup script which does a 'mysqldump' on each database, and then restarts the server ( which gives me daily transaction logs ). Tonight, I obviously plan on doing a complete reinstall from an old backup, and running the transaction logs. But, just out of curiosity ... what the hell is going on? Why is mysql not complaining bitterly, crashing, and worse? Note the lack of ibdata1 ... ibdata9 screamer mysql # ls -l total 2885424 drwx-- 2 mysql mysql 480 Nov 26 2006 Assets drwx-- 2 mysql mysql 168 Sep 21 14:57 EPricing drwx-- 2 mysql mysql 12512 Sep 25 21:04 NUS drwx-- 2 mysql mysql 656 Nov 26 2006 Timekeeper drwx-- 2 mysql mysql 1216 Jul 4 08:46 dbmail drwx-- 2 mysql mysql 648 Aug 13 14:18 dspam drwx-- 2 mysql mysql 16584 Sep 18 09:32 ebills drwx-- 2 mysql mysql 824 Sep 24 14:45 energy -rw-rw 1 mysql mysql 5242880 Sep 26 09:08 ib_logfile0 -rw-rw 1 mysql mysql 5242880 Sep 26 09:08 ib_logfile1 lrwxrwxrwx 1 root root 15112077312 Sep 26 09:08 ibdata10 drwx-- 2 mysql mysql 1848 Nov 26 2006 mysql drwx-- 2 mysql mysql 648 Dec 20 2006 roundcubemail drwx-- 3 mysql mysql 6240 Sep 26 08:54 sales -rw-rw 1 mysql mysql 239845598 Sep 14 20:40 screamer-bin.000201 -rw-rw 1 mysql mysql 591774120 Sep 17 20:44 screamer-bin.000202 -rw-rw 1 mysql mysql 289226895 Sep 18 20:38 screamer-bin.000203 -rw-rw 1 mysql mysql 281415790 Sep 19 20:36 screamer-bin.000204 -rw-rw 1 mysql mysql 286245722 Sep 20 20:37 screamer-bin.000205 -rw-rw 1 mysql mysql 331061564 Sep 21 20:43 screamer-bin.000206 -rw-rw 1 mysql mysql 411650198 Sep 24 20:38 screamer-bin.000207 -rw-rw 1 mysql mysql 309576301 Sep 25 20:37 screamer-bin.000208 -rw-rw 1 mysql mysql 140007700 Sep 26 09:08 screamer-bin.000209 -rw-rw 1 mysql mysql 3520 Sep 25 20:37 screamer-bin.index -rw-rw 1 mysql mysql 60432327 Sep 26 09:06 screamer-slow.log drwx-- 2 mysql mysql80 Sep 20 11:16 test screamer mysql # The only way I can explain the fact that things are still working is that these files are in fact *not* deleted, and are still in use by mysql, but not visible to anything else. But that's ridiculous as well. So what's going on? -- 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: regexp negate string help
MySQL's regex library doesn't have all those Perl features. You can use the pcre-compatible extension from http://www.xcdsql.org/MySQL/UDF/, or just use two clauses in the WHERE: one should be col NOT RLIKE linux$ Baron Tang, Jasmine wrote: Hi, I need to match anything that start with foo then followed by a string containing letter/number/underscore/dot but NOT end with the string linux. When I use the pattern 'foo[(a-z|0-9|_|.)]+(?!linux)' , I got ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp.I got the look-ahead assertion syntax (?!string) from Perl regexp, but mysql apparently doesn't like that. Specially, the following should match: foodafdlj_endwithx food3242jljlsd.endwithn but this should NOT match foosdfjl.blah_linux any suggestions? thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
On Tue, 2007-09-25 at 19:27 -0400, Gary Josack wrote: Did the space become available when deleted? try: lsof | grep deleted see if they're still running in memory. if so you might be able to save them. Thanks for the quick response :) They're there: mysqld 5460 mysql 10uW REG8,3 1073741824 761001 /root/.Trash/ibdata2 (deleted) mysqld 5460 mysql 11uW REG8,3 1073741824 6852461 /root/.Trash/ibdata3 (deleted) mysqld 5460 mysql 12uW REG8,3 1073741824 7376938 /root/.Trash/ibdata4 (deleted) mysqld 5460 mysql 13uW REG8,3 1073741824 6859981 /root/.Trash/ibdata5 (deleted) mysqld 5460 mysql 14uW REG8,3 1073741824 7376491 /root/.Trash/ibdata6 (deleted) mysqld 5460 mysql 15uW REG8,3 1073741824 7376500 /root/.Trash/ibdata7 (deleted) mysqld 5460 mysql 16uW REG8,3 1073741824 1369981 /root/.Trash/ibdata8 (deleted) mysqld 5460 mysql 17uW REG8,3 1073741824 7377058 /root/.Trash/ibdata9 (deleted) ( sorry about the text wrapping thing ) How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. -- 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: Ouch! ibdata files deleted. Why no catastrophe?
Well if you can stop all instances of writes to the databases you should be able to recover them. Each file is going to be in /proc/5460/fd/10-17 the file number corresponds to the fd you see in lsof output ex: cp /proc/5460/fd/10 ibdata2 This is still risky and i reccomend you get a dump immediately. As soon as you restart mysql those files are gone forever. Daniel Kasak wrote: On Tue, 2007-09-25 at 19:27 -0400, Gary Josack wrote: Did the space become available when deleted? try: lsof | grep deleted see if they're still running in memory. if so you might be able to save them. Thanks for the quick response :) They're there: mysqld 5460 mysql 10uW REG8,3 1073741824 761001 /root/.Trash/ibdata2 (deleted) mysqld 5460 mysql 11uW REG8,3 1073741824 6852461 /root/.Trash/ibdata3 (deleted) mysqld 5460 mysql 12uW REG8,3 1073741824 7376938 /root/.Trash/ibdata4 (deleted) mysqld 5460 mysql 13uW REG8,3 1073741824 6859981 /root/.Trash/ibdata5 (deleted) mysqld 5460 mysql 14uW REG8,3 1073741824 7376491 /root/.Trash/ibdata6 (deleted) mysqld 5460 mysql 15uW REG8,3 1073741824 7376500 /root/.Trash/ibdata7 (deleted) mysqld 5460 mysql 16uW REG8,3 1073741824 1369981 /root/.Trash/ibdata8 (deleted) mysqld 5460 mysql 17uW REG8,3 1073741824 7377058 /root/.Trash/ibdata9 (deleted) ( sorry about the text wrapping thing ) How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. -- 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: Ouch! ibdata files deleted. Why no catastrophe?
On Tue, 2007-09-25 at 23:11 -0400, Gary Josack wrote: Well if you can stop all instances of writes to the databases you should be able to recover them. Each file is going to be in /proc/5460/fd/10-17 the file number corresponds to the fd you see in lsof output ex: cp /proc/5460/fd/10 ibdata2 This is still risky and i reccomend you get a dump immediately. As soon as you restart mysql those files are gone forever. Yes I've been reading / thinking more about this, and I've decided against it. There's no real guarantee that MySQL will have written everything to the ibdata files at the point when I make a snapshot of them ( at least as far as I know ). Since the nightly backups are still working perfectly, I'll just shut down MySQL when the backup completes, delete /var/lib/mysql, and import the backup. That sounds a lot safer than anything else at the moment. Thanks again for your response. At least I learned about lsof and recovering deleted files :) -- 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: Ouch! ibdata files deleted. Why no catastrophe?
For future reference. The files do actually continue to be written to. I experience this all the time when people delete logs files and space keeps filling up. Daniel Kasak wrote: On Tue, 2007-09-25 at 23:11 -0400, Gary Josack wrote: Well if you can stop all instances of writes to the databases you should be able to recover them. Each file is going to be in /proc/5460/fd/10-17 the file number corresponds to the fd you see in lsof output ex: cp /proc/5460/fd/10 ibdata2 This is still risky and i reccomend you get a dump immediately. As soon as you restart mysql those files are gone forever. Yes I've been reading / thinking more about this, and I've decided against it. There's no real guarantee that MySQL will have written everything to the ibdata files at the point when I make a snapshot of them ( at least as far as I know ). Since the nightly backups are still working perfectly, I'll just shut down MySQL when the backup completes, delete /var/lib/mysql, and import the backup. That sounds a lot safer than anything else at the moment. Thanks again for your response. At least I learned about lsof and recovering deleted files :) -- 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]
