Billions of data smooth expansion of the database smooth expansion

1. Deployment architecture after capacity expansion

The former two pairs of dual-master nodes are expanded to two pairs of dual-master nodes, with a total of four nodes:

2. Add database VIP

Add a virtual IP address to Server2:

Modify/etc/keepalived/keepalived. Conf, additional:

. vrrp_instance VI_2 {# VRRP instance definition
    state BACKUP               MASTER indicates the MASTER node and BACKUP indicates the BACKUP node
    interface ens33               Bind the network card for external access
    virtual_router_id 112        # Virtual route identifier. A VRRP instance uses a unique identifier
    priority 100               100 indicates the highest priority. A higher number indicates a higher priority
    advert_int 1              # Interval for checking synchronization between master and backup nodes, in seconds
    authentication {           Set authentication information
        auth_type PASS         There are two kinds of PASS and AH
        auth_pass 6666         BACKUP password must be the same
    }
    virtual_ipaddress {         KeepAlived Virtual IP address10.10.20.131}} virtual_server 10.10.20.131 3306 {Configure the virtual server IP address and access port
    delay_loop 6                 # Health check time
    persistence_timeout 0        The session duration is set to 0 based on the session duration
    protocol TCP               The forwarding protocol can be TCP or UDPReal_server 10.10.20.126 3306 {Configure server node VIP1
    notify_down /usr/local/shell/mariadb.sh
    weight 1               # Set the weight, the larger the weight is higher
    TCP_CHECK {              #r Status monitoring Settings
       connect_timeout 10       Timeout is configured in seconds
       retry 3             # retries
       delay_before_retry 3        Retry interval
       connect_port 3306         Connect port, same as above}}}Copy the code

Note the following configuration items:

virtual_router_id 112        # Virtual route identifier. A VRRP instance uses a unique identifier
priority 100               100 indicates the highest priority. A higher number indicates a higher priority
Copy the code

3. Add dynamic data sources to application services

Modify the application service configuration and add a new data source pointing to the new setting of VIP: 10.10.20.131

Dynamic expansion of application services has been implemented previously. Modify the configuration file:

driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=654321
url=JDBC: mysql: / / 10.10.20.131:3306 / smooth? useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC
initialSize=5
minIdle=5
maxActive=20
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
Copy the code

Dynamically adjust capacity expansion through application service interfacesDynamically adjust the interface to make the expansion of the second data source take effect.

4. Remove the original synchronization relationship

Enter Server1:

MariaDB [(none)]> stop slave;
Copy the code

Enter the Server2:

MariaDB [(none)]> stop slave;
Copy the code

You can verify whether data synchronization is successful through the application service interface

5. Install the MariaDB expansion server

Create two VMS corresponding to Server3 and Server4 in the deployment architecture diagram.
Install the MariaDB service on Server3 and Server4

Refer to the MariaDB service installation tutorial in the previous chapter.

Configure Server3 and Server1 to implement dual-master synchronization

Server3: /etc/my.cnf:

[mysqld]
server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=2
auto-increment-increment=2
binlog_format=mixed
expire_logs_days=10

Copy the code

Restart the Server3 database

service mariadb restart
Copy the code

Create replicas for primary/secondary synchronization:

MariaDB [(none)]> grant replication slave, replication client on *.* to 'replica'@The '%' identified by 'replica';
mysql> flush privileges;
Copy the code

Perform full data backup on the Server1 node:

mysqldump -uroot -p654321 --routines --single_transaction --master-data=2 --databases smooth > server1.sql

Copy the code

View and record master status information:

. -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=17748; .Copy the code

Copy the backup server1. SQL to the Server3 node using the SCP command.

SCP server1. SQL [email protected]: / usr /local/
Copy the code

Restore data to Server3 node:

mysql -uroot -p654321 < /usr/local/server1.sql
Copy the code

Configure primary/secondary synchronization information

Based on the master status information above, execute in Server3:

MariaDB [(none)]> change master to master_host='10.10.20.125',master_user='replica', master_password='replica', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos=17748, master_connect_retry=30; Query OK, 0 rows affected (0.01sec)Copy the code

Enable primary/secondary synchronization:

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

Copy the code

If there is a problem, restore the master/slave synchronization information:

MariaDB [(none)]> reset slave; Query OK, 0 rows affected (0.01sec)Copy the code

Checking the synchronization status:

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting forMaster to send event Master_Host: 10.10.20.125 Master_User: Replica Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 11174 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 1746 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: YesCopy the code

Configure synchronization between Server1 and Server3

View the logs of Server3:

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     4781 |              |                  |
+------------------+----------+--------------+------------------+

Copy the code

On Server1, configure synchronization information:

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> change master to master_host='10.10.20.127',master_user='replica', master_password='replica', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=4781, master_connect_retry=30;

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

Copy the code

Configure the dual-master synchronization relationship between Server4 and Server2

Server4: /etc/my.cnf:

[mysqld]
server-id = 3
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=2
auto-increment-increment=2
binlog_format=mixed
expire_logs_days=10

Copy the code

Restart the Server4 database

service mariadb restart
Copy the code

Create replicas for primary/secondary synchronization:

MariaDB [(none)]> grant replication slave, replication client on *.* to 'replica'@The '%' identified by 'replica';
mysql> flush privileges;
Copy the code

Perform full data backup on the Server2 node:

mysqldump -uroot -p654321 --routines --single_transaction --master-data=2 --databases smooth > server2.sql

Copy the code

View and record master status information:

. -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=4208; .Copy the code

Copy the backup server2. SQL file to the Server4 node using the SCP command.

SCP server2. SQL [email protected]: / usr /local/
Copy the code

Restore data to Server4 node:

mysql -uroot -p654321 < /usr/local/server2.sql
Copy the code

Configure primary/secondary synchronization information

Based on the master status information above, execute in Server4:

MariaDB [(none)]> change master to master_host='10.10.20.126',master_user='replica', master_password='replica', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=4208, master_connect_retry=30; Query OK, 0 rows affected (0.01sec)Copy the code

Enable primary/secondary synchronization:

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

Copy the code

Note that if there is a problem, restore the master/slave synchronization information:

MariaDB [(none)]> reset slave; Query OK, 0 rows affected (0.01sec)Copy the code

Checking the synchronization status:

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting forMaster to send event Master_Host: 10.10.20.125 Master_User: Replica Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 11174 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 1746 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: YesCopy the code

Configure synchronization between Server2 and Server4

View the logs of Server4:

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     3696 |              |                  |
+------------------+----------+--------------+------------------+

Copy the code

On Server2, configure synchronization information:

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> change master to master_host='10.10.20.128',master_user='replica', master_password='replica', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=3696, master_connect_retry=30;

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

Copy the code

6. Add KeepAlived service for high availability

Ensure that the Keepalived service is installed on the new Server3 and Server4 nodes.

KeepAlived service installation can refer to the previous tutorial.

Modify Server3 node configurations

global_defs {
   router_id vip3          The machine identifier, usually set to hostname, will be used for email notification when a fault occurs.
}
vrrp_instance VI_1 {            # VRRP instance definition
    state BACKUP               MASTER indicates the MASTER node and BACKUP indicates the BACKUP node
    interface ens33               Bind the network card for external access
    virtual_router_id 111        # Virtual route identifier. A VRRP instance uses a unique identifier
    priority 98               100 indicates the highest priority. A higher number indicates a higher priority
    advert_int 1              # Interval for checking synchronization between master and backup nodes, in seconds
    authentication {           Set authentication information
        auth_type PASS         There are two kinds of PASS and AH
        auth_pass 6666         BACKUP password must be the same
    }
    virtual_ipaddress {         KeepAlived Virtual IP address10.10.20.130}} virtual_server 10.10.20.130 3306 {Configure the virtual server IP address and access port
    delay_loop 6                 # Health check time
    persistence_timeout 0        The session duration is set to 0 based on the session duration
    protocol TCP               The forwarding protocol can be TCP or UDPReal_server 10.10.20.127 3306 {Configure server node VIP3
    notify_down /usr/local/shell/mariadb.sh
    weight 1               # Set the weight, the larger the weight is higher
    TCP_CHECK {              #r Status monitoring Settings
       connect_timeout 10       Timeout is configured in seconds
       retry 3             # retries
       delay_before_retry 3        Retry interval
       connect_port 3306         Connect port, same as above}}}Copy the code

Ensure that the IP address is correctly configured. Restart the service after the modification.

Create the close script mariadb.sh

/ usr/local/shell/mariadb. Sh:

pkill keepalived
Copy the code

Add execution permission:

chmod a+x mariadb.sh
Copy the code

Modify Server4 node configurations

global_defs {
   router_id vip4          The machine identifier, usually set to hostname, will be used for email notification when a fault occurs.
}
vrrp_instance VI_1 {            # VRRP instance definition
    state BACKUP               MASTER indicates the MASTER node and BACKUP indicates the BACKUP node
    interface ens33               Bind the network card for external access
    virtual_router_id 112        # Virtual route identifier. A VRRP instance uses a unique identifier
    priority 98               100 indicates the highest priority. A higher number indicates a higher priority
    advert_int 1              # Interval for checking synchronization between master and backup nodes, in seconds
    authentication {           Set authentication information
        auth_type PASS         There are two kinds of PASS and AH
        auth_pass 6666         BACKUP password must be the same
    }
    virtual_ipaddress {         KeepAlived Virtual IP address10.10.20.131}} virtual_server 10.10.20.131 3306 {Configure the virtual server IP address and access port
    delay_loop 6                 # Health check time
    persistence_timeout 0        The session duration is set to 0 based on the session duration
    protocol TCP               The forwarding protocol can be TCP or UDPReal_server 10.10.20.128 3306 {Configure server node VIP4
    notify_down /usr/local/shell/mariadb.sh
    weight 1               # Set the weight, the larger the weight is higher
    TCP_CHECK {              #r Status monitoring Settings
       connect_timeout 10       Timeout is configured in seconds
       retry 3             # retries
       delay_before_retry 3        Retry interval
       connect_port 3306         Connect port, same as above}}}Copy the code

Restart the service, create a closed script mariadb. Sh/usr/local/shell/mariadb. Sh:

pkill keepalived
Copy the code

Add execution permission:

chmod a+x mariadb.sh
Copy the code

Modify keepAlived configuration for Server2 nodes:

global_defs {
   router_id vip2          The machine identifier, usually set to hostname, will be used for email notification when a fault occurs.
}   
vrrp_instance VI_1 {            # VRRP instance definition
    state BACKUP               MASTER indicates the MASTER node and BACKUP indicates the BACKUP node
    interface ens33               Bind the network card for external access
    virtual_router_id 112        # Virtual route identifier. A VRRP instance uses a unique identifier
    priority 100               100 indicates the highest priority. A higher number indicates a higher priority
    advert_int 1              # Interval for checking synchronization between master and backup nodes, in seconds
    authentication {           Set authentication information
        auth_type PASS         There are two kinds of PASS and AH
        auth_pass 6666         BACKUP password must be the same
    }
    virtual_ipaddress {         KeepAlived Virtual IP address10.10.20.131}} virtual_server 10.10.20.131 3306 {Configure the virtual server IP address and access port
    delay_loop 6                 # Health check time
    persistence_timeout 0        The session duration is set to 0 based on the session duration
    protocol TCP               The forwarding protocol can be TCP or UDPReal_server 10.10.20.126 3306 {Configure server node VIP1
    notify_down /usr/local/shell/mariadb.sh
    weight 1               # Set the weight, the larger the weight is higher
    TCP_CHECK {              #r Status monitoring Settings
       connect_timeout 10       Timeout is configured in seconds
       retry 3             # retries
       delay_before_retry 3        Retry interval
       connect_port 3306         Connect port, same as above}}}Copy the code

After modification, restart the Keepalived service.

7. Clear and verify data

Adjust and verify the dynamic interface expansion through application services
Clear data on the Server1 node

Data whose accountNo is even is retained according to the modulo rule
```
delete from t_trade_order whereaccountNo % 2 ! = 0Copy the code
```
Clear data on the Server2 node

The data whose accountNo is odd is reserved according to the module fetching rule
```
delete from t_trade_order whereaccountNo % 2 ! = 1Copy the code
```

This article was created and shared by Mirson. For further communication, please add to QQ group 19310171 or visit www.softart.cn

Billions of data smooth expansion of the database smooth expansion

1. Deployment architecture after capacity expansion

2. Add database VIP

3. Add dynamic data sources to application services

4. Remove the original synchronization relationship

5. Install the MariaDB expansion server

6. Add KeepAlived service for high availability

7. Clear and verify data

Related Posts

[stay up late liver] many days – dynamic programming ten – super delicate analysis

ES Slow query optimization scheme Profile API

The technical architecture selection of log analysis system under the number of billions