Setting up and upgrading MySQL 5.x

September 12, 2017 by Roberto Puzzanghera 0 comments

I have tested the following guide when installing mysql 5.6, 5.5, 5.1, 5.0 version. At the time I'm writing the latest stable version is 5.6.10.

Installing from source

You may want to install from source when the distributed binaries were compiled against a glibc version which is different from the one installed in your system.

cd /usr/local/mysql
wget latest mysql source
tar zxvf mysql-VERSION.tar.gz
cd mysql-VERSION
cmake .
make
make install
cd /usr/local
mv mysql mysql-VERSION
ln -s mysql-VERSION mysql

This will install mysql in /usr/local/mysql.

Installing from binaries

Download the binaries and extract the tarball where you want, /usr/local is always good.

cd /usr/local
wget mysql binaries
tar xzf mysql-VERSION.tar.gz
ln -s mysql-VERSION mysql

Configuring

Now you have mysql binaries on /usr/local/mysql. In case your distribution doesn't provide a mysql user and group by default you have to create them:

groupadd mysql 
useradd -r -g mysql mysql

Setup the file/dir permissions and install the database (for more details read carefully this http://dev.mysql.com/doc/refman/5.1/en/installing-binary.html):

cd /usr/local/mysql 
chown -R mysql.mysql . 
scripts/mysql_install_db --user=mysql 
chown -R root . 
chown -R mysql data

Note that this procedure does not setup any password for root, so you must enter the server and secure the account.

But we still have to start the server; I use the startup script provided by Slackware (it should work in any case). Adjust it to your needs; If you don't need incoming network connections, then leave --skip-networking commented out to improve system security.

File rc.mysqld

Save this file wherever you want (download). Slackware users may prefer to save it in /etc/rc.d

#!/bin/sh
# Start/stop/restart mysqld.
#
# Copyright 2003  Patrick J. Volkerding, Concord, CA
# Copyright 2003  Slackware Linux, Inc., Concord, CA
# Copyright 2008  Patrick J. Volkerding, Sebeka, MN
#
# This program comes with NO WARRANTY, to the extent permitted by law.
# You may redistribute copies of this program under the terms of the
# GNU General Public License.

# To start MySQL automatically at boot, be sure this script is executable:
# chmod 755 /etc/rc.d/rc.mysqld

# Before you can run MySQL, you must have a database.  To install an initial
# database, do this as root:
#
#   mysql_install_db --user=mysql
#
# Note that the mysql user must exist in /etc/passwd, and the created files
# will be owned by this dedicated user.  This is important, or else mysql
# (which runs as user "mysql") will not be able to write to the database
# later (this can be fixed with 'chown -R mysql.mysql /var/lib/mysql').
#
# To increase system security, consider using "mysql_secure_installation"
# as well.  For more information on this tool, please read:
#   man mysql_secure_installation

# To allow outside connections to the database comment out the next line.
# If you don't need incoming network connections, then leave the line
# uncommented to improve system security.
# SKIP="--skip-networking"

DATA="/usr/local/mysql/data"
MYSQLD="/usr/local/mysql/bin/mysqld_safe"
PID="$DATA/mysql.pid"

# Start mysqld:
mysqld_start() {
  if [ -x $MYSQLD ]; then
    # If there is an old PID file (no mysqld running), clean it up:
    if [ -r $PID ]; then
      if ! ps axc | grep mysqld 1> /dev/null 2> /dev/null ; then
        echo "Cleaning up old $PID."
        rm -f $PID
      fi
    fi
    $MYSQLD --datadir=$DATA --pid-file=$PID $SKIP &
  fi
}

# Stop mysqld:
mysqld_stop() {
  # If there is no PID file, ignore this request...
  if [ -r $PID ]; then
    killall mysqld
    # Wait at least one minute for it to exit, as we dont know how big the DB is...
    for second in 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 \
      0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 60 ; do
      if [ ! -r $PID ]; then
        break;
      fi
      sleep 1
    done
    if [ "$second" = "60" ]; then
      echo "WARNING:  Gave up waiting for mysqld to exit!"
      sleep 15
    fi
  fi
}

# Restart mysqld:
mysqld_restart() {
  mysqld_stop
  mysqld_start
}

case "$1" in
'start')
  mysqld_start
  ;;
'stop')
  mysqld_stop
  ;;
'restart')
  mysqld_restart
  ;;
*)
  echo "usage $0 start|stop|restart"
esac

Now let's start the server:

./rc.mysqld start

Now let's secure the server setting the password for root and deleting the test db

> cd /usr/local/mysql/bin 
> ./mysql -u root -p
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');
mysql> DROP DATABASE test;

Now delete all the anonymous accounts (adjust this to your needs):

mysql> DROP USER ''@'mysql-55';
mysql> DROP USER ''@'mysql-55';

mysql> SELECT User, Host, Password FROM mysql.user;
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root | host-name | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root | 127.0.0.1 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

Now all the root users have a not empty password.

Finally, in case your server is 64b, since libraries are stored in /usr/local/mysql/lib and not lib64 you can face problems when you configure php, for instance, which search the libraries in the lib64 dir.

Solve this creating a symbolic link:

cd /usr/local/mysql
ln -s lib lib64

Upgrading

NB: the following applyes to any upgrade from v. 5.0->5.1-5.5-5.6

As a general rule, to upgrade from one release series to another, you should go to the next series rather than skipping a series. The following notes have been tested during the upgrades from v. 5.0 to v. 5.1 and from v. 5.1 to v. 5.5.

When you upgrade you must always run mysql/bin/mysql_upgrade. But if you have Innodb tables the process can fail, because Innodb does not support REPAIR TABLE, so we'll use mysqldump to create a dump file and mysql to reload the file, as described here.

I'll suppose that at this point you still have the old server installed let's say in /usr/local/mysql-5.1 and the new server installed in /usr/local/mysql-5.5. The server that is actually running will be the one with a symbolic link to /usr/local/mysql:

root@mysqlserver:/usr/local> ls -l

lrwxrwxrwx 1 root root 9 Nov 1 16:52 mysql -> mysql-5.1/
drwxr-xr-x 2 root root 4096 Nov 1 16:50 mysql-5.1/
drwxr-xr-x 2 root root 4096 Nov 1 16:50 mysql-5.5/

The dump file must be created with the old server, and obviously it will be restored in the new server. So log into the old server and dump all databases:

cd /usr/local/mysql/bin 
./mysqldump --lock-tables --all-databases > ../../dump.sql -u root -p[password]

Now stop the old server

/path/to/rc.mysqld stop

Switch the symbolic link to the new server so that it will be started next time you run rc.mysqld:

rm mysql
ln -s mysql-5.5 mysql

Start the new server:

/path/to/rc.mysqld start

Restore the DBs

/usr/local/mysql/bin/mysql < ../../dump.sql -u root -p

If you get an error like

ERROR 6 (HY000) at line 30: Error on delete of './db_name/table_name.MYI' (Errcode: 13)

this is how to manage error codes:

> /usr/local/mysql/bin/perror 13

OS error code  13:  Permission denied

This is because (http://dev.mysql.com/doc/refman/5.5/en/load-data.html)

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 5.4.1, “Privileges Provided by MySQL”. For non-LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

At the end (try and error many times) both dump.sql and data dir must have 777 permissions.

cd /usr/local/mysl/data 
chown -R root:root . 
chmod -R o+wrx . 
chmod 777 /usr/local/dump.sql 

/usr/local/mysql/bin/mysql < /usr/local/to/dump.sql -u root -p

and we've got it!

Now restore the proviledges to mysqldata dir

chmod -R o-wrx . 
chown -R mysql.mysql .

To be sure that everything is ok, run the mysql_upgrade command

/usr/local/mysql/bin/mysql_upgrade -u root -p

Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
db1.table1                           OK
db1.table2                           OK
db2.table1                           OK

this performs a mysqlcheck/repair. If all tables are OK you can put the new mysql server in production.

Troubleshooting

If you get an error like this

ERROR 2006 (HY000) at line 982: MySQL server has gone away

then try adding max_allowed_packet=64M to your config file (see also here). This can be done also via command line as root doing

set global max_allowed_packet=64*1024*1024;

Add a comment

Recent comments
Recent posts

RSS feeds