MySQL Database Administration Commands

To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the MySQL server.MySQLimage1

mysql> create database [databasename];

List all databases on the MySQL server.

mysql> show databases;

To delete a database.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Show all records containing the name “Bob” AND the phone number ‘3444444’.

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

Finding all tables in MySQL with a column name in them.

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('first_column','second_column')
AND TABLE_SCHEMA='database_name';

Duplicating a MySQL Table, Indexes and Data.

CREATE TABLE newtable LIKE oldtable;
INSERT newtable SELECT * FROM oldtable;

Selecting a Max Value from a column.

SELECT id, MAX(column)
FROM YourTable
GROUP BY id;

Add column in MySQL.

ALTER TABLE minttec ADD country varchar (15) AFTER email;

Delete column in MySQL.

ALTER TABLE minttec drop country;

Rename table in MySQL.

RENAME TABLE minttec TO tecmint_table;

Display the size of databases (in MB).

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema ;

Display the size of databases (in GB).

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema ;

List the size of every table in every database, largest first.

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

List the size of every table in particular database.

SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = "<database>" ;

MySQL Uptime .

mysql -uroot -p -be"status"|egrep "Uptime"

Adding user and granting permission.

CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass'; 

CREATE USER 'finley'@'%' IDENTIFIED BY 'some_pass';

Granting Privileges.

GRANT USAGE ON *.* TO 'bia_readonly'@'%' IDENTIFIED BY 'XXXXXXX';

GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON `database`.* TO 'user'@'%';
// Read_only privileges:

GRANT SELECT ON database.* TO 'user'@'%' IDENTIFIED BY 'password';

// Read/write privileges:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES ON database.tables TO 'user'@'%' IDENTIFIED BY 'password';

// Access from specific IP's only:
  
GRANT USAGE ON *.* TO 'user'@'11.92.103.%' IDENTIFIED BY 'password';

GRANT SELECT ON reports.* TO 'fineley'@'%' IDENTIFIED BY PASSWORD '*AD0AC42AD201792E61290E2A176BEBC705CB1904' ;

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, CREATE TEMPORARY TABLES ON `database`.* TO 'user'@'11.92.103.%';

GRANT SELECT,UPDATE,INSERT,DELETE ON yourdb.* To your_user@'102.132.1.1' identified by 'password';

//  Granting privileges for Stored Procedures:

GRANT EXECUTE ON PROCEDURE `uidmasterv1_1`.`report_generation` TO 'udapp'@'11.2.66.%' IDENTIFIED BY PASSWORD '*40FD843DE21F5A23A03B6AF316371C74F711736D'

Here: EXECUTE is the Privilage

      report_generation is Stored Proc under uidmasterv1_1

Changing password for user.

UPDATE mysql.user SET Password=PASSWORD('XXXXXXX') WHERE User='backup' ; 
UPDATE mysql.user SET Password=PASSWORD('XXXXXXX') WHERE User='ring61' AND Host='%';
or
use mysql ;

SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');

For Mysql Version 5.7
---------------------

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

Display the privileges of an account.

 SHOW GRANTS FOR 'admin'@'localhost';

Revoke a user permission.

REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

REVOKE SELECT ON logging.* FROM 'ring61'@'%' 

REVOKE SELECT ON notification.* FROM 'ring61'@'%'

Drop a particular user.

DROP USER 'rupesh'@'%'

To list the application user excluding default user.

select user from mysql.user where User NOT IN ('abc','bcs','cby');

To list the application user if exists.

SELECT IF( EXISTS(SELECT * FROM mysql.user WHERE user='dhpt15'), 'Exists' , 'NotExists');

Script for taking grants.

#!/bin/bash
if (( $# != 2 )); then
echo "Usage: $0 INPUTFILENAME PORT USERNAME" >&2
exit 1
fi
#assign argument to variable
file_name=$1
hpost=$2
### Check the existence of the log directory, if the log directory does not exist throw a mail
if [ ! -f $file_name ];then
echo "File does not exist Aborting..."
exit 1
fi
##reset the output file
cat /dev/null > grantsdiff.txt
for i in `cat $file_name`
do
echo $i|tee -a grantsdiff.txt
echo "*****************************************************"|tee -a grantsdiff.txt
### Extracting the grants engaged by mysql instances
mysql -uroot -h$i -pXXXXXXXXXXX -P$hpost --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user where User NOT IN ('root','mysqlbackup') " | sort | \
while read u
do echo " "|tee -a grantsdiff.txt; mysql -uroot -h$i -pXXXXXXXXXXX -P$hpost --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'|tee -a grantsdiff.txt
done
done
Execution :
./grant.sh hostname.txt 3306

Find the host and user’s and print the grants.

mysql -u<user> -h`hostname` -P3306 -pXXXXXXX --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user" | sort >> user_list.txt ; for i in `cat user_list.txt` ; do echo "================GRANTS FOR $i========================="; mysql -umydbm -h`hostname` -P3306 -p$pass --silent --skip-column-names -be "show grants for $i"; done

Skip duplicate errors in replication.

STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

NOTE : 
If you are sure that skipping those errors will not bring your slave inconsistent and you want to skip them ALL, you would add to your my.cnf:

slave-skip-errors = 1062

MySQL connection details.

show processlist;

mysql -uroot -p -be "show processlist;" | awk '{print $2,$4,$5,$3 }' |awk -F ":" '{print $1}'|sort |uniq -c ; done 

// Running Process
SELECT * 
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE COMMAND != 'Sleep';

// Shows all queries running for 5 seconds or more:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND TIME >= 5;
show global status like 'Threads_connected' ;                           // To check how many threads connected to DB right now.

SHOW VARIABLES LIKE '%connections%'                                   // Gives the max No. of connections set for the client application(max_connections)

show variables like 'max_connections';                                 //  total max connections the DB is able to accept.

show variables like 'max_user_connections' ;                           // Gives the max_used_connections by the client application till date.

show variables like 'max_connect_errors';                             // to check user connection error.

set global max_connections = 500;                                   // incresing max_connect globally as per requirement.

SHOW GLOBAL STATUS LIKE'%thread%'                                      Threads_cached     ---- Gives the total thread cached by the application currently
                                                                       Threads_connected  ---- Gives the total no of connections used by the application including sleep connections.
                                                                       Threads_created    ---- Gives the total threads created by application till date.
	                                                               Threads_running    ---- No. of useful Threads for query execution.
mysql -uroot -p  -be "show global status like 'Threads_connected' ;show variables like 'max_connections';show variables like 'max_user_connections' ;show variables like 'max_connect_errors';" --skip-column-names --silent ; 

Remove or purge binary logs.

PURGE BINARY LOGS TO 'BINLOG.015689';

But you could prune by date:

PURGE BINARY LOGS BEFORE '2013-08-24 00:06:00';

PURGE BINARY LOGS BEFORE '2013-04-21';

Setting expire binary logs.

SHOW GLOBAL VARIABLES LIKE '%expire%' 
SET GLOBAL expire_logs_days = 3;

List out the tables with different storage engines.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database' AND ENGINE = 'MyISAM'; 

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database' AND ENGINE = 'MyISAM'; 

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database' AND ENGINE = 'memory';

Syntax for MySQL dump of database.

//Simple dump of database and table 
mysqldump -u<user> -p <database> <table> > dumpfile.sql

//Full Database dump 
mysqldump -h<hostname> -u<user> -p  --single-transaction  --routines --triggers  --all-databases  >  fulldump.sql

// --no-create-info: Taking <user> without Create Table info and only Content of tables.(Do not write CREATE TABLE statements that re-create each <user>ed table)

mysqldump -h<hostname> -u<user> -p --single-transaction --no-create-info --databases test > /var/lib/mysql/dumpfile_3306.sql 2>> /var/lib/mysql/dumpfile_3306.log 

//  --no-data: Taking <user> with out Data and only Table Structure of a Schema .(Do not <user> table contents)

mysqldump -h<hostname> -u<user> -p --single-transaction --no-data --databases test > /var/lib/mysql/dumpfile_3306.sql 2>> /var/lib/mysql/dumpfile_3306.log
 
//  --master-data: Write the binary log file name and position to the output

mysqldump -h<hostname> -u<user> -p  -P3306  --single-transaction --routines --triggers --master-data=2  --databases <db1> <db2>  > dumpfile_3306.sql 2>> dumpfile_3306.log

Dump routines and triggers only (prompting for password):

mysqldump -h<hostname> -u<user> -p --routines --triggers --no-create-info --no-data --no-create-db --skip-opt --databases ddb > outputfile.sql

Dump routines only:

 mysqldump -h<hostname> -u<user> -p --routines --skip_triggers --no-create-info --no-data --no-create-db --skip-opt --databases ddb > outputfile.sql

Ignore few tables in database

mysqldump -u root -p db_name --ignore-table=db_name.table_name > dump_db.sql

Dump data in tab format 

mysqldump -u root -p --tab=/var/lib/mysql-files/ db_name table_name > data.txt

Shell script for MySQL dump.

#!/bin/sh
DUMP_HOST=<hostname>; export DUMP_HOST ;
DUMP_PORT=3306 ; export DUMP_PORT ;
FILENAME=`date +"%d_%m_%Y_%H_%M_%S".sql`

DBNAMES=`mysql -u<user> -h$DUMP_HOST -P$DUMP_PORT -p<password> -be "show databases" --skip-column-names --silent |egrep -v 'information_schema|mysql|performance_schema' |awk 'ORS=NR%4?" ":"\n"'`;

mysqldump -u<user> --triggers --routines --single-transaction --master-data=1 --databases $DBNAMES -h$DUMP_HOST -P$DUMP_PORT -p<password> > /db/dumps/mysql/3306/$FILENAME
 #!/bin/sh
echo "TESTDATABASE bkp started: " `date` >> /mysql/opt/Testdatabasedump.log
mysqldump -h10.3.60.20 -udump -p'dump!@#' --single-transaction --databases TESTDATABASE > /mysql/opt/TESTDATABASE_bkp_07June.sql 2>> /mysql/opt/Testdatabasedump.log
echo "TESTDATABASE bkp completed: " `date` >> /mysql/opt/Testdatabasedump.log


Creates a Log file(Testdatabasedump.log) and sql file(TESTDATABASE_bkp_07June.sql) in the path mentioned.(Here mysql/opt/)

vi Testdatabasedump.log
TESTDATABASE bkp started: Thu Jun 7 10:22:20 IST 2012
TESTDATABASE bkp completed: Thu Jun 7 20:29:06 IST 2012

Taking dump of particular columns of particular tables of a database.

mysqldump -h [hostname] --single-transaction -u [username] -p [password] [databasename] [table1 table2 ....] --where [column1 column2] > [backupfile.sql]

eg: mysqldump -h<hostname> -u<user> -p --single-transaction <database> <table1> <table2 > --where outbd_tracker_key = 2 and last_updated_date = uid.sql

mysqldump with select range.

mysqldump -uroot -p --triggers --routines --single-transaction coremine table1 --where="clip_date between '2017-05-01' and '2017-06-30';" > table1.sql

select count(*) from coremine.table1 where clip_date between '2017-05-01' and '2017-06-30';

mysqldump without locking tables

mysqldump -u root -p --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql

Executing a dump file.

mysql -u<user> -p <database> < uid.sql  

Executing the dump from mysql prompt

mysql> source uidprocess_archive.sql

Extracting single database dump from sql file.

sed -n '/^-- Current Database: `db_inlive`/,/^-- Current Database: `/p' fullbackup.dec12-507pm.sql > db_inlive-706pm.sql 

Resetting MySQL root password ( Prior to version 8 )

1. Stop Mysql Service 

sudo /etc/init.d/mysql stop                  //(Ubuntu and Debian)

sudo /etc/init.d/mysqld stop                 //(CentOS, Fedora, and Red Hat Enterprise Linux)


2. Start MySQL without a password

sudo mysqld_safe --skip-grant-tables &

OR 

vi /etc/my.cnf 

--skip-grant-tables

sudo /etc/init.d/mysqld start

3.  Set a new MySQL root user password:

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Display the current date and time.

SELECT TIME_FORMAT(NOW(), '%H:%i:%s');

SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') ;

Creating new table and copy the data from old table.

CREATE TABLE node_new SELECT * FROM node;

List users in MySQL database

select host,
       user as username,
       plugin as auth_type,
       authentication_string,
       password_last_changed,
       password_expired,
       account_locked
from mysql.user
order by user;

OR

select user,host from mysql.user ;

Get the list of permissions of all MySQL users on all databases.

SELECT
 mu.User,
 mu.Host,
 md.db `Database`,
 REPLACE(RTRIM(CONCAT(
 IF(md.Select_priv = 'Y', 'Select ', ''),
 IF(mu.Select_priv = 'Y', 'Select ', ''),
 IF(md.Insert_priv = 'Y', 'Insert ', ''),
 IF(md.Update_priv = 'Y', 'Update ', ''),
 IF(md.Delete_priv = 'Y', 'Delete ', ''),
 IF(md.Create_priv = 'Y', 'Create ', ''),
 IF(md.Drop_priv = 'Y', 'Drop ', ''),
 IF(mu.Reload_priv = 'Y', 'Reload ', ''),
 IF(mu.Shutdown_priv = 'Y', 'Shutdown ', ''),
 IF(mu.Process_priv = 'Y', 'Process ', ''),
 IF(mu.File_priv = 'Y', 'File ', ''),
 IF(md.Grant_priv = 'Y', 'Grant ', ''),
 IF(md.References_priv = 'Y', 'References ', ''),
 IF(md.Index_priv = 'Y', 'Index ', ''),
 IF(md.Alter_priv = 'Y', 'Alter ', ''),
 IF(mu.Show_db_priv = 'Y', 'Show_db ', ''),
 IF(md.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),
 IF(mu.Super_priv = 'Y', 'Super_priv ', ''),
 IF(md.Lock_tables_priv = 'Y', 'Lock_tables ', ''),
 IF(md.Execute_priv = 'Y', 'Execute ', ''),
 IF(mu.Repl_slave_priv = 'Y', 'Repl_slave ', ''),
 IF(mu.Repl_client_priv = 'Y', 'Repl_client ', ''),
 IF(md.Create_view_priv = 'Y', 'Create_view ', ''),
 IF(md.Show_view_priv = 'Y', 'Show_view ', ''),
 IF(md.Create_routine_priv = 'Y', 'Create_routine ', ''),
 IF(md.Alter_routine_priv = 'Y', 'Alter_routine ', ''),
 IF(mu.Create_user_priv = 'Y', 'Create_user ', ''),
 IF(mu.Execute_priv = 'Y', 'Execute ', ''),
 IF(md.Event_priv = 'Y', 'Event ', ''),
 IF(mu.Create_tablespace_priv = 'Y', 'Create_tablespace ', ''),
 IF(md.Trigger_priv = 'Y', 'Trigger ', '')
 )), ' ', ', ') AS `Privileges`
FROM mysql.user mu LEFT OUTER JOIN mysql.db md ON mu.USER=md.USER AND mu.HOST=md.HOST ORDER BY mu.USER ASC;

Note:Gives 'NULL' in DB Column if privileges on all DB
     No values in Privileges Column means Table level Privilege for that DB.

For Versions 5.6
--------------------

SELECT
 mu.User,
 mu.Host,
 md.db `Database`,
 REPLACE(RTRIM(CONCAT(
 IF(md.Select_priv = 'Y', 'Select ', ''),
 IF(mu.Select_priv = 'Y', 'Select ', ''),
 IF(md.Insert_priv = 'Y', 'Insert ', ''),
 IF(md.Update_priv = 'Y', 'Update ', ''),
 IF(md.Delete_priv = 'Y', 'Delete ', ''),
 IF(md.Create_priv = 'Y', 'Create ', ''),
 IF(md.Drop_priv = 'Y', 'Drop ', ''),
 IF(mu.Reload_priv = 'Y', 'Reload ', ''),
 IF(mu.Shutdown_priv = 'Y', 'Shutdown ', ''),
 IF(mu.Process_priv = 'Y', 'Process ', ''),
 IF(mu.File_priv = 'Y', 'File ', ''),
 IF(md.Grant_priv = 'Y', 'Grant ', ''),
 IF(md.References_priv = 'Y', 'References ', ''),
 IF(md.Index_priv = 'Y', 'Index ', ''),
 IF(md.Alter_priv = 'Y', 'Alter ', ''),
 IF(mu.Show_db_priv = 'Y', 'Show_db ', ''),
 IF(md.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),
 IF(mu.Super_priv = 'Y', 'Super_priv ', ''),
 IF(md.Lock_tables_priv = 'Y', 'Lock_tables ', ''),
 IF(md.Execute_priv = 'Y', 'Execute ', ''),
 IF(mu.Repl_slave_priv = 'Y', 'Repl_slave ', ''),
 IF(mu.Repl_client_priv = 'Y', 'Repl_client ', ''),
 IF(md.Create_view_priv = 'Y', 'Create_view ', ''),
 IF(md.Show_view_priv = 'Y', 'Show_view ', ''),
 IF(md.Create_routine_priv = 'Y', 'Create_routine ', ''),
 IF(md.Alter_routine_priv = 'Y', 'Alter_routine ', ''),
 IF(mu.Create_user_priv = 'Y', 'Create_user ', ''),
 IF(mu.Execute_priv = 'Y', 'Execute ', ''),
 IF(md.Event_priv = 'Y', 'Event ', ''),
 IF(mu.Create_tablespace_priv = 'Y', 'Create_tablespace ', ''),
 IF(md.Trigger_priv = 'Y', 'Trigger ', '')
 )), ' ', ', ') AS `Privileges`
FROM mysql.user mu LEFT OUTER JOIN mysql.db md ON mu.USER=md.USER AND mu.HOST=md.HOST ORDER BY mu.USER ASC;

Display MySQL table level privileges.

SELECT USER,HOST,db,table_name,Table_priv FROM mysql.tables_priv ORDER BY USER ASC;

Read MySQL binary log events.

mysqlbinlog localhost-bin.000004 > filename.txt

mysqlbinlog --start-datetime="2008-05-22 00:00:00" --stop-datetime="2008-05-29 00:00:00" localhost-bin.000004

mysqlbinlog --read-from-remote-server -u<user> -p mysql-bin.000001 -h 192.168.10.1 -P 3306 --start-position=932 --stop-position=1132

// Greping a perticular file from binlogs:

mysqlbinlog mysql-bin.000004 |grep -n "1098901030032620120620162056.zip"

//Decoding the binary format in binlog.

mysqlbinlog --base64-output=decode-rows -v mysql-bin.001928 > mysql-bin_001928.txt 

Avoid the mysql binlogs to be created for session.

set sql_log_bin=0;

Find trigger in a particular database.

select * from information_schema.triggers where 
information_schema.triggers.trigger_name like '%trigger_name%' and 
information_schema.triggers.trigger_schema like '%data_base_name%'

SHOW TRIGGERS LIKE '%trigger_name%'\G

Show all triggers in database:

SHOW TRIGGERS\G

List Triggers by table name

select event_object_schema as database_name,
       event_object_table as table_name,
       trigger_name,
       action_order,
       action_timing,
       event_manipulation as trigger_event,
       action_statement as 'definition'
from information_schema.triggers 
where trigger_schema not in ('sys','mysql')
order by database_name,
         table_name;

List stored procedures and functions

select routine_schema as database_name,
       routine_name,
       routine_type as type,
       data_type as return_type,
       routine_definition as definition
from information_schema.routines
where routine_schema not in ('sys', 'information_schema',
                             'mysql', 'performance_schema')
    -- and r.routine_schema = 'database_name' -- put your database name here
order by routine_schema,
         routine_name;

List stored procedures

    SELECT 
        * 
    FROM information_schema.routines 
    WHERE ROUTINE_TYPE = 'PROCEDURE' ;

Finding tables without primary keys.

select table_schema,table_name from information_schema.columns group by table_schema,table_name having sum(if(column_key in ('PRI','UNI'), 1,0)) = 0;
USE INFORMATION_SCHEMA;

SELECT TABLES.table_name FROM TABLES
LEFT JOIN KEY_COLUMN_USAGE AS c 
ON (
       TABLES.TABLE_NAME = c.TABLE_NAME
   AND c.CONSTRAINT_SCHEMA = TABLES.TABLE_SCHEMA
   AND c.constraint_name = 'PRIMARY'
)
WHERE TABLES.table_schema <> 'information_schema' AND TABLES.table_schema <> 'performance_schema' AND TABLES.table_schema <> 'mysql' AND c.constraint_name IS NULL;

Saving a query result to .txt and csv

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'


mysql your_database --password=foo < my_requests.sql > out.csv

Command to see uncommented variables in my.cnf

cat /etc/my.cnf |grep -v ^# |grep -v ^$

Find last update time of table (MyISAM).

SELECT UPDATE_TIME FROM   information_schema.tables WHERE  TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'tablename' ;

Count(*) of each table in particular database.

SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dmoz__db';

Table count in each database.

SELECT IFNULL(table_schema,'Total') "Database",TableCount FROM (SELECT COUNT(1) TableCount,table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') GROUP BY table_schema WITH ROLLUP) A;

Innodb buffer pool hit rate.

1- (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)

SELECT round ((P2.variable_value / P1.variable_value),4) , P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

Check MySQL slave status.

mysql -uroot -pXXXXX -e "show slave status \G" | grep -e" Slave_IO_Running:" -e"Slave_SQL_Running:" -e"Seconds_Behind_Master:" -e"Slave_SQL_Running_State"

or

SHOW SLAVE STATUS \G

Setting event using event scheduler.

1.Enable Mysql Event Scheduler:
--------------------------------
SET GLOBAL event_scheduler = ON;
Or
SET GLOBAL event_scheduler = 1;

2.Create an Event:
-------------------
Event will run everyday and clear/delete 10 days old data based on time stamp.

CREATE EVENT newEvent
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM cart WHERE created <= DATE_SUB(NOW(), INTERVAL 10 DAY) ; 

3.Alter Events:
----------------
ALTER newEvent
ON SCHEDULE EVERY 5 HOUR
STARTS TIMESTAMP + 3 HOUR

4.View Events:
---------------
SHOW EVENTS;

5.Delete Events:
-----------------
DROP EVENT newEvent;

Find MySQL locking.

SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,
      r.trx_query waiting_query,
      b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,
      b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b  ON  b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r  ON  r.trx_id = w.requesting_trx_id;
SET @threshold = 30;

SELECT p.user,
  LEFT(p.HOST, LOCATE(':', p.HOST) - 1) host, p.id,
      TIMESTAMPDIFF(SECOND, t.TRX_STARTED, NOW()) duration,
      COUNT(DISTINCT ot.REQUESTING_TRX_ID) waiting
    FROM INFORMATION_SCHEMA.INNODB_TRX t
    JOIN INFORMATION_SCHEMA.PROCESSLIST p
      ON ( p.ID = t.TRX_MYSQL_THREAD_ID )
    LEFT JOIN INFORMATION_SCHEMA.INNODB_LOCK_WAITS ot
      ON ( ot.BLOCKING_TRX_ID = t.TRX_id )
    WHERE t.TRX_STARTED + INTERVAL @threshold SECOND <= NOW()
    GROUP BY LEFT(p.HOST, LOCATE(':', p.HOST) - 1), p.id, duration
    HAVING duration >= @threshold OR waiting > 0;
select * from information_schema.INNODB_LOCKs;
select * from information_schema.INNODB_LOCK_WAITS ;
select * from information_schema.INNODB_TRX;

Time taken by user to execute a particular command (eg: ALTER table).

select ID,USER,DB,sec_to_time(TIME) as running_since,state from information_schema.processlist where State='altering table';

Create an Index.

Create index loy_agent_idx1  ON table1 (loyalty_card(50));

List all indexes in MySQL database

select index_schema,
       index_name,
       group_concat(column_name order by seq_in_index) as index_columns,
       index_type,
       case non_unique
            when 1 then 'Not Unique'
            else 'Unique'
            end as is_unique,
        table_name
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql',
                           'performance_schema', 'sys')
group by index_schema,
         index_name,
         index_type,
         non_unique,
         table_name
order by index_schema,
         index_name;

List views in Database

select table_schema as database_name,
       table_name as view_name
from information_schema.views
where table_schema not in ('sys','information_schema',
                           'mysql', 'performance_schema')
        -- and table_schema = 'database_name' 
order by table_schema,
         table_name;

Check the view definition .

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'VIEW1' ;

List tables used by a view

select vtu.view_schema as database_name,
       vtu.view_name as view_name,
       vtu.table_schema as referenced_database_name,
       vtu.table_name as referenced_object_name,
       tab.table_type as object_type
from information_schema.view_table_usage vtu
join information_schema.tables tab on vtu.table_schema = tab.table_schema
                                   and vtu.table_name = tab.table_name
where view_schema not in ('sys','information_schema',
                          'mysql', 'performance_schema')
    -- and tab.table_schema = 'database_name' -- put your database name here
order by vtu.view_schema,
         vtu.view_name;

Alter table.

ALTER TABLE rep_cpg_xu_association MODIFY COLUMN id BIGINT UNSIGNED AUTO_INCREMENT;

Query General log.

select event_time,user_host,command_type,argument from mysql.general_log where event_time like'2017-12-0%' and argument like '%DELETE FROM%'order by event_time desc;

Check whether a table needs fragmentation.

select  ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables  where  DATA_FREE > 0;

The DATA_LENGTH and INDEX_LENGTH are the space your data and indexes are using, and DATA_FREE is the total amount of bytes unused in all the table pages (fragmentation).

select  ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free, (data_free/(index_length+data_length)) as frag_ratio from information_schema.tables  where  DATA_FREE > 0 order by frag_ratio desc;

Innodb Engine Status.

SHOW ENGINE INNODB STATUS;

Slow Query log Status.

SHOW GLOBAL VARIABLES WHERE VARIABLE_NAME LIKE 'slow_query%' OR VARIABLE_NAME LIKE 'long_query_time';

Long running query.

SELECT id,state,command,time,left(replace(info,'\n','<lf>'),120)
FROM information_schema.processlist
WHERE command <> 'Sleep' 
AND info NOT LIKE '%PROCESSLIST%'
ORDER BY time DESC LIMIT 50;
Find long running query since last 59 seconds :

SELECT 
	trx.trx_id
	,trx.trx_started
	,trx.trx_mysql_thread_id
FROM INFORMATION_SCHEMA.INNODB_TRX AS trx
INNER JOIN INFORMATION_SCHEMA.PROCESSLIST AS pl 
	ON trx.trx_mysql_thread_id = pl.id
WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 59 SECOND
  AND pl.user <> 'system_user';
//using performance Schema 
SELECT 
	pl.id 'PROCESS ID'
	,trx.trx_started
	,esh.event_name 'EVENT NAME'
	,esh.sql_text 'SQL'
FROM information_schema.innodb_trx AS trx
INNER JOIN information_schema.processlist pl 
	ON trx.trx_mysql_thread_id = pl.id
INNER JOIN performance_schema.threads th 
	ON th.processlist_id = trx.trx_mysql_thread_id
INNER JOIN performance_schema.events_statements_history esh 
	ON esh.thread_id = th.thread_id
WHERE trx.trx_started < CURRENT_TIME - INTERVAL 59 SECOND
  AND pl.user <> 'system_user'
ORDER BY esh.EVENT_ID;

List events on MySQL server

select event_schema as database_name,
       event_name,
       event_definition,
       event_type,
       concat(interval_value, ' ', interval_field) as 'interval',
       case when event_type = 'RECURRING'
                 then starts
            else execute_at end as execute_time,
       ends,
       status
from information_schema.events
-- where event_schema  = 'your database name'
order by event_schema,
         event_name;

Temp Table Disk Ratio.

select ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Created_tmp_disk_tables')*100/ ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Created_tmp_disk_tables')+(select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='created_tmp_tables'))) as Tmp_disk_tables_ratio; 

Select the last entry in a table.

SELECT * FROM table_name ORDER BY unique_column DESC LIMIT 1

Select first entry in a table.

SELECT * FROM table_name ORDER BY unique_column ASC LIMIT 1

Connection usage Rate.

select ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='threads_connected')/(select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME='max_connections'))*100 as connection_usage_rate ;

Setting  slave worker threads for executing replication events in parallel.

When parallel execution is enabled, the slave SQL thread acts as the coordinator for the slave worker threads, among which transactions are distributed on a per-database basis. This means that a worker thread on the slave slave can process successive transactions on a given database without waiting for updates to other databases to complete.Due to the fact that transactions on different databases can occur in a different order on the slave than on the master, checking for the most recently executed transaction does not guarantee that all previous transactions from the master have been executed on the slave.

Enable parallel replication using all available cores on the server:

STOP SLAVE;
SET GLOBAL slave_parallel_workers = 7 ; 
START SLAVE;

mysql -uroot -pXXXXXXX -e "show slave status \G" | grep -e" Slave_IO_Running:" -e"Slave_SQL_Running:" -e"Seconds_Behind_Master:" -e"Slave_SQL_Running_State:" -e"Master_Log_File:" -e"Relay_Log_File" -e"Relay_Master_Log_File" -e"Read_Master_Log_Pos" -e"Relay_Log_Pos"

 

View Global variables  that are configured in server ( version 8.0) .

SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE
FROM performance_schema.variables_info t1
JOIN performance_schema.global_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_SOURCE != 'COMPILED';

 

View Session variables  that are configured in server ( version 8.0) .

SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE
FROM performance_schema.variables_info t1
JOIN performance_schema.session_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_SOURCE = 'DYNAMIC';

Find recently created tables in database

select table_schema as database_name,
       table_name,
       create_time
from information_schema.tables
where create_time > adddate(current_date,INTERVAL -60 DAY)
      and table_schema not in('information_schema', 'mysql',
                              'performance_schema','sys')
      and table_type ='BASE TABLE'
      -- and table_schema = 'your database name' 
order by create_time desc,
         table_schema;

How to Fix a Lagging MySQL Replication

Replication lag is a frequent issue with loaded MySQL clusters. It can become critical when the lag gets too important: missing data when the slaves are used for reading, temporary data loss when losing the master.

Make the following configuration changes when slave is lagging too much

STOP SLAVE;
set GLOBAL sync_binlog=0;
SET GLOBAL slave_parallel_workers=8 ;
SET GLOBAL innodb_flush_log_at_trx_commit=2;
SET GLOBAL innodb_flush_log_at_timeout=1800;`
START SLAVE;

For ACID compliance, MySQL writes the contents of the InnoDB log buffer out to the log file at each transaction commit, then the log file is flushed to disk. Setting innodb_flush_log_at_trx_commit to 2 makes the flush happen every second (depending on the system load). This means that, in case of crash, innodb will have to replay all the non commited transactions (up you one second here).
innodb_flush_log_at_trx_commit=2 works in pair with innodb_flush_log_at_timeout. With this setting, we ensure MySQL writes and flushes the log every 1800 second.

We can also try changing slave_parallel_type to LOGICAL_CLOCK ( version 5.7 and above)

STOP SLAVE;
SET GLOBAL slave_parallel_type = LOGICAL_CLOCK;
START SLAVE ;

Find table without primary and unique key

SELECT t.table_schema,t.table_name,engine
FROM information_schema.tables t INNER JOIN information_schema .columns c
on t.table_schema=c.table_schema and t.table_name=c.table_name
GROUP BY t.table_schema,t.table_name
HAVING sum(if(column_key in ('PRI','UNI'), 1,0)) =0;

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: