Replication of mysql databases. Setting up mysql database replication. What is mariadb galera

I became acquainted with the replication of MySQL servers relatively recently, and as I carried out various experiments with the configuration, I wrote down what worked for me. When I had collected quite a lot of material, the idea came up to write this article. I've tried to collect tips and solutions to some of the most basic issues I've encountered. I'll provide links to documentation and other sources along the way. I can’t pretend to describe it completely, but I hope the article will be useful.

A short introduction

Replication (from the Latin replico - I repeat) is the replication of data changes from the main database server to one or more dependent servers. We will call the main server master, and dependent - replicas.
Data changes that occur on the master are repeated on the replicas (but not vice versa). Therefore, queries to change data (INSERT, UPDATE, DELETE, etc.) are executed only on the master, while queries to read data (in other words, SELECT) can be executed on both replicas and the master. The replication process on one of the replicas does not affect the operation of other replicas, and practically does not affect the work of the master.
Replication is performed using binary logs maintained on the master. They store all queries that lead (or potentially lead) to changes in the database (queries are not saved explicitly, so if you want to look at them, you will have to use the mysqlbinlog utility). The binlogs are transferred to the replicas (the binlog downloaded from the master is called a "relay binlog") and the stored queries are executed starting from a certain position. It is important to understand that during replication, it is not the changed data itself that is transferred, but only the requests that cause the changes.
With replication, the contents of the database are duplicated on several servers. Why is it necessary to resort to duplication? There are several reasons:

• performance and scalability. One server may not be able to handle the load caused by simultaneous read and write operations in the database. The benefits of creating replicas will be greater the more reads per write you have on your system.
• fault tolerance. In the event of a replica failure, all read requests can be safely transferred to the master. If the master fails, write requests can be transferred to the replica (after the master is restored, it can take over the role of the replica).
• data backup. The replica can be “slowed down” for a while to perform mysqldump, but the master cannot.
• deferred calculations. Heavy and slow SQL queries can be executed on a separate replica without fear of interfering with the normal operation of the entire system.

Additionally, there are some other interesting features. Since it is not the data itself that is transferred to the replicas, but the queries that cause them to change, we can use different table structures on the master and replicas. In particular, the type of table (engine) or set of indexes may differ. For example, to perform full-text search, we can use the MyISAM table type on the replica, despite the fact that the master will use InnoDB.

Setting up replication

Let's say we have a working MySQL database, already filled with data and turned on. And for one of the reasons described above, we are going to enable replication of our server. Our initial data:

• The master IP address is 192.168.1.101, the replicas are 192.168.1.102.
• MySQL installed and configured
• you need to configure testdb database replication
• we can pause the wizard for a while
• we of course have root on both machines

Wizard settings

Be sure to indicate the unique server ID, the path for binary logs and the name of the database for replication in the section:
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
replicate-do-db = testdb
Make sure you have enough disk space for binary logs.

Let's add the replication user, under whose rights replication will be performed. The "replication slave" privilege will suffice:
mysql@master> GRANT replication slave ON "testdb".* TO "replication"@"192.168.1.102" IDENTIFIED BY "password";

Reboot MySQL for the changes in the config to take effect:
root@master# service mysqld restart

If everything went well, the "show master status" command should show something like this:
mysql@master>SHOW MASTER STATUS\G
File: mysql-bin.000003
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:
The position value should increase as changes are made to the database on the master.

Replica settings

Let's specify the server ID, the name of the database for replication and the path to the relay binlogs in the config section, then reload MySQL:
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
replicate-do-db = testdb

Root@replica# service mysqld restart

Transferring data

Here we will have to lock the database for writing. To do this, you can either stop the applications or use the read_only flag on the master (attention: this flag has no effect on users with the SUPER privilege). If we have MyISAM tables, let's also "flush tables":
mysql@master> FLUSH TABLES WITH READ LOCK;
mysql@master> SET GLOBAL read_only = ON;

Let’s see the master’s status with the “show master status” command and remember the File and Position values ​​(after successfully blocking the master, they should not change):
File: mysql-bin.000003
Position: 98

We dump the database, and after the operation is completed, we remove the master’s lock:
mysql@master> SET GLOBAL read_only = OFF;

We transfer the dump to the replica and restore data from it.
Finally, we start replication with the commands “change master to” and “start slave” and see if everything went well:
mysql@replica> CHANGE MASTER TO MASTER_HOST = "192.168.1.101", MASTER_USER = "replication", MASTER_PASSWORD = "password", MASTER_LOG_FILE = "mysql-bin.000003", MASTER_LOG_POS = 98;
mysql@replica> start slave;
We take the values ​​of MASTER_LOG_FILE and MASTER_LOG_POS from the master.

Let's see how replication is going with the "show slave status" command:
mysql@replica> SHOW SLAVE STATUS\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 98
Relay_Log_File: mysql-relay-bin.001152
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: testdb,testdb
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: 98
Relay_Log_Space: 235
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: 5

I have highlighted the most interesting values ​​now. If replication starts successfully, their values ​​should be approximately the same as in the listing (see the description of the "show slave status" command in the documentation). The Seconds_Behind_Master value can be any integer.
If replication is normal, the replica will follow the master (the log number in Master_Log_File and the Exec_Master_Log_Pos position will increase). The lag time of the replica from the master (Seconds_Behind_Master), ideally, should be equal to zero. If it does not decrease or grows, it is possible that the load on the replica is too high - it simply does not have time to repeat the changes occurring on the master.
If Slave_IO_State is empty and Seconds_Behind_Master is NULL, replication has not started. See the MySQL log to find out the reason, eliminate it and restart replication:
mysql@replica> start slave;

Through these simple steps we get a replica whose data is identical to the data on the master.
By the way, the time the master is blocked is the time the dump is created. If it takes an unacceptably long time to create, you can try this:

• block writing to the master with the read_only flag, remember the position and stop MySQL.
• after that, copy the database files to the replica and enable the master.
• start replication in the usual way.

There are several ways to create a replica without stopping the master at all, but they do not always work.

Adding replicas

Suppose we already have a working master and a replica, and we need to add one more to them. This is even easier to do than adding the first replica to the master. And what’s much nicer is that there is no need to stop the master for this.
First, let's configure MySQL on the second replica and make sure that we have entered the necessary parameters into the config:
server-id = 3
replicate-do-db = testdb

Now let's stop replication on the first replica:
mysql@replica-1> stop slave;

The replica will continue to work normally, but the data on it will no longer be current. Let's look at the status and remember the master position that the replica reached before stopping replication:
mysql@replica-1> SHOW SLAVE STATUS\G

The values ​​we need will be Master_Log_File and Exec_Master_Log_Pos:
Master_Log_File: mysql-bin.000004
Exec_Master_Log_Pos: 155

Let's create a database dump and continue replication on the first replica:
mysql@replica-1> START SLAVE;

Let's restore the data from the dump on the second replica. Then enable replication:
mysql@replica-2> CHANGE MASTER TO MASTER_HOST = "192.168.1.101", MASTER_USER = "replication", MASTER_PASSWORD = "password", MASTER_LOG_FILE = "mysql-bin.000004", MASTER_LOG_POS = 155;
mysql@replica-2> START SLAVE;

The MASTER_LOG_FILE and MASTER_LOG_POS values ​​are the Master_Log_File and Exec_Master_Log_Pos values, respectively, from the result of the “show slave status” command on the first replica.
Replication must begin from the position where the first replica was stopped (and, accordingly, a dump is created). Thus, we will have two replicas with identical data.

Merging replicas

Sometimes the following situation arises: there are two databases on the master, one of which is replicated on one replica, and the second on another. How to set up replication of two databases on both replicas without dumping them on the master or shutting it down? Quite simply, using the "start slave until" command.
So, we have a master with databases testdb1 and testdb2, which are replicated on replicas replica-1 and replica-2, respectively. Let's configure replication of both databases to replica-1 without stopping the master.
Stop replication on replica-2 with the command and remember the position of the master:
mysql@replica-2> STOP SLAVE;
mysql@replica-2> SHOW SLAVE STATUS\G
Master_Log_File: mysql-bin.000015
Exec_Master_Log_Pos: 231

Let's create a dump of the testdb2 database and resume replication (this completes the manipulations with replica-2). We will restore the dump to replica-1.

The situation on replica-1 is this: the testdb1 database is at one master position and continues to replicate, the testdb2 database has been restored from a dump from another position. Let's synchronize them.

Let's stop replication and remember the position of the master:
mysql@replica-1> STOP SLAVE;
mysql@replica-1> SHOW SLAVE STATUS\G
Exec_Master_Log_Pos: 501

Let's make sure that in the config for replica-1 the name of the second database is indicated in the section:
replicate-do-db = testdb2

Let's reboot MySQL for the changes in the config to take effect. By the way, it was possible to simply restart MySQL without stopping replication - from the log we would know at what position in the master replication stopped.

Now let's replicate from the position where replica-2 was paused to the position where we just paused replication:
mysql@replica-1> CHANGE MASTER TO MASTER_HOST = "192.168.1.101", MASTER_USER = "replication", MASTER_PASSWORD = "password", MASTER_LOG_FILE = "mysql-bin.000015", MASTER_LOG_POS = 231;
mysql@replica-1> start slave until MASTER_LOG_FILE = "mysql-bin.000016 ", MASTER_LOG_POS = 501;

Replication will end as soon as the replica reaches the specified position in the until section, after which both of our databases will correspond to the same master position (at which we stopped replication on replica-1). Let's make sure of this:
mysql@replica-1> SHOW SLAVE STATUS\G
mysql@replica-1> START SLAVE;
Master_Log_File: mysql-bin.000016
Exec_Master_Log_Pos: 501

Let's add the names of both databases to the config for replica-1 in the section:
replicate-do-db = testdb1
replicate-do-db = testdb2

Important: each database must be listed on a separate line.
Restart MySQL and continue replication:
mysql@replica-1> CHANGE MASTER TO MASTER_HOST = "192.168.1.101", MASTER_USER = "replication", MASTER_PASSWORD = "password", MASTER_LOG_FILE = "mysql-bin.000016", MASTER_LOG_POS = 501;
After replica-1 catches up with the master, the contents of their database will be identical. You can merge the database onto replica-2 either in a similar way, or by making a complete dump of replica-1.

Castling master and replica

It may be necessary to switch a replica to master mode, for example, in the event of a master failure or when carrying out technical work on it. To make such a switch possible, you need to configure the replica like the master, or make it passive master.

Let's enable binary logging (in addition to relay binlogs) in the config in the section:
log-bin = /var/lib/mysql/mysql-bin

And add a user for replication:
mysql@master> GRANT replication slave ON 'testdb'.* TO 'replication'@'192.168.1.101′ IDENTIFIED BY "password ";

The passive master carries out replication like a regular replica, but in addition it creates binary logies - that is, we can start replication from it. Let's verify this with the command "show master status":
mysql@replica> SHOW MASTER STATUS\G
File: mysql-bin.000001
Position: 61
Binlog_Do_DB:
Binlog_Ignore_DB:

Now, in order to switch the passive master to active mode, you need to stop replication on it and enable replication on the former active master. To ensure that data is not lost at the time of switching, active master must be write-locked.
mysql@master> FLUSH TABLES WITH READ LOCK
mysql@master> SET GLOBAL read_only = ON;
mysql@replica> STOP SLAVE;
mysql@replica> SHOW MASTER STATUS;
File: mysql-bin.000001
Position: 61
mysql@master> CHANGE MASTER TO MASTER_HOST = "192.168.1.102", MASTER_USER = "replication", MASTER_PASSWORD = "password", MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 61;
mysql@master> start slave;
That's it, so we changed the active master. You can remove the block from the former master.

Conclusion

We've learned a bit about how to set up replication in MySQL and perform some basic operations. Unfortunately, the following important questions remain outside the scope of this article:

• elimination of single points of failure (SPF, Single Points of Failure). When using a single MySQL server, its failure led to the failure of the entire system. When using multiple servers, the failure of any one of them will result in a system failure unless we specifically take care of this. We need to provide for handling the situation with the failure of the master and replica. One of the existing tools is MMM, however, it requires modification with a file.
• load balancing. When using multiple replicas, we would like to use a transparent balancing mechanism, especially if the performance of the replicas is uneven. Under Linux, it is possible to use a standard solution - LVS.
• changing the logic of the application. In an ideal situation, requests to read data should be sent to replicas, and requests to change data should be sent to the master. However, due to the possible lag of replicas, such a scheme is often ineffective and it is necessary to identify such read requests that still need to be executed on the master.

I hope to cover these issues in future articles.
Thank you for your attention!

Tags: Add tags

These days, the MySQL database is used almost everywhere possible. It is impossible to imagine a website that would work without MySQL. Of course, there are some exceptions, but the bulk of the market is occupied by this database system. And the most popular implementation is MariaDB. When the project is small, a single server is enough to run it, on which all services are located: web server, database server and mail server. But when the project gets larger, you may need to allocate a separate server for each service or even split one service into several servers, for example, MySQL.

In order to maintain a synchronous state of databases on all servers at the same time, you need to use replication. In this article we will look at how to configure MySQL replication using MariaDB Galera Cluster.

WHAT IS MARIADB GALERA?

MariaDB Galera is a master-master cluster system for MariaDB. Starting with MariaDB 10.1, Galera Server and MariaDB Server software come in one package, so you get all the software you need right away. At the moment, MariaDB Galera can only work with InnoDB and XtraDB database engines. One of the benefits of using replication is the addition of redundancy to the site database. If one of the databases fails, you can immediately switch to another. All servers maintain a synchronized state with each other and ensure that there are no lost transactions.

Key features of MariaDB Galera:

• Replication with constant synchronization;
• Automatic merging of nodes;
• Ability to connect multiple master nodes;
• Support for recording to any of the nodes;
• Transparent parallel replication;
• Read and write scalability, minimal latency;
• Nodes that fail are automatically disconnected from the cluster;
• You cannot block access to tables.

CONFIGURING MYSQL REPLICATION

In this tutorial we will use Ubuntu 16.04 and MariaDB version 10.1 as an example. Before you begin, completely update your system:

sudo apt-get update -y
sudo apt-get upgrade -y

Since we will be deploying our configuration to multiple nodes, we need to perform update operations on all of them. If the MariaDB database server is not already installed, you need to install it. First add the repository and its key:

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

sudo add-apt-repository "deb http://ftp.utexas.edu/mariadb/repo/10.1/ubuntu xenial main"

sudo apt-get update -y

When the package list update is complete, install MariaDB with the command:

sudo apt install mariadb-server rsync -y

We will need the rsync package to perform direct synchronization. Once the installation is complete, you need to secure the database using the mysql_secure_installation script:

sudo mysql_secure_installation

By default, guest login is allowed, there is a test database, and no password is set for the root user. All this needs to be fixed. Read more in the article. In short, you will need to answer a few questions:

Enter current password for root (enter for none):
Change the root password? n
Remove anonymous users? Y
Disallow root login remotely? Y
Remove test database and access to it? Y
Reload privilege tables now? Y

When everything is ready, you can proceed to setting up the nodes between which mysql databases will be replicated. First, let's look at setting up the first node. You can put all the settings in my.cnf, but it would be better to create a separate file for these purposes in the /etc/mysql/conf.d/ folder.

Add these lines:

binlog_format=ROW

innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

wsrep_on=ON





wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="192.168.56.101"
wsrep_node_name="Node1"

Here the address 192.168.56.101 is the address of the current node. Next, go to another server and create the same file there:

sudo vi /etc/mysql/conf.d/galera.cnf

binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.56.101,192.168.56.102"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="192.168.56.102"
wsrep_node_name="Node2"

Similarly, the node address here is 192.168.0.103. Let's focus on an example with two servers, since this is enough to demonstrate the operation of the system, and you can add another server by entering an additional IP address in the wsrep_cluster_address field. Now let’s look at what the values ​​of the main parameters mean and move on to launch:

• binlog_format- the log format in which queries will be saved, the row value indicates that binary data will be stored there;
• default-storage-engine- SQL table engine that we will use;
• innodb_autoinc_lock_mode- operating mode of the value generator AUTO_INCREMENT;
• bind-address- IP address on which the program will listen for connections, in our case all IP addresses;
• wsrep_on- enables replication;
• wsrep_provider- library with which replication will be performed;
• wsrep_cluster_name - cluster name must match on all nodes;
• wsrep_cluster_address- list of server addresses between which mysql databases will be replicated, separated by commas;
• wsrep_sst_method- transport that will be used for data transmission;
• wsrep_node_address- IP address of the current node;
• wsrep_node_name- name of the current node.

The MySQL replication setup is almost complete. The last step left before launch is setting up the firewall. First enable the iptables rule management tool in Ubuntu - UFW:

Then open the following ports:

sudo ufw allow 3306/tcp
sudo ufw allow 4444/tcp
sudo ufw allow 4567/tcp
sudo ufw allow 4568/tcp
sudo ufw allow 4567/udp

LAUNCHING MARIADB GALERA

After successfully setting up all the nodes, all we have to do is launch the Galera cluster on the first node. Before we can start the cluster, you need to make sure that the MariaDB service is stopped on all servers:

sudo galera_new_cluster

You can check whether the cluster is running and how many machines are connected to it with the command:

Now there is only one machine, now go to another server and run a node there:

sudo systemctl start mysql

You can check if the launch was successful and if there were any errors with the command:

sudo systemctl status mysql

Then, by running the same command, you will verify that the new node has been automatically added to the cluster:

mysql -u root -p -e "show status like "wsrep_cluster_size""

To check how replication works, simply create a database on the first node and see if it was actually added to all the others:

mysql -u root -p

MariaDB [(none)]> create database test_db;
MariaDB [(none)]> show databases;

mysql -u root -p

MariaDB [(none)]> show databases;

As you can see, the database actually appears automatically on another machine. Mysql data replication is working.

The term replication is used to refer to a mechanism for synchronizing multiple copies of data, which increases information security, fault tolerance and system performance. A striking example is database replication between two servers.

Master-Slave MySQL replication

In Master-Slave terminology, the master is the primary server with the database; it writes to the database, but reading is distributed between the master and slave depending on the load on the system, which increases fault tolerance and performance. In addition, thanks to this approach, a copy of the database is always at hand and can be restored if one of the servers fails.

In what situations might a slave server be needed? For example, when a large array of data arrives to be written to the database and the master server simply does not have time to read and the client has to wait for the end of the write, which can be avoided thanks to the slave server.

Situations are possible when the master server fails; in this case, the slave server takes over all the functions of the master and works alone until it is restored. The client most likely won’t notice anything, and he certainly won’t wait an hour or two or three for the technician to fix it.

Setting up replication is not at all difficult, since the mechanism was built into MySQL from the very beginning.

Setting up on the Master server

Let's start by editing the configuration file my.cnf, which is most often located at /etc/mysql/my.cnf. You need to find and uncomment (remove #), or write such lines.

Bind-address = 0.0.0.0 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log

Important! If bind-address has already been registered, it needs to be changed, otherwise it will not be possible to establish a connection between the servers.

Immediately after this, we will restart the database on the server.

/etc/init.d/mysql restart

Now we need to create a user with rights to replicate our database; this can be done from root in the MySQL console using the command

GRANT REPLICATION SLAVE ON *.* TO "slave_user"@"%" IDENTIFIED BY "slave_password"; FLUSH PRIVILEGES;

Where instead of “slave_user” and “slave_password” you need to write the login and password for the slave.

Now let's look at the master data

SHOW MASTER STATUS;

Column values File And Position you need to remember, they will be used in setting up the slave, which is what we are moving on to now.

Setting up on the Slave server

The first step is to create a database with the same name as the one we are going to replicate. This is an important step and should not be neglected. Next, go to the configuration file that is already familiar to us my.cnf and write the settings.

Server-id = 2 relay-log = /var/log/mysql/mysql-relay-bin.log bin-log = /var/log/mysql/mysql-bin.log

Important! In bin-log the path to the bin-log is written on the mester server . The server ID must be different from the master ID, it is convenient to set it to 1 more.

CHANGE MASTER TO MASTER_HOST="1.1.1.1", MASTER_USER="slave_user", MASTER_PASSWORD="slave_password", MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 107; START SLAVE;

Where host is the master’s IP address, login and password correspond to those we created on the master, master_log_file and master_log_pos are filled with information from the last item for configuring the master server .

From this very moment, all changes in the database will be transferred from the master to the slave.

Checking replication status

In addition to the SHOW MASTER STATUS command; There is a similar one for the slave SHOW SLAVE STATUS\G, which will display a table with information. The main sign that the servers have connected and are working correctly is the presence of such lines

Data replication mysql allows you to have an exact copy of the database from one server - the master server (leading server) on one or more other servers (slave server). By default, Mysql replication is asynchronous.
Which means that the master server has no control and does not know whether the slave servers are reading the log file and whether they are doing it correctly.
There are also other types of synchronization, synchronous and semi-synchronous, where these processes are controlled.
Depending on the settings, you can replicate both entire databases and individual database tables.

What can you use replication for:
1. Load distribution between hosts to improve performance.
In such a scheme, the master node will perform read and write operations, nodes that have a subscription to the master node will provide a base for reading, thus we will relieve the master server from read operations
2. Data security and ease of maintenance, since the slave node contains read-only data, changes to the data on the subscriber will be limited, ease of maintenance - the ability to run processes serving the database without interrupting the operation of applications
3. Distribution of data over long distances. You can create a copy of data on any host, regardless of its location
mysql supports the following replication methods:
Traditional - the method is based on replication of events from the master's binary log file and requires log files. The positions between the master and slave servers must be synchronized.
Method Using Global Transaction Identifiers GTIDs (Transactional Method)
mysql supports the following types of synchronization:
asynchronous (one-way synchronization)
semi-synchronous (partial control of subscribers)
synchronous (full control of subscribers)

Setting up Mysql database replication traditional method

Principle of operation
Master server contains bin log files, which record all changes occurring in the master database, a file describing the names bin files, as well as the position in the log where the last master data was recorded
The slave node receives data from the master having information about the names bin files and position in the log file.

Wizard setup
my.ini must contain a unique identifier - a number from 1 to 2 to the 32nd power - 1, server-id.
By default server-id=0, which means do not accept subscriptions from slave servers

log-bin=mysql-bin
server-id=1
These two lines are enough to start
Note: however, if InnoDB is used, it is additionally recommended to add
innodb_flush_log_at_trx_commit=1
sync_binlog=1
And you need to check that the ability to work with the network is not disabled and the skip-networking parameter is set
The slave server connects to the master using a username and password, so we first create a user on the master server
CREATE USER repl@%.mydomain.com IDENTIFIED BY slavepass;
GRANT REPLICATION SLAVE ON *.* TO repl@%.mydomain.com;
Let's look at the condition
SHOW MASTER STATUS
If the procedure for creating binary logs has already been launched, then for InnoDB tables, you must first lock the tables in one of the sessions
FLUSH TABLES WITH READ LOCK;
If you exit the session, the table lock is automatically released
In another session we get the name values bin log and position
Both values ​​represent the replication coordinates at which the slave server must begin reading from the file at the desired location to begin replication.
The next step depends on whether there is data on the slave server, data from the master
If they exist, then we leave the tables locked and create dump(this is the recommended way when using InnoDB)
You can find out the database type with the command
mysqlshow -u mysql_user -p -i database-name
If the database is stored in binary files, then they can be copied from the master to the slave server
Let's do dump
mysqldump --all-databases --master-data dbdump.db
to select bases mysqldump --databases --master-data dbdump.db
master-data parameter, automatically adds CHANGE MASTER TO on a slave node, if the parameter is not added, then all tables in the session must be locked manually
Unlock
UNLOCK TABLES;

Slave Node Configuration A
Add to my.ini server-id from personal from the master and from other nodes

server-id=2
Create a subscription
CHANGE MASTER TO
MASTER_HOST=master_host_name,
MASTER_USER=replication_user_name,
MASTER_PASSWORD=replication_password,
MASTER_LOG_FILE=recorded_log_file_name,
MASTER_LOG_POS=recorded_log_position;
When setting up replication with existing data, you must transfer a snapshot from the master to the slave before replication begins
We use mysqldump
1.Start the slave node using --skip-slave-start parameter to prevent replication from starting
2.Import the dump file
mysql fulldb.dump
3. Start the subscription process
START SLAVE;
Checking the replication status
SHOW SLAVE STATUS\G
Slave_IO_State: - current state of the slave device
Slave_IO_Running: - whether the data stream is read from the master
Slave_SQL_Running: - whether sql queries are running, should be yes

Example Let’s configure the Master (master) server – ip 11.11.11.10 V my.ini
[ mysqld] log-bin=mysql-bin server-id=1
Create a user mysql -u root -p GRANT REPLICATION SLAVE ON *.* TO replica@% IDENTIFIED BY password; FLUSH PRIVILEGES;
Next, we lock all tables in the database FLUSH TABLES WITH READ LOCK;
We look at the status SHOW MASTER STATUS; We remember the file name and position, we will use them on the Slave server for subscription

On Slave B my.ini
log-bin=mysql-bin server-id=2
Create a subscription CHANGE MASTER TO MASTER_HOST=11.11.11.10, MASTER_PORT=3306,
MASTER_USER=replica, MASTER_PASSWORD=password,
MASTER_LOG_FILE=server-mysql-bin.000002,
MASTER_LOG_POS=1151664, MASTER_CONNECT_RETRY=10;
START SLAVE;
Replication status SHOW SLAVE STATUS\G

My report is intended for those people who know the word “replication”, even know that MySQL has it, and perhaps they set it up once, spent 15 minutes and forgot about it. They don't know anything else about her.

The report will not include:

All this is on the Internet, there is no point in understanding the syntax.

We'll go through the theory a little, try to explain how it all works inside, and after that you can dive into the documentation yourself with triple your strength.

What is replication, in principle? This is copying changes. We have one copy of the database, we want another copy for some purpose.

Replication comes in different types. Different comparison axes:

• degree of synchronization of changes (sync, async, semisync);
• number of recording servers (M/S, M/M);
• change format (statement-based (SBR), row-based (RBR), mixed);
• theoretically, a model for transferring changes (push, pull).

Fun fact - if you think about it a little, replication theoretically helps us scale only reading for fundamental reasons. Here is a somewhat non-obvious conclusion. This is because if we need to upload a certain number of changes to the same copy of data, and this particular copy of data is served by the same server, then this server is able to withstand a certain number of updates per second, and no more will be uploaded there. The server is capable of updating 1000 records per second, but not 2000. What will change if you put a replica to this server, no matter in master-slave or master-master mode? Will you be able to pour a second thousand updates on this replica? The correct answer is no.

Of course, you can add additional updates to a replica in master-master mode, but another thing is that when they don’t arrive at the first master and try to make a second thousand updates on it, the capacity will no longer be enough. You need to understand and not confuse two almost obvious points, that replication is about one thing, but that the data needs to be split up, and if you need to scale not reading, but writing, then you will have to do something else, and replication will not help much .

Those. replication is more about reading.

About synchronization.

Synchronization is a guarantee of availability and accessibility. Availability in the sense that our commit has passed, the transaction has been committed, everything is fine, this data is visible to one or more nodes in the cluster, they can participate in the following requests. Availability means that the data, in principle, is on more than one server, but perhaps the transaction has not been lost and is not available.

There's no "commit succeeded, what does that mean?" refrain here. Synchronous commit means that our local and remote (at least on one replica) have ended, i.e. we committed something to the machine, if we have synchronous replication mode, then these changes were successfully committed, they are visible for subsequent requests on the local machine, and they are also visible on the remote machine (at least one). This means that if a standard emergency situation occurs, i.e. a crowbar flew into one of the servers and pierced everything right through - from the processor to the screw itself, then, despite this, the data is not only copied to a certain remote server, but also, in addition, can instantly, without any additional delays, participate in subsequent transactions.

This is all general terminology and has absolutely nothing to do with MySQL. In any distributed system it will be arranged like this.

Asynchronous commit - no additional guarantees, depending on your luck.

Semi-synchronous commit is a nice intermediate solution, this is when our local commit has passed, nothing is known about the remote commit - maybe the slave caught up, or maybe it didn’t, but at least we received confirmation that this data is somewhere then they flew away and were accepted there and, probably, signed up.

About recording servers. What are the types of replication?

Master-slave classic, changes are all poured onto one server, after which they are copied to a mass of replicas.

Master-master true - when changes flow onto a bunch of masters simultaneously and somehow from one to another, from another to a third and between them all, which gives rise to both a number of joys and a number of automatic problems. It is clear that when you have one “golden copy” and several replicas from it, which should (ideally - instantly) repeat this “golden copy”, then everything is relatively simple from the point of view of how to drive data back and forth and what do on each specific copy. With master-master an interesting “headache” begins, and, I emphasize, not specifically in the case of MySQL, but purely theoretical. What to do if on two nodes at the same time they tried to run the same transaction, which changes the same data, and, for the sake of simplicity of the example, changes it in different ways. It is clear that we cannot apply these two changes at the same time. At the moment when we start changing something on one node, there is nothing yet on the second node. Conflict. One of the transactions will have to be rolled back. In addition, separate “dances” begin with checking watches, etc.

An interesting point - even the option where ultimately all changes from all masters should gradually spread everywhere will still not help that same write bandwidth. It's a shame, but that's how it is.

A nice option is called “Master-slave + routing requests”. It’s nice because it’s easy to program inside, you have one main copy, you replicate it to a bunch of machines. This is much simpler than in a master-master environment, when everyone has equal rights, etc., but from the application point of view it still looks like you have a lot of write points. You come to any node, it knows where to route you, and routes you successfully. Well, readings are scaled - that’s the joy of replication. You can read everything from all points, always.

Now closer to databases, “magic” statement-based, row-based, etc. formats. About the format of changes.

What can you do? You can transmit the queries themselves, or you can transmit only the modified rows. I emphasize that while we have not yet dived into the jungle of MySQL, this can be done by any DBMS that has queries that generate a large (or not very large) number of changes, i.e. updating a lot of data. The question arises - what exactly will we copy? You can send the requests themselves back and forth between nodes, or you can only send the changed data. It’s interesting that both ways are very bad! You can still try mixing.

One more point about what types of replications there are. About the distribution model. Probably, somewhere the Push-based model has not yet completely died out, when the node that made the changes is obliged to send them to all other nodes. From the point of view of programming and tracking states, this is still a hassle. That’s why Pull-based rules. Taking updates from one or another node is much easier to program than monitoring a chaotic cluster of its replicas on one node.

Some general terms were introduced. Let's move on to how we did it in MySQL.

MySQL, in itself, is a kind of deception. There is a logical layer called MySQL, which deals with all sorts of general things that are isolated from data storage - network, optimizer, caches, etc. The specific physical layer, which is responsible for storing data, lies one floor below. There are several built-in ones, and some installed by plugins. But even the built-in MyISAM, InnoDB, etc. live on the physical layer. Plugin architecture is cool, you can pick up a new engine, but a certain suboptimality immediately arises. In principle, transactional write-ahead log" and (WAL), which the physical storage layer writes anyway, would be good to use for replication, and if the system knows that there is a certain physical layer, or is well enough coupled with this physical layer , then it would be possible not to write a separate log at the logical level, but to use the same WAL... But with MySQL this is conceptually impossible, or if you change the interface in PSE so that it becomes possible conceptually, then there will be a lot of work.

Replication is implemented at the level of MySQL itself. There is also good in this - in addition to one log in the form of deeply internal data of the storage engine, there is a more or less logical log, perhaps at the statement level, which is maintained separately from this engine. And this is “extra” security, etc. plus, since there are no internal restrictions, you can do any creative thing like replacing the engine on the fly.

In these terms, MySQL 4.1 implemented: master-slave, pull-based, strictly async and strictly SBR. If you're stuck in the ancient 4.x era, then things are probably bad for you. Versions 5.x are already almost 10 years old - it’s time to update.

It’s funny to follow the versions of how people stepped on all kinds of rakes and, when nothing could be done, they screwed new rakes onto these rakes so that life would not be so painful. So, in version 5.1 they added RBR to compensate for the inevitable problems with SBR, and added a mixed mode. In version 5.6, we added some more nice things: semi-sync, delayed slave, GTID.

One more thing. Since MySQL is a kind of common layer, on the one hand, and a bunch of pluggable engines, on the other hand, including built-in ones, from a certain point there is a divine NDB cluster, about which they say cool things. There is completely synchronous master-master replication, a very accessible in-memory database... But there is one caveat - as soon as you start looking for people who use NDB cluster in production, there are very few such people.

What does the master do when you decide to enable replication? There are quite a few additional movements going on in the master. As usual, we receive requests over the network, parse them, send transactions, record them, etc. In addition to this, at the logical level of MySQL, the master begins to maintain a binary log - a file, not quite a text one, into which all changes are poured. The master can also send these logs over the network. It's all very simple and seems to work.

What does a slave do? It’s better not to send changes to the slave, because you might get into something incomprehensible. The slave has a little more work. In addition to keeping one additional log and sending it out upon request, there is also a thread that goes to a remote master, perhaps even more than one, and downloads a binary log from there. The solution is “let’s go to and from several remote masters "downloading different logs" is ambiguous. On the one hand, it’s not bad, but on the other, there is an instant discrepancy. You can’t just physically copy files via SCP, you already get one log on the server, it contains your own positions, locally we drag them along the grid, put them in a separate log, there is also a separate thread running and trying to play these local logs. The most hellish thing, in my opinion, is that up until version 5.6, the identification of a particular transaction in the log occurred by the file name and position on the master. An interesting solution.

Here is the write path that a simple insert takes without replication:

The application connected to the server, put it in the table and quit.

With replication there are several additional steps:

The writer application goes to the master in the same way, but in addition, this data ends up in one form or another in the binary log, then is downloaded over the network to the relay log, then from the relay log it is gradually replayed (if we are lucky and the slave does not lag, are replayed immediately) into a table on the slave, after which everything is available in the reader.

What exactly ends up in the binary log depends on the SBR/RBR/mixed settings. Where does all this grow from? Let's imagine ourselves as a database. We received a simple request “update one specific record” – UPDATE users SET x=123 WHERE id=456

What to write to binary log? In principle, it’s all the same, really. We can write down a short request, or (and he updated one record) we can write down the change somehow in one format or another.

Another situation. Let’s imagine that we received the same request, which in itself is small, but changes a lot of data - UPDATE users SET bonus=bonus+100

There is only one effective option - to write the request itself, because the request is exactly 32 bytes, and it can update an arbitrary number of records - 1000, 100,000, 1,000,000, as many as you like... It is inefficient to write changed records to the log.

What happens if we put such a simple request in the log: “let’s disable all users who have not logged in for a long time” – UPDATE users SET disabled=1 WHERE last_login

Suddenly horror sets in. The problem is that if the request itself is ideally replicated, then, firstly, the time is never synchronous between the two nodes, in addition, due to the fact that the recording path is so long, at the time of replay this “NOW” will diverge. The replica suddenly diverges from the master, and all subsequent changes, formally speaking, are no longer safe and can lead to anything.

Generally speaking, for such queries, regardless of the amount of changed data, ideally it would be necessary to copy the lines themselves. In this particular case, you can not copy the lines themselves, but fix the constant and write in the log not “NOW”, but the specific timestamp that was used by the master at the time of replication.

Fun facts you accidentally learn while diving into the jungle of replication. Moreover, you can dive shallowly - you’ll run into them right away. In random order they are:

• The master is multi-threaded, but the slave is not. It is clear that if the master pours a load into four cores, the slave does not have time to pour this load into one core. It's all pretty bad;
• The state of the slave is determined by the name of the position in the master file. Think about it - the state of one node in the cluster is determined by the name of the file and the position in this file on another node in the cluster, with which anything can happen for any reason!
• "saving" RBR. It turns out that by default the full before/after row images are written there, i.e. we changed one column in a five-kilobyte string, oops! – 10 KB of traffic and 20-40 bytes of overheads for this line, then oops! – there’s such a bold line from the previous version, oops! – after this there is a version with new values. Administrators howl in unison! However, this is just awesome from the point of view of some perverted applications, for example, external readers that try to hook up to the MySQL server, pull data from it and do something with it, for example, stick it in a full-text index. As bad as this is from the point of view of database administration, in which one change per three bytes generates 10 KB of traffic on the screw, and then 10 KB of network traffic for each slave, it is just as good for any systems such as full-text search, like Sphinx, which there is no local copy of the data, and there is no desire to implement MySQL from scratch. In MySQL 5.6 they realized it and made binlog_row_image (but by default full, not minimal or noblob).

In short, everything is not cleverly arranged - a stick, a rope, one log, another log. And even in this log, “childhood” diseases are quite funny:

For a person who has been using replication for two days, all this is scary and difficult. But, knowing how simple it is, in principle, it’s clear how to live with it:

• First of all, we don’t believe in defaults;
• We carefully look at the settings, think about what we want - SBR, RBR, etc.

And it’s better to set it up right away so you don’t have to sort out the strange mince later.

In the situation “the log is corrupted, the position has diverged, it is not known what is happening,” there is a certain toolkit - we look at events, try to understand which transaction has already passed through, which has not, whether this whole thing can be saved or restored, etc. If GTID “If you managed to turn it on in advance, then life becomes easier.

Another point in observing replication. It is interesting to see how the internal crooked structure provokes not only competition, but the creation of additional products. The “magic” Tungsten Replicator, they say, solves well the problem called “a single-threaded slave is bad,” and if not for the inherent difficulties, there would be no additional product that allows you to use this mechanism, transfer data to other systems, on the one hand, and at the same time solve a number of problems built into the existing system, on the other hand.

As usual, it is impossible to give advice. It helps some, others will spit a lot. But they say there are situations in which Tungsten copes well with the inevitable single-threaded lag. I'm sure there are other interesting tricks out there, but an internal single-threaded slave is hard.

What to do if for some reason you used replicas as a backup? I think you have to bang your head against the wall, because a replica and a backup are two different things. However, if you are creative guys and are using a fairly new version, delayed replication will save you, on the one hand, but on the other hand, if you don’t make full backups, nothing will save you anyway.

Next is another element of creativity. It’s not hard to imagine a situation where the master filled up the entire 10 PB cloud disk with logs or filled up the entire network by sending these logs, while we don’t need 90% of these updates, because we are interested in replicating, for example, one table on sight or one database on sight, and by default everything pours into the binary log - all changes across all databases, across all tables, across everything. The solution again amazes with its creativity. On the one hand, there are four settings - (binlog|replicate)_(do|ignore)_db, which allow you to filter on the master what will be written to the log and what will be ignored. On the slave, accordingly, it allows you to do the same. Those. on the master we can filter what goes into the binary log - into this funnel, which then merges into the network, and on the slave, accordingly, we can put an incoming filter on what arrives from the network. Or write only part of the data to disk, and then replay, again, only part of the data on the slave. Suddenly, even in this simple story, horror sets in, because the combination - we use one database and update the table in another database using an interesting syntax - it behaves somehow... And how exactly it will behave is unknown, because different filters are triggered at different times.

There are no built-in nice things called “re-election of the master if he suddenly dies”; you have to raise it with your hands. The lack of tools for cluster management - this, in my opinion, is good - gives rise to competition, gives rise to the creation of additional products. In fact, if the very cool master-master replication worked perfectly in regular MySQL, or at least automatic recovery after failures, then why would all Galera, Percona/MariaDB Cluster, etc. be needed?

A few more tricks. An interesting implementation is replication, which is as simple as stick and rope, without any checks, on the one hand, and without any tools to make it more pleasant to manage the cluster of a replicating slave, on the other hand. This is bad. But you can manually sculpt such interesting configurations from this that everyone who later comes and takes it apart for you will shudder.

Configuration No. 1. A master-master “on the knee” in MySQL style is done like this:

What's scary is how many idiots there are in the world! Google “Master-master MySQL replication” - every second link is like this. Hell and the Holocaust.

Focus No. 2 – catch-all slave – is more pleasant. There are no unnecessary checks - what flies from whom, who gets it, and what to do with it. Due to this, you can make funny things like a slave, onto which either part of the data from a bunch of servers is precisely merged, or all the data from all servers is precisely merged - a server with all the backups. But, I repeat, there is replication, i.e. There is a certain basic tool that copies table A instead of B and that’s it.

And finally, trick No. 3 - we replace everything. Let us remember that replication lives at the logical level, which is in no way connected with the physical storage level. Due to this, you can create extremely interesting weird things. You can change the engine “on the fly” for unclear purposes - here’s a true story, that, they say, replication from InnoDB databases to MyISAM tables just so that full-text search works somehow. There is a creative trick called "schema modification via replication". I refuse to understand what the fat is, but there are such tricks. Well, there is a clear and interesting mode of operation called “paranoid version upgrade through replication.”

During the report we learned:

Nevertheless, you can live with this hell if you at least roughly understand how it works.

The main message is that:

In 2015, at the HighLoad++ Junior conference, Andrey Aksenov read a new version of his report on the replication device in MySQL. We also deciphered it on our blog.