Basic MySQL Administration

Determine Whether Your MySQL Server Is Running

  1. Log into your web development server using a terminal emulator. If you're not sure how to do this, click here for instructions.

  2. Switch to your into your mysql directory by typing the following command:

    cd mysql

  3. Type the following at the command line:

    ~/mysql/bin/mysqladmin -u root -p status

    • Enter your root MySQL password in order to continue.
  4. If the results look something like the following output, MySQL is running properly:

    Uptime: 4 Threads: 1 Questions: 62 Slow queries: 0 Opens: 51 Flush tables: 1 Open tables: 45 Queries per second avg: 15.500

    If the results look like the following output, MySQL is running properly, but you entered the incorrect password:

    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

    If the results look like the following output, MySQL is not running properly:

    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/path/to/sock/mysql.sock' (111)

Stopping and Restarting Your MySQL Server

Stopping MySQL

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

  • Starting MySQL

  • To start up your MySQL server again, connect to your MySQL host and then type

    cd ~/mysql
    ./bin/mysqld_safe &

Resetting your MySQL Password

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

  2. Start MySQL in "wide open" mode by typing the following command:

    cd ~/mysql
    bin/mysqld_safe --skip-grant-tables &

    • Anyone anywhere can log on to your MySQL server while it is in this mode, so don't leave it this way.
  3. Log in to MySQL by typing the following command:

    bin/mysql

  4. Set a new password by typing the following command:

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

  5. Restart MySQL by typing the following commands:

    bin/mysqladmin shutdown

    bin/mysqld_safe &

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. In some cases, manual recovery may still be required. Signing up for server restart notifications is highly recommended.

  1. Log into your web development server using a terminal emulator. If you're not sure how to do this, click here for instructions.

  2. If you are familiar with the text editor "vim" or "vi," go ahead to the next step. If not, type the following command.

    export EDITOR=/usr/local/bin/pico

  3. To add a cron job, type the following command:

    crontab -e

  4. Insert this line of text:

    @reboot /ux01/tools/mysql.starter

  5. Save and close the file using Pico. (Click for instructions on how to use pico).

  6. You can check the contents of your crontab by typing the following command:

    crontab -l

    If you ever want to delete all of your cron jobs, type the following command

    crontab -r

Backing up your MySQL databases

These instructions will guide you through creating a simple and automated way to back up your database, where they will be copied to a backup file on disk that will be retrieved by UW-IT's nightly filesystem backups. This can be done without stopping the database, though tables will be locked while it completes. While the nightly filesystem backups create backup copies of your data files as well, it is not recommended to rely on these for consistent backups as the database files may be in an inconsistent state. This method avoids this pitfall by making an authoritative copy of your data.

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 over quota. Pay attention to how large your database is and how close your account is to its quota before using this method. | Manage Your Disk Space

    1. Log into your web development server using a terminal emulator. If you're not sure how to do this, click here for instructions.

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, which will have the login name of "backup":

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.

  1. Create a directory to store the MySQL database backup files by typing these commands:

    cd ~
    mkdir mysql_backup

  2. Download a copy of the mysql_backup.sh script to run the backup by typing this command:

    wget http://www.washington.edu/computing/media/mysql_backup.sh

  3. Change the permissions of the script so that only you can read it by typing this command:

    chmod 700 mysql_backup.sh

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

  5. Open your crontab by typing this command:

    export EDITOR=pico;crontab -e

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

    0 2 * * * ~/mysql_backup.sh

  7. Save and close the file using Pico. (Click for instructions on how to use pico).

  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.

Last modified: September 16, 2013