Computer Programming web Web programming Tips



Short My SQL Database Tutorial

By Sergey Skudaev

Happy Amazon Prime Day July 12 - Exclusive Deals for Prime Members - Start Free 30-Day Trial

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.

MySQL tutorial



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.

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

mysql>

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

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

mysql>

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

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:


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

Subscribe to our mail list to receive free code examples.

Comments

No comments yet.

Add Comment

* Required information
(never displayed)
 
Smile Sad Huh Laugh Mad Tongue Crying Grin Wink Scared Cool Sleep Blush Unsure Shocked
 
1000
What is the next number: 10, 12, 14, ..?
 
Enter answer:
Captcha
Refresh
 
Enter code:
 
Notify me of new comments via email.
 
Remember my form inputs on this computer.
 
I have read and understand the privacy policy. *
 
I have read and agree to the terms and conditions. *
 
 
Powered by Commentics

My eBooks on Amazon.com

Learn C++ Programming By Examples

Learn SQL Programming By Examples

Learn PHP Programming by Examples

Visual Basic Programming By Examples

How to Build Your Own Web Site from Scratch