MySQL Commands

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

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..

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

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

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