In my first two months as a MySQL DBA, I have picked up a number of tips and tricks that have helped me in my daily job. In this post, I share them here with everyone. Hopefully they will help you as much as they have helped me.

Run a bash command from the MySQL command line:

When you are operating in the MySQL command line mode, use the command \! to execute a command as if you were in a normal bash shell. For instance, to run a top command from the MySQL command line, run the following code:
mysql>\! top

Kill a long running MySQL query:

If you have a query that has been running for a long time, you can kill a MySQL query same as you can kill a bash process. To kill a query, run a show processlist; to get the process Id. Then run a kill command with the Id:

mysql> show processlist;
+-------+-------+-----------+------+---------+------+-------+--------+
| Id    | User  | Host      | db   | Command | Time | State | Info   |
+-------+-------+-----------+------+---------+------+-------+--------+
| 19638 | admin | localhost | mysql| Sleep   | 23154| NULL  | NULL   |
+-------+-------+-----------+------+---------+------+-------+--------+
1 row in set (0.00 sec)

mysql>kill 19638;

Cancel the current command you are inputting:

While operating in a bash command line, you can hit Ctrl+C to cancel your input. If you execute this in a mysql command line interface, you will close the connection. Instead, before any query terminators ( ; or \G), end your query with \c:
mysql> select * from mysql.user where user=\c
mysql>

Change your AUTO_INCREMENT value

If you’re like me, you’ve done some development at one point or another on a live site and wanted to test your code changes. You execute the code and it inserts your data into your database flawlessly. Once you’re ready to implement your changes, you remove the rows you inserted. However, now you’ve increased your AUTO_INCREMENT counter a few times and need/want it back where it was. Find the last auto incremented value, and run a quick alter table to update it:
mysql>SELECT id FROM members ORDER BY id DESC LIMIT 1;
+----+
| id |
+----+
| 82 |
+----+
1 row in set (0.00 sec)
mysql>ALTER TABLE members AUTO_INCREMENT=83;

If you’re asking, why not run a SHOW CREATE TABLE members query to get the current value, the answer is because that will show the current AUTO_INCREMENT value, after the data you inserted. You need to find the last value in your table, and set the next AUTO_INCREMENT to be 1 higher than that.

Make your query output, more terminal friendly

As most everyone should know, the command delimiter in MySQL is the semi-colon: ;. However, if you are running a query with lots of column output, for example a SELECT * query, to make your output more readable, terminate your command with a \G:
mysql
mysql> SELECT * FROM mysql.user WHERE User='root' AND Host='localhost'\G
***************** 1. row *****************
Host: localhost
User: root
Password: *********************
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
...
1 row in set (0.00 sec)

Make output from `SHOW PROCESSLIST` more readable

When you have a very large processlist that causes queries to take up multiple rows and scrolls the screen, you can make it easier to read by paging the output. By setting the pager to less -S,this will page the output for better readability. With the -S switch, this will cause lines to display one line instead of wrapping. By setting the pager, you can navigate output with the arrow keys. Set output paging with the following command:
mysql>pager less -S
To set the pager back, close the mysql connection or run:
mysql>nopager

Retrieve the output of your mysql command in HTML or XML format

When running a MySQL command using the command line mysql command, pass the -H or -X switches to receive your output in HTML or XML output, then store the output in a file. You can suppress column headers with the –skip-column-names switch:
$ mysql -u user -p password mysql -e'select * from table' -H --skip-column-names > output.html'
$ mysql -u user -p password mysql -e'select * from table' -X --skip-column-names > output.xml'

Skip a one-off error in replication:

If your MySQL replication is not running and you have determined that the error is a one-off situation, you can tell MySQL to skip the error and continue replication with a simple command. This command can only be run when the Slave_SQL thread is not running. This command skips event groups in the binary logs. In an InnoDB, and other transactional tables, an event group corresponds to an entire transaction. For nontransactional tables like MyISAM, an event group corresponds to a single statement. While it is possible to skip more than one error at a time, it si not recommended. To skip the current error, execute the following command:
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Disable binary logging for the current session:

Let’s say that you’re developing on your production data, which many of us do. In order to prevent your changes from being logged, and therefore replicated, run the following command when you enter the MySQL command line:
mysql>SET SQL_LOG_BIN=0;
This is a session variable, meaning it will be re-enabled when you close the session, or you can set it back to 1.

Load an SQL data dump into MySQL

Say you have a raw SQL file full of data that you need to import into MySQL. Assuming your CREATE TABLE statement is part of that code, you can import it directly in one of two ways, either through bash or mysql command line:
$ mysql -u user -p password < /tmp/data_dump.sql
mysql>LOAD DATA INFILE '/tmp/data_dump.sql';

Tell the optimizer which index you want to use:

The MySQL Optimizer generally does a great job of executing queries and selecting the right indexes to use. But let’s face it, sometimes, it gets it wrong. In these instances, you can tell MySQL which index to use when executing your query. The only way to know this however, is to test, test, and test. You have to know your data (data, indexes, query times, etc). The only way to find out what index the optimizer is using, is to use the EXPLAIN command on your SELECT query, which is a whole other post.
mysql>SELECT * FROM table USE INDEX (index_name);

Choose proper columns (and column order) for indexes

I’m not going to get into this in depth as indexing tables is it’s own post. However, for a quick and dirty indexing solution, try the following. Say you have the following queries you run regularly on your site with varying WHERE clauses:
mysql>SELECT * FROM table WHERE a=x, b=y, AND c=z;
mysql>SELECT * FROM table WHERE a=x AND b=y;
mysql>SELECT * FROM table WHERE a=x;
At first thought, you may just think to use a covering index that will satisfy all 3 queries:
mysql>ALTER TABLE table ADD INDEX (a,b,c);
However this may not be the best solution. The covering index is correct, but the order may not be. The point of an index is to speed up query processing time by returning as few rows as possible. If you have 10,000 rows in your database, and the WHERE clause a=x satisfies 9,500 of those rows, you aren’t making MySQL’s job any easier. What you want to do is find out which of your conditions returns the fewest rows because then, you’re running additional conditions on a smaller data set and your query runs faster. For example, a=x may satisfy 9,500 rows and then of those 9,500 b=y may satisfy 5,000. After a=x, you’re running the next condition on 9,500 rows of data. However, if b=y satisfies 6,000 rows initially and a=x then satisfies 5,000 of those, you’re query will run faster. This is because your second condition is run on fewer rows, meaning faster processing times. This can all still be done through a covering index, but you need to find out which of your conditions is the most restrictive. To do that, run the following command:
mysql>SELECT SUM(a=x), SUM(b=y), SUM(c=z) FROM table;
What this will do is tell you the total number of rows that satisfy each condition. So if your output looks like this:

mysql>SELECT SUM(a=x), SUM(b=y), SUM(c=z) FROM table;
+----------+----------+----------+
| SUM(a=x) | SUM(b=y) | SUM(c=z) |
+----------+----------+----------+
|    10000 |     6000 |       17 |
+----------+----------+----------+
1 row in set (0.00 sec)

Then c=z condition is the most restrictive and so c should be the first column in your covering index. Run your query again, this time with a WHERE clause of c=z to find the second most restrictive condition, and so on and so forth.
mysql>SELECT SUM(a=x), SUM(b=y), SUM(c=z) FROM table WHERE c=z; and you may get

mysql>SELECT SUM(a=x), SUM(b=y), SUM(c=z) FROM table WHERE c=z;
+----------+----------+----------+
| SUM(a=x) | SUM(b=y) | SUM(c=z) |
+----------+----------+----------+
|        5 |        2 |       17 |
+----------+----------+----------+
1 row in set (0.00 sec)

From here, b=y is the next most restrictive condition. So your index should be built like this:
ALTER TABLE table ADD INDEX (c,b,a) and your queries will run faster.

The one drawback to this is that if c is the first column in your index, but it is not specified in the WHERE clause, as in the first two queries above, the index will be ignored. The only way to remedy this, is to create another index for those two queries, whereas the initial (a,b,c) index would satisfy all queries. The only way to determine which solution works best for you is to benchmark each option.

If you have any MySQL Tips or tricks of your own, please share them in the comments below to help everyone out.

Check out these Related Posts