Setting up multi-source replication (MySQL 8.0) with GTID based replication

Last week I posted a blog on how to setup multi-source for MySQL 5.7 using GTID based replication, in this blog we will do the same using MySQL 8.0.

Most of the procedure are the same, there are only two things that we need to change are;
1) Procedure for creating the replication user.
2) Fetching the GTID value from the MySQL dumpfile.

1) Create replication user for MySQL 8
MySQL 8 uses a new authentication plugin so to create the replication user will differ from MySQL 5.7. The procedure for 5.7 was with:

mysql> CREATE USER 'repl_user'@'slave-host' IDENTIFIED BY 'repl_pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave-host';

With MySQL 8 you need to create the user like:

mysql> CREATE USER 'repl_user'@'slave-host' IDENTIFIED WITH sha256_password BY 'repl_pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave-host';

Another option is to configure MySQL to use old authentication method, then you do not have to modify the CREATE statement above. This can be done by setting configuration variable default_authentication_plugin to "mysql_native_password".

2) Changed format of GTID_PURGED information in dumpfile
The GTID_PURGED information in the dumpfile now include a comment like:

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'dfa44283-d466-11e9-80ec-ec21e522bf21:1-6';

To fetch the GTID you now have to use below command that will remove also the new comment:

cat dumpM1.sql | grep GTID_PURGED | perl -p0 -e 's#/\*.*?\*/##sg' | cut -f2 -d'=' | cut -f2 -d$'\''

The github page and the scripts to create a test environment have also been updated!

Setting up multi-source replication (5.7) with GTID based replication

Purpose of this guide is to show how to setup a new multi-source slave from two (or more) existing masters. We will also set up a replication filter to only apply data from some of the databases.

So the scenarios is that we have already 2 master up and running and we want filter one database from each. Lets call them master1 and master2 with databases master1 and master2 to make it easy.

First step will be to make sure that both the master databases have GTID replication enabled. Configuration (my.cnf) needed on master servers will be (restart of databases will be needed):

gtid-mode=ON log-slave-updates=ON enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE log-bin=mysql-bin binlog-format=ROW

Next step is to create a user for replication on both master servers:

mysql> CREATE USER 'repl_user'@'slave-host' IDENTIFIED BY 'repl_pass'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave-host';

Provisioning of data to new multi-source slave

1) Now it's time to provision some data to our slave, step one is to run mysqldump on master databases: 

mysqldump -u<user> -p<pass> --single-transaction --triggers --routines --set-gtid-purged=ON --databases master1 > dumpM1.sql mysqldump -u<user> -p<pass> --single-transaction --triggers --routines --set-gtid-purged=ON --databases master2 > dumpM2.sql

(we only dump database master1 from master1 and master2 from master2)

2) Get GTID_PURGED information from dump files and remember this (you need this later):

cat dumpM1.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'\'' cat dumpM2.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'\''

(should look something like: aeaeb1f9-cfab-11e9-bf5d-ec21e522bf21:1-5)

3) Now we need to remove GTID_PURGED information from dump files before import:

sed '/GTID_PURGED/d' dumpM1.sql > dumpM1_nopurge.sql sed '/GTID_PURGED/d' dumpM2.sql > dumpM2_nopurge.sql

4) Import data into new multi-source slave:

mysql -u<user> -p<pass> < dumpM1_nopurge.sql mysql -u<user> -p<pass> < dumpM2_nopurge.sql

5) Clear GTID state of slave server and set GTID_PURGED to values collected earlier in step 2):

mysql> RESET MASTER; mysql> SET GLOBAL GTID_PURGED="<Master1 GTID_PURGED>,<Master2 GTID_PURGED>";

Configure and start multi-source replication

1) Now it's time to configure the replication channels and set the filter rule
    (filters on slave will be for all channels):

mysql> CHANGE MASTER TO MASTER_HOST=<master1-host>, MASTER_USER="repl", MASTER_PASSWORD="repl", MASTER_AUTO_POSITION=1 FOR CHANNEL "master1"; mysql> CHANGE MASTER TO MASTER_HOST=<master2-host>, MASTER_USER="repl", MASTER_PASSWORD="repl", MASTER_AUTO_POSITION=1 FOR CHANNEL "master2"; mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=('master1.%','master2.%');

2) After this we start both channels:

mysql> START SLAVE FOR CHANNEL "master1"; mysql> START SLAVE FOR CHANNEL "master2";

3) You can now looks at status with:

mysql> SHOW SLAVE STATUS FOR CHANNEL "master1"\G mysql> SHOW SLAVE STATUS FOR CHANNEL "master2"\G

Setup your own multi-source sandbox environment

If you want to play around with Multi-Source replication and want to jumpstart a sandbox environment then you might want to look at my workshop at GitHub.

MySQL Error: Too many connections!

When your application get error "too many connections" underlying problem might be caused by multiple things. For a in-depth understanding of how MySQL handles connections read this blog.

In this blog we will investigate why and how to solve these problems when running MySQL installed on Debian/Ubuntu using systemd (default MySQL packages).

OS: Debian 9
MySQL Server version: 5.7.25 MySQL Community Server (GPL)
(will most likely be the same for MySQL 8.0 versions)

Goal is to have 10.000 working connections to MySQL!

The default value for max_connections is 151 connections so first step is to increase the max_connections variable to 10.000.
This is documented in the manuals here:
- https://dev.mysql.com/doc/refman/5.7/en/too-many-connections.html
- https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connections

The max_connections is a dynamic setting so lets increase value to 10.000 and see what happens.

root@debian:~# mysql -uroot -proot -se "select @@max_connections" @@max_connections 214

Hmmm, it looks like we only got 214 connections, lets look at the error log:

2019-04-01T06:29:48.301871Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 50000) 2019-04-01T06:29:48.302000Z 0 [Warning] Changed limits: max_connections: 214 (requested 10000) 2019-04-01T06:29:48.302004Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 2000)

Looks like we hit some resource limit.
Lets look in the MySQL manual for running MySQL under systemd, that can be found here.
Looks like we need to increase the number of allowed open files for MySQL, locate the systemd configuration folder for MySQL and create file /etc/systemd/system/mysqld.service.d/override.conf (file can be called anything ending with .conf). You can also create/modify the override file by using native systemctl command like: systemctl edit mysql

Add LimitNOFILE=10000 in file override.conf like:

root@debian:~# cat /etc/systemd/system/mysql.service.d/override.conf [Service] LimitNOFILE=10000

After this we need to reload the systmed daemon and restart the MySQL service like:

root@debian:~# systemctl daemon-reload root@debian:~# systemctl restart mysql

MySQL is now saying we have 9190 connections:

root@debian:~# mysql -uroot -proot -se "select @@max_connections" @@max_connections 9190

So, MySQL is using some files for additional work and we need to set this a bit higher to get 10.000 connections, lets set it to 11.000 and reload the systemd daemon and restart the MySQL service.

root@debian:~# mysql -uroot -proot -se "select @@max_connections" mysql: [Warning] Using a password on the command line interface can be insecure. @@max_connections 10000

Good, now we have 10.000 connections available according to MySQL.

Lets run our application and verify we can get 10.000 connections, I use this small perl script to open 10.000 connections.

Just below 5.000 connection I get a new error in the application "Can't create a new thread (errno 11)"
Lets have a look at the MySQL error log:

root@debian:~# tail -1 /var/log/mysql/error.log 2019-04-01T06:50:35.657397Z 0 [ERROR] Can't create thread to handle new connection(errno= 11)

I found this new limit by running command below when perl script was running:
watch -n.5 "mysql -uroot -proot -se'show status like \"%threads%\"'"

Strange, where is this new limitation just below 5.000 connections coming from?

Looking at resource limits for my MySQL daemon I should have close to 8000 processes:

root@debian:~# cat /proc/$( pgrep -o mysql )/limits Max processes             7929                 7929                 processes Max open files            11000                11000                files

Lets looks at status report for my MySQL service:

root@debian:~# systemctl status mysql | grep Tasks     Tasks: 127 (limit: 4915)

There seem to be some additional limit on Tasks that limit me to 4915 connections.
Lets expand our override.conf configuration to cover for 11.000 tasks also.

root@debian:~# cat /etc/systemd/system/mysql.service.d/ [Service] LimitNOFILE=11000 TasksMax=11000

(remember to reload systemd and restart MySQL service after each change in override.conf)

Now we got just under 8.000 connection and got the same error " Can't create a new thread (errno 11)" but this time it's because of the limit of max processes:

root@debian:~# cat /proc/$( pgrep -o mysql )/limits Max processes             7929                 7929                 processes

Lets increase this limit to 11.000 in our override.conf:

root@debian:~# cat /etc/systemd/system/mysql.service.d/override.conf [Service] LimitNOFILE=11000 LimitNPROC=11000 TasksMax=11000

After reloading systemd configuration and restarting MySQL service I can now get 10.000 connections and the perl script runs without any errors!

Summary:
There are different limits when setting max_connections in MySQL:
- The default max connections is 151.
- At 214 connections you are limited by max open files.
- At 4915 you are limited by Max Tasks (systemd)
- Just below 8000 you are limited by max number of processes

By adding a override file (as showed above) you can overcome all these limits.

Remember to:
Look at error message in application and MySQL error logs.
Look at output from: cat /proc/$( pgrep -o mysql )/limits
Look at output from: systemctl status mysql
Test your application (or use my perl script) and monitor that it works!
Monitor how many connections you have: mysql -uroot -p -e'show status like "Threads_connected"'