This is a list of handy MySQL commands that I use time and time again. At the bottom are statements, clauses, and functions you can use in MySQL. Below that are PHP and Perl API functions you can use to interface with MySQL. To use those you will need to build PHP with MySQL functionality. To use MySQL with Perl you will need to use the Perl modules DBI and DBD::mysql.
Below when you see # it means from the unix shell. When you see mysql> it means from a MySQL prompt after logging into MySQL.
To login (from unix shell) use -h only if needed.
# [mysql dir]/bin/mysql -h hostname -u root -p
Create a database on the sql server.
mysql> create database [databasename];
List all databases on the sql server.
mysql> show databases;
Switch to a database.
mysql> use [db name];
To see all the tables in the db.
mysql> show tables;
To see database's field formats.
mysql> describe [table name];
To delete a db.
mysql> drop database [database name];
To delete a table.
mysql> drop table [table name];
Show all data in a table.
mysql> SELECT * FROM [table name];
Returns the columns and column information pertaining to the designated table.
mysql> show columns from [table name];
Show certain selected rows with the value "whatever".
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";
Show all records containing the name "Bob" AND the phone number '3444444'.
mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';
Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.
mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
Show all records starting with the letters 'bob' AND the phone number '3444444'.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;
Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";
Show unique records.
mysql> SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc).
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows.
mysql> SELECT COUNT(*) FROM [table name];
Sum column.
mysql> SELECT SUM(*) FROM [table name];
Join tables on common columns.
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;
Change a users password from unix shell.
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'
Change a users password from MySQL prompt. Login as root. Set the password. Update privs.
# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;
Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
Set a root password if there is on root password.
# mysqladmin -u root password newpassword
Update a root password.
# mysqladmin -u root -p oldpassword newpassword
Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;
Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;
To update info already in a table.
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
Delete a row(s) from a table.
mysql> DELETE from [table name] where [field name] = 'whatever';
Update database permissions/privilages.
mysql> flush privileges;
Delete a column.
mysql> alter table [table name] drop column [column name];
Add a new column to db.
mysql> alter table [table name] add column [new column name] varchar (20);
Change column name.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.
mysql> alter table [table name] add unique ([column name]);
Make a column bigger.
mysql> alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.
mysql> alter table [table name] drop index [colmn name];
Load a CSV file into a table.
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
Dump all databases for backup. Backup file is sql commands to recreate all db's.
# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
Dump one database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
Dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1.
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table Example 2.
mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');
MYSQL Statements and clauses
ALTER DATABASE
ALTER TABLE
ALTER VIEW
ANALYZE TABLE
BACKUP TABLE
CACHE INDEX
CHANGE MASTER TO
CHECK TABLE
CHECKSUM TABLE
COMMIT
CREATE DATABASE
CREATE INDEX
CREATE TABLE
CREATE VIEW
DELETE
DESCRIBE
DO
DROP DATABASE
DROP INDEX
DROP TABLE
DROP USER
DROP VIEW
EXPLAIN
FLUSH
GRANT
HANDLER
INSERT
JOIN
KILL
LOAD DATA FROM MASTER
LOAD DATA INFILE
LOAD INDEX INTO CACHE
LOAD TABLE...FROM MASTER
LOCK TABLES
OPTIMIZE TABLE
PURGE MASTER LOGS
RENAME TABLE
REPAIR TABLE
REPLACE
RESET
RESET MASTER
RESET SLAVE
RESTORE TABLE
REVOKE
ROLLBACK
ROLLBACK TO SAVEPOINT
SAVEPOINT
SELECT
SET
SET PASSWORD
SET SQL_LOG_BIN
SET TRANSACTION
SHOW BINLOG EVENTS
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW CREATE DATABASE
SHOW CREATE TABLE
SHOW CREATE VIEW
SHOW DATABASES
SHOW ENGINES
SHOW ERRORS
SHOW GRANTS
SHOW INDEX
SHOW INNODB STATUS
SHOW LOGS
SHOW MASTER LOGS
SHOW MASTER STATUS
SHOW PRIVILEGES
SHOW PROCESSLIST
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES
SHOW WARNINGS
START SLAVE
START TRANSACTION
STOP SLAVE
TRUNCATE TABLE
UNION
UNLOCK TABLES
USE
String Functions
AES_DECRYPT
AES_ENCRYPT
ASCII
BIN
BINARY
BIT_LENGTH
CHAR
CHAR_LENGTH
CHARACTER_LENGTH
COMPRESS
CONCAT
CONCAT_WS
CONV
DECODE
DES_DECRYPT
DES_ENCRYPT
ELT
ENCODE
ENCRYPT
EXPORT_SET
FIELD
FIND_IN_SET
HEX
INET_ATON
INET_NTOA
INSERT
INSTR
LCASE
LEFT
LENGTH
LOAD_FILE
LOCATE
LOWER
LPAD
LTRIM
MAKE_SET
MATCH AGAINST
MD5
MID
OCT
OCTET_LENGTH
OLD_PASSWORD
ORD
PASSWORD
POSITION
QUOTE
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SHA
SHA1
SOUNDEX
SPACE
STRCMP
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UNCOMPRESS
UNCOMPRESSED_LENGTH
UNHEX
UPPER
Date and Time Functions
ADDDATE
ADDTIME
CONVERT_TZ
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATE_ADD
DATE_FORMAT
DATE_SUB
DATEDIFF
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
FROM_UNIXTIME
GET_FORMAT
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SEC_TO_TIME
SECOND
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIMEDIFF
TIMESTAMP
TIMESTAMPDIFF
TIMESTAMPADD
TIME_FORMAT
TIME_TO_SEC
TO_DAYS
UNIX_TIMESTAMP
UTC_DATE
UTC_TIME
UTC_TIMESTAMP
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Mathematical and Aggregate Functions
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
BIT_AND
BIT_OR
BIT_XOR
CEIL
CEILING
COS
COT
COUNT
CRC32
DEGREES
EXP
FLOOR
FORMAT
GREATEST
GROUP_CONCAT
LEAST
LN
LOG
LOG2
LOG10
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
STD
STDDEV
SUM
TAN
TRUNCATE
VARIANCE
Flow Control Functions
CASE
IF
IFNULL
NULLIF
Command-Line Utilities
comp_err
isamchk
make_binary_distribution
msql2mysql
my_print_defaults
myisamchk
myisamlog
myisampack
mysqlaccess
mysqladmin
mysqlbinlog
mysqlbug
mysqlcheck
mysqldump
mysqldumpslow
mysqlhotcopy
mysqlimport
mysqlshow
perror
Perl API - using functions and methods built into the Perl DBI with MySQL
available_drivers
begin_work
bind_col
bind_columns
bind_param
bind_param_array
bind_param_inout
can
clone
column_info
commit
connect
connect_cached
data_sources
disconnect
do
dump_results
err
errstr
execute
execute_array
execute_for_fetch
fetch
fetchall_arrayref
fetchall_hashref
fetchrow_array
fetchrow_arrayref
fetchrow_hashref
finish
foreign_key_info
func
get_info
installed_versions
last_insert_id
looks_like_number
neat
neat_list
parse_dsn
parse_trace_flag
parse_trace_flags
ping
prepare
prepare_cached
primary_key
primary_key_info
quote
quote_identifier
rollback
rows
selectall_arrayref
selectall_hashref
selectcol_arrayref
selectrow_array
selectrow_arrayref
selectrow_hashref
set_err
state
table_info
table_info_all
tables
trace
trace_msg
type_info
type_info_all
Attributes for Handles
PHP API - using functions built into PHP with MySQL
mysql_affected_rows
mysql_change_user
mysql_client_encoding
mysql_close
mysql_connect
mysql_create_db
mysql_data_seek
mysql_db_name
mysql_db_query
mysql_drop_db
mysql_errno
mysql_error
mysql_escape_string
mysql_fetch_array
mysql_fetch_assoc
mysql_fetch_field
mysql_fetch_lengths
mysql_fetch_object
mysql_fetch_row
mysql_field_flags
mysql_field_len
mysql_field_name
mysql_field_seek
mysql_field_table
mysql_field_type
mysql_free_result
mysql_get_client_info
mysql_get_host_info
mysql_get_proto_info
mysql_get_server_info
mysql_info
mysql_insert_id
mysql_list_dbs
mysql_list_fields
mysql_list_processes
mysql_list_tables
mysql_num_fields
mysql_num_rows
mysql_pconnect
mysql_ping
mysql_query
mysql_real_escape_string
mysql_result
mysql_select_db
mysql_stat
mysql_tablename
mysql_thread_id
mysql_unbuffered_query
Wednesday, August 18, 2010
Saturday, May 22, 2010
Introduction to lsof
Introduction to lsof
Overview
LiSt Open Files is a useful and powerful tool that will show you opened files. In Unix everything is a file: pipes are files, IP sockets are files, unix sockets are files, directories are files, devices are files, inodes are files...
Useful Examples
So in this tangle of files lsof listst files opened by processes running on your system.
When lsof is called without parameters, it will show all the files opened by any processes.
lsof | nl
Let us know who is using the apache executable file, /etc/passwd, what files are opened on device /dev/hda6 or who's accessing /dev/cdrom:
lsof `which apache2`
lsof /etc/passwd
lsof /dev/hda6
lsof /dev/cdrom
Now show us what process IDs are using the apache binary, and only the PID:
lsof -t `which apache2`
Show us what files are opened by processes whose names starts by "k" (klogd, kswapd...) and bash. Show us what files are opened by init:
lsof -c k
lsof -c bash
lsof -c init
Show us what files are opened by processes whose names starts by "courier", but exclude those whose owner is the user "zahn":
lsof -c courier -u ^zahn
Show us the processes opened by user apache and user zahn:
lsof -u apache,zahn
Show us what files are using the process whose PID is 30297:
lsof +p 30297
Search for all opened instances of directory /tmp and all the files and directories it contains:
lsof +D /tmp
List all opened internet sockets and sockets related to port 80:
lsof -i
lsof -i :80
List all opened Internet and UNIX domain files:
lsof -i -U
Show us what process(es) has an UDP connection opened to or from the host www.test.com at port 123 (ntp):
lsof -iUDP@www.test.com:123
lsof provides many more options and could be an unvaluable foresinc tool if your system get compromised or as daily basis check tool..
Overview
LiSt Open Files is a useful and powerful tool that will show you opened files. In Unix everything is a file: pipes are files, IP sockets are files, unix sockets are files, directories are files, devices are files, inodes are files...
Useful Examples
So in this tangle of files lsof listst files opened by processes running on your system.
When lsof is called without parameters, it will show all the files opened by any processes.
lsof | nl
Let us know who is using the apache executable file, /etc/passwd, what files are opened on device /dev/hda6 or who's accessing /dev/cdrom:
lsof `which apache2`
lsof /etc/passwd
lsof /dev/hda6
lsof /dev/cdrom
Now show us what process IDs are using the apache binary, and only the PID:
lsof -t `which apache2`
Show us what files are opened by processes whose names starts by "k" (klogd, kswapd...) and bash. Show us what files are opened by init:
lsof -c k
lsof -c bash
lsof -c init
Show us what files are opened by processes whose names starts by "courier", but exclude those whose owner is the user "zahn":
lsof -c courier -u ^zahn
Show us the processes opened by user apache and user zahn:
lsof -u apache,zahn
Show us what files are using the process whose PID is 30297:
lsof +p 30297
Search for all opened instances of directory /tmp and all the files and directories it contains:
lsof +D /tmp
List all opened internet sockets and sockets related to port 80:
lsof -i
lsof -i :80
List all opened Internet and UNIX domain files:
lsof -i -U
Show us what process(es) has an UDP connection opened to or from the host www.test.com at port 123 (ntp):
lsof -iUDP@www.test.com:123
lsof provides many more options and could be an unvaluable foresinc tool if your system get compromised or as daily basis check tool..
How to Mounting an USB External Hardrive on Linux Machine
Here are few tips to mounting your external or USB hardrive:
after you plug your usb drive just type in your console
[code] [root@austin]# dmesg [/code]
then look at this following message
usb hd plugin
at this picture the device is detected as sdb1
then you should now that the device you need to mount is on /dev/sdb1
now create the directory that will be linked to the drive
[code] mkdir /mnt/usbdrive [/code]
now mount your drive to the directory that just created before:
[code] mount -t (your partition type) /dev/sdb1 /mnt/usbdrive [/code]
remember, you should know your partition type corectly before mounting
you can try to check with this command:
[code][root@austin]#fdisk -l
Disk /dev/hda: 240 heads, 63 sectors, 1940 cylinders
Units = cylinders of 15120 * 512 bytes
Device Boot Start End Blocks Id System
/dev/hda 1 286 2162128+ c Win95 FAT32 (LBA)
/dev/hda2 * 288 1940 12496680 5 Extended
/dev/hda5 288 289 15088+ 83 Linux
/dev/hda6 290 844 4195768+ 83 Linux
/dev/hda7 845 983 1050808+ 82 Linux swap
/dev/hda8 984 1816 6297448+ 83 Linux
/dev/hda9 1817 1940 937408+ 83 Linux
/dev/sdb1 1 2010 156301488+ 83 Linux [/code]
Various filesystem types like xiafs, ext2, ext3, reiserfs is using id 83
Some systems mistakenly assume that 83 must mean ext2.
example on linux partition:
[code] mount -t ext2 /dev/sdb1 /mnt/usbdrive [/code]
if you see some error you can try other types of partition id 83
[code] mount -t ext3 /dev/sdb1 /mnt/usbdrive [/code]
if you see this kind message.
that's mean you have succesfully mounting your external hardrive!
now you can move your file through your new mounting directiory /mnt/usbdrive
FYI: the name of windows partition in linux is Vfat aka FAT file system or ntfs
the name of usb flash disk partition is usbfs
after you plug your usb drive just type in your console
[code] [root@austin]# dmesg [/code]
then look at this following message
usb hd plugin
at this picture the device is detected as sdb1
then you should now that the device you need to mount is on /dev/sdb1
now create the directory that will be linked to the drive
[code] mkdir /mnt/usbdrive [/code]
now mount your drive to the directory that just created before:
[code] mount -t (your partition type) /dev/sdb1 /mnt/usbdrive [/code]
remember, you should know your partition type corectly before mounting
you can try to check with this command:
[code][root@austin]#fdisk -l
Disk /dev/hda: 240 heads, 63 sectors, 1940 cylinders
Units = cylinders of 15120 * 512 bytes
Device Boot Start End Blocks Id System
/dev/hda 1 286 2162128+ c Win95 FAT32 (LBA)
/dev/hda2 * 288 1940 12496680 5 Extended
/dev/hda5 288 289 15088+ 83 Linux
/dev/hda6 290 844 4195768+ 83 Linux
/dev/hda7 845 983 1050808+ 82 Linux swap
/dev/hda8 984 1816 6297448+ 83 Linux
/dev/hda9 1817 1940 937408+ 83 Linux
/dev/sdb1 1 2010 156301488+ 83 Linux [/code]
Various filesystem types like xiafs, ext2, ext3, reiserfs is using id 83
Some systems mistakenly assume that 83 must mean ext2.
example on linux partition:
[code] mount -t ext2 /dev/sdb1 /mnt/usbdrive [/code]
if you see some error you can try other types of partition id 83
[code] mount -t ext3 /dev/sdb1 /mnt/usbdrive [/code]
if you see this kind message.
that's mean you have succesfully mounting your external hardrive!
now you can move your file through your new mounting directiory /mnt/usbdrive
FYI: the name of windows partition in linux is Vfat aka FAT file system or ntfs
the name of usb flash disk partition is usbfs
How To Format Harddrive in Linux/Unix Console
Here is a simple steps how to formatting a new harddrive in linux/unix console :
First, we need to take a look all partition in your server
[code]$fdisk -l
.....
.....
Disk /dev/hdd: 320.0 GB, 320072933376 bytes
255 heads, 63 sectors/track, 38913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System[/code]
You will see a result above that tell /dev/hdd is have 320GB but don't have any partition on it.
We need to create a new partition for /dev/hdd with these steps below :
[code]$fdisk /dev/hdd
Command : n
Command Action : p
Partition Number : 1
First Cylinder : Enter
Last Cylinder : Enter
Command : p
Disk /dev/hdd: 320.0 GB, 320072933376 bytes
255 heads, 63 sectors/track, 38913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/hdd1 1 38913 312568641 83 Linux
Command : w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.[/code]
You will see that /dev/hdd1 is ready to format
We need to verify all devices again before do a format
[code]$fdisk -l
......
Disk /dev/hdd: 320.0 GB, 320072933376 bytes
255 heads, 63 sectors/track, 38913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/hdd1 1 38913 312568641 83 Linux
$ls -al /dev/hdd1
brw-r----- 1 root disk 22, 65 Sep 9 21:00 /dev/hdd1[/code]
Next, we will start formatting the harddrive partition file system with this command below
[code]$mkfs.ext3 /dev/hdd1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
39075840 inodes, 78142160 blocks
3907108 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
2385 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424, 20480000, 23887872, 71663616
Writing inode tables: 1996/2385
.....
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information:
done
This filesystem will be automatically checked every 24 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.[/code]
Finally, We just need to mount the partition and your new harddrive is ready to use
First, we need to take a look all partition in your server
[code]$fdisk -l
.....
.....
Disk /dev/hdd: 320.0 GB, 320072933376 bytes
255 heads, 63 sectors/track, 38913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System[/code]
You will see a result above that tell /dev/hdd is have 320GB but don't have any partition on it.
We need to create a new partition for /dev/hdd with these steps below :
[code]$fdisk /dev/hdd
Command : n
Command Action : p
Partition Number : 1
First Cylinder : Enter
Last Cylinder : Enter
Command : p
Disk /dev/hdd: 320.0 GB, 320072933376 bytes
255 heads, 63 sectors/track, 38913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/hdd1 1 38913 312568641 83 Linux
Command : w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.[/code]
You will see that /dev/hdd1 is ready to format
We need to verify all devices again before do a format
[code]$fdisk -l
......
Disk /dev/hdd: 320.0 GB, 320072933376 bytes
255 heads, 63 sectors/track, 38913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/hdd1 1 38913 312568641 83 Linux
$ls -al /dev/hdd1
brw-r----- 1 root disk 22, 65 Sep 9 21:00 /dev/hdd1[/code]
Next, we will start formatting the harddrive partition file system with this command below
[code]$mkfs.ext3 /dev/hdd1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
39075840 inodes, 78142160 blocks
3907108 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
2385 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424, 20480000, 23887872, 71663616
Writing inode tables: 1996/2385
.....
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information:
done
This filesystem will be automatically checked every 24 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.[/code]
Finally, We just need to mount the partition and your new harddrive is ready to use
Saturday, April 24, 2010
How To Create a Container/VPS Machine With OpenVZ Server
After We have installed a OpenVZ VPS Master Server, We can create a container or VPS Machine on it.
Please see this URL if you still don't have OpenVZ VPS Master Server running/installed on your server.
http://wowtutorial.org/en/tutorial/97.html
Create a VPS Machine in OpenVZ VPS Master Server
First we need to make sure we have a vps config in /etc/vz/conf
All vps config that we have created will store in this directory
VPS config is use to define a harddrive space, memory, and other config stuff
Example there are 3 config file there
[code]$ls -al /etc/vz/conf
ve-light.conf-sample
ve-vps.basic.conf-sample
ve-vps.heavy.conf-sample[/code]
Next, We will need to make sure we have precreated template in /vz/template/cache directory
[code]$ls -al /vz/template/cache
centos-4-i386-default.tar.gz fedora-core-5-i386-minimal.tar.gz-old
centos-4-i386-default.tar.gz-old fedora-core-6-i686-default.tar.gz
centos-4-i386-minimal.tar.gz fedora-core-7-i386-default.tar.gz
centos-4-i386-minimal.tar.gz-old fedora-core-7-i386-minimal.tar.gz
centos-5-i386-minimal.tar.gz mandriva-2006-i386-minimal.tar.gz
debian-3.1-i386-minimal.tar.gz opensuse-10-i386-default.tar.gz
fedora-8-i386-minimal.tar.gz slackware-12.0-i386-minimal.tar.gz
fedora-8-i386-minimal.tar.gz.1 ubuntu-6.06-i386-minimal.tar.gz
fedora-core-5-i386-default.tar.gz ubuntu-7.10-i386-minimal.tar.gz
fedora-core-5-i386-minimal.tar.gz[/code]
If we have a config and precreated template we can start create a VPS Machine on OpenVZ VPS Master Server
We will use vzctl command to create,start,stop,destroy,etc.. the VPS Machine
Example command to create a VPS Machine:
[code]$vzctl create NODE --config yourconfigfile --ostemplate yourostemplate --ipadd yourvpsipadd[/code]
Here is the real examples command
[code]$vzctl create 1 --config vps.basic --ostemplate centos-4-i386-default --ipadd 10.10.1.2[/code]
Example config is : ve-vps.basic.conf-sample we can just use vps.basic in --config line command
NODE here is just numbering your VPS. we can name it 1, 100, 1000 ,etc
Next, We need to set a nameserver & Hostname for brandnew VPS Machine
[code]$vzctl set 1 --nameserver 10.10.1.50 --hostname server.testing.com --save[/code]
Note: Example Nameserver ip address is 10.10.1.50
After that, we can start the VPS Machine with this command
[code]$vzctl start 1[/code]
We also can directly enter into VPS Machine from VPS Master Server
[code]$vzctl enter 1[/code]
This command will automatically let us enter NODE 1 which is our brandnew vps
Then we can just use passwd to change the root password
If we want to exit from container/vps machine 1, it's simple type exit
[code]$exit[/code]
it will automatically logout and stay in OpenVZ Master VPS Machine again
To take a look a running VPS Machine type
[code]$vzlist
VEID NPROC STATUS IP_ADDR HOSTNAME
1 46 running 10.10.1.2 server.testing.com[/code]
Next Step is trying to connect the ip from outside network using putty.
If you are able to connect into 10.10.1.2 port 22 it's mean you are successfully configure a container/VPS Machine
If you want to stop the VPS Machine type
[code]$vzctl stop 1[/code]
It will automatically stop container/vps machine 1 (server.testing.com)
If you want to delete/destroy your VPS Machine, type
[code]$vzctl destroy 1[/code]
Note : you need to stop vps machine 1 first, before destroy the vps machine
Please see this URL if you still don't have OpenVZ VPS Master Server running/installed on your server.
http://wowtutorial.org/en/tutorial/97.html
Create a VPS Machine in OpenVZ VPS Master Server
First we need to make sure we have a vps config in /etc/vz/conf
All vps config that we have created will store in this directory
VPS config is use to define a harddrive space, memory, and other config stuff
Example there are 3 config file there
[code]$ls -al /etc/vz/conf
ve-light.conf-sample
ve-vps.basic.conf-sample
ve-vps.heavy.conf-sample[/code]
Next, We will need to make sure we have precreated template in /vz/template/cache directory
[code]$ls -al /vz/template/cache
centos-4-i386-default.tar.gz fedora-core-5-i386-minimal.tar.gz-old
centos-4-i386-default.tar.gz-old fedora-core-6-i686-default.tar.gz
centos-4-i386-minimal.tar.gz fedora-core-7-i386-default.tar.gz
centos-4-i386-minimal.tar.gz-old fedora-core-7-i386-minimal.tar.gz
centos-5-i386-minimal.tar.gz mandriva-2006-i386-minimal.tar.gz
debian-3.1-i386-minimal.tar.gz opensuse-10-i386-default.tar.gz
fedora-8-i386-minimal.tar.gz slackware-12.0-i386-minimal.tar.gz
fedora-8-i386-minimal.tar.gz.1 ubuntu-6.06-i386-minimal.tar.gz
fedora-core-5-i386-default.tar.gz ubuntu-7.10-i386-minimal.tar.gz
fedora-core-5-i386-minimal.tar.gz[/code]
If we have a config and precreated template we can start create a VPS Machine on OpenVZ VPS Master Server
We will use vzctl command to create,start,stop,destroy,etc.. the VPS Machine
Example command to create a VPS Machine:
[code]$vzctl create NODE --config yourconfigfile --ostemplate yourostemplate --ipadd yourvpsipadd[/code]
Here is the real examples command
[code]$vzctl create 1 --config vps.basic --ostemplate centos-4-i386-default --ipadd 10.10.1.2[/code]
Example config is : ve-vps.basic.conf-sample we can just use vps.basic in --config line command
NODE here is just numbering your VPS. we can name it 1, 100, 1000 ,etc
Next, We need to set a nameserver & Hostname for brandnew VPS Machine
[code]$vzctl set 1 --nameserver 10.10.1.50 --hostname server.testing.com --save[/code]
Note: Example Nameserver ip address is 10.10.1.50
After that, we can start the VPS Machine with this command
[code]$vzctl start 1[/code]
We also can directly enter into VPS Machine from VPS Master Server
[code]$vzctl enter 1[/code]
This command will automatically let us enter NODE 1 which is our brandnew vps
Then we can just use passwd to change the root password
If we want to exit from container/vps machine 1, it's simple type exit
[code]$exit[/code]
it will automatically logout and stay in OpenVZ Master VPS Machine again
To take a look a running VPS Machine type
[code]$vzlist
VEID NPROC STATUS IP_ADDR HOSTNAME
1 46 running 10.10.1.2 server.testing.com[/code]
Next Step is trying to connect the ip from outside network using putty.
If you are able to connect into 10.10.1.2 port 22 it's mean you are successfully configure a container/VPS Machine
If you want to stop the VPS Machine type
[code]$vzctl stop 1[/code]
It will automatically stop container/vps machine 1 (server.testing.com)
If you want to delete/destroy your VPS Machine, type
[code]$vzctl destroy 1[/code]
Note : you need to stop vps machine 1 first, before destroy the vps machine
Thursday, September 3, 2009
WordPress or phpBB not Sending Email? Try this fix.
Okay, for anybody who just upgraded Wordpress, only to realize you’re not getting notification emails anymore, or the PHP mail() function isn’t working, or SMTP isn’t working, here is your solution. Yes, this is guaranteed to work, even if you’re using Gmail.
Method 1
If you are using c-panel and if you have signed up with google mail application then login to your C-panel and find the option MX Entry.
Make sure that your setting is same as shown in image below
Click image to Zoome it
Method 2
1. Go to your hosting cpanel and create this email account: wordpress@yourdomain.com. Replace “yourdomain.com” with whatever your domain name is. Make sure that if you have a .net, that you use .net instead of .com in that email. Set up your username and password and remember it.
2. Download WP-Mail-SMTP or Cimy Swift SMTP. Just pick one of these and install and activate the plugin.
3a. Configure your SMTP plugin under Settings or Tools in your sidebar. This is the most important step! Do not fill in this info with what Gmail tells you to put in. Do the following instead:
3b. Sender e-mail must be: the new email account you created in your hosting cpanel. Put in the full wordpress@yourdomain.com in there.
3c. If there is a checkbox to use SMTP, check it.
3d. SMTP server address will be: mail.yourdomain.com. Replace “yourdomain.com” with your domain name again.
3e. Port will be 25 or 26. Try those before trying 456 for gmail. Contact your hosting provider if neither works. The chances of neither of these working are small.
3f. Username would be: wordpress@yourdomain.com. Replace “yourdomain.com” with your domain name again.
3g. Password is the password for that account.
3h. Set SSL or TLS to no or no encryption. Try these before using TLS for gmail. Contact your hosting provider in case they require encryption, if this doesn’t work.
4. Wordpress@yourdomain.com will now send the email to your personal account. Make sure that the email on your profile is correct, whether it’s yahoo, hotmail, gmail or any other account. If you are using a contact form, make sure that the email is set to your personal account, as well. The emails should now go to your spam or inbox.
If it still doesn’t work, you can have your wordpress@yourdomain.com send the email to itself and have your personal account pop forward it. Read the following if you are still having problems. Gmail is used as an example because most people have problems getting it to agree with their wordpress/hosting.
5a. Make sure that the email on your profile is changed to the wordpress@yourdomain.com email. If you are using a contact form, make sure that the emails are sent to the wordpress@yourdomain.com email. *This is important because wordpress and Gmail conflict and this is the only workaround I’ve come up with.
5b. Now, wordpress is set up to use SMTP to send emails out with your wordpress@yourdomain.com email. Step 5a makes sure that all the emails are sent to that same account. So basically, wordpress@yourdomain.com will send emails to itself.
5c. Now login to Gmail and go to Settings.
5d. Under Settings, go to Accounts.
5e. Under “Get mail from other accounts,” go to “Add a mail account you own”.
5f. Username is wordpress@yourdomain.com. Replace “yourdomain.com” with your domain name.
5g. Password is wordpress@yourdomain.com’s password.
5h. POP Server should be mail.yourdomain.com. Replace “yourdomain.com” with your domain name.
5i. Port should be 110.
5j. Click Save Changes.
6. Now, Wordpress will send the emails to the hosting server email account. And Gmail will fetch them. You could also create a Yahoo or Hotmail account and skip step 5 entirely, and then have Yahoo or Hotmail forward those emails to Gmail.
If you are still having problems, or need assistance, leave a comment. And good luck.
Monday, August 3, 2009
Create user in mysql
The mysql user information is kept inside USER table under mysql database. So creation of new user will affect the USER table of mysql database. In order to know a list of mysql users according with user table's description issue,
Here test is the root account password.
mysql -u root -ptest
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.30-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql;
Database changed
By query the Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv etc you can check whether user has certain types of privilege or not.
There are several ways to create user in mysql which is described below.
Way 01: With the CREATE USER statement
1)Connect to mysql database as a user who must have the global CREATE USER privilege or the INSERT privilege for the mysql database. You can use root because by default root has privilege to create user.
2)In fact CREATE USER statement creates a new record in the mysql.user table that has no privileges assigned by default.
mysql> create user arju;
Query OK, 0 rows affected (0.13 sec)
Notice that if you specify only the user name part of the account name while creating user, a host name part of '%' is used.
User arju is not assigned any password in this way as you see password is null. To assign password to user arju issue,
mysql> set password for 'arju'=password('test');
Query OK, 0 rows affected (0.03 sec)
In one command you can set password while creating user. The following command will crate a user arju2 and password is test in the host localhost. Both password, username and hostname should be within single quote.
mysql> create user 'arju2'@'localhost' identified by 'test';
Query OK, 0 rows affected (0.00 sec)
If you don't put username, host name and password within single quote syntax error will be resulted.
mysql> create user 'arju2'@'localhost' identified by test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test' at line 1
An equivalent statement of setting password is issuing update statement.
mysql> UPDATE mysql.user SET Password=PASSWORD('test')
-> WHERE User='arju' AND Host='%';
Query OK, 0 rows affected (0.05 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Changing via update and insert of the user table, it is necessary to use FLUSH PRIVILEGES to tell the server to reload the grant tables. Otherwise, the changes go unnoticed until you restart the server.
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.08 sec)
Now, have a look that connecting to mysql database as user arju will fail but arju2 will be successful.
D:\xampp\mysql\bin>mysql -u arju -ptest
ERROR 1045 (28000): Access denied for user 'arju'@'localhost' (using password: YES)
D:\xampp\mysql\bin>mysql -u arju2 -ptest
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.1.30-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Note that the 'arju'@'localhost' account can be used only when connecting from the local host. The 'arju'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.
You can check the privilege assigned for a user by issuing,
By default it is search for host '%'.
Way 02: With the grant option
To create a user named momin with password test2 and all privileges for a particular database called ecommerce issue,
mysql> grant ALL PRIVILEGES on ecommerce.* to 'momin'@'localhost' identified by 'test2';
Query OK, 0 rows affected (0.00 sec)
mysql> use mysql;
Database changed
D:\xampp\mysql\bin>mysql -u momin -ptest2
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.1.30-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Here ecommerce database is shown as we have given permission.
Way 03: Using INSERT statement into Table mysql.user
You can also create user in mysql by simply INSERT a new row into mysql.user table. Note that creating user through INSERT statement needs to use FLUSH PRIVILEGES to tell the server to reload the grant tables. If we don't do that the changes go unnoticed until you restart the server. This restriction is not applicable in CREATE USER statement.
Insert statement can be issued in two ways. One way to create user robert,
mysql> use mysql;
Database changed
mysql> INSERT INTO user SET Host='localhost',User='robert', Password=password('test');
Query OK, 1 row affected, 3 warnings (0.00 sec)
Alternative way of creating user richard is,
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','richard',password('test'));
Query OK, 1 row affected, 3 warnings (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Check by,
See that while setting password we used the PASSWORD() function with INSERT in order to encrypt the password. But with the CREATE USER statement it was unnecessary as create user statement automatically encrypts the password.
Here test is the root account password.
mysql -u root -ptest
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.30-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql;
Database changed
mysql> desc user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| 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 | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_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 | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.14 sec)
mysql> select host, user from user;
+-----------+---------+
| host | user |
+-----------+---------+
| % | magento |
| 127.0.0.1 | root |
| localhost | |
| localhost | magento |
| localhost | pma |
| localhost | root |
+-----------+---------+
6 rows in set (0.00 sec)
By query the Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv etc you can check whether user has certain types of privilege or not.
There are several ways to create user in mysql which is described below.
Way 01: With the CREATE USER statement
1)Connect to mysql database as a user who must have the global CREATE USER privilege or the INSERT privilege for the mysql database. You can use root because by default root has privilege to create user.
2)In fact CREATE USER statement creates a new record in the mysql.user table that has no privileges assigned by default.
mysql> create user arju;
Query OK, 0 rows affected (0.13 sec)
mysql> select user,password, host, select_priv, insert_priv from mysql.user where user='arju';
+------+----------+------+-------------+-------------+
| user | password | host | select_priv | insert_priv |
+------+----------+------+-------------+-------------+
| arju | | % | N | N |
+------+----------+------+-------------+-------------+
1 row in set (0.00 sec)
Notice that if you specify only the user name part of the account name while creating user, a host name part of '%' is used.
User arju is not assigned any password in this way as you see password is null. To assign password to user arju issue,
mysql> set password for 'arju'=password('test');
Query OK, 0 rows affected (0.03 sec)
mysql> select user,password, host, select_priv, insert_priv from mysql.user where user='arju';
+------+-------------------------------------------+------+-------------+-------------+
| user | password | host | select_priv | insert_priv |
+------+-------------------------------------------+------+-------------+-------------+
| arju | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | % | N | N |
+------+-------------------------------------------+------+-------------+-------------+
1 row in set (0.00 sec)
In one command you can set password while creating user. The following command will crate a user arju2 and password is test in the host localhost. Both password, username and hostname should be within single quote.
mysql> create user 'arju2'@'localhost' identified by 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,password, host, select_priv, insert_priv from mysql.user where user='arju2';
+-------+-------------------------------------------+-----------+-------------+-------------+
| user | password | host | select_priv | insert_priv |
+-------+-------------------------------------------+-----------+-------------+-------------+
| arju2 | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | localhost | N | N |
+-------+-------------------------------------------+-----------+-------------+-------------+
1 row in set (0.00 sec)
If you don't put username, host name and password within single quote syntax error will be resulted.
mysql> create user 'arju2'@'localhost' identified by test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test' at line 1
An equivalent statement of setting password is issuing update statement.
mysql> UPDATE mysql.user SET Password=PASSWORD('test')
-> WHERE User='arju' AND Host='%';
Query OK, 0 rows affected (0.05 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Changing via update and insert of the user table, it is necessary to use FLUSH PRIVILEGES to tell the server to reload the grant tables. Otherwise, the changes go unnoticed until you restart the server.
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.08 sec)
mysql> select user,password, host, select_priv, insert_priv from mysql.user where user='arju';
+------+-------------------------------------------+------+-------------+-------------+
| user | password | host | select_priv | insert_priv |
+------+-------------------------------------------+------+-------------+-------------+
| arju | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | % | N | N |
+------+-------------------------------------------+------+-------------+-------------+
1 row in set (0.00 sec)
Now, have a look that connecting to mysql database as user arju will fail but arju2 will be successful.
D:\xampp\mysql\bin>mysql -u arju -ptest
ERROR 1045 (28000): Access denied for user 'arju'@'localhost' (using password: YES)
D:\xampp\mysql\bin>mysql -u arju2 -ptest
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.1.30-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select user();
+-----------------+
| user() |
+-----------------+
| arju2@localhost |
+-----------------+
1 row in set (0.00 sec)
Note that the 'arju'@'localhost' account can be used only when connecting from the local host. The 'arju'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.
You can check the privilege assigned for a user by issuing,
mysql> show grants for arju;
+-----------------------------------------------------------------------------------------------------+
| Grants for arju@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'arju'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for arju2;
ERROR 1141 (42000): There is no such grant defined for user 'arju2' on host '%'
mysql> show grants for arju2@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for arju2@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'arju2'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
By default it is search for host '%'.
Way 02: With the grant option
To create a user named momin with password test2 and all privileges for a particular database called ecommerce issue,
mysql> grant ALL PRIVILEGES on ecommerce.* to 'momin'@'localhost' identified by 'test2';
Query OK, 0 rows affected (0.00 sec)
mysql> use mysql;
Database changed
mysql> select host, user from user where user='momin';
+-----------+-------+
| host | user |
+-----------+-------+
| localhost | momin |
+-----------+-------+
1 row in set (0.00 sec)
D:\xampp\mysql\bin>mysql -u momin -ptest2
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.1.30-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ecommerce |
| test |
+--------------------+
3 rows in set (0.00 sec)
Here ecommerce database is shown as we have given permission.
Way 03: Using INSERT statement into Table mysql.user
You can also create user in mysql by simply INSERT a new row into mysql.user table. Note that creating user through INSERT statement needs to use FLUSH PRIVILEGES to tell the server to reload the grant tables. If we don't do that the changes go unnoticed until you restart the server. This restriction is not applicable in CREATE USER statement.
Insert statement can be issued in two ways. One way to create user robert,
mysql> use mysql;
Database changed
mysql> INSERT INTO user SET Host='localhost',User='robert', Password=password('test');
Query OK, 1 row affected, 3 warnings (0.00 sec)
Alternative way of creating user richard is,
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','richard',password('test'));
Query OK, 1 row affected, 3 warnings (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Check by,
mysql> select user, host, password from user where user in('robert','richard');
+---------+-----------+-------------------------------------------+
| user | host | password |
+---------+-----------+-------------------------------------------+
| robert | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| richard | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+---------+-----------+-------------------------------------------+
2 rows in set (0.01 sec)
See that while setting password we used the PASSWORD() function with INSERT in order to encrypt the password. But with the CREATE USER statement it was unnecessary as create user statement automatically encrypts the password.
Subscribe to:
Posts (Atom)
