Skip Navigation

UW IT Connect

UW Directories | Calendar | Tools

IT Connect

Connecting You to Information Technology at the UW

Basic MySQL Administration

IT Connect > Web > Publishing > Using MySQL > Basic MySQL Administration

• 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
• Backing up your MySQL databases
• Logging Client Interaction With Your MySQL Server
• References

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 setting up MySQL on the UW servers.

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:

1. StepsActions

1. Log in to your Homer or Dante account with Tera Term or another terminal emulator. Mac and Linux users can open a terminal and run ssh hostname to log in, where hostname is the name of the host, such as homer.u.washington.edu.

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

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

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.

1. StepsActions

1. Use ps and kill to stop your MySQL server without having the password. Running ps uxw 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 uxw
   USER    PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
   agraf  8977  0.0  0.0  10952  1620 ?        S    14:16   0:00 sshd: agraf@pts/51
   agraf  8978  0.0  0.0   5348  1748 pts/51   Ss+  14:16   0:00 -psh
   agraf 10539  0.0  0.0   5068  1256 pts/51   R+   14:32   0:00 ps uxw
   agraf 18438  0.0  0.0   4488  1188 ?        S    Nov09   0:00 /bin/sh bin/mysqld_safe
   agraf 18463  0.0  0.0  15852  3848 ?        S    Nov09   0:00 /da23/d54/agraf/mysql/bin/mysqld --basedir=/da23/d54/agraf/mysql
   agraf 18464  0.0  0.0  15852  3848 ?        S    Nov09   0:00 /da23/d54/agraf/mysql/bin/mysqld --basedir=/da23/d54/agraf/mysql
   agraf 18465  0.0  0.0  15852  3848 ?        S    Nov09   0:00 /da23/d54/agraf/mysql/bin/mysqld --basedir=/da23/d54/agraf/mysql
   $ kill 18438 18463 18464 18465
   $ ps uxw
   USER    PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
   agraf  8977  0.0  0.0  10952  1620 ?        S    14:16   0:00 sshd: agraf@pts/51
   agraf  8978  0.0  0.0   5348  1748 pts/51   Ss+  14:16   0:00 -psh
   agraf 10539  0.0  0.0   5068  1256 pts/51   R+   14:32   0:00 ps uxw
   

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

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

3. Log in to MySQL.

   bin/mysql

4. Set a new password

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

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

1. StepsActions

1. Make sure that you are connected to your Web development host (ovid or vergil).

2. If you are familiar with the text editor "vim" or "vi," go ahead to the next step. If not, then you may want to use the "pico" text editor instead. The following command will let you edit the cron job in pico.

   export EDITOR=/usr/local/bin/pico

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

4. You will then need to insert this line of text:

   0,15,30,45 * * * * /ux01/tools/mysql.starter

5. Then save the file and exit.

6. You can check the contents of your crontab file with the command

   crontab -l

   If you ever want 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

Backing up your MySQL databases

Do not rely on filesystem backups for database backups, as inconsistencies and corruption can occur. Also, working with your own files is faster and easier than asking UW Technology for backup copies that may or may not work for recovering your data. Instead, dump databases to a backup file on disk where it will be retrieved by filesystem backups. This can be done without stopping the database, though tables will be locked while it completes.

Warning:Using the backup method outlined below will create two additional copies of your database. If you have a large database, this could put your account overquota. Pay attention to how large your database is and how close your account is to its quota before using this method.

1. StepsActions

1. Create a user that has only SELECT and LOCK TABLES privileges on all databases. Do not use the mysql root user, or any other privileged user, since you must store this user's password on disk.

   Connect to MySQL with the command-line client and use a command like this to create the user:

   mysql> grant SELECT, LOCK TABLES on *.* to backup@"localhost" identified by 'backup_password';
   mysql> flush privileges;

   Make sure to replace backup_password with a real password.

2. Create a directory to store the MySQL database backup files.

   cd ~
   mkdir mysql_backup

3. Download a copy of the mysql_backup.sh script to run the backup.

   wget http://www.washington.edu/itconnect/web/publishing/mysql_backup.sh

4. Change the permissions of the script so that only you can read it. Remember, that file has a password to your MySQL database in it.

   chmod 700 mysql_backup.sh

5. Edit the mysql_backup.sh file to include the password for your MySQL backup account.

   ##### required configuration parameters #################
   
   MYSQL_BACKUP_PASSWORD='backup_password'
   
   ###### end configuration -- see below for requirements ##

   Remember to substitute the real password for backup_password.

6. Add a cron job to run the script every morning at 2:00 AM.

   Open your crontab:

   crontab -e

   Paste the following text onto a new line in the crontab.

   0 2 * * * ~/mysql_backup.sh

   Save and exit.

7. More options and information are available in the documentation for mysqldump.

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.

---

About IT Connect | News Feed | News Archive | Contact IT Connect

IT Connect is provided by
UW Technology