MySQL | backup | restore | daily SQL | MySQL | MySQL Enterprise

Disables locking while copying the non-InnoDB files:
[mysqlbackup]
no-locking
After issuing FLUSH TABLES WITH READ LOCK:
We can see one of the following status:

• Flushing tables
• Waiting for table flush
• Waiting for global read lock

Pending FLUSH TABLES WITH READ LOCK because connection has:

• executed LOCK TABLES ... WRITE; for one or more tables
• a long running query (including DDL, simple SELECT, etc.)

Identify Queries Taking Longer Than 10 Seconds:
SELECT * FROM sys.session WHERE command = 'Query' AND time > 10\G
SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME, PROCESSLIST_STATE, PROCESSLIST_INFO FROM performance_schema.threads WHERE PROCESSLIST_COMMAND = 'Query' AND PROCESSLIST_TIME > 10;
SELECT * FROM information_schema.PROCESSLIST WHERE Command = 'Query' AND TIME > 10;
Flush MySQL Host:
flush hosts;
Flush MySQL bin logs:
flush bin logs;
Select current date and time:
select now();
Purge  MySQL Binary Logs:
PURGE BINARY LOGS BEFORE <date and time>;
Get list of tables with particular column:
SELECT DISTINCT TABLE_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME NOT REGEXP "^test_.*(_archive)|(_new)|(_old)$" AND
TABLE_NAME REGEXP "^test_.*" AND
COLUMN_NAME = '<column_name>' AND
TABLE_SCHEMA ='<schema_name>';Retrieve Table Situation:
mysql> SHOW CREATE TABLE <table_name> \G
mysql> SHOW INDEX FROM <table_name> \G 
mysql> SHOW TABLE STATUS WHERE NAME='<table_name>' \G
Grant privileges to user:
GRANT SELECT, SHOW VIEW ON `<DB_NAME>`.* TO '<USER_NAME>'@'%';
OR
GRANT SELECT, SHOW VIEW ON <DB_NAME>.* TO <USER_NAME>;

Revoke privileges from user:
REVOKE  SELECT, SHOW VIEW ON `<DB_NAME>`.* FROM `<USER_NAME>`@`%`;
Note: For revoke statement single quote (') is not working and backtick (`) is only working.
Ref. https://dev.mysql.com/doc/refman/8.0/en/identifiers.html

Generate DDL for View (dump View):

mysql -u<USERNAME> -p -S/mysql/mysql.sock --skip-column-names --batch -e 'select CONCAT("DROP TABLE IF EXISTS ", TABLE_SCHEMA, ".", TABLE_NAME, "; CREATE OR REPLACE VIEW ", TABLE_SCHEMA, ".", TABLE_NAME, " AS ", VIEW_DEFINITION, "; ") table_name from information_schema.views WHERE TABLE_SCHEMA="<DATABASE_NAME>"';

Note: Make certain not to use super use such as root, as DDL will not be generated because of Bug.

Generate DROP TABLE script for many tables:
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_name LIKE 'd%';
Find number of connections:
SHOW STATUS WHERE `variable_name` = 'Threads_connected';
Find Table size:
SELECT TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM information_schema.TABLES WHERE TABLE_SCHEMA = "<DB_NAME>" AND
ORDER BY (DATA_LENGTH + INDEX_LENGTH);
Find if some table is locked:
SHOW OPEN TABLES WHERE
`Table` LIKE '%[TABLE_NAME]%' AND
`Database` LIKE '[DBNAME]' AND In_use > 0;
Find all foreign keys on a table:
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE where
REFERENCED_TABLE_NAME = '<table_name>';
Find all invalid views:
SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'view'  AND table_rows is null AND table_comment like '%invalid%';

Find Largest Table of the database:
SELECT CONCAT(table_schema, '.', table_name),
CONCAT(ROUND(table_rows / 1000000, 2), 'M') ROWS,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA_LENGTH,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') INDEX,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') TOTAL_SIZE, ROUND(index_length / data_length, 2) INDEXFRAC
FROM   information_schema.TABLES
WHERE TABLE_SCHEMA='<DB_NAME>'
ORDER  BY data_length + index_length DESC
LIMIT  5;
Find all table with no primary key:
SELECT t.table_schema,
       t.table_name,
       c.constraint_name,
       c.column_name
FROM information_schema.tables t
     LEFT JOIN information_schema.key_column_usage c
          ON t.table_schema = c.table_schema
             AND t.table_name = c.table_name
             AND c.constraint_name = 'PRIMARY'
 WHERE t.table_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema' )
       AND c.constraint_name IS NULL
       AND t.table_type = 'BASE TABLE';

Ansible Related:
$ssh-copy-id <AD_ID>@<Target_IP_Address>

$ansible all -m ping -u mysql -k
$ansible-playbook mysql8.yml -k -vvv
$ansible-playbook mysql8.yml -vvv

spool out of to text file:
mysql>TEE variables.txt;
mysql>SHOW GLOBAL VARIABLES;
mysql>NOTEE;

Execute script:
mysql>source /mysql/admin/scripts/CreateDB.sql

Execute script in background:
$ mysql -u<user> -p<code> -S/mysql/my.sock -e "source /mysql/qry.sql" report 2>&1 > /mysql/admin/mysql_output.log &

Connect using socket:
#mysql -uroot -p --socket=/mysql/<project_name>/data/<project_name>.sock
$mysql -uroot -p<code> -S/mysql/<project_name>/<project_name>.sock
Connect remote host:
# mysql -h <hostname> -u root -p -P<port_no>

#service mysqld status;
# service mysqld status
#service mysqldb start;
#service mysqld stop;

$ systemctl status mysql
mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysql)
   Active: active (exited) since Sat 2016-04-23 21:13:11 PDT; 1 years 11 months ago
  Process: 968 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=0/SUCCESS)

show databases;
use databases;
show variables;
show tables;

Display current database connection:
select database();
Enable Read Only Mode for Database:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;
Display variables:
show variables like 'innodb%';
Disable logging for DB session at session:
SET SQL_LOG_BIN = 0;
Disable logging for all DB session:
skip-log-bin
Select Date:
select now();
List all users:
SELECT User,Host FROM mysql.user;
Find out hostname from mysql prompt:
show variables like 'hostna%';
Change system variable:
https://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html

set global audit_log_flush='on';
SET global audit_log_statement_policy='ALL';

Generate big dump file which can not be opened if there are large no. of databases and database size is big:

$mysqldump -uroot -p --all-databases --all-tablespaces > F:\MySQL\db_.sql

Execute mysqldump for more than one database:
mysqldump -u<username> -p<code> --databases db_1 db_2  > F:\MySQL\Backup\all_db_bkup.sql

$mysqldump -uroot -p<code> <DB_NAME> --set-gtid-purged=OFF > /mysql/backup/<DB_NAME>_bkup_<date>.sql
Note: without --databases, the dump output contains no CREATE DATABASE or USE statements. Use of --set_gtid_purged=OFF is required to import database from Cluster to non-cluster env.
Execute mysqldump with trigger and routines:

$ mysqldump -uroot -p --set-gtid-purged=OFF -S/mysql/mydb.sock --databases <db_name> --events --triggers --routines /tmp/db.sql
mysqldump with --force and --ignore table option:
$mysqldump: references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
With --force, mysqldump prints the error message, but it also writes an SQL comment containing the view definition to the dump output and continues executing.
$mysqldump -uroot -p --set-gtid-purged=OFF -S /mysql/mysql.sock --databases <db_name> --events \
--triggers --routines --force --ignore-table=schema.table_name --ignore-table=schema.table_name > /mysql/dump/<db_name>.sql
Dump only DDL (no data):
mysqldump -uroot -p --databases <db_name> -S/mysql/<db_name>/data/db_name.sock <DB_NAME> --no-create-db --nodata > /tmp/DDL.sql

Dump Table data and DDL with where condition:

mysqldump -uroot -p -S/mysql/mysql.sock --set-gtid-purged=OFF \
<DB_NAME> <TABLE_NAME> \

--where="ENDDATE between '20200801000000' and '20201101000000'" \
 > /mysql/dump/<schema_name>.<Table_Name>.sql

Find out database size:
SELECT table_schema AS "Database Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size in (MB)" FROM information_schema.TABLES GROUP BY table_schema order by 2 desc;

Restore dump for database:
mysql -uroot -S/mysql/mysql.sock <db_name> < /mysql/bkup/db_bkup.sql
mysql -uroot -S/mysql/mysql.sock <db_name> -vvv < /mysql/bkup/db_bkup.sql
If you encounter following error while importing data into the database from one env. to another env. having different database name, then you need to create user that is missing in the target env.
ERROR 1449 (HY000) at line <line_no>: The user specified as a definer ('<db_name>'@'%') does not exist
Generate Create User script for database mysql:

mysql --silent -uroot -p -S/mysql/mtsprd/data/mtsprd.sock --skip-column-names --execute "SELECT CONCAT('CREATE USER ', QUOTE(user), '@', QUOTE(host),
IF(LENGTH(plugin) > 0, CONCAT(' IDENTIFIED WITH ', plugin, IF(LENGTH(password) > 0,
CONCAT(' AS ', QUOTE(password)), '')), IF(LENGTH(password) > 0,
CONCAT(' IDENTIFIED BY PASSWORD ', QUOTE(password)), '')), ';') FROM mysql.user";

List tables associated with view for MySQL 8.0.13: 
SELECT * FROM information_schema.VIEW_TABLE_USAGE WHERE VIEW_SCHEMA = '<DB_NAME>' and VIEW_NAME='<VIEW_NAME>'; 

MySQL max_connection:
 Explore MySQL Calculator to calculate memory for no. of max_connection.