MySQL Administration

Connecting

MySQL has a fairly straightforward argument-passing syntax, but it's has a few anomalies, as documented below.

-h hostname
-u username
-p password
-P portnumber

By default, the first non-starting-with-hyphen argument will be taken as the name of the database to use.

Catches

  • password - If you want to specify the password, you have to leave no space between the -p and the password. Why this is the case is anybody's guess; it's inconsistent with every other command line tool in existence. You need to use it as -pPutThePasswordHere. The alternative is long GNU-style options, --password=ThePassword

  • hostname - By default, localhost is assumed. When the hostname is localhost, explicit or implied, the client will assume you want to use a file-socket connection, ignoring any other options like the port number. This is apparently by design, see http://bugs.mysql.com/bug.php?id=16855 for more details. If you really, really want a TCP connections, you have to either specify --protocol=TCP or use something like -h 127.0.0.1. Anything that isn't localhost.

Setting the initial root password

In short, you can use

mysqladmin -u password NEWPASSWORD

Or if that fails you need to get into the DB as root. Perhaps

mysql -u root -p

And give a blank password. Run the following commands to update the password manually.

USE mysql;
UPDATE user SET password=PASSWORD("NEWPASSWORD") where User='root';
FLUSH PRIVILEGES;
QUIT

Create /root/.my.cnf

[client]
user=root
password='NEWPASSWORD'

References: http://www.cyberciti.biz/faq/mysql-change-root-password/

Adding databases

To create a MySQL databases on a server run the following commands as root mysql

The system should be set up so that root is the MySQL administrator, and you can log in without password. If not check if there has been a root MySQL password set or create one using the procedure above.

CREATE DATABASE database;
GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION ;
FLUSH PRIVILEGES;

Then write that to a .my.cnf in the users home directory if it doesn't already exist:

[client]
user=USER
password=PASSWORD

[mysql]
database=DATABASE

Also check the .my.cnf when creating additional databases so that you can use their existing password for the new database.

External Access to a MySQL database.

The recommended way of connecting to a MySQL database remotely is through a SSH tunnel.

See this page on connecting to ssh through an SSH tunnel for more information

If using an SSH tunnel is not an option, for example, the MySQL server is running on a Windows host then a command similar to the following can be used:

GRANT ALL PRIVILEGES ON database.* TO user@source_server IDENTIFIED BY 'existing_user_password' WITH GRANT OPTION;
FLUSH PRIVILEGES ;

Important: In order for this to work the following will also need to be completed:

  1. The firewall on the server will need to allow remote connections to MySQL
  2. MySQL must not only be listening on 127.0.0.1 (or local host). The easy way to fix this is to set it to listen on all IP addresses (use: 0.0.0.0)

Checking privileges on a system

Sometimes you want to see who can access what. To check global-level privileges you can try:

SELECT User,Host from mysql.user;

Read up here for more details. http://dev.mysql.com/doc/refman/5.0/en/grant.html

Resetting MySQL Passwords

This process defines the steps required to reset a password on a MySQL database. If this procedure is not followed exactly it will cause SERIOUS problems. Double check the command BEFORE pressing enter, and/or get someone to check the SQL commands before they are run.

  1. Generate a new MySQL suitable password using

    pwgen -n 10
    Note: pwgen is available in Ubuntu package repositories.
  2. Go to the server running the database.
  3. As root, run MySQL.

    mysql
  4. At the MySQL prompt type

    SET PASSWORD FOR 'USER_GOES_HERE'@'localhost' = PASSWORD('PASSWORD_GOES_HERE');

An alternative method is

  1. Connect to MySQL

    mysql

    update user set password=PASSWORD('INSERT_NEW_PASSWORD_HERE') where user = 'USERNAME';
    flush privileges;
    quit out of MySQL.
  1. Change to user in question.

    su - user
  2. Update the .my.cnf password field.

    vi .my.cnf
    save and quit.
  3. Run MySQL to test

    mysql


Corrupted databases

Occasionally tables and databases can become corrupted. Some signs which can indicated a corrupted database:

  • Hanging when attempting to load a database/table
  • phpmyadmin failing to log in or returning random errors
  • inability to find tables which are known to exist
  • Unexpected EOF

Checking and repairing tables

If you are able to connect to the Mysql server and use the database then the following command will allow you to check a suspect table:

USE DATABASENAME;
check table TABLENAME;

if this returns an error it can be fixed using:

repair table TABLENAME;

Once repaired it is handy to check that the table is returning an OK using the first command again

If you are unable to correct the problem using the repair problem and the Mysql database is using MyISAM (this is the default in MySQL) then you will be able to use a Myisamchk. This test is ran over the data files and MUST be conducted when the database is stopped and can be invoked using the following command as root:

Checking the database:

myisamcheck tablename.MYI 

the -r flag can be used with myisamcheck to repair the table.

Restoring Databases

Restoring From The Legacy Backup Method

Backups are made on a daily basis at Midnight and are stored in /var/lib/mysqlbackup

These files are named like: YYYY-MM-DD.gz

The following commands can be used in order to extract a specific database from the file:

zgrep -n '^CREATE DATABASE' FILE| grep -A1 DATABASE

This will give you the line numbers where all the databases begin. Once you have these line numbers the following command can be used to create a MySQL dump.

zcat FILE | sed -n 'LINE,LINEp' > somewhere

Remember:

  • Typically, the output should be visual inspected to ensure it looks completed.
  • Once this has been done, the database should be dropped using:
    •    drop database DATABASENAME;
  • The data can be re-imported using a command similar to:
    •    mysql DATABASE NAME < FILENAME;

Restoring Databases From a mylvmbackup Backup

Backups are made on a daily basis at Midnight and are stored in /var/lib/mysqlbackup

These files are named like backup-YYYYMMDD_hhmmss_mysql.tar.gz

  1. Get the MySQL datadir.

    MYSQLDATADIR=`mysqladmin variables | grep datadir | awk '{ print $4 }'`
  2. Shutdown MySQL.

    service mysqld stop
  3. NOTE: This will destroy existing data Extract the backup into the MySQL directory.

    tar -C $MYSQLDATADIR -xzvf /var/lib/mysqlbackup/backup-20080404_175310_mysql.tar.gz
  4. If you need to restart replication check out the /var/lib/mysql/backup-pos/backup-20080404_175310_mysql.pos file for binary log and position details.

    cat /var/lib/mysql/backup-pos/backup-20080404_175310_mysql.pos
    You should also follow the MySQL replication wiki docs.
  5. Start up MySQL.

    service mysqld start
  6. Check the MySQL log file & check for errors.

    tail -f /var/log/mysqld.log
  7. Remove the /var/lib/mysql/backup-pos directory.

  8. http://www.mysqlperformanceblog.com/2006/06/05/innodb-thread-concurrency/

InnoDB vs MyISAM

Any connection issuing an UPDATE or DELETE against a MyISAM table will request an exclusive write lock on the MyISAM table. If no other locks (read or write) are currently placed on the table, the exclusive write lock is granted and all other connections issuing requests of any kind (DDL, SELECT, UPDATE, INSERT, DELETE) must wait until the thread with the exclusive write lock updates the record(s) it needs to and then releases the write lock.

Since there is only table-level locks, there is no ability (like there is with InnoDB) to only lock one or a small set of records, allowing other threads to SELECT from other parts of the table data.

MySQL storage engines are discussed at greater length in a sister article.

Timezone

Once the system timezone data has been updated, MySQL requires a restart to read the new timezone data. This will affect all time/date calculations if the system time changes due to timezone updates and MySQL has not been restarted.