mysql

mysql

Check db size

Check mysql

SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "DB_name" ORDER BY (data_length + index_length) DESC;

Show mysql users

Show all mysql database users
SELECT User FROM mysql.user;

Show user with pass and host
select user, password, host from mysql.user;

Show users privileges
SHOW GRANTS FOR 'user'@'localhost';

Backup + cron + mail

create a use with read-only rights
sudo useradd -r -s /bin/false USERNAME

create this user in db
grant select on database_name.* to 'USERNAME'@'%' identified by 'password';

Mysql realtime status check

Log into mysql server and run

mytop

Drupal db backup

this command will clean cache tables
drush cc all

this command do an actual database backup
drush sql-dump > /path/to/filename.sql

Backuping mysql

MySQL
- database involed

backup
mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]
or better solutions when data lock error
mysqldump --single-transaction