Replication is a mechanism for synchronizing the contents of multiple copies of an object. This process refers to copying data from one source to many others and vice versa.
Designations:
- master - the main server whose data needs to be duplicated;
- replica - a repaired server that stores a copy of the master data
To set up replication in MySQL, you need to follow the sequence of actions described below, but this is not a dogma and the parameters can change depending on the circumstances.
On the main server, edit the my.cnf file and add the following lines to the mysqld section:
Server-id = log-bin = mysql-bin log-bin-index = mysql-bin.index log-error = mysql-bin.err relay-log = relay-bin relay-log-info-file = relay-bin. info relay-log-index = relay-bin.index expire_logs_days=7 binlog-do-db =
- - unique MySQL server identifier, number in the range 2 (0-31)
- - the name of the database, information about which will be written to the binary log; if there are several databases, then each requires a separate line with the binlog_do_db parameter
On the slave, edit the my.cnf file and add the following lines to the mysqld section:
Server-id = master-host = master master-user = replication master-password = password master-port = 3306 relay-log = relay-bin relay-log-info-file = relay-log.info relay-log-index = relay-log.index replicate-do-db =
On the main server, add the replication user with data replication rights:
GRAANT REPLICATION SLAVE ON *.* TO "replication"@"replica" IDENTIFIED BY "password"
Let's block replicated databases on the main server from changing data, programmatically or using the MySQL functionality:
Mysql@master> FLUSH TABLES WITH READ LOCK; mysql@master> SET GLOBAL read_only = ON;
To unlock, use the command:
Mysql@master> SET GLOBAL read_only = OFF;
Let's make backup copies of all databases on the main server (or those that we need):
Root@master# tar -czf mysqldir.tar.gz /var/lib/mysql/
or using the mysqldump utility:
Root@master# mysqldump -u root -p --lock-all-tables > dbdump.sql
Let's stop both servers (in some cases you can do without it):
Root@master# mysqlamdin -u root -p shutdown root@replica# mysqlamdin -u root -p shutdown
Let's restore replicated databases on the slave server by copying the directory. Before replication begins, the databases must be identical:
Root@replica# cd /var/lib/mysql root@replica# tar -xzf mysqldir.tar.gz
or mysql functionality, then there was no need to stop mysql on the slave server:
Root@replica# mysql -u root -p< dbdump.sql
Let's run mysql on the master server (and then on the slave server, if necessary):
Root@master# /etc/init.d/mysql start root@replica# /etc/init.d/mysql start
Let's check the operation of the master and slave servers:
Mysql@replica> start slave; mysql@replica> SHOW SLAVE STATUS\G mysql@master> SHOW MASTER STATUS\G
On the slave server, check the logs in the master.info file, it should contain requests to change data in the database. So this binary file must first be converted to text format:
Root@replica# mysqlbinlog master.info > master_info.sql
If errors occur, you can use the commands:
Mysql@replica> stop slave; mysql@replica> RESET SLAVE; mysql@master> RESET MASTER;
and repeat all steps starting with blocking the databases.
To hot add replication servers you can use the following syntax:
Mysql@replica> SHOW SLAVE STATUS\G mysql@master> SHOW MASTER STATUS\G mysql@replica-2> CHANGE MASTER TO MASTER_HOST = "master", MASTER_USER ="replication", MASTER_PASSWORD = "password", MASTER_LOG_FILE ="mysql- bin.000004 ", MASTER_LOG_POS = 155; mysql@replica-2> START SLAVE;
Information from the statuses will show the position and name of the current log file.
With asynchronous replication, an update from one replica is propagated to others after some time, rather than in the same transaction. Thus, asynchronous replication introduces a latency, or waiting time, during which individual replicas may not be effectively identical. But this type of replication also has positive aspects: the master server does not have to worry about data synchronization; you can block the database (for example, to create a backup copy) on a slave machine, without problems for users.
List of sources used
- Habrahabr.ru - Basics of replication in MySQL (http://habrahabr.ru/blogs/mysql/56702/)
- Wikipedia (http://ru.wikipedia.org/wiki/Replication_(computing_technology))
When using any materials from the site in whole or in part, you must clearly indicate a link to as the source.
Good day everyone! Today in our article we will look at examples of setting up replication of the “master-slave” type.
A little theory
Why is replication needed? First of all, this is a safety net in case the main mysql server fails, then you can switch to the slave server and continue working. Secondly, this is an opportunity to reduce the load on the main Mysql server by using the master server only for writing, and performing read operations on the slave server. How does replication occur? The master server writes binlogs, in which it indicates the operations that are performed on the database (databases) and remembers the offset in the log from its beginning to the current record (position). The slave server connects to the master, compares position values and reads changes in the log starting from the value of its own position and ending with the value of the master’s position. It applies changes (commands) to the databases on the slave server.
Installation and configuration of Master
We change my.cnf on the head server:
Server-id = 1 - indicate the server id log_bin = /var/log/mysql/mysql-bin.log - log name and path
A small clarification: by default, the wizard writes binlogs for all databases, this can be changed using "binlog-do-db". Values will be recorded in the logs when a specific database is used; changes in other databases will not be recorded. Here you can specify how many days to store logs, what their maximum size is (parameters expire_logs_days and max_binlog_size). Add a user to MySQL under whose rights replication will be performed:
GRANT replication slave ON *.* TO user_name@ip_slave_server IDENTIFIED BY "password";
replication slave is a privilege that allows the user to read binlogs. ip_slave_server - ip of the server from which the user will connect. Reboot the mysql server:
/etc/init.d/mysql restart
Let's check the master's work:
Show master status;
You should see the name of binlog and its position. When executing commands on the database, the position will change.
Slave setup
We make changes to the my.cnf file:
Server-id = 2 - the identifier of the slave server must be different from the master identifier. relay-log = /var/lib/mysql/mysql-relay-bin - like a binary log, consists of a set of numbered files containing events that describe changes to the database. relay-log-index = /var/lib/mysql/mysql-relay-bin.index - an index file that contains the names of all relay log files in use. replicate-do-db = the database that will be replicated.
Important note! When organizing a cross db (when one database is used and the data is updated in another database), binlog-do-db does not need to be specified in the master server settings, binlog-and should be written for all databases, and in the slave settings it is necessary to use replicate-do instead -db specify replicate-wild-do-table=db_name.%, where db_name is the name of the replicated database. Reboot the mysql server:
/etc/init.d/mysql restart
Enabling replication
SET GLOBAL read_only = ON;
Let's look at the state of master:
Show master status;
We remember the values of File and Position (or better yet, write them down). The Position value should not change now. We dump the master using the mysqldump command:
Mysqldump -uname -ppassword db_master_name > dump_db,
where name is the user name, password is the password, db_master_name is the database name, dump_db is the name of the dump. After the dump is completed, we allow writing to the database:
SET GLOBAL read_only = OFF;
We transfer the dump to the slave and expand it
Mysql -uname -ppassword db_slave_name< dump_db
Setting up replication
CHANGE MASTER TO MASTER_HOST = “master ip”, MASTER_USER = “user_name”, MASTER_PASSWORD = “password”, MASTER_LOG_FILE = “log name”, MASTER_LOG_POS = position;
master ip - the ip of the server on which the master is located, username - the name of the user we created on the master, log name - the File value on the master when the database dump was made, position - the Position value on the master when the database dump was made. Let's start the slave:
Start slave;
Let's see how replication is going: On the master: SHOW MASTER STATUS\G On the slave: SHOW SLAVE STATUS\G
Security settings on the master server
The bind-address parameter in /etc/mysql/my.cnf specifies what IP address the mysql server will listen to while waiting for a connection. Typically it has the value bind-address = 127.0.0.1. But after setting up the slave server, we need to allow connections from the slave server and local connections should work. Bind-address can allow connections from only one IP or from all. Because we need to specify more than one ip for the connection, we comment out the line with bind-address = 127.0.0.1. Now the mysql server will accept connections from all IP addresses, which is very dangerous. iptables will help us solve this problem:
Iptables -I INPUT -p tcp -s ip_slave_server-a --dport 3306 -j ACCEPT -first allow connection from the IP address of the slave server iptables -I INPUT -p tcp --dport 3306 -j DROP -then deny connection from all other IP addresses.
Now we will have 2 MySQL servers running in master-slave mode, which significantly increases the reliability of the site and for some Drupal sites helps to increase the speed of work. In the next article we will look at switching between master and slave modes in the event of a master server failure.
Not long ago I was asked to talk about replication in MySQL. I decided that this topic could be useful to many, so in this article I will talk about what is replication in MySQL, when is it needed and how to configure it.
The main task of replication is combine the power of several servers. Let's say your website has a dedicated server, but over time it becomes very visited and can no longer withstand the load. As a result, the server begins to slow down and crash regularly. The easiest way is to buy a more powerful server, and this is what most people do. But sooner or later there comes a time when the cost of increasing the price of a server does not correspond to the increase in its performance, so it is more profitable to buy 2 different servers for less money.
As a result, your database will be on two servers at once. When one main server (aka the head server) can no longer cope, it switches to a spare one.
All database update requests always go to the head server. After updating the head server, it places information about this in a separate file, from where the slave servers take all the information. But sampling operations, which are usually the majority, and they are the slowest, can already be transferred to slave servers, since the data is the same in both.
Now let's figure it out how to configure replication in MySQL:
- Install the most latest versions of MySQL to all servers.
- Create a user with the privilege on the main server REPLACING SLAVE. For the address from which it can connect, specify " All".
- Stop all servers.
- In settings MySQL(in file my.cnf) In chapter
add the following lines: log-bin
server-id=1 Please note that server-id must be different on all servers. In fact, this is what distinguishes one server from another. - On slave servers, add to settings MySQL the following lines: master-host=master_host_name
master-user=login of the created_user
master-password=password of the created_user
master-port=port_for_connecting_to_the_master_server
server-id=id_of_this_slave_server - Move all bases from the head server to the slaves.
- Run the head server, then all the slaves.
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.
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 restart 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.
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.
Thank you for your attention!
Tags: Add tags
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.
