MySQL howtos

• Automated MySQL database backup

Upgrading to MySQL 4.1

Your RedHat server will come with a MySQL 3.23 database.  You can usually upgrade to the more recent 4.1 series without a problem.  Why use 4.1 over 3.23?  Sub-query support; better fulltext index code; better replication support; multi-table deletes; and better InnoDB support.

Here is the quick cut and paste HOWTO (last tested 2004-11-01 on a WhiteBox Linux 3 distro):

#what version do you want to install?
rootpasswd=yourrootmysqlpasswordhere
VERSION=4.1
# use the following if you want the _beta_ version
#VERSION=5.0
# sometimes the RPMs are called $VERSION-0 and sometimes $VERSION-1
# so you may need to change this (it is correct for the current VERSION)
MINOR=0

#grap the RPMs
function installmysql() {
if [ -z "$VERSION" ]; then
    echo "No VERSION variable set, defaulting to 4.1"
    VERSION=4.1
fi
# figure out the latest specific version (e.g. 4.1.13 or 5.0.11)
if [ "$VERSION" = "4.1" ]; then
    VERSION=$(wget -O - http://dev.mysql.com/downloads/mysql/4.1.html 2>&1| grep "Pick a mirr" | grep mysql-stand | head -n 1 | sed 's/.*mysql-standard-//g' | sed 's/-.*//g')
    echo "Installing $VERSION"
elif [ "$VERSION" = "5.0" ]; then
    VERSION=$(wget -O - http://dev.mysql.com/downloads/mysql/5.0.html 2>&1| grep "Pick a mirr" | grep mysql-stand | head -n 1 | sed 's/.*mysql-standard-//g' | sed 's/-.*//g')
    echo "Installing $VERSION"
fi

DIR=4.1
if echo $VERSION | grep -q 5.0 ; then
    DIR=5.0
fi
for RPM in "MySQL-client" "MySQL-devel" "MySQL-shared-compat" "MySQL-server" ; do
FILE=$RPM-$VERSION-$MINOR.i386.rpm
if [ -e $FILE ] ; then
     echo $FILE already downloaded, skipping it
fi
URL="http://mysql.mirrors.pair.com/Downloads/MySQL-$DIR/$FILE"
echo Fetching $URL
wget --quiet $URL
if [ $? -ne 0 ]; then
    echo "$URL could not be downloaded." >&2
    return 1
fi
done

# stop a running instnace
if [ -e /etc/init.d/mysql ]; then
/etc/init.d/mysql stop
elif [ -e /etc/init.d/mysqld ]; then
/etc/init.d/mysqld stop
fi

#install the rpms
rpm -Uvfh "MySQL-*$VERSION*.rpm"
if [ $? -ne 0 ]; then
    echo "Mysql rpms did not install" >&2
    return 1
fi

# make sure MySQL will start on startup
chkconfig --level 35 mysql on

# make mysql use the 'old password' format.  Which is compatible with more clients than the
# new password format.
if ! grep -qai old-passwords /etc/my.cnf ; then
    replace "[mysqld]" "[mysqld]
    old-passwords" --  /etc/my.cnf
fi

if ! grep -q mysql /etc/passwd ; then
    # seems like the rpm upgrade may remove the old mysql user, so re-add it
    useradd -s /sbin/nologin mysql
    chown -R mysql:mysql /var/lib/mysql
fi

# start it up
/etc/init.d/mysql restart
#wait for it to start up
sleep 10
# set a password
# Note we use old-password (not password) so the password is set using the 'old' format.
# The 'old' format seems to be more compatible with other apps (e.g. php)
if [ ! -z "$rootpasswd" ] ; then
    /usr/bin/mysqladmin -u root old-password "$rootpasswd"
    /usr/bin/mysqladmin -u root -h `hostname` old-password "$rootpasswd"
else 
    echo "No rootpasswd variable set.  We recommend you set the root password"
fi
}
installmysql

Resolving: Access denied for user: 'root@localhost.localdomain'

If you connect to MySQL like this: mysql -u root -p -h 127.0.0.1 and get and error like:

ERROR 1045: Access denied for user: 'root@localhost.localdomain' (Using password: YES)

...then MySQL is probably picking up the wrong hostname from /etc/hosts.  See if there is a line like: 127.0.0.1 localhost.localdomain localhost and change it to simply 127.0.0.1 localhost.

Resolving: [ERROR] bdb: unable to initialize mutex: Function not implemented.

You may see this on Debian systems upgrading to MySQL 4.1.  It results from your server not having the Berkely database library files necessary to support that table type in MySQL.  The BDB table type is not that common.  So you can just skip support for it.  Add a skip-bdb line to your my.cnf file (/etc/mysql/my.cnf on Debian and /etc/my.cnf on RedHat boxes).

Resolving: 'Client does not support authentication protocol requested by server'

If you get an error about client authentication version when connecting to MySQL then it may be because your server is using the new password format, while your mysql client (or PHP script, or JDBC code) is using the old password format.  See: http://dev.mysql.com/doc/mysql/en/Application_password_use.html.

You can change a new password to an old password like this:

mysql> update user set password = old_password('passwordhere') where user = 'phpbb'; flush privileges;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user, password from user;
+-------+-------------------------------------------+
| user  | password                                  |
+-------+-------------------------------------------+
| root  | *BA3E2F47E409A6ABA83D219D70631A02FE28539E |
| root  | *BA3E2F47E409A6ABA83D219D70631A02FE28539E |
|       |                                           |
|       |                                           |
| phpbb | 09a6e3834c6d11c9                          |
+-------+-------------------------------------------+
5 rows in set (0.00 sec)

In this case root has a new, longer password.  While the phpbb user has the shorter, old password format.  In PHP something like mysql_connect('localhost', 'phpbb', 'passwordhere') should now successfully create a connection.

Forgot your MySQL Password?

We all forget passwords at some point.  Fortunately you can reset your MySQL password if you forget it.

Add a "skip-grant-tables" line to /etc/my.cnf under the [mysqld] section.

Restart MySQL:
/etc/init.d/mysql* restart

Set your password to what you like:
mysql -e "update user set password = old_password('newpassword') where user = 'root'" mysql

Remove the skip-grant-tables option from /etc/my.cnf.

Restart MySQL:
/etc/init.d/mysql* restart