Using SQL to create SQL (and other usefulness)

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):

mysql> select concat('alter table ', table_name, ' engine = blackhole;') from tables where table_schema = 'my_schema' and table_name not in ('table1','table3','table5','table7','table9','table11');

+------------------------------------------------------------+
| concat('alter table ', table_name, ' engine = blackhole;') |
+------------------------------------------------------------+
| alter table table0 engine = blackhole;                     |
| alter table table2 engine = blackhole;                     |
| alter table table4 engine = blackhole;                     |
| alter table table6 engine = blackhole;                     |
| alter table table8 engine = blackhole;                     |
| alter table table10 engine = blackhole;                    |
| alter table table12 engine = blackhole;                    |
+------------------------------------------------------------+

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

mysql> select concat('alter table ', table_name, ' engine = blackhole;') into outfile '/tmp/blackhole_alter_list.sql' from tables where table_schema = 'my_schema' and table_name not in ('table1','table3','table5','table7','table9','table11');
Query OK, 7 rows affected (0.00 sec)

And now I have an SQL to simply run:

mysql> connect my_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Connection id:    43
Current database: my_schema

mysql> source /tmp/blackhole_alter_list.sql;

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:

mysql> select concat('replicate-wild-ignore-table = ', table_schema, '.', table_name) from tables where table_schema = 'my_schema' and table_name not in ('table1', 'table3', 'table5', 'table7', 'table9', 'table11');
+-------------------------------------------------------------------------+
| concat('replicate-wild-ignore-table = ', table_schema, '.', table_name) |
+-------------------------------------------------------------------------+
| replicate-wild-ignore-table = my_schema.table0                          |
| replicate-wild-ignore-table = my_schema.table2                          |
| replicate-wild-ignore-table = my_schema.table4                          |
| replicate-wild-ignore-table = my_schema.table6                          |
| replicate-wild-ignore-table = my_schema.table8                          |
| replicate-wild-ignore-table = my_schema.table10                         |
| replicate-wild-ignore-table = my_schema.table12                         |
+-------------------------------------------------------------------------+

And into a file:

mysql> select concat('replicate-wild-ignore-table = ', table_schema, '.', table_name) into outfile '/tmp/replicate-exclude.cnf' from tables where table_schema = 'my_schema' and table_name not in ('table1', 'table3', 'table5', 'table7', 'table9', 'table11');

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:

cat "[mysqld]" > /etc/mysql/conf.d/replicate-exclude.cnf

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

mybox:~# cat /tmp/replicate-exclude.cnf >> /etc/mysql/conf.d/replicate-exclude.cnf

And restart:

mybox:~# /etc/init.d/mysql restart

Make sure to clean up!

mybox:~# rm /tmp/replicate-exclude.cnf

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!