MySQL Console

The MySQL utility is a very popular admin tool; Uniform Server provides a dedicated menu button that directly runs this utility in a console window. The console window displays mysql> where you type MySQL commands, this combination is commonly refered to as the MySQL prompt. From this window the MySQL server can be administered.

This page focuses on using the MySQL prompt. It is not intended as a definitive guide but just an introduction to show you how to access and use MySQL client.

MySQL prompt background information
MySQL prompt - command window short cut
Basic MySQL commands
Creating MySQL users
MySQL user permissions
Permissions overview
Data Manipulation
Table manipulation
Related topics

MySQL prompt background information

The following shows how to run a MySQL prompt in a standard command window from a fixed and portable installation.

Fixed installation:

After installing the MySQL server its environment is set-up allowing you to run the MySQL utility directly from a command prompt. To start a MySQL prompt proceed as follows:

1	`Start the mysql server`	- MySQL server must be running.
2	`Start > Run`	- Opens the run menu.
3	`Type cmd click OK`	- Opens a standard command window.
4	`Type mysql.exe {parameters}`	- Runs the MySQL utility and displays the mysql prompt.

Note 1: The {parameters} are: -h127.0.0.1 -uroot -proot Or --host=127.0.0.1 --port=3306 --user=root --password=root
Note 2: If you have changed the MySQL root password, remember to substitute (-proot) or --password=root with your password in the above.

Portable installation:

The MySQL server environment is not configured for a standard command window an additional command is required to run a MySQL prompt. To start a MySQL prompt proceed as follows:

1	`Start the mysql server`	- MySQL server must be running.
2	`Start > Run`	- Opens the run menu.
3	`Type cmd click OK`	- Opens a standard command window.
4	`Type cd {path to mysql.exe}`	- Where {path to mysql.exe} is full path to mysql.exe for example Z:\UniServerZ\core\mysql\bin
5	`Type mysql.exe {parameters}`	- Runs the MySQL utility and displays the mysql prompt.

Running a MySQL prompt requires a number of steps; you can reduce these by running the Server console and entering the line:

mysql.exe --host=127.0.0.1 --port=3306 --user=root --password=root

however this again requires additional work. The next section introduces MySQL console that remove the above tedium.

MySQL prompt - command window short cut

Running the MySQL utility (MySQL Prompt) from UniController has a number of advantages.

Opening a MySQL prompt requires only a single button click.
The button is enabled only when the MySQL server is running.
Server location tracking is automatic setting correct paths to MySQL binaries.
User configured port and root password automatically picked up.

Run MySQL Console:

Start UniController
Start MySQL server: Click Start MySQL button
Start MySQL Prompt: Click MySQL Console
We are not going to use the utility close it as follows:
At the mysql prompt enter exit.

A MySQL console window is shown on the right. Note after entering exit and pressing return you are greeted with bye and returned to a server command console window. To re-run a MySQL console first close this server command console window and click the MySQL console button again.

Basic MySQL commands

The following provides a list of basic mysql commands along with their description.
Each command starts at a command prompt mysql> it is shown for reference you do not type this in.

Command	Description
`mysql> create database [databasename];`	Create a database on the MySQL server.
`mysql> show databases;`	List all databases on the MySQL server.
`mysql> use [db name];`	Switch to a database (Selects a database)
`mysql> show tables;`	To view all tables in a db.
`mysql> describe [table name];`	To view database's field formats.
`mysql> drop database [database name];`	To delete a db.
`mysql> drop table [table name];`	To delete a table.
`mysql> SELECT * FROM [table name];`	Show all data in a table.
`mysql> show columns from [table name];`	Returns the columns and column information for a table.

Creating MySQL users

Creating a user for MySQL requires you create a user name with a password and assign permissions to different databases as needed.

New user

To create a new user with username 'fred' and password 'us123' proceed as follows:

Click MySQL Console	- Runs MySQL prompt. At the prompt type next three lines
`CREATE USER 'fred'@'127.0.0.1' IDENTIFIED BY 'us123';`	- Enter SQL commad to create user and password.
`FLUSH PRIVILEGES;`	- Flush the privileges which reloads the 'user' table in MySQL
`exit;`	- Exit mysql prompt

At this stage, our new user fred is lacking permission and can't do anything not even log onto the server.

MySQL user permissions

Generally a database for web application is assigned a specific user. There would be no need to have this user access any other database.

You can assign a user to an existing database or create a new one. In this example we create a new database 'wordpress' and allow 'fred' to have full access to it. He will be able to create, read, update and delete records as needed.

New database assign user

To create a new database and assign user 'fred' proceed as follows:

Click MySQL Console	- Runs MySQL prompt. At the prompt type next four lines
`CREATE DATABASE wordpress;`	- Enter SQL commad to create new database.
`GRANT ALL PRIVILEGES ON wordpress.* TO 'fred'@'127.0.0.1';`	- Grant privileges
`FLUSH PRIVILEGES;`	- Flush the privileges which reloads the 'user' table in MySQL
`exit;`	- Exit mysql prompt

The above grants all permissions to your new user. If you wish to refine permissions granted see next section for details.

Permissions overview

You can grant all permissions to a user for a specific database as above or grant only a limited set of permission covered in this section.

Using the root user account a database structure is created. You assign restrictions to this database for a specific user either allowing full permissions or tailor restrictions to data and or table manipulation.

Data Manipulation

The most common use for a database is data manipulation requiring the following minimum permissions:

SELECT	- read only
INSERT	- insert rows/data
UPDATE	- change inserted rows/data
DELETE	- drop rows of data

To assign individual permissions proceed as follows:

Click MySQL Console	- Runs MySQL prompt. Note: logs into MySQL as the root user.
`GRANT SELECT, INSERT, UPDATE ON wordpress.* TO 'fred'@'127.0.0.1';`	- Enter SQL commad to assign SELECT, INSERT and UPDATE on database wordpress to user fred.
`FLUSH PRIVILEGES;`	- Flush the privileges which reloads the 'user' table in MySQL
`exit;`	- Exit mysql prompt

User fred can now select, insert and update records. However fred does not have permission to delete records or adjust the structure of the database.

Table manipulation

Generally most users do not require access to allow database structure alterations. However if they are designing databases, permissions to edit tables and database structure are required. Following are the permissions required:

CREATE	- create new tables
ALTER	- change table/column names
DROP	- drop columns/tables