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.

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.

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

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

Tutorial about using Oracle with PHP

If you sign for hosting through one of 4 links on my site I placed below I will send you PHP authentication templates for free. See details user Authentication Demo

Hosting companies I use and recomend!

1. Fatcow.com I use it for www.configure-all.com, www.hardstuffez.com and www.healthstairs.com
40% off at FatCow!

2. www.bluehost.com
Host Unlimited Domains - 1 Account $6.95 Per Month

3. www.hostmonster.com
HostMonster - Perl, CGI, SSH, Free Domain

Affiliate Disclosure: I may get comission, when you purchase products or services through links on my website.

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

If you like this page click +1 button.

Please rate the tutorial

How to Build Your Own Web Site from Scratch [Kindle Edition] $2.99

Earn Money on Internet as an Affiliate [Kindle Edition] $0.99

Comments

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.
Joseph 08-08-2010

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
Sergey 08-08-2010

Register to add comments ( 1000 char ) for mysql_manual.php.