The Library

Related Documents

Related Articles

Migrating to Galera Cluster

Length: xxx words; Published: October 20, 2014; Topic: General; Level: Beginner

For systems that already have instances of the standalone versions of MySQL or MariaDB, the Galera Cluster installation replaces the existing database server with a new one that includes the wsrep API patch. This only affects the database server, not the data.

When upgrading from a standalone database server, you must take some additional steps in order to subsequently preserve and use your data with Galera Cluster.

For more information on installing Galera Cluster, see Galera Installation.

Upgrading System Tables

When you finish upgrading a standalone database server to Galera Cluster, but before you initialize your own cluster, you need to update the system tables to take advantage of the new privileges and capabilities. You can do this with mysql_upgrade.

In order to use mysql_upgrade, you need to first start the database server, but start it without initializing replication. For systems that use init, run the following command:

# service mysql start --wsrep_on=OFF

For servers that use systemd, instead use this command:

# systemctl start mysql --wsrep_on=OFF

The command starts mysqld with the wsrep_on parameter set to OFF, which disables replication. With the database server running, you can update the system tables:

# mysql_upgrade

If this command generates any errors, check the MySQL Reference Manual for more information related to the particular error message. Typically, these errors are not critical and you can usually ignore them, unless they relate to specific functionality that your system requires.

When you finish upgrading the system tables, you need to stop the mysqld process until you are ready to initialize the cluster. For servers that use init, run the following command:

# service mysql stop

For servers that use systemd, instead use this command:

# systemctl stop mysql

Running this command stops database server. When you are ready to initialize your cluster, choose this server as your starting node.

For more information on initializing and adding nodes to a cluster, see Starting the Cluster.

Migrating from MySQL to Galera Cluster

In the event that you have an existing database server that uses the MyISAM storage engine or the stock MySQL primary-replica replication, there are some additional steps that you need to take. The Galera Replication Plugin requires a transactional storage engine in order to function. As MyISAM is non-transactional, you need to migrate your data to InnoDB, in addition to installing the new software packages.

There are three types of database servers referred to in this guide:

  • Primary Server Refers to the MySQL primary (formerly referred to as master) server.
  • Replica Server Refers to a MySQL replica (formerly referred to as slave) server.
  • Cluster Node Refers to a node in Galera Cluster.

For the sake of simplicity, replica servers and cluster nodes are referenced collectively, rather than individually. In production, you may have several replica servers and must have at least three cluster nodes.

Infrastructure Preparation

For your existing infrastructure, you have a MySQL primary server as well as several replica servers that form a primary-replica cluster. Before you can begin migration, you first need to prepare your infrastructure for the change.

  1. Launch at least three new servers, outside of and unconnected to your existing database infrastructure.
  2. On each new server, install Galera Cluster. For information on how to do this, see Galera Installation.
  3. Configure the database server. In addition to the IP addresses of each node, on the wsrep_cluster_address parameter, include the IP addresses of the MySQL primary server and each instance of the replica servers.

For more information on configuring Galera Cluster, see System Configuration and Replication Configuration.

  1. When you finish the installation and configuration, start the cluster. For more information on how to start the cluster, see Starting the Cluster.

To check that it is running properly, log into one of the database clients and run the wsrep_cluster_size status variable:

SHOW STATUS LIKE 'wsrep_cluster_size';

+--------------------+-------+
| Varialbe_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

Galera Cluster is now running in parallel to your MySQL primary-replica cluster. It contains no data and remains unused by your application servers. You can now begin migrating your data.

Data Migration

In order to migrate data from a MySQL primary-replica cluster to Galera Cluster, you need to manually transfer it from your existing infrastructure to the new one.

  1. Stop the load of the primary server.

  2. On the primary server, run mysqldump:

    $ mysqldump -u root -p --skip-create-options --all-databases > migration.sql

    The --skip-create-options ensures that the database server uses the default storage engine when loading the data, instead of MyISAM.

  3. Transfer the migration.sql output file to one of your new cluster nodes.

    $ scp migration.sql user@galera-node-IP

  4. On the cluster node, load the data from the primary server.

    mysql -u root -p < migration.sql

  5. Restart the load from the application servers, this time directing it towards your cluster nodes instead of the primary server.

Your application now uses Galera Cluster, instead of your previous MySQL primary-replica cluster.

Note

Bear in mind that your application will experience downtime at this stage of the process. The length of the downtime varies depending on the amount of data you have to migrate, specifically how long it takes mysqldump to create a snapshot of the primary server, then transfer and upload it onto a cluster node.

Database Migration

With your application server now using the new cluster nodes, you now need to migrate your primary and replica servers from stock MySQL to Galera Cluster.

  1. Using the same process described in Galera Installation, install and configure Galera Cluster on the server.

  2. Start the node with replication disabled. For servers that use init, run the following command:

    # service mysql start --wsrep-on=OFF

    For servers that use systemd, instead run this command:

    # systemctl start mysql --wsrep-on=OFF

  3. From the database client, manually switch the storage engine on each table from MyISAM to InnoDB:

    ALTER TABLE table_name ENGINE = InnoDB;

  4. Update the system tables:

      # mysql_upgrade

For more information, see :ref:`Upgrading System Tables <upgrade-system-tables>`.

  5. From one of the running Galera Cluster nodes, copy the grastate.dat file into the data directory of the former MySQL primary server.

    $ scp grastate.dat user@server-primary-ip:/path/to/datadir

  6. Using your preferred text editor, on the former MySQL primary server update the sequence number (that is, the seqno) in the grastate.dat file from -1 to 0.

  7. Restart the primary and replica servers. For servers that use init, run the following command:

    # service mysql restart

    For servers that use systemd, instead run this command:

    # systemctl restart mysql

  8. Resume load on these servers.

When the former MySQL primary and replica servers come back after restarting, they establish network connectivity with the cluster and begin catching up with recent changes. All of the servers now function as nodes in Galera Cluster.

Related Documents

Related Articles