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: MySQL replication: most important config parameters for performance on slave server? mysql replication delay very serious 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 . . .
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). . . .
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 Liked? Help and Share!
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 – . . .
To explain the replication, we will use the article Installing MySQL 5.5 Part 2 – Multiple instances with mysqld_multi because we will need 2 MySQL’s running on our server Let’s activate the binary logs on our server, creating a file named my_rep.cnf with the follow content: [mysqld_multi] mysqld = /mysql/mysql/bin/mysqld_safe mysqladmin = /mysql/mysql/bin/mysqladmin [mysqld1] port = 3306 datadir = /mysql/3306/data socket = /mysql/3306/mysql.sock log-error = /mysql/3306/logs/mysqld.log pid-file = /mysql/3306/mysqld.pid server_id = 1 #LOGS log-bin = /mysql/3306/logs/server1_bin.log log-bin-index = /mysql/3306/logs/server1_log-bin.index expire_logs_days = 7 binlog-format = MIXED [mysqld2] port = 3307 datadir = /mysql/3307/data socket = /mysql/3307/mysql.sock log-error = /mysql/3307/logs/mysqld.log pid-file = /mysql/3307/mysqld.pid server_id = 2 #LOGS relay-log = /mysql/3307/logs/server2.relay_log relay-log-index = /mysql/3307/logs/server2.relay_log_index expire_logs_days = 7 Done this, let’s start the MySQL service like our article: cd /mysql/mysql mysqld_multi –defaults-file=/mysql/my_rep.cnf start Now, . . .