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

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';

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;

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

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));

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;

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"

 

 

 

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: