How do I replace msql 5.7 with percona server 5.7?

July 6, 2016 1.6k views
MySQL LEMP Ubuntu 16.04

I have a droplet with ubuntu 16.04lts, nginx 1.10 and mysql 5.7.
I want to replace Mysql 5.7 with percona server 5.7.
How do I do this without loosing database?
I tried a few time to remove mysql but it always seems to leave something behind that stops percona server installation.
Can someone do a step by step tutorial on this please?

Thank you
Steph

5 Answers

@stephgiguere

On Ubuntu 16.xx, I would first start by making a backup of /var/lib/mysql if you have already setup one or more databases. This ensures that your database data can be restored should the removal of MySQL cause removal of database data too (it shouldn't, but it's software -- things happen).

Removing MySQL

If you do not have databases, or you do and simply don't care about them, you can run the command below. You may be asked if you're sure you want to remove MySQL, use the arrow key to select yes and hit enter.

service mysql stop \
&& apt-get purge -y mysql-*

If you do have databases that you care about:

service mysql stop \
&& mkdir /usr/local/src/mysql_backup \
&& cp -R /var/lib/mysql /usr/local/src/mysql_backup

Then confirm that the data indeed exists in our new backup directory:

ls -al /usr/local/src/mysql_backup

Then purge MySQL. As above, you may be asked if you're sure you want to remove MySQL, use the arrow key to select yes and hit enter.

service mysql stop \
&& apt-get purge -y mysql-*

Once you're done, we need prepare to install Percona. We'll download the.deb file, use dpkg to install it (which adds the repositories we need to install Percona) and then update/sync the packages.

cd /usr/local/src \
&& wget https://repo.percona.com/apt/percona-release_0.1-3.$(lsb_release -sc)_all.deb \
&& dpkg -i percona-release_0.1-3.$(lsb_release -sc)_all.deb \
&& apt-get update

Now we can run:

apt-get install -y percona-server-server-5.5

... to install Percona 5.5. You'll be prompted to set a root password and from there, you should be all set and see something like:

160706 19:42:03 [Note] Plugin 'FEDERATED' is disabled.
160706 19:42:03 InnoDB: The InnoDB memory heap is disabled
160706 19:42:03 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160706 19:42:03 InnoDB: Compressed tables use zlib 1.2.8
160706 19:42:03 InnoDB: Using Linux native AIO
160706 19:42:03 InnoDB: Initializing buffer pool, size = 128.0M
160706 19:42:03 InnoDB: Completed initialization of buffer pool
160706 19:42:03 InnoDB: highest supported file format is Barracuda.
160706 19:42:03  InnoDB: Waiting for the background threads to start
160706 19:42:04 Percona XtraDB (http://www.percona.com) 5.5.49-37.9 started; log sequence number 1597945
160706 19:42:04  InnoDB: Starting shutdown...
160706 19:42:08  InnoDB: Shutdown completed; log sequence number 1597945


 * Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
 * Run the following commands to create these functions:

    mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
    mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
    mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

 * See http://www.percona.com/doc/percona-server/5.5/management/udf_percona_toolkit.html for more details


Processing triggers for systemd (229-4ubuntu6) ...
Processing triggers for ureadahead (0.100.0-19) ...

as the final output.

You may wish to use the functions noted in the message above, and to do that, you can run this (just copy and paste as you would the above commands):

mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'" \
&& mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'" \
&& mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

Here is the message I get after executing

service mysql stop \
&& apt-get purge -y mysql-*
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 dbconfig-mysql : Depends: mysql-client but it is not going to be installed or
                           mariadb-client but it is not going to be installed or
                           virtual-mysql-client
E: Error, pkgProblemResolver::Resolve generated breaks, this may be caused by he                                                                                                                                                             ld packages.

And during percona server 5.7 install I get:

Unpacking percona-server-common-5.7 (5.7.13-6-1.xenial) ...
dpkg: error processing archive /var/cache/apt/archives/percona-server-common-5.7_5.7.13-6-1.xenial_amd64.deb (--unpack):
 trying to overwrite '/usr/share/mysql/estonian/errmsg.sys', which is also in package mysql-server-core-5.7 5.7.12-0ubuntu1.1
E: Sub-process /usr/bin/dpkg returned an error code (1)

What do I do?

  • @stephgiguere

    If you ran:

    sudo apt-get install -y mysql-server
    

    ... to install MySQL, then:

    service mysql stop \
    && sudo apt-get purge -y mysql-*
    

    ... should work without and problems (just tested it on a fresh Droplet with Ubuntu 16.04).

    Since it's giving that error, try running only the purge command:

    sudo apt-get purge -y mysql-*
    

    or

    sudo apt-get purge -y mysql-server
    

    ... and see if that will purge the installation. If not, let's try updating and upgrading to see if we can automatically fix the issue before running the above again.

    sudo apt-get update && sudo apt-get upgrade -y
    

    Let that run it's course and try the above again. The only questionable output would be:

    http://repo.percona.com/apt/dists/xenial/InRelease: Signature by key 430BDF5C56E7C94E848EE60C1C4CBDCDCD2EFD2A uses weak digest algorithm (SHA1)
    

    Which simply means that Percona needs to update their key, though this won't affect the installation.

Did this work? Planning to do the same :-)

@arijuki Yes and no, I went with mariadb instead.

  • Did it work, plug'n'play? I tried some time ago and installation did not go through 😔

@arijuki
I ended up rebuilding the droplet from scratch, it does not take that long to reinstall everything and import website. Not the best solution but still faster then troubleshooting for hours.

Have another answer? Share your knowledge.