Agrégateur de flux

The FOSDEM 2016 in Brussels (Belgium) January 29/30 is over and was very interesting and IMHO a big success.

For all those who could not participate at FOSDEM 2016 our presentation slides about PERFORMANCE_SCHEMA and sys schema are available here:

PERFORMANCE_SCHEMA and sys schema - What can we do with it? (PDF, 406 kbyte)

Taxonomy upgrade extras: sysperformance_schemaslidespresentation

The FOSDEM 2016 in Brussels (Belgium) January 29/30 is over and was very interesting and IMHO a big success.

For all those who could not participate at FOSDEM 2016 our presentation slides about PERFORMANCE_SCHEMA and sys schema are available here:

PERFORMANCE_SCHEMA and sys schema - What can we do with it? (PDF, 406 kbyte)

Taxonomy upgrade extras: sysperformance_schemaslides

FromDual has the pleasure to announce the release of the new version 0.3 of the FromDual Ops Center for MySQL and MariaDB.

The FromDual Ops Center for MySQL and MariaDB (focmm) is an application for DBA's and system administrators to manage MySQL and MariaDB database farms.

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks.

More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center can be downloaded here.

In the inconceivable case that you find a bug in the FromDual Ops Center please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.2 to 0.3

An upgrade path from Ops-Center v0.2 to v0.3 is currently not supported. Please also check Upgrading.

Changes in Ops Center 0.3

• Ops Center repository database was changed from Sqlite to MySQL/MariaDB.
• Structure and schema comparison introduced.
• Variables comparison introduced.
• Processlist tool introduced.
• VIP is checked over crontab now.
• Debugging facility improved.
• Some configuration rules/checks implemented.
• User management implemented for multiple-instances.
• Clone user implemented.
• Database backup implemented.
• Jab handling implemented.
• Blocking backup implemented for MyISAM tables.
• Skip Slave with GTID works now.

Taxonomy upgrade extras: OperationsreleaseBackupfailoverfocmmFromDual Ops Center

FromDual has the pleasure to announce the release of the new version 0.3 of the FromDual Ops Center for MySQL and MariaDB.

The FromDual Ops Center for MySQL and MariaDB (focmm) is an application for DBA's and system administrators to manage MySQL and MariaDB database farms.

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks.

More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center can be downloaded here.

In the inconceivable case that you find a bug in the FromDual Ops Center please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.2 to 0.3

An upgrade path from Ops-Center v0.2 to v0.3 is currently not supported. Please also check Upgrading.

Changes in Ops Center 0.3

• Ops Center repository database was changed from Sqlite to MySQL/MariaDB.
• Structure and schema comparison introduced.
• Variables comparison introduced.
• Processlist tool introduced.
• VIP is checked over crontab now.
• Debugging facility improved.
• Some configuration rules/checks implemented.
• User management implemented for multiple-instances.
• Clone user implemented.
• Database backup implemented.
• Jab handling implemented.
• Blocking backup implemented for MyISAM tables.
• Skip Slave with GTID works now.

Taxonomy upgrade extras: operationOperationsreleaseBackupfailover

FromDual has the pleasure to announce the release of the new version 0.3 of the FromDual Ops Center for MySQL and MariaDB.

The FromDual Ops Center for MySQL and MariaDB (focmm) is an application for DBA's and system administrators to manage MySQL and MariaDB database farms.

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks.

More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center can be downloaded here.

In the inconceivable case that you find a bug in the FromDual Ops Center please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.2 to 0.3

An upgrade path from Ops-Center v0.2 to v0.3 is currently not supported. Please also check Upgrading.

Changes in Ops Center 0.3

• Ops Center repository database was changed from Sqlite to MySQL/MariaDB.
• Structure and schema comparison introduced.
• Variables comparison introduced.
• Processlist tool introduced.
• VIP is checked over crontab now.
• Debugging facility improved.
• Some configuration rules/checks implemented.
• User management implemented for multiple-instances.
• Clone user implemented.
• Database backup implemented.
• Jab handling implemented.
• Blocking backup implemented for MyISAM tables.
• Skip Slave with GTID works now.

Taxonomy upgrade extras: operationOperationsreleaseBackupfailover

Das DOAG Regionaltreffen Freiburg/Südbaden vom 26. Januar 2016 zum Thema MySQL hat im gewohnten Rahmen statt gefunden.

Für all diejenigen, die nicht am Anlass teilnehmen konnten sind unsere Vortragsfolien hier verfügbar: MySQL für Oracle DBA's (PDF, 1.7 Mbyte) und MySQL Backup/Recovery (PDF, 348 kbyte).

Taxonomy upgrade extras: doagBackupRecoveryOracleDBAvortragfolienslidespresentation

Das DOAG Regionaltreffen Freiburg/Südbaden vom 26. Januar 2016 zum Thema MySQL hat im gewohnten Rahmen statt gefunden.

Für all diejenigen, die nicht am Anlass teilnehmen konnten sind unsere Vortragsfolien hier verfügbar: MySQL für Oracle DBA's (PDF, 1.7 Mbyte) und MySQL Backup/Recovery (PDF, 348 kbyte).

Taxonomy upgrade extras: doagBackupRecoveryOracleDBAvortragfolienslidespresentation

Das DOAG Regionaltreffen Freiburg/Südbaden vom 26. Januar 2016 zum Thema MySQL hat im gewohnten Rahmen statt gefunden.

Für all diejenigen, die nicht am Anlass teilnehmen konnten sind unsere Vortragsfolien hier verfügbar: MySQL für Oracle DBA's (PDF, 1.7 Mbyte) und MySQL Backup/Recovery (PDF, 348 kbyte).

Taxonomy upgrade extras: doagBackupRecoveryOracleDBAvortragfolien

FromDual has the pleasure to announce the release of the new version 1.3.0 of its popular MySQL, Galera Cluster, MariaDB and Percona Server multi-instance environment MyEnv.

The new MyEnv can be downloaded here.

In the inconceivable case that you find a bug in the MyEnv please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 1.1.x or higher to 1.3.0 # cd ${HOME}/product # tar xf /download/myenv-1.3.0.tar.gz # rm -f myenv # ln -s myenv-1.3.0 myenv

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

cd ${HOME}/product/myenv ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/
Changes in MyEnv 1.3.0 MyEnv

• NDB Cluster stuff was removed and cleaned-up.
• my.cnf template was slightly adapted to new requirements (query_cache_size, innodb_buffer_pool_instances, binlog_stmt_cache_size, wsrep_sst_method and wsrep_sst_auth).
• Cgroups stuff was fixed and made more verbose in case of configuration errors.
• Cgroups template (tpl/cgroups.conf) was extended.
• Some warnings made more clear.
• Too verbose error message in case of missing my.cnf was redirected to debug logging.

MyEnv Installer

• Preparation work for Mac OSX was done.
• Installation deletion can be automatized now.
• Installer made ready for 5.7
• Tags angel and default are not attached to each section any more.
• Schema sys was added to hideschema variable

MyEnv Utilities

• Script insert_test.sh host tag added.
• ORDER BY added to GROUP BY (in query_bench.php) to make it working the same in the future.
• rc bug fixed in block_galera_node.sh

For subscriptions of commercial use of MyEnv please get in contact with us.

Taxonomy upgrade extras: MyEnvoperationMySQL Operationsmulti instanceconsolidationtestingreleasecloudcgroupscontainer

FromDual has the pleasure to announce the release of the new version 1.3.0 of its popular MySQL, Galera Cluster, MariaDB and Percona Server multi-instance environment MyEnv.

The new MyEnv can be downloaded here.

In the inconceivable case that you find a bug in the MyEnv please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 1.1.x or higher to 1.3.0 # cd ${HOME}/product # tar xf /download/myenv-1.3.0.tar.gz # rm -f myenv # ln -s myenv-1.3.0 myenv

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

cd ${HOME}/product/myenv ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/
Changes in MyEnv 1.3.0 MyEnv

• NDB Cluster stuff was removed and cleaned-up.
• my.cnf template was slightly adapted to new requirements (query_cache_size, innodb_buffer_pool_instances, binlog_stmt_cache_size, wsrep_sst_method and wsrep_sst_auth).
• Cgroups stuff was fixed and made more verbose in case of configuration errors.
• Cgroups template (tpl/cgroups.conf) was extended.
• Some warnings made more clear.
• Too verbose error message in case of missing my.cnf was redirected to debug logging.

MyEnv Installer

• Preparation work for Mac OSX was done.
• Installation deletion can be automatized now.
• Installer made ready for 5.7
• Tags angel and default are not attached to each section any more.
• Schema sys was added to hideschema variable

MyEnv Utilities

• Script insert_test.sh host tag added.
• ORDER BY added to GROUP BY (in query_bench.php) to make it working the same in the future.
• rc bug fixed in block_galera_node.sh

For subscriptions of commercial use of MyEnv please get in contact with us.

Taxonomy upgrade extras: myenvoperationMySQL Operationsmulti instanceconsolidationtestingupgradereleasecloudcgroupscontainer

FromDual has the pleasure to announce the release of the new version 1.3.0 of its popular MySQL, Galera Cluster, MariaDB and Percona Server multi-instance environment MyEnv.

The new MyEnv can be downloaded here.

In the inconceivable case that you find a bug in the MyEnv please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 1.1.x or higher to 1.3.0 # cd ${HOME}/product # tar xf /download/myenv-1.3.0.tar.gz # rm -f myenv # ln -s myenv-1.3.0 myenv

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

cd ${HOME}/product/myenv ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/
Changes in MyEnv 1.3.0 MyEnv

• NDB Cluster stuff was removed and cleaned-up.
• my.cnf template was slightly adapted to new requirements (query_cache_size, innodb_buffer_pool_instances, binlog_stmt_cache_size, wsrep_sst_method and wsrep_sst_auth).
• Cgroups stuff was fixed and made more verbose in case of configuration errors.
• Cgroups template (tpl/cgroups.conf) was extended.
• Some warnings made more clear.
• Too verbose error message in case of missing my.cnf was redirected to debug logging.

MyEnv Installer

• Preparation work for Mac OSX was done.
• Installation deletion can be automatized now.
• Installer made ready for 5.7
• Tags angel and default are not attached to each section any more.
• Schema sys was added to hideschema variable

MyEnv Utilities

• Script insert_test.sh host tag added.
• ORDER BY added to GROUP BY (in query_bench.php) to make it working the same in the future.
• rc bug fixed in block_galera_node.sh

For subscriptions of commercial use of MyEnv please get in contact with us.

Taxonomy upgrade extras: myenvoperationMySQL Operationsmulti instanceconsolidationtestingupgradereleasecloudcgroupscontainer

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

Taxonomy upgrade extras: GTIDreplicationmulti-source

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

Taxonomy upgrade extras: GTIDreplicationmulti-source replication

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

Das DOAG Regionaltreffen Nürnberg / Franken vom 17. Dezember 2015 mit dem Thema MySQL für Oracle DBAs war ein voller Erfolg. Die knapp 20 Teilnehmer haben sich rege eingebracht und ihre Erfahrungen kund getan.

Für all diejenigen, die nicht am Anlass teilnehmen konnten sind unsere Vortragsfolien hier verfügbar: MySQL Beispiele aus der Praxis - Wie setzen Kunden MySQL ein? (PDF, 3.3 Mbyte).

Taxonomy upgrade extras: doag

Das DOAG Regionaltreffen Nürnberg / Franken vom 17. Dezember 2015 mit dem Thema MySQL für Oracle DBAs war ein voller Erfolg. Die knapp 20 Teilnehmer haben sich rege eingebracht und ihre Erfahrungen kund getan.

Für all diejenigen, die nicht am Anlass teilnehmen konnten sind unsere Vortragsfolien hier verfügbar: MySQL Beispiele aus der Praxis - Wie setzen Kunden MySQL ein? (PDF, 3.3 Mbyte).

Taxonomy upgrade extras: doag

Das DOAG Regionaltreffen Nürnberg / Franken vom 17. Dezember 2015 mit dem Thema MySQL für Oracle DBAs war ein voller Erfolg. Die knapp 20 Teilnehmer haben sich rege eingebracht und ihre Erfahrungen kund getan.

Für all diejenigen, die nicht am Anlass teilnehmen konnten sind unsere Vortragsfolien hier verfügbar: MySQL Beispiele aus der Praxis - Wie setzen Kunden MySQL ein? (PDF, 3.3 Mbyte).

Die DOAG Konferenz und Ausstellung 2015 ist erfolgreich vorüber.

Das FromDual Cheat Sheet für MySQL und MariaDB war ein riesen Erfolg. Haben Sie es versäumt, sich eins zu sicher, schicken Sie uns einfach ein eMail mit Ihrer Anschrift und wir schicken Ihnen Ihr persönliches Cheat Sheet zu.

Zahlreiche interessante Gespräche mit potentiellen und aktuellen Kunden haben statt gefunden. Viel Informationen wurden auf- und Feedback entgegen genommen.

Diese werden in den nächsten MyEnv-Cloud Release und in den übernächsten FromDual Ops Center Release fliessen um Ihre MySQL und MariaDB Cloud-Datenbanken noch besser verwalten zu können.

Bis es soweit ist, können Sie sich schon mal die Folien zu unseren MySQL Vorträgen ansehen:

• MySQL Performance Tuningfür Oracle-DBA's (PDF, 680 kbyte)
• Der Datenbank-Backup ist gemacht - was nun ? (PDF, 415 kbyte)
• MySQL-Server im Teamwork - Replikation und Cluster (PDF, 456 kbyte)
• Weltweite Produktionsdatenverwaltung mit MySQL-Replikation (PDF, 625 kbyte)

Taxonomy upgrade extras: folienslidesvortragpresentation

Die DOAG Konferenz und Ausstellung 2015 ist erfolgreich vorüber.

Das FromDual Cheat Sheet für MySQL und MariaDB war ein riesen Erfolg. Haben Sie es versäumt, sich eins zu sicher, schicken Sie uns einfach ein eMail mit Ihrer Anschrift und wir schicken Ihnen Ihr persönliches Cheat Sheet zu.

Zahlreiche interessante Gespräche mit potentiellen und aktuellen Kunden haben statt gefunden. Viel Informationen wurden auf- und Feedback entgegen genommen.

Diese werden in den nächsten MyEnv-Cloud Release und in den übernächsten FromDual Ops Center Release fliessen um Ihre MySQL und MariaDB Cloud-Datenbanken noch besser verwalten zu können.

Bis es soweit ist, können Sie sich schon mal die Folien zu unseren MySQL Vorträgen ansehen:

• MySQL Performance Tuningfür Oracle-DBA's (PDF, 680 kbyte)
• Der Datenbank-Backup ist gemacht - was nun ? (PDF, 415 kbyte)
• MySQL-Server im Teamwork - Replikation und Cluster (PDF, 456 kbyte)
• Weltweite Produktionsdatenverwaltung mit MySQL-Replikation (PDF, 625 kbyte)

Taxonomy upgrade extras: folienslidesvortragpresentation

Die DOAG Konferenz und Ausstellung 2015 ist erfolgreich vorüber.

Das FromDual Cheat Sheet für MySQL und MariaDB war ein riesen Erfolg. Haben Sie es versäumt, sich eins zu sicher, schicken Sie uns einfach ein eMail mit Ihrer Anschrift und wir schicken Ihnen Ihr persönliches Cheat Sheet zu.

Zahlreiche interessante Gespräche mit potentiellen und aktuellen Kunden haben statt gefunden. Viel Informationen wurden auf- und Feedback entgegen genommen.

Diese werden in den nächsten MyEnv-Cloud Release und in den übernächsten FromDual Ops Center Release fliessen um Ihre MySQL und MariaDB Cloud-Datenbanken noch besser verwalten zu können.

Bis es soweit ist, können Sie sich schon mal die Folien zu unseren MySQL Vorträgen ansehen:

• MySQL Performance Tuningfür Oracle-DBA's (PDF, 680 kbyte)
• Der Datenbank-Backup ist gemacht - was nun ? (PDF, 415 kbyte)
• MySQL-Server im Teamwork - Replikation und Cluster (PDF, 456 kbyte)
• Weltweite Produktionsdatenverwaltung mit MySQL-Replikation (PDF, 625 kbyte)