Shulou(Shulou.com)06/01 Report--

The following is mainly to bring you to build a binlog-based master-slave architecture detailed steps, I hope these content can bring you practical use, this is also the main purpose of my editing to build a binlog-based master-slave architecture detailed steps this article. Okay, no more nonsense, let's go straight to the following.

The master-slave architecture based on binlog mode can also be called traditional asynchronous replication mode.

Environment:

host IP

OS MySQL Version Master192.168.32.3CentOS release 6.5 (Final)5.6.16-logSlave192.168.32.2CentOS release 6.5 (Final)5.6.16-log

Master-slave copy schematic:

Principle of master-slave replication:

This is done through three threads.

Master, records data changes to binary log via dump thread (these records are called binary log events)

Slave, IO thread, copy Master's binary log to its relay log

Slave, SQL thread, reads from Slave's realy log and replays these records

Application scenarios of master-slave replication:

1. Slave as Master's Data Backup

When there is a problem with Master, manually or automatically switch to Slave host to ensure uninterrupted service

2. Master and Slave do read-write separation, Slave implements Load Balancer, and separates read-write traffic

And I'm going to bring in an image that I found on the website.

3. Under the read-write separation architecture, multiple slaves are split according to the business.

Here's an image from the Internet.

Master-slave setup configuration:

1. Modify my.cnf configuration

Master Host Configuration:

cat /home/data/mysql3306/my.cnf

server_id = 2

log_bin = /home/data/mysql3306/mysql-bin

The above two parameters must be configured. Other parameters are configured according to your MySQL installation directory and business situation.

Slave Host Configuration:

cat /home/data/mysql3306/my.cnf

server_id = 1

Slave host binlog is not required to be enabled. Other parameters are configured according to your MySQL installation directory and business situation. If there is a need for cascading replication, it will be enabled. Generally, the master-slave architecture is not enabled to save disk I/O.

2. Authorize copy connection users

mysql> grant replication slave on *.* to repliter@'192.168.32.2' identified by PASSWORD ' *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

It is recommended to encrypt the password first (password() function) and then authorize it, so that the plaintext password will not be recorded in the binglog and reduce the risk.

3. Configure master-slave copy filtering rules

If our business data is split into slaves, for example, Slave1 only needs to copy the data of forums (BBS), Slave2 only needs to copy the data of online classes (edusoho_e), etc., in this case, we need to configure the filtering rules for master-slave replication, and only select the data that Slave hosts need to copy. There are two filter rules that can be configured. Of course, you can also synchronize all data directly to Slave without configuration.

Master host is configured with master-slave replication filtering rules:

cat /home/data/mysql3306/my.cnf

binlog-do-db=bailidb (for more filtering rules, please read MySQL's official website)

Note: MySQL needs to be restarted

Before updating the data, look at the position

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000006

Position: 120

Binlog_Do_DB: bailidb

Insert data from a non-filter database:

INSERT INTO `backup`.` vip_1` (`sname`) VALUES ('Python');

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000006

Position: 120

Binlog_Do_DB: bailidb

position has not changed.

Update a piece of data that matches the filter rule database:

UPDATE `bailidb`.` bl_admin` SET `username` = 'heihei' WHERE `userid` = '40';

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000006

Position: 562

Binlog_Do_DB: bailidb

position position change, indicating that the configuration has taken effect

Description:

Although Master supports this master-slave filtering rule, after configuration, only the configured library can be written or the configured library cannot be written into binlog. With the business change, some libraries that do not need replication synchronization need to be replicated and synchronized now, so you need to restart Master database. If the business changes again later, you need to restart Master database. Obviously, this is not flexible enough, and the cost of restarting Master is definitely much higher than restarting Slave; On the other hand, unless it is very certain, all data change operations of the Master should be recorded in the binlog, and problems can be recovered according to this, and the Master should not be filtered. Therefore, in general, select the master-slave replication filter rule of the Slave configuration.

Slave host configures master-slave replication filtering rules:

Now the business requirements are, now the Slave host only needs forum (BBS), and online classroom data (edusoho_e) data

cat /home/data/mysql3306/my.cnf

replicate_do_db=bbs

replicate_do_db=edusoho_e (for more filtering rules, please read MySQL's website)

Note: MySQL needs to be restarted

4. Prepare to copy data

After backing up the bbs and edusoho_e data on the Master host, transfer them to the Slave host for data import to make them consistent with the Master database.

The author uses MySQL's own mysqldump tool, which belongs to the warm backup method, that is, the database table will be locked during the backup process, which will have a certain impact on the business. The larger the data volume, the greater the impact. The author agrees with the experience of online peers, below 50G or can use mysqldump tool, above 50G should consider using physical backup tools such as: Xtrabackup

mysqldump -uroot -p --single-transaction --master-data=2 --databases bbs edusoho_e > `date +%F`.sql

--single-transaction ensures read consistency of data

--master-data=2 will CHANGE MASTER TO information comments

For the specific meaning of parameters, please consult mysqldump --help.

Upload to Slave Host:

You can use scp, ftp, sz, etc., not much to say here

Create a database with the same name:

mysql> create database bbs;

Query OK, 1 row affected (0.03 sec)

mysql> create database edusoho_e;

Query OK, 1 row affected (0.00 sec)

5. Enable data replication

Data import first

mysql -uroot -p

< 2019-04-28.sql 再判断从Master主机的哪里开始进行复制，还记得之前mysqldump的--master-data=2选项吗，它记录了我们需要从Master的哪里开始进行复制 more 2019-04-28.sql（注意：千万别使用vim，如果sql文件大的话，足够把你内存吃完，使用more分页查看就已经足够） -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=120（这是一个注释信息的样本） 查看CHANGE MASTER TO语法选项 mysql>

help change master to;(not recommended because it is not necessary)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.32.3',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000007',MASTER_LOG_POS=120;

Query OK, 0 rows affected (0.08 sec)

mysql> start slave user='repliter' password='123456';

Some people may ask, why don't you add user and password when you change MASTER TO? Isn't it unnecessary to write separately?

The reason: for safety.

During CHANGE MASTER TO, the I/O thread is responsible for maintaining the update of the master.info file. The I/O thread will update the binlog file and position of the Master host, as well as other information, including the username and password of Slave connecting to the Master host, so as to realize the synchronization of master-slave data. Then the problem comes. If user and password are written directly during CHANGE MASTER TO, they will also be written into the master.info file, which greatly increases the risk of account leakage. Because such account passwords can only be known by DBAs, for account security, specify the user password when starting slave, so that user and password information will not be saved to the master.info file.

View Slave replication status:

mysql> show slave status\G;(For the specific meaning of parameter items, be sure to look at MySQL official website, because many materials on the Internet are translated according to their own understanding, it is difficult to understand) Here only look at I/O, SQL thread running status

Slave_IO_State: Waiting for master to send event

xxx

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

xxx

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

xxx

6. Data synchronization test:

Master host changes two pieces of data:

INSERT INTO `bbs`.` myhash_0` (`c1`, `c2`, `c5`) VALUES ('1', '2', '3');

UPDATE `edusoho_e`.` biz_targetlog` SET `target_type` = 'trade.sucessfull' WHERE `id` = '5';

and then check whether the data synchronization has come over.

Off-topic:

Although it is a test, the author thinks that the purpose of the test is not only to test, but also to apply it to the pathfinder and vanguard army on the line. Therefore, when doing the test, we should consider as much as possible. Can this be applied to the line?

Instead of trying to save trouble, simplify the process. For example, when authorizing copy connection users, I have seen root@'%' copy connection users. Is this also done online? Also: mysqldump time, really can enter a plaintext password in the command line? vim a tens of G file, really no problem? You should use the minimum permissions possible and use appropriate commands to make the Cloud Virtual Machine run stably first.

For the above detailed steps on building a master-slave architecture based on binlog, do you think it is very helpful? If you need to know more, please continue to pay attention to our industry information, I believe you will like these contents.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.