Table of Contents

List Users

SELECT USER FROM mysql.user;

Reset Passwords

Sets the password secretpassword for user someuser.

UPDATE mysql.user SET password=PASSWORD('secretpassword') WHERE USER='someuser';

List the Size of Databases

SELECT table_schema "Database", Round(SUM(data_length + index_length) / 1024 / 1024, 1) "MB" FROM information_schema.tables GROUP  BY table_schema;

Select Random Records

The following query, selects one random entry from the table table.

SELECT * FROM TABLE ORDER BY RAND()  LIMIT 1

Select Using RegExp

This query will select lastname and firstname from the table employees with the condition that the first character of lastname match either of the uppercase letters M, B or T:

SELECT lastname, firstname FROM employees WHERE lastname REGEXP  '^(M|B|T)';

Comma-separated Values

A structurally simple format is the CSV format that is supported by many data processing software packages (Excel, etc…) and MySQL can both import and export from CSV.

Export

This line exports data from the table account to the file /tmp/dump.csv in a comma-separated value format.

SELECT * FROM account INTO OUTFILE '/tmp/dump.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

Import

Imports are preformed symmetrically, by preserving the FIELD and LINES setting:

LOAD DATA INFILE '/tmp/dump.csv' INTO TABLE account FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

Dump and Restore a Database

To dump a database to a file, issue:

mysqldump -u root -p database > database.sql

to restore a database from a file, issue:

mysqldump -u root -p database < database.sql

Sum-Up Columns in MySQL Table

Given the following table:

Name Score
Amanda Lay 5
Ty Tass 11
Ty Tass 2
Lou Sirr 7
Amanda Lay 4
Lou Sirr 20

we want to find out the total score for Ty Ass. We can then write the SQL query:

SELECT Name,SUM(Score) AS Total FROM TABLE WHERE Name='Ty Ass';

which will return the following table:

Name Total
Ty Tass 13

The same can be done if the scores are distributed on multiple columns:

Name Crouched Sideways Laying
Amanda Lay 5 7 30

To find out the total, summative score for Amanda Lay, we use the query:

SELECT Name,(Crouched + Sideways + Laying) AS Total FROM TABLE WHERE Name='Amanda Lay';

which will return the table:

Name Total
Amanda Lay 42

Empty a Database Without Dropping

To empty the database mydatabase, issue:

SELECT CONCAT('DROP TABLE IF EXISTS ', TABLE_NAME, ';') FROM information_schema.tables WHERE table_schema = 'mydatabase';

Repair MySQL Tables

mysql -Be "select concat('repair table ', TABLE_SCHEMA, '.', table_name, ';') from information_schema.tables where table_schema<>'information_schema';" | tail -n+2 | mysql

or

mysqlcheck --auto-repair -A

Reset Root Password

First, stop MySQL using:

/etc/init.d/mysql stop

Now we run MySQL by omitting to read the grant tables:

mysqld --skip-grant-tables

And we log-in as root:

mysql -u root

Then we run a query to replace the root password:

FLUSH PRIVILEGES;
UPDATE mysql.user SET Password=PASSWORD('password') WHERE USER='root';
FLUSH PRIVILEGES;
exit

And after that we restart MySQL:

/etc/init.d/mysql restart

Import Multiple SQL Files

In a directory containing .sql files, run:

ls -1 *.sql | awk '{ print "source",$0 }' | mysql --batch -u username --password=password database

where:

Drop all Tables in Database

This can be accomplished with:

mysql -u USER --password=PASSWORD -Nse 'SHOW TABLES' DATABASE | while read table; do mysql -u USER --password=PASSWORD -e "DROP TABLE $table" DATABASE; done

where:

Truncate all Tables in Database

This can be done with:

mysql -u USER --password=PASSWORD -Nse 'show tables' DATABASE | while read table; do mysql -u USER --password=PASSWORD -e "TRUNCATE TABLE $table" DATABASE; done

where: