Master-slave replication of Linux Operations Learning database

last week we introduced the database backup and recovery, we can almost in the daily production of data protection, but if our database server is really down or a variety of problems crash, and so we recover will also lose a lot of users or turnover, this is in production can avoid the problem, So today we introduce the master-slave replication of the database, to achieve high availability and backup effect of the database, reduce the time library maintenance time, increase the available time!

In general production environment, is a main library plus a from the library, there is a remote disaster recovery, when we crash due to various reasons, we can also have at least one library to provide services outside the overall over.

We first provided three machines, one for each master and two from each, to install Mariadb-server.

Modify the configuration file for the primary database: VIM/ETC/MY.CNF

[Mysqld]

Server-id=1 #给数据库指定唯一id

Log-bin=mysql-bin #开启mysql的二进制日志

Skip-name-resolve #跳过名词解析, non-essential

Systemctl Restart MARIADB

MySQL < show master stauts; #查看二进制日志状态, which file to write, starting with that position.

MySQL < grant replication Slave,replication client on * * to [e-mail protected] ' slave_server_ip ' identified by ' CentOS '; #建立一个备份使用的用户, give permission to copy

Modify the configuration file from the database: VIM/ETC/MY.CNF

[Mysqld]

server-id=2

Log-bin=mysql-bin

Read-only=1 #开启只读, double write problem

Relay-log=mysql-relay-log #开启中继日志

Log-slave-updates=1 #开启从跟随主二进制日志更新

Systemctl Restart MARIADB

MySQL < change master to Master_host = ' master_server ip ', #指定主库的ip

< change master_user= ' slave ' #上面定义的用户

< change master_password= ' CentOS ' #上面定义的密码

< change master_log_file= ' file '

< change master_log_pos= ' Position ';

Start slave;

show slave status\g;

Slave_io_running:yes #I/o thread is running correctly
Slave_sql_running:yes #SQL线程正常运行

When we are done, we can use various SQL statements of the data to test if the synchronization will be replicated automatically!

There's a lot of people here asking, why is it so configured? The non-writable from the library is because when two databases are written to a block block at the same time, the file system will crash, this problem is one of the biggest problems that plague our double writing; When our database changes, the binary log is written first, and the binary log is written to the hard disk when it satisfies the conditions. When we open two threads from the top: I/O thread and SQL thread. Where the I/O thread is in the discovery of binary log data updates, automatically pull the log, write to the relay log, the relay log through the SQL thread, write to the native MySQL database, at this time, two library operation synchronization, but a problem was born, if our binary log is pulled away, is the primary database waiting for the binary to come back, return the user to write the operation OK, or not wait for the direct return OK? This involves the synchronous asynchronous mechanism of our MySQL.

MySQL defaults to the asynchronous mechanism, that is, when the user's write operation is completed, the binary log, we return OK, regardless of whether the server is pulled, whether it is completed, so that the birth of a problem, if we are at this time the database crashes, when our database from the primary database switch to the database, Data is lost, resulting in inconsistent master and slave data.

And the synchronization mechanism, is our main data after being written, waiting for all the backend copy from the database to complete, and our database from sometimes not only one, plus the consumption of the network, service time is very long, and the customer will not have so much patience to wait for the completion of the process, which will lead to customer churn.

But the solution is also very simple, that is, the semi-synchronous mechanism came into being, as long as there is a copy from the data to complete, immediately return to the customer write OK, that is, enhanced security, and reduce the wait time, and the semi-synchronous mechanism can also set the time-out period, if there is no replication completed within a certain time, Return the results directly to the customer.

So how do we switch to MySQL's semi-sync?

Our MySQL supports a variety of plugins under/usr/lib64/mysql/plugins/, where we see two semisync_master.so and semisync_slave.so libraries, so we can install them directly in the database.

Main Library:

MySQL < install plugin rpl_semi_sync_master soname ' semisync_master.so ';

MySQL < show global variables like ' rpl_semi% ';

MySQL < set global rpl_semi_sync_master_enabled =1 #开启主半同步

From library:

MySQL < install global rpl_semi_sync_slave soname ' semisync_slave.so ';

MySQL < show global varialbes like ' rpl_semi% ';

MySQL < set global rpl_semi_sync_slave_enabled=1 #开启从半同步

MySQL < stop slave;

MySQL < start slave;

View MySQL error log to see the changed information, MySQL error log generally in/var/log/mariadb/mariadb.log

Slave I/O thread:start semi-sync replication to master ' [e-mail protected]:3306 ' in log ' mysql-bin.000003 ' at position 809

When we saw that our half-sync was done.

This is the end of today's master-slave copy, have you learned it?

Master-slave replication of Linux Operations Learning database