Migrating MySQL to a new server

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:

Copy your sql dump file with eg rsync

On the new server, import your dumps:

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.

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.

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

Login on your new server, go to the mysql shell via the admin user

And paste the output from our previous script.

Et Voila, your databases and users are migrated!

 

Install MySQL with Sphinx Search Engine support

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

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

When doing a search inside Mysql, eg by:

I got the follwoing error:

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

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:

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

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:

Extract the archives

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

Build your sphinx files by:

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:

 

But 1st check your plugin dir details in

/etc/sphinx/default

or /etc/mysql/my.cnf

Time to load your sphinx plugin :

 

 

Forgot MySQL root password

I you forgot your MySQL root password, you can recover MySQL database server password with following five easy steps.

  • Stop the MySQL server process.
  • Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for password.
  • Connect to mysql server as the root user.
  • Setup new mysql root account password i.e. reset mysql password.
  • Exit and restart the MySQL server.

Here are commands you need to type for each step (login as the root user):

  • Stop mysql service
  • Start to MySQL server without password:
  • Connect to mysql server using mysql client:
  • Setup new MySQL root user password
  • Stop MySQL Server:
  • Start MySQL server and test it