Using SQL to create SQL (and other usefulness)

By | In MySQL | February 11th, 2011

Today, I had a task of modifying a slave to exclude (or if you want to say specifically include) tables. The situation is that this slave need only contain tables used for reporting. The added benefit being that with fewer tables to replicate there will be less replication traffic, less disk I/O and less disk space used hence better performance for queries that digest statistical data.

In the past, I would manually edit files, but today I decided to turn over a new leaf and be lazy — and let the database do the work for me! I’m posting this article because I feel obliged to share with you my laziness.

The client provided me a list of tables in a spreadsheet which I cut-n-pasted and used the magical regex powers of vim to give me a quoted, comma-separated list of tables. I love information_schema, because as opposed to ‘SHOW’ commands which may be easy to remember, don’t give me the ability to produce an output which I desire. Using the information_schema table “tables”, I can get the information I need. The list of tables which I produced in a simple vim edit are a list of tables to exclude, so this givs me part of my where clause. Also, I have a particular schema to specify. I then pasted my list of tables into my query as shown below (table names modified to protect the innocent):

Better yet, why not produce an SQL file I can run?

And now I have an SQL to simply run:

These tables are now blackhole tables, meaning that regardless of whatever DML statement runs against them, nothing really happens, no disk I/O (hence not really existing). This is very convenient, indeed!

Also, I might want to have this list of table to exclude from replication in my my.cnf. Again, SQL can make this happen:

And into a file:

This file you are creating is a mysql configuration include file. Ubuntu and other Linux distributions have configured MySQL so that the top-level /etc/my.cnf file includes any “.cnf” file in /etc/mysql/conf.d (check your distrubtion first). In this case, the list of tables to exclude will be contained in this directory so as to not require editing the top-level my.cnf file. Make sure first to add the correct “[mysqld]” section to the top of the file:

And concatenate the contents of the file you created to this config file:

And restart:

Make sure to clean up!

Your replication setup will now exclude that list of tables from replication.

So, as you can see, you can use SQL to produce output that you can put to use within the database or outside the database (configuration files, code, etc).

I hope this tip proves useful to you!

Contact Us

Leave a Reply

Your email address will not be published.
Required fields are marked (*).