lundi 5 mars 2012

MySQL 5.5 upgrade on Debian Squeeze (1/2)

Since a few days, I started to see the following error in the log file:
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x9C\xF0\x9F...' for column 'message' at row 1
After some googling, I found an answer on SO. It seems that the problem was caused by some characters  like an 'emoji' character aka japanese smiley face.

There are known issues storing 4byte utf characters in some versions of MySQL that were fixed by using utf8mb4 to represent 4 byte UTF characters, as the normal utf8 character set can only represent characters up to 3 bytes in length.

This has been fixed on MySQL 5.5.3 and we will see in this post series how to run different versions of MySQL on the same Debian server.
In this first post, we will install the server, of course we will keep the old instance of MySQL running. We will make sure that the new server can be started before we will move on to the next post that describe how to make an init script to start/stop your new MySQL server with grace.


Configuration files and scripts are available for download on our git repository at bitbucket

But wait, why not just upgrade to the new version with aptitude?

Because, there are other applications using the current MySQL server that I don't want to disturb.

If you don't have this constraint or if you are starting from scratch, I recommend installing the latest version of MySQL using aptitude with dotdeb.org because in Debian Squeeze, the current version of MySQL is (and it remains forever) the 5.1.x

Installing a fresh MySQL 5.5 server:

‣ make sure you have a full safe backup of all of your data and configuration files.
$ cp -r /var/lib/mysql/* /home/backup/var_lib_mysql/
$ mysqldump -u root -p --all-databases /home/backup/mysql/dump.sql
‣ Install the asynchronous I/O library
This is so that we can take advantage of the asynchronous I/O capability in the new InnoDB plugin that ships with MySQL 5.5
# aptitude install libaio-dev
‣ Download the latest version of MySQL
Get MySQL-5.5.21-1.linux2.6.x86_64.tar from their website . Note that this is the Linux Generic version and not the debian one. If you are using a 32bits OS choose mysql-5.5.21-linux2.6-i686.tar.gz
‣ Untar and move mysql folder to /usr/local/appservers/
Create also a symbolic link as follows:
/usr/local/appservers#ln -s mysql-5.5.21-linux2.6-x86_64 mysql-5.5
‣ Set the correct file and directory permissions on the MySQL installation directory
Setting correct permissions is very important, make sure that all the files except those under the data directory are owned by root. The data directory has to be owned by the user mysql.
 chown -R root:root mysql-5.5/*
 chown -R mysql:mysql mysql-5.5/data
‣ Get your my.cnf configuration file
Copy the sample MySQL configuration file to the etc directory or use your old my.cnf
 $ mkdir /etc/mysql-5.5
 $ cp /usr/local/appservers/mysql-5.5/support-files/my-large.cnf /etc/mysql-5.5/my.cnf
You can checkout my configuration file which contains some optimization, especially if you don't use MyIsam engine (I'm using Innodb engine exclusively) so I deleted some useless options like: key_buffer that was defined by the debian version of my.cnf

The datadir option which is where MySQL store data, was set to a directory located on a partition with enough space to hold your data
datadir = /usr/local/appservers/mysql-5.5/data
For performance issue, query logging was disabled:
general_log and general_log_file
Finally, I included some additional configuration files (lower_case_table_names.cnf, mysqld_safe_syslog.cnf)
!includedir /etc/mysql-5.5/conf.d/
It is worth noting, that every option is defined on the MySQL website
‣ Create /var/run/mysqld-5.5
#mkdir /var/run/mysqld-5.5
#chown -R mysql /var/run/mysqld-5.5/
#ls -l /var/run/mysql*
#drwxr-xr-x 2 mysql       root        4096 mar 30  2010 mysqld
#drwxr-xr-x 2 mysql       root        4096 mar  6 01:08 mysqld-5.5
#

‣ Initialize the MySQL data directory and create the system tables
/usr/local/appservers/mysql-5.5/scripts#./mysql_install_db --user=mysql \
 --datadir=/usr/local/appservers/mysql-5.5/data/ \
 --basedir=/usr/local/appservers/mysql-5.5 \
 --defaults-file=/etc/mysql-5.5/my.cnf
‣ You can now start the server
/usr/local/appservers/mysql-5.5/bin/mysqld_safe \
 --defaults-file=/etc/mysql-5.5/my.cnf \
 --basedir=/usr/local/appservers/mysql-5.5
‣ Connect to your server
/usr/local/appservers/mysql-5.5/bin/mysql -S/var/run/mysqld-5.5/mysqld-5.5.sock -uroot

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
‣ Stop the server
mysqladmin --defaults-file=/etc/mysql-5.5/my.cnf shutdown
Success, we have been able to install a new version of MySQL server on a machine that was running an old instance of MySQL.
Now, jump to the next post to see how to use /etc/init.d/mysql-5.5 start|stop command and how to make your new server start at system boot.

Aucun commentaire:

Enregistrer un commentaire