Useful MySQL Commands

To create a database, log in as root:

mysql -uroot -p

create database [database_name]; create user ‘[user]’@’%’ identified by ‘[password]’; (% is a wildcard for any host) grant all privileges on [database_name].* to ‘[user]’@’%’ with grant option;

To look at the schema rows in the database:
select table_name,table_rows from information_schema.Tables where table_schema=‘[database_name]’;

To look at all the database variables:
show variables;

Some useful variables are:

set global slow_query_log=‘ON’ set global slow_query_log=‘OFF’ (remember to turn it off) set long_query_time = 0.1 (time in seconds)

Importing and Exporting in MySQL is really easy:

Import

mysql -u[user] -p[password] -h[host] [database_name] < [file_name].dump

Export

mysqldump -u[user] -p[password] -h[host] [database_name] > [file_name].dump

Note: There is no space between the dash parameter names and the parameters. Also note the change of direction of the < > symbols.

If you work with MySQL alot, you may find that at some point you need to import or export a signal table. As far as I know there isn’t a nice and handy mysql command to do this but it is still easy to do it using SED, a language native to linux systems:

1. Export full database.

mysqldump -u[user] -p[password] -h[host] [database_name] > [file_name].dump

2. Use SED command, replacing table_name with the table you want

sed -n -e ‘/DROP TABLE IF EXISTS `[table_name]`/,/UNLOCK TABLES/p’ database_export.dump > table_name.dump

3. Import table the same way that you would import a database

mysql -u[user] -p[password] -h[host] [database_name] < [table_name].dump