MySQL 5.0 WorkShopBy Open Source Group : 25/10/2007 MySQL 5.0 WorkShopBy Open Source Group
Installation : Installation MySQL Installation is straight forward
Simpler than Oracle and Other RDBMS installation
Download Installation from www.mysql.com
For Linux download rpm binaries .
For windows download windows binaries or download complete customized package WAMP (apache,mysql,php for windows) from sourceforge.net 25/10/2007 2
Installation-RedHat Linux : Installation-RedHat Linux Install the packages through
a.system-config-packages
b.Command line utility rpm Check MySQL packages are installed or not
#rpm –qa|grep mysql
Install the packages
#system-config-packages
Or installation through rpm
#rpm –ivh mysql-server-5.0.22-2.1.i386.rpm
#rpm –ivh mysql-5.0.22.-2.1.i386.rpm 25/10/2007 3
Installation-RedHat Linux : Installation-RedHat Linux Server RPM – mysqld daemon process
Client RPM – mysql, mysqladmin, mysqldump, mysqlimport
Header Libraries –Mysql support for 3rd party application : snort
Shared Libraries –Libraries that are common to server and client programs 25/10/2007 4
Default Installation -RedHat Linux : Default Installation -RedHat Linux 2 default accounts (root,anonymous)
2 default databases (mysql,test)
Important Database is mysql
Internal credential list of user/password stores in the default mysql database
To reveal currently logged in mysql user execute – mysql>select user();
MySQL maintains command history that can be accessed by arrow keys
Test database is the dummy database accessible by anonymous user
Default Data directory - /var/lib/mysql
Default Log Directory - /var/log/mysqld.log
Default Config File - /etc/my.cnf
Client Utility – mysql
Client Admin Utility –mysqladmin ,mysql
Backup Utility – mysqldump,mysqlimport 25/10/2007 5
Installation-RedHat Linux : Installation-RedHat Linux Start the Server Process
Client Side Utility # chkconfig mysqld on /* to start the mysql server during boot time
#service mysqld start /* start the mysqld server
#service mysqld status /* check mysqld server status #mysql
#mysql –u root -p -h
#mysqladmin
#mysqladmin –u root -pnic123 25/10/2007 6
Authentication : Authentication By default connections are only be made from
Is permitted on username and password i.e root@localhost
Two ways to secure user account using mysqladmin and mysql utility
Default installation does not have password for root account
Root is the super user ,Secure root and anonymous accounts.
Linux /Other OS root account is different from MySQL root account
Increase DBMS security by removing anonymous account and put password on root account
User authentication stores in user,host and db table of mysql database #mysqladmin –u root password nic123
#mysql > set password=password('nic123'); 25/10/2007 7
Securing Authentication : Securing Authentication Viewing the default user accounts after installation
#mysql –e ‘select user,host,password from mysql.user’; # mysql -e 'select user,host,password from mysql.user';
+------+-----------------------+------------------+
| user | host | password |
+------+-----------------------+------------------+
| root | localhost | |
| root | localhost.localdomain | |
| | localhost.localdomain | |
| | localhost | |
+------+-----------------------+------------------+ 25/10/2007 8
Securing Authentication : Securing Authentication Securing root account with password
Dropping anonymous account Securing Root Account with New Password
#mysqladmin –u root password ‘nic123’
#mysql –u root –e ‘set password =password(‘nic123’);
Viewing Changes
mysql> select user,host,password from mysql.user;
+------+-----------------------+------------------+
| user | host | password |
+------+-----------------------+------------------+
| root | localhost | 4b5698aa4603595b |
| root | localhost.localdomain | |
| | localhost.localdomain | |
| | localhost | |
+------+-----------------------+------------------+ 25/10/2007 9
Securing Authentication Cont.. : Securing Authentication Cont.. Deleting anonymous account
use ‘DELETE’ command to remove the anonymous account Deleting Anonymous Account
mysql> DELETE FROM mysql.user WHERE user='';
mysql>DELETE FROM mysql.user WHERE user=‘root’ AND host=‘localhost.localdomain’;
Viewing the Changes
mysql> select user,password,host from mysql.user;
+------+------------------+-----------+
| user | password | host |
+------+------------------+-----------+
| root | 4b5698aa4603595b | localhost |
+------+------------------+-----------+ 25/10/2007 10
Default DataBase : Default DataBase By default three databases loaded during mysql 5.0 and two databases loads during mysql 4.0 installation
Don’t interfere with and database
Drop the database mysql>show databases;
Information_schema
Mysql
test 25/10/2007 11
Default DataBase –File Structure : Default DataBase –File Structure Different folders under /var/lib/mysql are called databases
Bydefault two folders are there i.e mysql,test
When create database it creates a folder under /var/lib/mysql /hr where hr is the database name
All databases and other files are owned by mysql user
Tables are created under the database folder . [root@mysqlmaster ~]# ll /var/lib/mysql
-rw-rw---- 1 mysql mysql 10485760 Oct 20 15:25 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Oct 21 03:06 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Oct 20 15:09 ib_logfile1
drwx------ 2 mysql mysql 4096 Oct 20 15:09 mysql
drwx------ 2 mysql mysql 4096 Oct 20 15:09 test 25/10/2007 12
Default DataBase –Table Structure : Default DataBase –Table Structure Tables are created under data base folder
Each new table creation creates three files
Each table file creates with (.frm, .MYD, .MYI) extension.
.frm –table schema definition file
.MYD –table data file
.MYI –table index file Folder of mysql database /var/lib/mysql/mysql
-rw-rw---- 1 mysql mysql 10330 Oct 20 15:09 user.frm
-rw-rw---- 1 mysql mysql 380 Oct 22 01:31 user.MYD
-rw-rw---- 1 mysql mysql 2048 Oct 22 01:31 user.MYI 25/10/2007 13
Summary : Summary Download rpm files from www.mysql.org or install the files from linux CD
Start the server – service mysqld start
Default permission –root,anonymous without password
Tighten Privilege – Delete anonymous user and create apassword for root user
Global user table – mysql .user
Client Utilities –mysql,mysqladmin,mysqlimport,mysqldump
Default data directory -/var/lib/mysql
Default Config file - /etc/my.cnf
Log File - /var/log/mysqld.log 25/10/2007 14
User Management -Introduction : User Management -Introduction Default Account (root,anonymous ) without password
Mysql server only accept connection from localhost
Global user table – mysql.user
Other authentication table – mysql.host,mysql.db
User management Commands –
CREATE
DROP
GRANT
REVOKE 25/10/2007 15
User Management-Privileges : User Management-Privileges 25/10/2007 16
User Management-AUTH MECHANISIM : User Management-AUTH MECHANISIM 25/10/2007 17
User Management-mysql.user table : User Management-mysql.user table Mysql>describe mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | NULL | |
| User | char(16) | NO | PRI | NULL | |
| Password | char(41) | NO | | NULL | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | | 25/10/2007 18
User Management-mysql.host table : User Management-mysql.host table mysql> describe mysql.host;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | NULL | |
| Db | char(64) | NO | PRI | NULL | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+ 25/10/2007 19
User Management-mysql.db table : User Management-mysql.db table mysql> describe mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | NULL | |
| Db | char(64) | NO | PRI | NULL | |
| User | char(16) | NO | PRI | NULL | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+ 25/10/2007 20
User Management-User creation –CREATE : User Management-User creation –CREATE Use create or grant command to create user :
Mysql>Create user identified by ‘’;
View the users : mysql> create user dbn;
mysql> create user dbn@localhost identified by 'abc123';
Mysql>create user ‘dbn’@’192.168.0.1’ identified by ‘abc123’;
Mysql>create user ‘dbn’@cipa.nic.in’ identified by ‘abc123’; Mysql> select user,host,password from mysql.user; 25/10/2007 21
User Management-User creation –GRANT : User Management-User creation –GRANT Use grant command to create user :
Mysql>GRANT ALL ON TO IDENTIFIED BY ;
View the users :
Show Grants for user: mysql>GRANT ALL ON *.* TO ‘dbn’@’localhost’ IDENTIFIED BY ‘nic123’;
mysql>GRANT ALL ON cipa.* TO ‘dbn’@’localhost’ IDENTIFIED BY ‘nic123’;
mysql>GRANT ALL ON *.* TO ‘dbn’@’cipa.nic.in’ IDENTIFIED BY ‘nic123’; Mysql> select user,host,password from mysql.user; Mysql> SHOW GRANTS FOR @; 25/10/2007 22
User Management-User creation –REVOKE : User Management-User creation –REVOKE Use grant command to create user with no privilege:
Mysql>GRANT USAGE ON TO IDENTIFIED BY ;
REVOKE PRIVILEGE from a user : mysql>GRANT USAGE ON *.* TO ‘dbn’@’localhost’ IDENTIFIED BY ‘nic123’;
Mysql>GRANT USAGE ON cipa.* TO dbn’@’localhost’ IDENTIFIED BY ‘nic123’; Mysql> REVOKE ALL PRIVILEGES ON *.* from ‘dbn’@’localhost’ 25/10/2007 23
User Management-User DELETE : User Management-User DELETE To remove user:
DROP
DELETE
Mysql>DROP USER ‘
Mysql>DELETE FROM mysql.user WHERE user=‘username’ AND HOST=‘hostname’;
After dropping user use command FLUSH PRIVILEGES to Flush all grants mysql> delete from mysql.user where user='hrm';
Mysql>delete from mysql.user where user=‘dbn’ and host=‘cipa.nic.in’;
Mysql> drop user dbn; 25/10/2007 24
User Management-Password : User Management-Password Two Common Ways to update password
set password method
update query against mysql.user table mysql>SET PASSWORD=PASSWORD(‘nic123’); /*
mysql> update mysql.user set password=password('nic123') where user='dbn' and host='localhost'; 25/10/2007 25
Recovery ROOT password : Recovery ROOT password Stop The Mysql Server
Start the server with - -skip-grant-tables
Or put the line skip-grant-tables under [mysqld] section.
Restart the server
Run mysql client and issue the following command.
Again Restart the server without skip-grant-tables parameter 25/10/2007 26 mysql> UPDATE mysql.user SET PASSWORD=PASSWORD('abc123') WHERE user='root';
Mysql>FLUSH PRIVILEGES
Client Utilities : Client Utilities Mysql – Terminal monitor
Mysqladmin – Admin Program
Mysqldump – Backup Utility
Mysqlimport – Import Utility from File
Mysqlshow - Shows information about database-tables-columns 25/10/2007 27
Client Utilities -mysql : Client Utilities -mysql Mysql client works in two modes
Interactive (Terminal monitor Mode)
Batch (Commands are submitted and results are sent to STDOUT) Interactive mode
#mysql –u root –pnic123
Mysql> Batch mode
# mysql -uroot -pnic123 -e 'select * from mysql.user;' 25/10/2007 28
Client Utilities -mysqladmin : Client Utilities -mysqladmin Administering mysql daemon
#mysqladmin [options] command1 command2 #mysqladmin –u root –pabc123 ping
#mysqladmin –uroot –pabc123 –h host ping
#mysqladmin –uroot –pabc123 –h processlist
#mysqladmin –u root –pabc123 kill processid /* kill process
#mysqladmin –u root –pabc123 create cipa /* create cipa database
#mysqladmin –u root –pabc123 drop cipa /* drop database
#mysqladmin –u root –pabc123 password ‘nic123’ /* password change
#mysqladmin –u root –pabc123 reload
#mysqladmin –u root –pabc123 -h localhost shutdown 25/10/2007 29
Client Utilities -mysqldump : Client Utilities -mysqldump Utility is used to backup data base
Backup can be done for all database or individual database
Mysql client utility will be used to import data from mysqldump data
Restore Data – using mysql utility #mysqldump -u root –pnic123 –all-databases > alldb.sql
#mysqldump -u root –pnic123 --databases contact > contactdb.sql
#mysqldump - -quick --all-databases > alldb.sql /* backing up large database #mysql –u root –pnic123 < contactdb.sql
#mysqldump --databases contact|mysql –uroot –pnic123 –h remotehost 25/10/2007 30
Client Utilities -mysqlimport : Client Utilities -mysqlimport Permits importing data from text files into tables
Mysqlimport [options] Mysql>create database contact;
Mysql>create table people(fname char(20),lname char(20),phone char(12),email char(30));
Create a text file people.txt in /var/lib/mysql directory and put data with tab separated.
e.G
Dbn nayak 9431327286 dbn@yahoo.com
Prem kumar 9431755555 prem@yahoo.com
Mysqlimport --local –u root –pnic123 contact people.txt 25/10/2007 31
Client Utilities -mysqlshow : Client Utilities -mysqlshow The utility shows information about database,table and columns
Syntax:
#mysqlshow [options] db_name tbl_name col_name
Example:
#mysqlshow –uroot –pnic123 //Will show all databases
#mysqlshow –uroot –pnic123 mysql //will show tables under mysql database
#mysqlshow –uroot –pnic123 mysql user // will show Columns under mysql .user table
#mysqlshow –uroot –pnic123 mysql user host // will show Column host information under mysql .user table 25/10/2007 32
Exploring SHOW Command : Exploring SHOW Command Mysql>show databases;
Mysql>show tables from ;
Mysql>show fields from ;
Mysql>show create database ; /how the database is created/
Mysql>show create table ;/how the table is created/
Mysql>show engines;
Mysql>show character set;
Mysql>show processlist;
Mysql>show variables; //system
Mysql>show status; 25/10/2007 33
Data Types : Data Types TINYINT unsigned,auto_increment Signed: -128 to 127 Unsigned: 0 to 255
SMALLINT unsigned,auto_increment Signed: -32,768 to 32,767 Unsigned 0 to 65,535
MEDIUMINT unsigned,auto_increment Signed: -8,388,608 to 8,388,607 Unsigned 0 to 16,777,215
INT unsigned,auto_increment Signed: -2,147,683,648 to 2,147,683,647 Unsigned: 0 to429,496,967,295
VARCHAR(#) 4-255 Stores up to 255 characters (fields adjust to max. value size) (use BLOB for case sensitivity) 25/10/2007 34
Data Types : Data Types TEXT Stores (almost) unlimited amount of data but reduces efficiency of querying, etc. (use BLOB for case sensitivity)
DATE (yyyy-mm-dd) 1000-01-01 to 9999-12-31
TIME (hhh-mm-ss) -838:59:59 to 838:59:59
DATETIME (yyyy-mm-dd hh-mm-ss) 1000-01-01 00:00:00 to 9999-12-31 23:59:59
YEAR (yyyy) 1901 to 2155
TIMESTAMP(#) # determines display pass nothing for this field,
2,4,6,8,10,12,14 it is automatically updated to the current system time 1970-01-01 to 2037 25/10/2007 35
Operators : Operators 25/10/2007 36
DML Statements : DML Statements mysql> SELECT VERSION(), CURRENT_DATE;
+----------------+--------------+
| VERSION() | CURRENT_DATE
+----------------+--------------+
| 5.0.7-beta-Max | 2005-07-11 |
+----------------+--------------+
1 row in set (0.01 sec)
mysql> 25/10/2007 37
DML Statements cont…… : DML Statements cont…… Keywords may be entered in any lettercase. The following queries are equivalent:
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE; 25/10/2007 38
DML Statements cont…… : DML Statements cont…… Here's another query. It demonstrates that you can use mysql as a simple calculator:
mysql> SELECT SIN(PI()/4), (4+1)*5;
+------------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 | 25 |
+------------------+---------+
1 row in set (0.02 sec) 25/10/2007 39
DML Statements cont…… : DML Statements cont…… You can even enter multiple statements on a single line. Just end each one with a semicolon:
mysql> SELECT VERSION(); SELECT NOW();
+----------------+
| VERSION() |
+----------------+
| 5.0.7-beta-Max |
+----------------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2005-07-11 17:59:36 |
+---------------------+
1 row in set (0.00 sec) 25/10/2007 40
DML Statements cont…… : DML Statements cont…… If you decide you do not want to execute a command that you are in the process of entering, cancel it by typing \c:
mysql> SELECT
-> USER()
-> \c
mysql> 25/10/2007 41
DML Statements cont…… : DML Statements cont…… Loading Data into a Table
To load the text file pet.txt into the pet table, use this command:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
Note that if you created the file on Windows with an editor that uses \r\n as a line terminator, you should use:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> FIELDS TERMINATED BY ','; 25/10/2007 42
DML Statements cont…… : DML Statements cont…… You could add a new record using an INSERT statement like this:
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL); 25/10/2007 43
DML Statements cont…… : DML Statements cont…… Retrieving Information from a Table
The SELECT statement is used to pull information from a table.
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;
Selecting All Data
mysql> SELECT * FROM pet; 25/10/2007 44
DML Statements cont…… : DML Statements cont…… Selecting Particular Rows
mysql> SELECT * FROM pet WHERE name = 'Bowser';
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
-> OR (species = 'dog' AND sex = 'f'); 25/10/2007 45
DML Statements cont…… : DML Statements cont…… Selecting Particular Columns
mysql> SELECT name, birth FROM pet;
mysql> SELECT owner FROM pet;
mysql> SELECT DISTINCT owner FROM pet;
mysql> SELECT name, species, birth FROM pet
-> WHERE species = 'dog' OR species = 'cat'; 25/10/2007 46
DML Statements cont…… : DML Statements cont…… Sorting Rows
mysql> SELECT name, birth FROM pet ORDER BY birth;
The default sort order is ascending, with smallest values first. To sort in reverse (descending) order, add the DESC keyword to the name of the column you are sorting by:
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
You can sort on multiple columns, and you can sort different columns in different directions. For example, to sort by type of animal in ascending order, then by birth date within animal type in descending order (youngest animals first), use the following query:
mysql> SELECT name, species, birth FROM pet
-> ORDER BY species, birth DESC; 25/10/2007 47
DML Statements cont…… : DML Statements cont…… Date Calculations
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5) AS age -> FROM pet;
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5) AS age FROM pet ORDER BY name; 25/10/2007 48
DML Statements cont…… : DML Statements cont…… Pattern Matching :
To find names beginning with ‘b’:
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
To find names ending with ‘fy’:
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
To find names containing a ‘w’:
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
To find names containing exactly five characters, use five instances of the ‘_’ pattern character:
mysql> SELECT * FROM pet WHERE name LIKE '_____'; 25/10/2007 49
DML Statements cont…… : DML Statements cont…… Deleting Information from a Table
The DELETE statement is used to delete data from a table.
DELETE FROM which_table
WHERE conditions_to_satisfy;
mysql> DELETE FROM pet;
This will delete all data from the table viz. pet 25/10/2007 50
DML Statements cont…… : DML Statements cont…… Update Information from a Table
The UPDATE statement is used to modify data from a table.
UPDATE which_table
SET column_name=value
WHERE conditions_to_satisfy;
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser'; 25/10/2007 51
DML Joins : DML Joins Normal join
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
Left join
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5; 25/10/2007 52