• Home
• Registration
• Database Design
• Password Keeper
• MySQL and Excel
• Learn MySQL
• Learn PHP
• Learn Visual Basic
•  
• Learn Java
• Web Site Tips
• Miscellaneous
• C ++ Code Examples
• Start C++
• C ++ Continue
• C ++ Pointer
• Variable Scope
• Linked List
• Bubble Sort
• Two Bubble Sort
• Insertion Sort
• Selection Sort
• Two Selection Sort
• Merge Bin Sort
• Advertise here
• Site map

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.

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

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>

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.

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

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

* * *

If you do not like to read from screen you can download all contents of this web site as PDF file, print it out and read it seating in a comfortable chair.

To see the link, you have to be registered visitor. If I did not registered. Please register on my site .

Did you find information useful? Send to your friend a link to this page

Please rate the tutorial

Hi Sergey!
Is there a way to open my sql database 
without the use of its native server?  
I copied mysql database data files and 
want to restore my records.
Any ideas you can give me 
would be greatly appreciated.

 

Hi Joseph

MySql directory has "data"
directory with directories for each database
with the same name as database name.
Inside database directory there is one 
db.opt file and 3 files for each table: one
file frm (table definition),
one MYD (table data) and one MYI (table
indices). 
If you have all these files your could
restore MySQL database. Did you copied MYD
and MYI files?
You do not have to know password to your old
database. Instal MySQL database on any PC,
Create a new MySQL database with new
password. Create any table. 
Open data directory for new MySQL, open
directory with the same name as a new
database
and copy into that directory all your files
(db.opt, .frm. MYD and MYI) for all tables
from old database with lost password.
Access a new database with a new password...
mysql -u user -ppassword
select your database:
use databasename
and access your tables from an old database:
select * from table