# to see the full CREATE TABLE statement and show table engine
\nshow create table table_name\\G<\/p>\n
#To see full CREATE TABLE statement on all tables in a database
\nexport database=”dbname”;for table in `mysql -s –column-name=false -e “SHOW TABLES” $database`;do mysql -e “SHOW CREATE TABLE $table” $database;done<\/p>\n
#convert a table from MyISAM to InnoDB
\nALTER TABLE table_name ENGINE=InnoDB;<\/p>\n
#watch the processlist fly by (from the command line\/not a MySQL command)
\nwatch “mysql -e ‘show processlist;'”<\/p>\n
#List mysql users from the command line.
\nmysql -u root -B -N -p -e “SELECT user, host FROM user” mysql<\/p>\n
#Show grants for particular myaql user instead of the one you are logged in as
\nSHOW GRANTS [FOR user]<\/p>\n
#Add a new user with only SELECT and UPDATE on a specific database
\ngrant SELECT,UPDATE on database.* to ‘user’@’%’ IDENTIFIED BY ‘PASSWORD’;<\/p>\n
#Add a new user with all privileges on a specific database
\ngrant all on database.* to user@’IP_ADDRESS’ IDENTIFIED BY ‘PASSWORD’;<\/p>\n
#List all tables in all databases
\nUSE information_schema;
\nSELECT TABLE_SCHEMA, TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN (‘mysql’, ‘information_schema’, ‘performance_schema’) ORDER BY TABLE_SCHEMA, TABLE_NAME;<\/p>\n
#Dump all databases to a file
\nmysqldump –all-databases > \/tmp\/all.sql<\/p>\n
#To import a database
\nmysql -u username -p -h localhost DATA-BASE-NAME < data.sql<\/p>\n
#To turn on slow query logging from the mysql prompt<\/p>\n
#Set the long query time to whatever is appropriate, the default is 10 seconds
\nmysql> SET GLOBAL long_query_time=1;
\n#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
\nmysql> SET GLOBAL slow_query_log_file=’\/var\/lib\/mysqllogs\/slow-log’;
\n#And finally set a variables to enable logging
\nmysql> SET GLOBAL slow_query_log=1;<\/p>\n
#To Repair and Optimize all MySql tables:
\nmysqlcheck \u2013u root \u2013p \u2013auto-repair \u2013check \u2013optimize \u2013all-databases<\/p>\n
#To get the sizes of your databases
\nSELECT 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;
\n#For extra points, tune your Innodb Buffer Pool accordingly.<\/p>\n
#To dump tables t1, t2, t3, t7 from the database test:
\nmysqldump test t1 t3 t7 > tabledump.sql<\/p>\n
#Determine whether a table is indexed:
\nshow index from [table name]<\/p>\n","protected":false},"excerpt":{"rendered":"
# 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 … Continue reading