Help! My Database Stopped Working

What do you do when MySQL stopped working? Here are my 2 cents on what you should do.

1. Check if the database is running. Check the MySQL process.
   
   ps -aux | grep mysql
   
   
2. Check the error logs. Where is the location of the error logs again? The default location would be at /usr/local/mysql/data/ and look for a file with .err at the end of it. Sample errors would be:
   
   
   • Database got restarted - check with your sys admin why it restarted.
   • Replication failed - do a show slave status and check the error then restart the replication.
   
   
3. Check the MySQL process list and see if there are any read / write locking happening.
   
   show processlist
   
   
   

MySQL Eggs

When I was playing some Windows games before, there used to be eggs like a combination of certain keys would produce messages like the program pays tribute to blah, blah, blah. I wish MySQL has something like that... that if I press certain combination of keys that instead of the language in English, it would become a combination of English and unknown language. Just a thought.

When you look at the new feature set of MySQL, they are just so serious in trying to become the no. 1 database out there. Where is the fun??

My.cnf File

This is the configuration file of MySQL. For new DBAs, depending on your needs (eg. if the database is small, medium, large), just copy the appropriate my.cnf in /usr/local/mysql/support-files/ . Then just change couple of parameters and you are all set. There is no need to make a huge fuss about it. Many experts will tell you to change this file, I recommend not to listen to them. MySQL was created to be simple... If you really need to change it, then make sure you know what you are doing.

MySQL Conference 2008

Yep. Guess you have heard that there is such a thing called "MySQL Conference 2008". In fact, this has been a yearly event. This year it would be held in Santa Clara, California on April 14-17, 2008.

But the question is should you attend at all? Well, if your company is going to pay for it, why not?! But if you are going to pay for it, think twice. Sure, you will learn a lot, but do you really need them especially since you are just starting as a MySQL DBA? Even if you are an intermediate DBA, it would just be for fun if you are going there... I'm not sure if the learning value outweighs the cost.

Anyway, check this out for details... http://en.oreilly.com/mysql2008/public/content/home

How to Install a MySQL Database

Just search the MySQL documentation and it will clearly tell you on how to install MySQL. Just for clarification, when you install MySQL. There's the installation for the server part and there's the installation for the client part.

When your boss or peers asked you to install MySQL, they are referring to the server part. The server is what you use to create your database and store your data. The client is what you use to 'query' your database server.

For simplicity sake, use MySQL RPMs. This is used in a Linux system. I assume that if you are using Windows that you should be able to guess what you need to do. There's a reason why you should use RPMs and the reason is that it is simple, efficient and easy to manage.

As a newbie, do not be tempted to do binary installation eventhough it looks easy and believe me it is easy too. But RPMs are way easier... here's how to install a MySQL database in a Linux system.

1. Download the appropriate MySQL RPM for your Linux system.

1.1 32-bit or 64 machine??? Oh, you need the assistance of your system administrator for this... specifically ask them if you have a 32-bit or 64-bit. For 32-bit machines, you need the RPM with 386 appended to it and the 64-bit has the 686 appended to it.

1.2 What kind of operating system? Again, ask your system administrator. It is easier to ask than to figure it on your own. So, if they tell you they are using a Fedora Core 6 (note on the version of the operating system, it is important), then just look for the corresponding RPM in the MySQL website.

2. Install the MySQL RPM.

Ahem, ahem... use this command....

rpm -ivh .rpm

How hard is that? Oh, in case you made a mistake. There are two things that will most likely happen, you will get an error saying that you downloaded the wrong RPM (you can blame your system administrator or you can blame your boss for giving you a small monitor -- can't see that right rpm in a 19" monitor) or you installed the wrong RPM.

If you installed the wrong RPM, use this command to erase it.

rpm -qa | grep -i mysql ((a) this will tell you what MySQL version was installed.)

rpm -e (result from the above) (This will remove the RPM.)

So, that's it. Your next step as outlined in the MySQL documentation is to start the server, issue some grant SQL, etc...

Pareto Principle in MySQL

Pareto Principle, also known as the 80/20 rule, states that 80% of the output comes from 20% of the input. How are we going to apply this to MySQL?

If the MySQL documentation is 1,000 pages long (I'm giving an arbitrary number here), then according to the 80/20 rule, in order to learn 80% of MySQL, you just have to study 20% of the documentation which is 200 pages.

But! 200 pages is still a lot. Non-fiction books are almost 200 pages long. What if we apply 80/20 rule to this situation again? So, to learn 80% of the 200 pages, you just need to learn 20% of it which would be 40 pages.

Think about it, in a daily DBA work, do you really need to know 40 pages worth of MySQL documentation? Most likely not, you may have intuitively applied the 80/20 rule again and again until you are left with probably 3 pages worth of MySQL documentation which are the bare essentials to your work.

MySQL Logs

My previous boss told us that in order to become a good system admnistrator, one must know how to parse the 'logs'.

I hate the 'logs'. As you are well aware of, there are three major logs in MySQL and those are the error logs, slow query logs, and the binary logs.

1. Error Logs

This is the simplest of all the logs and the most important for all the newbies. In case you forget anything, remember to know where the error logs are located because it will save you a lot of headaches in the feature.

2. Slow Query Logs

The infamous utility called the mysqldumpslow will help you parse your slow query logs. If you have a small database, then this can help you. But what if you have a 25 GB database with a rate of 1,000 queries per seconds? Mysqldumpslow will still be able to help you, but you may have to parse the slow query logs on your own.

You can use your favorite language, PERL, Python or even AWK to help you parse the slow query logs. Come to think about it, they are just text files. Is it easy to write a parsing script? Hmmm, it depends on your skill but it is worthwhile to learn regular expression and ways to parse these files on your own.

3. Binary Logs

When I first heard about this type of log, I hate it! This log contains all the changes done to your database. And there is a parameter in the my.cnf that controls the growth of the binary logs and it would be painful if you don't set it right.

Imagine the binary log grew to 2 GB, when you use the mysqlbinlog to convert the binary logs to text files, imagine how long it would take? Or what if you know a position in the binary log that you want to extract, how long would it take the mysqlbinlog to get it from a 2 GB file?