Skip Navigation
W
University of Washington
IT Connect
Connecting You to Information Technology at the UW

 

Basic MySQL Administration

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

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

  2. Log into your web development host using a terminal emulator. Not sure how to do this? Click here for directions.

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

  4. 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
netid    24918  0.0  0.3 33684 12612 pts/1   SN   10:47   0:00 /usr/local/mysql/bin/mysqld --basedir=/da21/d50/netid/mysql --datadir=/da21/d50/netid/mysql/data --log-error=/da21/d50/netid/mysql/data/ovid21.u.washington.edu.err --pid-file=/da21/d50/netid/mysql/data/ovid21.u.washington.edu.pid --socket=/da21/d50/netid/mysql.sock --port=52801
netid    24920  0.0  0.3 33684 12612 pts/1   SN   10:47   0:00 /usr/local/mysql/bin/mysqld --basedir=/da21/d50/netid/mysql --datadir=/da21/d50/netid/mysql/data --log-error=/da21/d50/netid/mysql/data/ovid21.u.washington.edu.err --pid-file=/da21/d50/netid/mysql/data/ovid21.u.washington.edu.pid --socket=/da21/d50/netid/mysql.sock --port=52801
netid     6072  0.0  0.0  4600 2164 pts/1    R    11:36   0:00 ps uxww

    There are a few useful bits of information here. Specifically, you will see that a process called "/usr/local/mysql/bin/mysqld" is running. These are components of your MySQL server. Part of the command should include "--port=52801." This is the port that your server is running on. Additionally, there is the PID (process ID) listed in case you need to manually kill your processes. 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
netid     6072  0.0  0.0  4600 2164 pts/1    R    11:36   0:00 ps uxww

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

Stopping and Restarting Your MySQL Server

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

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

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

  4. Log in to MySQL.

    bin/mysql

  5. Set a new password

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

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

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

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

  4. To add a cron job, type the command

    crontab -e

  5. This will open your crontab file in your default editor(vi, unless you've changed it).

  6. You will then need to insert this line of text:
    @reboot /ux01/tools/mysql.starter

  7. Then save the file (:wq in Vi. ctrl+x in pico) and exit.

  8. That's it! 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 Information 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

  2. 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;
    mysql> quit;

    Make sure to replace backup_password with a real password.

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

    cd ~
    mkdir mysql_backup

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

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

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

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

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

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

  9. To restore your database from backup, using the "import" tab in phpMyAdmin is the easiest and most straight-forward option.

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

Adding MySQL Binaries to your PATH

If you think you're going to be referencing the MySQL binaries often, it may be worthwhile to add your MySQL bin directory to your Linux $PATH variable. This adds your MySQL paths to the directories in which your command line searches to execute a program. These steps will allow you to run mysql from any directory without having to type in ~/mysql/bin/mysql. This functions much in the same way that you can type in scp without having to enter in /usr/local/bin/scp.

  1. StepsActions

  2. First you'll want to create a directory to put your links in.

    mkdir ~/bin

  3. Then you'll want to add links to all of your MySQL binaries to your bin directory.

    ln -s $HOME/mysql/bin/* ~/bin

  4. Finally you'll want to add a line to your .profile file that tells your command line where to look for binaries.

    echo PATH=$PATH:~/bin > ~/.profile

  5. That's it! Close your terminal window and re-connect to begin enjoying these changes.

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 ITC
UW Information Technology