Installing Percona Server 5.1 on Lucid (and after MariaDB)

I installed MariaDB 5.1 onto a server. It worked well, but I wanted to move towards Percona Server, looking towards the future and possibly later using Percona XtraDB Cluster.

My first attempts at doing this involved removing the APT repository for MariaDB and adding one for Percona DB:

deb http://repo.percona.com/apt lucid main
deb-src http://repo.percona.com/apt lucid main

Trying to install percona-server-server-5.1 and percona-server-client-5.1 with libmysql16 didn’t work. The command complained that there were unmet dependencies: mysql-common. According to Bug #877018, an install of the Percona Server version of libmysql16 was needed.

Turns out that my version of libmysql16 was a MariaDB version, not a Percona version – and the Percona version wasn’t to be installed:

# apt-cache policy libmysqlclient16
libmysqlclient16:
  Installed: 5.1.62-mariadb115~lucid
  Candidate: 5.1.62-mariadb115~lucid
  Version table:
 *** 5.1.62-mariadb115~lucid 0
        100 /var/lib/dpkg/status
     5.1.61-rel13.2-430.lucid 0
        500 http://repo.percona.com/apt/ lucid/main Packages
     5.1.61-0ubuntu0.10.04.1 0
        500 http://192.168.6.162/ubuntu/ lucid-updates/main Packages
        500 http://192.168.6.162/ubuntu/ lucid-security/main Packages
     5.1.41-3ubuntu12 0
        500 http://192.168.6.162/ubuntu/ lucid/main Packages

Forcing the install of the proper version of libmysql16 took care of that:

# apt-get --reinstall install libmysqlclient16=5.1.61-rel13.2-430.lucid
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libevent-1.4-2
Use 'apt-get autoremove' to remove them.
The following packages will be REMOVED:
  libmariadbclient16
The following packages will be DOWNGRADED:
  libmysqlclient16
0 upgraded, 0 newly installed, 1 downgraded, 1 to remove and 3 not upgraded.
Need to get 3,691kB of archives.
After this operation, 6,259kB of additional disk space will be used.
Do you want to continue [Y/n]? y
WARNING: The following packages cannot be authenticated!
  libmysqlclient16
Install these packages without verification [y/N]? y
Get:1 http://repo.percona.com/apt/ lucid/main libmysqlclient16 5.1.61-rel13.2-430.lucid [3,691kB]
Fetched 3,691kB in 16s (231kB/s)
(Reading database ... 100664 files and directories currently installed.)
Removing libmariadbclient16 ...
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place
dpkg: warning: downgrading libmysqlclient16 from 5.1.62-mariadb115~lucid to 5.1.61-rel13.2-430.lucid.
(Reading database ... 100657 files and directories currently installed.)
Preparing to replace libmysqlclient16 5.1.62-mariadb115~lucid (using .../libmysqlclient16_5.1.61-rel13.2-430.lucid_i386.deb) ...
Unpacking replacement libmysqlclient16 ...
Setting up libmysqlclient16 (5.1.61-rel13.2-430.lucid) ...

Processing triggers for libc-bin ...
ldconfig deferred processing now taking place

However, there were parts of the MySQL installation that were not accounted for by the removal of MariaDB nor by the installation of Percona Server. Removing these would also remove everything that depended on MySQL server – and Percona Server could not be installed until they were removed. I took care of this impasse by incorporating them into a single `apt-get` command using the syntax to remove and add packages at the same time (note the plus and minus package suffixes):

apt-get install mysql-client-core-5.1- percona-server-client-5.1+ percona-server-server-5.1+ mysql-server-core-5.1-

After a copious amount of output, this final command took care of everything: Percona Server was live. I restarted things that might have broken with MySQL going down and all was well with Percona Server 5.1.

Upgrading from MySQL 5.1 to 5.5 on Ubuntu Lucid Lynx

I decided to try Percona’s MySQL server, and went for the most recent version (5.5). Unfortunately, the version of MySQL included with Ubuntu Lucid Lynx is 5.1 – so installing Percona’s 5.5 server means an upgrade.

This upgrade requires some additional steps beyond the usual.

First, the new MySQL 5.5 makes InnoDB the default storage engine – so anything that relies on that default will have to be changed. To revert this “change” you may need to add the following to your my.cnf:

[mysqld]
default-storage-engine=MyISAM


Secondly, the mysql database will need updating. Run this command:

# su - mysql -c "mysqld_safe --init-file=/usr/share/mysql/mysql_system_tables.sql --verbose"

This runs the command as the mysql user; if you are using Ubuntu, the mysql user has a shell of /bin/false which causes this to fail. You can either give the mysql user a new shell (such as /bin/bash) by editing with the command vipw or you can run the mysql_safe command as root, like this:

# mysqld_safe --init-file=/usr/share/mysql/mysql_system_tables.sql --verbose

Once this command runs, make sure that the MySQL instance stops by running service mysql stop or mysqladmin shutdown.

This should fix things. One thing to watch out for is the innodb_buffer_pool_size – this is increased from a default of 128Mb to 132Mb; if you’ve tight memory you’ll want to plan for this.

Percona has a complete guide for migrating from 5.1 to 5.5; MySQL also has a thorough description of upgrading to 5.5. MariaDB 5.5 is not out yet, but there is instructions on upgrading to 5.3 (equivalent to MySQL 5.1).

Connecting to the Right MySQL Database – Tips and Tricks

Recently, I had to set up a second MySQL database instance on a machine, and found myself wondering which database I was really connecting to with the mysql client.

How do I make sure? This turns out to be rather easy:

mysqladmin -p -u root variables

Add the appropriate options to connect to the database you want – options like a) --socket (socket path); b) --protocol (specify protocol); c) --port (port); and several others.

When you run mysqladmin, there will be a large set of variables listed. Check datadir – it will show you where the database is located on disk. Also check socket: it shows where the mysql socket is which is used for communications. TCP communications will be done through a port, shown as port in the variables listing.

If you run mysql specifying the port, you might be surprised to see that it is ignored. Unless the client is forced to use TCP (via the --protocol parameter) or it picks TCP (through the use of the --hostname parameter) the client will use the specified socket instead.

To force TCP, you can use one of these commands:

mysql --protocol=TCP --port=3311 -p -u root

mysql --host=hostname --port=3311 -p -u root

Alternately, instead of forcing TCP, specify the local socket:

mysql --socket=/var/lib/mysql2/mysql.socket -p -u root

You can specify these options with mysqladmin to verify the actual database (using the variables command). There are short versions of these options (separate them from arguments by a space, not an equals):

• host: -h
• port: -P
• socket: -S