Is there a way to backtrack who connects to MySQL server? Aside from show processlist;? I want to see who connects last day. Thank you.
MySQL connection logs backtrack
MySQL
Related Solutions
Here is something interesting to remember : the status variables Questions takes a count of all queries executed by mysqld, regardless of who issued each query.
Why do I say regardless of who issued each query ?
There are queries that are launched internally by mysqld. Here is a simple example:
mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 15 |
+---------------+-------+
1 row in set (0.00 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions | 56 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 15 |
+---------------+-------+
1 row in set (0.00 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions | 58 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions | 59 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions | 60 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
Please note that issuing a simple query like show global status like 'Questions';
is a query even if your are just requesting internal data from mysqld.
Multiply this by any number of DB Connections coming from MONyog, Munin, Nagios, MySQL Enterprise Monitor, or any other MySQL Monitoring tools and will always have a steady rise in the number of lightweight queries being executed.
I wrote about this before in ServerFault in a question "1 billion mysql queries in 24 hours? Can something be wrong?"
Queries can quitely come and go. The question left to answer is, why so many DB Connections? You need to make sure Apache is closing DB Connections at the same rate as mysqld. You may need to see run netstat
and look for DB Connections to mysql that have the TIME_WAIT status.
There are bug reports on this going back to MySQL 4.1 although the root of the problem may actually be with PHP:
The system user
is the user defined to execute MySQL Replication.
There are two DB Connections dedicated to performing MySQL Replication
- IO Thread : This thread is responsible for downloading the latest Binary Log Entries from the Master and Storing those Entries in the Slave's Relay Logs.
- SQL Thread : This thread is responsible for processing the relay logs like a FIFO queue. It basically reads the next available Relay Log Entry and processes the SQL of that Entry.
The system user
is referred to as a non-client thread in the MySQL Documentation about SHOW PROCESSLIST;
As far seeing localhost:#####, that number after the colon is a really a port number within mysqld assigned to localhost.
UPDATE 2012-04-27 18:00 EDT
Questions from your comment
Can I rename the system user? Or any other properties of the system user? Also, is the system user is dedicated only for replication? Or any other MySQL processes are spawned by System User? I understand that system user cannot be accessed by a client, its an internal process spawned by MySQL.
Answers to Questions from your comment
No, you cannot rename the system user. It is dedicated to handle MySQL Replication only. The only way to manipulate properties of of the system user
would be throught the GRANT command issued to create the replication user.
For example, when you setup a replication user, you issue a command like this:
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* to 'repl'@'%';
When START SLAVE
is issued on a Slave, the Master authenticates the DB Thread coming from the Slave and assign one thread on the Master. The thread on the Master will ship binary log entries to the I/O Thread on the Slave. The I/O Thread on the Slave is assigned to system user
for handling communcation between Master and Slave. The SQL Thread on the Slave is also assigned to system user
for handling intracommunication of local relay log entries to be processed FIFO (Frist In, First Out) by mysqld running on the Slave. No direct access is permitted via the MySQL Client on the Slave except for
STOP SLAVE;
(Kills both I/O Thread and SQL Thread)STOP SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;
START SLAVE;
(Creates both I/O Thread and SQL Thread)START SLAVE IO_THREAD;
START SLAVE SQL_THREAD;
Of course, you could issue KILL ####;
where #### is the process ID of either the I/O Thread or SQL Thread. You would be totally respsonsible for reestablishing replication at the risk of losing the correct log file and position if the KILL
command misses any communication because of an unnatural stoppage of a replicaton thread.
Related Question
- MySQL: Replication is paused with both threads running, System Lock in ProcessList
- Mysql show no more than 5 concurrent connection
- Mysql – How to restore truncated MySQL table from binary logs
- Mysql – How to find MYSQL Logs for Drop Delete Record
- MySQL – How to Reset Aborted_connects
- MySQL – Troubleshooting SELECT from Table with BLOB Fields
Best Answer
There are multiple audit logging products available. If you are using Percona or MariaDB flavour of MySQL you have the option of their plugin. If you are using Oracle MySQL you can pay for their enterprise version of audit plugin (as part of Enterprise Edition). There is also an audit plugin from McAfee that will fill this requirement and is generally available cross-alternative and from 5.1+. These products permit you to log both logins and queries. Finally there's a plugin to track logins only from a community contributor. Links to all below.
http://www.percona.com/doc/percona-server/5.5/management/audit_log_plugin.html https://mariadb.com/kb/en/mariadb/documentation/plugins/mariadb-audit-plugin/ http://dev.mysql.com/doc/refman/5.5/en/audit-log-plugin.html http://code.openark.org/blog/mysql/introducing-audit_login-simple-mysql-login-logfile-based-auditing