Short My SQL Database Tutorial

By Sergey Skudaev

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.

One hour MySQL tutorial: resources

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.

One hour MySQL tutorial: Installation

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. Hover mouse over a thumbnail to see large image.

One hour MySQL tutorial: MySQL security

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>

One hour MySQL tutorial: create database

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

mysql> create database eshop_db;
Query OK, 1 row affected (0.02 sec)

mysql>

One hour MySQL tutorial: create table

To create a table type the following script:

mysql> 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.

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

One hour MySQL tutorial: create a user

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.

mysql> 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.

mysql> exit;
Bye

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:

mysql> 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

Or you may see message unknown function mysql_connect(). Copy all mysql dll in C:\Apache2.2\bin directory.

If it does not work visit MySQL web site to find out how to fix it

Tutorial about using Oracle with PHP

My eBooks on Amazon.com

US UK BR CA

Computer Programming Web programming Tips