MySQL Commands

This is a list of handy MySQL commands that I use from time to time. At the bottom are functions, statements and clauses you can use with 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.
The description of the command is listed above the code description.

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 “” not found /]

To delete a db.
mysql> drop database [database name];

To delete a table.
mysql> drop table [table “” not found /]

Show all data in a table.
mysql> SELECT * FROM [table “” not found /]

Returns the columns and column information pertaining to the designated table.
mysql> show columns from [table “” not found /]

Show certain selected rows with the value “whatever”.
mysql> SELECT * FROM [table “” not found /]
WHERE [field name] = "whatever";

Show all records containing the name “Bob” AND the phone number ‘3444444’.
mysql> SELECT * FROM [table “” not found /]
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 “” not found /]
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 “” not found /]
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 “” not found /]
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 “” not found /]
WHERE rec RLIKE "^a";

Show unique records.
mysql> SELECT DISTINCT [column name] FROM [table “” not found /]

Show selected records sorted in an ascending (asc) or descending (desc).
mysql> SELECT [col1],[col2] FROM [table “” not found /]

Return number of rows.
mysql> SELECT COUNT(*) FROM [table “” not found /]

Sum column.
mysql> SELECT SUM(*) FROM [table “” not found /]

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


mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

To update info already in a table.

mysql> UPDATE [table “” not found /]
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 “” not found /]
where [field name] = 'whatever';

Update database permissions/privilages.

mysql> flush privileges;

Delete a column.

mysql> alter table [table “” not found /]
drop column [column name];

Add a new column to db.
mysql> alter table [table “” not found /]
add column [new column name] varchar (20);

Change column name.
mysql> alter table [table “” not found /]
change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupelicates.
mysql> alter table [table “” not found /]
add unique ([column name]);

Make a column bigger.

mysql> alter table [table “” not found /]
modify [column name] VARCHAR(3);

Delete unique from table.
mysql> alter table [table “” not found /]
drop index [colmn name];

Load a CSV file into a table.
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table “” not found /]
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 “” not found /]
(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 “” not found /]
(personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

Help and Show Commands

$ mysql --help | less
$ mysqld --help
$ mysqlshow --help | less
$ mysqldump --help | less
$ mysqlshow - show all databases.
$ mysqlshow db_name - all tables in particular database.
$ mysqlshow db_name BA* - all tables which start from BA letters.

mysql> \?
mysql> use db_name;
mysql> show databases;
mysql> show databases like 'ba%'
mysql> show tables;
mysql> describe table_name;
mysql> select user(), now(), version(), database();
| user() | now() | version() | database() |
| ana@localhost | 2003-01-05 21:24:27 | 4.0.1-alpha-nt | test |

mysql> show tables from db_name
mysql> show tables from db_name like '__ab%'
mysql> show columns from table_name
mysql> show columns from table_name from db_name
mysql> show grants for user_name
mysql> show index from table_name
mysql> show index from table_name from db_name
mysql> show processlist
mysql> show status
mysql> show table status from db_name
mysql> show variables

MariaDB vs. MySQL

MySQL is dead. Long live MySQL. Now that oracle owns MySQL, it will certainly kill it as quickly and quietly as possible. If you were a large corporation, why would you want to have a stable, open source, widely used software platform competing with your fee related software that is generating a profit from the corporate world. The short and sweet answer is you wouldn’t. That is why MySQL is going away.


Luckily the founder of MySQL, Michael Widenius left Sun and started his own community branch of MySQL, called MariaDB. MariaDB is a backward compatible, drop-in replacement branch of MySQL® Database Server which includes all major open source storage engines, including the Maria storage engine. You can find the MariaDB code at Launchpad, and download binaries at Downloads.

MariaDB is community developed, in collaboration with Monty Program

Start checking it out now peeps, it is coming and you better learn about it now.


About MariaDB

MariaDB 5.1 is based on MySQL 5.1 and is available under the terms of the GPL v2 license.

MariaDB will be kept up to date with the latest MySQL release from the same branch.

In most respects MariaDB will work exactly as MySQL: all commands, interfaces, libraries and APIs that exist in MySQL also exist in MariaDB.

Currently the main differences between MariaDB 5.1 and MySQL 5.1 are:

In MariaDB 5.2, the new features are:

  • Group commit for the Maria storage engine. (faster)
  • userstatv2′, which creates several new information_schema tables with useful statistics information. You enable the statistics by setting the ‘userstat’ variable.

(for a definite and up-to-date list of features in 5.2, see MariaDB 5.2 page). has recently released MariaDB 5.1.41 RC for download and testing. You can download it here. It should be a drop in replacement for any recent MySQL 5.1 release.

As a final note, Oracle has made 10 promises regarding it’s commitment to Sun’s product line and is outlined here. Of course it should be noted that those promises were followed by a disclaimer called the “Cautionary Statement Regarding Forward-Looking Statement”
which makes quite clear that the document is in no way legally binding.

He that hath an ear, let him hear…


I recently had a client that went to backup a specific account using the cpanel backup script ‘pkgacct username’ located in the /scripts folder. Whenever he tried to use this script, the backup would fail. I found the error was not related to the cpanel script itself but to the gzip command itself. I needed to re-install gzip, which when complated, repaired the issue. In order to track this issue down, I used the ldd command

The ldd command is one that is rarely used by most and can be very helpful in deducing specific install error related to shared library dependencies. The actual ldd command is used for printing shared library dependencies like so;

root@host [~]# ldd /bin/tar => /lib/tls/ (0xb7eae000)
/lib/ (0xb7fe8000)

root@host [~]# ldd /bin/gzip => /lib/tls/ (0xb7eae000)
/lib/ (0xb7fe8000)

We can see that the tar/gzip commands uses the specified files in order to function correctly. If those files are corrupted or missing, then the pkgacct script will fail.

Using the ldd, gdb and strace commands helped me find and quickly solve this specific error.

Oh, to repair this error, I simply used RPM to uninstall and re-install the gzip package. I downloaded the rpm from CentOS;

rpm -qpR {.rpm-file} Find out what dependencies a rpm file has

rpm -ev {package} Erase/remove/ an installed package

rpm -ev –nodeps {package} Erase/remove/ an installed package without checking for dependencies

rpm -ivh {rpm-file} Install the package


I went to bed the other night after browsing the web for about an hour. I knew I was going to have a hard time waking up, so I setup a task on my linux laptop to wake me at 7:00am with a mp3 I wanted to wake up to.

“What the heck do you mean you setup a task, what’s a task” you asked? Well, linus has a command that will carry out a specified task or series of tasks at a specific time that you set. “How do I employ this wonder of wonders” you ask? well, let me show you.

At the prompt, type this command

at 0700

followed by the ‘Enter’ key

When you press ‘Enter’ the prompt will change into an arrow indicating that more information is needed from you for what command to run at 0700.

Type the following command (assuming you have XMMS installed) which will play the specified mp3 file (also assuming that you actually have a mp3 file located in the directory shown below

> xmms /home/myusername/mp3s/wake_my_ass-up.mp3

Once you have typed what program to run, and what mp3 to play, press ‘Enter’ again and then press ‘Ctrl-D’

That’s it. Now, your puter will make the XMMS player play that particular mp3 file at the specified time. (ugh, I HATE getting up early)

More advanced task scheduling is possible using ‘cron’, which is explained elsewhere on this site. If you want to repeatedly execute any task, whether hourly, daily, weekly, monthly or yearly, setup a script and use cron to run it everyday. Setup multiple scripts to execute multiple tasks daily. Nice huh?


Pipe, as the name implies, is a sort of hollow tube (a pipe) where you can put data into one end and use the output on the other end. It takes the output from one command and uses it as the input for the following command. This functionality is only one of the many features that make linux the most powerful and useful operating systems you will ever use.

On most qwerty keyboards, the pipe command will be located above the backslash ( \ ) key.

To use the pipe command, you simply need to place this symbol ” | ” between commands. Here is a simple example:

root@host# ls /bin | grep tar

In this example, we are listing the contents of the /bin directory and then using the output of that response to search for the term “tar”. (yes, I know this is a poor example of searching, it’s an example g33kboy, get over it… : ) ). This command outputs the lines “tar” and “unicode_start”. Why unicode_start you ask? look at the middle three letters in start… hmmm s”tar”t.

Lets use a little more advanced command;

root@host [/usr]# du -mh | sort -nr | head -10
1020K ./local/cpanel/3rdparty/etc/ixed/ixed.lin
1020K ./local/Zend/lib/Optimizer_TS-3.3.3/php-4.4.x
1020K ./local/Zend/lib/Optimizer_TS-3.3.3/php-4.3.x
1016K ./local/cpanel/base/3rdparty/roundcube/program/lib/encoding
1012K ./share/mysql-test/suite/jp/t
1012K ./share/doc/initscripts-7.93.34
1012K ./local/cpanel/src/Cpanel-TaskQueue-0.3
1012K ./local/cpanel/src/3rdparty/gpl/libxml2-2.6.23/python
1008K ./share/doc/lm_sensors-2.8.7/doc
1008K ./local/cpanel/base/3rdparty/roundcube/program/lib/MDB2/Driver

This command will the send the output of the disk usage command to the sort command, which then sends the results to the head command which returns the results seen above. The flags or switches used after the commands themselves are used to modify the commands results in a specific way;

-n Sort a column in numerical order
-r Sort in reverse order (so “Z” starts the list instead of “A”)

and then to the head command which displays the first few lines at the top of a file. the -10 shows the first 10 lines of the results.

A more complex example would be something like;

grep "SMTP connection from" /var/log/exim_mainlog |grep "connection count" |awk '{print $7}' |cut -d ":" -f 1 |cut -d "[" -f 2 |cut -d "]" -f 1 |sort -n |uniq -c | sort -n

This series of piped commands searches the exim_mainlog and pulls all the IPs in all the entries and line them up in an ascending order. I am not going to go into all of the specific commands and switches themselves as that would be an article unto itself.

One of the commands I frequently use when verifying disk space usage is;

du -sk ./* | sort -nr | awk 'BEGIN{ pref[1]="K"; pref[2]="M"; pref[3]="G";} { total = total + $1; x = $1; y = 1; while( x > 1024 ) { x = (x + 1023)/1024; y++; } printf("%g%s\t%s\n",int(x*10)/10,pref[y],$2); } END { y = 1; while( total > 1024 ) { total = (total + 1023)/1024; y++; } printf("Total: %g%s\n",int(total*10)/10,pref[y]); }'

This is also a command which outputs the results of disk usage sorted very nicely.

As you can see the pipe command is very versatile when linking commands together to locate specific information, or modify information contained within files or folders. It is infinitely configurable when used with multiple commands and the related switches or flags.


I just wanted to spend a few moments talking about backups. I just finished an email to a client who had 3 major databases crash because of Innodb issues which corrupted the tables beyond repair. I went to restore them and found the daily backup corrupted also. usually, there are weekly and monthly backup enabled in cPanel as well. Unfortunately, the client had changed those setting to save space. Guess what That means. No More Clients. Those 3 client may very well leave because of their entire site being destroyed.

This is a common occurrence I see all to often.

The backup functionality is there for a reason peeps, learn it, live it, love it. Please do not cheap out on utilizing it or making sure you have enough because the one time you need it will be the time you are glad you paid that extra little bit a month to have it in place.

I can hear some of you saying, “but what about all that extra cost? I can’t afford that extra amount every month!!!” My question to you is can you afford not to???

If your server crashes or your sql server loses it’s mind and begins singing zippity-do-dah while writing the entire book of Deuteronomy to your tables, is the 5 or 10 dollars you spent on lunch today more important than your 500 clients and all of their data? You do the math, you are the businessmen who run this show. But now, guess what you are now the ringmaster of the 3 ring fail circus, standing center stage while a giant elephant takes a sh@t on you and everybody, including your clients, their friends, family, acquaintances and everyone they come in contact with for the next week, including the garbage man, are going to hear about your failure.

Think i’m kidding about this? I cannot count the number of cancellations I process a week due to poor management skills. Luckily, we have more incoming requests that outgoing.

Just a word of warning here my friends, backup, backup, backup…

Linux Services

When starting your Linux server you may see services that are starting which may slow the boot process. i will show you how to enable/disable certain services and which ones are essential for your system.

First, a few words about runlevels. Each runlevel has a different purpose. In most Linux distributions runlevels are organized as follows:

Runlevel 1 – Single-User Mode : No network interfaces, daemons, or non-root logins.
Runlevel 2 – Multi-User Mode: No network interfaces or daemons.
Runlevel 3 – Multi-User Mode with Networking: Normal start.
Runlevel 4 – Unused.
Runlevel 5 – X11: Runlevel 3 + X Window System.

For more information regarding your specific distribution, please see

To see what the chkconfig usage is, simply run
root@host [~]# chkconfig
usage: chkconfig --list [name] chkconfig --add name
chkconfig --del name
chkconfig [--level levels ] name on|off|reset

In order to determine if a service is on or off we type “chkconfig –list [name]” like so;

root@host [~]# chkconfig --list httpd
httpd 0:off 1:off 2:on 3:on 4:on 5:on 6:off

The following command lists all of the services are running in our system and at what runlevel they operate:
chkconfig --list
similar to this

root@host [~]# /sbin/chkconfig --list
cpanel 0:off 1:off 2:off 3:on 4:on 5:on 6:off
lfd 0:off 1:off 2:on 3:on 4:on 5:on 6:off
sysstat 0:off 1:on 2:on 3:on 4:on 5:on 6:off
yum 0:off 1:off 2:off 3:off 4:off 5:off 6:off
netplugd 0:off 1:off 2:off 3:off 4:off 5:off 6:off
auditd 0:off 1:off 2:off 3:off 4:off 5:off 6:off
lm_sensors 0:off 1:off 2:on 3:off 4:on 5:on 6:off
snmptrapd 0:off 1:off 2:off 3:off 4:off 5:off 6:off
filelimits 0:off 1:off 2:on 3:on 4:on 5:on 6:off
xfs 0:off 1:off 2:on 3:on 4:on 5:on 6:off
dc_client 0:off 1:off 2:off 3:off 4:off 5:off 6:off
bandmin 0:off 1:off 2:on 3:on 4:on 5:on 6:off
saslauthd 0:off 1:off 2:off 3:on 4:off 5:off 6:off
courier-authlib 0:off 1:off 2:on 3:on 4:on 5:on 6:off
rawdevices 0:off 1:off 2:off 3:off 4:on 5:on 6:off
pure-ftpd 0:off 1:off 2:on 3:on 4:on 5:on 6:off
rdisc 0:off 1:off 2:off 3:off 4:off 5:off 6:off
exim 0:off 1:off 2:on 3:on 4:on 5:on 6:off
network 0:off 1:off 2:on 3:on 4:on 5:on 6:off
netfs 0:off 1:off 2:off 3:off 4:on 5:on 6:off
sshd 0:off 1:off 2:on 3:on 4:on 5:on 6:off
mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
nscd 0:off 1:off 2:off 3:off 4:off 5:off 6:off
spamassassin 0:off 1:off 2:on 3:on 4:on 5:on 6:off
portsentry 0:off 1:off 2:off 3:on 4:on 5:on 6:off
securetmp 0:off 1:off 2:on 3:on 4:on 5:on 6:off
courier-imap 0:off 1:off 2:on 3:on 4:on 5:on 6:off
winbind 0:off 1:off 2:off 3:off 4:off 5:off 6:off
ipaliases 0:off 1:off 2:on 3:on 4:on 5:on 6:off
snmpd 0:off 1:off 2:off 3:off 4:off 5:off 6:off
syslog 0:off 1:off 2:on 3:on 4:on 5:on 6:off
smb 0:off 1:off 2:off 3:off 4:off 5:off 6:off
iptables 0:off 1:off 2:on 3:on 4:on 5:on 6:off
dc_server 0:off 1:off 2:off 3:off 4:off 5:off 6:off
crond 0:off 1:off 2:on 3:on 4:on 5:on 6:off
httpd 0:off 1:off 2:on 3:on 4:on 5:on 6:off
xinetd 0:off 1:off 2:off 3:on 4:on 5:on 6:off
fastmail 0:off 1:off 2:on 3:on 4:on 5:on 6:off
ror 0:off 1:off 2:on 3:on 4:on 5:on 6:off
named 0:off 1:off 2:off 3:on 4:on 5:on 6:off
xinetd based services:
ntalk: off
time: off
rsync: off
daytime-udp: off
echo: off
chargen-udp: off
chargen: off
time-udp: off
daytime: off
popa3d: off
swat: off
finger: off
echo-udp: off
talk: off
popa3ds: off

For a complete list of linux services please visit

Prefork vs. Worker

Apache uses modules called MPM’s or Multi-Processing Modules for the most basic functions of a web server. The default apache setup contains a selection of of these MPM’s which are responsible for binding to network ports on the server, accepting and dispatching child processes to handle the requests from a browser in order to display a webpage.

Most likely you have Prefork running which is supposed to be better for single or dual core cpu systems, and worker is supposed to be better for multi-CPU systems.

Prefork is older technology, based off of the stable Apache 1.3 software. Prefork is also the default MPM for Apache 2.x. Prefork works, but in certain cases, it doesn’t handle large traffic loads as well as Worker.

Servers that were running prefork and were experiencing higher than average traffic usually run more stable with Worker. You will also see an increase in the amount of requests Apache can handle or serve up per second.

Worker uses a multi-process, multi-threaded approach for serving up webpages. By using many threads to serve requests, it is able to run better with less system resources than a individual process based server, yet it retains much of the stability of a process-based server by keeping multiple processes available, each with many threads.

Unfortunately, PHP5 does not work well with the Worker MPM out of the box, but it can be configured to work using CGI or FastCGI. To check your version of php simply run

[root@host] ~ >> php -v
PHP 5.2.9 (cli) (built: Oct 14 2009 03:46:53)

Check with a developer to see if this would be something you would be interested in upgrading to so he can check the sites on the server to make sure they are compatible with using worker.

This is the general idea behind using worker. It may better suit your needs and lower load spikes if it is compatible with your current sites.

More information is available here:

Create Apache default Success page

In order to modify this page, login to your server via ssh and cd over to /usr/local/apache/htdocs

cd /usr/local/apache/htdocs

Here, you can edit the file index.html

vim index.html

If you list the content of /usr/local/apache/htdocs you will see that you can also modify the content of other error pages that apache outputs as well.

Also, you can also edit these pages live using komposer using the Edit >> Publishing Site Settings and connecting to the server via ftp.

This site is one of the ways in which Red Hat gives something back to the open source community. Our desire is to create a connection point for conversations about the broader impact that open source can have–and is having–even beyond the software world. We think of this site as a “Red Hat community service.” Meaning: all ideas are welcome, and all participants are welcome.

This will not be a site for Red Hat, about Red Hat. Instead this will be a site for open source, about the future. What you see today is only a beginning. In the spirit of open source, we are releasing the site early. We will grow its functionality and content over time based on ideas we create together.

With your help, this will be a place that connects people, creates dialog, and–if we dare to dream–maybe even changes the world a little bit for the better.

Good to have you here. Let’s get started.