Mysql – How to tell if a MySQL database is being accessed

MySQL

Is there some convenient way to see which users are accessing a MySQL database? Ideally I'd have some kind of application making this easy, but other methods would be OK.

If there isn't a standard definition, I would accept "have queried the database in the last 30 minutes".

Best Answer

How about the query:

SHOW FULL PROCESSLIST

This will show you connections to the database, what user they are logged in with, the state of the connection, and any currently running command.

Related Solutions

MySQL – Prevent Non-Replication Writes to MySQL Slave

Enable the read-only option in my.cnf. It can also be specified as a flag on the command line using --read-only with mysqld.

Mysql – Setting automatic backups of MySQL database including table lock etc – MySQL Workbench

If you have MySQL installed on the Ubuntu machine, you can do the following:

Suppose IP of Virtual is 10.1.2.30

You can the following on the Ubuntu machine

BACKUP_FILE=/root/MySQLData.sql
MYSQL_HOST=10.1.2.30
MYSQL_USER=whateverusername
MYSQL_PASS=whateverpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} --all-databases > ${BACKUP_FILE}

You could crontab this on the Ubuntu machine as well

If you are looking for consistent mysqldumps, you could

use --single-transaction option if all the data is InnoDB
if all data is MyISAM, run FLUSH TABLES WITH READ LOCK; on the database
if data is MyISAM and InnoDB mixed, run FLUSH TABLES WITH READ LOCK; and --single-transaction on the database (See my post https://dba.stackexchange.com/questions/8587/mysql-db-backup-excluding-specific-tables/8613#8613 for some ideas on scripting that)

Give it a Try !!!

Best Answer

Related Solutions

MySQL – Prevent Non-Replication Writes to MySQL Slave

Mysql – Setting automatic backups of MySQL database including table lock etc – MySQL Workbench

Related Topic