MySQL

MySQL Reference

Useful commands to check on mysql

mysqladmin -u root -p status
systat -vmstat

• Setting root password

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

• Creating a Database

# mysqladmin create [databasename]
or
mysql> create database [databasename]
Example: # mysqladmin create mydatabase [Enter]

• Dropping (Removing) a Database

# mysqladmin drop [databasename]

Example: # mysqladmin drop mydatabase [Enter]

• Populating an Existing Database from a *.sql File

# mysql [databasename] < [databasedumpfile.sql]

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

• Dumping Database Structure and Data to a file

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

From within the MySQL interface

• Starting MySQL from the Command Line

# 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.

• Seeing What Databases are Available

mysql> show databases;

Example:  >mysql show databases; [Enter]

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

• Telling MySQL to Use a Specific Database

mysql> use [databasename];

Example: mysql> use mydatabase; [Enter]

• Seeing What Tables are Available Within a Database

mysql> show tables;

Example:  mysql> show tables; [Enter]

• Looking at the Data in a Particular Table

mysql> select * from [tablename];

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

• Adding a Database User with Password

mysql> grant all privileges on [databasename].* to [dbusername]@localhost
identified by '[dbpassword]';

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

• Removing a Database User

   
        mysql> DELETE FROM mysql.user WHERE user='username' and host='localhost';
  FLUSH PRIVILEGES;

        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");

• Add a column to an existing table

mysql> alter table foo add column bar char(100);
mysql> alter table foo add column bar char(100) after foo;

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

See Also

• MySQL Backups