mysql – Nicovs' Blog

When you need to migrate a MySQL server instance to a new server for any reason, eg upgrade of hardware, distri upgrade,… you can easily do the following:

Migrating the databases
On the “old” mysql server, you need to export all databases by:

mysqldump -uroot -p --all-databases > /tmp/dumpofalldb.sql

1	mysqldump -uroot -p --all-databases > /tmp/dumpofalldb.sql

Copy your sql dump file with eg rsync

rsync -avh /tmp/dumpofalldb.sql username@192.168.10.11:/tmp

1	rsync -avh /tmp/dumpofalldb.sql username@192.168.10.11:/tmp

On the new server, import your dumps:

mysql -uroot -p < /tmp/dumpofalldb.sql

1	mysql -uroot -p < /tmp/dumpofalldb.sql

Migrating the users

If you are migrating to the same MySQL version you can export all users and settings by dumping the mysql database itself.

mysqldump -uroot -p mysql &gt; /tmp/dumpmysqldb.sql

1	mysqldump -uroot -p mysql > /tmp/dumpmysqldb.sql

and just copy & import of that file on the new server

However, when doing a MySQL version upgrade, we need to export all the Users and their privileges.

Here’s an easy bash script to do this.

#!/bin/bash
# addapted from http://serverfault.com/questions/105612/how-to-copy-user-priviledges-with-mysql
read -p "Enter your MySQL admin user: " User
read -sp "Enter password" Password
echo ""
echo "-----------------------------------------------------------------------------------"
( mysql -u$User -p$Password --batch --skip-column-names -e "SELECT user, host FROM user" mysql ) | while read user host
do
        CreateUser=`mysql -u$User -p$Password --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"`
        echo "$CreateUser;"
        echo "flush privileges;"
done
echo "-----------------------------------------------------------------------------------"

#!/bin/bash

# addapted from http://serverfault.com/questions/105612/how-to-copy-user-priviledges-with-mysql

read -p "Enter your MySQL admin user: " User

read -sp "Enter password" Password

echo ""

echo "-----------------------------------------------------------------------------------"

( mysql -u$User -p$Password --batch --skip-column-names -e "SELECT user, host FROM user" mysql ) | while read user host

CreateUser=`mysql -u$User -p$Password --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"`

echo "$CreateUser;"

echo "flush privileges;"

done

echo "-----------------------------------------------------------------------------------"

Copy the lines between the ———————-

mysql -uroot -p

1	mysql -uroot -p

And paste the output from our previous script.

Et Voila, your databases and users are migrated!

Recently I had some problems with a Sphinx search server which was upgraded through

apt-get update && apt-get upgrade

1	apt-get update && apt-get upgrade

This caused the Sphinx Storage engine inside my MySQL server fail to work.

When doing a search inside Mysql, eg by:

select * from dbname.content_index;

1	select * from dbname.content_index;

I got the follwoing error:

Error Code: 1286. Unknown table engine 'SPHINX'

1	Error Code: 1286. Unknown table engine 'SPHINX'

Also, when i was looking inside MySQL, to show the Engines, did not see any Sphinx engine active:

root@mysql:~#  mysql -uroot -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

mysql>

root@mysql:~# mysql -uroot -p

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 76

Server version: 5.1.63-0+squeeze1 (Debian)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines;

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

8 rows in set (0.00 sec)

mysql>

After searching my google friend, I never really found a straightforward solution to this problem. That’s why I bundled all steps I had to take to fix this problem.

I know my Shpinx was installed by enabling the ha_sphinx.so from within MySQL, but then i got this error:

mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';
ERROR 1126 (HY000): Can't open shared library '/var/lib/mysql/plugins//ha_sphinx.so' (errno: 2 undefined symbol: _ZN7handler5cloneEP11st_mem_root)
mysql>

mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

ERROR 1126 (HY000): Can't open shared library '/var/lib/mysql/plugins//ha_sphinx.so' (errno: 2 undefined symbol: _ZN7handler5cloneEP11st_mem_root)

mysql>

Time to redo Sphinx – MySQL integration it seems…. This is because your sphinx plugin is build on the installed release version of MySQL :'(

These are the steps you need to perform to do this:

1. Look up the current installed version of MySQL

# dpkg --list | grep mysql-server
ii  mysql-server-5.1                        5.1.63-0+squeeze1            MySQL database server binaries and system database setup
ii  mysql-server-core-5.1                   5.1.63-0+squeeze1            MySQL database server binaries

# dpkg --list | grep mysql-server

ii mysql-server-5.1 5.1.63-0+squeeze1 MySQL database server binaries and system database setup

ii mysql-server-core-5.1 5.1.63-0+squeeze1 MySQL database server binaries

It seems that i have MySQL 5.1.63 installed on this system.

2. Download the MySQL source for THIS version and also the latest Sphinx version from the site

I got mine as follows:

wget http://sphinxsearch.com/files/sphinx-2.0.6-release.tar.gz
wget http://downloads.skysql.com/archives/mysql-5.1/mysql-5.1.63.tar.gz

1 2	wget http://sphinxsearch.com/files/sphinx-2.0.6-release.tar.gz wget http://downloads.skysql.com/archives/mysql-5.1/mysql-5.1.63.tar.gz

Extract the archives

tar -xvf mysql-5.1.63.tar.gz
tar -xvf sphinx-2.0.6-release.tar.gz

1 2	tar -xvf mysql-5.1.63.tar.gz tar -xvf sphinx-2.0.6-release.tar.gz

Copy the mysqlse directory from sphinx to mysql engine directory from the sources:

cp -R sphinx-2.0.6/mysqlse/ mysql-5.1.63/storage/sphinx

1	cp -R sphinx-2.0.6/mysqlse/ mysql-5.1.63/storage/sphinx

Build your sphinx files by:

cd mysql-5.1.63 
sh BUILD/autorun.sh
./configure; make

cd mysql-5.1.63

sh BUILD/autorun.sh

./configure; make

Your newly created sphinx.so files can now be copied to the mysql live instyalltion directory:

* You night need some pre-requisits to perform this:

apt-get install build-essential
apt-get install cmake
apt-get instal libncurses5-dev

apt-get install build-essential

apt-get install cmake

apt-get instal libncurses5-dev

But 1st check your plugin dir details in

/etc/sphinx/default

or /etc/mysql/my.cnf

cp storage/sphinx/.libs/ha_sphinx.* /usr/lib64/mysql/plugin

1	cp storage/sphinx/.libs/ha_sphinx.* /usr/lib64/mysql/plugin

Time to load your sphinx plugin :

mysql -u root -p

mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

mysql -u root -p

mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

root@mysql:~/#  mysql -uroot -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| SPHINX     | YES     | Sphinx storage engine 2.0.6-release                            | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>

root@mysql:~/# mysql -uroot -p

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.1.63-0+squeeze1 (Debian)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines;

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| SPHINX | YES | Sphinx storage engine 2.0.6-release | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

mysql>

Tag: mysql

Migrating MySQL to a new server

Install MySQL with Sphinx Search Engine support

Forgot MySQL root password