Accidental DBA’s Guide to MySQL Management

So, you’ve been tasked with managing the MySQL databases in your environment, but you’re not sure where to start.

Here’s the quick & dirty guide. Oh yeah, and for those who love our stuff, take a look to your right.

See that subscribe button? Grab our newsletter!

Steps to MySQL Management

Here are the steps that are required for MySQL management as a DBA:

1. Installation

The “yum” tool is your friend.  If you’re using Debian, you’ll use apt-get but it’s very similar. You can do a “yum list” to see what packages are available. We prefer to use the Percona distribution of MySQL.

It’s fully compatible with stock MySQL distribution, but usually a bit ahead in terms of tweaks and fixes. Also, if you’re not sure, go with MySQL 5.5 for new installations.

$ rpm -Uhv
$ yum install Percona-Server-client-55
$ yum install Percona-Server-shared-55
$ yum install Percona-Server-shared-compat
$ yum install Percona-Server-server-55

The last command will create a fresh database for you as well.

Already have data in an existing database? Then you can migrate between MySQL and Oracle.

2. Setup Replication

MySQL replication is a process you’ll need to setup over and over again. Its statement based in MySQL. A lot of INSERT, UPDATE, DELETE & CREATE statements are transferred to the slave database, and applied by a thread running on that box.

The steps to setup are as follows:

1. lock the primary with FLUSH TABLES WITH READ LOCK;
2. issue SHOW MASTER STATUS and note the current file & position
3. make a copy of the data. You can dump the data:

  $ mysqldump -A --single-transaction > full_primary.mysql

Alternatively, you can use xtrabackup to take setup replication without locking!

4. copy the dump to the slave database (scp works, but rsync is even better as it can restart if the connection dies).
5. import the dump on the slave box (overwrites everything so make sure you got your boxes straight!)

  $ mysql < full_primary.mysql

6. point to the master

  mysql> change master to
   master_user='rep',
   master_password='rep',
   master_host='10.20.30.40',
   master_log_file='bin-log.001122',
   master_log_pos=11995533;

7. start replication & check

  mysql> start slave;
  mysql> show slave statusG;

You should see something like this:

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

3. Analyze Slow Query & Tune

If you’re managing an existing MySQL database and you hit a performance blip, it’s likely due to something that has changed. You may be getting a spike in user traffic, that’s new! Or you may have some new application code that has been recently deployed, that’s new SQL that’s running in your database. What to do?

If you haven’t already, enable the slow query log:

  mysql> set global slow_query_log=1;
  mysql> set global long_query_time=0.50;

Now wait a while. A few hours perhaps, or a few days. The file should default to

/var/lib/mysql/server-slow.log

Now analyze it. You’ll use a tool from the percona toolkit to do that. If you haven’t already done so, install the percona toolkit as well.

  $ yum install percona-toolkit
  $ pt-query-digest /var/lib/mysql/server-slow.log > /tmp/server-report.txt

Once you’ve done that “less” the file, and review. You’ll likely see the top five queries account for 75% of the output. That’s good news because it means less query tuning. Concentrate on those five and you’ll get the most bang for your buck.

Bounce your opinions about the queries off of the developers who build application code. Ask them where the code originates. What are those pages doing?

Check the tables, are there missing indexes? Look at the EXPLAIN output. Consider tuning the table data structures, multi-column, or covering indexes. There is typically a lot that can improve these troublesome queries.

4. Monitoring Command Line Tools

You’ll want to have a battery of day-to-day tools at your disposal for interactive monitoring of the database. Don’t go overboard. Obsessive tuning means obsessively turning knobs and dials. If there are no problems, you’re likely to create some. So, keep that in mind.

innotop is a “top” like utility for monitoring what’s happening inside your little database universe.  It’s probably already available through yum and the “epel” repository:

  $ yum install innotop
  First edit the .my.cnf file and add:
  [client]
  user=root
  password=mypw

From there you should be able to just fire up innotop without problems.

mysqltuner is a catch all tool that does a once over of your server, and gives you some nice feedback.  Get a copy as follows:

  $ wget mysqltuner.pl
  Then run it:
  $ chmod +x mysqltuner.pl
  $ ./mysqltuner.pl

Here are a couple of useful mysql shell commands to get database information:

  mysql> show processlist;
  mysql> show innodb status;
  mysql> show status;

There is also one last tool which can come in handy for reviewing a new MySQL server. Also, from percona toolkit, the summary tool. Run it as follows:

  $ pt-summary

5. Backups

You absolutely need to know about backups if you want to sleep at night. Hardware and database servers fail, software has bugs that bite. And if all that doesn’t get you, people make mistakes. So-called operator error will surely get you at some point. There are three main types:

1. Cold Backups

With the database shutdown, make a complete copy of the /var/lib/mysql directory, along with perhaps the /etc/my.cnf file. That together amounts to a cold backup of your database.

2. Hot Backups

There has been an enterprise tool for MySQL that provides this for some time. But we’re all very lucky to also have the open source Percona xtrabackup at our disposal. Here’s a howto using it for replication setup.

3. Logical Backups

These will generate a file containing all the CREATE statements to recreate all your objects, and then INSERT statements to add data.

  $ mysqldump -A > my_database_dump.mysql

6. Review Existing Servers

The percona toolkit summary tool is a great place to start.

  $ pt-summary

Want to compare the my.cnf files of two different servers?

  $ pt-config-diff h=localhost h=10.20.30.40

Of course, you’ll want to review the my.cnf file overall. Be sure you have looked at these variables:

tmp_table_size
max_head_table_size
default_storage_engine
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
join_buffer_size
log_slow_queries
log_bin
innodb_log_buffer_size
innodb_log_file_size
innodb_buffer_pool_size
key_buffer_size (for MyISAM)
query_cache_size
max_packet_size
max_connections
table_cache
thread_cache_size
thread_concurrency

7. Security Essentials

The output of the pt-summary and mysqltuner.pl scripts should give you some useful information here. Be sure to have passwords set on all accounts. Use fewer privileges by default, and only add additional ones to accounts as necessary.

You can use wildcards for the IP address but try to be as specific as possible. Allow for a subnet, not the whole internet ‘10.20.30.%’ for example instead of just ‘%’.

Also keep in mind that at the operating system or command line level, anyone with root access can really mess up your database. Writing to the wrong datafile or changing permissions can hose a running database very quickly.

8. Monitoring

Use a monitoring system such as Nagios to keep an eye on things.  At minimum check for:

1. connect to db
2. server load average
3. disk partitions have free space
4. replication running – see above IO & SQL running status messages
5. no swapping – plenty of free memory

9. Ongoing Maintenance

Periodically it’s a good idea to review your systems even when they’re running smoothly. Don’t go overboard with this however. As they say if it isn’t broke, don’t fix it.

1. check for unused & duplicate indexes
2. check for table fragmentation
3. perform table checks (if using MyISAM)

10. Manage the Surprises

MySQL is full of surprises. In the Oracle world you might be surprised at how arcane some things are to setup, or how much babysitting they require. Or you might be surprised at how obscure some tuning & troubleshooting techniques are. In the MySQL world there are big surprises too. Albeit sometimes of a different sort.

1. Replication Checksums

One that continues to defy my expectations is those surrounding replication. Even if it is running without error, you still have more checking today. Unfortunately, many DBAs don’t even know this!

That’s because MySQL replication can drift out of sync without error. We go into specific details of what things can cause this, but more importantly how to check and prevent it, by bulletproofing MySQL with table checksums.

2. Test & Confirm Restores of Backups

Spinup a cloud server in Amazon EC2, and restore your logical dump or hotbackup onto that box. Point a test application at that database and verify that all is well. It may seem obvious that a backup will do all this.

But besides the trouble when a filesystem fills up, or some command had the wrong flag or option included. There can be even bigger problems if some piece or section of the database was simply overlooked.

It’s surprising how easy it is to run into this trouble. Testing also gives you a sense of what restoring time looks like in the real world. A bit of information your boss is sure to appreciate.

If you made it this far, you know you want to grab the newsletter.

Conclusion

That’s all about how to manage MySQL as a DBA. Hopefully, you have found this guide exceptional from other ordinary guides to MySQL management. For any further queries, our comment box is always open for you. Thanks for reading!