Mysql useful notes

to get only the DDL for all tables in Database without data

mysqldump -u -p -h host --no-data --skip-opt db

terrible issue i faced by using the above command.. the AUTO-INCREMENT and other options where not shown up in the dump .. im not going to use skip-opt ..

mysqldump -u -p -h host --no-data db

to get only the data-dump as inserts for a table

mysqldump -u -p -h host --no-create-info --skip-opt db table-name

sometimes your user access didnt allow to use mysqldump .. in this scenario use

--single-transaction

clause

to get the tables order by row count..

select table_schema,table_name,count(table_rows) from information_schema.tables where table_schema not in ('information_schema','performance_schema','mysql') group by 1,2 order by 1,3 desc;

Run sql as string from command line

mysql -u user -p -e ‘SQL Query’ Database_Name

to export to a file

mysql -u user -p -e ‘SQL Query’ Database_Name > filename

Performance pointers
Beware of running ANALYZE while there is some long running queries on the table. In MySQl if you have a long running query accessing the table and you run ANALYZE TABLE, it is unable to access the table with lock until the first query completes. So Analyze will wait.

There is a good possibility that a long running query was triggered when we are trying to analyze the table. We can schedule all 'Analyze' specifically the tables serving end-user at off-time.