Linux Troubleshooting

Linux is legendary for its stability - once set up correctly, a Linux box, left to its own devices, will run trouble-free for a very long time. Most problems arise soon after installation or major configuration changes, and are the result of misconfiguration, typographical errors or the occasional hardware failure.

However, from time to time accidents do happen, even in the best-regulated environments . . .

A Linux Troubleshooting Toolkit

The best way to minimise the impact of those unforeseeable events is to prepate for them, by assembling the recovery tools in advance

Tom's Root Boot Disk

An essential part of every Linux professional's bag of tricks, this tiny (by today's standards) package unpacks to create a 1.722 MB floppy disk that is a complete Linux distribution with a selection of recovery tools - until you see how it's done you'll find it hard to believe a single floppy can contain so much!

An alternative version comes in El Torito (bootable CD-ROM) format . You can download tomsrtbt from http://www.toms.net/rb/

Knoppix

This is a popular Linux distribution, based on Debian, which boots and runs entirely from CD-ROM. While it is popular for demonstrations, or for letting interested users get a taste of Linux without having to install a distribution on the hard drive, it is also incredibly useful as a system repair tool. You can download Knoppix fromhttp://www.knopper.net/knoppix/index-en.html (read the notes on software patents, then click on the KNOPPIX link - it's still there).

mkbootdisk

Most Linux distributions have a command to build a bootable floppy disk which can be used to repair a system. Red Hat Linux, for example, has the mkbootdiskcommand. In order to use this, you only need to know the desired kernel version to write to floppy, and you can find the current kernel version with the uname -rcommand:

mkbootdisk 2.4.20-8

or

mkbootdisk `uname -r`

In general, mkbootdisk and similar utilities will read various configuration files, such as /etc/fstab and /boot/grub/grub.conf, in order to work out the root filesystem, any required kernel command-line arguments and the drivers which will need to be loaded from the generated ramdisk image. One useful but not widely-known option for mkbootdisk is the --iso option, which makes a bootable CD-ROM image. This can then be updated with additional utilities, etc. if required.

Other Boot Disks

Most Linux distributions allow you to boot from the first installation CD in a system repair or 'rescue' mode. For Red Hat, for example, using the first CD-ROM to boot with the command 'linux rescue' will boot the system and then attempt a number of basic repairs automatically. The repair script will attempt to identify all the Linux partitions on your hard drives and mount them in the correct location. At the end of this process, you should wind up with the system completely assembled and mounted under /mnt/sysimage.

Red Hat Linux Professional boxed sets of recent vintage also include a rather neat credit-card-sized rescue CD, and similar CD's are sometimes available from Linux-related company stands at trade shows.

Problems:

Can't Boot?

Watch the system closely as it boots, and take note of any error messages that appear. If the system complains that it is unable to mount the root filesystem, for example, this can be for any of several reasons:

• The BIOS cannot find the boot loader. This sometimes happens after you've installed Linux to dual-boot with Windows, but - out of concern to not misconfigure the system - have asked the install program to place the boot loader in the Linux root (or /boot) filesystem. The problem is that the BIOS can't see it there, unless you make that the active partition. The simplest fix is to reinstall Linux and this time, let it place the LILO or GRUB boot loader into the Master Boot Record - don't worry, the Linux boot loaders are automatically set up to let you choose Linux or Windows at boot time. It is possible to perform a more complex fix, for example by copying the Linux boot loader sector into a file, and setting up the Windows NT/2K/XP boot loader to chain to it - but that is too complex to describe here (seehttp://www.lesbell.com.au/Home.nsf/web/Using+the+NT+Boot+Loader+to+Boot+Linux?OpenDocument where you'll find a longer article describing how to use the NT boot loader to boot Linux).
• The kernel doesn't have a device driver to access the hard drive (e.g. a SCSI drive). Fix this by using the mkinitrd script to build a new initrd file that contains the correct drivers, or recompile the kernel to include the driver code. This usually happens because you've built a new kernel and slightly messed up the configuration.
• The kernel doesn't have a filesystem driver to access the root partition. For example, if the root filesystem is formatted with ext3, then you will need the ext3 andjbd modules in the initrd or compiled into the kernel. Fix as for the previous problem. Again, this usually happens after building a new kernel.
• The partition table has been modified, for example, by the installation of another operating system. In this case, edit the kernel command line (in /ec/lilo.confor /boot/grub/menu.lst) and the contents of /etc/fstab to contain the correct entries.
• Filesystems are corrupted, due to a power failure or system crash. Generally, after a system crash or power outage (what? No UPS?), the system will come up and repair itself. If you are using a journalling filesystem like ext3fs, jfs, xfs or resiserfs, it will usually perform a roll-forward recovery from its journal file and carry on. Even with the older ext2fs, the system usually runs an fsck (file system check) on the various file systems and repairs them automatically. However, just occasionally manual intervention is required - ; you might have to answer 'Y' to a string of questions (answering 'N' will get you nowhere unless you intend to perform really low-level repairs yourself in a last-ditch attempt to avoid data loss). In the worst case, you might have to reboot from rescue media and manuall run the e2fsck (or similar) command against each filesystem in turn. For example:
  e2fsck -p /dev/hda7
  
  
  If the program complains that the superblock - the master block that links to everything else - is corrupted, it is useful to remember that the superblock is so critical that it is duplicated every 8192 blocks through the filesystem and you can tell e2fsck to use one of the backups:

  e2fsck -b 8193 /dev/hda7
  

• One or more filesystems cannot be found and mounted: Check the contents of /etc/fstab - in making quick alterations here, typographical errors are common. You can use the e2label command to view the label of each filesystem: some distributions set these to the mount point so you can figure out what is what.

In each case, you will need to boot from some kind of rescue media, then work at the command line to repair the damage. If you boot from tomsrtbt or Knoppix, you will have editors and other utilities available. If you boot from the Red Hat installation CD in rescue mode, you will need to change the root directory so that the various system directories and filesystems are in the correct locations:

chroot /mnt/sysimage

See the box "The chroot Command" for details of why and how this works.

Forgot root password

If you have - really have - forgotten the root password for your system, it is still possible, in many cases, to log in and fix this. On some distributions, you can boot in single-user maintenance mode (runlevel 1) by appending a '1' or 'single' on the end of the normal kernel boot command line. With the LILO boot loader, for example, you can type

linux 1

to boot this way. With GRUB, it's a little more complex: you have to choose the boot menu item you want to use, then press 'e' to edit it, move to the kernel command line and press 'e' to edit it, append the '1' at the end of the line, press Enter to terminate editing and then press 'b' to boot it.

However, some distributions will still request the root password in runlevel 1. For those, you should append the option 'init=/bin/bash' to the kernel command line, e.g.

linux init=/bin/bash

Now, instead of running the init process to kick off all the startup scripts, the kernel will simply run a bash shell. Since the startup scripts have not run, you may have to mount other filesystems manually, and you will certainly have to remount the root filesystem read-write with the command:

mount -o remount,rw /

Now, you can set about removing the root password. To do this, simply edit the /etc/shadow file and remove the encrypted password field from the file - it's usually the second field of the first line. You can now reboot, log in as root and use the passwd command to reset the password.

Security Warning!

Now that everyone knows this tip, you should take care to set a LILO or GRUB password to stop an attacker from editing the boot command line and breaking into your system this way. Of course, an attacker could also remove the root password by booting from floppy or CD, so you should set the system to boot from hard drive first, and then password-protect the BIOS settings, too!

Can't Eject CD-ROM?

You can normally eject a CD using the eject command (and you can close the drive again later with eject -t). But what if you get a message:

eject: unable to eject, last error: Invalid argument

The problem here is that something is accessing the CD-ROM drive - but what? You can use the fuser command to find out:

fuser /dev/cdrom

will show processes that have an open file or are otherwise accessing the CD-ROM drive. The command

fuser -uik /dev/cdrom

will show you the process ID and user that "owns" the drive, and will interactively allow you to kill the process.

No sound

Sound configuration is fairly tricky unless you know exactly what type of sound hardware you have - the chipset, not the brand of card. The simplest solution is to use the distribution's own sound configuration command - for Red Hat, this is redhat-config-soundcard or sndconfig (for the older versions).

X resolution too low or too high

Try using the left Ctrl and Alt keys with the + and - keys on the numeric pad to cycle through the various resolutions available on your system. You can also manually edit the XF86Config file (look in /etc/X11/ or nearby for this, depending on your distribution), then find the relevant Modes line, and comment out inappropriate modes

For example, if my monitor couldn't cope with 1400 x 1050 resolution, I would remove that entry from the Modes line in my XF86Config file:

Section "Screen"

Identifier "Screen0"

Device "Videocard0"

Monitor "Monitor0"

DefaultDepth 24

SubSection "Display"

Depth 24

Modes "1400x1050" "1280x1024" "1280x960" "1024x768" "800x600" "640x480"

EndSubSection

EndSection

Sometimes, increasing the DefaultDepth entry will reduce the maximum resolution to something that your monitor can cope with.

Find the Right Driver Module

You can make the system attempt to load every device driver module of any given type in turn by using the command

modprobe -t type \*

where type is the name of a directory under /lib/modules/kernelver/kernel. For example:

modprobe -t net \*

will attempt to load most network drivers, one after another.

Trouble-shooting techniques

Use pairs of similarly-configured systems

Quick things to check:

Is a filesystem full? This can show up in lots of different ways: being unable to save files, print jobs not spooling correctly (especially on Samba print/file servers), and so on. Use the df command to see available space:

[root@freya home]# df -H

Filesystem Size Used Avail Use% Mounted on

/dev/Volume00/LogVol00 520MB 254MB 240MB 52% /

/dev/hda3 128MB 2 1MB 101MB 17% /boot

/dev/Volume00/LogVol03 2.2GB 134MB 1.9GB 7% /home

/dev/Volume00/LogVol05 520MB 8.5MB 485MB 2% /opt

none 264MB 0 264MB 0% /dev/shm

/dev/Volume00/LogVol02 1.1GB 36MB 969MB 4% /tmp

/dev/Volume00/LogVol01 4.3GB 3.0GB 1.1GB 75% /usr

/dev/Volume00/LogVol06 1.1GB 101MB 903MB 11% /usr/local

/dev/Volume00/LogVol04 3.2GB 2.3GB 756MB 75% /var

/dev/hda1 16GB 13GB 2.8GB 83% /mnt/winc

Remember that a filesystem can fill up either because almost all of its data blocks are used up (some are reserved for the root user, just to get out of trouble) or because all its i-nodes (there is one of these per file) are used up.

If you need to make space by deleting some large files, use the command 'ls -lS' to get a directory listing that is sorted by file size. To scan an entire filesystem (e.g. /home or /var) for the largest files, use the command:

du | sort -n

The largest files will be at the end of the listing.

Adding New Drives

Sometimes the growth of a filesystem - particularly /home - means that it is necessary to find it a new home; in other words, add another physical disk and relocate the filesystem to its new home where there is room to grow.

Here is the procedure for adding another drive, with a single partition which will become the new /home filesystem (I'm assuming fdisk has already been used to partition it):

As root:

# mkdir /mnt/newhome

# mkfs -t ext2 /dev/hdb1

# mount /dev/hdb1 /mnt/newhome

# (cd /home && tar cf - .) | (cd /mnt/newhome && tar xpf -)

then

# cd /

# mv /home /home.old

# mkdir /home

# umount /mnt/newhome

# mount /dev/hdb1 /home

Once the new /home directory tree has been checked out, you can then safely

# cd /home.old

# rm -rf *

# cd ..

# rmdir /home.old

# rmdir /mnt/newhome

to clean up.

Network Problems

Use the ifconfig command to check whether an interface has been configured and is up. For example:

Long delays while starting daemons at boot time

If the system seems to stop for 30 seconds or more while starting - particularly when starting network deamons like sendmail or NFS - then the problem is likely to be either DNS misconfiguration, a DNS outage, or no network connection at all. Check that /etc/resolv.conf contains the correct DNS addresses, check that/etc/hosts contains the correct IP address and names for this machine, and then check that the network interface is up.

Troubleshooting Techniques and Skills

The first rule is: Use the log files - they are the primary source of debugging information and clues. You can examine the main log file with the command:

tail /var/log/messages

and you can watch it continuously by running the command:

tail -f /var/log/messages

in a window while you work. For security and login-related problems, check the file /var/log/secure. There are other log files and directories that relate to different subsystems in /var/log, and you should never overlook them.

If trying to resolve boot-time problems, use the command:

dmesg | less

to review the kernel ring buffer.

The next rule is to compare similarly-configured systems, if you have them. Often, you can see obvious differences in the configuration files between a working system and the broken system.

Next: if you are stumped, talk the problem over with a colleague or friend. They don't have to know the perfect solution - often, their suggestions can trigger a new line of thinking or remind you of something you have overlooked.

If you don't have someone you can talk to, then use online resources. Get to know how to perform searches at http://www.google.com/linux , and how to search thecomp.os.linux and similar newsgroups at http://groups.google.com. On many occasions, I've turned up answers online after exhausting my own ideas.

Problem Avoidance Techniques

Keep a system change log. Whenever you make changes to the system, write them into the log. In general, if you never make changes to a system, it will just keep running - so that if the system breaks, the problem is usually related to recent changes.

Before making changes to critical system configuration files, make a backup copy which you can restore if everything goes pear-shaped. For example:

cp /etc/fstab /etc/fstab.good

vi /etc/fstab

There is no substitute for learning as much as possible about how the system works, and the role of the various configuration files in /etc, the daemon start/stop scripts in/etc/rc.d/init.d, how the init process works, and so on.

And, of course, the most importand System Administration Rule of all: Never make changes after three p.m. on a Friday!

The chroot Command

The chroot command is extremely useful for both system security and for system repair. Its basic syntax is:

chroot new-root-dir [command ...]

and its purpose is to run the specified command with the root directory changed to new-root-dir. If no command is specified, the default behaiour is to run an interactive shell (usually a bash shell). For example, the command:

chroot /var/ftp

will run a command shell in /var/ftp. However, note that the behaviour is to change the root directory first, and then try to invoke the command or shell, so that there had better be a file /var/ftp/bin/bash (which there would be, on many systems). In addition, the command will usually need to be statically linked, as otherwise it would attempt to load libraries from /lib, which is now /var/ftp/lib.

The chroot command is often used to start network daemons on servers - this is so that if an attacker manages to compromise the daemon, perhaps through a buffer overflow, he is unable to navigate around the entire system directory tree, but is instead constrained within a 'chroot jail'.

A major use of the chroot command is to change the root directory of the system after booting from a repair floppy or CD. For example, if you boot a Red Hat installation CD with the command 'linux rescue', the root file system is actually a RAM disk, and the root filesystem on your hard drive is mounted as /mnt/sysimage. Commands you give will load programs from /bin and /sbin on the RAM disk, which is obviously limited. To get access to those directories on the hard drive, you will need to change your root directory with the command

chroot /mnt/sysimage

Understanding Linux CPU Load - when should you be worried?

You might be familiar with Linux load averages already. Load averages are the three numbers shown with the uptime and top commands - they look like this:

load average: 0.09, 0.05, 0.01

Most people have an inkling of what the load averages mean: the three numbers represent averages over progressively longer periods of time (one, five, and fifteen minute averages), and that lower numbers are better. Higher numbers represent a problem or an overloaded machine. But, what's the the threshold? What constitutes "good" and "bad" load average values? When should you be concerned over a load average value, and when should you scramble to fix it ASAP?

First, a little background on what the load average values mean. We'll start out with the simplest case: a machine with one single-core processor.

The traffic analogy

A single-core CPU is like a single lane of traffic. Imagine you are a bridge operator ... sometimes your bridge is so busy there are cars lined up to cross. You want to let folks know how traffic is moving on your bridge. A decent metric would be how many cars are waiting at a particular time. If no cars are waiting, incoming drivers know they can drive across right away. If cars are backed up, drivers know they're in for delays.

So, Bridge Operator, what numbering system are you going to use? How about:

• 0.00 means there's no traffic on the bridge at all. In fact, between 0.00 and 1.00 means there's no backup, and an arriving car will just go right on.
• 1.00 means the bridge is exactly at capacity. All is still good, but if traffic gets a little heavier, things are going to slow down.
• over 1.00 means there's backup. How much? Well, 2.00 means that there are two lanes worth of cars total -- one lane's worth on the bridge, and one lane's worth waiting. 3.00 means there are three lane's worth total -- one lane's worth on the bridge, and two lanes' worth waiting. Etc.

= load of 1.00

= load of 0.50

= load of 1.70

This is basically what CPU load is. "Cars" are processes using a slice of CPU time ("crossing the bridge") or queued up to use the CPU. Unix refers to this as the run-queue length: the sum of the number of processes that are currently running plus the number that are waiting (queued) to run.

Like the bridge operator, you'd like your cars/processes to never be waiting. So, your CPU load should ideally stay below 1.00. Also like the bridge operator, you are still ok if you get some temporary spikes above 1.00 ... but when you're consistently above 1.00, you need to worry.

So you're saying the ideal load is 1.00?

Well, not exactly. The problem with a load of 1.00 is that you have no headroom. In practice, many sysadmins will draw a line at 0.70:

• The "Need to Look into it" Rule of Thumb: 0.70 If your load average is staying above > 0.70, it's time to investigate before things get worse.

• The "Fix this now" Rule of Thumb: 1.00. If your load average stays above 1.00, find the problem and fix it now. Otherwise, you're going to get woken up in the middle of the night, and it's not going to be fun.

• The "Arrgh, it's 3AM WTF?" Rule of Thumb: 5.0. If your load average is above 5.00, you could be in serious trouble, your box is either hanging or slowing way down, and this will (inexplicably) happen in the worst possible time like in the middle of the night or when you're presenting at a conference. Don't let it get there.

What about Multi-processors? My load says 3.00, but things are running fine!

Got a quad-processor system? It's still healthy with a load of 3.00.

On multi-processor system, the load is relative to the number of processor cores available. The "100% utilization" mark is 1.00 on a single-core system, 2.00, on a dual-core, 4.00 on a quad-core, etc.

If we go back to the bridge analogy, the "1.00" really means "one lane's worth of traffic". On a one-lane bridge, that means it's filled up. On a two-late bridge, a load of 1.00 means its at 50% capacity -- only one lane is full, so there's another whole lane that can be filled.

= load of 2.00 on two-lane road

Same with CPUs: a load of 1.00 is 100% CPU utilization on single-core box. On a dual-core box, a load of 2.00 is 100% CPU utilization.

Multicore vs. multiprocessor

While we're on the topic, let's talk about multicore vs. multiprocessor. For performance purposes, is a machine with a single dual-core processor basically equivalent to a machine with two processors with one core each? Yes. Roughly. There are lots of subtleties here concerning amount of cache, frequency of process hand-offs between processors, etc. Despite those finer points, for the purposes of sizing up the CPU load value, the total number of cores is what matters, regardless of how many physical processors those cores are spread across.

Which leads us to a two new Rules of Thumb:

• The "number of cores = max load" Rule of Thumb: on a multicore system, your load should not exceed the number of cores available.

• The "cores is cores" Rule of Thumb: How the cores are spread out over CPUs doesn't matter. Two quad-cores == four dual-cores == eight single-cores. It's all eight cores for these purposes.

Bringing It Home

Let's take a look at the load averages output from uptime:

~ $ uptime
23:05 up 14 days, 6:08, 7 users, load averages: 0.65 0.42 0.36

This is on a dual-core CPU, so we've got lots of headroom. I won't even think about it until load gets and stays above 1.7 or so.

Now, what about those three numbers? 0.65 is the average over the last minute, 0.42 is the average over the last five minutes, and 0.36 is the average over the last 15 minutes. Which brings us to the question:

Which average should I be observing? One, five, or 15 minute?

For the numbers we've talked about (1.00 = fix it now, etc), you should be looking at the five or 15-minute averages. Frankly, if your box spikes above 1.0 on the one-minute average, you're still fine. It's when the 15-minute average goes north of 1.0 and stays there that you need to snap to. (obviously, as we've learned, adjust these numbers to the number of processor cores your system has).

So # of cores is important to interpreting load averages ... how do I know how many cores my system has?

cat /proc/cpuinfo to get info on each processor in your system. Note: not available on OSX, Google for alternatives. To get just a count, run it through grepand word count: grep 'model name' /proc/cpuinfo | wc -l

Monitoring Linux CPU Load with Scout

Scout provides 2 ways to modify the CPU load. Our original server load plugin andJesse Newland's Load-Per-Processor plugin both report the CPU load and alert you when the load peaks and/or is trending in the wrong direction:




Quick Subversion setup

This a quick and dirty setup of the Subversion version control system on CentOS 5. This setup will use the built in Subversion daemon as opposed to the popular Apache mod_svn module where check in's and check out's are done through http. For more information on Subversion please visit their website linked above or the fantastic free book Version control with Subversion.

Install and run Subversion on CentOS 5

This install is done on CentOS Linux 5. These steps should work on almost any Linux distro. It assumes you can install packages with yum from a repository somewhere. It will also let the svn daemon run on it's default port of 3690. Make sure your firewalls are open if you need to get to it remotely. Also, repo is short for Subversion repository.

Login to the machine as root or sudo to root (sudo su -).

Install subversion with yum.

yum install subversion

Make a user and group called svn to run the server as.

adduser -M svn -s /sbin/nologin

Make a directory to store the svn repositories. I'm going to use /disk01/svn in this example.

mkdir -p /disk01/svn

Optional step: Sync a remote svn repository if you have one. If not just move on to the next step.

rsync -av --timeout=30 --delete --rsh="ssh -c arcfour -l root" remotehost:/disk01/svn/ /disk01/svn/

If you did not copy other repos from elsewhere or just want to make another/new one do the following. Put in your repo name where the word repo is.

svnadmin create /disk01/svn/repo

Give the svn user permission to everything in the dir before starting.

chown -R svn: /disk01/svn

Let's start the svn deamon as the svn user. All repos are assumed to be in dir /disk01/svn.

su -m svn -c 'svnserve -d -r /disk01/svn'

That should have started the svnserv daemon. Check your process list (ps -aux |grep svnsrve). If everything worked put that line in the /etc/rc.local file so it will start on start up.

Repository Configuration

The configuration for each repo is in the file svnserve.conf in each repo's conf dir. It sets the access type allowed to the repo. repo is the name of the repo you want to change the config for.

Edit the file /disk01/svn/repo/conf/svnserve.conf.

The following file is an example of a simple but effective setup. Everyone has anon read access. Only people that have authorized can write. Authorization is done through the repo passwd file using built in authorization (this is discussed next).

[general] anon-access=read auth-access=write password-db=passwd 

Save the file. You don't need to restart the daemon for it to take effect.

Adding Users and Authorization

To add a user to svn you will add them to the Subversion passwd file. This is located in the repo's conf dir. repo is the name of the repo you want to change the password for.

Edit the file /disk01/svn/repo/conf/passwd

The following file is the format of the passwd file. It is just plain text. The format very important. Even a wrong space can mess it up. Make sure there are no spaces between user=password. One user=password per line. Add as many as you want.

[users] user1=password1 user2=password2 

Save the file. You don't need to restart the daemon for it to take effect.

Subversion commands

Here are some simple commands to test your new repo or possibly ones you copied over from another machine.

Copy a subversion repo directory

svn co svn://hostname/reponame/trunk

List the files in a repo

svn list svn://hostname/reponame/trunk/scripts

Get info on file in repo

svn info svn://hostname/reponame/trunk/scripts/filename

Repo update emails

If you want an email update when a repo gets updated you can put this script in the repo's hooks dir. This script will run on each commit. Put the following shell script code into the file /disk01/svn/reponame/hooks/post-commit. Where reponame is the repo you want the emails for. You may have to rename the file in the hooks dir to post-commit as there is usually a default one or you can just make a new one.

REPOS="$1" REV="$2"  REPOS_BASE=${REPOS##*/} EMAIL_LIST="email1@hostname.org email2@hostname.org email3@hostname.org"  svn log --verbose -r $REV svn://hostname/${REPOS_BASE} | mail -s "Commit notice for repository ${REPOS_BASE}" ${EMAIL_LIST} 

MySQL Handy 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