/ mozey.co / blog

mysql

July 1, 2013

🔗 Find log file locations

List all open files and filter for interesting bits

sudo lsof -nc mysqld | grep -vE '(.so(..*)?$|.frm|.MY?|.ibd|ib_logfile|ibdata|TCP)'

🔗 Database information

select version();

show databases;

use database;

show tables;

describe table;
    
show procedure status like '%xxx%'\G;

show procedure status where DB = "xxx";

🔗 View all foreign keys in table

use information_schema;

select 
table_name,column_name,constraint_name, referenced_table_name,referenced_column_name 
from key_column_usage 
where referenced_table_name = 'table';

🔗 View indexes

Specific table

show index from TABLE_NAME;

Database

select *
from information_schema.statistics
where table_schema = 'DATABASE_NAME'
order by table_name, index_name, seq_in_index;

🔗 Export table to CSV

use DATABASE;
select 'HEADER1'
union all
select COLUMN1
from TABLE
into outfile '/tmp/TABLE.csv'
fields terminated by ','
enclosed by '"'
lines terminated by '\n';

For dealing with lots of columns see Exporting MySQL table into a csv file

🔗 Create table from CSV

Use online converter to create SQL statements from CSV

CSV TO SQL Converter

🔗 Run a script

mysql -u USER -p < script.sql

Or login first then

source script.sql

🔗 Run SQL statement from CLI

mysql -u USER --password=PASSWORD --database=DATABASE -e "show tables;"

🔗 Show last queries executed

How to show the last queries executed on MySQL?

Enable query log dynamically, values lost on server restart

set global log_output = "file";

set global general_log_file = "/tmp/mysql";

set global general_log = 'on';

Create log file and set permissions

touch /tmp/mysql

sudo chown mysql:mysql /tmp/mysql

sudo chmod +rw /tmp/mysql

Check variable values

show session variables like 'log_output';

show global variables like 'general_log';

View log file

cat /tmp/mysql

🔗 Slow Queries

how to use mysql query profiling

Enable slow query log dynamically, values lost on server restart

set global slow_query_log = 'on';
set global slow_query_log_file = '/var/log/mysql/localhost-slow.log';
set global log_queries_not_using_indexes = 'on';
set global long_query_time = 0.000001;
set session long_query_time = 0.000001;
set global min_examined_row_limit = 0;
set session min_examined_row_limit = 0;

Reset log file

echo "" > /var/log/mysql/localhost-slow.log

cat /var/log/mysql/localhost-slow.log

View Top 5 queries Sorted by average time, do not Abstract params

mysqldumpslow -t 5 -s at -a /var/log/mysql/localhost-slow.log

Check variable values

show global variables like 'slow_query_log';

show session variables like 'long_query_time';

pt-query-digest

sudo apt-get install percona-toolkit

pt-query-digest /var/log/mysql/localhost-slow.log

🔗 Query breakdown

Use this to see time per query state

set session profiling = 1;
show profiles;

# Run query

select * from information_schema.profiling where query_id=123;

🔗 Create a gzipped mysql dump

mysqldump  -u USER --password='PASSWORD' DATABASE \
| gzip -c | cat > DATABASE.sql.gz

🔗 mysqldump errcode 24

perror 24

> Too many open files

Solution is to make mysqldump keep only one table open at a time

mysqlddump --single-transaction ...

🔗 Create index

alter table `table` add index `index_name` (`col1`,`col2`)

🔗 Alter table

🔗 Add column

alter table table_name add column_name datatype

🔗 Check if MySQL is running

mysqladmin -u root -p status