Mysql replication of multiple databases. What is replication in MySQL? We carry out further actions on the Slave server

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 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: - are they running? sql queries, 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

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

Replication- a technique used in the architecture of systems operating under load, the result of which is the distribution of the load when working with one database across several servers. MySQL MASTER SLAVE replication is used more often, but a second type of replication is also used - Master-Master.

What is MySQL MASTER SLAVE replication and what is it used for?

Replication Master-Slave involves duplicating data to a slave MySQL server; such duplication is carried out mostly to ensure reliability. If the Master server fails, its functions are switched to the Slave.

Replication can also be carried out to improve system performance, but performance is almost always secondary here.
When an application works with a database, the most frequent operations are SELECT- requests to read data, modify data - requests DELETE, INSERT, UPDATE, ALTER Statistically it happens much less often.

To prevent data loss if one of the servers fails, operations to change information in tables are always processed by the Master server. The changes are then replicated to the Slave. Reading can be done from a server playing the role of Slave.
Due to this, you can get a gain in performance along with reliability.

The solution is popular, but not always applicable since there may be delays during replication - if this happens, the information also has to be read from the Master server.

Directing requests of a certain type to a particular database server is in any case implemented at the application level.

If you do the division SELECT queries and all the rest at the program level, sending them to the desired server when one of them fails, the application that the infrastructure serves will be inoperable. For this to work, you need to provide more complex circuit and reserve each of the servers.

Replication is for fault tolerance, not for scaling.

MySQL MASTER SLAVE replication - setting up on Debian

We will use two servers with addresses:

Master server 192.168.0.1
Slave server 192.168.0.2

For demonstration, VDS connected to a local network are used.
To always know for sure on which server we are executing this or that command, we will edit the /etc/hosts files on both servers

192.168.0.1 master

192.168.0.2 slave

Let's replace the existing values in /etc/hostname with master and slave, respectively, so that the changes take effect and reboot the server.

1. We make settings on the master server.

root@master:/#

Editing the main one configuration file database server

mcedit /etc/mysql/my.cnf

Select the server ID - you can specify any number, the default is 1 - just uncomment the line

server-id = 1

Set the path to the binary log - also specified by default, uncomment it

Set the name of the database that we will replicate to another server

binlog_do_db = db1

Restart Mysql so that the configuration file is reread and the changes take effect:

/etc/init.d/mysql restart

2. Set the user’s necessary rights

Go to the database server console:

We give the user on the slave server the necessary rights:

GRANT REPLICATION SLAVE ON *.* TO "slave_user"@"%" IDENTIFIED BY "123";

Locking all tables in the database

FLUSH TABLES WITH READ LOCK;

Checking the status of the Master server:

3. Create a database dump on the server

Create a database dump:

mysqldump -u root -p db1 > db1.sql

Unlock tables in the mysql console:

4. Transfer the database dump to the Slave server

scp db1.sql [email protected]:/home

We carry out further actions on the Slave server

root@slave:/#

5. Creating a database

Loading the dump:

mysql -u root -p db1< db1.sql

6. Make changes to my.cnf

mcedit /etc/mysql/my.cnf

We assign an ID by incrementing the value set on the Master server

server-id = 2

Set the path to the relay log

relay-log = /var/log/mysql/mysql-relay-bin.log

and the path to the bin log on the Master server

log_bin = /var/log/mysql/mysql-bin.log

Specify the base

binlog_do_db = db1

Restarting the service

/etc/init.d/mysql restart

7. Set up a connection to the Master server

CHANGE MASTER TO MASTER_HOST="192.168.0.1", MASTER_USER="slave_user", MASTER_PASSWORD="123", MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 327;

We start replication on the slave server:

You can check the operation of replication on the Slave with the following request:

************************** 1. row ******************** *******
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 555
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

Since no errors occurred, we can conclude that replication is configured correctly.

Is good tool scaling, but the main disadvantage is desynchronization of data copying and delays, which can be critical.

Using a more modern solution allows you to completely avoid them. It is easy to set up, reliable, and eliminates the need to manually copy database dumps.

whisk April 8, 2009 at 11:10 am

MySQL Replication Basics

MySQL

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 database MySQL data, already filled with data and included in the work. 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 performing a technical work. 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 the only 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:

mysql
replication

Add tags

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 separate file, from where the slave servers get 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.