MySQL quick tips

MySQL useful information:

Info retrieval:

#To retrieve the list of index and primary keys of a table, below query can be used.
show index from tablename;

#To get the list of triggers in the database
show triggers;

#To see triggers related to particular table
show triggers like 'tablename';

#To see the create statement of a table
show create table tablename;

#To create a table just like another existing table
create table newtable like oldtable;

#To see the query execution plan, how MySQL would execute or which index would be used/considered
explain select * from tablename;
Look at the key column to know which index is actually going to be used by MySQL while this query execution. To understand explain output, check http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

#You can force MySQL to use your index, if you wish to.
Have a look at http://dev.mysql.com/doc/refman/5.6/en/index-hints.html

#To see the partitions of table
explain partitions select * from tablename;

#To see all database threads or process or to know what queries are currently being run:
show processlist;

#To know about the database replication status ( slave-IO-thread and Slave-SQL-thread are Yes, then replication is working fine)
show slave status\G

#To know the master co-ordinates and file
show master status\G

# To get the count of rows in all table in a database.
mysqlshow --count -hDBIP -uUSER -p databasename 
     - This will count rows in each table and populate.

Backup:

#To take the backup of MySQL database,
mysqldump --singletransaction -hDBIP -uUSER -p databasename > backupfile.sql

#To take only few particular tables of a database
mysqldump --singletransaction -hDBIP -uUSER -p  databasename table1 table2 > backupfile.sql

#To extract only schema and routines/functions without any data
mysqldump --no-data --routines -hDBIP -uUSER -p databasename > backupfile.sql

#To skip / exclude any tables while doing backup.

mysqldump --singletransaction -hDBIP -uUSER -p  dbname --ignore-table=dbname.table1 --ignore-table=dbname.table2 > backupfile.sql

Restore it:

#To restore database using a backup file
#Login to database server and ensure the file is present in the server.
mysql -uUSER -p databasename < backupfile.sql

Note: You cannot restore few/partial tables out of a backup file which holds data of whole database.
     Always check if there is any error thrown in the output

More options and details are available at the reference link below.


Reference:

http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html