In this tutorial you will learn about MySQL security, how to use command prompt window to start MySQL server and client. How to create MySQL user,how to grant permission or revoke permission from a user.

How to set password for a new user or default root user and ananimous user. How to create database

and tables using Data Definition Language.

MySQL is an open source database. Download it from http://dev.mysql.com/downloads/mysql/5.0.html

MySQL manual downloads http://dev.mysql.com/doc/ Installation for windows is very easy. See manual.

It is recomended to install MySQL in root directory. If your Windows are installed in C drive, then install

MySQL in "C:\mysql" directory.

After installation go to Command Prompt. Depending on Windows version, command prompt window will open in C:\windows or C:\Documents and Settings\username> directory.

What ever directory is displayed in the Command Prompt window, type cd.. command to go up to root directory. In the root directory type cd mysql\bin and press enter.

When you get in C:\mysql\bin directory, type: mysqld-nt( for Windows 2000, XP)

or mysqld (for windows Me, 98). MySQL will be started.

MySQL by default has two anonymous user accounts and two root user accounts without password.

If you do not change these accounts, every one can access your data. To fix it,

login in MySQL as root user. Type:C:\mysql\bin>mysql -u root and hit enter. The following

text displays.

C:\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 3.23.58-nt

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

SET password for anonymous account

mysql> SET PASSWORD FOR ''@'localhost' =PASSWORD('lexapro');
Query OK, 0 rows affected (0.06 sec)

Try to login as anonymous user without password: access denied.

C:\mysql\bin>mysql
ERROR 1045: Access denied for user: 'ODBC@localhost' (Using password: NO)

Try to login as anonymous user with new password: access granted

C:\mysql\bin>mysql -plexapro
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32 to server version: 3.23.58-nt

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

mysql>

SET password for root user accounts.

mysql> SET PASSWORD FOR 'root'@'localhost' =PASSWORD('aspirin');
Query OK, 0 rows affected (0.00 sec)

mysql> SET PASSWORD FOR 'root'@'%' =PASSWORD('aspirin');
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

Try to login as root user without password: access denied

C:\mysql\bin>mysql -u root
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Try to login as root user with new password: access granted

C:\mysql\bin>mysql -u root -paspirin mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29 to server version: 3.23.58-nt

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

mysql>

To create a database type: create database eshop_db; and press enter. Database is created.

mysqlgt: create database eshop_db;
Query OK, 1 row affected (0.02 sec)

mysql>

To create a table type the following script:

mysqlgt: create table products(
-> pr_prodid int(11) NOT NULL auto_increment,
-> pr_catid int(11),
-> products varchar(50),
-> prod_number varchar(20),
-> price double,
-> pr_image varchar(255),
-> pr_user_01 varchar(20),
-> pr_user_02 varchar(20),
-> pr_user_03 varchar(20),
-> PRIMARY KEY (pr_prodid)
-> );
Query OK, 0 rows affected (0.01 sec)

Read Database Design tutorial to learn more about designing database and SQL

To view all columns in products table type: SHOW COLUMNS FROM products; and press enter.

mysqlgt: SHOW COLUMNS FROM products;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| pr_prodid | int(11) | | PRI | NULL | auto_increment |
| pr_catid | int(11) | YES | | NULL | |
| products | varchar(50) | YES | | NULL | |

| prod_number | varchar(20) | YES | | NULL | |
| price | double | YES | | NULL | |
| pr_image | varchar(255) | YES | | NULL | |
| pr_user_01 | varchar(20) | YES | | NULL | |
| pr_user_02 | varchar(20) | YES | | NULL | |
| pr_user_03 | varchar(20) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

mysqlgt:

To view all tables in eshop_db database type: SHOW TABLES FROM eshop_db; press enter.

mysqlgt: SHOW TABLES FROM eshop_db;
+-------------------+
| Tables_in_eshop_db |
+-------------------+
| categories |
| customers |
| orders |
| products |
+-------------------+
4 rows in set (0.03 sec)

mysql>

To create a user for your database

1. Login as 'root' user:

C:\mysql\bin>mysql -u root -paspirin
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 3.23.58-nt

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

2. Type: GRANT ALL PRIVILEGES ON eshop_db.* TO 'john'@'localhost' IDENTIFIED BY 'maxalt';

and press enter. New User 'john' with password 'maxalt' created.

mysqlgt: GRANT ALL PRIVILEGES ON eshop_db.* TO 'john'@'localhost' IDENTIFIED BY 'maxalt';
Query OK, 0 rows affected (0.00 sec)

Check if user john has access to the database. Type: 'exit' to exit database.

mysqlgt: exit;
Bye

Login as a user 'john' with password 'maxalt'.

C:\mysql\bin>mysql -u john -pmaxalt eshop_db
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19 to server version: 3.23.58-nt

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

mysql>

Revoking access from user:

1. Login as user 'root' user.

C:\mysql\bin>mysql -u root -paspirin
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 3.23.58-nt

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

2. Revoke all privileges:

mysqlgt: REVOKE ALL PRIVILEGES ON eshop_db.* FROM 'john'@'localhost' IDENTIFIED BY
'maxalt'
-> ;
Query OK, 0 rows affected (0.13 sec)

mysql>

Access revoked. User john cannot access eshop_db database.

Please read MySQL manual to learn more...

If you use MySQL 5.0 and get error message like this:

mysql_connect() [function.mysql-connect]: Client does not support authentication protocol

requested by server; consider upgrading MySQL client in

C:\Apache2\htdocs\examples\fieldsarray.php on line 10 Unable to connect

Visit MySQL web site to find out how to fix it