Implementation of--mysql Master-slave architecture in actual combat project

One master one from 1.1 environment prepares:

CentOS System server 2, a user to do MySQL master server, one for MySQL from the server, configured yum source, firewall shutdown, each node clock service synchronization, each node can communicate with each other through the host name

1.2 Preparation steps:

1) iptables-f && setenforce Empty firewall policy, turn off SELinux
2) Take two servers and install the MySQL service using Yum mode, which requires a consistent version
3) Start two server MySQL services separately to ensure the service is OK
Architecture diagram:

1.3 Implementation steps: 1.3.1 Configuring Master master server

Configure master, including opening a binary log, specifying a unique SERVR ID. For example, add the following value to the configuration file

    server-id=1　　　　　 //配置server-id，让主服务器有唯一ID号    log-bin=mysql-bin　　//打开Mysql日志，日志格式为二进制    skip-name-resolve　　//关闭名称解析，（非必须）

1.3.2 Create a replication account

Establish a backup account in the master database: Each slave uses the standard MySQL username and password to connect to master. The user who makes the copy operation grants REPLICATION SLAVE permissions.

    MariaDB [(none)]> grant replication slave,replication client on *.* to [email protected]'192.168.%.%' identified by 'keer';

1.3.3 Viewing the primary server status

In Master's database execution show master status , view the primary server binary log status and location number.

1.3.4 Configuring slave from the server

Configure slave, turn on the trunk log, specify a unique Servr ID, and set read-only permissions. Add the following values to the configuration file:

    server-id=2                 //配置server-id，让从服务器有唯一ID号    relay_log = mysql-relay-bin //打开Mysql日志，日志格式为二进制    read_only = 1               //设置只读权限    log_bin = mysql-bin         //开启从服务器二进制日志    log_slave_updates = 1       //使得更新的数据写进二进制日志中

1.3.5 Starting a replication thread from the server

Let slave connect to master and begin to redo the events in the master binary log.

MariaDB [(none)]> change master to master_host='192.168.37.111',    >master_user='slave',    >master_password='keer',    >master_log_file='mysql-bin.000001',    >master_log_pos=413;

Execute start slave； to start the replication thread.
　　

1.3.6 View from server status

You can use SHOW SLAVE STATUS\G; view from the server state, as shown below, and also show processlist \G; view the current replication status:

    Slave_IO_Running: Yes       //IO线程正常运行    Slave_SQL_Running: Yes      //SQL线程正常运行

1.3.7 Test

1) test master-Slave synchronization
We create a database on the master server, then use that database to create a table, add a record, and see if the slave server is successfully synchronized.
First, let's take a look at what databases are on two servers:
Master

Slave

Now, we create a database "Keer" on the primary server:

Then, let's see if the server has been updated:

As we can see, the database is synchronized, and then we create the table and insert a record in the table:

Then, let's look at whether the server is updated:

2) test read-only properties　　
We've set up read-only from the server, so let's try to insert the data:

This is awkward, why do we set read-only or can we insert data? How can this be solved?
Because we are logged in using the root super-user, this big pervert is not affected by the read-only settings, so we can still insert data inside, of course, if we switch to other ordinary users will not have such a problem.
The solution is also very simple, we just need to lock up the table to be able to:

MariaDB [keer]> flush tables with read lock;

After the lock table operation, let's insert a piece of data:

In this case, even if we are superuser logged in, we can no longer insert the data. That would be a lot safer. However, there are pros and cons, so that our master and slave can not be synchronized in real time, because our master-slave synchronization is by the Lord's SQL statement to be executed from the top of the implementation again, but we lock the table, the SQL statement can not be executed. It is also easy to synchronize the method, simply unlock the table.
We insert a piece of data in our Lord:

At this point, we need to solve the table from the top to achieve synchronization:

1.4 Expansion--to achieve a master multi-1.4.1 Demand analysis

When our servers have been running for a period of time and the traffic has become more and more, a primary one from the ability to achieve high availability and load balancing does not meet our needs, we will choose to add another from the server.
But now that our master has been running for a long time, we also need to synchronize the data with the newly installed slave, even if it doesn't have the master data.
At this point, there are several ways to get slave to start from another service, such as copying data from Master, cloning from another slave, and starting a slave from the most recent backup. In order to speed up slave synchronization with master, data synchronization can be performed in the following ways:

(1) Data snapshot at a certain time of master;
(2) Backup data of the database;
(3) The binary log file of master.

Architecture diagram:

Next, let's implement this process:

1.4.2 Specific implementation process

1) Full backup of Master

[[email protected] ~]# mkdir /backup[[email protected] ~]# mysqldump --all-database > /backup/mysql-backup-`date +%F-%T`-all.sql

Check the primary binary log status and location number as usual.

2) Send the full backup data to the new

[[email protected] ~]# scp /backup/mysql-backup-2017-11-21-10\:28\:29-all.sql @192.168.37.133:~[email protected]'s password: mysql-backup-2017-11-21-10:28:29-all.sql                                                           100%  504KB 503.9KB/s   

3) Import this full backup from the database:

4) Check to see if the recovery was successful:


Let's take a look at the binary log status and location number from:

Our data has been successfully restored.

5) Set master/slave
The next step is to follow the steps above to set up the slave server:

[[email protected] ~]# vim /etc/my.cnf    [mysqld]    server-id = 3    relay-log = mysql-relay-bin    read-only = 1    log-bin = mysql-bin    log-slave-updates = 1

　　
Then, we'll set it up from the server:

[[email protected] ~]# mysql -uroot -pEnter password: MariaDB [(none)]> change master to master_host='192.168.37.111',    >master_user='slave',    >master_password='keer',    >master_log_file='mysql-bin.000001',    >master_log_pos=330;MariaDB [(none)]> start slave;

Then let's look at the status from the server:

6) Testing
Set up, let's test, when the Lord has operations, whether the update from the top:


At this point, we have completed a master two from. If necessary, we can also continue to add from the server.

1.5 extension--achieve semi-synchronous replication 1.5.13 Ways to replicate

1) Synchronous replication
When the main library executes a transaction, all of the transactions executed from the library are returned to the client. Because it is necessary to wait for all the transactions from the library to be returned, the performance of full synchronous replication must be severely impacted . A time-out is required.
2) Asynchronous replication
　　mysql Default replication is asynchronous, the main library after executing the client committed transactions will immediately return the results to the client, do not care whether the library has been received and processed, so there is a problem, if the Lord crash off, At this time the business that the Lord has submitted may not have been uploaded from the top, if at this time, forcibly will be from Ascension, may lead to the new master's data is incomplete.
3) Semi-synchronous replication
Between asynchronous replication and full synchronous replication, the main library does not immediately return to the client after executing a client-submitted transaction, but waits for at least one receipt from the library and written to the relay log to return to the client. semi-synchronous replication improves data security relative to asynchronous replication, and It also causes a certain amount of latency, which is at least a TCP/IP round-trip time. Therefore, semi-synchronous replication is best used in low latency networks.

1.5.2 for semi-synchronous replication

Semi-synchronous replication is implemented based on specific modules. But the very nice thing is that the version above our mariadb5.5 is with this module by default.
If we have MARIADB installed on our centos7, we can go to the catalogue and check it out:

[[email protected] ~]# cd /usr/lib64/mysql/plugin/

Once we have found our module, we can install it by command and then use it with a simple configuration. Here's how:
On the master server:

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=ON;

On the slave server:

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled = ON ;MariaDB [(none)]> stop slave;   MariaDB [(none)]> start slave;

Now that our semi-synchronous replication is on, we can look at the logs:

It can also be seen from the log that our semi-synchronous replication is turned on.

Above, the experiment is completed.

Mutual master from (dual)

There is a database of the architecture of the two-master mode, that is, the main from each other, this setting in the enterprise is not commonly used, but we still need to master.
Master-master replicates two servers, both master and slave of the other server. In this way, changes made by either party are applied to the other party's database through replication.
The architecture diagram is as follows:

2.1 Environment Preparation:

CentOS System server 2, two users realize MySQL mutual master from, configured yum source, firewall shutdown, each node clock service synchronization, each node can communicate with each other through the host name

2.2 Preparation steps:

1) iptables-f && setenforce Empty firewall policy, turn off SELinux
2) Take two servers and install the MySQL service using Yum mode, which requires a consistent version
3) Start two server MySQL services separately to ensure the service is OK

2.3 Implementation steps: 2.3.1 Configure the first Master master server

    server-id = 1　　　　　 //配置server-id，让主服务器有唯一ID号    log-bin = mysql-bin　　//打开Mysql日志，日志格式为二进制    skip-name-resolve  　　//关闭名称解析，（非必须）    relay_log = mysql-relay-bin //打开Mysql日志，日志格式为二进制    read_only = 1               //设置只读权限    log_slave_updates = 1       //使得更新的数据写进二进制日志中    auto_increment_offset = 1　　　 //表示自增长字段从那个数开始    auto_increment_increment = 2　　//表示自增长字段每次递增的量

2.3.2 Configuring the second Master master server

    server-id = 2　　　　　 //配置server-id，让主服务器有唯一ID号    log-bin = mysql-bin　　//打开Mysql日志，日志格式为二进制    skip-name-resolve　  　//关闭名称解析，（非必须）    relay_log = mysql-relay-bin //打开Mysql日志，日志格式为二进制    read_only = 1               //设置只读权限    log_slave_updates = 1       //使得更新的数据写进二进制日志中    auto_increment_offset = 2　　　 //表示自增长字段从那个数开始    auto_increment_increment = 2　　//表示自增长字段每次递增的量

2.3.3 Create a copy account

Because we are implementing a dual Master mode, we have to create a copy account on each of our machines:

2.3.4 View server Status

We look at the status of the two servers separately, laying the groundwork for our next setup:

2.3.5 Starting a replication thread

Let's set up the two machines separately:
First, set the first master machine first:

MariaDB [(none)]> change master to master_host='192.168.37.222',master_user='slave',master_password='keer',master_log_file='mysql-bin.000003',master_log_pos=245;MariaDB [(none)]> start slave;

Next, let's take a look at his status:

Now, let's set up the second master:

MariaDB [(none)]> change master to master_host='192.168.37.111',master_user='slave',master_password='keer',master_log_file='mysql-bin.000002',master_log_pos=245;MariaDB [(none)]> start slave;

Let's take a look at his status:
　　
As you can see, our two machines are set up, and then we can test them.

2.3.5 Test

In our configuration file, we set the start value and growth value of the self-growing field, and now we are going to create a self-growing table:

MariaDB [keer]> create table test(id int PRIMARY KEY AUTO_INCREMENT,name char(20) not null);

Because we set the master and slave, we can use the command on either machine.
Once we've created the table, we can insert the data for testing, and we'll insert multiple data to see the effect:
First server:

MariaDB [keer]> insert into test(name) value('zhao'),('qian'),('sun'),('li');Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0

A second server:

MariaDB [keer]> insert into test(name) value('zhou'),('wu'),('zheng'),('wang');Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0

We've all inserted the data, so let's see how it works:

As you can see, the data inserted in two tables is not duplicated.
But now is not the continuous ID we imagined, this is because both of our machines are inserting 4 data at the same time, and not synchronous insertion, so there will be a sequence, the first machine on the execution of the SQL statement after the completion of the second machine. So there is a situation like this.
However, this does not insert duplicate data. Our purpose is also achieved.

Above, experiment two completed.

If there are shortcomings, please give us a lot of advice here? (? >?<?)?

Implementation of--mysql Master-slave architecture in actual combat project