Realization of smooth database expansion for 100 billion-level data smooth expansion

Realization of smooth database expansion for 100 billion-level data smooth expansion

1. Deployment architecture after expansion

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

2. New database VIP

  1. Increase the virtual IP configuration on the Server2 node:

    Modify/etc/keepalived/keepalived.conf and add:

    ... {VI_2 vrrp_instance #vrrp instance defines State the BACKUP #lvs state mode, the main representative of the MASTER, on behalf of the BACKUP backup node interface ens33 # access the external network adapter binding virtual_router_id 112 # virtual routing label, using the same example of a unique identifier vrrp priority 100 # priority stage 100 represents the maximum priority, the larger the number the higher the priority advert_int. 1 #MASTER check and backup node synchronization interval, in seconds authentication { # authentication information provided AUTH_TYPE PASS # PASS and AH have two kinds AUTH_PASS 6666 # verification Password, BACKUP password must be the same } virtual_ipaddress { #KeepAlived virtual IP address 10.10.20.131 } } virtual_server 10.10.20.131 3306 { #Configure virtual server IP and access port delay_loop 6 #health check time persistence_timeout 0 #session retention time, here to be tested, so set to 0, actually can be configured according to the session valid time protocol TCP #forwarding protocol type , Support TCP and UDP real_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, the higher the weight TCP_CHECK { #rStatus monitoring settings connect_timeout 10 #Timeout configuration, in seconds retry 3 #Retry times delay_before_retry 3 #Retry interval connect_port 3306 #Connect port, keep the same as above } } } Copy code

    Pay attention to the configuration items:

    112 virtual_router_id # virtual routing label, using the same example of a unique identifier vrrp priority 100 # priority and 100 is the maximum priority, the larger the number the higher the priority duplicated code

3. Add dynamic data sources to application services

  1. Modify the application service configuration, add a new data source, point to the newly set VIP: 10.10.20.131

    Previously, the dynamic expansion of the application service was realized. Modify it in 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 =to false testOnReturn = to false copy the code
  2. Through the application service interface, the dynamic expansion adjustment allows the second data source of the expansion to take effect through the dynamic adjustment interface.

4. Releasing the original dual-master synchronization relationship

  1. Enter Server1:

    MariaDB [(none)]> stop slave; copy code
  2. Enter Server2:

    MariaDB [(none)]> stop slave; copy code
  3. You can verify whether the data is successfully desynchronized through the application service interface

5. Install MariaDB to expand the server

  1. Create two new virtual machines, corresponding to Server3 and Server4 in the above deployment architecture diagram.

  2. Install MariaDB service on the two nodes of Server3 and Server4

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

  3. Configure Server3 and Server1 to realize the new dual-master synchronization

    1. Server3 node, modify/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 code
    1. Restart the Server3 database
    service mariadb restart copy the code
    1. Create replica users for master-slave synchronization:
    MariaDB [(none)]> grant replication slave, replication client on *.* to'replica' @ '%' identified by'replica' ; mysql> flush privileges; Copy code
    1. On the Server1 node, perform a full data backup:
    mysqldump -uroot -p654321 --routines --single_transaction --master-data=2 --databases smooth> server1.sql Copy code
    1. View and record the 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 code
    1. Copy the backed up server1.sql to the Server3 node through the scp command.
    server1.sql root@10.10.20.127 SCP:/usr/local/ duplicated code
    1. Restore the data to the Server3 node:
    -uroot--p654321 MySQL </usr/local/server1.sql duplicated code
    1. Configure master-slave synchronization information

    According to the above master status information, 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.01 sec) Copy code
    1. Turn on master-slave synchronization:
    MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) Copy code

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

    MariaDB [(none)]> reset slave; Query OK, 0 rows affected (0.01 sec) Copy code
    1. Check synchronization status information:
    MariaDB [(none)]> show slave status/G *************************** 1. row ******************** ******* Slave_IO_State: Waiting for master 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: Yes Copy code
    1. Configure the synchronization of Server1 and Server3 nodes

    View the log information of Server3:

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

    On the Server1 node, 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 code
  4. Configure the new dual-master synchronization relationship between Server4 and Server2

    1. Server4 node, modify/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 code
    1. Restart the Server4 database
    service mariadb restart copy the code
    1. Create replica users for master-slave synchronization:
    MariaDB [(none)]> grant replication slave, replication client on *.* to'replica' @ '%' identified by'replica' ; mysql> flush privileges; Copy code
    1. On the Server2 node, perform a full data backup:
    mysqldump -uroot -p654321 --routines --single_transaction --master-data=2 --databases smooth> server2.sql Copy code
    1. View and record the 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 code
    1. Copy the backed up server2.sql to the Server4 node through the scp command.
    server2.sql root@10.10.20.128 SCP:/usr/local/ duplicated code
    1. Restore the data to the Server4 node:
    -uroot--p654321 MySQL </usr/local/server2.sql duplicated code
    1. Configure master-slave synchronization information

    According to 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.01 sec) Copy code
    1. Turn on master-slave synchronization:
    MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) Copy code

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

    MariaDB [(none)]> reset slave; Query OK, 0 rows affected (0.01 sec) Copy code
    1. Check synchronization status information:
    MariaDB [(none)]> show slave status/G *************************** 1. row ******************** ******* Slave_IO_State: Waiting for master 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: Yes Copy code
    1. Configure the synchronization of Server2 and Server4 nodes

    View the log information of Server4:

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

    On the Server2 node, 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 code

6. Increase KeepAlived service to achieve high availability

  1. Make sure to install the Keepalived service on the newly added Server3 and Server4 nodes.

    KeepAlived service installation can refer to the previous tutorial.

  2. Modify Server3 node configuration

    global_defs { router_id vip3 # Machine ID, generally set to hostname, when a failure occurs, email notification will be used. } {VI_1 vrrp_instance #vrrp instance defines State the BACKUP #lvs state mode, the main representative of the MASTER, on behalf of the BACKUP backup node interface ens33 # access the external network adapter binding virtual_router_id 111 # virtual routing label, using the same example of a unique identifier vrrp priority 98 # priority stage 100 represents the maximum priority, the larger the number the higher the priority advert_int. 1 #MASTER check and backup node synchronization interval, in seconds authentication { # authentication information provided AUTH_TYPE PASS # PASS and AH have two kinds AUTH_PASS 6666 # verification Password, BACKUP password must be the same } virtual_ipaddress { #KeepAlived virtual IP address 10.10.20.130 } } virtual_server 10.10.20.130 3306 { #Configure the virtual server IP and access port delay_loop 6 #health check time persistence_timeout 0 #session retention time, here to be tested, so set to 0, actually can be configured according to the session valid time protocol TCP #forwarding protocol type , Support TCP and UDP real_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, the higher the weight TCP_CHECK { #rStatus monitoring settings connect_timeout 10 #Timeout configuration, in seconds retry 3 #Retry times delay_before_retry 3 #Retry interval connect_port 3306 #Connect port, keep the same as above } } } Copy code

    Note that the IP configuration inside is correct, restart the service after the modification is complete.

    Create the shutdown script mariadb.sh

    /usr/local/shell/mariadb.sh:

    pkill keepalivedCopy code

    Add execution permissions:

    chmod a+x mariadb.shCopy code
  3. Modify Server4 node configuration

    global_defs { router_id vip4 # Machine ID, generally set to hostname, when a failure occurs, email notification will be used. } {VI_1 vrrp_instance #vrrp instance defines State the BACKUP #lvs state mode, the main representative of the MASTER, on behalf of the BACKUP backup node interface ens33 # access the external network adapter binding virtual_router_id 112 # virtual routing label, using the same example of a unique identifier vrrp priority 98 # priority stage 100 represents the maximum priority, the larger the number the higher the priority advert_int. 1 #MASTER check and backup node synchronization interval, in seconds authentication { # authentication information provided AUTH_TYPE PASS # PASS and AH have two kinds AUTH_PASS 6666 # verification Password, BACKUP password must be the same } virtual_ipaddress { #KeepAlived virtual IP address 10.10.20.131 } } virtual_server 10.10.20.131 3306 { #Configure virtual server IP and access port delay_loop 6 #health check time persistence_timeout 0 #session retention time, here to be tested, so set to 0, actually can be configured according to the session valid time protocol TCP #forwarding protocol type , Support TCP and UDP real_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, the higher the weight TCP_CHECK { #rStatus monitoring settings connect_timeout 10 #Timeout configuration, in seconds retry 3 #Retry times delay_before_retry 3 #Retry interval connect_port 3306 #Connect port, keep the same as above } } } Copy code

    Restart the service and create the shutdown script mariadb.sh/usr/local/shell/mariadb.sh:

    pkill keepalivedCopy code

    Add execution permissions:

    chmod a+x mariadb.shCopy code
  4. Modify the keepAlived configuration of the Server2 node:

    global_defs { router_id vip2 # Machine ID, generally set to hostname, when a failure occurs, email notification will be used. } {VI_1 vrrp_instance #vrrp instance defines State the BACKUP #lvs state mode, the main representative of the MASTER, on behalf of the BACKUP backup node interface ens33 # access the external network adapter binding virtual_router_id 112 # virtual routing label, using the same example of a unique identifier vrrp priority 100 # priority stage 100 represents the maximum priority, the larger the number the higher the priority advert_int. 1 #MASTER check and backup node synchronization interval, in seconds authentication { # authentication information provided AUTH_TYPE PASS # PASS and AH have two kinds AUTH_PASS 6666 # verification Password, BACKUP password must be the same } virtual_ipaddress { #KeepAlived virtual IP address 10.10.20.131 } } virtual_server 10.10.20.131 3306 { #Configure virtual server IP and access port delay_loop 6 #health check time persistence_timeout 0 #session retention time, here to be tested, so set to 0, actually can be configured according to the session valid time protocol TCP #forwarding protocol type , Support TCP and UDP real_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, the higher the weight TCP_CHECK { #rStatus monitoring settings connect_timeout 10 #Timeout configuration, in seconds retry 3 #Retry times delay_before_retry 3 #Retry interval connect_port 3306 #Connect port, keep the same as above } } } Copy code

    After modification, restart the Keepalived service.

7. Clean up data and verify

  1. Adjust and verify through the application service dynamic expansion interface

  2. Clean up data on Server1 node

    According to the modulus rule, keep the data whose accountNo is an even number

    from t_trade_order Delete WHERE accountNo% 2! = 0 duplicated code
  3. Clean up the data on the Server2 node

    According to the modulus rule, keep the data whose accountNo is an odd number

    from t_trade_order Delete WHERE accountNo% 2! =. 1 duplicated code

This article is created and shared by mirson. If you need further communication, please join the QQ group: 19310171 or visit www.softart.cn