MySQL

MySQL commands for the MySQL console

Always use mysql "console" instead of "phpmyadmin" for Security reasons!!!

List Size of all Databases

Login as mysql user admin do:

mysql -u admin -p

at the mysql prompt do:

mysql> 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;

Create new Database

mysql> create database test; 

Delete Database

mysql> drop database test;

Reload User Privileges

mysql> flush privileges;

Show Tables List of a Database

mysql>use databasename; #change to the Database

mysql>show tables;      #list Tables to control

Dump all Databases

Note: This do only if you want Restore the same "named" Server, cause MySQL System Databases like "mysql" or "information_schema" are dumped too!! This will break on other MySQL Servers!

mysql > mysqldump -u root -p --opt --all-databases > databases-dump-$date.sql

Remark: It's better you write a Bash Script to dump every Database for its own! This does not scramble a fresh Setup!

User Handling

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON db.* TO ‘user’@'localhost’;

UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='username'; FLUSH PRIVILEGES;

List Users

Login to MySQL Console as Admin, go into the System Database called mysql, and list.

mysql -u mysqladminname -p

use mysql;

select * from mysql.user;

Special Characters Names Users Databases Problems

These Characters can run you into problems cause Signs like Quotes (` ´ ) are NOT DISPLAYED at the mysql Console!

Character   Escape Sequence
\0  An ASCII NUL (0x00) character.
\'  A single quote (“'”) character.
\"  A double quote (“"”) character.
\b  A backspace character.
\n  A newline (linefeed) character.
\r  A carriage return character.
\t  A tab character.
\Z  ASCII 26 (Control-Z). See note following the table.
\\  A backslash (“\”) character.
\%  A “%” character. See note following the table.
\_  A “_” character. See note following the table.

Check Privileges

do:

SHOW GRANTS FOR 'user'@'localhost';

Revoke Grant Privileges

do:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost';

Wordpress Privileges

GRANT SELECT , INSERT , UPDATE , DELETE ON  wordpress.* TO 'user'@'localhost';

Drupal Privileges

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON 'drupal'.* TO 'user'@'localhost';