Why Does MySQL Replication Delays?

Sunday May 12th, 2013 by marceloaltmann | Leave a comment

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 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?

Sunday May 12th, 2013 by marceloaltmann | Leave a comment

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

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

Wednesday May 1st, 2013 by marceloaltmann | Leave a comment

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

Friday February 22nd, 2013 by marceloaltmann | Leave a comment

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

Wednesday February 6th, 2013 by marceloaltmann | Leave a comment

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

Thursday January 31st, 2013 by marceloaltmann | Leave a comment

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

Thursday November 8th, 2012 by marceloaltmann | Leave a comment

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

MySQL IN AND OR

Saturday November 3rd, 2012 by marceloaltmann | Leave a comment

MySQL IN??? MySQL OR??? MySQL AND???
When I use one and when I use 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:

AND – we use AND 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.

OR – we use OR 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 .
IN – we use in the same way which we use OR 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');

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

Recording session on MySQL

Tuesday October 30th, 2012 by marceloaltmann | Leave a comment

Hi guys, today let’s learn about a very useful option on mysql command line to recording all what happen in your mysql session, It’s the option –tee. Is very easy to use, you just need to add the –tee=/path/to/file

mysql -u root -p --tee=/tmp/log.mysql

It is all for today!
Enjoy!!!

Date format in MySQL

Wednesday October 17th, 2012 by marceloaltmann | Leave a comment

Hi guys, today let’s talk about how to format date in MySQL
For this tutorial I’m using mysql server 5.5
Basically you need to know how to use a function called DATE_FORMAT
Let’s create a table and insert some rows:

CREATE TABLE IF NOT EXISTS `dates` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `dates` (`id`, `date`) VALUES
(1, '2012-10-17 13:39:55'),
(2, '2012-10-09 09:23:11'),
(3, '2012-08-24 16:47:07');

OK? then let’s select this values and show it formated

SELECT id, DATE_FORMAT( `date` , '%d/%c/%Y %H:%i:%s' ) AS `date` FROM `dates`

It’s easy, just remember which the format is always %ONE_LETTER, where this letter is the field which you want to show (see the complete documentation)