> -----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=XXXXXX --check --auto-repair mydatabase
mysqlcheck --user=root --password=XXXXXX --analyze --auto-repair mydatabase
Ask about "mytop" and various field lengths:
# BIGINT UNSIGNED = 8 Byte = FFFFFFFFFFFFFFFF = 18446744073709551615
# INT UNSIGNED = 4 Byte = FFFFFFFF = 4294967295
# MEDIUMINT UNSIGNED = 3 Byte = FFFFFF = 16777215
# SMALLINT UNSIGNED = 2 Byte = FFFF = 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
