MYSQL Commands

From wiki.linuxonlinehelp.de
Jump to: navigation, search

MYSQL Commands for the MYSQL CONSOLE:

(try to use mysql console instead of phpmyadmin)

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!

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

Better do write a Bash Script to dump every Database for its own! This does not scramble a fresh setup!