Menu Close

Category: replication

MySQL Fabric – Part 1 – Installing

MySQL Fabric is a tool included on MySQL Utilities that helps you to manage your MySQL instances. It works by basically adding a new layer between your application and MySQL instances, which can provide an easy way to use sharding and build a highly available system. To install our Fabric environment, we will have to configure 4 servers, I will use the follow names and IP on this tutorial: fabric1 (192.168.0.200) – fabric mysql1 (192.168.0.201) – mysql master mysql2 (192.168.0.202) – mysql slave mysql3 (192.168.0.203) – mysql slave Note: I’m running CentOS 6.5 on all servers. 1. Add mysql repo on all 4 machines, please read Installing latest version of MySQL via yum for more info: rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm yum update 2. Install mysql mysql-server mysql-utilities: yum install mysql mysql-server . . .

Liked? Help and Share!

MySQL Sandbox

Hi there, today we will learn about an amazing tool that every single MySQL dba must know, I’m talking about MySQL Sandbox. MySQL Sandbox is developed by Giuseppe Maxia (The Data Charmer), it’s a tool that make the installation of MySQL servers very easy. If you need to quickly create a MySQL instance for test or a replication setup(it supports master slave, circular and master master replication), this it the tool. INSTALLATION: Go to http://mysqlsandbox.net/ and get the latest version (I got from launchpad): yum install perl perl-ExtUtils-MakeMaker perl-Test-Simple wget https://launchpad.net/mysql-sandbox/mysql-sandbox-3/mysql-sandbox-3/+download/MySQL-Sandbox-3.0.44.tar.gz tar -zxvf MySQL-Sandbox-3.0.44.tar.gz cd MySQL-Sandbox-3.0.44 perl Makefile.PL make make test make install CREATING A SINGLE SANDBOX: To create a single sandbox, all you need is the mysql package that you want install and the make_sandbox command: [root@localhost ~]# make_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz unpacking /root/mysql-5.6.17-linux-glibc2.5-i686.tar.gz Executing . . .

Liked? Help and Share!

Multi-Source Replication with MySQL 5.7 – example

Complementing this previous post, today we are going to configure 1 slave receiving updates from 2 master with Multi-source replication. As mention before, this feature is only available on labs.mysql.com. To configure it, is very simple, we are going to nees 2 masters with GTID enabled (see this post to know how to configure) and a slave with crash save options enabled. Master 1 e 2: gtid-mode=on enforce-gtid-consistency Slave master_info_repository=TABLE relay_log_info_repository=TABLE gtid-mode=on enforce-gtid-consistency Let’s first create our replication user: master1 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’127.0.0.1’ IDENTIFIED BY ‘123’; master2 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’127.0.0.1’ IDENTIFIED BY ‘123’; Now we are going to configure our slave normally, there is a new option named FOR CHANEL, that will identify which . . .

Liked? Help and Share!

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: 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 . . .

Liked? Help and Share!

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: 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 . . .

Liked? Help and Share!

How Does MySQL Replication Works?

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). . . .

Liked? Help and Share!

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 Liked? Help and Share!

Liked? Help and Share!

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 – . . .

Liked? Help and Share!

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

Liked? Help and Share!

MySQL replication – Master – Slave

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, . . .

Liked? Help and Share!