RE: DBA questions to ask WAS: How to corrupt a database please???
-Original Message- From: Nurudin Javeri [mailto:nsjav...@idh.com] Sent: Sunday, April 18, 2010 9:25 AM To: mysql@lists.mysql.com Subject: How to corrupt a database please??? Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu 100GB! That's a HUGE database to play with and can take more than hours to repair. I would tone it down. You just want them to fix it and know what the commands/steps are, not spend hours sitting there watching a blinking cursor. We have almost a BILLION rows in our DB and it's only 70GB and we cringe if we have to alter a table as we know it's going to take a LONG time. I would focus on JUNIOR DBA tasks, such as: How do you start/stop the mysql server? sudo /etc/init.d/mysql stop|start|restart Where are the mysql logs found? (Trick Q: On ubuntu they're stupidly in /var/log/messages !?) GRANTing permissions (figure out why the script isn't writing to the DB, or find the security issue with this user, etc.) Execute a mysql command from the bash command line (not the mysql CLI) How do you find a slow query (slow query log) Setup a master/slave and then deliberately write to the slave. This causes replication to fail then. How do you fix it? mysql show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: No Last_Error: Error 'Duplicate key name 'id_operator'' on query. Default database: 'core'. Query: 'ALTER TABLE `user_has_notification` ADD INDEX `id_operator` (`id_operator`)' mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G What if /var/log/mysql growing too large? mysql PURGE BINARY LOGS; (The command RESET MASTER is not intended while replication slaves are running.) Foreign Key Failures? mysql show innodb status; LATEST FOREIGN KEY ERROR 090604 0:50:37 Cannot drop table `core/city` because it is referenced by `core/state` How do you make a backup? mysqldump -uroot -p --opt --add-drop-database --complete-insert --quote-names --comments --verbose --databases mydatabase | gzip -c ~/mydatabase.sql.gz How would you load that backup back in? gunzip ~/mydatagbase.sql.gz | mysql -uroot -p How do you load a comma separated file with a column header line? load data infile '/home/prod/user-batch.csv' ignore into table invitation_request fields terminated by ',' ignore 1 lines; What if you have lost/forgotten the mysql root password? http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html#resetting -permissions-unix You could throw these in for good measure: mysqlcheck --user=root --password=XX --check --auto-repair mydatabase mysqlcheck --user=root --password=XX --analyze --auto-repair mydatabase Ask about mytop and various field lengths: # BIGINT UNSIGNED = 8 Byte = = 18446744073709551615 # INT UNSIGNED = 4 Byte = = 4294967295 # MEDIUMINT UNSIGNED = 3 Byte = FF = 16777215 # SMALLINT UNSIGNED = 2 Byte = = 65535 # TINYINT UNSIGNED = 1 Byte = FF = 255 # BIGINT SIGNED = -9223372036854775808 to 9223372036854775807 # INT SIGNED = -2147483648 to 2147483647 # MEDIUMINT SIGNED = -8388608 to 8388607 # SMALLINT SIGNED = -32768 to 32767 # TINYINT SIGNED = -128 to 127 # TINYTEXT = 255 # TEXT = 65535 # MEDIUMTEXT = 16777215 # LONGTEXT = 4294967295 # TEXT fields are NOT case sensitive, whereas BLOB fields are. # Always try to use UNSIGNED integers whenever possible. Anyways, you get the idea. Don't set the JUNIOR interviewee up for failure from the start. The questions should be something in the realm of what they'll be doing on a daily basis. The rest is stuff that Google will solve if and when that time comes. You want to make sure they have a solid grasp of mysql, not an expert in it. Don't blast them if they don't have this stuff memorized. I sure as hell don't, that's what our Wiki is for. You want people that can find answers, not memorize them. Daevid. http://daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to corrupt a database please???
Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
open the file and remove some data and close it for both data file and index files, So the tables will be corrupted when access. On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: How to corrupt a database please???
You can remove the innodb logs and/or the innodb data file. You can also remove some of the individual .idb files (if you're using file-per-table option). On Sun, Apr 18, 2010 at 11:25 AM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: How to corrupt a database please???
On Sun, Apr 18, 2010 at 10:39 AM, Suresh Kuna sureshkumar...@gmail.com wrote: open the file and remove some data and close it for both data file and index files, So the tables will be corrupted when access. On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu Umm, shouldn't you train your Junion DBA to: 1. fail off of the corrupted servers. 2. restore from backup. 3. or at least get a non-junior dba and then have them shadow? I have a problem with the idea of repairing Innodb. Depending on where the corruption (checksum mismatch) has occurred it can be very difficult to get all the original data out. Don't get me wrong, there are way to do it, but it is a nasty endeavor. For that mater I don't trust repairing MyISAM all that much either. I try my very best to keep MyISAM out of production. In my opinion MyISAM should be treated as something one step higher than the blackhole engine. Put data in and you might be able to pull it out later. (don't get me wrong, packed myisam has its place...) -- Rob Wultsch wult...@gmail.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: How to corrupt a database please???
What if the DBA ask for the backup? And those recommendations can be fixed or they have a very high chance of making recovery impossible? On Sun, Apr 18, 2010 at 1:09 PM, Rob Wultsch wult...@gmail.com wrote: On Sun, Apr 18, 2010 at 10:39 AM, Suresh Kuna sureshkumar...@gmail.com wrote: open the file and remove some data and close it for both data file and index files, So the tables will be corrupted when access. On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu Umm, shouldn't you train your Junion DBA to: 1. fail off of the corrupted servers. 2. restore from backup. 3. or at least get a non-junior dba and then have them shadow? I have a problem with the idea of repairing Innodb. Depending on where the corruption (checksum mismatch) has occurred it can be very difficult to get all the original data out. Don't get me wrong, there are way to do it, but it is a nasty endeavor. For that mater I don't trust repairing MyISAM all that much either. I try my very best to keep MyISAM out of production. In my opinion MyISAM should be treated as something one step higher than the blackhole engine. Put data in and you might be able to pull it out later. (don't get me wrong, packed myisam has its place...) -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mr.crip...@gmail.com
Re: How to corrupt a database please???
On Sun, Apr 18, 2010 at 11:07 AM, Jim Lyons jlyons4...@gmail.com wrote: You can remove the innodb logs and/or the innodb data file. You can also remove some of the individual .idb files (if you're using file-per-table option). He originally asked about how to provide a training excise about repairing a db. How the hell do you repair from not having data files? For that matter the recovery from lacking log files (and assuming a crashed state) is imho ugly as hell. -- Rob Wultsch wult...@gmail.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: How to corrupt a database please???
A couple good tests are. 1. Corrupt a relay log. For this you can stop the sql thread, cat /dev/urandom over the newest relay log, start the sql thread and watch it fail. 2. Change the innodb_log_file_size in my.cnf without going through the proper procedure to remove the old log files. In 5.0 this will cause incorrect information in frm file errors for queries which will take a little bit of work to track down. 3. Can some random data over myisam files and run a check tables so mysql marks them as crashed. On Sun, Apr 18, 2010 at 11:13 AM, Rob Wultsch wult...@gmail.com wrote: On Sun, Apr 18, 2010 at 11:07 AM, Jim Lyons jlyons4...@gmail.com wrote: You can remove the innodb logs and/or the innodb data file. You can also remove some of the individual .idb files (if you're using file-per-table option). He originally asked about how to provide a training excise about repairing a db. How the hell do you repair from not having data files? For that matter the recovery from lacking log files (and assuming a crashed state) is imho ugly as hell. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
On Sun, Apr 18, 2010 at 11:13 AM, Andrés Tello mr.crip...@gmail.com wrote: What if the DBA ask for the backup? And those recommendations can be fixed or they have a very high chance of making recovery impossible? Who is the dba going to ask for a backup? Himself? The guy that puts backups on tape? One way or another the DBA damn well better know how to get a backup. Failing off of a server gets you on to a slave which should be sync'd with the master. If you restore from backup then you can run a pitr . In my opinion both of these options are usually superior to running repair table on a production server. That is if you like uptime. For the record innodb corruption is quite rare, at least in comparison to MyISAM corruption. If I get a call at 2AM and find a server having died due to innodb corruption I would fail off of the server. No ifs, no ands, not buts. I would assume: 1. Possible, perhaps even probably hardware issues if there is Innodb corruptions. 2. A failover takes a set amount of time. Repairing corruptions will usually take longer, perhaps much much longer. -- Rob Wultsch wult...@gmail.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: How to corrupt a database please???
Rob Wultsch wrote: On Sun, Apr 18, 2010 at 11:13 AM, Andrés Tello mr.crip...@gmail.com wrote: What if the DBA ask for the backup? And those recommendations can be fixed or they have a very high chance of making recovery impossible? Who is the dba going to ask for a backup? Himself? The guy that puts backups on tape? One way or another the DBA damn well better know how to get a backup. Failing off of a server gets you on to a slave which should be sync'd with the master. If you restore from backup then you can run a pitr . In my opinion both of these options are usually superior to running repair table on a production server. That is if you like uptime. For the record innodb corruption is quite rare, at least in comparison to MyISAM corruption. If I get a call at 2AM and find a server having died due to innodb corruption I would fail off of the server. No ifs, no ands, not buts. I would assume: 1. Possible, perhaps even probably hardware issues if there is Innodb corruptions. 2. A failover takes a set amount of time. Repairing corruptions will usually take longer, perhaps much much longer. I agree with Rob. InnoDB failures are nearly always caused by OS-level or HW-level failures. The worst-case scenario is to need to rebuild part of your data from whatever information remains in the corrupted file. It is much better to restore from backup or rebuild from a slave than to go through the pain of rebuilding a corrupted tablespace. But, here are some ideas on ways to screw one up: 1) Put it on an NFS drive then read from it using another user's account while the database is trying to write to it. 2) Scan it with an antivirus program while it is online and actively making changes. 3) Use a hex editor and manually zero out a page of data or index 4) Delete the active log file (or both of them) 5) Turn on two MySQL instances to the same files at the same time. 6) Delete the .frm file for a table 7) Take a backup of the tablespace, change a few things, the restore the tablespace but not the logs. While I can't predict what kind of problem you will create for yourself, these are all things that have created problems for others in the past. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
