Last active
October 24, 2018 01:29
-
-
Save grenoult/4388999067ff9424e06c91d5966877bf to your computer and use it in GitHub Desktop.
MySQL & MySQL Dump common queries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// MySQL & MySQL Dump common commands | |
-- Export specific rows from table | |
mysqldump -u user -p dbname --tables myTable --where="id < 1000" | |
-- Dump: one INSERT per row | |
mysqldump --extended-insert=FALSE | |
-- Dump: display columns names | |
mysql dump --complete-insert=TRUE | |
-- Get last 15 biggest tables of a database | |
SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" | |
FROM information_schema.TABLES | |
WHERE table_schema = "DBNAME" | |
ORDER BY 2 DESC | |
LIMIT 0,15; | |
-- Remove table data from a dump | |
sed '/INSERT INTO `table1`/d' ./YOURDB.sql > YOURDB2.sql | |
sed '/INSERT INTO `table2`/d' ./YOURDB2.sql > YOURDB3.sql | |
sed '/INSERT INTO `table3`/d' ./YOURDB3.sql > YOURDB4.sql | |
sed '/INSERT INTO `table4`/d' ./YOURDB4.sql > YOURDB5.sql | |
-- Get size of Database | |
SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" | |
FROM information_schema.tables | |
GROUP BY table_schema; | |
-- Activate long query log | |
SHOW VARIABLES LIKE '%_query_%'; | |
SET GLOBAL slow_query_log = ON; | |
SET long_query_time = 5; | |
-- Get queries that takes long time to execute. | |
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND <> 'Sleep' AND TIME > 0 | |
-- Truncate query log | |
TRUNCATE table mysql.general_log; | |
-- Activate/Deactivate query log | |
SET global general_log = 1; | |
SET global log_output = 'table'; | |
-- Do you query | |
select * from mysql.general_log; | |
-- Deactivate query log | |
SET global general_log = 0; | |
-- Disable FK | |
SET FOREIGN_KEY_CHECKS=0; | |
-- Enable FK | |
SET FOREIGN_KEY_CHECKS=1; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment