# to see the full CREATE TABLE statement and show table engine
show create table table_name\G
#To see full CREATE TABLE statement on all tables in a database
export database=”dbname”;for table in `mysql -s –column-name=false -e “SHOW TABLES” $database`;do mysql -e “SHOW CREATE TABLE $table” $database;done
#convert a table from MyISAM to InnoDB
ALTER TABLE table_name ENGINE=InnoDB;
#watch the processlist fly by (from the command line/not a MySQL command)
watch “mysql -e ‘show processlist;'”
#List mysql users from the command line.
mysql -u root -B -N -p -e “SELECT user, host FROM user” mysql
#Show grants for particular myaql user instead of the one you are logged in as
SHOW GRANTS [FOR user]
#Add a new user with only SELECT and UPDATE on a specific database
grant SELECT,UPDATE on database.* to ‘user’@’%’ IDENTIFIED BY ‘PASSWORD’;
#Add a new user with all privileges on a specific database
grant all on database.* to user@’IP_ADDRESS’ IDENTIFIED BY ‘PASSWORD’;
#List all tables in all databases
USE information_schema;
SELECT TABLE_SCHEMA, TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN (‘mysql’, ‘information_schema’, ‘performance_schema’) ORDER BY TABLE_SCHEMA, TABLE_NAME;
#Dump all databases to a file
mysqldump –all-databases > /tmp/all.sql
#To import a database
mysql -u username -p -h localhost DATA-BASE-NAME < data.sql
#To turn on slow query logging from the mysql prompt
#Set the long query time to whatever is appropriate, the default is 10 seconds
mysql> SET GLOBAL long_query_time=1;
#Set the location that the slow query log should go to, make sure the folder path exists and that the mysql user has write permissions to it
mysql> SET GLOBAL slow_query_log_file=’/var/lib/mysqllogs/slow-log’;
#And finally set a variables to enable logging
mysql> SET GLOBAL slow_query_log=1;
#To Repair and Optimize all MySql tables:
mysqlcheck –u root –p –auto-repair –check –optimize –all-databases
#To get the sizes of your databases
SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema;
#For extra points, tune your Innodb Buffer Pool accordingly.
#To dump tables t1, t2, t3, t7 from the database test:
mysqldump test t1 t3 t7 > tabledump.sql
#Determine whether a table is indexed:
show index from [table name]
