MySQL Console

The MySQL client 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 referred to as the MySQL prompt. From this window, the MySQL server can be administered.

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

MySQL Console - background information

The following shows how to run the MySQL client 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 client directly from a command prompt. To start the MySQL client, proceed as follows:

1. Start the mysql server - MySQL server must be running.
2. Press the Windows + R keys - Opens the run menu.
3. Type cmd and click OK - Opens a standard command window.
4. Type mysql.exe {parameters} - Runs the MySQL client 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 the MySQL client. To start the MySQL client, proceed as follows:

1. Start the mysql server - MySQL server must be running.
2. Press the Windows + R keys - Opens the run menu.
3. Type cmd and 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 C:\UniServerZ\core\mysql\bin
5. Type mysql.exe {parameters} - Runs the MySQL client 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.

Running the MySQL client 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, which removes the above tedium.

MySQL Console - MySQL client shortcut

Running the MySQL client from UniController has a number of advantages: Opening the MySQL client requires only a single button click. The button is enabled only when the MySQL server is running. Server location tracking is automatically setting correct paths to MySQL binaries. User configured port and root password are automatically picked up. Run MySQL Console: Start UniController. Start MySQL server: click Start MySQL button. Start MySQL Console: click MySQL Console. Since we are not going to use the utility, close it by entering exit.

A MySQL console window is shown on the right. Note that after entering exit and pressing Enter, you are greeted with Bye and returned to a Server Console window. To re-run a MySQL console, first close this server console window and click the MySQL Console button again.

Basic MySQL commands

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

Creating MySQL users

Creating a user for MySQL requires you to create a username 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:

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

MySQL user permissions

Generally, the database of a web application is assigned to one 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:

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 as 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:

To assign individual permissions, proceed as follows:

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:

To assign individual permissions, proceed as follows:

User fred is now allowed to create, drop and alter tables on the 'wordpress' database.

Related topics

Set New MySQL root user password using MySQL Console
Create Restricted MySQL User using MySQL Console
Delete Restricted MySQL User using MySQL Console
Edit Restricted MySQL User using MySQL Console