MySQL

MySQL commands for the MySQL console

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

Basic MySQL Info

If you want to setup Databases of PHP Kit like Wordpress or Drupal read the Installation Notes!!! Mostly you have to Change some individual Settings a the my.cnf Config for:

• Memory usage
• Upload Limit
• Safe Modes
• Modules

If you ignore these Infos, you can run into "white" or "blank" Error Pages! On low End servers 1GB RAM its useful to optimize too!

Performance Tuning

For Performance you can install a seperate SSD Drive and mount it as Space for the MySQL Data Tree, checkout where your Distribution hold the Data, mount the Drive to /mysql and move them to the new Path. On the old Tree remove the empty Folder and set a symbolic Link to /mysql. Don't forget to set the mysql:mysql Rights to /mysql !

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;

Delete Users without Password

DELETE FROM mysql.user WHERE user='' or password='';
FLUSH PRIVILEGES;

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