- How to login via console Windows/Linux.
- How to create a user / How to create a super user.
- How to grant user permissions to a database.
- How to create a database.
- How to login via workbench Windows
MySQL has a command line tool which can be used to query and manage databases and users. This command is simply "mysql" and will usually be in the command path on Linux, although to use it on Windows you would normally first need to change to the directory/folder that the MySQL applications are installed in before running "mysql." Alternatively in Windows click on Start > All Programs > MySQL to access the MySQL Command Line Client icon (this icon may also already be accessible from a shortcut on your server desktop). Once you have logged into MySQL in either Windows or Linux, the MySQL commands will be the same.
Logging in to MySQL from the command line
The basic usage of the "mysql" command is this:
mysql -u [username] –p
If you are logging in via the MySQL Command Line icon in Windows, you will simply be prompted for the user password for the “root” user account.
After entering your password (successfully), the mysql command prompt will start and you'll see something like this:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.22
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
You can quit from the MySQL command line client at any time by entering \q and then <enter>
How to add a new account in MySQL by using GRANT or INSERT in MySQL 4.x
For MySQL 4 and earlier, you will need to use the GRANT option.
- Using GRANT statements cause the server to make appropriate modifications to the grant tables.
Information you will need:
User name – the name you have selected for the new account
Password – password you have selected for the new account
Database – The database and table the user is being given access to use. The database is identified after the ON option. Using *.* for the database name grants permissions on all databases and tables, making the account a super user account.
Connection – identifies the computer the user is allowed to connect from. Accounts set to @’localhost’ can only connect from the machine on which MySQL is running, accounts set to @’%’ can connect from any machine, accounts set to @’someIP’ can only connect from machines with the specified IP, and accounts set with @’someDomain’ can only connect from the specified domain.
The following knowledgebase article will walk you through the step to create a user account and assign privileges in MySQL 4.x:
How to add a new account in MySQL by using CREATE USER in MySQL 5.x
The CREATE USER command is available only in MySQL 5 and newer releases, and it follows this syntax (substituting your selected username and password):
mysql> CREATE USER [username] IDENTIFIED BY PASSWORD 'securepasswordhere';
Once the account is created you can set the user permissions:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
How to create a new database
The command for creating a new database is the same in MySQL 4 and 5:
CREATE DATABASE [databasename];
How to log into MySQL via MySQL Workbench in Windows: