Testing new MySQL on production server

Even when your MySQL works on a mission-critical server, there is a way to make downtime as short as possible after you have compiled a new version of the DBMS. MySQL documentation contains a chapter called 5.6. Running Multiple MySQL Servers on the Same Machine. But the reality is a bit more complicated.

First of all, you have to create a test database with the mysql_install_db command. Create an empty directory called, for example, test-db in your home directory and give full permissions to all users, to avoid problems with mysql user trying to create files:

$ mkdir test-db $ chmod 777 test-db

Now, let's populate the directory with a test database:

$ mysql_install_db --user=mysql --datadir=/DIR/db

Note that we assume that your database runs under the username mysql, as it should. Now, you can try to run the test database. It must listen on a different IP port, use different data directory, different files for socket and pid, different log and error log. Hence, the following command:

$ PATH-TO-NEW/mysqld --port=12345 --datadir=/DIR/db --socket=/DIR/db/sock --pid-file=/DIR/db/pid --log=/DIR/db/log --log-error=/DIR/db/log-err

Now, you can check whether the new, fresh copy of MySQL works. If it does, you can stop the running version and then run make install for the new one.

Note that we did not grant any permissions to any users, so you might not be able to connect to the new database with mysql client.

MySQL+XtraDB: fixing compilation errors

Trying to launch MySQL with the new XtraDB engine. First, I downloaded MySQL sources with patches by Percona and untarred them. They compile without errors and MySQL starts without problems. However, this tarball uses InnoDB, while we wanted to test XtraDB.

Next, I downloaded XtraDB 5.1.42-1.0.6-9 sources from Percona web-site. According to the installation instructions, to use XtraDB, you have to replace the contents of storage/innobase directory with the contents of the XtraDB archive. This time, I received two error messages during the compilation phase, but both of them had already been addressed by the developers and described at Percona's Launchpad.net. In the first case, the file handler/i_s.cc contained a minor error at line 801: error: invalid conversion from ‘const char*’ to ‘char*’

To fix this,

if((p = strchr(index->table_name, '/')))

in line 801 in handler/i_s.cc has to be replaced with:

if((p = strchr((char *) index->table_name, '/')))

Then I got another error message: ha_innodb.cc:2622: undefined reference to `active_mi'. To solve this second issue, I had to add disable compilation of MySQL embedded server by adding the following option to the ./configure script: --without-embedded-server.

So, finally, make command succeeded and I tried to install MySQL, but then there was the third error:/bin/sh: @MKDIR_P@: command not found. Fortunately, someone else had seen this message before and the solution is available here. In the file /storage/innobase/Makefile.in line MKDIR_P = @MKDIR_P@ must be replaced with MKDIR_P = @mkdir_p@.

So, to save your time, do these three modifications before you run ./configure. So, the session transcript would look like this:

$ wget http://www.mysqlperformanceblog.com/mysql/5.1/\
source/mysql-5.1.26-percona.tar.bz2
$ wget http://www.percona.com/percona-builds/Percona-XtraDB/\
Percona-XtraDB-5.1.43-9.1/source/percona-xtradb-1.0.6-9.1.tar.gz
$ tar -xjf mysql-5.1.26-percona.tar.bz2
$ tar -xzf percona-xtradb-1.0.6-9.1.tar.gz
$ rm -r mysql-5.1.26-percona/storage/innobase/*
$ mv percona-xtradb-1.0.6-9.1/* mysql-5.1.26-percona/storage/innobase
$ cd mysql-5.1.26-percona/
mysql-5.1.26-percona$ sed -i 's/strchr(index/strchr((char *) index/' storage/innobase/handler/i_s.cc
mysql-5.1.26-percona$ sed -i 's/@MKDIR_P@/@mkdir_p@/' storage/innobase/Makefile.in
mysql-5.1.26-percona$ ./configure '--build=x86_64-linux-gnu' \
'--host=x86_64-linux-gnu' '--prefix=/usr' '--exec-prefix=/usr' \
'--libexecdir=/usr/sbin' '--datadir=/usr/share' \
'--localstatedir=/var/lib/mysql' '--includedir=/usr/include' \
'--infodir=/usr/share/info' '--mandir=/usr/share/man' '--with-server-suffix=-1ubuntu2' \
'--with-comment=(Ubuntu)' '--with-system-type=debian-linux-gnu' \
'--enable-shared' '--enable-static' '--enable-thread-safe-client' \
'--enable-assembler' '--enable-local-infile' '--with-pic' \
'--with-lib-ccflags=-fPIC' '--with-pstack' '--with-fast-mutexes' \
'--with-big-tables' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' \
'--with-mysqld-user=mysql' '--with-libwrap' '--with-ssl' \
'--without-docs' '--with-extra-charsets=all' '--with-plugins=max' \
'--without-ndbcluster' '--without-embedded-server' '--with-embedded-privilege-control' \
'build_alias=x86_64-linux-gnu' 'host_alias=x86_64-linux-gnu' \
'CC=gcc' 'CFLAGS=-O3 -DBIG_JOINS=1 -fPIC -fno-strict-aliasing' \
'LDFLAGS=-Wl,-Bsymbolic-functions' 'CPPFLAGS=' 'CXX=g++' \
'CXXFLAGS=-O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions \
-fno-rtti -fPIC -fno-strict-aliasing' 'FFLAGS=-g -O2'
mysql-5.1.26-percona$ make
mysql-5.1.26-percona$ sudo make install