MySQL Multi Source Replication

Last week, durring MySQL Connect, MySQL 5.7.2 DMR was launched, one of the new functionality is the multi source replication. At the moment, MySQL can have only one master per slave (you can archive multi source replication via some hacks, but like the name says, it’s a hack).

See how to configure here

To clarify, there is a difference between multi-master replication and multi source replication, see the bellow pictures to understand the difference:

MySQL Multi Master Replication

MySQL Multi Master Replication


Multi Master Replication – In the above picture, we have 2 master’s and 1 slave, where, master 1 is master of master 2, master 2 is master of master 1 and also of slave, in order to slave receive updates from master 1, all this updates must past to master 2 to later one be replied to slave.
MySQL Multi Source Replication

MySQL Multi Source Replication


Multi Source Replication – In the above picture, we have 2 master’s and 1 slave, master 1 is only master of slave, and master 2 is only master of slave. The slave receive updates from both master’s directly.

In the next post, I’ll explain how to configure 1 slave and 2 masters in multi source replication.
The test version can be downloades via labs.mysql.com, go there, download and try for yourself.
Remember, this is a test version, so, don’t use in production !!!

MySQL force user change password

Since version 5.6.6 MySQL has introduced the capability to force an user to change their password.
You can archive that by issue ALTER USER PASSWORD EXPIRE statement.

ALTER USER 'marcelo'@'localhost' PASSWORD EXPIRE;

Next time marcelo tries to login from localhost it will block all statements rather then SET PASSWORD;

mysql> \s
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

To remove this block, just issue an :

SET PASSWORD = PASSWORD('pwd');
#Or use the hash received from SELECT PASSWORD('pwd');
SET PASSWORD = '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD';

But be careful, if you expire the password for an account, you won’t be able to login with any client version before 5.6.10(GA):

ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

As Peter Zaitsev wrote on his post in some rpm installation, MySQL install the root user with a temporary password and force you to change it in the next login.

More information can be find at MySQL Password Expiration Documentation and MySQL ALTER USER Documentation

Why Does MySQL Replication Delays?

These days I’ve answer some questions about replication lag, and I realized that most of people does not correctly understand how this process works internally on MySQL, and why does delays happen:

See the bellow image, it represents asynchronous replication on MySQL, I highly recommend you to read my other post:

“How Does MySQL Replication Works?”

How replication works

How you can see, there is a big difference related to the entry point of data on master and slave, while the master has multiple simultaneously threads inserting/updating/deleting data, on the slave is just a single one responsible for deal with all these transactions

Let’s imagine that one transaction (an UPDATE for example) took 50 seconds to be applied on the master, during this time, others threads executed queries on the master, what is gonna happen when the slave receive and execute this transaction? yes, you are right, DELAY, all other pending transaction and the ones which got inserted while the slave is applying this UPDATE will need to wait until it finish to be applied.

If you use MySQL from version 5.6.3 and have your data partitioned per database, you can minimize this “bottleneck” using slave-parallel-workers, with this option you can config the number of SQL_THREAD'S to execute simultaneously transaction since they are in different databases.

How Does MySQL Replication Works?

See the bellow image, it represents how asynchronous replication works on MySQL
How replication works

Bearing in mind the number on the image, let’s see how the process works:

  • Item 1 in the image represents the clients executing queries on master, note that the master is capable to handle multiple simultaneous connections (it can be configurable by max_connections variable). 
  • Master process these queries, and save it to his binary log(item number 2 in the image), then it can later on be replied on the slave.
  • The slave has 2 threads reubuntu mount ext4 partitionsponsible to deal with replication :

    • IO_THREAD – is responsible to connect to the Master, ask for new transaction (item number 3 in the image) and save them to his own log (relay log, item number 4 in the image).

    • SQL_THREAD – is responsible for read all new transaction from relay log(item number 5 in the image), and apply them to the database server(item number 6 in the image) .

Got a packet bigger than ‘slave_max_allowed_packet’ bytes and binlog_format = STATEMENT | MIXED

Got a packet bigger than ‘slave_max_allowed_packet’ bytes and binlog_format=STATEMENT|MIXED

Since version 5.1.64 MySQL introduces a new variable named slave_max_allowed_packet, which was introduced to allow large updates using row-based replication do not cause replication to fail when exceeded max_allowed_packet.

The problem is if you have you replication using binlog_format=STATEMENT or binlog_format=MIXED it ignores this option and use as limit for queries what is on max_allowed_packet variable but still reporting on slave_max_allowed_packet causing the IO_THREAD to report the wrong message.

Solution:
Run the follow query on master:

master> SHOW VARIABLES LIKE 'binlog_format';

If it return STATEMENT or MIXED, you need to change the value for max_allowed_packet, a good option if possible is to have this variable equally configured on master and slave.

http://bugs.mysql.com/bug.php?id=68490

http://bugs.mysql.com/bug.php?id=69104

MySQL 5.6 Replication with GTID – Global Transaction ID

Hi guys, Early February Oracle released the new version of MySQL named 5.6, one of the enhancements is the GTID (Global Transaction ID)

GTID is an unique identifier which will be added at each transaction, and will be very useful on the slave. remember before we needed to set MASTER_LOG_FILE and MASTER_LOG_POS, now we don’t need them anymore.

Let’s see some new variables which we need to add to our cnf file:
gtid-mode : It will enable GTID, in order to this function work, we need to turn on log-bin and log-slave-updates
enforce-gtid-consistency : It will guarantee that only allowed command will be executed ( more information here)

Basicly, is only this what we need to enable GTID, for this tutorial I will use 2 virtual machines, black (master – 192.168.1.110) and white (slave – 192.168.1.111), see bellow my cnf file:

black.cnf

[mysqld]
...
binlog-format=MIXED
log-bin
log-slave-updates=true
gtid-mode=on 
enforce-gtid-consistency=true 
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
binlog-checksum=CRC32
master-verify-checksum=1
server-id=1
report-host=black

white.cnf

[mysqld]
...
binlog-format=MIXED
log-slave-updates=true
log-bin
gtid-mode=on 
enforce-gtid-consistency=true 
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=2
report-host=white

Next steep, we need to create the replication user on master (black):

GRANT REPLICATION SLAVE ON *.* TO replication@192.168.1.111 IDENTIFIED BY 'reppwd';
FLUSH PRIVILEGES;

In case you serve already has data, you can follow this post to create a backup from your master, note that now when we raw the SHOW MASTER STATUS; we see a new field named Executed_Gtid_Set, and then restore the backup on slave

Let’s start the replication, the very good stuff in here is that we don’t need to worry about the binary log and position anymore, is just simple as that with GTID:

CHANGE MASTER TO MASTER_HOST='192.168.1.110', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;

At this point, you should have your replication running with GTID, lets see :

white>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.110
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: black-bin.000002
          Read_Master_Log_Pos: 936
               Relay_Log_File: white-relay-bin.000003
                Relay_Log_Pos: 1146
        Relay_Master_Log_File: black-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 936
              Relay_Log_Space: 1560
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 2671c08b-7cf0-11e2-ac39-00163ebee7c2
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 2671c08b-7cf0-11e2-ac39-00163ebee7c2:1-4
            Executed_Gtid_Set: 2671c08b-7cf0-11e2-ac39-00163ebee7c2:1-4
                Auto_Position: 1
1 row in set (0.00 sec)

That Retrieved_Gtid_Set and Executed_Gtid_Set displays the transaction that we read from master, and the transaction that we already executed.

Let’s check if the commands executed on master are replied on slave:

black> CREATE DATABASE gtid_test;
Query OK, 1 row affected (0.00 sec)

black> use gtid_test;
Database changed
black> CREATE TABLE test (id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

black> INSERT INTO test (id) VALUES (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

black> 

Now let’s check it on slave:

white> USE gtid_test;
Database changed
white> SELECT * FROM test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

white>

Done, we have our replication running with Global Transaction ID (GTID)

Consistent backup – snapshot

Hi guys, today let’s learn about how to have a consistent backup (snapshot)
First of all, in what situations do we use a snapshot?

1. Lets say that your production server now will have a replica, how do you do the first load of data in this slave? what was the master bin log position when you started the backup, during the backup process, does anyone wrote any query to db?

2. In case you want to implement an incremental backup strategy, you can take a snapshot once a week and in case you need to restore you server, you just restore the snapshot and apply the binary logs.

Then, let’s start.

To grantee this data integrity we will need 2 sessions open on master, first one to lock all databases, second one to do the copy

Then, let’s go:

Session 1:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

You will receive an output like this:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

IMPORTANT: LEAVE THIS SESSION OPEN DURING ALL PROCESS, IF YOU CLOSE THIS SESSION, THE DATABASE LOCK WILL BE RELEASED

To do the backup, we will use mysqldump:
Session 2:

mysqldump -u usuario -p --all-databases > all_dbs.sql

After the backup is completed, make sure it ends with:
— Dump completed on xxxx-xx-xx xx:xx:xx

Done, we now ca release the lock on session 1:

UNLOCK TABLES;

Is this, we have a snapshot of our database.

MySQL Replication with SSL

Hi folks, let’s continue talk about replication, you can see the basics in this other post

First of all, lets create the ssl certificates, go to the master server:
Create CA certificate:

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

Create server certificate:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Create client certificate:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

copy ca-cert.pem, client-req.pem, client-cert.pem to slave server

Adjust the cnf file:

ssl
ssl-ca=/etc/mysql/sslcerts/ca-cert.pem
ssl-cert=/etc/mysql/sslcerts/server-cert.pem
ssl-key=/etc/mysql/sslcerts/server-key.pem

Make sure you restart the MySQL service, and check if the ssl is enabled

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        | /etc/mysql/sslcerts/ca-cert.pem     |
| ssl_capath    |                                     |
| ssl_cert      | /etc/mysql/sslcerts/server-cert.pem |
| ssl_cipher    |                                     |
| ssl_key       | /etc/mysql/sslcerts/server-KEY.pem  |
+---------------+-------------------------------------+
7 rows IN SET (0.00 sec)

Then, we will need a replication user, and we will specify that this user require ssl:

GRANT REPLICATION SLAVE ON *.* TO 'user'@'178.77.90.38' IDENTIFIED BY 'PASSWORD' REQUIRE SSL;
FLUSH PRIVILEGES;

Ok, we have finish your work on master, let’s work on slave server:

Adjust the cnf file:

ssl
ssl-ca=/etc/mysql/sslcerts/ca-cert.pem
ssl-cert=/etc/mysql/sslcerts/client-cert.pem
ssl-key=/etc/mysql/sslcerts/client-key.pem

Let’s check if is everythink ok with ssl

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        | /etc/mysql/sslcerts/ca-cert.pem     |
| ssl_capath    |                                     |
| ssl_cert      | /etc/mysql/sslcerts/client-cert.pem |
| ssl_cipher    |                                     |
| ssl_key       | /etc/mysql/sslcerts/client-KEY.pem  |
+---------------+-------------------------------------+
7 rows IN SET (0.00 sec)

Finally, Start the replica:

CHANGE MASTER TO MASTER_HOST='xxxxxx', MASTER_USER='user', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/sslcerts/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/sslcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/sslcerts/client-key.pem';
START SLAVE;

How to rename table in MYSQL

To rename a table in MySQL you just need to run a command named RENAME TABLE, the syntax is very easy to use,

RENAME TABLE tb1 TO tb2;

The RENAME TABLE command will rename the table atomically, which means your table will be locked during the command.
You can also rename more than one table in one command:

RENAME TABLE tb1 TO tb2, tb3 TO tb4;

To rename the table you will need ALTER and DROP privileges on the old table and CREATE and INSERT on the new one.

You just need to be careful if you use triggers, on the bellow example, after a INSERT on table Cities, I count +1 to NOfCities on the iserted Countries, what happens if I rename the table Contries?

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: PlusOneCities
               Event: INSERT
               Table: Cities
           Statement: UPDATE Countries SET NOfCities = NOfCities + 1 WHERE Code = NEW.CountryCode
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> INSERT INTO Cities SET Name = 'City1', CountryCode = 'BRA', District = 'Brasilia', Population = 10000;
Query OK, 1 row affected (0.04 sec)

mysql> RENAME TABLE Countries TO Country;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO Cities SET Name = 'City2', CountryCode = 'BRA', District = 'Brasilia', Population = 10000;
ERROR 1146 (42S02): Table 'world.Countries' doesn't exist

See, we need to manualy change all triggers.

You can find more information on MySQL Documentation

How to use IN – AND – OR operators in MySQL

MySQL has 3 very useful operators named IN –  OR – AND. They behave very differently and is very common to misuse them.
When I use one and when I use the other?

we will use the city table to this tutorial:

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

Let’s see a simple rule to clarify this:

MySQL AND Operator

We use the AND operator to add a new condition which will need to be true:

SELECT * FROM City WHERE CountryCode = 'BRA' AND Population > 1000000 AND District LIKE 'São Paulo';

This query will return Cities which CountryCode is BRA and Population is bigger than 1000000 and Discrict is São Paulo, this three condition need to be true.

MySQL OR Operator

We use the OR operator to add a new condition which or one or other needs to be true:

SELECT * FROM City WHERE Population > 1000000 OR District LIKE 'São Paulo';

This query will return Cities which have a population bigger than 1000000 or have São Paulo as their district .

MySQL IN Operator

We use the IN operator the same way which we use the OR operator but for the same column:

SELECT * FROM City WHERE CountryCode = 'USA' OR CountryCode = 'BRA' OR CountryCode = 'IRL';
SELECT * FROM City WHERE CountryCode IN ('USA', 'BRA', 'IRL');

Both queries will return the same result, all cities which are in USA, BRA or IRL.