Friday, February 26, 2010

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.

No comments:

Post a Comment