Jan
18
2019
--

Replication Manager Works with MariaDB

Complex multi-cluster replication topology

Some time ago I wrote a script to manage asynchronous replication links between Percona XtraDB clusters. The original post can be found here. The script worked well with Percona XtraDB Cluster but it wasn’t working well with MariaDB®.  Finally, the replication manager works with MariaDB.

First, let’s review the purpose of the script. Managing replication links between Galera based clusters is a tedious task. There are many potential slaves and many potential masters. Furthermore, each replication link must have only a single slave. Just try to imagine how you would maintain the following replication topology:

A complex replication topology

The above topology consists of five clusters and four master-to-master links. The replication manager can easily handle this topology. Of course, it is not a fix to the limitations of asynchronous replication. You must make sure your writes are replication safe. You could want, for example, a global user list or to centralize some access logs. Just to refresh memories, here are some of the script highlights:

  • Uses the Galera cluster for Quorum
  • Configurable, arbitrarily complex topologies
  • The script stores the topology in database tables
  • Elects slaves automatically
  • Monitors replication links
  • Slaves can connect to a list of potential masters

As you probably know, MariaDB has a different GTID implementation and syntax for the multi-source replication commands. I took some time to investigate why the script was failing and fixed it. Now, provided you are using MariaDB 10.1.4+ with GTIDs, the replication manager works fine.

You can found the script here. Be aware that although I work for Percona, the script is not officially supported by Percona.

Jun
20
2018
--

Webinar Thu 6/21: How to Analyze and Tune MySQL Queries for Better Performance

database query tuning

database query tuningPlease join Percona’s MySQL Database Administrator, Brad Mickel as he presents How to Analyze and Tune MySQL Queries for Better Performance on Thursday, June 21st, 2018, at 10:00 AM PDT (UTC-7) / 1:00 PM EDT (UTC-4).

 

Query performance is essential in making any application successful. In order to finely tune your queries you first need to understand how MySQL executes them, and what tools are available to help identify problems.

In this session you will learn:

  1. The common tools for researching problem queries
  2. What an Index is, and why you should use one
  3. Index limitations
  4. When to rewrite the query instead of just adding a new index
Register Now

 

Brad Mickel

MySQL DBA

Bradley began working with MySQL in 2013 as part of his duties in healthcare billing. After 3 years in healthcare billing he joined Percona as part of the bootcamp process. After the bootcamp he has served as a remote database administrator on the Atlas team for Percona Managed Services.

The post Webinar Thu 6/21: How to Analyze and Tune MySQL Queries for Better Performance appeared first on Percona Database Performance Blog.

May
24
2018
--

Using dbdeployer to manage MySQL, Percona Server and MariaDB sandboxes

dbdeployer by Giuseppe Maxia

Some years ago, Peter Z wrote a blogpost about using MySQL Sandbox to deploy multiple server versions. Last February, Giuseppe  introduced us to its successor: dbdeployer. In this blogpost we will demonstrate how to use it. There is a lot of information in Giuseppe’s post, so head there if you want a deeper dive.

First step is to install it, which is really easy to do now since it’s developed in Go, and standalone executables are provided. You can get the latest version here.

shell> wget https://github.com/datacharmer/dbdeployer/releases/download/1.5.0/dbdeployer-1.5.0.linux.tar.gz
shell> tar xzf dbdeployer-1.5.0.linux.tar.gz
shell> mv dbdeployer-1.5.0.linux ~/bin/dbdeployer

If you have your ~/bin/ directory in the path, you should now be able to run dbdeployer commands.

dbdeployer by Giuseppe Maxia

Let’s start with deploying a latest version vanilla MySQL sandbox.

In the Support Team, we extensively use MySQL Sandbox (the predecessor to dbdeployer) to easily run different flavours and versions of MySQL so that we can test with the same versions our customers present us with. We store MySQL binaries in /opt/, so we can all share them and avoid wasting disk space on duplicated binaries.

The first step to using dbdeployer is getting the binary we want to run, and then unpacking it into the binaries directory.

shell> wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
shell> dbdeployer --sandbox-binary=/opt/mysql/ unpack mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz

This command will extract and move the files to the appropriate directory, which in this case is under /opt/mysql/ as overridden with the --sandbox-binary argument, so we can use them with the deploy command.

Standalone

To create a new standalone MySQL sandbox with the newly extracted binary, we can use the following command.

shell> dbdeployer --sandbox-binary=/opt/mysql/ deploy single 8.0.11
Creating directory /home/vagrant/sandboxes
Database installed in $HOME/sandboxes/msb_8_0_11
run 'dbdeployer usage single' for basic instructions'
.. sandbox server started

You can read the dbdeployer usage output to have even more information on how the tool works. Next, let’s connect to it.

shell> cd sandboxes/msb_8_0_11/
shell> ./use
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.11 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql [localhost] {msandbox} ((none)) > select @@version, @@port;
+-----------+--------+
| @@version | @@port |
+-----------+--------+
| 8.0.11    | 8011 |
+-----------+--------+
1 row in set (0.00 sec)

And that was it! When creating the new instance, dbdeployer will try to use the same port as the version numbers concatenated. If that port is in use, it will try another one, or we can manually override it with the --port argument.

Replication

We can also easily setup a replication environment with just one command.

shell> dbdeployer --sandbox-binary=/opt/mariadb/ deploy replication 10.2.15
Installing and starting master
. sandbox server started
Installing and starting slave1
. sandbox server started
Installing and starting slave2
. sandbox server started
$HOME/sandboxes/rsandbox_10_2_15/initialize_slaves
initializing slave 1
initializing slave 2
Replication directory installed in $HOME/sandboxes/rsandbox_10_2_15
run 'dbdeployer usage multiple' for basic instructions'

Again, you should run the recommended command to get more insight into what can be done. We can use the ./m script to connect to the master, and ./s1 to connect to the first slave. The ./use_all* scripts can come in handy to run commands in many servers at a time.

Multiple sandboxes

Finally, we will see how to create multiple sandboxes with the same version at the same time.

shell> dbdeployer --sandbox-binary=/opt/percona_server/ deploy multiple 5.7.21
Installing and starting node 1
. sandbox server started
Installing and starting node 2
. sandbox server started
Installing and starting node 3
. sandbox server started
multiple directory installed in $HOME/sandboxes/multi_msb_5_7_21
run 'dbdeployer usage multiple' for basic instructions'

This could be useful for setting up environments that are not already covered by the tool, like Galera clusters or semi-sync replication. With this approach, we will at least have a base to start from, and then can use our own custom scripts. dbdeployer now has templates, which would allow extending functionality to support this, if needed. I have not yet tried to do so, but sounds like an interesting project for the future! Let me know if you would be interested in reading more about it.

The post Using dbdeployer to manage MySQL, Percona Server and MariaDB sandboxes appeared first on Percona Database Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com