MySQL:Utility


 * Home
 * Administration Tools
 * Backup and Restore
 * Security
 * User Account Management
 * Utility

MySQL utility is a command line interface program (mysql.exe) that is installed in the bin directory of a MySQL distribution. In the case of XAMPP, all MySQL software components are in the mysql directory of XAMPP's root directory.

The guidelines and examples in this article refer to a XAMPP installation. However, you can install MySQL community server from MySQL downloads.

Starting utility

 * 1) Open the  bin directory of your mysql installation and locate mysql.exe program.
 * 2) Make note of the path of mysql.exe.
 * 3) You will use this path followed by forward slash (on Macs) or backward slash (on Windows) and followed by mysql to run MySQL command line interface program.
 * 4) You can also set the PATH environment variable to remember the path of mysql.exe program.
 * 5) If you use Windows or MAC, you must start a terminal utility (Windows Command for Windows OS or Terminal program for MAC OS). This kind of terminal utility gives you access to MySQL via the command line.

Here are two examples. Open a terminal (console - or command-line) window and type:
 * In Windows, which uses BitNami WAMP Stack for the Apache, PHP, MySQL bundle:
 * In Mac, which uses MAMP for the Apache, PHP, MySQL bundle:
 * In Linux:

Very important note: DO NOT ADD ; after you type in mysql -u root -p. If you do, mysql assumes that the password of root is ;.

User accounts administration
Your MySQL server installation has an anonymous superuser : account with all privileges and no required password. This means that you can login into the MySQL server as follows: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.1.30-community MySQL Community Server (GPL)

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

mysql>

OR with: Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.1.30-community MySQL Community Server (GPL)

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

mysql>


 * Note: Press ENTER when prompted with the password and the log in process goes on successfully.

Creating user accounts
Query OK, 0 rows affected (0.05 sec)
 * Create a user called cis520 with password cis520

mysql> select user, select_priv, insert_priv, update_priv, delete_priv -> create_priv, drop_priv, reload_priv -> from user -> where user="cis520"; ++-+-+-+-+---+-+ ++-+-+-+-+---+-+ ++-+-+-+-+---+-+ 1 row in set (0.00 sec)
 * Check out some of the privileges the user just created has
 * Use 'mysql' database prior to running this query.
 * user  | select_priv | insert_priv | update_priv | create_priv | drop_priv | reload_priv |
 * cis520 | N          | N           | N           | N           | N         | N           |

mysql> grant all privileges on *.* to 'cis520'@'localhost' -> identified by 'cis520' -> with grant option; Query OK, 0 rows affected (0.00 sec)
 * Grant privileges to the cis520 user such that this user can do everything for any database, including granting privileges to others

mysql> select user, select_priv, insert_priv, update_priv, delete_priv -> create_priv, drop_priv, reload_priv -> grant_priv -> from user -> where user="cis520"; ++-+-+-+-+---++ ++-+-+-+-+---++ ++-+-+-+-+---++ 1 row in set (0.00 sec)
 * user  | select_priv | insert_priv | update_priv | create_priv | drop_priv | grant_priv |
 * cis520 | Y          | Y           | Y           | Y           | Y         | Y          |

Where is user metadata?
All user account information is stored in the table user of the database mysql.

Do the following to verify the existence of a database called mysql, which has a table called user, in which there is a user account called admin (or cis520 - ask your instructor!). ++ ++ ++ 13 rows in set (0.00 sec)
 * Database          |
 * information_schema |
 * collection        |
 * mydb              |
 * mysql             |
 * test              |

Database changed

+---+ +---+ +---+ 23 rows in set (0.01 sec)
 * Tables_in_mysql          |
 * columns_priv             |
 * db                       |
 * event                    |
 * func                     |
 * general_log              |
 * help_category            |
 * help_keyword             |
 * help_relation            |
 * help_topic               |
 * host                     |
 * ndb_binlog_index         |
 * plugin                   |
 * proc                     |
 * procs_priv               |
 * servers                  |
 * slow_log                 |
 * tables_priv              |
 * time_zone                |
 * time_zone_leap_second    |
 * time_zone_name           |
 * time_zone_transition     |
 * time_zone_transition_type |
 * user                     |

+---+---+--+-+-+---+ +---+---+--+-+-+---+ +---+---+--+-+-+---+ 39 rows in set (0.00 sec)
 * Field                | Type                              | Null | Key | Default | Extra |
 * Host                 | char(60)                          | NO   | PRI |         |       |
 * User                 | char(16)                          | NO   | PRI |         |       |
 * Password             | char(41)                          | NO   |     |         |       |
 * Select_priv          | enum('N','Y')                     | NO   |     | N       |       |
 * Insert_priv          | enum('N','Y')                     | NO   |     | N       |       |
 * Update_priv          | enum('N','Y')                     | NO   |     | N       |       |
 * Delete_priv          | enum('N','Y')                     | NO   |     | N       |       |
 * Create_priv          | enum('N','Y')                     | NO   |     | N       |       |
 * Drop_priv            | enum('N','Y')                     | NO   |     | N       |       |
 * Reload_priv          | enum('N','Y')                     | NO   |     | N       |       |
 * Shutdown_priv        | enum('N','Y')                     | NO   |     | N       |       |
 * Process_priv         | enum('N','Y')                     | NO   |     | N       |       |
 * File_priv            | enum('N','Y')                     | NO   |     | N       |       |
 * Grant_priv           | enum('N','Y')                     | NO   |     | N       |       |
 * References_priv      | enum('N','Y')                     | NO   |     | N       |       |
 * Index_priv           | enum('N','Y')                     | NO   |     | N       |       |
 * Alter_priv           | enum('N','Y')                     | NO   |     | N       |       |
 * Show_db_priv         | enum('N','Y')                     | NO   |     | N       |       |
 * Super_priv           | enum('N','Y')                     | NO   |     | N       |       |
 * Create_tmp_table_priv | enum('N','Y')                    | NO   |     | N       |       |
 * Lock_tables_priv     | enum('N','Y')                     | NO   |     | N       |       |
 * Execute_priv         | enum('N','Y')                     | NO   |     | N       |       |
 * Repl_slave_priv      | enum('N','Y')                     | NO   |     | N       |       |
 * Repl_client_priv     | enum('N','Y')                     | NO   |     | N       |       |
 * Create_view_priv     | enum('N','Y')                     | NO   |     | N       |       |
 * Show_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
 * Create_routine_priv  | enum('N','Y')                     | NO   |     | N       |       |
 * Alter_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
 * Create_user_priv     | enum('N','Y')                     | NO   |     | N       |       |
 * Event_priv           | enum('N','Y')                     | NO   |     | N       |       |
 * Trigger_priv         | enum('N','Y')                     | NO   |     | N       |       |
 * ssl_type             | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
 * ssl_cipher           | blob                              | NO   |     | NULL    |       |
 * x509_issuer          | blob                              | NO   |     | NULL    |       |
 * x509_subject         | blob                              | NO   |     | NULL    |       |
 * max_questions        | int(11) unsigned                  | NO   |     | 0       |       |
 * max_updates          | int(11) unsigned                  | NO   |     | 0       |       |
 * max_connections      | int(11) unsigned                  | NO   |     | 0       |       |
 * max_user_connections | int(11) unsigned                  | NO   |     | 0       |       |

How to examine user metadata?
Assume that your MySQL server installation has a user called admin. To display the user accounts that are called admin and check whether their passwords are empty, use the following statement:

+--++--+ +--++--+ +--++--+ The output above indicates that admin is an anonymous account, meaning that it does not have a password. The output might differ on your system, but the presence of accounts with empty passwords means that your MySQL installation is unprotected until you do something about it: Repeat the SQL statement above to find out what the password is of user called "cis520" and user called "root".
 * User | Host              | Password |
 * admin| localhost         |          |
 * You should assign a password to each MySQL root account.
 * If you want to prevent clients from connecting as anonymous users without a password, you should either
 * assign a password to each anonymous account or else
 * remove the accounts.

Using utility
If you did not set up any password for the root account on MySQL, then you don’t have to enter any password.

At mysql> prompt type the following:

show databases;
The command lists all the databases that are managed by MySQL DBMS and stored in a sub-directory of mysql directory.

For example, BitNami WAMP Stack on my computer has a data directory where databases are stored. The output of the show database command is: ++ ++ ++ 11 rows in set (0.31 sec)
 * Database          |
 * information_schema |
 * bitnami_mediawiki |
 * donate            |
 * dvunitcms         |
 * greenfoot_db      |
 * mydb              |
 * mysql             |
 * nbproject         |
 * publications      |
 * rmhhomebase       |
 * test              |

use database;
i.e., use rmhhomeabse;

The command uses a particular database (in the example above it is rmhhomebase database) for subsequent commands, such as show tables. Here is an example of the use command and its output:

Database changed

show tables;
The command lists the tables in the rmhhomebase database. Here is an example of the show command and its output:

+---+ +---+ +---+ 7 rows in set (0.19 sec)
 * Tables_in_rmhhomebase |
 * dbdates              |
 * dblog                |
 * dbpersons            |
 * dbschedules          |
 * dbscl                |
 * dbshifts             |
 * dbweeks              |

describe table;
i.e.,describe dbweeks;

The command describes the columns in the table dbweeks in the rmhhomebase database. Here is an example:

+---+-+--+-+-+---+ +---+-+--+-+-+---+ +---+-+--+-+-+---+ 8 rows in set (0.08 sec)
 * Field            | Type    | Null | Key | Default | Extra |
 * id               | char(8) | NO   | PRI | NULL    |       |
 * dates            | text    | YES  |     | NULL    |       |
 * weekday_group    | int(11) | YES  |     | NULL    |       |
 * weekend_group    | int(11) | YES  |     | NULL    |       |
 * family_room_group | int(11) | YES |     | NULL    |       |
 * status           | text    | YES  |     | NULL    |       |
 * name             | text    | YES  |     | NULL    |       |
 * end              | int(11) | YES  |     | NULL    |       |

help;
Lists all MySQL commands. Output of the help command is:

For information about MySQL products and services, visit:

http://www.mysql.com/

For developer information, including the MySQL Reference Manual, visit:

http://dev.mysql.com/

To buy MySQL Network Support, training, or other products, visit:

https://shop.mysql.com/

List of all MySQL commands: Note that all text commands must be first on line and end with ';' ?        (\?) Synonym for `help'. clear    (\c) Clear command. connect  (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter. ego      (\G) Send command to mysql server, display result vertically. exit     (\q) Exit mysql. Same as quit. go       (\g) Send command to mysql server. help     (\h) Display this help. notee    (\t) Don't write into outfile. print    (\p) Print current command. prompt   (\R) Change your mysql prompt. quit     (\q) Quit mysql. rehash   (\#) Rebuild completion hash. source   (\.) Execute an SQL script file. Takes a file name as an argument. status   (\s) Get status information from the server. tee      (\T) Set outfile [to_outfile]. Append everything into given outfile. use      (\u) Use another database. Takes database name as argument. charset  (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

help contents;
Lists several categories of commands, such as data definitions commands, or data manipulation commands. To find out all the commands in a specific category, type help "category";. (replace category with help topic) For example:
 * Help Contents:

You asked for help about help category: "Data Definition" For more information, type 'help ', where is one of the following topics: ALTER DATABASE ALTER EVENT ALTER PROCEDURE ALTER SERVER ALTER TABLE ALTER TABLESPACE ALTER VIEW CONSTRAINT CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE INDEX CREATE PROCEDURE CREATE SERVER CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE VIEW DROP DATABASE DROP EVENT DROP FUNCTION DROP INDEX DROP PROCEDURE DROP SERVER DROP TABLE DROP TABLESPACE DROP TRIGGER DROP VIEW MERGE RENAME TABLE

exit;

exit MySQL command line interface program. quit or CTRL-C has the same effect.

Connection error
Can't connect to MySQL server on 'localhost'. This means that the MySQL server application is not running. Check the processes that are currently running on your computer. You'll see that mysql is missing. This is NOT the mysql utility, but the MySQL server application. Start MySQL server application and try to sign in again.

Cannot run SQL script that creates tables and inserts data into those tables
Failed to open file 'c:\xampp\LearningSQLExample.sql'. This usually means that that database for which tables are created has not been created in the first place and/or selected. To fix this: mysql>create database bank; mysql>use bank; mysql> source c:\xampp\LearningSQLExample.sql

Practice exercises
This is an exercise design to help you get familiar with database creation. This exercise can be done using MySQL Workbench and accomplishes the same task.

You are logged into mysql with the user account ‘root’ and no password. Query OK, 0 rows affected (0.09 sec)
 * 1. Create the database collection
 * 2. Create a table called item in the database collection. First, switch to the database you just created:
 * 3. The table item has itemID, itemName, and itemDescription columns. The data type of itemID column is integer and the data types for name and description are stirngs. An integer data type in MySQL is int, and a variable size string data type in MySQL is varchar(xx), where xx is the initial maximum size.
 * 3. The table item has itemID, itemName, and itemDescription columns. The data type of itemID column is integer and the data types for name and description are stirngs. An integer data type in MySQL is int, and a variable size string data type in MySQL is varchar(xx), where xx is the initial maximum size.
 * 3. The table item has itemID, itemName, and itemDescription columns. The data type of itemID column is integer and the data types for name and description are stirngs. An integer data type in MySQL is int, and a variable size string data type in MySQL is varchar(xx), where xx is the initial maximum size.

+---+ +---+ +---+ 1 row in set (0.00 sec)
 * 4. Verify that the table has been created correctly
 * Important Note: Press ENTER key after each line. MySQL command line interface program prompts you with – > to continue your SQL command. To finish the command, enter ; at the end of the last line.
 * Tables_in_collection |
 * item                 |

+-+--+--+-+-+---+ +-+--+--+-+-+---+ +-+--+--+-+-+---+ 3 rows in set (0.05 sec)
 * Field          | Type         | Null | Key | Default | Extra |
 * itemID         | int(11)      | YES  |     | NULL    |       |
 * itemName       | varchar(32)  | YES  |     | NULL    |       |
 * itemDescription | varchar(128) | YES |     | NULL    |       |

Query OK, 1 row affected (0.00 sec)
 * 5. Adding data to the item table

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

++--+-+ ++--+-+ ++--+-+ 3 rows in set (0.00 sec)
 * 6. Verify that the table item has the data you just inserted
 * itemID | itemName | itemDescription |
 * 1 | car     | Audi            |
 * 2 | dog     | Akita           |
 * 3 | city    | Adover          |

Wikipedia links

 * MySQL