Alpha
Share and Organize Code
login register
HotNewTop
F.A.Q.
1. To begin adding code first register.
2. Create your own tags for code that you add.
3. To see your own code click Me, to see all code click Everyone, or filter by people rob.
4. To follow a user, click on their username, rob and hit follow.
5. Add other code to your library by clicking the
button near the code titles (note that you can not edit these codes).
6. To create a copy of a code that you can edit use fork.
7. If you can't find your code, double check your filter settings as you can filter by Category, User and Tag simultaneously.
8. Add comments to a code by clicking on it's title or the comments link underneath it.

List Tables Using MyISAM (plain)

1
2
3
4
5
/* Within MySQL Shell you can use this: */

SELECT table_schema, table_name, engine FROM information_schema.tables WHERE engine='myisam' AND table_schema NOT IN ('mysql','performance_schema','information_schema');

/* This will list all tables using MyISAM excluding default mysql table schemas. */
[+] (1) more...
comments (0) flag fork

Show Table Row Counts (plain)

1
2
SELECT TABLE_SCHEMA,TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA IN ('db1','db2','db3') ORDER BY `TABLE_SCHEMA`, `TABLE_NAME`, `TABLE_ROWS`;
/* This will show you database table counts */
comments (0) flag fork

Size of All tables in MySQL (plain)

1
2
3
4
5
6
7
8
/* Size of ALL Table Schemas in GB and MB */
SELECT round(sum(((data_length + index_length)/1024/1024/1024)),2) GB, round(sum(((data_length + index_length)/1024/1024)),2) MB FROM information_schema.TABLES;

/* Size of ALL Table Schemas Ordered by Table Schema Name */
SELECT TABLE_SCHEMA, TABLE_NAME, round(((data_length + index_length)/1024/1024/1024),2) GB, round(((data_length + index_length)/1024/1024),2) MB FROM information_schema.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME;

/* You can also filter on
Database (Table_Schema) Name, Order by Size, etc */
[+] (4) more...
comments (0) flag fork

SHOW tips (plain)

1
2
3
4
5
6
7
8
9
10
/* From your MySQL client you can get a lot of good info with SHOW cmds */
SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE "%partial_table_name%";
SHOW TABLE STATUS WHERE Rows > 15000;
SHOW TABLE STATUS WHERE Rows > 15000 AND Index_length > 52564;

/* Some probably don't know you can have WHERE clauses in your SHOW cmds */


/* Enjoy */
[+] (6) more...
comments (0) flag fork

Generate GRANT Statements (plain)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#!/bin/bash
mysql -N -ss -u root -p -e"SELECT CONCAT('SHOW GRANTS FOR ', user, '@', host, ';') FROM mysql.user;" > ~/showgrants.sql
sed -i "s/FOR /FOR '/g" ~/showgrants.sql
sed -i "s/@/'@'/g" ~/showgrants.sql
sed -i "s/;/';/g" ~/showgrants.sql
mysql < ~/showgrants.sql > ~/grants.sql

# --- OR

mysql -N -ss -u root -p -e"SELECT CONCAT('SHOW GRANTS FOR ', user, '@', host, ';') FROM mysql.user;" > ~/showgrants.sql
sed "s/FOR /FOR '/g" ~/showgrants.sql > ~/showgrantsa.sql
sed "s/@/'@'/g" ~/showgrantsa.sql > ~/showgrantsb.sql
sed "s/;/';/g" ~/showgrantsb.sql > ~/showgrantsc.sql
mysql -N -ss -u root -p < ~/showgrantsc.sql > ~/grants.sql

# --- Don't forget to add the mysql delimiters in ~/grants.sql since they aren't added by default.
# --- Feel free to alter or improve in anyway



#
[+] (17) more...
comments (1) flag fork

Display Table Schema Sizes (plain)

1
SELECT table_schema, sum(data_length+index_length)/1024/1024/1024 "Gigs" FROM information_schema.tables GROUP BY table_schema;
comments (1) flag fork

MySQL Match Against (plain)

1
2
MATCH(c.code, c.title) AGAINST ('keyword' IN NATURAL LANGUAGE MODE)
MATCH(c.code, c.title) AGAINST ('keyword' IN BOOLEAN MODE)
comments (0) flag fork

Get all counts of counts (plain)

1
2
3
SELECT c.count, count(*) FROM
(SELECT count(*) as count FROM category GROUP BY id) as c
GROUP BY c.count
comments (0) flag fork

Get all records for this year (plain)

1
SELECT * FROM table WHERE  YEAR(recDate) =  YEAR(CURDATE()) AND MONTH(recDate) = MONTH(CURDATE());
comments (0) flag fork

Get all records for this month (plain)

1
SELECT * FROM table WHERE DATE_FORMAT(recDate, '%Y%m') = DATE_FORMAT(NOW(), '%Y%m');
comments (0) flag fork

SELECT, GROUP BY CONCAT, then UPDATE (plain)

1
UPDATE foodservice_recipes SET ingredients=(SELECT GROUP_CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(amount,' '),item),' '),quantity)) AS ingredients FROM foodservice_ingredients WHERE recipe_id=1) WHERE id=1;
comments (0) flag fork

Add and revoke privileges (plain)

1
2
3
4
5
GRANT ALL PRIVILEGES ON *.* TO '<user>'@* IDENTIFIED BY '<password>' WITH GRANT OPTION;

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,RELOAD,PROCESS,FILE,REFERENCES,INDEX,ALTER ON *.* TO '<user>'@'<ip>' IDENTIFIED BY '<password>';

REVOKE SELECT,INSERT,UPDATE,DELETE,CREATE,RELOAD,PROCESS,FILE,REFERENCES,INDEX,ALTER ON *.* FROM '<user>'@'<ip>';
[+] (1) more...
comments (0) flag fork

Import using socket (plain)

1
mysql --socket=/var/lib/mysqld/mysqld1.sock -u root < /home/dump.sql
comments (0) flag fork

Init new database on socket (plain)

1
mysql_install_db --user=mysql --ldata=/var/lib/mysql1
comments (0) flag fork

Connecting on socket (plain)

1
mysql --socket=/var/lib/mysqld/mysqld1.sock -u root -p<password>
comments (0) flag fork

Kill running query (plain)

1
mysql -h localhost -u username -p -e "KILL Id#"
comments (0) flag fork

Show running queries (plain)

1
mysql -h localhost -u username -p -e "SHOW PROCESSLIST"
comments (1) flag fork

Analyze SELECT Statement (plain)

1
2
/* Just add EXPLAIN in front of SELECT... */
EXPLAIN SELECT * FROM db.table;
comments (0) flag fork

Find Worst Indexes in all Databases (plain)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
/*
SQL script to grab the worst performing indexes in all databases
*/
SELECT
  t.TABLE_SCHEMA AS `db`
, t.TABLE_NAME AS `table`
, s.INDEX_NAME AS `index name`
, s.COLUMN_NAME AS `field name`
, s.SEQ_IN_INDEX `seq in index`
, s2.max_columns AS `# cols`
, s.CARDINALITY AS `card`
, t.TABLE_ROWS AS `est rows`
, ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %`
FROM INFORMATION_SCHEMA.STATISTICS s
INNER JOIN INFORMATION_SCHEMA.TABLES t
  ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
  AND s.TABLE_NAME = t.TABLE_NAME
INNER JOIN (
  SELECT
     TABLE_SCHEMA
   , TABLE_NAME
   , INDEX_NAME
   , MAX(SEQ_IN_INDEX) AS max_columns
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA != 'mysql'
  GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
) AS s2
ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA
AND s.TABLE_NAME = s2.TABLE_NAME
AND s.INDEX_NAME = s2.INDEX_NAME
WHERE t.TABLE_SCHEMA != 'mysql'                         /* Filter out the mysql system DB */
AND t.TABLE_ROWS > 10                                   /* Only tables with some rows */
AND s.CARDINALITY IS NOT NULL                           /* Need at least one non-NULL value in the field */
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 /* Selectivity < 1.0 b/c unique indexes are perfect anyway */
ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME          /* Switch to `sel %` DESC for best non-unique indexes */
LIMIT 20;
/*
Change this row limit to whatever you prefer
...
*/
[+] (36) more...
comments (1) flag fork

Change BinLog Format on Live Master DB (plain)

1
2
3
4
5
6
FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED'; /*possible values: STATEMENT, ROW, MIXED*/
FLUSH LOGS;
UNLOCK TABLES;
RESET MASTER; /*This starts the Master over again with new bin log format*/
[+] (2) more...
comments (2) flag fork

Show Locked Tables (plain)

1
2
3
4
/* Display all locked / unlocked tables */
SHOW OPEN TABLES;
/* Display table locks in specific DB */
SHOW OPEN TABLES FROM databaseName WHERE In_use <> 0;
comments (1) flag fork

Generate OPTIMIZE statements for any Fragmented tables (plain)

1
2
3
SELECT CONCAT('optimize table ', table_schema, '.', table_name, ';')
FROM information_schema.tables WHERE table_schema
NOT IN ('information_schema','mysql') AND data_free > 0 ORDER BY table_schema, data_free DESC;
comments (1) flag fork

Find MyISAM tables larger than 4 Gigs (plain)

1
2
3
4
5
SELECT table_schema,table_name,table_type,engine,table_rows,data_length/1024/1024/1024 as "size - Gigs"
FROM information_schema.tables
WHERE engine='MyISAM'
AND data_length > 536870912
ORDER BY table_schema,table_rows DESC;
[+] (1) more...
comments (1) flag fork

Find Fragmented Tables (plain)

1
2
3
SELECT table_schema,table_name,data_free
FROM information_schema.tables where table_schema
NOT IN ('information_schema','mysql') AND data_free > 0 ORDER BY table_schema, data_free DESC;
comments (0) flag fork

ORDER BY with custom (plain)

1
2
3
4
/* place at bottom */
SELECT * FROM categories ORDER BY name='Other', name ASC;
/* place at top */
ORDER BY FIELD(name, 'English/US', 'English/UK', name), name ASC;
comments (0) flag fork