Basic MySQL Administration

Last updated: March 15, 2024
Audience: All UW

Important! – For users of the ovid systems: those servers are in a cluster, so please be sure to log into the correct ovid server before using the following instructions.

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. Type the following at the command line:
    ~/mysql/bin/mysqladmin -u root -p status

    • Enter your root MySQL password in order to continue.
  3. 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, use the following command:
    ~/mysql/bin/mysqladmin -u root -p shutdownThis 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
    ~/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.  PID’s are in bold text.
    $ 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 
    
    $ kill 18438 18463
    
    $ 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:
    ~/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:
    ~/mysql/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. Shutdown “wide open” mode and then restart MySQL normally by typing the following commands:
    ~/mysql/bin/mysqladmin shutdown
    ~/mysql/bin/mysqld_safe &

Viewing and Changing MySQL user accounts

  1. Type the following command to connect to the server:
    ~/mysql/bin/mysql -u root -p
  2. At the mysql> prompt, check to see that all users are properly set up by running this command:
    select user,host from mysql.user;
  3. You should see something similar to the following:
    +——+——————-+
    | user | host             |
    +——+——————-+
    | root | %.uw.edu    |
    | root | %.washington.edu  |
    | root | localhost     |
    +——+——————-+
    3 rows in set (0.00 sec)
  4. If changes to the admin account are needed then carefully, one at a time, type the following six commands. After typing the semicolon character (;)  in each command, press the “Enter” key to execute that command line.
  5. use mysql;
    delete from user where Host like "%";
    grant all privileges on *.* to root@"%.uw.edu" identified by "mypassword" with grant option;
    grant all privileges on *.* to root@"%.washington.edu" identified by "mypassword" with grant option;
    grant all privileges on *.* to root@localhost identified by "mypassword" with grant option;
    flush privileges;

    • The quote characters shown above are all required.
    • Replace mypassword with the password you selected earlier in this procedure.
    • Explanation: This step allows other systems, including the web servers, to connect to your MySQL databases.

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.

Important! – For users of the ovid systems: those servers are in a cluster, so please be sure to log into the correct ovid server before using the following instructions.

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

  1. 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
  2. To add a cron job, type the following command:
    crontab -e
  3. Insert this line of text:
    @reboot /usr/local/bin/mysql.starter
  4. Save and close the file using Pico. (Click for instructions on how to use pico).
  5. You can check the contents of your crontab by typing the following command:
    crontab -l
  6. 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.
  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.
  3. 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.

  4. Create a directory to store the MySQL database backup files by typing these commands:
    mkdir ~/mysql_backup
  5. Download a copy of the mysql_backup.sh script to run the backup by typing this command:
    wget https://itconnect.uw.edu/mysql_backup.sh
  6. Change the permissions of the script so that only you can read it by typing this command:
    chmod 700 ~/mysql_backup.sh
  7. Edit the mysql_backup.sh file to include the password for your MySQL backup account and the port that your MySQL server runs on.
    ##### required configuration parameters #################
    MYSQL_BACKUP_PASSWORD='BACKUP_PASSWORD'
    MYSQL_PORT='PORT'
    ###### end configuration -- see below for requirements ##

    Remember to substitute the real password for backup_password.

  8. Open your crontab by typing this command:
    export EDITOR=pico;crontab -e
  9. Paste the following text onto a new line in the crontab.
    0 2 * * * ~/mysql_backup.sh
  10. Save and close the file using Pico. (Click for instructions on how to use pico).
  11. More options and information are available in the documentation for mysqldump.
  12. To restore your database from backup, using the “import” tab in phpMyAdmin is the easiest and most straight-forward option.