Re: Recovery from a MySQL dump is too long !
Am 14.08.2012 09:42, schrieb Bob Sauvage: > Hello *, > > My INNODB database has a size of 80GO. I've a replication setup on 3 slaves > and I backup my db from them. > If a problem occurs on the database, a recovery from a dump takes around 6H ! > That's too long for us. > > 2 ideas : > 1. Stop the slave and rsync the folder /var/lib/mysql to another server > 2. Stop the slave, take a dump and when it's finish, pull the dump in another > MySQL instance daily. i do the stop slave and rsync data-dir since many years over WAN connections for offsite backups, never was a friend of dumping databases to textfiles if something is damaged due a power-interruption this is also the fastest restore because rsync is much faster even with --checksums as any dump signature.asc Description: OpenPGP digital signature
Recovery from a MySQL dump is too long !
Hello *, My INNODB database has a size of 80GO. I've a replication setup on 3 slaves and I backup my db from them. If a problem occurs on the database, a recovery from a dump takes around 6H ! That's too long for us. 2 ideas : 1. Stop the slave and rsync the folder /var/lib/mysql to another server 2. Stop the slave, take a dump and when it's finish, pull the dump in another MySQL instance daily. Do you have another ideas ? What's your advices ? Thanks in advance,
crash recovery
Hi, Lately I got this messgae in my errorLog file, need more datails why the DB was restarted! 120711 19:42:06 mysqld restarted 120711 19:42:06 [Warning] Asked for 196608 thread stack, but got 126976 120711 19:42:07 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 120711 19:42:07 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 4 4031987031. InnoDB: Doing recovery: scanned up to log sequence number 4 4031987031 InnoDB: Last MySQL binlog file position 0 78062804, file name ./sdc-bin.97 120711 19:42:07 InnoDB: Flushing modified pages from the buffer pool... 120711 19:42:07 InnoDB: Started; log sequence number 4 4031987031 /usr/sbin/mysqld: ready for connections. Version: '4.1.14-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) -mad
MySQL Data Recovery on Linux from files recovered to lost+found
Hi List I have (had) a mysql database running on a linux server which crashed and suffered e2fsck file system corruption. I applied the e2fsck filesystem checker, which recovered what appears to be most of the files comprising the data, storing them in the "lost+found" directory. This looks something like: [root@vm-rec lost+found]# ls -l | head -rw-r--r-- 1 rootroot163 Jul 16 2009 #1442435 -rw--- 1 rootroot 1753 Mar 27 2011 #1442436 -rw--- 1 rootroot481 Jul 12 2011 #1442437 -rw-r--r-- 1 rootroot 47 Jul 12 2011 #1442438 -rw-r- 1 rootroot646 Aug 21 2010 #1442439 -rw-r--r-- 1 rootroot486 Nov 12 2010 #1442441 So, the names of these files are lost and we are left only with their contents, which seems intact. I can distinguish all the files that would have been part of a MySQL DB, e.g, if I use the linux "file" utility on each file, I get: --- . . . #2474833: MySQL table definition file Version 10 #2474834: MySQL MISAM compressed data file Version 1 #2474836: MySQL table definition file Version 10 #2474839: MySQL MISAM compressed data file Version 1 #2474841: MySQL table definition file Version 10 #2474842: MySQL MISAM compressed data file Version 1 . . . --- ... etc ... Also, if I extract the "ascii" content of one of the "MySQL table definition file" using the linux strings utility, I seem to be able to distinguish the MySQL schema of a table in the database. e.g: --- [root@vm-rec quarrantine]# strings \#2475839 PRIMARY FK3AB9A8B2CDB30B3D InnoDB ) timeslot_id attendee_user_id comments signup_site_id calendar_event_id calendar_id list_index timeslot_id attendee_user_id comments signup_site_id calendar_event_id calendar_id list_index --- Using these strings as keywords, searching through a copy of the original schema which I have reveals that the file seems to contain exactly the schema of a known table: --- CREATE TABLE `signup_ts_attendees` ( `timeslot_id` bigint(20) NOT NULL, `attendee_user_id` varchar(255) NOT NULL, `comments` text, `signup_site_id` varchar(255) NOT NULL, `calendar_event_id` varchar(255) DEFAULT NULL, `calendar_id` varchar(255) DEFAULT NULL, `list_index` int(11) NOT NULL, PRIMARY KEY (`timeslot_id`,`list_index`), KEY `FKBAB08100CDB30B3D` (`timeslot_id`), CONSTRAINT `FKBAB08100CDB30B3D` FOREIGN KEY (`timeslot_id`) REFERENCES `signup_ts` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; --- So it would seem that there might be some way to map these files back to a format with which I can reconstruct the original database ... My question is: Does any one have any recommendations of a process I could use to do this effectively ? Many Thanks in advance Traiano Welcome --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL - licensing data recovery environments
Hi all, does anybody know where can I find a recent guide/information regarding the licensing of data recovery environments with /MySQL Enterprise Server/? Regards Dimitre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: innodb recovery
Hi John, The data files will give you some informations like log_file_size, mutliple tablespace is being used or not. Although my.cnf can help you a lot. With the above information, use it with newer version of mysql. Krishna On Wed, Jan 20, 2010 at 3:02 PM, Johny Brawo wrote: > Hello! > > I got all data files (ibdata1, ib_logfile, etc) recovevered from mine > old Debian 3.1 box (and i dont know MySQL version :( ). I want to get > that DB running again. > Can i copy these files to newer version of MySQL, and if i can - how? > Any commands, any parameters? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com > >
Re: innodb recovery
Hi Johny Do you have the my.cnf configuration file ?? that can simplify things. Carlos On 1/20/2010 3:32 AM, Johny Brawo wrote: Hello! I got all data files (ibdata1, ib_logfile, etc) recovevered from mine old Debian 3.1 box (and i dont know MySQL version :( ). I want to get that DB running again. Can i copy these files to newer version of MySQL, and if i can - how? Any commands, any parameters? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
innodb recovery
Hello! I got all data files (ibdata1, ib_logfile, etc) recovevered from mine old Debian 3.1 box (and i dont know MySQL version :( ). I want to get that DB running again. Can i copy these files to newer version of MySQL, and if i can - how? Any commands, any parameters? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Free online training class on MySQL backups and recovery.
Hey everyone, I just posted new blog post about an online training class I will be doing January the 13th. This class will cover backups, recovery and disaster planning. It is completely free with no strings attached. If you are interested, take a look at the blog post here: http://www.paragon-cs.com/wordpress/?p=358 It contains all the details. There is a limited number of seats available so you need to respond soon. Looking forward to seeing you there! thanks, keith -- Chief Training Officer Paragon Consulting Services 850-637-3877
RE: Replication recovery
B should be the only one with a bad replication position, since it was replicating when A crashed. So just adjust B, and A should catch up as normal (provided you have the last 24 hours of binlogs on B for A to read ) Regards, Gavin Towey -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Wednesday, October 07, 2009 11:12 AM To: Gavin Towey Cc: mysql@lists.mysql.com Subject: Re: Replication recovery When only one machine dies I do send the new master position info to the still running slave, and yes, it does the trick. My main challenge is when A dies and is dead for 24 hours and then B dies too. Now A is already out of synch with B and now B has a new log position... Doesnt this make A now have a huge gap in data? How do I get A up to date with B? thx, Bryancan On 10/07/2009 12:53 PM, Gavin Towey wrote: > In the case that one machine has a power failure, then starts a new binlog, > you just have to set the slave to start replicating from the beginning of > that binlog. That's easy to detect and repair with a daemon script. Even if > both machines die, it'll be a similar scenario. > > Regards, > Gavin Towey > > -Original Message- > From: Bryan Cantwell [mailto:bcantw...@firescope.com] > Sent: Wednesday, October 07, 2009 10:47 AM > To: mysql@lists.mysql.com > Subject: Replication recovery > > I have 2 - 5.0.51a mysql databases setup in a dual master scenario. A is > master of B and vise versa... > In Linux 2.6.26 (if that matters). > Everything is great while all is running normally. But, when I am > testing the system by creating disasterous scenarios, I find some > challenges I hope to get overcome. > > Let's say 'A' machine's plug gets kicked out of the wall and so when > mysql restarts it starts fresh bin-log and the slave 'B' does not > realize this change and we are now out of sync. 'A', however will simply > catch up to 'B' and there MAY not be a problem. > > Even worse, 'A' dies and no one does anything about it, then later 'B' > dies. Now Someone finally comes along and restarts both machines at the > same time and neither are on the 'same page' and are totally out of sync. > > How, without re-copying the datafiles and starting over (after > determining the most up to date machine to use), can I bring both 'A' > and 'B' to the same point so I can move forward? > > Thanks, > > Bryancan > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com > > > The information contained in this transmission may contain privileged and > confidential information. It is intended only for the use of the person(s) > named above. If you are not the intended recipient, you are hereby notified > that any review, dissemination, distribution or duplication of this > communication is strictly prohibited. If you are not the intended recipient, > please contact the sender by reply email and destroy all copies of the > original message. > The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication recovery
When only one machine dies I do send the new master position info to the still running slave, and yes, it does the trick. My main challenge is when A dies and is dead for 24 hours and then B dies too. Now A is already out of synch with B and now B has a new log position... Doesnt this make A now have a huge gap in data? How do I get A up to date with B? thx, Bryancan On 10/07/2009 12:53 PM, Gavin Towey wrote: In the case that one machine has a power failure, then starts a new binlog, you just have to set the slave to start replicating from the beginning of that binlog. That's easy to detect and repair with a daemon script. Even if both machines die, it'll be a similar scenario. Regards, Gavin Towey -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Wednesday, October 07, 2009 10:47 AM To: mysql@lists.mysql.com Subject: Replication recovery I have 2 - 5.0.51a mysql databases setup in a dual master scenario. A is master of B and vise versa... In Linux 2.6.26 (if that matters). Everything is great while all is running normally. But, when I am testing the system by creating disasterous scenarios, I find some challenges I hope to get overcome. Let's say 'A' machine's plug gets kicked out of the wall and so when mysql restarts it starts fresh bin-log and the slave 'B' does not realize this change and we are now out of sync. 'A', however will simply catch up to 'B' and there MAY not be a problem. Even worse, 'A' dies and no one does anything about it, then later 'B' dies. Now Someone finally comes along and restarts both machines at the same time and neither are on the 'same page' and are totally out of sync. How, without re-copying the datafiles and starting over (after determining the most up to date machine to use), can I bring both 'A' and 'B' to the same point so I can move forward? Thanks, Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication recovery
In the case that one machine has a power failure, then starts a new binlog, you just have to set the slave to start replicating from the beginning of that binlog. That's easy to detect and repair with a daemon script. Even if both machines die, it'll be a similar scenario. Regards, Gavin Towey -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Wednesday, October 07, 2009 10:47 AM To: mysql@lists.mysql.com Subject: Replication recovery I have 2 - 5.0.51a mysql databases setup in a dual master scenario. A is master of B and vise versa... In Linux 2.6.26 (if that matters). Everything is great while all is running normally. But, when I am testing the system by creating disasterous scenarios, I find some challenges I hope to get overcome. Let's say 'A' machine's plug gets kicked out of the wall and so when mysql restarts it starts fresh bin-log and the slave 'B' does not realize this change and we are now out of sync. 'A', however will simply catch up to 'B' and there MAY not be a problem. Even worse, 'A' dies and no one does anything about it, then later 'B' dies. Now Someone finally comes along and restarts both machines at the same time and neither are on the 'same page' and are totally out of sync. How, without re-copying the datafiles and starting over (after determining the most up to date machine to use), can I bring both 'A' and 'B' to the same point so I can move forward? Thanks, Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication recovery
I have 2 - 5.0.51a mysql databases setup in a dual master scenario. A is master of B and vise versa... In Linux 2.6.26 (if that matters). Everything is great while all is running normally. But, when I am testing the system by creating disasterous scenarios, I find some challenges I hope to get overcome. Let's say 'A' machine's plug gets kicked out of the wall and so when mysql restarts it starts fresh bin-log and the slave 'B' does not realize this change and we are now out of sync. 'A', however will simply catch up to 'B' and there MAY not be a problem. Even worse, 'A' dies and no one does anything about it, then later 'B' dies. Now Someone finally comes along and restarts both machines at the same time and neither are on the 'same page' and are totally out of sync. How, without re-copying the datafiles and starting over (after determining the most up to date machine to use), can I bring both 'A' and 'B' to the same point so I can move forward? Thanks, Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
Step # 1 : Stop mysql service
# /etc/init.d/mysql stop
Step # 2: Start to MySQL server w/o password:
# mysqld_safe --skip-grant-tables &
Step # 3: Connect to mysql server using mysql client:
# mysql -u root
Step # 4: Setup new MySQL root user password
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where
User='root';
mysql> flush privileges;
mysql> quit
Step # 5: Stop MySQL Server:
# /etc/init.d/mysql stop
Step # 6: Start MySQL server and test it
# /etc/init.d/mysql start
# mysql -u root -p
Note: You can check structure of table 'user'
mysql> SHOW CREATE TABLE 'user';
And update other required fields also.
Enjoy ;-)
On Wed, Aug 26, 2009 at 2:36 PM, Martijn Engler wrote:
> I actually get the feeling you are not connecting as root.
> Try mysql -uroot -p test instead of just mysql test
>
> Have a nice day,
>
> - Martijn
>
> On Wed, Aug 26, 2009 at 03:02, Joe wrote:
> > OK, thanks, that got me in. But upon inspection, the user.host
> > values do not look fouled up as I thought they were (it appears
> > the bogus update may have aborted). But my access problem
> > remains
> >
> > If I start with --skip-grant-tables, 'show databases' shows all
> > DBs. But without that flag, I only see the 'information_schema'
> > DB.
> >
> > Any suggestions as to where I look from here?
> >
> > On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com
> > wrote:
> >> Hey Joe,
> >>
> >> stop the server, start it with --skip-grant-tables, change the
> >> root entry in mysql.user to your liking, and then restart the
> >> server without --skip-grant-tables.
> >>
> >> viola!
> >>
> >> Walter
> >>
> >> On Wed, Aug 26, 2009 at 02:12, Joe
> > wrote:
> >> > We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we
> >> > really need some help regaining access to. While attempting
> >> > to adjust/add remote user access, we accidentally did the
> >> > following:
> >> >
> >> > use mysql;
> >> > update user set host = 'SomeBogusIP' where user = 'root';
> >> >
> >> > Now, we can't get into the DB to fix it:
> >> >
> >> > # mysql test
> >> > ERROR 1044 (42000): Access denied for user ''@'localhost' to
> >> > database 'test'
> >> > # mysql mysql
> >> > ERROR 1044 (42000): Access denied for user ''@'localhost' to
> >> > database 'mysql'
> >> >
> >> > We are not MySQL experts by any stretch, so any help is
> >> > appreciated.
> >> >
> >> >
> >> > Here are the files we evidently touched:
> >> > # ls -ltr /var/lib/mysql/mysql/
> >> > -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD
> >> > -rw-r- 1 mysql mysql844 Aug 25 17:35 user.MYD
> >> > -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI
> >> > -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI
> >> >
> >> > We do have a months-old copy of the 'mysql' db directory.
> >> >
> >> > Thanks in advance.
> >> >
> >> > --
> >> > MySQL General Mailing List
> >> > For list archives: http://lists.mysql.com/mysql
> >> > To unsubscribe:
> >> > http://lists.mysql.com/mysql?unsub=li...@olindata.com
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com
>
>
--
Best Regards,
Prabhat
Re: recovery help needed
I actually get the feeling you are not connecting as root. Try mysql -uroot -p test instead of just mysql test Have a nice day, - Martijn On Wed, Aug 26, 2009 at 03:02, Joe wrote: > OK, thanks, that got me in. But upon inspection, the user.host > values do not look fouled up as I thought they were (it appears > the bogus update may have aborted). But my access problem > remains > > If I start with --skip-grant-tables, 'show databases' shows all > DBs. But without that flag, I only see the 'information_schema' > DB. > > Any suggestions as to where I look from here? > > On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com > wrote: >> Hey Joe, >> >> stop the server, start it with --skip-grant-tables, change the >> root entry in mysql.user to your liking, and then restart the >> server without --skip-grant-tables. >> >> viola! >> >> Walter >> >> On Wed, Aug 26, 2009 at 02:12, Joe > wrote: >> > We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we >> > really need some help regaining access to. While attempting >> > to adjust/add remote user access, we accidentally did the >> > following: >> > >> > use mysql; >> > update user set host = 'SomeBogusIP' where user = 'root'; >> > >> > Now, we can't get into the DB to fix it: >> > >> > # mysql test >> > ERROR 1044 (42000): Access denied for user ''@'localhost' to >> > database 'test' >> > # mysql mysql >> > ERROR 1044 (42000): Access denied for user ''@'localhost' to >> > database 'mysql' >> > >> > We are not MySQL experts by any stretch, so any help is >> > appreciated. >> > >> > >> > Here are the files we evidently touched: >> > # ls -ltr /var/lib/mysql/mysql/ >> > -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD >> > -rw-r- 1 mysql mysql 844 Aug 25 17:35 user.MYD >> > -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI >> > -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI >> > >> > We do have a months-old copy of the 'mysql' db directory. >> > >> > Thanks in advance. >> > >> > -- >> > MySQL General Mailing List >> > For list archives: http://lists.mysql.com/mysql >> > To unsubscribe: >> > http://lists.mysql.com/mysql?unsub=li...@olindata.com > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
R: Re: recovery help needed
When you are in without the flag , issue the following: Select current_user(); It should return root. Then do this: Grant all privileges on *.* 'root'@'%' identified by 'letmein' It should work If you did not mess too much with grant tables. Claudio Il giorno 26 ago, 2009 4:36 m., "Todd Lyons" ha scritto: >> On Wed, Aug 26, 2009 at 02:12, Joe > wrote: >> > We have an inaccessible... So connect to it from somewhere other than localhost. :-) Specifically from the bogus IP you set it to. Let's say you used the bogus IP of 10.200.100.20. 1. Give the mysql server the IP 10.200.100.10 and a netmask 255.255.255.0. 2. On some other machine on the same LAN, give it the bogus IP with the same netmask. 3. No need to worry about routes, it's on the same LAN. 4. On the other machine, connect using 'mysql -h10.200.100.10 -uroot -p'. When you enter the correct password, it should let you in. * I don't know if you'll need to restart mysql for it to bind to the new IP. I don't think so, but then again I have not tested it. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: h...
Re: recovery help needed
>> On Wed, Aug 26, 2009 at 02:12, Joe > wrote: >> > We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we >> > really need some help regaining access to. While attempting >> > to adjust/add remote user access, we accidentally did the >> > following: >> > >> > use mysql; >> > update user set host = 'SomeBogusIP' where user = 'root'; >> > Now, we can't get into the DB to fix it: >> > # mysql test >> > ERROR 1044 (42000): Access denied for user ''@'localhost' to >> > database 'test' So connect to it from somewhere other than localhost. :-) Specifically from the bogus IP you set it to. Let's say you used the bogus IP of 10.200.100.20. 1. Give the mysql server the IP 10.200.100.10 and a netmask 255.255.255.0. 2. On some other machine on the same LAN, give it the bogus IP with the same netmask. 3. No need to worry about routes, it's on the same LAN. 4. On the other machine, connect using 'mysql -h10.200.100.10 -uroot -p'. When you enter the correct password, it should let you in. * I don't know if you'll need to restart mysql for it to bind to the new IP. I don't think so, but then again I have not tested it. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
OK, thanks, that got me in. But upon inspection, the user.host values do not look fouled up as I thought they were (it appears the bogus update may have aborted). But my access problem remains If I start with --skip-grant-tables, 'show databases' shows all DBs. But without that flag, I only see the 'information_schema' DB. Any suggestions as to where I look from here? On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com wrote: > Hey Joe, > > stop the server, start it with --skip-grant-tables, change the > root entry in mysql.user to your liking, and then restart the > server without --skip-grant-tables. > > viola! > > Walter > > On Wed, Aug 26, 2009 at 02:12, Joe wrote: > > We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we > > really need some help regaining access to. While attempting > > to adjust/add remote user access, we accidentally did the > > following: > > > > use mysql; > > update user set host = 'SomeBogusIP' where user = 'root'; > > > > Now, we can't get into the DB to fix it: > > > > # mysql test > > ERROR 1044 (42000): Access denied for user ''@'localhost' to > > database 'test' > > # mysql mysql > > ERROR 1044 (42000): Access denied for user ''@'localhost' to > > database 'mysql' > > > > We are not MySQL experts by any stretch, so any help is > > appreciated. > > > > > > Here are the files we evidently touched: > > # ls -ltr /var/lib/mysql/mysql/ > > -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD > > -rw-r- 1 mysql mysql 844 Aug 25 17:35 user.MYD > > -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI > > -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI > > > > We do have a months-old copy of the 'mysql' db directory. > > > > Thanks in advance. > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?unsub=li...@olindata.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: recovery help needed
You have to reset the permissions. http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Carlos On 8/25/2009 7:12 PM, Joe wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
Hey Joe, stop the server, start it with --skip-grant-tables, change the root entry in mysql.user to your liking, and then restart the server without --skip-grant-tables. viola! Walter On Wed, Aug 26, 2009 at 02:12, Joe wrote: > We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really > need some help regaining access to. While attempting to > adjust/add remote user access, we accidentally did the > following: > > use mysql; > update user set host = 'SomeBogusIP' where user = 'root'; > > Now, we can't get into the DB to fix it: > > # mysql test > ERROR 1044 (42000): Access denied for user ''@'localhost' to > database 'test' > # mysql mysql > ERROR 1044 (42000): Access denied for user ''@'localhost' to > database 'mysql' > > We are not MySQL experts by any stretch, so any help is > appreciated. > > > Here are the files we evidently touched: > # ls -ltr /var/lib/mysql/mysql/ > -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD > -rw-r- 1 mysql mysql 844 Aug 25 17:35 user.MYD > -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI > -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI > > We do have a months-old copy of the 'mysql' db directory. > > Thanks in advance. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
recovery help needed
We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication recovery on restart
Bryan, When the slave encounters that error, you can simply set it to replicate from the next binlog file in the sequence starting at position 98. It should be easy to have a script automate this process. Regards, Gavin Towey -Original Message- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Friday, July 31, 2009 12:51 PM To: mysql@lists.mysql.com Subject: RE: Replication recovery on restart Yes I am trying to simulate total failure. In this test case I am using 2 Virtual Machines and I just kill one and then when it comes back I have the challenge described. How can I go about getting the slave back in tune with the newly restarted master? Thanks -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Friday, July 31, 2009 1:21 PM To: Cantwell, Bryan; mysql@lists.mysql.com Subject: RE: Replication recovery on restart Bryan, How are you restarting mysql? In the case a master crashes, it's definitely common for the slave to miss the fact that the master is using a different binlog. The slave advances to a position past the end of the previous binlog, and stops with and error like "tried to read impossible position." In this case you do have to intervene, but that's an easy enough case to write a script to handle. When restarting mysql normally, you shouldn't have this problem: i.e. service mysql restart / /etc/ini.d/mysql restart Regards, Gavin Towey -Original Message- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Friday, July 31, 2009 10:08 AM To: mysql@lists.mysql.com Subject: RE: Replication recovery on restart Before I simulate a total server failure, master1 is using binary file msyql-bin1 position 2231467 and it's slave master2 is following the correct file at the correct position. This is after initial setup. Once I restart master1, it will then start to use msyql-bin2 position 98 and master 2 is still trying to follow msyql-bin1 position 2231467. And since I have this as dual master setup, if I simulate both boxes restarting in a total catastrophe, the masters both change files and the slaves remain trying to follow on the old information. -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Thursday, July 30, 2009 5:08 PM To: Cantwell, Bryan; mysql@lists.mysql.com Subject: RE: Replication recovery on restart Hi Bryan, Please define "out of whack." Tell us exactly what you're doing when you restart, and what the replication state is before and after, and where the updates are coming from. Regards, Gavin Towey -Original Message- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Thursday, July 30, 2009 11:00 AM To: mysql@lists.mysql.com Subject: Replication recovery on restart I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databases both replicating wonderfully. They are configured in a dual master scenario so that one can take over for the other in my HA environment I've built. All is working great until... If one or the other box reboots or the mysql restarts, the replication gets out of whack. Especially if I simulate both of them crashing in a worst case scenario, they are then both trying to sync from the wrong Master_log_file and Read_Master_Log_Pos... Since catastrpohe WILL happen eventually (heence the need for HA) how do I direct the newly restarted boxes to the right position in the correct files on restart? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain pri
RE: Replication recovery on restart
Yes I am trying to simulate total failure. In this test case I am using 2 Virtual Machines and I just kill one and then when it comes back I have the challenge described. How can I go about getting the slave back in tune with the newly restarted master? Thanks -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Friday, July 31, 2009 1:21 PM To: Cantwell, Bryan; mysql@lists.mysql.com Subject: RE: Replication recovery on restart Bryan, How are you restarting mysql? In the case a master crashes, it's definitely common for the slave to miss the fact that the master is using a different binlog. The slave advances to a position past the end of the previous binlog, and stops with and error like "tried to read impossible position." In this case you do have to intervene, but that's an easy enough case to write a script to handle. When restarting mysql normally, you shouldn't have this problem: i.e. service mysql restart / /etc/ini.d/mysql restart Regards, Gavin Towey -Original Message- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Friday, July 31, 2009 10:08 AM To: mysql@lists.mysql.com Subject: RE: Replication recovery on restart Before I simulate a total server failure, master1 is using binary file msyql-bin1 position 2231467 and it's slave master2 is following the correct file at the correct position. This is after initial setup. Once I restart master1, it will then start to use msyql-bin2 position 98 and master 2 is still trying to follow msyql-bin1 position 2231467. And since I have this as dual master setup, if I simulate both boxes restarting in a total catastrophe, the masters both change files and the slaves remain trying to follow on the old information. -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Thursday, July 30, 2009 5:08 PM To: Cantwell, Bryan; mysql@lists.mysql.com Subject: RE: Replication recovery on restart Hi Bryan, Please define "out of whack." Tell us exactly what you're doing when you restart, and what the replication state is before and after, and where the updates are coming from. Regards, Gavin Towey -Original Message- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Thursday, July 30, 2009 11:00 AM To: mysql@lists.mysql.com Subject: Replication recovery on restart I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databases both replicating wonderfully. They are configured in a dual master scenario so that one can take over for the other in my HA environment I've built. All is working great until... If one or the other box reboots or the mysql restarts, the replication gets out of whack. Especially if I simulate both of them crashing in a worst case scenario, they are then both trying to sync from the wrong Master_log_file and Read_Master_Log_Pos... Since catastrpohe WILL happen eventually (heence the need for HA) how do I direct the newly restarted boxes to the right position in the correct files on restart? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication recovery on restart
Bryan, How are you restarting mysql? In the case a master crashes, it's definitely common for the slave to miss the fact that the master is using a different binlog. The slave advances to a position past the end of the previous binlog, and stops with and error like "tried to read impossible position." In this case you do have to intervene, but that's an easy enough case to write a script to handle. When restarting mysql normally, you shouldn't have this problem: i.e. service mysql restart / /etc/ini.d/mysql restart Regards, Gavin Towey -Original Message- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Friday, July 31, 2009 10:08 AM To: mysql@lists.mysql.com Subject: RE: Replication recovery on restart Before I simulate a total server failure, master1 is using binary file msyql-bin1 position 2231467 and it's slave master2 is following the correct file at the correct position. This is after initial setup. Once I restart master1, it will then start to use msyql-bin2 position 98 and master 2 is still trying to follow msyql-bin1 position 2231467. And since I have this as dual master setup, if I simulate both boxes restarting in a total catastrophe, the masters both change files and the slaves remain trying to follow on the old information. -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Thursday, July 30, 2009 5:08 PM To: Cantwell, Bryan; mysql@lists.mysql.com Subject: RE: Replication recovery on restart Hi Bryan, Please define "out of whack." Tell us exactly what you're doing when you restart, and what the replication state is before and after, and where the updates are coming from. Regards, Gavin Towey -Original Message- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Thursday, July 30, 2009 11:00 AM To: mysql@lists.mysql.com Subject: Replication recovery on restart I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databases both replicating wonderfully. They are configured in a dual master scenario so that one can take over for the other in my HA environment I've built. All is working great until... If one or the other box reboots or the mysql restarts, the replication gets out of whack. Especially if I simulate both of them crashing in a worst case scenario, they are then both trying to sync from the wrong Master_log_file and Read_Master_Log_Pos... Since catastrpohe WILL happen eventually (heence the need for HA) how do I direct the newly restarted boxes to the right position in the correct files on restart? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication recovery on restart
Before I simulate a total server failure, master1 is using binary file msyql-bin1 position 2231467 and it's slave master2 is following the correct file at the correct position. This is after initial setup. Once I restart master1, it will then start to use msyql-bin2 position 98 and master 2 is still trying to follow msyql-bin1 position 2231467. And since I have this as dual master setup, if I simulate both boxes restarting in a total catastrophe, the masters both change files and the slaves remain trying to follow on the old information. -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Thursday, July 30, 2009 5:08 PM To: Cantwell, Bryan; mysql@lists.mysql.com Subject: RE: Replication recovery on restart Hi Bryan, Please define "out of whack." Tell us exactly what you're doing when you restart, and what the replication state is before and after, and where the updates are coming from. Regards, Gavin Towey -Original Message- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Thursday, July 30, 2009 11:00 AM To: mysql@lists.mysql.com Subject: Replication recovery on restart I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databases both replicating wonderfully. They are configured in a dual master scenario so that one can take over for the other in my HA environment I've built. All is working great until... If one or the other box reboots or the mysql restarts, the replication gets out of whack. Especially if I simulate both of them crashing in a worst case scenario, they are then both trying to sync from the wrong Master_log_file and Read_Master_Log_Pos... Since catastrpohe WILL happen eventually (heence the need for HA) how do I direct the newly restarted boxes to the right position in the correct files on restart? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication recovery on restart
Hi Bryan, Please define "out of whack." Tell us exactly what you're doing when you restart, and what the replication state is before and after, and where the updates are coming from. Regards, Gavin Towey -Original Message- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Thursday, July 30, 2009 11:00 AM To: mysql@lists.mysql.com Subject: Replication recovery on restart I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databases both replicating wonderfully. They are configured in a dual master scenario so that one can take over for the other in my HA environment I've built. All is working great until... If one or the other box reboots or the mysql restarts, the replication gets out of whack. Especially if I simulate both of them crashing in a worst case scenario, they are then both trying to sync from the wrong Master_log_file and Read_Master_Log_Pos... Since catastrpohe WILL happen eventually (heence the need for HA) how do I direct the newly restarted boxes to the right position in the correct files on restart? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication recovery on restart
I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databases both replicating wonderfully. They are configured in a dual master scenario so that one can take over for the other in my HA environment I've built. All is working great until... If one or the other box reboots or the mysql restarts, the replication gets out of whack. Especially if I simulate both of them crashing in a worst case scenario, they are then both trying to sync from the wrong Master_log_file and Read_Master_Log_Pos... Since catastrpohe WILL happen eventually (heence the need for HA) how do I direct the newly restarted boxes to the right position in the correct files on restart? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysterious progress after recovery in MySQL Community Edition 5.1.34
I could afford to completely delete the schema (AKA database) into which the "LOAD DATA LOCAL INFILE" is going. How exactly would I do that, given that the server is still really busy shutting down? If necessary, in some instances, I could afford to lose all the data on a given machine (and I am not using replication) if necessary; is there a faster way to do that than uninstall and reinstall? Thanks, Mike Spreitzer Mike Spreitzer/Watson/i...@ibmus 06/12/09 12:57 PM To Michael Dykman cc mysql@lists.mysql.com Subject Re: Mysterious progress after recovery in MySQL Community Edition 5.1.34 BTW, I have another instance of this problem right now. I will try breaking that huge table up into chunks, but have not yet done so. I have a "LOAD DATA LOCAL INFILE" in progress, and want to abort it (so I can try the better way). I have ^Ced the `mysql` client twice, killing it. The server continued working on that statement for a while. I then tried `/usr/share/mysql/mysql.server stop`; that has been in progress for a while now, has printed about 320 dots so far. What is the fastest way to get this thing aborted? Thanks, Mike Spreitzer Mike Spreitzer/Watson/i...@ibmus 06/12/09 11:50 AM To Michael Dykman cc mysql@lists.mysql.com Subject Re: Mysterious progress after recovery in MySQL Community Edition 5.1.34 Yes, when the shutdown was initiated there was a huge "LOAD DATA" in progress. Is there some server config change I should make that would cause commits to happen occasionally during that operation? I know of no way to resume such an operation after the server shutdown and eventual restart, the best I could hope for would be to quickly delete the partially loaded data and start loading it all over again, right? (Now I see why it was suggested I break this data file up into smaller pieces.) Thanks, Mike Spreitzer SMTP: mspre...@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM Office phone: +1-914-784-6424 (IBM T/L 863-) AOL Instant Messaging: M1k3Sprtzr Michael Dykman 06/12/09 11:25 AM To Mike Spreitzer/Watson/i...@ibmus cc mysql@lists.mysql.com Subject Re: Mysterious progress after recovery in MySQL Community Edition 5.1.34 It looks to me like you had trouble shutting down because you were in the middle of a HUGE transaction.. having been killed, a rollback of nearly 10 million statement need be run. I would suggest that somewhere in your processing, you are holding one connection open a long time, doing a lot of work, but failing to commit it periodically. On Fri, Jun 12, 2009 at 10:33 AM, Mike Spreitzer wrote: > A colleague had to kill a MySQL server (on RedHat Enterprise Linux 5) > because it had some problem shutting down. Later I launched it (with > `/usr/share/mysql/mysql.server start`). In its err log I saw the recovery > happen, apparently with a successful completion, and then the usual > announcement that the server is listening on its socket --- which I had > taken to mean the server is ready to be used. Apparently that's not quite > right. After that, I find another series of progress numbers is being > written into the err log, one every few minutes (so the whole recovery > will take hours!). I see no obvious indication of what is progressing. > Can anybody give me a clue about what is going on here? Following is the > tail of my err log right now, starting from some point in the last > shutdown sequence: > > Version: '5.1.34-community-log' socket: '/var/lib/mysql/mysql.sock' port: > 3306 MySQL Community Server (GPL) > 090611 22:59:59 [Note] /usr/sbin/mysqld: Normal shutdown > > 090611 22:59:59 [Note] Event Scheduler: Purging the queue. 0 events > 090611 23:00:01 [Warning] /usr/sbin/mysqld: Forcing close of thread 2 > user: 'root' > > 090612 11:01:41 mysqld_safe Starting mysqld daemon with databases from > /var/lib/mysql > 090612 11:01:41 [Warning] The syntax '--log_slow_queries' is deprecated > and will be removed in MySQL 7.0. Please use > '--slow_query_log'/'--slow_query_log_file' instead. > InnoDB: Log scan progressed past the checkpoint lsn 4 1328926534 > 090612 11:01:41 InnoDB: Database was not shut down normally! > InnoDB: Starting crash recovery. > InnoDB: Reading tablespace information from the .ibd files... > InnoDB: Restoring possible half-written data pages from the doublewrite > InnoDB: buffer... > InnoDB: Doing recovery: scanned up to log sequence number 4 1334169088 > InnoDB: Doing recovery: scanned up to log sequence number 4 1339411968 > InnoDB: Doing recovery: scanned up to log sequence number 4 1344654848 > InnoDB: Doing recovery: scanned up to log sequence number 4 1349897728 > InnoDB: Doing recovery: scanned up to log sequence number 4 1354899846 > InnoDB: 1 transaction(s) which must b
Re: Mysterious progress after recovery in MySQL Community Edition 5.1.34
BTW, I have another instance of this problem right now. I will try breaking that huge table up into chunks, but have not yet done so. I have a "LOAD DATA LOCAL INFILE" in progress, and want to abort it (so I can try the better way). I have ^Ced the `mysql` client twice, killing it. The server continued working on that statement for a while. I then tried `/usr/share/mysql/mysql.server stop`; that has been in progress for a while now, has printed about 320 dots so far. What is the fastest way to get this thing aborted? Thanks, Mike Spreitzer SMTP: mspre...@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM Office phone: +1-914-784-6424 (IBM T/L 863-) AOL Instant Messaging: M1k3Sprtzr Mike Spreitzer/Watson/i...@ibmus 06/12/09 11:50 AM To Michael Dykman cc mysql@lists.mysql.com Subject Re: Mysterious progress after recovery in MySQL Community Edition 5.1.34 Yes, when the shutdown was initiated there was a huge "LOAD DATA" in progress. Is there some server config change I should make that would cause commits to happen occasionally during that operation? I know of no way to resume such an operation after the server shutdown and eventual restart, the best I could hope for would be to quickly delete the partially loaded data and start loading it all over again, right? (Now I see why it was suggested I break this data file up into smaller pieces.) Thanks, Mike Spreitzer SMTP: mspre...@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM Office phone: +1-914-784-6424 (IBM T/L 863-) AOL Instant Messaging: M1k3Sprtzr Michael Dykman 06/12/09 11:25 AM To Mike Spreitzer/Watson/i...@ibmus cc mysql@lists.mysql.com Subject Re: Mysterious progress after recovery in MySQL Community Edition 5.1.34 It looks to me like you had trouble shutting down because you were in the middle of a HUGE transaction.. having been killed, a rollback of nearly 10 million statement need be run. I would suggest that somewhere in your processing, you are holding one connection open a long time, doing a lot of work, but failing to commit it periodically. On Fri, Jun 12, 2009 at 10:33 AM, Mike Spreitzer wrote: > A colleague had to kill a MySQL server (on RedHat Enterprise Linux 5) > because it had some problem shutting down. Later I launched it (with > `/usr/share/mysql/mysql.server start`). In its err log I saw the recovery > happen, apparently with a successful completion, and then the usual > announcement that the server is listening on its socket --- which I had > taken to mean the server is ready to be used. Apparently that's not quite > right. After that, I find another series of progress numbers is being > written into the err log, one every few minutes (so the whole recovery > will take hours!). I see no obvious indication of what is progressing. > Can anybody give me a clue about what is going on here? Following is the > tail of my err log right now, starting from some point in the last > shutdown sequence: > > Version: '5.1.34-community-log' socket: '/var/lib/mysql/mysql.sock' port: > 3306 MySQL Community Server (GPL) > 090611 22:59:59 [Note] /usr/sbin/mysqld: Normal shutdown > > 090611 22:59:59 [Note] Event Scheduler: Purging the queue. 0 events > 090611 23:00:01 [Warning] /usr/sbin/mysqld: Forcing close of thread 2 > user: 'root' > > 090612 11:01:41 mysqld_safe Starting mysqld daemon with databases from > /var/lib/mysql > 090612 11:01:41 [Warning] The syntax '--log_slow_queries' is deprecated > and will be removed in MySQL 7.0. Please use > '--slow_query_log'/'--slow_query_log_file' instead. > InnoDB: Log scan progressed past the checkpoint lsn 4 1328926534 > 090612 11:01:41 InnoDB: Database was not shut down normally! > InnoDB: Starting crash recovery. > InnoDB: Reading tablespace information from the .ibd files... > InnoDB: Restoring possible half-written data pages from the doublewrite > InnoDB: buffer... > InnoDB: Doing recovery: scanned up to log sequence number 4 1334169088 > InnoDB: Doing recovery: scanned up to log sequence number 4 1339411968 > InnoDB: Doing recovery: scanned up to log sequence number 4 1344654848 > InnoDB: Doing recovery: scanned up to log sequence number 4 1349897728 > InnoDB: Doing recovery: scanned up to log sequence number 4 1354899846 > InnoDB: 1 transaction(s) which must be rolled back or cleaned up > InnoDB: in total 9403752 row operations to undo > InnoDB: Trx id counter is 0 18944 > 090612 11:01:47 InnoDB: Starting an apply batch of log records to the > database... > InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 > 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 > 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 > 68 69 70 71 72 73 74 75 76
Re: Mysterious progress after recovery in MySQL Community Edition 5.1.34
Yes, when the shutdown was initiated there was a huge "LOAD DATA" in progress. Is there some server config change I should make that would cause commits to happen occasionally during that operation? I know of no way to resume such an operation after the server shutdown and eventual restart, the best I could hope for would be to quickly delete the partially loaded data and start loading it all over again, right? (Now I see why it was suggested I break this data file up into smaller pieces.) Thanks, Mike Spreitzer SMTP: mspre...@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM Office phone: +1-914-784-6424 (IBM T/L 863-) AOL Instant Messaging: M1k3Sprtzr Michael Dykman 06/12/09 11:25 AM To Mike Spreitzer/Watson/i...@ibmus cc mysql@lists.mysql.com Subject Re: Mysterious progress after recovery in MySQL Community Edition 5.1.34 It looks to me like you had trouble shutting down because you were in the middle of a HUGE transaction.. having been killed, a rollback of nearly 10 million statement need be run. I would suggest that somewhere in your processing, you are holding one connection open a long time, doing a lot of work, but failing to commit it periodically. On Fri, Jun 12, 2009 at 10:33 AM, Mike Spreitzer wrote: > A colleague had to kill a MySQL server (on RedHat Enterprise Linux 5) > because it had some problem shutting down. Later I launched it (with > `/usr/share/mysql/mysql.server start`). In its err log I saw the recovery > happen, apparently with a successful completion, and then the usual > announcement that the server is listening on its socket --- which I had > taken to mean the server is ready to be used. Apparently that's not quite > right. After that, I find another series of progress numbers is being > written into the err log, one every few minutes (so the whole recovery > will take hours!). I see no obvious indication of what is progressing. > Can anybody give me a clue about what is going on here? Following is the > tail of my err log right now, starting from some point in the last > shutdown sequence: > > Version: '5.1.34-community-log' socket: '/var/lib/mysql/mysql.sock' port: > 3306 MySQL Community Server (GPL) > 090611 22:59:59 [Note] /usr/sbin/mysqld: Normal shutdown > > 090611 22:59:59 [Note] Event Scheduler: Purging the queue. 0 events > 090611 23:00:01 [Warning] /usr/sbin/mysqld: Forcing close of thread 2 > user: 'root' > > 090612 11:01:41 mysqld_safe Starting mysqld daemon with databases from > /var/lib/mysql > 090612 11:01:41 [Warning] The syntax '--log_slow_queries' is deprecated > and will be removed in MySQL 7.0. Please use > '--slow_query_log'/'--slow_query_log_file' instead. > InnoDB: Log scan progressed past the checkpoint lsn 4 1328926534 > 090612 11:01:41 InnoDB: Database was not shut down normally! > InnoDB: Starting crash recovery. > InnoDB: Reading tablespace information from the .ibd files... > InnoDB: Restoring possible half-written data pages from the doublewrite > InnoDB: buffer... > InnoDB: Doing recovery: scanned up to log sequence number 4 1334169088 > InnoDB: Doing recovery: scanned up to log sequence number 4 1339411968 > InnoDB: Doing recovery: scanned up to log sequence number 4 1344654848 > InnoDB: Doing recovery: scanned up to log sequence number 4 1349897728 > InnoDB: Doing recovery: scanned up to log sequence number 4 1354899846 > InnoDB: 1 transaction(s) which must be rolled back or cleaned up > InnoDB: in total 9403752 row operations to undo > InnoDB: Trx id counter is 0 18944 > 090612 11:01:47 InnoDB: Starting an apply batch of log records to the > database... > InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 > 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 > 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 > 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 > 93 94 95 96 97 98 99 > InnoDB: Apply batch completed > InnoDB: Last MySQL binlog file position 0 235599817, file name > ./mysql-bin.05 > InnoDB: Starting in background the rollback of uncommitted transactions > 090612 11:07:21 InnoDB: Rolling back trx with id 0 18568, 9403752 rows to > undo > > InnoDB: Progress in percents: 1090612 11:07:21 InnoDB: Started; log > sequence number 4 1354899846 > 090612 11:07:21 [Note] Recovering after a crash using mysql-bin > 090612 11:07:25 [Note] Starting crash recovery... > 090612 11:07:25 [Note] Crash recovery finished. > 090612 11:07:25 [Note] Event Scheduler: Loaded 0 events > 090612 11:07:25 [Note] /usr/sbin/mysqld: ready for connections. > Version: '5.1.34-community-log' socket: '/var/lib/mysql/mysql.sock' port: > 3
Re: Mysterious progress after recovery in MySQL Community Edition 5.1.34
It looks to me like you had trouble shutting down because you were in the middle of a HUGE transaction.. having been killed, a rollback of nearly 10 million statement need be run. I would suggest that somewhere in your processing, you are holding one connection open a long time, doing a lot of work, but failing to commit it periodically. On Fri, Jun 12, 2009 at 10:33 AM, Mike Spreitzer wrote: > A colleague had to kill a MySQL server (on RedHat Enterprise Linux 5) > because it had some problem shutting down. Later I launched it (with > `/usr/share/mysql/mysql.server start`). In its err log I saw the recovery > happen, apparently with a successful completion, and then the usual > announcement that the server is listening on its socket --- which I had > taken to mean the server is ready to be used. Apparently that's not quite > right. After that, I find another series of progress numbers is being > written into the err log, one every few minutes (so the whole recovery > will take hours!). I see no obvious indication of what is progressing. > Can anybody give me a clue about what is going on here? Following is the > tail of my err log right now, starting from some point in the last > shutdown sequence: > > Version: '5.1.34-community-log' socket: '/var/lib/mysql/mysql.sock' port: > 3306 MySQL Community Server (GPL) > 090611 22:59:59 [Note] /usr/sbin/mysqld: Normal shutdown > > 090611 22:59:59 [Note] Event Scheduler: Purging the queue. 0 events > 090611 23:00:01 [Warning] /usr/sbin/mysqld: Forcing close of thread 2 > user: 'root' > > 090612 11:01:41 mysqld_safe Starting mysqld daemon with databases from > /var/lib/mysql > 090612 11:01:41 [Warning] The syntax '--log_slow_queries' is deprecated > and will be removed in MySQL 7.0. Please use > '--slow_query_log'/'--slow_query_log_file' instead. > InnoDB: Log scan progressed past the checkpoint lsn 4 1328926534 > 090612 11:01:41 InnoDB: Database was not shut down normally! > InnoDB: Starting crash recovery. > InnoDB: Reading tablespace information from the .ibd files... > InnoDB: Restoring possible half-written data pages from the doublewrite > InnoDB: buffer... > InnoDB: Doing recovery: scanned up to log sequence number 4 1334169088 > InnoDB: Doing recovery: scanned up to log sequence number 4 1339411968 > InnoDB: Doing recovery: scanned up to log sequence number 4 1344654848 > InnoDB: Doing recovery: scanned up to log sequence number 4 1349897728 > InnoDB: Doing recovery: scanned up to log sequence number 4 1354899846 > InnoDB: 1 transaction(s) which must be rolled back or cleaned up > InnoDB: in total 9403752 row operations to undo > InnoDB: Trx id counter is 0 18944 > 090612 11:01:47 InnoDB: Starting an apply batch of log records to the > database... > InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 > 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 > 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 > 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 > 93 94 95 96 97 98 99 > InnoDB: Apply batch completed > InnoDB: Last MySQL binlog file position 0 235599817, file name > ./mysql-bin.05 > InnoDB: Starting in background the rollback of uncommitted transactions > 090612 11:07:21 InnoDB: Rolling back trx with id 0 18568, 9403752 rows to > undo > > InnoDB: Progress in percents: 1090612 11:07:21 InnoDB: Started; log > sequence number 4 1354899846 > 090612 11:07:21 [Note] Recovering after a crash using mysql-bin > 090612 11:07:25 [Note] Starting crash recovery... > 090612 11:07:25 [Note] Crash recovery finished. > 090612 11:07:25 [Note] Event Scheduler: Loaded 0 events > 090612 11:07:25 [Note] /usr/sbin/mysqld: ready for connections. > Version: '5.1.34-community-log' socket: '/var/lib/mysql/mysql.sock' port: > 3306 MySQL Community Server (GPL) > 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 > 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 > > > > Thanks, > Mike Spreitzer > -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysterious progress after recovery in MySQL Community Edition 5.1.34
A colleague had to kill a MySQL server (on RedHat Enterprise Linux 5) because it had some problem shutting down. Later I launched it (with `/usr/share/mysql/mysql.server start`). In its err log I saw the recovery happen, apparently with a successful completion, and then the usual announcement that the server is listening on its socket --- which I had taken to mean the server is ready to be used. Apparently that's not quite right. After that, I find another series of progress numbers is being written into the err log, one every few minutes (so the whole recovery will take hours!). I see no obvious indication of what is progressing. Can anybody give me a clue about what is going on here? Following is the tail of my err log right now, starting from some point in the last shutdown sequence: Version: '5.1.34-community-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 090611 22:59:59 [Note] /usr/sbin/mysqld: Normal shutdown 090611 22:59:59 [Note] Event Scheduler: Purging the queue. 0 events 090611 23:00:01 [Warning] /usr/sbin/mysqld: Forcing close of thread 2 user: 'root' 090612 11:01:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 090612 11:01:41 [Warning] The syntax '--log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '--slow_query_log'/'--slow_query_log_file' instead. InnoDB: Log scan progressed past the checkpoint lsn 4 1328926534 090612 11:01:41 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 4 1334169088 InnoDB: Doing recovery: scanned up to log sequence number 4 1339411968 InnoDB: Doing recovery: scanned up to log sequence number 4 1344654848 InnoDB: Doing recovery: scanned up to log sequence number 4 1349897728 InnoDB: Doing recovery: scanned up to log sequence number 4 1354899846 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 9403752 row operations to undo InnoDB: Trx id counter is 0 18944 090612 11:01:47 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 235599817, file name ./mysql-bin.05 InnoDB: Starting in background the rollback of uncommitted transactions 090612 11:07:21 InnoDB: Rolling back trx with id 0 18568, 9403752 rows to undo InnoDB: Progress in percents: 1090612 11:07:21 InnoDB: Started; log sequence number 4 1354899846 090612 11:07:21 [Note] Recovering after a crash using mysql-bin 090612 11:07:25 [Note] Starting crash recovery... 090612 11:07:25 [Note] Crash recovery finished. 090612 11:07:25 [Note] Event Scheduler: Loaded 0 events 090612 11:07:25 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.34-community-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Thanks, Mike Spreitzer
Re: Site Attack/Failure Recovery
Is there anyway of doing what is described below with version 5 or will I l have to wait for MySQL 6.0 PlanetMySQL Blog: MySQL 6.0 Feature #2: Online Backup Alexander Nozdrin, Chuck Bell, Lars Thalmann, Peter Gulutzan, Rafal Somla BACKUP DATABASE copies all data and metadata in one or more MySQL databases, into an "image file". RESTORE reads an image file and rewrites all the data and metadata in one or more MySQL databases. So if you lose a database, you can recover all of it as of the time of the last BACKUP DATABASE statement.And then you can re-run the statements in MySQL's binary log to recover "from the time of the last BACKUP DATABASE statement", "to the time that the database loss occurred". In other words ... Careful Use of Online Backup will protect from database loss. Rob Wultsch wrote: On Wed, May 14, 2008 at 10:25 PM, John Comerford <[EMAIL PROTECTED]> wrote: 2) Incremental Backups - say one every half hour, then a script to transfer that to an off site machine that way I can get the DB back to within the last good half hour... http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Being compromised is not inevitable, but hardware failure is. Having trusted (an therefore tested) backups is the only way to operate. Is there some practice in particular you are concerned about? Blanket suggestion: Don't escape things manually, have the db (or emulation) do it for you using prepared statements. It is easier to code this way, and much more secure in the long run. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Site Attack/Failure Recovery
I have worked as a DBA for a long time, just not with MySQL and I have spent a lot of time making sure the application is secure. I suppose my question was really what is the best way to do incremental backups? The DB I have most experience with has thing like after imaging etc. which allows you to easily roll A DB forward to a particular point in time, making disaster recover pretty straight forward. I am wondering if there is such a thing in MySQL or is there a product/scripts that are considered the "standard" for doing this sort of thing? Rob Wultsch wrote: On Wed, May 14, 2008 at 11:13 PM, John Comerford <[EMAIL PROTECTED]> wrote: Quickly scanning this page, it doesn't seem to give syntax for an incremental backup. I am hoping to be able to run something that dumps only data changed since the last backup. You could use diff and the previous dump to generate a incremental dump. I very much suggest against this. If you don't spend the time to really understand what is going on then you are going to end up hosed without a good backup to revert to. I suggest that if don't know how to sanitize input then you are over your head. People get paid a lot of money to to be DBA's, and good chunk of that is understanding disaster mitigation/recovery. K.I.S.S.: words to live by. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Site Attack/Failure Recovery
On Wed, May 14, 2008 at 11:13 PM, John Comerford <[EMAIL PROTECTED]> wrote: > Quickly scanning this page, it doesn't seem to give syntax for an > incremental backup. I am hoping to be able to run something that dumps only > data changed since the last backup. > You could use diff and the previous dump to generate a incremental dump. I very much suggest against this. If you don't spend the time to really understand what is going on then you are going to end up hosed without a good backup to revert to. I suggest that if don't know how to sanitize input then you are over your head. People get paid a lot of money to to be DBA's, and good chunk of that is understanding disaster mitigation/recovery. K.I.S.S.: words to live by. -- Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Site Attack/Failure Recovery
Quickly scanning this page, it doesn't seem to give syntax for an incremental backup. I am hoping to be able to run something that dumps only data changed since the last backup. Rob Wultsch wrote: On Wed, May 14, 2008 at 10:25 PM, John Comerford <[EMAIL PROTECTED]> wrote: 2) Incremental Backups - say one every half hour, then a script to transfer that to an off site machine that way I can get the DB back to within the last good half hour... http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Being compromised is not inevitable, but hardware failure is. Having trusted (an therefore tested) backups is the only way to operate. Is there some practice in particular you are concerned about? Blanket suggestion: Don't escape things manually, have the db (or emulation) do it for you using prepared statements. It is easier to code this way, and much more secure in the long run. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Site Attack/Failure Recovery
On Wed, May 14, 2008 at 10:25 PM, John Comerford <[EMAIL PROTECTED]> wrote: > 2) Incremental Backups - say one every half hour, then a script to transfer > that to an off site machine that way I can get the DB back to within the > last good half hour... http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Being compromised is not inevitable, but hardware failure is. Having trusted (an therefore tested) backups is the only way to operate. Is there some practice in particular you are concerned about? Blanket suggestion: Don't escape things manually, have the db (or emulation) do it for you using prepared statements. It is easier to code this way, and much more secure in the long run. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Site Attack/Failure Recovery
Hi Folks, I am fairly new to MySQL and I am going to be setting up a web site on a third party hosting machine. I continuously hear horror stories about machines/sites being hacked and databases being destroyed. Despite my best efforts I am sure I have some security flaws in my site. What I am looking to do is provide myself with a mechanism to roll back my database to a clean state if I happen to suffer one of these attacks (or a failure). I was wondering what is the best way to do this. From my limited knowledge of MySQL I think maybe I could use one of the following: 1) Binary Logs - Not really sure how these work but I was thinking of maybe coping them to an off site machine every half hour and apply them in sequence if I need to go back to a point in time ? 2) Incremental Backups - say one every half hour, then a script to transfer that to an off site machine that way I can get the DB back to within the last good half hour... I don't really know much about either so if someone could give me some pointers as to which is best it would be much appreciated... Thanks, JC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crash Recovery Problem
I've got MySQL 5.0.55a running on a Windows XP system. All tables in the active database on the system are innodb. The startup log says that innodb recovery completed and that connections are available. Meanwhile, mysql is writing to the hdd at around 2MB/sec according to process explorer. I can connect to the server fine until I run a query against the database. Then no other connections can issue any command whatsoever. The mysql client just freezes up, and I can't make any new connections to the server either. After a period of time, mysqld-nt.exe crashed and one thread is left running, still writing to the drive at approx 2MB/sec. Does anyone out there have any idea what's going wrong here? I'm stumped. -- Grant Limberg [EMAIL PROTECTED]
Re: InnoDB Crash RECOVERY HELP (Urgent)
The error message says to go to http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html to learn how to set the different recovery options for innodb. On 9/21/06, Sayed Hadi Rastgou Haghi <[EMAIL PROTECTED]> wrote: Dear all, our DB server crashed and when I try to start Mysql /etc/init.d/mysql/start I get these lins in my error log 060921 13:00:14 mysqld started 060921 13:00:14 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 060921 13:00:14 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 3 3546172175. InnoDB: Error: tried to read 65536 bytes at offset 0 2173440. InnoDB: Was only able to read 54784. InnoDB: Fatal error: cannot read from file. OS error number 17. 060921 13:01:24InnoDB: Assertion failure in thread 3086943936 in file os0file.c line 2107 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=536866816 max_used_connections=0 max_connections=550 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2094947 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbffe3d4c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x814cbfb 0x8d28b8 0x1 0x8355aed 0x835c659 0x835ce73 0x829ba01 0x81d3af3 0x81c5cb2 0x815028a 0x773e33 0x80e0c71 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 060921 13:01:24 mysqld ended when I add skip-innodb in my.cnf, it startsup but my innodb tables could not be accessed. How can I start MySQL server again? -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. http://www.spreadfirefox.com/?q=affiliates&id=0&t=1";> Get Firefox! -- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Crash RECOVERY HELP (Urgent)
Dear all, our DB server crashed and when I try to start Mysql /etc/init.d/mysql/start I get these lins in my error log 060921 13:00:14 mysqld started 060921 13:00:14 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 060921 13:00:14 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 3 3546172175. InnoDB: Error: tried to read 65536 bytes at offset 0 2173440. InnoDB: Was only able to read 54784. InnoDB: Fatal error: cannot read from file. OS error number 17. 060921 13:01:24InnoDB: Assertion failure in thread 3086943936 in file os0file.c line 2107 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=536866816 max_used_connections=0 max_connections=550 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2094947 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbffe3d4c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x814cbfb 0x8d28b8 0x1 0x8355aed 0x835c659 0x835ce73 0x829ba01 0x81d3af3 0x81c5cb2 0x815028a 0x773e33 0x80e0c71 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 060921 13:01:24 mysqld ended when I add skip-innodb in my.cnf, it startsup but my innodb tables could not be accessed. How can I start MySQL server again? -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. http://www.spreadfirefox.com/?q=affiliates&id=0&t=1";> Get Firefox!
InnoDB crash recovery and innodb_support_xa, sync_binlog
Hello, I have some question about InnoDB crash recovery. Q: I understand transaction and write to disk sequence as following figure. Is this correct? w/ innodb_flush_log_at_trx_commit = 1 innodb_support_xa = on sync_binlog = 1 skip-innodb_doublewrite BEGIN INSERT \ +-- store --> biglog_cache (memory) +-- fsync? --> innodb_log_file (disk) (A) ==> COMMIT \ +-- fdatasync--> binlog (disk) (B) ==>| +-- (prepare) fsync? --> innodb_log_file (disk) (C) ==>| +-- (commit) fsycn? --> innodb_log_file (disk) (D) ==> Q: How InnoDB crash recovery when suddenly OS crash at (A)..(D)? (A) roll back by innodb_log_file. (B) roll back by innodb_log_file and remove INSERT from binlog. (C) roll back by innodb_log_file and remove INSERT from binlog. (D) roll forward by innodb_log_file. Q: If sync_binlog = 0, what happen? If still binlog did not sync to disk ... (B),(C) does mysqld failed to removing INSERT from binglog in crash recovery sequence? (D) INSERT in binlog is vanished? If so, table data in replicated master and slave is collapsed? (master has INSERTed row but slave doest not have.) Q: If innodb_support_xa = off, what happen? (B),(C) When roll back by innodb_log_file, mysqld does or does not remove INSERT from binlog? Q: What is best setting for crash recovery? I suppose, "innodb_support_xa = on and sync_binlog = 1" is best setting. But mysqld is VERY VERY slower when enable innodb_support_xa and/or sync_binlog. xa=on + sync_binlog=0 is2 times slower than xa=off + sync_binlog=0. xa=on + sync_binlog=1 is 4..7 times slower than xa=off + sync_binlog=0. # This benchmark done with hard disk that have 128MB write cache and # battery backup unit. but xa=on + sync_binlog=1 is very slower... Please any answer or advice. -- HIROSE, Masaaki -- 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
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 sev
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: Permiss
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:
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]
Backup and recovery problems
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, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ib_logfile based recovery
On 5/24/06, Christopher Korn <[EMAIL PROTECTED]> wrote: Hello everyone, Today a script killed a few InnoDB tables in my database. Although I have backups (2 days old) I want to recover - if possible - a more current state of my database. Is it possible to recover tables via the "ib_logfileX" files? If i simply open the files with VIm I can see that much of the data I need is in this file. Greetings, Chris -- --chris [EMAIL PROTECTED] Do you have binary logging enabled from the time of your last backup ,if so you could try to run it to get back the data upto the point where your tables were deleted. You could try doing this accoring to the manual at http://dev.mysql.com/tech-resources/articles/point_in_time_recovery.html It has various methods which will suit your needs, just follow them carefully... Thats the best way to recover lost data due to an unfortunate delete(s) The ib_logfileX files are log files used by the innodb storage engine mainly to rollback any uncommitted transactions so that the database is in a consistent state after it recovers from a crash, so I don't think you could use that file for recovery of lost data, and yes the file is basically text, but its only understood by the innodb storage engine ( as far as I know) Kishore Jalleda http://kjalleda.googlepages.com/mysqlprojects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ib_logfile based recovery
Hello everyone, Today a script killed a few InnoDB tables in my database. Although I have backups (2 days old) I want to recover - if possible - a more current state of my database. Is it possible to recover tables via the "ib_logfileX" files? If i simply open the files with VIm I can see that much of the data I need is in this file. Greetings, Chris -- --chris [EMAIL PROTECTED] Bis zu 70% Ihrer Onlinekosten sparen: GMX SmartSurfer! Kostenlos downloaden: http://www.gmx.net/de/go/smartsurfer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple-Master Replication recovery
Thanks a lot. I'll try. - Original Message - From: "Kishore Jalleda" <[EMAIL PROTECTED]> To: "古雷" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, March 29, 2006 11:53 PM Subject: Re: Multiple-Master Replication recovery > any one of the servers could go down in many ways like > 1) disk crash > 2) replication failure > 3) power failure > 4) any hardware component failure > 5) OS hang > 6) Network failure > 7) MYSQL bug > 8) table corruption etc ... > 9) or just scheduled donwtime > > in any case what really matters is the difference in status of the failed > server from the point of failure to the point of recovery , so it all > depends on how up-to-date your data is on the failed server.. here are some > typical cases .. > 1) you have everything intact ( data, bin logs, *.info files, etc ) then > just bring back the failed server online and you are good to go > 2) if you rebuild the server from scratch it depends on how current your > backup is , in this take a snapshot of the main server ( either hot or cold > depending on your setup) and start the multi-master replication .. > > In theory you really dont have to take the server offline unless it really > demands , in the worst case as Barry said its lot easier to shut down the > master (if you can afford some downtime) take a complete > snapshot/backup/dump ( varies for MYISAM and INNODB) and bring back the > failed server back online > > Hope this helps > > Kishore Jalleda > > > On 3/28/06, 古雷 <[EMAIL PROTECTED]> wrote: >> >> Hello: >> >> If I use Multiple-Master Replication with two mysql server, when one of >> them goes down(disk crashed) must I shutdown the good one to recover the >> Multiple-Master Replication ? >> >> regards, >> >> gu lei >
Re: Multiple-Master Replication recovery
any one of the servers could go down in many ways like 1) disk crash 2) replication failure 3) power failure 4) any hardware component failure 5) OS hang 6) Network failure 7) MYSQL bug 8) table corruption etc ... 9) or just scheduled donwtime in any case what really matters is the difference in status of the failed server from the point of failure to the point of recovery , so it all depends on how up-to-date your data is on the failed server.. here are some typical cases .. 1) you have everything intact ( data, bin logs, *.info files, etc ) then just bring back the failed server online and you are good to go 2) if you rebuild the server from scratch it depends on how current your backup is , in this take a snapshot of the main server ( either hot or cold depending on your setup) and start the multi-master replication .. In theory you really dont have to take the server offline unless it really demands , in the worst case as Barry said its lot easier to shut down the master (if you can afford some downtime) take a complete snapshot/backup/dump ( varies for MYISAM and INNODB) and bring back the failed server back online Hope this helps Kishore Jalleda On 3/28/06, 古雷 <[EMAIL PROTECTED]> wrote: > > Hello: > > If I use Multiple-Master Replication with two mysql server, when one of > them goes down(disk crashed) must I shutdown the good one to recover the > Multiple-Master Replication ? > > regards, > > gu lei
Re: Multiple-Master Replication recovery
古雷 wrote: > Hello: > If I use Multiple-Master Replication with two mysql server, when one of them > goes down(disk crashed) must I shutdown the good one to recover the > Multiple-Master Replication ? I think yes. Depends on what you mean with "recover the Multiple-Master Replication" Do you want to have the server with the new disk behave again as the replicant? Normally you can switch it on demand but yeah i would prefer to shut it down and start it after you have set it up. Regards Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple-Master Replication recovery
Hello: If I use Multiple-Master Replication with two mysql server, when one of them goes down(disk crashed) must I shutdown the good one to recover the Multiple-Master Replication ? regards, gu lei
Re: question about recovery with binlog
Hello. At least it replaces the contents of my test file. If you're able to provide the test case where replace utility doesn't work please provide it to the list with the contents of the file. wangxu wrote: > I execute follow statement. > - > shell>replace @@session.sql_mode=0 @@session.sql_mode=1 -- 1.01 > - > But string "@@session.sql_mode=0" doesn't replace to "@@session.sql_mode=1". > > > > - Original Message - > From: "Gleb Paharenko" <[EMAIL PROTECTED]> > To: > Sent: Monday, January 23, 2006 7:56 PM > Subject:Re: question about recovery with binlog > > > >>Hello. >> >>There a lot of different ways to perform this operation. See: >> http://dev.mysql.com/doc/refman/5.0/en/replace-utility.html >> man sed >> man awk >> >> >>wangxu wrote: >> >>>How to replace it? >>> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >> >> -- 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: question about recovery with binlog
I execute follow statement. - shell>replace @@session.sql_mode=0 @@session.sql_mode=1 -- 1.01 - But string "@@session.sql_mode=0" doesn't replace to "@@session.sql_mode=1". - Original Message - From: "Gleb Paharenko" <[EMAIL PROTECTED]> To: Sent: Monday, January 23, 2006 7:56 PM Subject:Re: question about recovery with binlog > Hello. > > There a lot of different ways to perform this operation. See: > http://dev.mysql.com/doc/refman/5.0/en/replace-utility.html > man sed > man awk > > > wangxu wrote: > > How to replace it? > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: question about recovery with binlog
Hello. There a lot of different ways to perform this operation. See: http://dev.mysql.com/doc/refman/5.0/en/replace-utility.html man sed man awk wangxu wrote: > How to replace it? > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about recovery with binlog
How to replace it?
The output is a binary file.
- Original Message -
From: "Gleb Paharenko" <[EMAIL PROTECTED]>
To:
Sent: Friday, January 20, 2006 10:11 PM
Subject: Re: question about recovery with binlog
> Hello.
>
> Just a possible workaround - you can parse the output of mysqlbinlog and
> replace the buggy statement with the correct one.
>
>
> wangxu wrote:
> > My mysql version is 5.0.16.
> >
> > My problem is similar to the bug.
> >
> > My sql mode is ANSI and TRADITIONAL
> >
> > If mysql can't do recovery with mysqlbinlog at my sql mode until the bug
> > will be fixed?
> >
> > Follow is the information of my binlog.
> >
> > --
> > /*!40019 SET @@session.max_insert_delayed_threads=0*/;
> > /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
> > # at 4
> > #060119 13:55:30 server id 1 end_log_pos 98Start: binlog v 4, server v
> > 5.0.16-standard-log created 060119 13:55:30
> > # Warning: this binlog was not closed properly. Most probably mysqld
> > crashed writing it.
> > # at 98
> > #060119 13:57:41 server id 1 end_log_pos 165 Query thread_id=4
> > exec_time=0 error_code=0
> > use wangxu;
> > SET TIMESTAMP=1137650261;
> > SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
> > @@session.unique_checks=1;
> > SET @@session.sql_mode=501481487;
> > SET
> > @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
> > insert ht_detail
> > values('79NK0006','79NK0006',1),('79NK0007','79NK0007',1),('79NK0008','79NK0008',1);
> > # at 263
> > #060119 13:57:41 server id 1 end_log_pos 290 Xid = 215
> > COMMIT;
> > # at 290
> > #060119 14:08:30 server id 1 end_log_pos 445 Query thread_id=7
> > exec_time=0 error_code=0
> > SET TIMESTAMP=1137650910;
> > SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
> > SET @@session.sql_mode=524288;
> > CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wangxu` /*!40100 DEFAULT
> > CHARACTER SET latin1 */;
> > # at 445
> > #060119 14:08:30 server id 1 end_log_pos 542 Query thread_id=7
> > exec_time=0 error_code=0
> > SET TIMESTAMP=1137650910;
> > DROP TABLE IF EXISTS `ht_detail`;
> > # at 542
> > #060119 14:08:40 server id 1 end_log_pos 639 Query thread_id=8
> > exec_time=0 error_code=0
> > SET TIMESTAMP=1137650920;
> > DROP TABLE IF EXISTS `ht_detail`;
> > # at 639
> > #060119 14:23:13 server id 1 end_log_pos 733 Query thread_id=11
> > exec_time=0 error_code=0
> > SET TIMESTAMP=1137651793;
> > SET @@session.foreign_key_checks=1, @@session.unique_checks=1;
> > SET @@session.sql_mode=501481487;
> > create table ht_header(a int);
> > # at 733
> > #060119 14:23:53 server id 1 end_log_pos 818 Query thread_id=12
> > exec_time=0 error_code=0
> > SET TIMESTAMP=1137651833;
> > drop table ht_header;
> > # at 818
> > #060119 14:28:10 server id 1 end_log_pos 915 Query thread_id=15
> > exec_time=0 error_code=0
> > SET TIMESTAMP=1137652090;
> > SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
> > SET @@session.sql_mode=524288;
> > DROP TABLE IF EXISTS `ht_detail`;
> > # at 915
> > #060119 14:29:44 server id 1 end_log_pos 998 Query thread_id=16
> > exec_time=0 error_code=0
> > use test;
> > SET TIMESTAMP=1137652184;
> > SET @@session.foreign_key_checks=1, @@session.unique_checks=1;
> > SET @@session.sql_mode=501481487;
> > drop table ht_detail;
> > # at 998
> > #060119 14:30:29 server id 1 end_log_pos 1160 Query thread_id=16
> > exec_time=0 error_code=0
> > SET TIMESTAMP=1137652229;
> > create table ht_detail(hth varchar(30),sbh varchar(15),sbsl int,primary
> > key(hth,sbh)) type = innodb;
> > # at 1160
> > #060119 14:54:50 server id 1 end_log_pos 1243 Query thread_id=20
> > exec_time=0 error_code=0
> > SET TIMESTAMP=1137653690;
> > drop schema wangxu;
> > # at 1243
> > #060119 14:54:57 server id 1 end_log_pos 1328 Query thread_id=20
> > exec_time=0 error_code=0
> > SET TIMESTAMP=1137653697;
> > create schema wangxu;
> > # at 1328
> > #060119 14:55:17 server id 1 end_log_pos 1422 Query thread_id=20
> > exec_time=0 error_code=0
> > use wangxu;
Re: question about recovery with binlog
Hello.
Just a possible workaround - you can parse the output of mysqlbinlog and
replace the buggy statement with the correct one.
wangxu wrote:
> My mysql version is 5.0.16.
>
> My problem is similar to the bug.
>
> My sql mode is ANSI and TRADITIONAL
>
> If mysql can't do recovery with mysqlbinlog at my sql mode until the bug
> will be fixed?
>
> Follow is the information of my binlog.
>
> --
> /*!40019 SET @@session.max_insert_delayed_threads=0*/;
> /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
> # at 4
> #060119 13:55:30 server id 1 end_log_pos 98Start: binlog v 4, server v
> 5.0.16-standard-log created 060119 13:55:30
> # Warning: this binlog was not closed properly. Most probably mysqld crashed
> writing it.
> # at 98
> #060119 13:57:41 server id 1 end_log_pos 165 Query thread_id=4
> exec_time=0 error_code=0
> use wangxu;
> SET TIMESTAMP=1137650261;
> SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
> @@session.unique_checks=1;
> SET @@session.sql_mode=501481487;
> SET
> @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
> insert ht_detail
> values('79NK0006','79NK0006',1),('79NK0007','79NK0007',1),('79NK0008','79NK0008',1);
> # at 263
> #060119 13:57:41 server id 1 end_log_pos 290 Xid = 215
> COMMIT;
> # at 290
> #060119 14:08:30 server id 1 end_log_pos 445 Query thread_id=7
> exec_time=0 error_code=0
> SET TIMESTAMP=1137650910;
> SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
> SET @@session.sql_mode=524288;
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wangxu` /*!40100 DEFAULT CHARACTER
> SET latin1 */;
> # at 445
> #060119 14:08:30 server id 1 end_log_pos 542 Query thread_id=7
> exec_time=0 error_code=0
> SET TIMESTAMP=1137650910;
> DROP TABLE IF EXISTS `ht_detail`;
> # at 542
> #060119 14:08:40 server id 1 end_log_pos 639 Query thread_id=8
> exec_time=0 error_code=0
> SET TIMESTAMP=1137650920;
> DROP TABLE IF EXISTS `ht_detail`;
> # at 639
> #060119 14:23:13 server id 1 end_log_pos 733 Query thread_id=11
> exec_time=0 error_code=0
> SET TIMESTAMP=1137651793;
> SET @@session.foreign_key_checks=1, @@session.unique_checks=1;
> SET @@session.sql_mode=501481487;
> create table ht_header(a int);
> # at 733
> #060119 14:23:53 server id 1 end_log_pos 818 Query thread_id=12
> exec_time=0 error_code=0
> SET TIMESTAMP=1137651833;
> drop table ht_header;
> # at 818
> #060119 14:28:10 server id 1 end_log_pos 915 Query thread_id=15
> exec_time=0 error_code=0
> SET TIMESTAMP=1137652090;
> SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
> SET @@session.sql_mode=524288;
> DROP TABLE IF EXISTS `ht_detail`;
> # at 915
> #060119 14:29:44 server id 1 end_log_pos 998 Query thread_id=16
> exec_time=0 error_code=0
> use test;
> SET TIMESTAMP=1137652184;
> SET @@session.foreign_key_checks=1, @@session.unique_checks=1;
> SET @@session.sql_mode=501481487;
> drop table ht_detail;
> # at 998
> #060119 14:30:29 server id 1 end_log_pos 1160 Query thread_id=16
> exec_time=0 error_code=0
> SET TIMESTAMP=1137652229;
> create table ht_detail(hth varchar(30),sbh varchar(15),sbsl int,primary
> key(hth,sbh)) type = innodb;
> # at 1160
> #060119 14:54:50 server id 1 end_log_pos 1243 Query thread_id=20
> exec_time=0 error_code=0
> SET TIMESTAMP=1137653690;
> drop schema wangxu;
> # at 1243
> #060119 14:54:57 server id 1 end_log_pos 1328 Query thread_id=20
> exec_time=0 error_code=0
> SET TIMESTAMP=1137653697;
> create schema wangxu;
> # at 1328
> #060119 14:55:17 server id 1 end_log_pos 1422 Query thread_id=20
> exec_time=0 error_code=0
> use wangxu;
> SET TIMESTAMP=1137653717;
> create table ht_detail(a int);
> # at 1422
> #060119 17:10:13 server id 1 end_log_pos 1505 Query thread_id=24
> exec_time=0 error_code=0
> SET TIMESTAMP=1137661813;
> drop schema wangxu;
> # at 1505
> #060119 17:10:17 server id 1 end_log_pos 1590 Query thread_id=24
> exec_time=0 error_code=0
> SET TIMESTAMP=1137661817;
> create schema wangxu;
> # at 1590
> #060119 17:10:47 server id 1 end_log_pos 1687 Query thread_id=25
> exec_time=0 error_code=0
> SET TIMESTAMP=1137661847;
> SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
> SET @@session.sql_mode=524288;
> DROP TABLE IF EXISTS `ht_detail`;
> # at 1687
> #060119 17
Re: question about recovery with binlog
My mysql version is 5.0.16.
My problem is similar to the bug.
My sql mode is ANSI and TRADITIONAL
If mysql can't do recovery with mysqlbinlog at my sql mode until the bug will
be fixed?
Follow is the information of my binlog.
--
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#060119 13:55:30 server id 1 end_log_pos 98Start: binlog v 4, server v
5.0.16-standard-log created 060119 13:55:30
# Warning: this binlog was not closed properly. Most probably mysqld crashed
writing it.
# at 98
#060119 13:57:41 server id 1 end_log_pos 165 Query thread_id=4
exec_time=0 error_code=0
use wangxu;
SET TIMESTAMP=1137650261;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1;
SET @@session.sql_mode=501481487;
SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
insert ht_detail
values('79NK0006','79NK0006',1),('79NK0007','79NK0007',1),('79NK0008','79NK0008',1);
# at 263
#060119 13:57:41 server id 1 end_log_pos 290 Xid = 215
COMMIT;
# at 290
#060119 14:08:30 server id 1 end_log_pos 445 Query thread_id=7
exec_time=0 error_code=0
SET TIMESTAMP=1137650910;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
SET @@session.sql_mode=524288;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wangxu` /*!40100 DEFAULT CHARACTER
SET latin1 */;
# at 445
#060119 14:08:30 server id 1 end_log_pos 542 Query thread_id=7
exec_time=0 error_code=0
SET TIMESTAMP=1137650910;
DROP TABLE IF EXISTS `ht_detail`;
# at 542
#060119 14:08:40 server id 1 end_log_pos 639 Query thread_id=8
exec_time=0 error_code=0
SET TIMESTAMP=1137650920;
DROP TABLE IF EXISTS `ht_detail`;
# at 639
#060119 14:23:13 server id 1 end_log_pos 733 Query thread_id=11
exec_time=0 error_code=0
SET TIMESTAMP=1137651793;
SET @@session.foreign_key_checks=1, @@session.unique_checks=1;
SET @@session.sql_mode=501481487;
create table ht_header(a int);
# at 733
#060119 14:23:53 server id 1 end_log_pos 818 Query thread_id=12
exec_time=0 error_code=0
SET TIMESTAMP=1137651833;
drop table ht_header;
# at 818
#060119 14:28:10 server id 1 end_log_pos 915 Query thread_id=15
exec_time=0 error_code=0
SET TIMESTAMP=1137652090;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
SET @@session.sql_mode=524288;
DROP TABLE IF EXISTS `ht_detail`;
# at 915
#060119 14:29:44 server id 1 end_log_pos 998 Query thread_id=16
exec_time=0 error_code=0
use test;
SET TIMESTAMP=1137652184;
SET @@session.foreign_key_checks=1, @@session.unique_checks=1;
SET @@session.sql_mode=501481487;
drop table ht_detail;
# at 998
#060119 14:30:29 server id 1 end_log_pos 1160 Query thread_id=16
exec_time=0 error_code=0
SET TIMESTAMP=1137652229;
create table ht_detail(hth varchar(30),sbh varchar(15),sbsl int,primary
key(hth,sbh)) type = innodb;
# at 1160
#060119 14:54:50 server id 1 end_log_pos 1243 Query thread_id=20
exec_time=0 error_code=0
SET TIMESTAMP=1137653690;
drop schema wangxu;
# at 1243
#060119 14:54:57 server id 1 end_log_pos 1328 Query thread_id=20
exec_time=0 error_code=0
SET TIMESTAMP=1137653697;
create schema wangxu;
# at 1328
#060119 14:55:17 server id 1 end_log_pos 1422 Query thread_id=20
exec_time=0 error_code=0
use wangxu;
SET TIMESTAMP=1137653717;
create table ht_detail(a int);
# at 1422
#060119 17:10:13 server id 1 end_log_pos 1505 Query thread_id=24
exec_time=0 error_code=0
SET TIMESTAMP=1137661813;
drop schema wangxu;
# at 1505
#060119 17:10:17 server id 1 end_log_pos 1590 Query thread_id=24
exec_time=0 error_code=0
SET TIMESTAMP=1137661817;
create schema wangxu;
# at 1590
#060119 17:10:47 server id 1 end_log_pos 1687 Query thread_id=25
exec_time=0 error_code=0
SET TIMESTAMP=1137661847;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
SET @@session.sql_mode=524288;
DROP TABLE IF EXISTS `ht_detail`;
# at 1687
#060119 17:10:47 server id 1 end_log_pos 1959 Query thread_id=25
exec_time=0 error_code=0
SET TIMESTAMP=1137661847;
CREATE TABLE `ht_detail` (
`hth` varchar(30) NOT NULL default '',
`sbh` varchar(15) NOT NULL default '',
`sbsl` int(11) default NULL,
PRIMARY KEY (`hth`,`sbh`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# at 1959
#060119 17:10:47 server id 1 end_log_pos 2072 Query thread_id=25
exec_time=0 error_code=0
SET TIMESTAMP=1137661847;
/*!4 ALTER TABLE `ht_detail` DISABLE KEYS */;
# at 2072
#060119 17:10:47 server id 1 end_log_pos 188 Query thread_id=25
exec_time=0 error_code=0
SET TIMESTAMP=1137661847;
INSERT INTO `ht_detail` VALUES
('79NK0001/0003','79NK0001
Re: question about recovery with binlog
Hello. Are you sure that the bug is thrown by mysqlbinlog? May be you're getting this while importing the output produced by mysqlbinlog? Have a look here: http://bugs.mysql.com/bug.php?id=13897 Check that you're using the same versions of mysql client and mysqlbinlog. wangxu wrote: > Mysqlbinlog throw out a error "ERROR 1231 (42000) at line 10: Variable > 'sql_mode' can't be set to the value of '501481487'" when i recovery a > binlog. > > What can i do? -- 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]
question about recovery with binlog
Mysqlbinlog throw out a error "ERROR 1231 (42000) at line 10: Variable 'sql_mode' can't be set to the value of '501481487'" when i recovery a binlog. What can i do?
Re: a question of specifying Times for Recovery
Hello. >If "default-character-set" can't set in "[client]"? The comprehensive explanations you will find at: http://bugs.mysql.com/bug.php?id=11673 "wangxu" <[EMAIL PROTECTED]> wrote: >Follow is a part of my my.ini. >--- >[client] > >port=3306 > >default-character-set=utf8 >--- >If "default-character-set" can't set in "[client]"? > > >- Original Message --- -- 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: a question of specifying Times for Recovery
Follow is a part of my my.ini. --- [client] port=3306 default-character-set=utf8 --- If "default-character-set" can't set in "[client]"? - Original Message - From: "sheeri kritzer" <[EMAIL PROTECTED]> To: "wangxu" <[EMAIL PROTECTED]> Cc: Sent: Friday, November 18, 2005 5:44 AM Subject: Re: a question of specifying Times for Recovery What does your my.cnf configuration for mysql say? Check out: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html I think you want "character_set" as your variable, not "default_character_set". Just my guess, not seeing any of your config files. -Sheeri On 11/16/05, wangxu <[EMAIL PROTECTED]> wrote: > I specifying Times for Recovery with mysqlbinlog. > > Follow is my command: > > mysqlbinlog --database=menagerie --stop-data="2005-11-14 9:22:01" C:\Program > Files\MySQL\MySQL Server 5.0\data\1.01 | mysql -uroot -p11 menagerie > > Mysql server throw a exception: > > mysqlbinlog: unknown variable 'default-character-set=utf8' > > please help me. > thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a question of specifying Times for Recovery
What does your my.cnf configuration for mysql say? Check out: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html I think you want "character_set" as your variable, not "default_character_set". Just my guess, not seeing any of your config files. -Sheeri On 11/16/05, wangxu <[EMAIL PROTECTED]> wrote: > I specifying Times for Recovery with mysqlbinlog. > > Follow is my command: > > mysqlbinlog --database=menagerie --stop-data="2005-11-14 9:22:01" C:\Program > Files\MySQL\MySQL Server 5.0\data\1.01 | mysql -uroot -p11 menagerie > > Mysql server throw a exception: > > mysqlbinlog: unknown variable 'default-character-set=utf8' > > please help me. > thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM index file recovery/writing by hand
Hello. > documentation available somewhere (except for sources) where one can > find how MYI files are organized? I don't think other sources except internals.texi (located in the Doc directory) are publicly available. Sergei Rodionov <[EMAIL PROTECTED]> wrote: > Hi all. > > I am desperately seeking advice on quite unusual matter with MyISAM tables. > We have application with very large volume of data being posted into > database at once, and in order to speed process up, > an attempt to write MYD files by hand has been attempted. However, MySQL > wont load such table properly, unless MYI (index) file > is written accordingly. Present recovery tools (myisamchk) are > normalizing database by index, so I cant just put there empty MYI file and > ask for recovery (then I will get empty table, basically). Is there any > recovery tool that restores MYI file by MYD? Or is there any > documentation available somewhere (except for sources) where one can > find how MYI files are organized? > > Thanks in advance > Sergei > > -- 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]
MyISAM index file recovery/writing by hand
Hi all. I am desperately seeking advice on quite unusual matter with MyISAM tables. We have application with very large volume of data being posted into database at once, and in order to speed process up, an attempt to write MYD files by hand has been attempted. However, MySQL wont load such table properly, unless MYI (index) file is written accordingly. Present recovery tools (myisamchk) are normalizing database by index, so I cant just put there empty MYI file and ask for recovery (then I will get empty table, basically). Is there any recovery tool that restores MYI file by MYD? Or is there any documentation available somewhere (except for sources) where one can find how MYI files are organized? Thanks in advance Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recovery question
Gleb Paharenko wrote: > Hello. > > REPAIR TABLE ... USE_FRM helps in difficult cases. See: > http://dev.mysql.com/doc/mysql/en/repair-table.html > Thanks Gleb. I'd forgotten about that option. To others who try the same thing - make sure you have enough space in your TMPDIR or set TMPDIR/--tmpdir to a place where you have sufficient space. I started the REPAIR, which ran for a while, then stopped and appeared to be idling. It took me a few hours before I checked the mysqld.log and found out that it had run out of space in /tmp and was waiting for some to be cleared up. -- /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recovery question
try REPAIR TABLE 'tablename' Gleb Paharenko wrote: >Hello. > > > >REPAIR TABLE ... USE_FRM helps in difficult cases. See: > > http://dev.mysql.com/doc/mysql/en/repair-table.html > > > > > > > > > > > >Per Jessen <[EMAIL PROTECTED]> wrote: > > > >>All, >> >> > > > >>I've got a table with about 25mill rows that was victim of a crash recently. >>(power-failure). >> >> > > > >>I've been trying to recover it, but I'm not making much progress. >> >> > > > > > > >>>From the most recent attempts: >> >> > > > > > > >>myisamchk --safe-recover --force >> >> > > > >>- recovering (with keycache) MyISAM-table '' >> >> > > > >>Data records: 21622679 >> >> > > > >>4988000 >> >> > > > >>7256000 >> >> > > > >>10627000 >> >> > > > >>myisamchk: error: 126 for record at pos 1589881104 >> >> > > > >>MyISAM-table '' is not fixed because of errors >> >> > > > > > > >>myisamchk -r --force --tmpdir=/data2/tmp >> >> > > > >>- recovering (with sort) MyISAM-table '' >> >> > > > >>Data records: 12876899 >> >> > > > >>- Fixing index 1 >> >> > > > >>- Fixing index 2 >> >> > > > >>- Fixing index 3 >> >> > > > >>Key 3 - Found wrong stored record at 0 >> >> > > > >>MyISAM-table '' is not fixed because of errors >> >> > > > > > > > > > >>Where do I go from here? I've got a backup of the table, but I'm not sure >>what sort of state it >> >> > > > >>is in. >> >> > > > > > > > > > > >
Re: recovery question
Hello. REPAIR TABLE ... USE_FRM helps in difficult cases. See: http://dev.mysql.com/doc/mysql/en/repair-table.html Per Jessen <[EMAIL PROTECTED]> wrote: > All, > I've got a table with about 25mill rows that was victim of a crash recently. > (power-failure). > I've been trying to recover it, but I'm not making much progress. > >>From the most recent attempts: > > myisamchk --safe-recover --force > - recovering (with keycache) MyISAM-table '' > Data records: 21622679 > 4988000 > 7256000 > 10627000 > myisamchk: error: 126 for record at pos 1589881104 > MyISAM-table '' is not fixed because of errors > > myisamchk -r --force --tmpdir=/data2/tmp > - recovering (with sort) MyISAM-table '' > Data records: 12876899 > - Fixing index 1 > - Fixing index 2 > - Fixing index 3 > Key 3 - Found wrong stored record at 0 > MyISAM-table '' is not fixed because of errors > > > Where do I go from here? I've got a backup of the table, but I'm not sure > what sort of state it > is in. > > -- 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: recovery question
Per Jessen wrote: > I've got a backup of the table, but I'm not sure what sort of state > it is in. Correction - no backup is available. This table has got to be recoverable. -- /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recovery question
All, I've got a table with about 25mill rows that was victim of a crash recently. (power-failure). I've been trying to recover it, but I'm not making much progress. >From the most recent attempts: myisamchk --safe-recover --force - recovering (with keycache) MyISAM-table '' Data records: 21622679 4988000 7256000 10627000 myisamchk: error: 126 for record at pos 1589881104 MyISAM-table '' is not fixed because of errors myisamchk -r --force --tmpdir=/data2/tmp - recovering (with sort) MyISAM-table '' Data records: 12876899 - Fixing index 1 - Fixing index 2 - Fixing index 3 Key 3 - Found wrong stored record at 0 MyISAM-table '' is not fixed because of errors Where do I go from here? I've got a backup of the table, but I'm not sure what sort of state it is in. -- /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql cluster : point-in-time recovery
We have a mysql cluster. I know we can make backup in the management server of the mysql cluster with the command "start backup". After that, How we can make a point-in-time recovery ? Example: - I did a backup at 7:00am. - at 11:00am I have a crash. - I want to restore all my data until 10:59am. - So, I restore my cluster with the ndb_restore functionality. - But after that ?, How I can restore the transactions occured until 10:59am ? - Do I must use log-bin parameter like in the mysql standard ? If yes, Do I must apply it on one of my storage node and all will be replicated to the others ? Thanks ! David Marois DBA <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
Re: recovery of a very large table?
Not a bad suggestion... but when I try it, I get the following output: Checking MyISAM file: theTable Data records: 22906970 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed and last repair failed - check file-size myisamchk: error: Size of indexfile is: 2049552384Should be: 19229444096- check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 myisamchk: error: Can't read indexpage from filepos: -1 - check records and index references myisamchk: error: Record at: 0 Can't find key for index: 1 MyISAM-table 'theTable' is corrupted Fix it using switch "-r" or "-o" Renato Golin wrote: On Wednesday 06 April 2005 20:05, jon wrote: Normal recovery seems to grab 490 rows... but, originally there were some 22 million rows in there. Seems your data file was corruped too not only the indexes. and probably broke when updating the 491st registry... try use myisamchk -e -e, --extend-check Try to recover every possible row from the data file Normally this will also find a lot of garbage rows; Don't use this option if you are not totally desperate. it could take a very long time to run also... be warned! ;) --rengolin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recovery of a very large table?
On Wednesday 06 April 2005 20:05, jon wrote: > Normal recovery seems to grab 490 rows... but, originally there were > some 22 million rows in there. Seems your data file was corruped too not only the indexes. and probably broke when updating the 491st registry... try use myisamchk -e -e, --extend-check Try to recover every possible row from the data file Normally this will also find a lot of garbage rows; Don't use this option if you are not totally desperate. it could take a very long time to run also... be warned! ;) --rengolin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recovery of a very large table?
Hey folks... While we weren't paying attention, one of the tables we were logging to got big. Really big... like over 2 gigs... and then the server crashed and the data became corrupt. 'Course, we'd like that data back... Normal recovery seems to grab 490 rows... but, originally there were some 22 million rows in there. So far, I've tried: myisamchk -f tableName myisamchk -o tableName myisamchk -unpack tableName Same result, every time. Weird, eh? Some corruption is no problem. (We'll take what we can get.) I believe that the table was packed up at some point, but I'm not sure. So... what are my options here? Machine is linux - using ubuntu on the recovery box. -- jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB recovery
Thanks, That was pretty easy... And, thank you mysql! ;-) Yves On Fri, 28 Jan 2005 08:14:37 -0600, Tom Crimmins <[EMAIL PROTECTED]> wrote: > [snip] > > This box recently crashed as a result of a power outage (possible surge, my > surge protector may have failed) > The box doesn't boot up, but the HD is ok... all my docs are there.. > So, I'm wondering if it's somehow possible to get the MySQL 3 files and > recreate them on another box we have here that runs MySQL 4.0. > Is this possible? > It wouldn't be as simple as getting the files from the dead box and putting > them in the new box, would it? > > > [/snip] > > If these were myisam tables, assuming the files didn't get badly damaged, > you should be able to copy the directory for each database you need to > recover into the mysql datadir on the new host. You may have to use > myisamchk to repair the indexes. > > http://dev.mysql.com/doc/mysql/en/myisamchk-syntax.html > > You will want to do all of this with mysqld stopped. > > --- > Tom Crimmins > Interface Specialist > Pottawattamie County, Iowa > -- Yves Arsenault -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB recovery
[snip] This box recently crashed as a result of a power outage (possible surge, my surge protector may have failed) The box doesn't boot up, but the HD is ok... all my docs are there.. So, I'm wondering if it's somehow possible to get the MySQL 3 files and recreate them on another box we have here that runs MySQL 4.0. Is this possible? It wouldn't be as simple as getting the files from the dead box and putting them in the new box, would it? [/snip] If these were myisam tables, assuming the files didn't get badly damaged, you should be able to copy the directory for each database you need to recover into the mysql datadir on the new host. You may have to use myisamchk to repair the indexes. http://dev.mysql.com/doc/mysql/en/myisamchk-syntax.html You will want to do all of this with mysqld stopped. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB recovery
Hello, I have a server here that ran MySQL 3.23.x This box recently crashed as a result of a power outage (possible surge, my surge protector may have failed) The box doesn't boot up, but the HD is ok... all my docs are there.. So, I'm wondering if it's somehow possible to get the MySQL 3 files and recreate them on another box we have here that runs MySQL 4.0. Is this possible? It wouldn't be as simple as getting the files from the dead box and putting them in the new box, would it? Thanks, -- Yves Arsenault -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql db recovery
i use suse 9.0 and mysql 4.0 , i drop a db is very important for me , hoe to i can recover it ?do you have any tool for this function . this is vital for me . please help me . -- E.Norouzi Site Administrative Of P.S.P.ÂCo, Ltd. Tel:+98-21-2865439/41 Fax:+98-21-2865120 International Fax:+1-603-761-5357 P.O.Box:16315-885 #12,4th Floor, No.939 ,Shariati Ave.Tehran 1661714377,ÂIran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should I trust my data after an InnoDB recovery?
Jeremy Zawodny wrote: My question is: should I trust my data now? Yes. InnoDB is fully ACID compliant. So anyway, should I bother with a restore? What's the chance of having data corrupted / missing after a power 'failure' and recovery as above? The only "missing" data should be uncommitted transactions unless you've changed InnoDB's default flushing frequency. Cool. Thanks! -- signature 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: Should I trust my data after an InnoDB recovery?
On Thu, May 06, 2004 at 09:55:30AM +1000, Daniel Kasak wrote: > Hi all. > > My boss just pulled the power on our MySQL server. > Yes, I've already thanked him. > > It's a 4.0.18 server, with MyISAM tables and InnoDB tables, running on a > 2.6.5 kernel and XFS filesystem. > > The XFS recovery proceeded without any complaints. > The InnoDB recovery also seemed to go smoothly ( great work by the way ): > > 040506 9:38:41 InnoDB: Database was not shut down normally. > InnoDB: Starting recovery from log files... > InnoDB: Starting log scan based on checkpoint at > InnoDB: log sequence number 0 1032429395 > InnoDB: Doing recovery: scanned up to log sequence number 0 1032430197 > 040506 9:38:42 InnoDB: Starting an apply batch of log records to the > database... > InnoDB: Progress in percents: 14 15 16 17 18 19 20 21 22 23 24 25 26 27 > 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 > 52 53 > 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 > 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 > InnoDB: Apply batch completed > InnoDB: Last MySQL binlog file position 0 139443, file name > ./screamer-bin.210 > 040506 9:38:42 InnoDB: Flushing modified pages from the buffer pool... > 040506 9:38:42 InnoDB: Started > > I've done a 'myisamchk' on all the MyISAM tables. They were all OK. > > My question is: should I trust my data now? Yes. InnoDB is fully ACID compliant. > So anyway, should I bother with a restore? What's the chance of having > data corrupted / missing after a power 'failure' and recovery as above? The only "missing" data should be uncommitted transactions unless you've changed InnoDB's default flushing frequency. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Should I trust my data after an InnoDB recovery?
Hi all. My boss just pulled the power on our MySQL server. Yes, I've already thanked him. It's a 4.0.18 server, with MyISAM tables and InnoDB tables, running on a 2.6.5 kernel and XFS filesystem. The XFS recovery proceeded without any complaints. The InnoDB recovery also seemed to go smoothly ( great work by the way ): 040506 9:38:41 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 1032429395 InnoDB: Doing recovery: scanned up to log sequence number 0 1032430197 040506 9:38:42 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 139443, file name ./screamer-bin.210 040506 9:38:42 InnoDB: Flushing modified pages from the buffer pool... 040506 9:38:42 InnoDB: Started I've done a 'myisamchk' on all the MyISAM tables. They were all OK. My question is: should I trust my data now? I have plain-text logs and nightly backups, so I can ( and have before ) drop the whole thing tonight after work and import from last night's backup, then run the transaction logs through. However this is a lengthy and painful process, largely due to us having a lot of 'create temporary table' statements without matching 'drop table' statements - so I have to manually edit the log and delete this stuff ( it's only used for selects anyway ). I've been meaning to learn up on the binary logs, but never gotten around to it. So anyway, should I bother with a restore? What's the chance of having data corrupted / missing after a power 'failure' and recovery as above? Thanks! Dan -- signature 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: Database recovery
> > > > > Hi, > > > > > > I have recently had a system crash that required the > > > installation of a new hard drive. I have access to the > > > files on the old hard drive, on which is a database I > > > need to recover. > > > > > > I am running MySql 3.23.37 and all the tables in the > > > database to be recovered are MyISAM. > > > > > > How can I recover the old database onto a new > > > server with only file access? The documentation > > > suggests to me that I need to copy all the *.frm, > > > *.MYD, and *.MYI files. If this is the case, where > > > should I copy them to on the new server? > > > > Each table in MySQL is represented by a MYD, MYI, and frm file. These are > collected in directories, each of which represents a > > database. So copy the files, along with the directories they were in > (which represent the databases), except for the "mysql" > > database, into the MySQL data directory. For example, if you used the RPM > install, this directory will be /var/lib/mysql, or > > wherever you extracted the binary install to if you've used the binary > installation. Then restart MySQL, and it will pick up the > > databases. > > > > > > Hi, > > Thanks for the info. I now have all the frm MYD and MYI files under a new > directory within MySQL. > > How do I restart the service manually? Would it be: > > /etc/rc.d/init.d/mysql restart > > I am using Apache web server on a Cobalt RAQ. > > Thanks > No matter, its all done. Thank you very much for your help. Regards Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database recovery
> > Hi, > > > > I have recently had a system crash that required the > > installation of a new hard drive. I have access to the > > files on the old hard drive, on which is a database I > > need to recover. > > > > I am running MySql 3.23.37 and all the tables in the > > database to be recovered are MyISAM. > > > > How can I recover the old database onto a new > > server with only file access? The documentation > > suggests to me that I need to copy all the *.frm, > > *.MYD, and *.MYI files. If this is the case, where > > should I copy them to on the new server? > > Each table in MySQL is represented by a MYD, MYI, and frm file. These are collected in directories, each of which represents a > database. So copy the files, along with the directories they were in (which represent the databases), except for the "mysql" > database, into the MySQL data directory. For example, if you used the RPM install, this directory will be /var/lib/mysql, or > wherever you extracted the binary install to if you've used the binary installation. Then restart MySQL, and it will pick up the > databases. > > Hi, Thanks for the info. I now have all the frm MYD and MYI files under a new directory within MySQL. How do I restart the service manually? Would it be: /etc/rc.d/init.d/mysql restart I am using Apache web server on a Cobalt RAQ. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database recovery
"Andy Hall" <[EMAIL PROTECTED]> wrote on 07/04/2004 09:50:19: > Hi, > > I have recently had a system crash that required the installation of > a new hard drive. I have access to the files on the old hard drive, > on which is a database I need to recover. > > I am running MySql 3.23.37 and all the tables in the database to be > recovered are MyISAM. > > How can I recover the old database onto a new server with only file > access? The documentation suggests to me that I need to copy all the > *.frm, *.MYD, and *.MYI files. If this is the case, where should I > copy them to on the new server? It would help for this sort of question if you said what OS you are using: the answer will vary with OS. Normally, you copy files from. to the /data/ directory: on most Windows installations, this will be c:\mysql\data\. If you cannot find this, searching for files with the extension .MYI on the old disk might well find them for you. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database recovery
> Hi, > > I have recently had a system crash that required the > installation of a new hard drive. I have access to the > files on the old hard drive, on which is a database I > need to recover. > > I am running MySql 3.23.37 and all the tables in the > database to be recovered are MyISAM. > > How can I recover the old database onto a new > server with only file access? The documentation > suggests to me that I need to copy all the *.frm, > *.MYD, and *.MYI files. If this is the case, where > should I copy them to on the new server? Each table in MySQL is represented by a MYD, MYI, and frm file. These are collected in directories, each of which represents a database. So copy the files, along with the directories they were in (which represent the databases), except for the "mysql" database, into the MySQL data directory. For example, if you used the RPM install, this directory will be /var/lib/mysql, or wherever you extracted the binary install to if you've used the binary installation. Then restart MySQL, and it will pick up the databases. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database recovery
Hi, I have recently had a system crash that required the installation of a new hard drive. I have access to the files on the old hard drive, on which is a database I need to recover. I am running MySql 3.23.37 and all the tables in the database to be recovered are MyISAM. How can I recover the old database onto a new server with only file access? The documentation suggests to me that I need to copy all the *.frm, *.MYD, and *.MYI files. If this is the case, where should I copy them to on the new server? The technical director is away and I am in charge of this recovery... I dont want to get it wrong, so *any* help is greatly appreciated. Andy Hall.
Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
Hi, This only delays the agony. I have done that (using bzip2), but eventually this will not work. I would rather engineer a solution that "will just work" and will scale properly (given infrastructure support ie: network/disk). Thanks, Steve Williams William R. Mussatto wrote: How about if you dumped to a compressor and stored the result? Steve Williams said: Hi, The problem with doing a myqldump to a file (via cron) is that at some point it will hit the filesize limitiations. By streaming it over the network, that problem is avoided on both ends of the pipe. The idea of doing the "scp" of the mysql data directory is not a bad one, but would require the shutting down of the database (production). The shutting down of the disaster recovery one isn't a problem... I may resort to it, but I'd prefer to just figure out which timeout is causing the problem. Thanks, Steve Williams -Original Message- From: dan orlic [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 3:52 PM To: Steve Williams Cc: [EMAIL PROTECTED] Subject: Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery) perhaps you would get a better response from doing scp... which runs over ssh... or doing the mysqldump in a cron job, so it will already be complete for transport by ssh. I still think scp is the more proper way to go. dan orlic Steve Williams wrote: Hi, We have a (pre-existing) disaster recovery/backup script that uses = mysqldump, ssh, mysql to backup an existing database. One of the tables = is rather large (1 Gig or so), and the time that it takes to "DROP = TABLE" on an already loaded recover server causes a timeout. I have = confirmed tested by creating an empty database on the recovery server & = the mysqldump loads fine. The second time I run it, it gets a timeout = error. The basic logic is: mysqldump ... somedatabase | ssh [EMAIL PROTECTED] "mysql ..." This technique is because only the SSH port is open to the recovery = host. mysqldump: Error 2013: Lost connection to MySQL server during query when = dumping table 'Item' at row: 1539 real 3:10.4 user0.0 sys 0.0 I just do not know which timeout is causing the problem. mysql> show variables like '%timeout%' -> ; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | wait_timeout | 28800 | +--+---+ 8 rows in set (0.06 sec) =09 Or is it a timeout associated with mysqldump?? Can anyone shed some light?? Thanks, Steve Williams -- 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: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
well then. perhaps you can add/modify the sshd.conf file, is there a default timout there? that would be my next shot. dan William R. Mussatto wrote: How about if you dumped to a compressor and stored the result? Steve Williams said: Hi, The problem with doing a myqldump to a file (via cron) is that at some point it will hit the filesize limitiations. By streaming it over the network, that problem is avoided on both ends of the pipe. The idea of doing the "scp" of the mysql data directory is not a bad one, but would require the shutting down of the database (production). The shutting down of the disaster recovery one isn't a problem... I may resort to it, but I'd prefer to just figure out which timeout is causing the problem. Thanks, Steve Williams -Original Message- From: dan orlic [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 3:52 PM To: Steve Williams Cc: [EMAIL PROTECTED] Subject: Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery) perhaps you would get a better response from doing scp... which runs over ssh... or doing the mysqldump in a cron job, so it will already be complete for transport by ssh. I still think scp is the more proper way to go. dan orlic Steve Williams wrote: Hi, We have a (pre-existing) disaster recovery/backup script that uses = mysqldump, ssh, mysql to backup an existing database. One of the tables = is rather large (1 Gig or so), and the time that it takes to "DROP = TABLE" on an already loaded recover server causes a timeout. I have = confirmed tested by creating an empty database on the recovery server & = the mysqldump loads fine. The second time I run it, it gets a timeout = error. The basic logic is: mysqldump ... somedatabase | ssh [EMAIL PROTECTED] "mysql ..." This technique is because only the SSH port is open to the recovery = host. mysqldump: Error 2013: Lost connection to MySQL server during query when = dumping table 'Item' at row: 1539 real 3:10.4 user0.0 sys 0.0 I just do not know which timeout is causing the problem. mysql> show variables like '%timeout%' -> ; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | wait_timeout | 28800 | +--+---+ 8 rows in set (0.06 sec) =09 Or is it a timeout associated with mysqldump?? Can anyone shed some light?? Thanks, Steve Williams -- 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: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
How about if you dumped to a compressor and stored the result? Steve Williams said: > Hi, > > The problem with doing a myqldump to a file (via cron) is that at some > point it will hit the filesize limitiations. By streaming it over the > network, that problem is avoided on both ends of the pipe. > > The idea of doing the "scp" of the mysql data directory is not a bad > one, but would require the shutting down of the database (production). > The shutting down of the disaster recovery one isn't a problem... I may > resort to it, but I'd prefer to just figure out which timeout is causing > the problem. > > Thanks, > Steve Williams > > -Original Message- > From: dan orlic [mailto:[EMAIL PROTECTED] > Sent: Friday, February 27, 2004 3:52 PM > To: Steve Williams > Cc: [EMAIL PROTECTED] > Subject: Re: InnoDB, mysqldump/mysql timeout dropping table (disaster > recovery) > > > perhaps you would get a better response from doing scp... which runs > over ssh... or doing the mysqldump in a cron job, so it will > already be complete for transport by ssh. I still think scp is the more > proper way to go. > > dan orlic > > Steve Williams wrote: > >>Hi, >> >>We have a (pre-existing) disaster recovery/backup script that uses = >> mysqldump, ssh, mysql to backup an existing database. One of the >> tables = is rather large (1 Gig or so), and the time that it takes to >> "DROP = TABLE" on an already loaded recover server causes a timeout. >> I have = confirmed tested by creating an empty database on the recovery >> server & = the mysqldump loads fine. The second time I run it, it gets >> a timeout = error. >> >>The basic logic is: >> >>mysqldump ... somedatabase | ssh [EMAIL PROTECTED] "mysql ..." >> >>This technique is because only the SSH port is open to the recovery = >> host. >> >>mysqldump: Error 2013: Lost connection to MySQL server during query >> when = dumping table 'Item' at row: 1539 >> >>real 3:10.4 >>user0.0 >>sys 0.0 >> >>I just do not know which timeout is causing the problem. >> >> >> >> mysql> show variables like '%timeout%' >> -> ; >> +--+---+ >> | Variable_name| Value | >> +--+---+ >> | connect_timeout | 5 | >> | delayed_insert_timeout | 300 | >> | innodb_lock_wait_timeout | 50| >> | interactive_timeout | 28800 | >> | net_read_timeout | 30| >> | net_write_timeout| 60| >> | slave_net_timeout| 3600 | >> | wait_timeout | 28800 | >> +--+---+ >> 8 rows in set (0.06 sec) >>=09 >> >>Or is it a timeout associated with mysqldump?? >> >>Can anyone shed some light?? >> >>Thanks, >>Steve Williams >> >> >> >> > > > > -- > 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: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
Hi, The problem with doing a myqldump to a file (via cron) is that at some point it will hit the filesize limitiations. By streaming it over the network, that problem is avoided on both ends of the pipe. The idea of doing the "scp" of the mysql data directory is not a bad one, but would require the shutting down of the database (production). The shutting down of the disaster recovery one isn't a problem... I may resort to it, but I'd prefer to just figure out which timeout is causing the problem. Thanks, Steve Williams -Original Message- From: dan orlic [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 3:52 PM To: Steve Williams Cc: [EMAIL PROTECTED] Subject: Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery) perhaps you would get a better response from doing scp... which runs over ssh... or doing the mysqldump in a cron job, so it will already be complete for transport by ssh. I still think scp is the more proper way to go. dan orlic Steve Williams wrote: >Hi, > >We have a (pre-existing) disaster recovery/backup script that uses = >mysqldump, ssh, mysql to backup an existing database. One of the tables = >is rather large (1 Gig or so), and the time that it takes to "DROP = >TABLE" on an already loaded recover server causes a timeout. I have = >confirmed tested by creating an empty database on the recovery server & = >the mysqldump loads fine. The second time I run it, it gets a timeout = >error. > >The basic logic is: > >mysqldump ... somedatabase | ssh [EMAIL PROTECTED] "mysql ..." > >This technique is because only the SSH port is open to the recovery = >host. > >mysqldump: Error 2013: Lost connection to MySQL server during query when = >dumping table 'Item' at row: 1539 > >real 3:10.4 >user0.0 >sys 0.0 > >I just do not know which timeout is causing the problem. > > > > mysql> show variables like '%timeout%' > -> ; > +--+---+ > | Variable_name| Value | > +--+---+ > | connect_timeout | 5 | > | delayed_insert_timeout | 300 | > | innodb_lock_wait_timeout | 50| > | interactive_timeout | 28800 | > | net_read_timeout | 30| > | net_write_timeout| 60| > | slave_net_timeout| 3600 | > | wait_timeout | 28800 | > +--+---+ > 8 rows in set (0.06 sec) >=09 > >Or is it a timeout associated with mysqldump?? > >Can anyone shed some light?? > >Thanks, >Steve Williams > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
perhaps you would get a better response from doing scp... which runs over ssh... or doing the mysqldump in a cron job, so it will already be complete for transport by ssh. I still think scp is the more proper way to go. dan orlic Steve Williams wrote: Hi, We have a (pre-existing) disaster recovery/backup script that uses = mysqldump, ssh, mysql to backup an existing database. One of the tables = is rather large (1 Gig or so), and the time that it takes to "DROP = TABLE" on an already loaded recover server causes a timeout. I have = confirmed tested by creating an empty database on the recovery server & = the mysqldump loads fine. The second time I run it, it gets a timeout = error. The basic logic is: mysqldump ... somedatabase | ssh [EMAIL PROTECTED] "mysql ..." This technique is because only the SSH port is open to the recovery = host. mysqldump: Error 2013: Lost connection to MySQL server during query when = dumping table 'Item' at row: 1539 real 3:10.4 user0.0 sys 0.0 I just do not know which timeout is causing the problem. mysql> show variables like '%timeout%' -> ; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | wait_timeout | 28800 | +--+---+ 8 rows in set (0.06 sec) =09 Or is it a timeout associated with mysqldump?? Can anyone shed some light?? Thanks, Steve Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
Hi, We have a (pre-existing) disaster recovery/backup script that uses = mysqldump, ssh, mysql to backup an existing database. One of the tables = is rather large (1 Gig or so), and the time that it takes to "DROP = TABLE" on an already loaded recover server causes a timeout. I have = confirmed tested by creating an empty database on the recovery server & = the mysqldump loads fine. The second time I run it, it gets a timeout = error. The basic logic is: mysqldump ... somedatabase | ssh [EMAIL PROTECTED] "mysql ..." This technique is because only the SSH port is open to the recovery = host. mysqldump: Error 2013: Lost connection to MySQL server during query when = dumping table 'Item' at row: 1539 real 3:10.4 user0.0 sys 0.0 I just do not know which timeout is causing the problem. mysql> show variables like '%timeout%' -> ; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | wait_timeout | 28800 | +--+---+ 8 rows in set (0.06 sec) =09 Or is it a timeout associated with mysqldump?? Can anyone shed some light?? Thanks, Steve Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb recovery problems
Hi, I have been backing up via the dubious method of copying the database data folder onto another machine where it is properly backed up onto DLT. (yes, I know I should have used mysqldump!) Recovering some tables today I copied the files back into their position (including the ibdata1 file and the other id_* files) but the data is not recovered the table. There are no errors being given though. It is just as if I haven't recovered the file - the data is still missing The ibdata1 file and all the other id_* files from backup appear to be the same size as they are after the data had been deleted Any help greatly appreciated! Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql disaster recovery
I have had some nasty NFS experiences (especially with the server from which you're mounting the data going down). In my experience (and I'm echoing previous responses now) replication is better. Cheers, Andrew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday 03 December 2003 22:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: mysql disaster recovery This information may not be current, but I seem to remember hearing some really nasty stories about people putting MySQL data dirs on NFS exports. I would research the appropriate documentation before attempting such a configuration. -Original Message- From: mysql-digest-help [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 5:10 PM To: halla3; mysql Cc: John.Griffin Subject: RE: mysql disaster recovery Hi Andrew, I am not a Guru. I would suggest that you look at MySQL's excellent replication facility rather than NFS mount a drive. Having your data on an NFS mounted drive will significantly degrade the performance of your database. Replication will not. John Griffin -Original Message- From: Andrew Hall [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 4:51 PM To: [EMAIL PROTECTED] Subject: mysql disaster recovery Greetings Gurus, I have a mysql server that I need to create a disaster recovery system for. What I am planning on doing is putting the data dir on a NFS mounted directory so that I can start mysql on either of two servers in case one dies. The inbound connections would be load balanced in a fail over scenario, so the IP that clients will connect to will be on the load balancer. I'm wondering if there is anything already developed that would test mysql on the primary server, and if its not functioning, kill any remaining mysql processes if necessary, and start it on the secondary. This logic seems to be the biggest problem. Any suggestions, or other methodologies to implement this would be welcome. Thank you for your time in advance, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql disaster recovery
This information may not be current, but I seem to remember hearing some really nasty stories about people putting MySQL data dirs on NFS exports. I would research the appropriate documentation before attempting such a configuration. -Original Message- From: mysql-digest-help [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 5:10 PM To: halla3; mysql Cc: John.Griffin Subject: RE: mysql disaster recovery Hi Andrew, I am not a Guru. I would suggest that you look at MySQL's excellent replication facility rather than NFS mount a drive. Having your data on an NFS mounted drive will significantly degrade the performance of your database. Replication will not. John Griffin -Original Message- From: Andrew Hall [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 4:51 PM To: [EMAIL PROTECTED] Subject: mysql disaster recovery Greetings Gurus, I have a mysql server that I need to create a disaster recovery system for. What I am planning on doing is putting the data dir on a NFS mounted directory so that I can start mysql on either of two servers in case one dies. The inbound connections would be load balanced in a fail over scenario, so the IP that clients will connect to will be on the load balancer. I'm wondering if there is anything already developed that would test mysql on the primary server, and if its not functioning, kill any remaining mysql processes if necessary, and start it on the secondary. This logic seems to be the biggest problem. Any suggestions, or other methodologies to implement this would be welcome. Thank you for your time in advance, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql disaster recovery
Andrew, Andrew Hall wrote: > > John, > > Thank you for your reply. I have read the replication chapter and this > seems to be easier, but I do have a few questions. > > 1. I am using InnoDB tables, at least there are innodb argument to > mysqld in the start script, so should I use mysqldump instead of tar-ing > the data dir to create the baseline for the slave? I believe thats what > the docs are stating, but I wanted to be certain. You'll only be using innodb tables if you specified that table type when you created each table or if you have " default-table-type=innodb" in a my.cnf file somewhere. You would also have to create the innodb tablespace and log files before you could create the tables. You can copy the innodb files to a slave, but the my.cnf files on both machines should match. > 2. I want to have a failover scenario here, so what would the impact be > on the mysql db if I go master->slave->master? > I wouldn't recommend this. We had a instance a couple of weeks ago where the master db was having disk issues and when we had to repair some tables. When we did, we lost information in the master but luckily the slaves had replicated the data so we were able to restore that info back. > 3. What happens if the master fails, dies, and goes offline. Must I > change all my apps to connect to the IP of the slave for connections to > work? I am unclear if replication will provide failover capabilities. > For all of our "services", we run "floating ip addresses". That way if something like mysql or dns crashes, we can automatically drop the ip on the main machine and bring it up on the backup without being concerned about the server's actual ip address. Searching google for pirinna and heartbeat should help you with the fail-over part. > 4. What is necessary to bring the master back up after a failure? > Should I? or should I leave the slave (new master) up, and make the old > master the new slave? I'd leave the "new master" up until you can stop both databases and sync the data. > 5. I am running 3.23.54 and I know I should upgrade, but its not going > to happen today, so are there any show stopping bugs with this version? We've been running .53 for over a year and half with no problems. We use iptables on the db servers to get around the security updates > Thank you for your time, > > Andrew > > On Tue, 2003-12-02 at 17:10, John Griffin wrote: > > Hi Andrew, > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hope this helps! walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql disaster recovery
John, Thank you for your reply. I have read the replication chapter and this seems to be easier, but I do have a few questions. 1. I am using InnoDB tables, at least there are innodb argument to mysqld in the start script, so should I use mysqldump instead of tar-ing the data dir to create the baseline for the slave? I believe thats what the docs are stating, but I wanted to be certain. 2. I want to have a failover scenario here, so what would the impact be on the mysql db if I go master->slave->master? 3. What happens if the master fails, dies, and goes offline. Must I change all my apps to connect to the IP of the slave for connections to work? I am unclear if replication will provide failover capabilities. 4. What is necessary to bring the master back up after a failure? Should I? or should I leave the slave (new master) up, and make the old master the new slave? 5. I am running 3.23.54 and I know I should upgrade, but its not going to happen today, so are there any show stopping bugs with this version? Thank you for your time, Andrew On Tue, 2003-12-02 at 17:10, John Griffin wrote: > Hi Andrew, > > I am not a Guru. I would suggest that you look at MySQL's excellent replication > facility rather than NFS mount a drive. Having your data on an NFS mounted drive > will significantly degrade the performance of your database. Replication will not. > > > John Griffin > > --Original Message-- > From: Andrew Hall [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 02, 2003 4:51 PM > To: [EMAIL PROTECTED] > Subject: mysql disaster recovery > > > Greetings Gurus, > > I have a mysql server that I need to create a disaster recovery system > for. What I am planning on doing is putting the data dir on a NFS > mounted directory so that I can start mysql on either of two servers in > case one dies. The inbound connections would be load balanced in a fail > over scenario, so the IP that clients will connect to will be on the > load balancer. > > I'm wondering if there is anything already developed that would test > mysql on the primary server, and if its not functioning, kill any > remaining mysql processes if necessary, and start it on the secondary. > This logic seems to be the biggest problem. > > Any suggestions, or other methodologies to implement this would be > welcome. > > Thank you for your time in advance, > > Andrew > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql disaster recovery
Hi Andrew, I am not a Guru. I would suggest that you look at MySQL's excellent replication facility rather than NFS mount a drive. Having your data on an NFS mounted drive will significantly degrade the performance of your database. Replication will not. John Griffin -Original Message- From: Andrew Hall [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 4:51 PM To: [EMAIL PROTECTED] Subject: mysql disaster recovery Greetings Gurus, I have a mysql server that I need to create a disaster recovery system for. What I am planning on doing is putting the data dir on a NFS mounted directory so that I can start mysql on either of two servers in case one dies. The inbound connections would be load balanced in a fail over scenario, so the IP that clients will connect to will be on the load balancer. I'm wondering if there is anything already developed that would test mysql on the primary server, and if its not functioning, kill any remaining mysql processes if necessary, and start it on the secondary. This logic seems to be the biggest problem. Any suggestions, or other methodologies to implement this would be welcome. Thank you for your time in advance, Andrew -- 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 disaster recovery
Greetings Gurus, I have a mysql server that I need to create a disaster recovery system for. What I am planning on doing is putting the data dir on a NFS mounted directory so that I can start mysql on either of two servers in case one dies. The inbound connections would be load balanced in a fail over scenario, so the IP that clients will connect to will be on the load balancer. I'm wondering if there is anything already developed that would test mysql on the primary server, and if its not functioning, kill any remaining mysql processes if necessary, and start it on the secondary. This logic seems to be the biggest problem. Any suggestions, or other methodologies to implement this would be welcome. Thank you for your time in advance, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
