MySQL double master and double slave + atlas data test

1. Set up process

Dual host configuration

Master1 To configure

Modify the configuration file ： vim /etc/my.cnf # The primary server is unique ID server-id=1 # Enable binary logging log-bin=mysql-bin # Set up the database not to be copied ( Multiple can be set ) binlog-ignore-db=mysql binlog-ignore-db=information_schema # Set up the database to be copied binlog-do-db= The name of the master database to be copied # Set up logbin Format binlog_format=STATEMENT # As a slave database , Update binary log files when there are write operations log-slave-updates # Represents the amount of each increment of the self growing field , It refers to the starting value of the auto increment field , The default value is 1, The value range is 1 .. 65535 auto-increment-increment=1 # Indicates the number from which the self growing field starts , Refers to how many fields are incremented at a time , His range is 1 .. 65535 auto-increment-offset=1

Master2 To configure

Modify the configuration file ： vim /etc/my.cnf # The primary server is unique ID server-id=3 # Enable binary logging log-bin=mysql-bin # Set up the database not to be copied ( Multiple can be set ) binlog-ignore-db=mysql binlog-ignore-db=information_schema # Set up the database to be copied binlog-do-db= The name of the master database to be copied # Set up logbin Format binlog_format=STATEMENT # As a slave database , Update binary log files when there are write operations log-slave-updates # Represents the amount of each increment of the self growing field , It refers to the starting value of the auto increment field , The default value is 1, The value range is 1 .. 65535 auto-increment-increment=1 # Indicates the number from which the self growing field starts , Refers to how many fields are incremented at a time , His range is 1 .. 65535 auto-increment-offset=1 Dual slave configuration Slave1 To configure Modify the configuration file ： vim /etc/my.cnf # From the server only ID server-id=2 # Enable relay logging relay-log=mysql-relay Slave2 To configure Modify the configuration file ： vim /etc/my.cnf # From the server only ID server-id=4 # Enable relay logging relay-log=mysql-relay # Host computer MySQL To carry out the authorization order in the library GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'root'; # Inquire about Master1 The state of show master status; # Command to copy host CHANGE MASTER TO MASTER_HOST=' The host IP Address ', MASTER_USER='slave', MASTER_PASSWORD='root', MASTER_LOG_FILE='mysql-bin. Specific figures ',MASTER_LOG_POS= Specific value ; Master2 Copy Master1 Master1 Copy Master2 start slave;

111 host 112 Slave 113 host 114 Slave

Test results after successful construction ：

2 Insert any host into the database , Data will be copied from all nodes
2 Data inserted from one node will not be copied to other nodes , Therefore, it is not recommended to operate the database from the node

create database testdb;
use testdb;
create table mytbl(id int,name varchar(20));
select *from mytbl;
insert into mytbl values(1,'zhang3');
insert into mytbl values(2,'li4');

hold 113 The main server stopped at 111 Add a new piece of data ：112 There's data ,113 and 114 No data , And then restart 113,113 and 114 There's data again
hold 111 The main server stopped at 113 Add a new piece of data ： 114 There's data ,111,112 No data , And then restart 111,111,112 There's data again

Atlas build ：

Modify the configuration ：

vim /usr/local/mysql-proxy/conf/test.cnf

#Atlas Back end connected MySQL The main library IP And port , Multiple items can be set , Separate with commas
proxy-backend-addresses = 192.168.25.111:3306,192.168.25.113:3306
          
#Atlas Back end connected MySQL From library IP And port ,@ The numbers that follow represent weights , Used for load balancing , If omitted, it defaults to 1, Multiple items can be set , Separate with commas
proxy-read-only-backend-addresses = 192.168.25.111:3306@1,192.168.25.113:3306@1

# The user name and its corresponding encrypted MySQL password , Password usage PREFIX/bin The encryption program in the directory encrypt encryption , Down user1 and user2 For example , Replace it with your MySQL User name and encryption password for
！
pwds = root:vXwUtsmCOL4a8s/oWZlMvQ==

Yes atlas Pressure test ：tps:3000 about , Storage first 3000w data

40 It's been about ten minutes 100w data , Too slow , I passed mysql Stored procedures to store

You can find ：111 The main library and 112 There is a serious delay from the library , That's counting 111 The main library has 100W Left and right data , however 112 Only 40W Left and right data , There is a serious master-slave delay , I will pass pxc To solve the delay problem

Insert... Using stored procedures 30w data ： Probably spent. 5 Minute speed is OK

drop procedure if exists insert_jb_info;
create procedure insert_jb_info()
 begin
 declare i int;
 set i=1;
 while(i<=300000)do
 insert into jb_info(name, phone, address, create_time, update_time) values(CONCAT('name',i),i,'address','2022-04-18 18:47:19.491','2022-04-18 18:47:19.491');
 set i=i+1;
 end while;
 end;;
 delimiter ;
call insert_jb_info();

  I'm going to put it in storage 3000W data , See if the database can withstand , Can't bear the direct sub warehouse and sub table

You can see 300W Data warehousing took 1 Hours , Good slow

Maybe my computer is on 5 Virtual machines +idea, Also, master-slave replication leads to performance degradation

 1 Hours 52 minute , Total warehousing 5000W data

Found four databases , Each database has 10 Tables , Each watch has 500W data , The most general consistency , In other words, if we don't have high requirements for real-time data, we can pass 2 Lord 2 Always ensure high availability of data , If the real-time requirement is very high , So let's finish the next section pxc colony