MySQL

MySQL Reference
Useful commands to check on mysql mysqladmin -u root -p status systat -vmstat

SET PASSWORD FOR root@localhost=PASSWORD('rubberchicken');
 * Setting root password

or mysql> create database [databasename] Example: # mysqladmin create mydatabase [Enter]
 * Creating a Database
 * 1) mysqladmin create [databasename]


 * Dropping (Removing) a Database
 * 1) mysqladmin drop [databasename]

Example: # mysqladmin drop mydatabase [Enter]


 * Populating an Existing Database from a *.sql File
 * 1) mysql [databasename] < [databasedumpfile.sql]

Example: # mysql mydatabase < mydatabase.sql [Enter]

mysqldump --add-drop-database --add-drop-trigger --create-options --complete-insert --extended-insert --routines --tz-utc -h 127.0.0.1 -u root -p --databases mydb > /tmp/mydb.mysqldump
 * Dumping Database Structure and Data to a file

From within the MySQL interface

 * Starting MySQL from the Command Line
 * 1) mysql

Example: mysql [Enter]

You will be welcomed with the following message:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is ## to server version: #.##.##

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

The prompt changes to "mysql>", which will be shown in each example below. mysql> show databases;
 * Seeing What Databases are Available

Example: >mysql show databases; [Enter]

(be sure to use the semi-colon to terminate the command)

mysql> use [databasename];
 * Telling MySQL to Use a Specific Database

Example: mysql> use mydatabase; [Enter]

mysql> show tables;
 * Seeing What Tables are Available Within a Database

Example: mysql> show tables; [Enter]

mysql> select * from [tablename];
 * Looking at the Data in a Particular Table

Example: mysql> select * from lastname; [Enter]

mysql> grant all privileges on [databasename].* to [dbusername]@localhost identified by '[dbpassword]';
 * Adding a Database User with Password

Example: grant all privileges on mydatabase.* to joeuser@localhost identified by 'supersecretpasswd';

mysql> DELETE FROM mysql.user WHERE user='username' and host='localhost'; FLUSH PRIVILEGES;
 * Removing a Database User

Example: DELETE FROM mysql.user WHERE user='techgeek' and host='localhost'; FLUSH PRIVILEGES;


 * Performing Search-and-Replace Actions on a Table

mysql> update DatabaseName set TableName = REPLACE(TableName,"[String to Search For]","[String Replacement]");

Example: mysql> update roesublist set Grades = REPLACE(Grades,"Grades: Any","K,1,2,3,4,5,6,7,8,9,10,11,12");

mysql> alter table foo add column bar char(100); mysql> alter table foo add column bar char(100) after foo;
 * Add a column to an existing table


 * Quick views of users and databases:

CREATE OR REPLACE VIEW quickuser AS SELECT User, Host, CONCAT(Select_priv, Lock_tables_priv, Create_tmp_table_priv) AS `read`, CONCAT(Insert_priv, Update_priv, Delete_priv) AS `write`, CONCAT(Execute_priv) AS `exec`, CONCAT(Create_priv, Alter_priv, Drop_priv, References_priv, Index_priv, Create_routine_priv, Alter_routine_priv, Create_view_priv, Show_view_priv,  Event_priv, Trigger_priv) AS `schema`, CONCAT(Grant_priv) AS `grant`, CONCAT(Super_priv, Shutdown_priv, Process_priv, File_priv, Show_db_priv, Reload_priv, Create_user_priv, Create_tablespace_priv) AS admin, CONCAT(Repl_slave_priv, Repl_client_priv) AS repl, Password, password_expired AS expired, CONCAT(ssl_type, ssl_cipher, x509_issuer, x509_subject) AS `ssl`, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string FROM user ORDER BY User, Host;

CREATE OR REPLACE VIEW quickdb AS SELECT DB, User, Host, CONCAT(Select_priv, Lock_tables_priv, Create_tmp_table_priv) AS `read`, CONCAT(Insert_priv, Update_priv, Delete_priv) AS `write`, CONCAT(Execute_priv) AS `exec`, CONCAT(Create_priv, Alter_priv, Drop_priv, References_priv, Index_priv, Create_routine_priv, Alter_routine_priv, Create_view_priv, Show_view_priv,  Event_priv, Trigger_priv) AS `schema`, CONCAT(Grant_priv) AS `grant` FROM db ORDER BY Db, User, Host;