Basic MySQL Administration

Summary

This article covers the basics of MySQL administration. You will learn how to stop and restart MySQL safely and how to configure and administer your database server.

Note: This article assumes that you have followed the instructions for installing and starting MySQL on the UW servers.

Included on this page:

Determining Whether Your MySQL Server Is Running
Stopping and Restarting Your MySQL Server
Resetting the MySQL Password
Using cron to start Your MySQL Server Automatically After a System Restart
Logging Client Interaction With Your MySQL Server
References

Determining Whether Your MySQL Server Is Running

In order to use or administer MySQL, your MySQL server needs to be running. To verify that this is the case, follow these instructions:

Log in to your Homer or Dante account with Tera Term or another terminal emulator.
Press the O key for Other, then press the W key to drop into the Web development environment and cd into your mysql directory:
cd mysql

Note: Stay in your mysql directory; at no point in this installation should you cd to public_html or student_html. Doing so will cause errors if you attempt to stop or restart MySQL.

Maximize the size of your terminal window, and type the following at the command line:

ps uxww

If MySQL is running, you will see output similar to the following:

USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
agraf    23917  0.0  0.0  2136  856 ?        S    Jun18   0:00 /bin/sh ./bin/mysqld_safe
agraf    24131  0.0  0.0 19376 3928 ?        S    Jun18   0:00 /rc22/d77/agraf/mysql/bin/mysqld --basedir=/rc22/d77/agraf/mysql
--datadir=/rc22/d77/agraf/mysql-data --pid-file=/rc22/d77/agraf/mysql-data/ovid21.u.washington.edu.pid --skip-external-locking
 --port=90212 --socket=/rc22/d77/agraf/mysql.sock
agraf    24169  0.0  0.0 19376 3928 ?        S    Jun18   0:08 /rc22/d77/agraf/mysql/bin/mysqld --basedir=/rc22/d77/agraf/mysql
--datadir=/rc22/d77/agraf/mysql-data --pid-file=/rc22/d77/agraf/mysql-data/ovid21.u.washington.edu.pid --skip-external-locking
 --port=90212 --socket=/rc22/d77/agraf/mysql.sock
agraf    24170  0.0  0.0 19376 3928 ?        S    Jun18   0:00 /rc22/d77/agraf/mysql/bin/mysqld --basedir=/rc22/d77/agraf/mysql
--datadir=/rc22/d77/agraf/mysql-data --pid-file=/rc22/d77/agraf/mysql-data/ovid21.u.washington.edu.pid --skip-external-locking
 --port=90212 --socket=/rc22/d77/agraf/mysql.sock
agraf    31814  0.0  0.0  4216 1764 pts/168  R    15:14   0:00 ps uxww

Specifically, you will see that a process called "./bin/mysqld_safe" is running. This is the command you typed to start your MySQL server. If MySQL is not running, you will only see a reponse similar to this:

USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
agraf     3531  0.0  0.0  4224 1752 pts/191  R    15:21   0:00 ps uxww

Of course in either case your UW NetID will be in place of "agraf".

Stopping and Restarting Your MySQL Server

To stop your MySQL server, make sure you are in your mysql directory, and then type

./bin/mysqladmin -u root -p shutdown

This lets the mysqladmin utility know that you want to shut down MySQL running as user root (-u root), and that you will enter a password (-p).
To start up your MySQL server again, make sure you are in your mysql directory, and then type

./bin/safe_mysqld &

See Step 10 of Installing and Starting MySQL for a more detailed explanation about starting MySQL.

Resetting the MySQL Password

Sometimes people lose the password for their MySQL server. When this happens, one can regain access to the MySQL server by the following method: shut down MySQL without having the password, start MySQL in a "wide open" mode, set a new password, and then restart it in the normal passworded mode.

These instructions were created from information in the MySQL Reference Manual page, How to Reset the Root Password. They assume that the MySQL account being reset is named "root," but the method should work for any account name.

Use ps and kill to stop your MySQL server without having the password. Running ps uxww will list the processes running on your account, including their PID or Process ID. The kill command takes PIDs as arguments, and attempts to stop the processes those PIDs refer to. Below is an example of using ps and kill to stop your MySQL processes.

$ ps uxww
USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
agraf    23917  0.0  0.0  2136  856 ?        S    Jun18   0:00 /bin/sh ./bin/mysqld_safe
agraf    24131  0.0  0.0 19420 3944 ?        S    Jun18   0:00 /rc22/d77/agraf/mysql/bin/mysqld --basedir=/rc22/d77/agraf/mysql
--datadir=/rc22/d77/agraf/mysql-data --pid-file=/rc22/d77/agraf/mysql-data/ovid21.u.washington.edu.pid --skip-external-locking
--port=90212 --socket=/rc22/d77/agraf/mysql.sock
agraf    24169  0.0  0.0 19420 3944 ?        S    Jun18   0:16 /rc22/d77/agraf/mysql/bin/mysqld --basedir=/rc22/d77/agraf/mysql
--datadir=/rc22/d77/agraf/mysql-data --pid-file=/rc22/d77/agraf/mysql-data/ovid21.u.washington.edu.pid --skip-external-locking
--port=90212 --socket=/rc22/d77/agraf/mysql.sock
agraf    24170  0.0  0.0 19420 3944 ?        S    Jun18   0:00 /rc22/d77/agraf/mysql/bin/mysqld --basedir=/rc22/d77/agraf/mysql
--datadir=/rc22/d77/agraf/mysql-data --pid-file=/rc22/d77/agraf/mysql-data/ovid21.u.washington.edu.pid --skip-external-locking
--port=90212 --socket=/rc22/d77/agraf/mysql.sock
agraf    22477  0.0  0.0  5144 1588 pts/125  S    Jul17   0:00 -tcsh
agraf     9136  0.0  0.0  4236 1796 pts/125  R    09:49   0:00 ps uxww
$ kill 23917 24131 24169 24170
$ ps uxww
USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
agraf    22477  0.0  0.0  5144 1588 pts/125  S    Jul17   0:00 -tcsh
agraf     9136  0.0  0.0  4236 1796 pts/125  R    09:49   0:00 ps uxww

You can see in the output from the second run of ps uxww that the MySQL processes are no longer running.

Start MySQL in "wide open" mode.

Note: Anyone anywhere can log on to your MySQL server while it is in this mode, so don't leave it this way, please.

cd ~/mysql
bin/mysqld_safe --skip-grant-tables &
Log in to MySQL.

bin/mysql
Set a new password

mysql> UPDATE mysql.user SET Password=PASSWORD('put your password here') WHERE User='root';
mysql> EXIT
Restart MySQL.

bin/mysqladmin shutdown

bin/mysqld_safe &

Using cron to Start Your MySQL Server Automatically After a System Restart

Normally, you would have to start your MySQL server up again after the system it is running on is restarted. Cron is a program that can start it for you, so you don't have to log in and start it again before using your database after a system restart.

To add a cron job, type the command

crontab -e

This will open your crontab file in your default editor(vi, unless you've changed it). You will then need to insert this line of text:

0,5,10,15,20,25,30,35,40,45,50,55 * * * * /ux01/tools/mysql.starter

Then save the file and exit. You can check the contents of your crontab file with the command

crontab -l

To delete all of your cron jobs, type the command

crontab -r

Note: The default text editor for editing cron jobs is 'vi', but this can be changed by editing the EDITOR environment variable.
How to Use the vi Editor

Logging Client Interaction With Your MySQL Server

Your MySQL server can log all information about all connections and queries made to it. To start up your MySQL server with logging enabled, first make sure your server is stopped, and then start it in the following way:

./bin/safe_mysqld --log="logfilepath" &

Replace logfilepath with the entire path to a log file whose name you make up yourself. Since all other MySQL logs are kept in the data directory, it might be a good idea to use something like "/rc34/d82/accountname/mysql/data/connections.log", with /rc34/d82/accountname replaced with the actual path to your home directory.

To view the most recent activity on your MySQL server, simply type the following command:

tail -20 ~/mysql/data/connections.log

If necessary, replace connections.log with the name of your log file. You will see something like this:

/rc34/d82/accountname/mysql/bin/mysqld, Version: 4.0.15-log, started with:
		Tcp port: XXXX  Unix socket: /rc34/d82/accountname/mysql.sock
		Time                 Id Command    Argument
		020807 16:34:47       1 Connect    root@localhost on
		020807 16:34:52       1 Query      SHOW DATABASES
		020807 16:34:57       1 Init DB    John_Doe
		020807 16:35:02       1 Query      SHOW TABLES
		020807 16:35:13       1 Query      SHOW COLUMNS FROM friends
		020807 16:35:18       1 Quit      
		020807 16:36:23       2 Connect    root@veron01.u.washington.edu on 
		2 Init DB    mysql
		2 Quit      
		020807 16:47:56       3 Connect    root@localhost on 
		3 Shutdown

Your log file will tell you the port that your MySQL server is running on, the path to your socket file, the time and location of each connection to your MySQL server, and the SQL commands that were run during each connection.

If you do choose to keep MySQL logs, be sure to monitor them. Log files can get large very quickly and need to be flushed on a regular basis.

References

See the main MySQL Documentation for more help. The following links may also be useful for configuring, administering, and troubleshooting your MySQL server:
MySQL Server-Side Scripts
MySQL Client-Side Scripts Page
MySQL Log Files

Tip: phpMyAdmin is a web-based PHP utility that provides a simple graphical interface for many MySQL administrative tasks. See Installing phpMyAdmin for information on how to set up and use phpMyAdmin.