Cédric Bruderer

Most of you know, that it is possible to synchronize MySQL and MariaDB servers using replication. But with the latest releases, it is also possible to use more than just two servers as a multi-master setup.

Most of you know that both MySQL and MariaDB support replication in a hierarchical master-slave-setup, to propagate changes across all connected servers.

But with the latest releases, a slave can have more than one master.

The keyword: Multi-Source replication

It is supported from MySQL 5.7 and MariaDB 10.0 on, and this article describes how to set it up.

What does Multi-Source mean?

Multi-Source means, that you can take two or more masters and replicate them to one slave, where their changes will be merged. This works just like the regular MySQL/MariaDB replication.

Well, we are going to exploit that a little. It is still possible to configure a Master-Master set up, what basically allows the following configuration.

Multiple servers are conjoined in a cluster, where every server is a master, and the replication happens over one central node.

Why should we use Multi-Source-Replication, if it's just Master-Master?

With a Master-Master-Setup, you are limited to a ring topology or two servers. With Multi-Source, you can now use more than two server without having to use the ring topology, which might break and cause the replication to halt.

Further it is possible to back everything up at one place, without the risk of interrupting access to the databases.

Layout

The logical topography is a star. The following image shows a possible set up, with servers located in different countries. Thanks to the asynchronous replication, which does not require a broadband connection to work properly, this is possible without a problem.

What has to be considered?

• The problems for any other Master-Master-Setup apply here as well.
• If the replication in the cluster is stalled, the problem is usually on more than one server, maybe even the entire cluster.
• Although the synchronization is asynchronous and does not cause a lot of network traffic itself, the replication of large or heavily accessed databases will cause some traffic at the central node.

How do I set it up?

The way you set it up, is like any other Master-Master replication. Except, that you will have more masters in the cluster.

1) Set up a standard installation of MySQL 5.7 or MariaDB 10.0 or above.

 

2) Prepare the configuration on all servers:

- On all the outer nodes (In Layout: All except server 1)

log_slave_updates = 0;

- On the central node (In Layout: server 1)

log_slave_updates = 1;

 

NOTE:

• The central server must forward everything it receives, so that the changes starting on some outer node will also reach all the other outer nodes.
• The outer nodes must not forward such changes, because they would loop through the cluster forever.

 

- Give each server a unique ID!

 

- Create the user which will be used for the replication:

CREATE USER 'replicator'@'localhost' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'' IDENTIFIED BY 'password';

- On MySQL 5.7 you have to enable the use of GTIDs (see step 3).

 

3) Set up the replication with all the outer nodes:

I will start with MySQL 5.7:

Source:

• MySQL Multi-Source-Replication
• Online enable GTIDs

 

Although there is a paragraph, which states that it is possible with file and position, I have experienced something different, what forced me to enable GTIDs.

You have to change the repositories for "master info" and "relay log" to the format "TABLE":

mysql> SET GLOBAL master_info_repository = 'TABLE'; mysql> SET GLOBAL relay_log_info_repository = 'TABLE';

Further, it is necessary to enable GTID. From MySQL 5.7.6 and higher, it is possible to do this without restarting the server.

mysql> SET GLOBAL ENFORCE_GTID_CONSISTENCY = ON; mysql> SET GLOBAL gtid_mode= OFF_PERMISSIVE; mysql> SET GLOBAL gtid_mode= ON_PERMISSIVE; mysql> SET GLOBAL gtid_mode= ON;

The value of "ENFORCE_GTID_CONSISTENCY" has to be "ON", otherwise the slave will return an error and refuse to work. And don't forget to make those changes in your "my.cnf" persistent.

Create a new link to a master:

mysql> CHANGE MASTER TO MASTER_HOST='', MASTER_PORT=, MASTER_USER='', MASTER_PASSWORD='', MASTER_AUTO_POSITION=1 FOR CHANNEL 'mysql-master';

Start slave:

mysql> START SLAVE [thread_type] [FOR CHANNEL=[]];

Stop slave:

mysql> STOP SLAVE [thread_type] [FOR CHANNEL=[]];

How to modify a link:

mysql> STOP SLAVE FOR CHANNEL='mysql-master'; mysql> CHANGE MASTER TO MASTER_HOST='', MASTER_PORT=, MASTER_USER='', MASTER_PASSWORD='', MASTER_AUTO_POSITION=1 FOR CHANNEL 'mysql-master'; mysql> START SLAVE FRO CHANNEL='mysql-master'; mysql> SHOW SLAVE STATUS FOR CHANNEL 'mysql-master'\G

 

Now MariaDB 10.0:

Source: MariaDB Multi-Source-Replication

Create a new link to a master (ATTENTION: MariaDB has a different syntax, compared to MySQL):

mariadb> CHANGE MASTER 'maria-master' TO MASTER_HOST='', MASTER_PORT=, MASTER_USER='', MASTER_PASSWORD='';

Start all slaves on this server at the same time:

mariadb> START ALL SLAVES;

Check the status of all slaves on this server:

mariadb> SHOW ALL SLAVES STATUS\G

If you want to manage a slave on its own, you can use the regular commands. But you have to make the connection the default one.

Here is an example what you have to do, if you want to modify the connection 'maria-master':

mariadb> STOP SLAVE'maria-master'; mariadb> CHANGE MASTER 'maria-master' TO MASTER_HOST='', MASTER_PORT=, MASTER_USER='', MASTER_PASSWORD=''; mariadb> START SLAVE 'maria-master'; mariadb> SHOW SLAVE 'maria-master' STATUS\G

After you have done this, you have a normal Master-Slave replication between two servers (as shown in the following picture). To complete the star, repeat those steps on each server, you want to connect.

Is it possible expand an existing set up?

It is no problem to expand an existing master-master or master-slave set up. However I would recommend to create new connections. This way the replication is consistent in the configuration with the other connections.

How do I break it?

Just like any other Master-Master-Setup. So be careful, with writing on more than one server. In general, if a bad command is committed, it will be replicated to the other nodes. This will cause the cluster to stall.

Another problem is auto_increment. Duplicate IDs will cause a "Duplicate Key Error" and stall each server it happens on. This can be prevented by editing the values of "auto_increment_increment" and "auto_increment_offset". In this scenario, the value "auto_increment_increment" should be 7 (the amount of servers in the cluster) and the value of "auto_increment_offset" would be something from 0 to 6 (or 1 to 7, depending on your preferences).

How do I fix it?

Sadly, that is not so easy. In addition, the statements are still replicated over the cluster, what usually causes more than just one server to stall, most time it is the entire cluster.

You have to execute every action on each connection. This gets tedious if you have a large amount of nodes.

Let's assume you have a duplicate key error, because two inserts happened at the same time.

 

MySQL 5.7:

If you experience a stalled replication on MySQL, you have to skip the GTID of the transaction which caused the stall.

First, stop the slave:

mysql> STOP SLAVE FOR CHANNEL 'failed-transactions-channel-name';

Retrieve the next GTID:

mysql> SHOW SLAVE STATUS\G

The line "Retrieved_Gtid_Set" contains the next GTID which would be executed. Copy the value and tell the server to execute that GTID:

mysql> SET GTID_NEXT="dd57a411-b477-11e5-b518-005056244454";

Execute a blank transaction:

mysql> BEGIN; COMMIT;

Tell the server to take control of the GTIDs:

mysql> SET GTID_NEXT="AUTOMATIC";

And restart the slave:

mysql> START SLAVE FOR CHANNEL 'failed-transactions-channel-name';

 

MariaDB 10.0:

Stop the slave for the connection:

mariadb> STOP SLAVE 'maria-master';

Define which connection, you would like to edit:

mariadb> SET @@default_master_connection='maria-master'; # No, it's not a joke.

Skip the failed statement:

mariadb> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

Restart the slave:

mariadb> START SLAVE 'maria-master';

Check if the slave is running:

mariadb> SHOW SLAVE 'maria-master' STATUS\G

Please note the "@@default_master_connection". It is really necessary to set this variable, or you will not be able to change the counter you want. I'm not sure, if I should be surprised or shocked, that this is the recommended solution by MariaDB.

Hot expand

What is needed to add a new node? Is it required to stop the entire cluster? Or can I just add a new server?

If you want to add a new node to the cluster, it would be best, if you take a dump from another node, including the master data. You then import that dump into the node, make sure the link to the master is configured correctly, and start it. If everything is set up the right way, you should have no problems at all.

It is not necessary to stop the entire cluster, since you can add the links between the nodes while the servers are up.

Backup and Restore Method 1

The thought behind Multi-Source replication was to make the administrators life easier when it comes to backups. Instead of backing up all data at their respective location, you can gather it on one server and do the backup on this machine, without interrupting the "productive" servers work.

To obtain a backup of all the replicated databases from the cluster, it would be best to do it on the central server. The reason for this is, that everything has to go over it. This method is suitable to protect yourself from to losing all data on the cluster. The downside is, when one node dies, you have to obtain the backup from that location and transfer it to the failed server.

Method 2

If you would like to keep your data save on the location of the server, you can set up a slave at each location and replicate the master to it. For a restore, you could use the existing slave as the new node of the star, while the old server is rebuilding. This set up is capable of keeping the downtime of the service as little as possible. Further, you are not required to transfer the backup from one location to another, since it is already stored close to the failed server.

 

 

Versions used:

MariaDB: 10.1.10

MySQL: 5.7.10

In my first Blog ever, I am going to cover the migration of a SQLite-Database to MySQL. The Tool used is MySQL-Workbench, which you can Download from the MySQL website. In this particular case, it is about the upgrade of mocenter 0.2 to 0.3.

In the Workbench on the right side, you have the button “Database Migration”. Once you click on it, the introduction to the migration wizard will show up.

Setting up source and target

On the bottom of the screen there is a button called “Start Migration”. Click it to get to the source selection.

On the first drop down menu choose “SQLite”. The menu will now change and give you the possibility to load a file. After you did this you can test the connection with the button on the bottom left, or go to the bottom right and click next.

You get a new menu, where you can choose your target of the database. You can use a stored connection or a new one. If you have no idea which connection type to use, TCP/IP usually works fine. Here I recommend you to test the connection, so you see if you can reach target. Then click next.

If the schema fetch does not return any error, you get to the schema selection.

There you have to select a target schema, before you can click next and go to fetch the source. Once you have done that too, click next once more.

Object selection

Now the database is ready to copy.

If you want to remove some of the tables from the migration, you can do this under “Show Selection”.

In case you see some warnings, you can ignore them.

Now click next and make sure there are no errors or failures, until you can select how to create the target database.

If you want to, you can create the SQL file to import the database structure somewhere else. I am just going to put it onto my server.

Clicking next after this step will create the database on the server. If you chose to create the SQL file, it would be created now as well.

Click next, when the creation is done, and you should get something, that looks like this picture. If you select a line, you will be shown the command that was executed.

This was the structure. Now to the data:

To transfer your data from the SQLite directly into the MySQL-Database, you can make an online copy. If you want to, or have to, do it from the command line you could also make a bash file.

If you click the option “Truncate target tables”, all the tables that already exist will be cleared of any data, so be careful using that checkbox.

Once complete, you should get report that looks somewhat like this.

------------------------------------------------------------------------------------ MySQL Workbench Migration Wizard Report Date: Sun Oct 18 17:36:53 2015 Source: SQLite 1.0.0 Target: MySQL 5.6.24 ------------------------------------------------------------------------------------ I. Migration 1. Summary Number of migrated schemas: 1 1. mocenter Source Schema: mocenter - Tables: 10 - Triggers: 0 - Views: 0 - Stored Procedures: 0 - Functions: 0 2. Migration Issues - versions warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - PRIMARY warning Truncated key column length for column from 0 to 255 - nodes warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - name_ui warning Truncated key column length for column from 0 to 255 - clusters warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - cluster_name warning Truncated key column length for column from 0 to 255 - vips warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - ip_address warning Truncated key column length for column from 0 to 255 - servers warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - server_name warning Truncated key column length for column from 0 to 255 - users warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - login_name warning Truncated key column length for column from 0 to 255 - moc_identifier warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - PRIMARY warning Truncated key column length for column from 0 to 255 - checks warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - PRIMARY warning Truncated key column length for column from 0 to 255 - name_dc warning Truncated key column length for column from 0 to 255 - jobs warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. 3. Object Creation Issues 4. Migration Details 4.1. Table mocenter.versions (versions) Columns: - version LONGTEXT - mr_version INT NULL - timestamp INT NULL Foreign Keys: Indices: - PRIMARY (version(255)) 4.2. Table mocenter.licenses (licenses) Columns: - license LONGTEXT NULL Foreign Keys: Indices: 4.3. Table mocenter.nodes (nodes) Columns: - node_id INT - name LONGTEXT NULL - last_change_ts INT NULL - status LONGTEXT NULL - node_type LONGTEXT NULL - hostname LONGTEXT NULL - basedir LONGTEXT NULL - datadir LONGTEXT NULL - my_cnf LONGTEXT NULL - port INT NULL - database_user LONGTEXT NULL - database_user_password LONGTEXT NULL - error_log LONGTEXT NULL - pid_file LONGTEXT NULL - read_only INT NULL - server_id INT NULL - role_id INT NULL - cluster_id INT NULL - master_id INT NULL Foreign Keys: Indices: - PRIMARY (node_id) - name_ui (name(255)) 4.4. Table mocenter.clusters (clusters) Columns: - cluster_id INT - name LONGTEXT NULL - last_change_ts INT NULL - type INT NULL Foreign Keys: Indices: - PRIMARY (cluster_id) - cluster_name (name(255)) 4.5. Table mocenter.vips (vips) Columns: - vip_id INT - ip_address LONGTEXT NULL - name LONGTEXT NULL - ipaddr_type INT NULL - interface LONGTEXT NULL - alias INT NULL - primary_id INT NULL - failover_id INT NULL - location_id INT NULL - cluster_id INT NULL - last_change_ts INT NULL - fo_sync_only INT NULL - fo_wait_sync INT NULL Foreign Keys: Indices: - PRIMARY (vip_id) - ip_address (ip_address(255)) 4.6. Table mocenter.servers (servers) Columns: - server_id INT - name LONGTEXT NULL - default_ip LONGTEXT NULL - os_user LONGTEXT NULL - cluster_id INT NULL - last_change_ts INT NULL - myenv_basedir LONGTEXT NULL Foreign Keys: Indices: - PRIMARY (server_id) - server_name (name(255)) 4.7. Table mocenter.users (users) Columns: - user_id INT - login_name LONGTEXT NULL - password_hash LONGTEXT NULL - email_address LONGTEXT NULL - first_name LONGTEXT NULL - last_name LONGTEXT NULL - mobile LONGTEXT NULL - role_id INT NULL Foreign Keys: Indices: - PRIMARY (user_id) - login_name (login_name(255)) 4.8. Table mocenter.moc_identifier (moc_identifier) Columns: - moc_identifier LONGTEXT Foreign Keys: Indices: - PRIMARY (moc_identifier(255)) 4.9. Table mocenter.checks (checks) Columns: - unit_id INT - type LONGTEXT NULL - name LONGTEXT - last_check_ts INT NULL - last_check_status LONGTEXT NULL - last_successful_check_ts INT NULL - last_successful_check_status LONGTEXT NULL Foreign Keys: Indices: - PRIMARY (unit_id, name(255)) - name_dc (name(255)) 4.10. Table mocenter.jobs (jobs) Columns: - job_id INT - name LONGTEXT NULL - server LONGTEXT NULL - pid INT NULL - start_ts INT NULL - status LONGTEXT NULL - check_interval INT NULL - last_check_ts INT NULL - end_ts INT NULL - error_code INT NULL - error_message LONGTEXT NULL - command LONGTEXT NULL Foreign Keys: Indices: - PRIMARY (job_id) II. Data Copy - `mocenter`.`moc_identifier` Succeeded : copied 1 of 1 rows from "mocenter"."moc_identifier" - `mocenter`.`clusters` Succeeded : copied 1 of 1 rows from "mocenter"."clusters" - `mocenter`.`jobs` Succeeded : copied 0 of 0 rows from "mocenter"."jobs" - `mocenter`.`users` Succeeded : copied 1 of 1 rows from "mocenter"."users" - `mocenter`.`vips` Succeeded : copied 1 of 1 rows from "mocenter"."vips" - `mocenter`.`versions` Succeeded : copied 2 of 2 rows from "mocenter"."versions" - `mocenter`.`servers` Succeeded : copied 2 of 2 rows from "mocenter"."servers" - `mocenter`.`checks` Succeeded : copied 46 of 46 rows from "mocenter"."checks" - `mocenter`.`licenses` Succeeded : copied 1 of 1 rows from "mocenter"."licenses" Conclusion

Migrating from SQLite to MySQL is very easy when using MySQL Workbench.

Taxonomy upgrade extras: focmmmigration

In my first Blog ever, I am going to cover the migration of a SQLite-Database to MySQL. The Tool used is MySQL-Workbench, which you can Download from the MySQL website. In this particular case, it is about the upgrade of mocenter 0.2 to 0.3.

In the Workbench on the right side, you have the button “Database Migration”. Once you click on it, the introduction to the migration wizard will show up.

Setting up source and target

On the bottom of the screen there is a button called “Start Migration”. Click it to get to the source selection.

On the first drop down menu choose “SQLite”. The menu will now change and give you the possibility to load a file. After you did this you can test the connection with the button on the bottom left, or go to the bottom right and click next.

You get a new menu, where you can choose your target of the database. You can use a stored connection or a new one. If you have no idea which connection type to use, TCP/IP usually works fine. Here I recommend you to test the connection, so you see if you can reach target. Then click next.

If the schema fetch does not return any error, you get to the schema selection.

There you have to select a target schema, before you can click next and go to fetch the source. Once you have done that too, click next once more.

Object selection

Now the database is ready to copy.

If you want to remove some of the tables from the migration, you can do this under “Show Selection”.

In case you see some warnings, you can ignore them.

Now click next and make sure there are no errors or failures, until you can select how to create the target database.

If you want to, you can create the SQL file to import the database structure somewhere else. I am just going to put it onto my server.

Clicking next after this step will create the database on the server. If you chose to create the SQL file, it would be created now as well.

Click next, when the creation is done, and you should get something, that looks like this picture. If you select a line, you will be shown the command that was executed.

This was the structure. Now to the data:

To transfer your data from the SQLite directly into the MySQL-Database, you can make an online copy. If you want to, or have to, do it from the command line you could also make a bash file.

If you click the option “Truncate target tables”, all the tables that already exist will be cleared of any data, so be careful using that checkbox.

Once complete, you should get report that looks somewhat like this.

------------------------------------------------------------------------------------ MySQL Workbench Migration Wizard Report Date: Sun Oct 18 17:36:53 2015 Source: SQLite 1.0.0 Target: MySQL 5.6.24 ------------------------------------------------------------------------------------ I. Migration 1. Summary Number of migrated schemas: 1 1. mocenter Source Schema: mocenter - Tables: 10 - Triggers: 0 - Views: 0 - Stored Procedures: 0 - Functions: 0 2. Migration Issues - versions warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - PRIMARY warning Truncated key column length for column from 0 to 255 - nodes warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - name_ui warning Truncated key column length for column from 0 to 255 - clusters warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - cluster_name warning Truncated key column length for column from 0 to 255 - vips warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - ip_address warning Truncated key column length for column from 0 to 255 - servers warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - server_name warning Truncated key column length for column from 0 to 255 - users warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - login_name warning Truncated key column length for column from 0 to 255 - moc_identifier warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - PRIMARY warning Truncated key column length for column from 0 to 255 - checks warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - PRIMARY warning Truncated key column length for column from 0 to 255 - name_dc warning Truncated key column length for column from 0 to 255 - jobs warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. 3. Object Creation Issues 4. Migration Details 4.1. Table mocenter.versions (versions) Columns: - version LONGTEXT - mr_version INT NULL - timestamp INT NULL Foreign Keys: Indices: - PRIMARY (version(255)) 4.2. Table mocenter.licenses (licenses) Columns: - license LONGTEXT NULL Foreign Keys: Indices: 4.3. Table mocenter.nodes (nodes) Columns: - node_id INT - name LONGTEXT NULL - last_change_ts INT NULL - status LONGTEXT NULL - node_type LONGTEXT NULL - hostname LONGTEXT NULL - basedir LONGTEXT NULL - datadir LONGTEXT NULL - my_cnf LONGTEXT NULL - port INT NULL - database_user LONGTEXT NULL - database_user_password LONGTEXT NULL - error_log LONGTEXT NULL - pid_file LONGTEXT NULL - read_only INT NULL - server_id INT NULL - role_id INT NULL - cluster_id INT NULL - master_id INT NULL Foreign Keys: Indices: - PRIMARY (node_id) - name_ui (name(255)) 4.4. Table mocenter.clusters (clusters) Columns: - cluster_id INT - name LONGTEXT NULL - last_change_ts INT NULL - type INT NULL Foreign Keys: Indices: - PRIMARY (cluster_id) - cluster_name (name(255)) 4.5. Table mocenter.vips (vips) Columns: - vip_id INT - ip_address LONGTEXT NULL - name LONGTEXT NULL - ipaddr_type INT NULL - interface LONGTEXT NULL - alias INT NULL - primary_id INT NULL - failover_id INT NULL - location_id INT NULL - cluster_id INT NULL - last_change_ts INT NULL - fo_sync_only INT NULL - fo_wait_sync INT NULL Foreign Keys: Indices: - PRIMARY (vip_id) - ip_address (ip_address(255)) 4.6. Table mocenter.servers (servers) Columns: - server_id INT - name LONGTEXT NULL - default_ip LONGTEXT NULL - os_user LONGTEXT NULL - cluster_id INT NULL - last_change_ts INT NULL - myenv_basedir LONGTEXT NULL Foreign Keys: Indices: - PRIMARY (server_id) - server_name (name(255)) 4.7. Table mocenter.users (users) Columns: - user_id INT - login_name LONGTEXT NULL - password_hash LONGTEXT NULL - email_address LONGTEXT NULL - first_name LONGTEXT NULL - last_name LONGTEXT NULL - mobile LONGTEXT NULL - role_id INT NULL Foreign Keys: Indices: - PRIMARY (user_id) - login_name (login_name(255)) 4.8. Table mocenter.moc_identifier (moc_identifier) Columns: - moc_identifier LONGTEXT Foreign Keys: Indices: - PRIMARY (moc_identifier(255)) 4.9. Table mocenter.checks (checks) Columns: - unit_id INT - type LONGTEXT NULL - name LONGTEXT - last_check_ts INT NULL - last_check_status LONGTEXT NULL - last_successful_check_ts INT NULL - last_successful_check_status LONGTEXT NULL Foreign Keys: Indices: - PRIMARY (unit_id, name(255)) - name_dc (name(255)) 4.10. Table mocenter.jobs (jobs) Columns: - job_id INT - name LONGTEXT NULL - server LONGTEXT NULL - pid INT NULL - start_ts INT NULL - status LONGTEXT NULL - check_interval INT NULL - last_check_ts INT NULL - end_ts INT NULL - error_code INT NULL - error_message LONGTEXT NULL - command LONGTEXT NULL Foreign Keys: Indices: - PRIMARY (job_id) II. Data Copy - `mocenter`.`moc_identifier` Succeeded : copied 1 of 1 rows from "mocenter"."moc_identifier" - `mocenter`.`clusters` Succeeded : copied 1 of 1 rows from "mocenter"."clusters" - `mocenter`.`jobs` Succeeded : copied 0 of 0 rows from "mocenter"."jobs" - `mocenter`.`users` Succeeded : copied 1 of 1 rows from "mocenter"."users" - `mocenter`.`vips` Succeeded : copied 1 of 1 rows from "mocenter"."vips" - `mocenter`.`versions` Succeeded : copied 2 of 2 rows from "mocenter"."versions" - `mocenter`.`servers` Succeeded : copied 2 of 2 rows from "mocenter"."servers" - `mocenter`.`checks` Succeeded : copied 46 of 46 rows from "mocenter"."checks" - `mocenter`.`licenses` Succeeded : copied 1 of 1 rows from "mocenter"."licenses" Conclusion

Migrating from SQLite to MySQL is very easy when using MySQL Workbench.

Taxonomy upgrade extras: focmmmigration

In my first Blog ever, I am going to cover the migration of a SQLite-Database to MySQL. The Tool used is MySQL-Workbench, which you can Download from the MySQL website. In this particular case, it is about the upgrade of mocenter 0.2 to 0.3.

In the Workbench on the right side, you have the button “Database Migration”. Once you click on it, the introduction to the migration wizard will show up.

Setting up source and target

On the bottom of the screen there is a button called “Start Migration”. Click it to get to the source selection.

On the first drop down menu choose “SQLite”. The menu will now change and give you the possibility to load a file. After you did this you can test the connection with the button on the bottom left, or go to the bottom right and click next.

You get a new menu, where you can choose your target of the database. You can use a stored connection or a new one. If you have no idea which connection type to use, TCP/IP usually works fine. Here I recommend you to test the connection, so you see if you can reach target. Then click next.

If the schema fetch does not return any error, you get to the schema selection.

There you have to select a target schema, before you can click next and go to fetch the source. Once you have done that too, click next once more.

Object selection

Now the database is ready to copy.

If you want to remove some of the tables from the migration, you can do this under “Show Selection”.

In case you see some warnings, you can ignore them.

Now click next and make sure there are no errors or failures, until you can select how to create the target database.

If you want to, you can create the SQL file to import the database structure somewhere else. I am just going to put it onto my server.

Clicking next after this step will create the database on the server. If you chose to create the SQL file, it would be created now as well.

Click next, when the creation is done, and you should get something, that looks like this picture. If you select a line, you will be shown the command that was executed.

This was the structure. Now to the data:

To transfer your data from the SQLite directly into the MySQL-Database, you can make an online copy. If you want to, or have to, do it from the command line you could also make a bash file.

If you click the option “Truncate target tables”, all the tables that already exist will be cleared of any data, so be careful using that checkbox.

Once complete, you should get report that looks somewhat like this.

------------------------------------------------------------------------------------ MySQL Workbench Migration Wizard Report Date: Sun Oct 18 17:36:53 2015 Source: SQLite 1.0.0 Target: MySQL 5.6.24 ------------------------------------------------------------------------------------ I. Migration 1. Summary Number of migrated schemas: 1 1. mocenter Source Schema: mocenter - Tables: 10 - Triggers: 0 - Views: 0 - Stored Procedures: 0 - Functions: 0 2. Migration Issues - versions warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - PRIMARY warning Truncated key column length for column from 0 to 255 - nodes warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - name_ui warning Truncated key column length for column from 0 to 255 - clusters warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - cluster_name warning Truncated key column length for column from 0 to 255 - vips warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - ip_address warning Truncated key column length for column from 0 to 255 - servers warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - server_name warning Truncated key column length for column from 0 to 255 - users warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - login_name warning Truncated key column length for column from 0 to 255 - moc_identifier warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - PRIMARY warning Truncated key column length for column from 0 to 255 - checks warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. - PRIMARY warning Truncated key column length for column from 0 to 255 - name_dc warning Truncated key column length for column from 0 to 255 - jobs warning Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL. 3. Object Creation Issues 4. Migration Details 4.1. Table mocenter.versions (versions) Columns: - version LONGTEXT - mr_version INT NULL - timestamp INT NULL Foreign Keys: Indices: - PRIMARY (version(255)) 4.2. Table mocenter.licenses (licenses) Columns: - license LONGTEXT NULL Foreign Keys: Indices: 4.3. Table mocenter.nodes (nodes) Columns: - node_id INT - name LONGTEXT NULL - last_change_ts INT NULL - status LONGTEXT NULL - node_type LONGTEXT NULL - hostname LONGTEXT NULL - basedir LONGTEXT NULL - datadir LONGTEXT NULL - my_cnf LONGTEXT NULL - port INT NULL - database_user LONGTEXT NULL - database_user_password LONGTEXT NULL - error_log LONGTEXT NULL - pid_file LONGTEXT NULL - read_only INT NULL - server_id INT NULL - role_id INT NULL - cluster_id INT NULL - master_id INT NULL Foreign Keys: Indices: - PRIMARY (node_id) - name_ui (name(255)) 4.4. Table mocenter.clusters (clusters) Columns: - cluster_id INT - name LONGTEXT NULL - last_change_ts INT NULL - type INT NULL Foreign Keys: Indices: - PRIMARY (cluster_id) - cluster_name (name(255)) 4.5. Table mocenter.vips (vips) Columns: - vip_id INT - ip_address LONGTEXT NULL - name LONGTEXT NULL - ipaddr_type INT NULL - interface LONGTEXT NULL - alias INT NULL - primary_id INT NULL - failover_id INT NULL - location_id INT NULL - cluster_id INT NULL - last_change_ts INT NULL - fo_sync_only INT NULL - fo_wait_sync INT NULL Foreign Keys: Indices: - PRIMARY (vip_id) - ip_address (ip_address(255)) 4.6. Table mocenter.servers (servers) Columns: - server_id INT - name LONGTEXT NULL - default_ip LONGTEXT NULL - os_user LONGTEXT NULL - cluster_id INT NULL - last_change_ts INT NULL - myenv_basedir LONGTEXT NULL Foreign Keys: Indices: - PRIMARY (server_id) - server_name (name(255)) 4.7. Table mocenter.users (users) Columns: - user_id INT - login_name LONGTEXT NULL - password_hash LONGTEXT NULL - email_address LONGTEXT NULL - first_name LONGTEXT NULL - last_name LONGTEXT NULL - mobile LONGTEXT NULL - role_id INT NULL Foreign Keys: Indices: - PRIMARY (user_id) - login_name (login_name(255)) 4.8. Table mocenter.moc_identifier (moc_identifier) Columns: - moc_identifier LONGTEXT Foreign Keys: Indices: - PRIMARY (moc_identifier(255)) 4.9. Table mocenter.checks (checks) Columns: - unit_id INT - type LONGTEXT NULL - name LONGTEXT - last_check_ts INT NULL - last_check_status LONGTEXT NULL - last_successful_check_ts INT NULL - last_successful_check_status LONGTEXT NULL Foreign Keys: Indices: - PRIMARY (unit_id, name(255)) - name_dc (name(255)) 4.10. Table mocenter.jobs (jobs) Columns: - job_id INT - name LONGTEXT NULL - server LONGTEXT NULL - pid INT NULL - start_ts INT NULL - status LONGTEXT NULL - check_interval INT NULL - last_check_ts INT NULL - end_ts INT NULL - error_code INT NULL - error_message LONGTEXT NULL - command LONGTEXT NULL Foreign Keys: Indices: - PRIMARY (job_id) II. Data Copy - `mocenter`.`moc_identifier` Succeeded : copied 1 of 1 rows from "mocenter"."moc_identifier" - `mocenter`.`clusters` Succeeded : copied 1 of 1 rows from "mocenter"."clusters" - `mocenter`.`jobs` Succeeded : copied 0 of 0 rows from "mocenter"."jobs" - `mocenter`.`users` Succeeded : copied 1 of 1 rows from "mocenter"."users" - `mocenter`.`vips` Succeeded : copied 1 of 1 rows from "mocenter"."vips" - `mocenter`.`versions` Succeeded : copied 2 of 2 rows from "mocenter"."versions" - `mocenter`.`servers` Succeeded : copied 2 of 2 rows from "mocenter"."servers" - `mocenter`.`checks` Succeeded : copied 46 of 46 rows from "mocenter"."checks" - `mocenter`.`licenses` Succeeded : copied 1 of 1 rows from "mocenter"."licenses" Conclusion

Migrating from SQLite to MySQL is very easy when using MySQL Workbench.