How to do different tasks on mysql
Here’s a list of things that I’ve had to do on mysql from time to time. You dont always need these but when you do, its good to have them put together in one place.
Enable logging and get all sql queries hitting a mysql DB written to a log file
mysql> show variables like "general_log%"; This shows you two values usually. One ‘’ is a flag for setting general logging on and off. The other ‘’ is where to actually log to.
mysql> SET GLOBAL general_log = 'ON';
mysql> show variables like "general_log%"; +------------------+-------------------------+ | Variable_name | Value | +------------------+-------------------------+ | general_log | ON | | general_log_file | /var/lib/mysql/logname.log | +------------------+-------------------------+ 2 rows in set (0.00 sec)
tail -f /var/log/mysqld/logname.log
When you’ve gotten what you’re looking for, go back and turn off the logging.
mysql> SET GLOBAL general_log = 'OFF';
Enabling slow query logging
You notice a web page loading slowly and you suspect it might be because its taking a long time to fetch the results from the DB. Or you’re running performance tests against your system and you want to find out which of your queries are running slowly and hurting your system’s response time? Well - mysql has a slow query logging facility that can help you narrow down the issue.
ssh into your mysql server as a user that has superuser privileges etc and add the below lines to /etc/my.cnf and restart mysql service on that box.
[mysqld] slow_query_log=1 slow_query_log_file=/tmp/slow.log long_query_time=2
The ‘slow_query_log’ flag turns on or off slow query logging. The ‘slow_query_log_file’ setting specifies the actual file to log to and the ‘long_query_time’ is the setting where you specify what is to be considered a slow query. Here, 2 stands for anything longer than 2 seconds.
And you’re all set! Run your tests, your UI scripts etc and then check in on the /tmp/slow.log file and you should see the queries you’re interested in.
Listing out all the currently executing queries on a mysql server
The show processlist command is really useful in that, it shows you whats currently executing on your mysql server, what state they are in etc etc. Sort of like the ps -ef for mysql.
https://gist.github.com/dbhaskaran1/0c5dc0e820fe5f8e482b76c7c74885b0
Getting mysql server status from the command line
After you’ve logged into your mysql server, issue the ```status``` command to see various bits of information about your mysql server and your current session, including current user, current database, is SSL being used or not, mysql version, Server characterset, uptime of the server, number of slow queries, no of open tables, average number of queries per second etc. Here’s a sample output,
mysql> status -------------- mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper
Connection id: 59 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.17 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 4 hours 39 min 28 sec
Threads: 8 Questions: 7605 Slow queries: 0 Opens: 626 Flush tables: 1 Open tables: 588 Queries per second avg: 0.453
Finding simple things about the DB like - what are the different databases available, what are the different tables in current DB etc
show databases;
show tables;
The easier way to get deadlock data to find deadlocks in your DB
Install innotop (yum install innotop).
Once you’ve got that installed, you can connect innotop to your DB from the command line like this
```innotop --user root --askpass```
and you will be prompted for the password on the command line. Then you will be placed in the Dashboard view of innotop. Hit ? there and you will see a list of different information screens that you can then navigate to depending on what you’re trying to find out. L for locks, U for user statistics, D for InnoDB deadlocks, M for replication status, K for InnoDB Lock Waits etc. Try it out!
This article has a whole lot more information on using innotop. link
Finding deadlocks with queries running against your DB (The harder way)
The ```show engine innodb status;``` gives you a whole lot of data about your database. One of the things that I find useful is the data on deadlocks.
Here’s a sample output from this command, (Warning - wall of text coming your way, my apologies!)
https://gist.github.com/dbhaskaran1/38b82fab0b491032931f5e2d4cf48a46
---
