Activities

August 2014
M T W T F S S
« Jul   Sep »
 123
45678910
11121314151617
18192021222324
25262728293031

MySQL : How do I store Slow sql queries in to a Table

One of the Dev. find difficulties to populate the large slow queries file which he ask to fix the Application performance issue. After few checking he informed me we can log slow queries on one the system table in MySQL version 5.5. So that we can pragmatically manipulate and explore it easily.

But I do not want him to have production database access to check the table “slow_log” under “mysql” database.

My Plan is

1. Use a script to take the daily backup of this table
2. Restore this sql backup to the development server.
3. Empty the slow log entries from Production database.

Risk

I do not want to restart the MySQL production server prior to do this. So the MySQL 5.5 has some advanced features of switching slow log either to a file or a table at runtime 🙂 lol.. That’s it

Here we go,

Checking the current status and disable to slow query log during the switching time.

mysql> set global slow_query_log=0;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables  like "log%";
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log                             | OFF                            |
| log_bin                         | ON                             |
| log_bin_trust_function_creators | OFF                            |
| log_error                       | /sqldata/mysql/mysql-error.log |
| log_output                      | FILE                           |
| log_queries_not_using_indexes   | OFF                            |
| log_slave_updates               | OFF                            |
| log_slow_queries                | OFF                            |
| log_warnings                    | 1                              |
+---------------------------------+--------------------------------+
9 rows in set (0.00 sec)

<strong>2. Enable the Table to use logging and enable slow query.</strong>  

mysql> set global log_output="TABLE";
Query OK, 0 rows affected (0.00 sec)
mysql> show variables  like "log%";
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log                             | OFF                            |
| log_bin                         | ON                             |
| log_bin_trust_function_creators | OFF                            |
| log_error                       | /sqldata/mysql/mysql-error.log |
| log_output                      | TABLE                          |
| log_queries_not_using_indexes   | OFF                            |
| log_slave_updates               | OFF                            |
| log_slow_queries                | OFF                            |
| log_warnings                    | 1                              |
+---------------------------------+--------------------------------+
9 rows in set (0.00 sec)
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables  like "log%";
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log                             | OFF                            |
| log_bin                         | ON                             |
| log_bin_trust_function_creators | OFF                            |
| log_error                       | /sqldata/mysql/mysql-error.log |
| log_output                      | TABLE                          |
| log_queries_not_using_indexes   | OFF                            |
| log_slave_updates               | OFF                            |
| log_slow_queries                | ON                             |
| log_warnings                    | 1                              |
+---------------------------------+--------------------------------+
9 rows in set (0.00 sec)
mysql> exit

3. Script for restoring the daily slow log to remote server

#!/bin/bash
slow_log_dump="/sqldata/temp/slow_log.sql"
remote_host="10.10.10.15"
mysqldump --lock-tables=0   mysql slow_log -r $slow_log_dump -uroot -p'pass'
mysql -h $remote_host -uslow_user -p'DEVDBpwd' -e 'use slow_log;truncate table slow_log; source /sqldata/temp/slow_log.sql;'
echo -n > $slow_log_dump
mysql -e 'use mysql; TRUNCATE TABLE  slow_log;' -uroot -p'pass'

4. Set a cron to execute the script daily

#crontab -l
00 21 * * * /bin/sh /home/installation/scripts/slow_long_query_moving.sh    >/dev/null 2>&1

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>