Author: MariaDB

Use Cases for MariaDB Data Versioning

Use Cases for MariaDB Data Versioning
rasmusjohansson

Thu, 07/05/2018 – 19:54

Working in software development, versioning of code is something that we’ve often taken for granted. Task definitions and bug descriptions are preferably also managed by a system that versions every change. On top of this, we use a lot of documents for designing, documenting and managing our development cycles. For example, some of the tools we use are Jira, Google Docs and our own Knowledge Base to accomplish these things, which all provide versioning support. In MariaDB Server 10.3, we’ve introduced an elegant and easy way for data versioning, called System-Versioned Tables. Let’s look at what it can be used for.

A Look Back at Data for GDPR and PCI DSS Compliance

The General Data Protection Regulation (GDPR) is now enforced by the European Union (EU). All companies collecting user data in the EU have to comply with the GDPR rules. In addition to the daily questions and statements coming over email asking you to agree to new terms because of GDPR, the companies also have to store personal and private data in a way that fulfills the criteria of GDPR.

Card payments also have their own rules. There are standards like the Payment Card Industry Data Security Standard (PCI DSS), which are followed by banks and other online businesses. 1) What happened when, 2) by whom and 3) what did the data look like before and after? In MariaDB Server, the MariaDB Audit plugin is there for dealing with 1) and 2). It can also be used for 3) by looking in the audit logs on changes made, but it doesn’t give you the full data for how it looked before and after. With the newly released System-Versioned Tables this is possible. Let’s say that payment card information is stored in a database table. By turning on versioning for that table, all changes will create a new version of the row(s) affected by the change. The rows will also be time stamped, which means that you can query the row to see what it looked like before.

Handling Personal Data in Registries

When you think about versioning, one thing that comes to mind are registries of all sorts, which is the domain of GDPR when it comes to handling personal data. There are many types of personal data and one important to all of us is healthcare data, for example the patient registries of hospitals. In these registries versioning is of great importance to keep track of patients’ health history and related information such as medication. Other personal data registers are civil registers, tax registers, school and student registers and employee registers. The list is endless.

Rapidly Changing Data Sets

All of the above mentioned examples with data versioning applied in one way or another can be seen as slowly changing data. What I mean is that, although the systems can be huge and the total amount of transactions happening enormous, each piece of data doesn’t change that often. For example, my information in the civil register doesn’t change every second. But what if we have rapidly changing data such as the share rates at a stock exchange or tracking vehicle data for a shipping company. In these cases, we can make use of MariaDB’s data versioning.

Creating applications or software for the above purposes and having a database that provides data versioning out-of-the-box will lead to easier design, less customization and a more secure solutions.

Step-by-step Example

I’ll end with a GDPR example. I have a newsletter with subscribers and want to make sure that I always know when and what has happened to the subscriber data. I create a table in the database for the purpose and turn on versioning for the table.

CREATE TABLE Subscriber (
SubscriberId int(11) NOT NULL AUTO_INCREMENT,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Newsletter bit NOT NULL,
PRIMARY KEY (SubscriberId)
) ENGINE=InnoDB WITH SYSTEM VERSIONING;

I insert myself as subscriber.

INSERT INTO Subscriber (FirstName, LastName, Newsletter) VALUES (‘Rasmus’, ‘Johansson’, 1);

I then try to add a column to the table.

ALTER TABLE Subscriber ADD COLUMN Gender char(1) NULL;
ERROR 4119 (HY000): Not allowed for system-versioned `Company`.`Subscriber`. Change @@system_versioning_alter_history to proceed with ALTER.

It results in the above error, because changing a versioned table is not permitted by default. I turn on the possibility to change the table and then the ALTER succeeds.

SET @@system_versioning_alter_history = 1;
ALTER TABLE Subscriber ADD COLUMN Gender char(1) NULL;
Query OK, 1 row affected (0.17 sec)

I also want a constraint on the new column.

ALTER TABLE Subscriber ADD CONSTRAINT con_gender CHECK (Gender in (‘f’,’m’));

Then I do a couple of updates in the table.

UPDATE Subscriber SET Newsletter = 0 WHERE SubscriberId = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0

UPDATE Subscriber SET Gender = ‘m’ WHERE SubscriberId = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0

Finally, I delete the row in the table:

DELETE FROM Subscriber WHERE SubscriberId = 1;

If we ask for the rows in the table, including the old versions we get the following.

SELECT *, ROW_START, ROW_END FROM Subscriber FOR SYSTEM_TIME ALL;
+————–+———–+———–+————+——–+—————————-+—————————-+
| SubscriberId | FirstName | LastName | Newsletter | Gender | ROW_START | ROW_END |
+————–+———–+———–+————+——–+—————————-+—————————-+
| 1 | Rasmus | Johansson | # | NULL | 2018-06-08 10:57:36.982721 | 2018-06-08 11:14:07.654996 |
| 1 | Rasmus | Johansson | | NULL | 2018-06-08 11:14:07.654996 | 2018-06-08 11:15:05.971761 |
| 1 | Rasmus | Johansson | | NULL | 2018-06-08 11:15:05.971761 | 2018-06-08 11:15:28.459109 |
| 1 | Rasmus | Johansson | | m | 2018-06-08 11:15:28.459109 | 2038-01-19 03:14:07.999999 |
+————–+———–+———–+————+——–+—————————-+—————————-+

Even though I deleted the row, I get four old versions of the row. All this was handled by the database. The only thing I had to do was to turn on versioning for the table. What will you do with MariaDB’s System Versioned-Tables? We’d love to hear from you!

How to
MariaDB Releases

Login
or
Register
to post comments

ALTER TABLE Improvements in MariaDB Server 10.3

ALTER TABLE Improvements in MariaDB Server 10.3
Marko Mäkelä

Thu, 05/31/2018 – 08:10

MariaDB Server 10.3.7 (the first Generally Available release in the series) includes some ALTER TABLE improvements that are worth mentioning. Last October, I wrote about the Instant ADD COLUMN feature that was introduced in the 10.3.2 alpha release. The effort to support instant ALTER TABLE in MariaDB comes from a collaboration with ServiceNow. The first part of that, instant ADD COLUMN, was brainstormed in April 2017 by engineers from MariaDB Corporation, Alibaba and Tencent. A prototype was first developed by Vin Chen (陈福荣) from the Tencent Game DBA Team and was later refined by our team for the MariaDB version.

Part of the original plan was to introduce syntax for ALTER TABLE…ALGORITHM=INSTANT in order to be able to give a guarantee that the requested operation will be performed instantly, or not at all. This was finally implemented in MariaDB Server 10.3.7. We also introduced the keyword ALGORITHM=NOCOPY, which will refuse an operation if the table would be rebuilt.

Example

CREATE TABLE t(id INT PRIMARY KEY, u INT UNSIGNED NOT NULL UNIQUE)
ENGINE=InnoDB;
INSERT INTO t(id,u) VALUES(1,1),(2,2),(3,3);

SET alter_algorithm=instant;
ALTER TABLE t ADD COLUMN d DATETIME DEFAULT current_timestamp();
–error ER_ALTER_OPERATION_NOT_SUPPORTED
# There is no instant DROP COLUMN yet
ALTER TABLE t DROP COLUMN u;
–error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t DROP COLUMN u, ALGORITHM=NOCOPY;
SET alter_algorithm=default;
ALTER TABLE t DROP COLUMN u;

The example illustrates a new configuration parameter alter_algorithm. A DBA could set it globally in the MariaDB Server configuration to NOCOPY in order to prevent expensive ALTER TABLE statements from being executed by mistake.

The ALGORITHM=INPLACE syntax, which was added in MariaDB Server 10.0, can misleadingly suggest that no copying ever takes place. However, until the efforts to implement instant schema changes (instant DROP COLUMN and others being worked on for upcoming MariaDB Server releases), the inplace operation often did involve copying, potentially allocating quite a bit of extra space, not only for the new copy of the table, but also for pre-sorting the data and for logging concurrent modifications to the table (ALTER ONLINE TABLE). With the ALGORITHM=INSTANT and ALGORITHM=NOCOPY clauses, which represent proper subsets of ALGORITHM=INPLACE, will are clarifying the situation.

Imitation is the sincerest form of flattery

We’re happy to hear that MySQL 8.0 will add support for instant ADD COLUMN that is based on our work. But you don’t have to wait to try it.

Try it out

MariaDB Server 10.3.7 was announced as GA on May 25, 2018. Download MariaDB TX 3.0, which includes MariaDB Server 10.3.7 to upgrade your current server and immediately get the benefit of instant ADD COLUMN for your old InnoDB tables.

Note that if you need to export InnoDB data files to older servers than MariaDB Server 10.3, you must rebuild the table first: ALTER TABLE t FORCE;

Big Data
DBA
Developer
InnoDB
MariaDB Releases

Perhaps the most user-visible InnoDB changes in the MariaDB Server 10.3.7 GA release are Instant ADD COLUMN for InnoDB tables, the new parameter alter_algorithm and the clauses ALGORITHM=INSTANT and ALGORITHM=NOCOPY that can protect the database from executing costly ALTER TABLE operations taking hours.

Upgrade to MariaDB Server 10.3.7, and enjoy the instant ADD COLUMN with your old InnoDB data files.

Login
or
Register
to post comments

What’s New in MariaDB Server 10.3

What’s New in MariaDB Server 10.3
maxmether

Fri, 05/25/2018 – 18:52

We are happy to announce the general availability (GA) of MariaDB Server 10.3! This release is a big milestone for the development of MariaDB Server and is the result of a huge effort by the development team and contributors – thanks to everyone involved! With our previous major release of MariaDB Server 10.2 last year, we started a journey of adding more enterprise-grade features to better close the gap with proprietary databases. With MariaDB Server 10.3 we take a huge leap on that journey by being the first enterprise open source database to add features like temporal data processing (through system versioning) as well as compatibility with Oracle sequences and Oracle PL/SQL. At the same time, we want to stay true to our open source and innovative roots by adding support for new storage engines to be able to more easily adapt to different workloads and different hardware available to users. This path allows us to adapt quickly to an ever-changing landscape where new innovations are being created at a constantly accelerated pace. This is our greatest release yet and, with this release, we want to put our stake in the Enterprise database category.

The key enhancements of MariaDB Server 10.3 can be put in the following categories:

Temporal data processing (system-versioned tables)

Oracle compatibility features

Purpose-built storage engines

 

Temporal Data Processing

Temporal data processing through our system versioning feature is one of the more exciting additions in the MariaDB Server 10.3 release. With system versioning, the database will keep track of all changes made to every row in the table. The old versions of the rows are not visible through normal query syntax, but by using a special syntax you can access all of the old versions of the row. This capability lends itself to a large number of use cases, anything from auditing and forensics (finding the exact point-in-time result set from a suspicious query executed some time ago) to things like analyzing changes in your data, comparing customer preferences year to year and a multitude of other possibilities. This feature can be turned on per table and the history can also be deleted periodically so that your table doesn’t grow indefinitely. The use cases are both exciting and endless! For more information on system versioning read our manual or this blog on automatic data versioning.

 

Oracle Compatibility

As the demand for MariaDB Server has increased in larger enterprises we have also seen a need for features that are readily available in proprietary databases. In order for MariaDB to be easier to use for DBAs and skilled database engineers from other products, we wanted to add as much compatibility as possible.

In MariaDB Server 10.3, we added a new stored routine syntax in addition to the already existing MariaDB SQL/PSM syntax. We now support MariaDB SQL/PL which is a syntax designed to be compatible with Oracle PL/SQL. This way, migrating existing applications is a lot easier and existing skills can be used without complex retraining. In the process we also added several new constructs to our stored procedure support like new ROW data types.

The new syntax isn’t the only new compatibility feature, we also added sequences in order to have a more flexible way of creating unique primary keys than the already existing auto_increment feature. This feature is fully compatible with Oracle sequences. Learn more about how to use sequences in this blog post. Together with features added previously (like window functions, common table expressions (CTEs), etc.) we now have a deep set of enterprise-grade features that can tackle any type of application need.

 

Purpose-Built Storage Engines

At MariaDB, we believe in using the right tool for the right trade. However, we don’t feel that you need to change everything in order to achieve that. We have a unique architecture with pluggable storage engines that allows the user to adapt the database to the use case and workload without changing the main characteristics and features. We believe that this flexibility serves the best interest of the user and we will work on further advancing this with future versions of MariaDB. This architecture will enable both the community and our team to innovate further by adding storage engines designed for new hardware and new use cases. In MariaDB Server 10.3, we introduce two new storage engines that are declared stable, MyRocks and Spider.

MyRocks comes from a collaboration with Facebook where the storage engine is built on top of RocksDB – an open source project mainly maintained by Facebook. The MyRocks storage engine is built using a log-structured merge tree (LSM tree) architecture and is well adapted to high write workloads. MyRocks also has a very high compression ratio and is built to optimize the lifetime of SSD disks.

Spider is a storage engine designed for extreme scale. The Spider storage engine allows you to shard a specific table across multiple nodes. It uses the partitioning protocol to define how the table should be split up and each individual shard will then reside on a remote MariaDB Server that will only handle queries for that particular shard. With Spider you get almost linear scaling for INSERTS and key lookup read queries.

 

And there’s more …

In addition to this, we have added a multitude of features to help speed up schema operations (like instant ADD COLUMN) and other optimizations and compatibility features. The ADD COLUMN feature is another example of our collaboration with customers and partners including Alibaba, Tencent and ServiceNow, and is just the beginning of making heavy DDL operations more usable.

Want all the details? Get a full list of features in MariaDB Server 10.3.

Get MariaDB Server 10.3 as part of the MariaDB TX 3.0 download – now available.

MariaDB Releases

Login
or
Register
to post comments

A Look into MariaDB Auditing for GDPR Compliance

A Look into MariaDB Auditing for GDPR Compliance
maria-luisaraviol

Wed, 05/23/2018 – 18:27

When we are talking about a database auditing concept, what we are focused on is tracking the use of database records, and the monitoring of each operation on the data.

The auditing activities goal is to provide a clear and reliable answer to the typical 4 W questions: Who accessed the database, When did this happen, What was touched, Where this access came from. Auditing should also help the security team answer the 5th W: Why this happened?

Auditing is also a very important task when we want to monitor the database activity to collect information that can help to increase the database performance or debug the application.

When we talk about security, accountability and regulatory compliance Database Auditing plays an even more critical role.

An auditing activity is key in achieving accountability as it allows us to investigate malicious or suspicious database activities. It’s used to help DBAs detect excessive user privileges or suspicious activities coming from specific connections.

In particular, the new European Union General Data Protection Regulation (GDPR) says that it will be important to be able to provide detail of changes to personal data to demonstrate that data protection and security procedures are effective and are being followed. Furthermore, we must ensure that data is only accessed by appropriate parties. This means that we need to be able to say who changed an item of data and when they changed it.

It’s broader than GDPR. HIPAA (Health Insurance Portability and Accountability Act) requires healthcare providers to deliver audit trails about anyone and everyone who touches any data in their records. This is down to the row and record level.

Furthermore, if a data breach occurs, organizations must disclose full information on these events to their local data protection authority (DPA) and all customers concerned with the data breach within 72 hours so they can respond accordingly.

MariaDB Audit Plugin

For all these reasons MariaDB started including the Audit Plugin since version 10.0.10 of MariaDB Server. The purpose of the MariaDB Audit Plugin is to log the server’s activity: for each client session, it records who connected to the server (i.e., user name and host), what queries were executed, and which tables were accessed and server variables that were changed.

Events that are logged by the MariaDB Audit Plugin are grouped into three different types: CONNECT, QUERY and TABLE events.

There are actually more types of events to allow fine-tuning of the audit, and focus on just the events and statements relevant for a specific organisation. These are detailed on the Log Settings Page.

There also exist several system variables to configure the MariaDB Audit Plugin. the Server Audit Status Variables page includes all variables relevant to review the status of the auditing. The overall monitoring should include an alert to monitor that the auditing is active.

This information is stored in a rotating log file or it may be sent to the local syslog.

For security reasons, it’s sometimes recommended to use the system logs instead of a local file: in this case the value of server_audit_output_type needs to be set to syslog.

It is also possible to set up even more advanced and secure solutions such as using a remote syslog service (Read more about the MariaDB Audit Plugin and setting up a rsyslog).

What does the MariaDB audit log file looks like?

The audit log file is a set of rows in plain text format, written as a list of comma-separated fields to a file. The general format for the logging to the plugin’s own file is defined like the following:

[timestamp],[serverhost],[username],[host],[connectionid],
[queryid],[operation],[database],[object],[retcode]

If the log file is sent to syslog the format is slightly different as the syslog has its own standard format (refer to the MariaDB Audit Plugin Log Format page for the details).

A typical MariaDB Audit plugin log file example is:

# tail mlr_Test_audit.log

20180421 09:22:38,mlr_Test,root,localhost,22,0,CONNECT,,,0
20180421 09:22:42,mlr_Test,root,localhost,22,35,QUERY,,’CREATE USER IF NOT EXISTS \’mlr\’@\’%\’ IDENTIFIED WITH \’mysql_native_password\’ AS \’*F44445443BB93ED07F5FAB7744B2FCE47021238F\”,0
20180421 09:22:42,mlr_Test,root,localhost,22,36,QUERY,,’drop user if exists mlr’,0
20180421 09:22:45,mlr_Test,root,localhost,22,0,DISCONNECT,,,0
20180421 09:25:29,mlr_Test,root,localhost,20,0,FAILED_CONNECT,,,1045
20180421 09:25:44,mlr_Test,root,localhost,43,133,WRITE,employees,salaries,
20180421 09:25:44,mlr_Test,root,localhost,43,133,QUERY,employees,’DELETE FROM salaries LIMIT 100′,0

Audit Files Analysis

Log files are a great source of information but only if you have a system in place to consistently review the data. Also the way you shape your application and database environment is important. In order to get useful auditing, for example, it’s recommended that every human user has his own account.

Furthermore, from the applications standpoint, if those are not using native DB accounts but application based accounts, each application accessing the same server should have its own “application-user”.

As we said before, you have to use the information collected and analyse it on a regular basis, and when needed, take immediate actions based on those logged events. However, even small environments can generate a lot of information to be analysed manually.

Starting with the most recent release, Monyog 8.5, the monitoring tool that is included with the MariaDB TX and MariaDB AX subscriptions,  added a very interesting feature for MariaDB: The Audit Log.

This feature parses the audit log maintained by MariaDB Server and displays the content in a clean tabular format.

Monyog accesses the audit log file, the same way it does for other MariaDB log files, including the Slow Query, General Query and Error log.

Through the Monyog interface you can select the server and the time-frame for which you want the audit log to be seen from.  Then, clicking on “SHOW AUDIT LOG” fetches the contents of the log. The limit on the number of rows that can be fetched in one time-frame is 10000.

The snapshot above gives you a quick summary of the audit log in a percentage, like Failed Logins, Failed Events, Schema changes, Data Changes and Stored Procedure. All these legends are clickable and shows the corresponding audit log entries on clicking.

Furthermore, you can use the filter option to fetch audit log based on Username, Host, Operation, Database and Table/Query.

MariaDB Releases

Login
or
Register
to post comments

Streaming Data From MariaDB Server Into MariaDB ColumnStore via MariaDB MaxScale

Streaming Data From MariaDB Server Into MariaDB ColumnStore via MariaDB MaxScale
markusmakela

Fri, 05/11/2018 – 23:04

In this blog post, we look at how to configure Change Data Capture (CDC) from the MariaDB Server toMariaDB ColumnStore via MariaDB MaxScale. Our goal in this blog post is to have our analytical
ColumnStore instance reflect the changes that happen on our operational MariaDB Server.

MariaDB MaxScale Configuration

We start by creating a MaxScale configuration with a binlogrouter and avrorouter instances. The
former acts as a replication slave and fetches binary logs and the latter processes the binary logs
into CDC records.

[replication-router]
type=service
router=binlogrouter
user=maxuser
passwd=maxpwd
server_id=2
master_id=1
binlogdir=/var/lib/maxscale
mariadb10-compatibility=1
filestem=mariadb-bin

[replication-listener]
type=listener
service=replication-router
protocol=MySQLClient
port=3306

[avro-router]
type=service
router=avrorouter
source=replication-router
avrodir=/var/lib/maxscale

[avro-listener]
type=listener
service=avro-router
protocol=cdc
port=4001

Copy the contents of this file into the `maxscale.cnf` file.

The docker-compose.yml File

The next step is to clone the MaxScale repository and to create the docker-compose file.

To clone the MaxScale repository, execute the following command.

git clone https://github.com/mariadb-corporation/MaxScale.git –branch=2.2 –depth=1

After the command completes, create the `docker-compose.yml` file with the following contents in the
same directory where you cloned MaxScale.
 

version: ‘2’
services:
    master:
        image: mariadb:10.2
        container_name: master
        environment:
            MYSQL_ALLOW_EMPTY_PASSWORD: Y
        command: mysqld –log-bin=mariadb-bin –binlog-format=ROW –server-id=1
        ports:
            – “3306:3306”

    maxscale:
        build: ./MaxScale/docker/
        container_name: maxscale
        volumes:
            – ./maxscale.cnf:/etc/maxscale.cnf.d/maxscale.cnf
        ports:
            – “3307:3306”
            – “4001:4001”

    mcs:
        image: mariadb/columnstore_singlenode:latest
        container_name: mcs
        ports:
            – “3308:3306”

    adapter:
        image: centos:7
        container_name: adapter
        command: /bin/sleep 0xffffffff

This file contains a MariaDB Server that acts as the master server, a MaxScale instance in a CDC
configuration and a single-node ColumnStore container. We also use a plain CentOS 7 container where
we install the adapter.

To start the cluster, run the following commands.

docker-compose build
docker-compose up -d

Configuring

The next step is to copy the ColumnStore configuration file from the `mcs` container and modify it
to use the container hostname instead of the loopback address. To do this, execute the following
commands.

docker cp mcs:/usr/local/mariadb/columnstore/etc/Columnstore.xml .
sed -i ‘s/127.0.0.1/mcs/’ Columnstore.xml
docker cp Columnstore.xml adapter:/etc/Columnstore.xml

After we have copied the configuration file into the `adapter` container, we are ready to install the adapter.

Installing Adapter

To access the container, execute `docker-compose exec adapter bash`. This will launch a new shell
where the following commands will be executed.

yum -y install epel-release
yum -y install https://downloads.mariadb.com/Data-Adapters/mariadb-columnstore-api/1.1.3/centos/x86_64/7/mariadb-columnstore-api-1.1.3-1-x86_64-centos7.rpm
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
yum -y install https://downloads.mariadb.com/Data-Adapters/mariadb-streaming-data-adapters/cdc-data-adapter/1.1.3/centos-7/mariadb-columnstore-maxscale-cdc-adapters-1.1.3-1-x86_64-centos7.rpm

After the adapter is installed, exit the shell.

Next we can start preparing the data on the master server and configure the replication between it
and MaxScale.

Preparing Data and Configuring Replication

We connect to the MariaDB Server running on the `master` container with the following command.

mysql -uroot -h 127.0.0.1 -P 3306

Once connected, executing the following SQL. This will prepare the server, create a table and insert
some dummy data into the table. It also modified the data to emulate changes in the database.

RESET MASTER;
CREATE USER ‘maxuser’@’%’ IDENTIFIED BY ‘maxpwd’;
GRANT ALL ON *.* TO ‘maxuser’@’%’;
CREATE DATABASE test;
USE test;
CREATE TABLE t1(id INT);
INSERT INTO t1 VALUES (1), (2), (3);
UPDATE t1 SET id = 4 WHERE id = 2;
DELETE FROM t1 WHERE id = 3;

Once we have created some data, we configure the replication between MaxScale and the master
server. To do this, execute the following command.

mysql -umaxuser -pmaxpwd -h 127.0.0.1 -P 3307 -e “CHANGE MASTER TO MASTER_HOST=’master’, MASTER_PORT=3306, MASTER_USER=’maxuser’, MASTER_PASSWORD=’maxpwd’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=4; START SLAVE”

MaxScale will start to replicate events from the master server and process them into CDC records.

Create CDC User

To use the CDC system in MaxScale, we have to create a user for it. Execute the following command to create a user.

docker-compose exec maxscale maxctrl call command cdc add_user avro-router cdcuser cdcpassword

Starting the Adapter

We again execute the commands inside the adapter container. To access the container, execute
`docker-compose exec adapter bash`.

Once inside the container, we can try to start the adapter. Given that the table `test.t1` does not
exist on ColumnStore, the adapter will give us an error when we try to start it:
 

[root@d444d5c5b820 /]$ mxs_adapter -c /etc/Columnstore.xml -u cdcuser -p cdcpassword -h maxscale -P 4001 test t1
Table not found, create with:

    CREATE TABLE test.t1 (domain int, event_number int, event_type varchar(50), id int, sequence int, server_id int, timestamp int) ENGINE=ColumnStore;

To create the table on ColumnStore, we have to exit the container. Once out of the container, we
connect to the ColumnStore container and create the table described in the error message with the
following command.

mysql -uroot -h 127.0.0.1 -P 3308 -e “CREATE TABLE test.t1 (domain int, event_number int, event_type varchar(50), id int, sequence int, server_id int, timestamp int) ENGINE=ColumnStore;”

Once the table is created, we go back into the adapter container with `docker-compose exec adapter
bash` and try to start it again.

[root@d444d5c5b820 /]$ mxs_adapter -c /etc/Columnstore.xml -u cdcuser -p cdcpassword -h maxscale -P 4001 test t1
4 rows and 1 transactions inserted in 0.210798 seconds. GTID = 0-1-6
2 rows and 1 transactions inserted in 0.164197 seconds. GTID = 0-1-7

This time we see that it processed a total of six rows of data. We can now connect to the
ColumnStore on another terminal and see what the table contains.

[markusjm@localhost blog]$ mysql -uroot -h 127.0.0.1 -P 3308 -e “SELECT * FROM test.t1”
+——–+————–+—————+——+———-+———–+————+
| domain | event_number | event_type    | id   | sequence | server_id | timestamp  |
+——–+————–+—————+——+———-+———–+————+
|      0 |            1 | insert        |    1 |        5 |         1 | 1523948280 |
|      0 |            2 | insert        |    2 |        5 |         1 | 1523948280 |
|      0 |            3 | insert        |    3 |        5 |         1 | 1523948280 |
|      0 |            1 | update_before |    2 |        6 |         1 | 1523948280 |
|      0 |            2 | update_after  |    4 |        6 |         1 | 1523948280 |
|      0 |            1 | delete        |    3 |        7 |         1 | 1523948281 |
+——–+————–+—————+——+———-+———–+————+

The changes we did on the master MariaDB Server have been propagated to ColumnStore. To understand
what the values are, we can map the SQL statements to the rows in the table.

The first SQL statement is `INSERT INTO t1 VALUES (1), (2), (3);` which inserts three values into
the table. We see that the first three rows in the resultset are of type `insert` and the values
match what we inserted.

The next SQL statement is `UPDATE t1 SET id = 4 WHERE id = 2;` which only touches one row. Although
it modifies only one row in the database, it generated two rows in ColumnStore. This happened
because the MaxScale CDC system stores both the before and after images of the modified row. This
allows easy comparisons between new and old values.

The final SQL statement was `DELETE FROM t1 WHERE id = 3;` which deleted one row. This statement was
converted to a delete entry with the data that was deleted (row with `id` of 3). This allows deleted
data to be retained for analytical and auditing purposes without actually storing it on the master
database.

Big Data
ColumnStore
How to
MaxScale

In this blog post, we look at how to configure Change Data Capture from the MariaDB Server toMariaDB ColumnStore via MariaDB MaxScale. Our goal in this blog post is to have our analytical
ColumnStore instance reflect the changes that happen on our operational MariaDB Server.

Login
or
Register
to post comments

How to Restore a Single Database from MariaDB Backup

How to Restore a Single Database from MariaDB Backup
Ulrich Moser

Tue, 04/10/2018 – 17:01

Lately, I’ve been asked how to restore a single database or even a single table out of a complete backup of MariaDB Server that was created with MariaDB Backup. This blog provides step-by-step guidance on how to achieve a restore of a database. Another blog post will pick up the question on how to restore a single table which has a separate set of challenges.

We will use the world sample database and a backup directory /opt/backup/ as an example to explain the process.

Step 1 – Creating the Backup and Preparing the Database for Export

As root or user with write permission to /opt/backup issue the following commands:

# TS=`date +”%Y-%m-%d_%H-%M-%S”`# mkdir /opt/backup/${TS}# mariabackup –backup –user backup1 –password MariaDB \ –target-dir “/opt/backup/${TS}”

This created a directory /opt/backup/2018-03-28_19-02-56 with the complete backup.

To be able to restore a database or to be more precise all or some tables of a database you first need to have the tables prepared for export. This is the easiest step in the process. To prepare all tables of a database world for export issue the following command:

# mariabackup  –prepare –export –databases world \–user backup1 –password MariaDB \ –target-dir “/opt/backup/${TS}”

After this step if you go to the backup directory you will find .cfg files for all tables in world.

# cd /opt/backup/2018-03-28_19-02-56# ls -l worldtotal 1132-rw-rw—- 1 root root    686 Mar 28 19:05 city.cfg-rw-r—– 1 root root   1578 Mar 28 19:03 city.frm-rw-r—– 1 root root 606208 Mar 28 19:03 city.ibd-rw-r—– 1 root root    856 Mar 28 19:03 country_capital.frm-rw-rw—- 1 root root   1228 Mar 28 19:05 country.cfg-rw-r—– 1 root root   1618 Mar 28 19:03 country.frm-rw-r—– 1 root root 163840 Mar 28 19:03 country.ibd-rw-rw—- 1 root root    665 Mar 28 19:05 countrylanguage.cfg-rw-r—– 1 root root   1542 Mar 28 19:03 countrylanguage.frm-rw-r—– 1 root root 229376 Mar 28 19:03 countrylanguage.ibd-rw-r—– 1 root root     61 Mar 28 19:03 db.opt

country_capital.frm is a view on country and city tables therefore it has no .cfg file since it has no tablespace.

Step 2 – Creating empty tables for the restore

Next, you’ll need to create a database you want to restore the tables to.  The database does not necessarily need to be named the same as the database in the backup. For demonstration purposes, we use a database named world2.

What you need is the CREATE DATABASE and CREATE TABLE SQL statements that you used to create the original tables. You can obtain these from your server by taking the full CREATE TABLE statements from SHOW CREATE TABLE for each table (see emphasized text).

MariaDB [world]> SHOW CREATE DATABASE world\G************************** 1. row ***************************Database: worldCreate Database: CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci */1 row in set (0.00 sec)MariaDB [world]> SHOW CREATE TABLE country\G*************************** 1. row ***************************Table: countryCreate Table: CREATE TABLE `country` (`Code` char(3) NOT NULL DEFAULT ”,`Name` char(52) NOT NULL DEFAULT ”,`Continent` enum(‘Asia’,’Europe’,’North America’,’Africa’,’Oceania’,’Antarctica’,’South America’) NOT NULLDEFAULT ‘Asia’,`Region` char(26) NOT NULL DEFAULT ”,`SurfaceArea` float(10,2) NOT NULL DEFAULT 0.00,`IndepYear` smallint(6) DEFAULT NULL,`Population` int(11) NOT NULL DEFAULT 0,`LifeExpectancy` float(3,1) DEFAULT NULL,`GNP` float(10,2) DEFAULT NULL,`GNPOld` float(10,2) DEFAULT NULL,`LocalName` char(45) NOT NULL DEFAULT ”,`GovernmentForm` char(45) NOT NULL DEFAULT ”,`HeadOfState` char(60) DEFAULT NULL,`Capital` int(11) DEFAULT NULL,`Code2` char(2) NOT NULL DEFAULT ”,PRIMARY KEY (`Code`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)MariaDB [world]> SHOW CREATE TABLE city\G*************************** 1. row ***************************      Table: cityCreate Table: CREATE TABLE `city` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT ”, `CountryCode` char(3) NOT NULL DEFAULT ”, `District` char(20) NOT NULL DEFAULT ”, `Population` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)) ENGINE=InnoDB AUTO_INCREMENT=4100 DEFAULT CHARSET=latin11 row in set (0.00 sec)MariaDB [world]> SHOW CREATE TABLE countrylanguage\G*************************** 1. row ***************************      Table: countrylanguageCreate Table: CREATE TABLE `countrylanguage` ( `CountryCode` char(3) NOT NULL DEFAULT ”, `Language` char(30) NOT NULL DEFAULT ”, `IsOfficial` enum(‘T’,’F’) NOT NULL DEFAULT ‘F’, `Percentage` float(4,1) NOT NULL DEFAULT 0.0, PRIMARY KEY (`CountryCode`,`Language`), KEY `CountryCode` (`CountryCode`), CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

You need to remove any referential integrity constraints from the CREATE TABLE statements (see text in red) and recreate them after successfully importing the tablespaces because this can cause problems when you try to discard the tablespace in the next step.

MariaDB [world2]> ALTER TABLE country DISCARD TABLESPACE;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

In this case drop the FOREIGN KEY CONSTRAINT by issuing:

MariaDB [world2]> ALTER TABLE city DROP FOREIGN KEY city_ibfk_1;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0

In the CREATE DATABASE statement replace world with world2.

If you do not have your original database or tables anymore you need to get your latest CREATE statements from your application. So it is always a good idea to get the CREATE statements from every database you have on your servers whenever a change to the schema has occurred and store them in a safe place.

If your original database schema still exists you can also use the following statements to prepare the database for restore:

MariaDB [(none)]> CREATE DATABASE world2;Query OK, 1 row affected (0.01 sec)MariaDB [(none)]> use world2Database changedMariaDB [world2]> CREATE TABLE country LIKE world.country;Query OK, 0 rows affected (0.05 sec)MariaDB [world2]> CREATE TABLE city LIKE world.city;Query OK, 0 rows affected (0.04 sec)MariaDB [world2]> CREATE TABLE countrylanguage LIKE world.countrylanguage;Query OK, 0 rows affected (0.04 sec)

Referential integrity constraints are not copied into the new schema.

Step 3 – Discard the tablespaces

MariaDB [world2]> ALTER TABLE country DISCARD TABLESPACE;Query OK, 0 rows affected (0.02 sec)MariaDB [world2]> ALTER TABLE city DISCARD TABLESPACE;Query OK, 0 rows affected (0.01 sec)MariaDB [world2]> ALTER TABLE countrylanguage DISCARD TABLESPACE;Query OK, 0 rows affected (0.01 sec)

After this step the database directory for world2 only contains the .frm files and the db.opt file.

Step 4 – Copy the tables to restore to the new database directory

# cp /opt/backup/2018-03-28_19-02-56/world/*.* /var/lib/mysql/world2

If you look into the database directory world2 now you will see the following:

# ls -ltotal 1008-rw-r—– 1 root  root 686 Mar 28 19:25 city.cfg-rw-rw—- 1 mysql mysql   1578 Mar 28 19:25 city.frm-rw-r—– 1 root  root 606208 Mar 28 19:25 city.ibd-rw-r—– 1 root  root 856 Mar 28 19:25 country_capital.frm-rw-r—– 1 root  root 1228 Mar 28 19:25 country.cfg-rw-rw—- 1 mysql mysql   1618 Mar 28 19:25 country.frm-rw-r—– 1 root  root 163840 Mar 28 19:25 country.ibd-rw-r—– 1 root  root 665 Mar 28 19:25 countrylanguage.cfg-rw-rw—- 1 mysql mysql   1542 Mar 28 19:25 countrylanguage.frm-rw-r—– 1 root  root 229376 Mar 28 19:25 countrylanguage.ibd-rw-rw—- 1 mysql mysql     61 Mar 28 19:25 db.opt

The form files are owned by user and group mysql but the tablespace and export files (.cfg files) are not. To be able to import the tablespaces you need to change the ownership.

chown -R mysql:mysql /var/lib/mysql/world2

Step 5 – Import the tablespaces

To complete the process you now need to import the restored tablespaces.

MariaDB [world2]> ALTER TABLE country IMPORT TABLESPACE;Query OK, 0 rows affected (0.09 sec)MariaDB [world2]> ALTER TABLE city IMPORT TABLESPACE;Query OK, 0 rows affected (0.10 sec)MariaDB [world2]> ALTER TABLE countrylanguage IMPORT TABLESPACE;Query OK, 0 rows affected (0.06 sec)

After importing the tablespace the database is fully restored. A SELECT against the imported tables shows that they have all the data expected:

MariaDB [world]> select count(id) from world.city;+———–+| count(id) |+———–+|      4081 |+———–+1 row in set (0.01 sec)

MariaDB [world2]> select count(id) from world2.city;+———–+| count(id) |+———–+|      4081 |+———–+1 row in set (0.01 sec)

If all went well we only need to add the FOREIGN KEY CONSTRAINTS again which is done in Step 6.

During import of the tablespaces you might get an error saying that the flags of the tablespaces to be imported do not match with the flags of the newly created tables in the new database.

MariaDB [world3]> ALTER TABLE country IMPORT TABLESPACE;ERROR 1808 (HY000): Schema mismatch (Table flags don’t match, sserver table has 0x21 and the meta-data file has 0x1)

See Step 7 on how to get around this error.

Step 6 – Recreate FOREIGN KEY Constraint

Recreate FOREIGN KEY constraint on table city:

MariaDB [world2]> ALTER TABLE city ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`);Query OK, 4081 rows affected (0.15 sec) Records: 4081  Duplicates: 0 Warnings: 0

Recreate FOREIGN KEY constraint on table countrylanguage:

MariaDB [world2]> ALTER TABLE countrylanguage ADD CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`);Query OK, 984 rows affected (0.12 sec)             Records: 984  Duplicates: 0 Warnings: 0

Step 7 – Identifying the InnoDB File Formats

If the world database was created with a version prior to that the Antelope file format you have ROW_FORMAT=COMPACT which corresponds to FLAG: 1 (0x1). If the version you are restoring to uses Barracuda file format the ROW_FORMAT will be Dynamic which corresponds to FLAG: 33 (0x21).

So check the row format of the original tables if you still have access to them.

MariaDB [(none)]> select * from information_schema.innodb_sys_tables where name like ‘world/%’\G*************************** 1. row **************************    TABLE_ID: 125        NAME: world/city        FLAG: 1      N_COLS: 12       SPACE: 131 FILE_FORMAT: Antelope  ROW_FORMAT: CompactZIP_PAGE_SIZE: 0  SPACE_TYPE: Single

This will also be the format of the tablespace files in /opt/backup/2018-03-28_19-02-56/world .

Do the same check on the new tables. If it looks like in the example below you must proceed with Step 8.

MariaDB [world]> select * from information_schema.innodb_sys_tables where name like ‘world2/%’\G*************************** 1. row ***************************    TABLE_ID: 159        NAME: world/city        FLAG: 33      N_COLS: 8       SPACE: 108 FILE_FORMAT: Barracuda  ROW_FORMAT: DynamicZIP_PAGE_SIZE: 0  SPACE_TYPE: Single

If you still have access to a working copy of the original database and tables like in the example above you can do the FILE_FORMAT check before trying to import the tablespaces and do Step 8 before.

Step 8 – Adjust FILE_FORMAT and ROW_FORMAT

In this case you need to change the ROW_FORMAT of the new empty tables to COMPACT by issuing:

ALTER TABLE country ROW_FORMAT=COMPACT;Query OK, 0 rows affected (0.01 sec)ALTER TABLE city ROW_FORMAT=COMPACT;Query OK, 0 rows affected (0.01 sec)ALTER TABLE countrylanguage ROW_FORMAT=COMPACT;Query OK, 0 rows affected (0.01 sec)

Now retry from Step 5.

Restoring a database to a MariaDB Galera Cluster

Generally speaking, this same procedure can be used to restore a single database to a MariaDB Galera Cluster. The imported tablespaces will only be available on the node where the restore has been executed since tablespace imports are not replicated to the other nodes. We will cover the whole how to restore a single database to a MariaDB Galera Cluster including samples in a later blog. Stay tuned!

DBA
Galera
How to
InnoDB

Lately, I’ve been asked how to restore a single database or even a single table out of a complete backup of MariaDB Server that was created with MariaDB Backup. This blog provides step-by-step guidance on how to achieve a restore of a database. Another blog post will pick up the question on how to restore a single table which has a separate set of challenges.

Login
or
Register
to post comments

MyRocks Storage Engine in MariaDB is Now Release Candidate

MyRocks Storage Engine in MariaDB is Now Release Candidate
Sergey Petrunya

Fri, 03/09/2018 – 11:19

The MyRocks storage engine was introduced in MariaDB Server 10.2 as an alpha plugin – the maturity of plugins is separate from the database. It became a beta plugin earlier this year, and with the release of MariaDB Server 10.3.5 (RC) last week, it is now a release candidate plugin.
So, what is MyRocks? It is a storage engine like InnoDB, but optimized for disk space and write efficiency. It uses a log-structured merge-tree (LSM Tree) technology to achieve higher compression and write performance. MyRocks is developed by Facebook, where it is used in production.
We make MyRocks available for MariaDB users via binaries and packages for recent versions of Ubuntu/Debian (deb), Red Hat/CentOS (rpm), Generic Linux (tarballs) and Microsoft Windows. For developers, you can continue to use features like common table expressions (CTEs) and window functions. For administrators, you can continue to enable and configuration parallel replication.
While it’s easy to get started with MyRocks using MariaDB, you have to run a couple of commands to enable it. The process is documented in our knowledge base (and there are links for further MyRocks documentation).
Additional Resources

Documentation on MyRocks in MariaDB

Upstream MyRocks documentation

Learn how Facebook migrated to MyRocks

Learn how to use MyRocks with MariaDB Server

MariaDB Releases
Plugins
Storage Engines

The MyRocks storage engine was introduced in MariaDB Server 10.2 as an alpha plugin – the maturity of plugins is separate from the database. It became a beta plugin earlier this year, and with the release of MariaDB Server 10.3.5 (RC) last week, it is now a release candidate plugin.

Login
or
Register
to post comments

MariaDB MaxScale 2.2: Introducing Failover, Switchover and Automatic Rejoin

MariaDB MaxScale 2.2: Introducing Failover, Switchover and Automatic Rejoin
Esa Korhonen

Thu, 02/22/2018 – 18:16

Failure tolerance and recoverability are essential for a high availability (HA) database setup. Although modern systems are quite reliable, hardware errors or software bugs (not necessarily in the database itself) can bring a system down. MariaDB HA setups use master-slave replication to copy the data to multiple servers, which may be located in different datacenters. Should the master server fail the application can be directed to use one of the slave servers. This operation either requires manual interference from a dba or a custom automated script. Depending on time of day and personnel, manual operation may be slow. Custom scripts may lack testing and flexibility. Clearly, recovery should be automatic, thoroughly tested and preferably included in existing database scalability software.

To answer this demand, MariaDB MaxScale 2.2.2. adds the following master-slave replication cluster management features:

Failover: replace a failed master with the most up-to-date slave

Switchover: swap the running master with a designated slave

Rejoin: rejoin a standalone server to the cluster as a slave

MariaDB MaxScale is an advanced database proxy for MariaDB database servers. It sits between client applications and the database servers, routing client queries and server responses. MaxScale also monitors the servers, so it will quickly notice any changes in server status or replication topology. This makes MaxScale a natural choice for controlling failover and similar features.

Failover for the master-slave cluster can and often should be set to activate automatically. Switchover must be activated manually through MaxAdmin, MaxCtrl or the REST interface. Rejoin can be set to automatic or activated manually. These features are implemented in the mariadbmonitor-module. This module replaces the old mysqlmonitor (MaxScale is still backwards compatible with the old name). All three operations require GTID-based replication and are intended for simple single-master replication topologies. Additionally, failover and switchover expect the topology to be one-layer deep. The cluster master may be replicating from an external master, in which case a promoted master server is instructed to replicate from the external master.

In this blog post, we present an example setup and experiment with the new features. The database setup for this example is:

One VM for MariaDB MaxScale 2.2.2
One VM for the master MariaDB Server
One VM for the slave MariaDB Server 
 

[root@box01 ~]# maxadmin list servers
Servers.
——————-+—————–+——-+————-+——————–
Server | Address | Port | Connections | Status
——————-+—————–+——-+————-+——————–
box02 | 192.168.50.12 | 3306 | 0 | Master, Running
box03 | 192.168.50.13 | 3306 | 0 | Slave, Running
——————-+—————–+——-+————-+——————–

Here is the vagrantfile used for the examples of this blog:

# -*- mode: ruby maxscale222
# vi: set ft=ruby :

# Vagrantfile API/syntax version. Don’t touch unless you know what you’re doing!
VAGRANTFILE_API_VERSION = “2”

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|

#: adding ansible stuff
config.ssh.insert_key = false

#: maxscale box
config.vm.define “box01″ do |box01|
box01.vm.hostname=”box01”
box01.vm.box = “centos7.2_x86_64”
box01.vm.network “private_network”, ip: “192.168.50.11”, virtualbox__intnet: “XY”
end

######: MASTER / SLAVE SERVERS :######
#: master, async && semisync replication
config.vm.define “box02″ do |box02|
box02.vm.hostname=”box02”
box02.vm.box = “centos7.2_x86_64”
box02.vm.network “private_network”, ip: “192.168.50.12”, virtualbox__intnet: “XY”
end

#: slave01, async && semisync replication
config.vm.define “box03″ do |box03|
box03.vm.hostname=”box03”
box03.vm.box = “centos7.2_x86_64”
box03.vm.network “private_network”, ip: “192.168.50.13”, virtualbox__intnet: “XY”
end
end

Setting up MariaDB MaxScale 2.2.2

In these examples we are running CentOS 7.2. If you are running a Debian based Linux distribution, check here for the best MaxScale package for your system.  After downloading, install the packages and configure MaxScale as presented below.

#: packages you want to download
[root@box01 ~]# ls -lhS
total 15M
-rw-r–r– 1 root root 7.8M Jan 10 20:44 maxscale-client-2.2.2-1.centos.7.x86_64.rpm
-rw-r–r– 1 root root 7.0M Jan 10 20:44 maxscale-2.2.2-1.centos.7.x86_64.rpm

#: set up them
[root@box01 ~]# rpm -ivh *.rpm
Preparing… ################################# [100%]
Updating / installing…
1:maxscale-client-2.2.2-1 ################################# [ 50%]
2:maxscale-2.2.2-1 ################################# [100%]

#: checking the version
[root@box01 ~]# maxscale –version-full
MaxScale 2.2.2 – eda82881619388a3512d6cfcbcf9ad83ea930339
[…snip…]

#: basic configuration – /etc/maxscale.cnf
[maxscale]
threads=auto
log_info=true

[rwsplit-service]
type=service
router=readwritesplit
user=maxuser
passwd=D96E8B61A569EDD7C625D822203932FA

[CLI]
type=service
router=cli

[CLI Unix Listener]
type=listener
service=CLI
protocol=maxscaled
socket=default

[CLI Inet Listener]
type=listener
service=CLI
protocol=maxscaled
address=localhost
port=6603

Above, the password for the service user is encrypted. An encrypted password can be generated with the maxkeys and maxpasswd utilities. For more information, check maxkeys/maxpasswd. Once configuration is complete, start MaxScale:

[root@box01 ~]# systemctl enable maxscale.service
Created symlink from /etc/systemd/system/multi-user.target.wants/maxscale.service to /usr/lib/systemd/system/maxscale.service.
[root@box01 ~]# systemctl is-enabled maxscale.service
enabled
[root@box01 ~]# systemctl start maxscale.service
[root@box01 ~]# systemctl status maxscale.service
● maxscale.service – MariaDB MaxScale Database Proxy
Loaded: loaded (/usr/lib/systemd/system/maxscale.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2018-01-12 00:24:21 GMT; 5s ago
Process: 4035 ExecStart=/usr/bin/maxscale (code=exited, status=0/SUCCESS)
Process: 4032 ExecStartPre=/usr/bin/install -d /var/run/maxscale -o maxscale -g maxscale (code=exited, status=0/SUCCESS)
Main PID: 4038 (maxscale)
CGroup: /system.slice/maxscale.service
└─4038 /usr/bin/maxscale

The following script demonstrates MaxScale’s runtime configuration management. These items could have been added to the configuration file instead. The commands generate a server cluster monitor within MaxScale and set it up for automatic cluster management. The individual parameters set here are presented in the next section.

#!/bin/bash

#: creating the monitor
maxadmin create monitor cluster-monitor mariadbmon

#: adding more features for the MariaDBMon monitor
maxadmin alter monitor cluster-monitor user=maxuser password=ACEEF153D52F8391E3218F9F2B259EAD monitor_interval=1000 replication_user=mariadb replication_password=ACEEF153D52F8391E3218F9F2B259EAD failcount=5 auto_failover=true auto_rejoin=true

#: restarting the monitor
maxadmin restart monitor cluster-monitor

#: creating the service listener
maxadmin create listener rwsplit-service rwsplit-listener 0.0.0.0 53310

#: creating and adding the servers
maxadmin create server prod_mariadb01 192.168.50.12 3306
maxadmin create server prod_mariadb02 192.168.50.13 3306
maxadmin add server prod_mariadb01 cluster-monitor rwsplit-service
maxadmin add server prod_mariadb02 cluster-monitor rwsplit-service

Before executing the script above, you should generate the users maxuser and mariadb (or whatever usernames were generated by the script) on the backends. Again, their encrypted passwords for the script should be generated with maxpasswd.

#: script execution output

[root@box01 ~]# ./mxs222_configs_rwsplit.sh
Created monitor ‘cluster-monitor’
Listener ‘rwsplit-listener’ created
Created server ‘prod_mariadb01’
Created server ‘prod_mariadb02’
Added server ‘prod_mariadb01’ to ‘cluster-monitor’
Added server ‘prod_mariadb01’ to ‘rwsplit-service’
Added server ‘prod_mariadb02’ to ‘cluster-monitor’
Added server ‘prod_mariadb02’ to ‘rwsplit-service’

The monitor is now running. To check the status of Maxscale, execute the following:

#: listing servers after creating the configurations

[root@box01 ~]# maxadmin list servers
Servers.
——————-+—————–+——-+————-+——————–
Server | Address | Port | Connections | Status
——————-+—————–+——-+————-+——————–
prod_mariadb01 | 192.168.50.12 | 3306 | 0 | Master, Running
prod_mariadb02 | 192.168.50.13 | 3306 | 0 | Slave, Running
——————-+—————–+——-+————-+——————–

#: listing monitors

[root@box01 ~]# maxadmin list monitors

———————+———————
Monitor | Status
———————+———————
cluster-monitor | Running
———————+———————

Finally, you may check the listener and the open port:

[root@box01 ~]# maxadmin list listeners | grep rwsplit-listener
Rwsplit-listener | rwsplit-service | MariaDBClient | 0.0.0.0 | 53310 | Running

[root@box01 ~]# netstat -l | grep 53310
tcp 0 0 0.0.0.0:53310 0.0.0.0:* LISTEN

Monitor configuration parameters

The following parameters enable and control the cluster management features.

replication_user and replication_password: These are the username and the password used by MariaDBMonitor when generating a CHANGE MASTER TO-command.

auto_failover: Enables automatic failover. Failover can be activated manually regardless of this setting

failover_timeout: Time limit (in seconds) for executing a failover, measured from the moment failover (automatic or manual) is activated. If time runs out, an event is logged, and automatic failover is disabled. Typically, the timeout is only reached if the selected new master server cannot consume its relay log quickly enough.

auto_rejoin: Enable automatic rejoin. When enabled, two types of servers are set to replicate from the current cluster master:

Standalone servers (no slave thread)
Any server replicating from (or attempting to) from a server which is not the cluster master server.

failcount: How many times (during different monitoring passes) a server must fail to respond to status query before it is declared down and an automatic failover may be triggered if enabled.

verify_master_failure: This enables an additional criteria for triggering an automatic failover. The monitor will look at the master binlog file positions of the slave servers and if they have advanced within a configured timeout, failover is not activated even if the monitor cannot connect to the master. This means that at least one slave still receives events even if MaxScale cannot connect to the master.

master_failure_timeout: The timeout for verify_master_failure.

switchover_timeout: Similar to failover_timeout, just for switchover.

An example configuration file section for a monitor with these settings is below.

[wb@maxscale maxscale.cnf.d]$ cat /var/lib/maxscale/maxscale.cnf.d/cluster-monitor.cnf

[cluster-monitor]
type=monitor
module=mariadbmon
servers=prod_mariadb01,prod_mariadb02
user=maxuser
password=ACEEF153D52F8391E3218F9F2B259EAD
replication_user=mariadb
replication_password=ACEEF153D52F8391E3218F9F2B259EAD
monitor_interval=1000 #: it should be >= 5000 for production
auto_failover=1
failover_timeout=5 #: it should be >= 10 for production
auto_rejoin=true
failcount=5
master_failure_timeout=2
verify_master_failure=true
switchover_timeout=90

Switchover

If the current master is showing any issues, you may want to promote a slave to take its place. The switchover-command takes three arguments: the monitor name, the slave to be promoted and the current master.

#: switchover process
#: listing servers and current status
[root@box01 ~]# maxadmin list servers
Servers.
——————-+—————–+——-+————-+——————–
Server | Address | Port | Connections | Status
——————-+—————–+——-+————-+——————–
prod_mariadb01 | 192.168.50.12 | 3306 | 0 | Master, Running
prod_mariadb02 | 192.168.50.13 | 3306 | 0 | Slave, Running
——————-+—————–+——-+————-+——————–

#: command to make the current slave a new master
[root@box01 ~]# maxadmin call command mariadbmon switchover cluster-monitor prod_mariadb02 prod_mariadb01

#: what Maxscale logs says, default location /var/log/maxscale/maxscale.log
2018-01-12 20:00:28 info : (2) Started CLI client session [8] for ‘root’ from localhost
2018-01-12 20:00:28 info : (8) [cli] MaxAdmin: call command “mariadbmon” “switchover” “cluster-monitor” “prod_mariadb02” “prod_mariadb01”
2018-01-12 20:00:29 notice : (8) [mariadbmon] Stopped the monitor cluster-monitor for the duration of switchover.
2018-01-12 20:00:29 notice : (8) [mariadbmon] Demoting server ‘prod_mariadb01’.
2018-01-12 20:00:29 notice : (8) [mariadbmon] Promoting server ‘prod_mariadb02’ to master.
2018-01-12 20:00:29 notice : (8) [mariadbmon] Old master ‘prod_mariadb01’ starting replication from ‘prod_mariadb02’.
2018-01-12 20:00:29 notice : (8) [mariadbmon] Redirecting slaves to new master.
2018-01-12 20:00:29 notice : (8) [mariadbmon] Switchover prod_mariadb01 -> prod_mariadb02 performed.2018-01-12 20:00:29 info : Stopped CLI client session [8]

The warning messages suggest activating gtid_strict_mode on the servers, as this enables some additional checks when a server is starting replication.

#: listing servers again
[root@box01 ~]# maxadmin list servers
Servers.
——————-+—————–+——-+————-+——————–
Server | Address | Port | Connections | Status
——————-+—————–+——-+————-+——————–
prod_mariadb01 | 192.168.50.12 | 3306 | 0 | Slave, Running
prod_mariadb02 | 192.168.50.13 | 3306 | 0 | Master, Running
——————-+—————–+——-+————-+——————– 

Switchover uses the server global setting read_only to freeze the master server when preparing to switch. Users with SUPER-privilege bypass read_only, which allows them to modify data during a switchover. This often causes replication to  break as different servers have different events. To prevent this, make sure that any users who regularly do write queries do not have SUPER.

Failover

Failover is activated when the master crashes or becomes unavailable. MariaDB Monitor will detect that the master is out of reach, will wait for a while in case the master quickly comes back (wait time is configurable), and finally begins failover to replace the failed master with a slave.

For example, if failcount is 5 and monitor_interval is 1000, the failover requires 5 monitor passes without master server connection, with one second waits between monitor passes.

Let’s demonstrate by shutting down the current master with systemctl. 

#: failover, let’s kill the current master
[root@box01 ~]# maxadmin list servers
Servers.
——————-+—————–+——-+————-+——————–
Server | Address | Port | Connections | Status
——————-+—————–+——-+————-+——————–
prod_mariadb01 | 192.168.50.12 | 3306 | 0 | Slave, Running
prod_mariadb02 | 192.168.50.13 | 3306 | 0 | Master, Running
——————-+—————–+——-+————-+——————–

[root@box03 mysql]# systemctl stop mariadb
[root@box03 mysql]# systemctl status mariadb
● mariadb.service – MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: inactive (dead) since Fri 2018-01-12 20:19:39 GMT; 12s ago
Process: 4295 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 4259 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 4223 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 4221 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 4259 (code=exited, status=0/SUCCESS)
Status: “MariaDB server is down”

Below is an excerpt of the MaxScale log. mariadbmon detects that the current master has gone away and after 2 monitor passes failover activates.

#: what Maxscale logs says, default location /var/log/maxscale/maxscale.log
2018-01-12 20:19:39 error : Monitor was unable to connect to server [192.168.50.13]:3306 : “Can’t connect to MySQL server on ‘192.168.50.13’ (115)”
2018-01-12 20:19:39 notice : [mariadbmon] Server [192.168.50.13]:3306 lost the master status.
2018-01-12 20:19:39 notice : Server changed state: prod_mariadb02[192.168.50.13:3306]: master_down. [Master, Running] -> [Down]
2018-01-12 20:19:39 warning: [mariadbmon] Master has failed. If master status does not change in 2 monitor passes, failover begins.
2018-01-12 20:19:39 error : [mariadbmon] No Master can be determined. Last known was 192.168.50.13:3306
2018-01-12 20:19:41 notice : [mariadbmon] Performing automatic failover to replace failed master ‘prod_mariadb02’.
2018-01-12 20:19:41 notice : [mariadbmon] Promoting server ‘prod_mariadb01’ to master.
2018-01-12 20:19:41 notice : [mariadbmon] Redirecting slaves to new master.
2018-01-12 20:19:42 warning: [mariadbmon] Setting standalone master, server ‘prod_mariadb01’ is now the master.
2018-01-12 20:19:42 notice : Server changed state: prod_mariadb01[192.168.50.12:3306]: new_master. [Slave, Running] -> [Master, Running]
#: checking the server’s status
[root@box01 ~]# maxadmin list servers
Servers.
——————-+—————–+——-+————-+——————–
Server | Address | Port | Connections | Status
——————-+—————–+——-+————-+——————–
prod_mariadb01 | 192.168.50.12 | 3306 | 0 | Master, Running
prod_mariadb02 | 192.168.50.13 | 3306 | 0 | Down
——————-+—————–+——-+————-+——————–

Automatic rejoin

When auto_rejoin is enabled, the monitor will rejoin any standalone database servers or any slaves replicating from a relay master to the main cluster. The typical use case for this feature is rejoining the old master after a failover. Should the master come back online after a slave was already promoted to its place, it would not be immediately replicating. Auto-rejoin will detect this and redirect the master. This is not certain to succeed as the master may have conflicting events. In this case the slave thread will end in an error.

Below is an example of a successful operation:

#: let’s test the auto_rejoin now as we will back up with

#: the server we put down on the failover exercise
[root@box01 ~]# maxadmin list servers
Servers.
——————-+—————–+——-+————-+——————–
Server | Address | Port | Connections | Status
——————-+—————–+——-+————-+——————–
prod_mariadb01 | 192.168.50.12 | 3306 | 0 | Master, Running
prod_mariadb02 | 192.168.50.13 | 3306 | 0 | Down
——————-+—————–+——-+————-+——————–

#: what Maxscale logs says, default location /var/log/maxscale/maxscale.log
2018-01-12 20:22:43 notice : Server changed state: prod_mariadb02[192.168.50.13:3306]: server_up. [Down] -> [Running]
2018-01-12 20:22:43 notice : [mariadbmon] Directing standalone server ‘prod_mariadb02’ to replicate from ‘prod_mariadb01’.
2018-01-12 20:22:43 notice : [mariadbmon] 1 server(s) redirected or rejoined the cluster.
2018-01-12 20:22:44 notice : Server changed state: prod_mariadb02[192.168.50.13:3306]: new_slave. [Running] -> [Slave, Running]

Above, the server backend prod_master02 has returned and was joined to the cluster as a slave of the current master.

#: checking the server’s status
[root@box01 ~]# maxadmin list servers
Servers.
——————-+—————–+——-+————-+——————–
Server | Address | Port | Connections | Status
——————-+—————–+——-+————-+——————–
prod_mariadb01 | 192.168.50.12 | 3306 | 0 | Master, Running
prod_mariadb02 | 192.168.50.13 | 3306 | 0 | Slave, Running
——————-+—————–+——-+————-+——————–

Additional Comments

If you omit the replication_user and the replication_password on the monitor configurations, the username and password used by the monitor to check the current state of the backends will be used instead. In this case the monitor user should have, in addition to its normal rights, the ability to connect among the backends as well. Usually the user for MariaDBMon is restricted to connections only from the MaxScale host.

If you use an encrypted password for the monitor user, the replication_password should be encrypted as well. Otherwise, the CHANGE MASTER TO query will fail.

MariaDB Servers forming a cluster should be configured with gtid_strict_mode enabled to make sure databases have the same binary log order among the instances.

MaxScale

MariaDB MaxScale 2.2 introduces failover, switchover and automatic rejoin for MariaDB Master/Slave replication clusters.

Login
or
Register
to post comments

MariaDB Server 10.2.13 now available

MariaDB Server 10.2.13 now available
dbart

Tue, 02/13/2018 – 12:21

The MariaDB project is pleased to announce the immediate availability of MariaDB Server 10.2.13. See the release notes and changelog for details and visit mariadb.com/dow…

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569