MySQL

MySQL commands for the MySQL console

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

If you want to show the size of all databases installed do:

login as mysql admin

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 database named "test":

mysql> create database test; 

Delete database named "test":

mysql> drop database test;

Update/Reload Databases at MYSQL Server:

mysql> flush privileges;

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

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

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