MySQL – How to Know When a MySQL User Was Last Authenticated

MySQL

We have some distributed servers that connect to the same MySQL server. We need to have a log with the date/time when it was the last time a MySQL User logged in. How could I do that?

Best Answer

You can do it using several ways: Create general log and store there the info like in this answer:

You would probably want to use the general query log.

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.

One important thing with logging for security is that an attacker cannot access the log to erase traces of their presence, so consider append-only files.

Or create a table in to the database and store there every login via stored procedure as explained in this web page

-- SET GLOBAL init_connect="";
SET GLOBAL init_connect="CALL tracking.login_trigger()";


DELIMITER //

CREATE PROCEDURE login_trigger()
SQL SECURITY DEFINER
BEGIN
  INSERT INTO login_tracking (user, host, ts)
  VALUES (SUBSTR(USER(), 1, instr(USER(), '@')-1), substr(USER(), instr(USER(), '@')+1), NOW())
  ON DUPLICATE KEY UPDATE ts = NOW();
END;

//

Related Solutions

Mysql – How to rename a MySQL database

From this blog post by Ilan Hazan:

In MySQL there is no support for database renaming. In order to rename a MySQL database you can do one of the following:

Create new database and rename all tables in the old database to be in the new database:

 CREATE database new_db_name;
 RENAME TABLE db_name.table1 TO new_db_name.table1, db_name.table2 TO new_db_name.table2;
 DROP database db_name;

In Linux shell, use mysqldump to back up the old database, then restore the dumped database under a new name using the MySQL utility. Finally, use the drop database command to drop the old database. This option can perform badly for large database.
```
 mysqldump -uxxxx -pxxxx -h xxxx db_name > db_name_dump.sql
 mysql -uxxxx -pxxxx -h xxxx -e "CREATE DATABASE new_db_name"
 mysql -uxxxx -pxxxx -h xxxx new_db_name < db_name_dump.sql
 mysql -uxxxx -pxxxx -h xxxx -e "DROP DATABASE db_name"
```

Write a simple Linux script (my favorite solution)

 #!/bin/bash

 dbuser=xxxx
 dbpass=xxxx
 olddb=xxxx
 newdb=xxxx

 mysqlconn="mysql -u $dbuser -p$dbpass -h localhost"

 $mysqlconn -e "CREATE DATABASE $newdb"
 params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='$olddb'")

 for name in $params; do
       $mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name";
       echo "Renamed $olddb.$name to $newdb.$name";
 done;

 #$mysqlconn -e "DROP DATABASE $olddb"

If all your tables are MyISAM, you can rename the old database folder name:
1. Shut down the MySQL server,
2. Rename the database folder name to the new name,
3. Start the MySQL server.

MySQL, is there a way to tell when was database last changed/modified

Only works for MyISAM tables

You can run a MySQL query against the information_schema table:

Example (replace dbname with your database name):

SELECT UNIX_TIMESTAMP(MAX(UPDATE_TIME)) as last_update 
FROM information_schema.tables 
WHERE TABLE_SCHEMA='dbname' 
GROUP BY TABLE_SCHEMA;

Best Answer

Related Solutions

Mysql – How to rename a MySQL database

MySQL, is there a way to tell when was database last changed/modified

Related Topic