How to enable the slow query log in MySQL & All Queries

https://www.a2hosting.in/kb/developer-corner/mysql/enabling-the-slow-query-log-in-mysql

mysql queriesmysql queries1. mysqldump -u root-p $sw0rdf1sh –all-databases > db_backup.sql2. mysqldump -u root-p $sw0rdf1sh –databases sampledatabase mysql | gzip > db_backup.sql.gz
3. max connection can be calculated by the formula
max.connection=(available RAM-global buffers)/thread buffers
5. To determine the sizes of all of your databases, at the mysql> prompt type the following command: All database
SELECT table_schema AS “Database”, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS “Size (MB)” FROM information_schema.TABLES GROUP BY table_schema;6. To determine the sizes of all of the tables in a specific database, at the mysql> prompt, type the following command. Replace database_name with the name of the database that you want to check: Specific database
SELECT table_name AS “Table”,ROUND(((data_length + index_length) / 1024 / 1024), 2) AS “Size (MB)”FROM information_schema.TABLESWHERE table_schema = “database_name”ORDER BY (data_length + index_length) DESC;After the command finishes, it displays a list of all of the tables and th
7.  Try out the following example to create a database −
<html>   <head>      <title>Connecting MySQLi Server</title>   </head>      <body>      <?php         $dbhost = ‘localhost:3306’;         $dbuser = ‘root’;         $dbpass = ‘<password here>’;         $conn = mysqli_connect($dbhost, $dbuser, $dbpass);            if(! $conn ){            echo ‘Connected failure<br>’;         }         echo ‘Connected successfully<br>’;         $sql = “CREATE DATABASE TUTORIALS”;                  if (mysqli_query($conn, $sql)) {            echo “Database created successfully”;         } else {            echo “Error creating database: ” . mysqli_error($conn);         }         mysqli_close($conn);      ?>   </body></html>
Be careful while deleting any database because you will lose your all the data available in your database.
Here is an example to delete a database created in previous chapter −
[root@host]# mysqladmin -u root -p drop TUTORIALSEnter password:******This will give you a warning and it will confirm if you really want to delete this database or not.
Dropping the database is potentially a very bad thing to do.Any data stored in the database will be destroyed.
Do you really want to drop the ‘TUTORIALS’ database [y/N] yDatabase “TUTORIALS” dropped
8. Finding long-running queries in MySQL query_finder.sql
SELECT id,state,command,time,left(replace(info,’\n’,'<lf>’),120)FROM information_schema.processlistWHERE command <> ‘Sleep’ AND info NOT LIKE ‘%PROCESSLIST%’ORDER BY time DESC LIMIT 50;

Script without using Performance_schema:SELECT  trx.trx_id ,trx.trx_started ,trx.trx_mysql_thread_id
10.   FROM INFORMATION_SCHEMA.INNODB_TRX AS trxINNER JOIN INFORMATION_SCHEMA.PROCESSLIST AS pl  ON trx.trx_mysql_thread_id = pl.idWHERE trx.trx_started < CURRENT_TIMESTAMP – INTERVAL 59 SECOND  AND pl.user <> ‘system_user’;
Script 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 trxINNER JOIN information_schema.processlist pl  ON trx.trx_mysql_thread_id = pl.idINNER JOIN performance_schema.threads th  ON th.processlist_id = trx.trx_mysql_thread_idINNER JOIN performance_schema.events_statements_history esh  ON esh.thread_id = th.thread_idWHERE trx.trx_started < CURRENT_TIME – INTERVAL 59 SECOND  AND pl.user <> ‘system_user’ORDER BY esh.EVENT_ID;
11. As a Database Administrator, this is our responsibility to find all locked and blocked transactions of MySQL Server.
Below is a script (work for MySQL 5.5 and above versions) :SELECT     pl.id    ,pl.user    ,pl.state    ,it.trx_id     ,it.trx_mysql_thread_id     ,it.trx_query AS query    ,it.trx_id AS blocking_trx_id    ,it.trx_mysql_thread_id AS blocking_thread    ,it.trx_query AS blocking_queryFROM information_schema.processlist AS pl INNER JOIN information_schema.innodb_trx AS it ON pl.id = it.trx_mysql_thread_idINNER JOIN information_schema.innodb_lock_waits AS ilw ON it.trx_id = ilw.requesting_trx_id         AND it.trx_id = ilw.blocking_trx_id
11. When DBA is monitoring the database, it requires to find and kill some unwanted processes of MySQL Server.
How to find running processes or sessions?
Below are three different ways:
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != ‘Sleep’;
show  processlist;
/*Execute using MySQL Console*/mysqladmin -u root -p -i 1 processlist;How to kill running processes or sessions?
KILL CONNECTION <processid>
12. This script is very useful for DBA for checking the size of tables and databases.
If you find freer space, DBA has to recover free space.
Please read interesting about MVCC:
Below are scripts:
/* Script for Database */SELECT  table_schema AS DataBase_Name ,ROUND(sum( data_length + index_length ) / 1024 /1024,1) AS OccupiedSize_inMB    ,ROUND(sum( data_free )/ 1024 / 1024,1) AS FreeSpace_inMB FROM information_schema.TABLES GROUP BY table_schema ;
/* Script for Tables */SELECT table_Name  AS Table_Name,   ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS TableSize_InMB   ,ROUND(sum( data_free )/ 1024 / 1024,1) AS FreeSpace_inMBFROM   information_schema.tables WHERE Table_Schema = ‘db_name’

Innodb buffer pool size script 14. I have a rough figure like, if you have 3 GB of InnoDB data and indexes, you require at least 1 GB size of innodb_buffer_pool_size.
Below is a script to calculate the size for InnoDB buffer pool.
SELECT  CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) AS RIBPS FROM( SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine=’InnoDB’) AS T;After executing above statement, You will get one RIBPS value. e.g. result in 2 RIBPS, so you require 2 GB size of the buffer pool.You can change this parameter in the config file and restart your MySQL Service.
InnoDB reserves additional memory for buffers and control the structures, so that the total allocated space is approximately 10% greater than the specified size.

 

 

15. First check the current status of the different Log Parameters:
SHOW VARIABLES LIKE ‘%log%’;Using above statement, you can check the current value of log parameter.
Now enable logging for general query and save the output into “mysql.general_log” table:
SET GLOBAL log_output = ‘TABLE’;SET GLOBAL general_log = ‘ON’;SELECT *FROM mysql.general_log;Now enable logging for long running queries:
SET GLOBAL slow_query_log = 1;SET GLOBAL long_query_time= 1;SET GLOBAL log_queries_not_using_indexes= ON;Sometimes, it is required to execute the FLUSH command of the log to get immediate updates into the Log Table.
FLUSH LOGS;

 

Advertisements

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