Database Tool Recommendations

12002

1. Data Comparison Tool: Diff

【Tool Introduction】:

Jianghu JS provides a basic list diff tool for data synchronization, which can quickly calculate the comparison between two lists, including which elements have been added, deleted, or modified.

【Using Diff】:

Import diff

const diff = require('@jianghujs/jianghu/app/common/diffUtil');

Usage

const res = diff(oldList, newList, primaryKey);  
// or  
const res = diff(oldList, newList); // primaryKey = 'id'

Parameter Description:

oldList: The old data list to compare

newList: The new data list to compare

primaryKey: The primary key used for comparison; defaults to 'id' if not specified. When specified, the comparison process will ignore the id field in the data and use the primaryKey field instead to compare other non-id fields.

Use Case 1: Compare using id as the primary key

const res = diff(  
  [  
    { id: 1, name: 'a' },  
    { id: 2, name: 'b' },  
    { id: 3, name: 'c' },  
    { id: 4, name: 'd' },  
    { id: 5, name: 'e' },  
  ],  
  [  
    { id: 1, name: 'a' },  
    { id: 2, name: 'x' },  
    { id: 7, name: 'e' },  
  ]  
);  
console.log(JSON.stringify(res, null, '  '));  
// Output  
// {  
//   "added": [  
//     { "id": 7, "name": "e" }  
//   ],  
//   "removed": [  
//     { "id": 3, "name": "c" },  
//     { "id": 4, "name": "d" },  
//     { "id": 5, "name": "e" }  
//   ],  
//   "changed": [  
//     {  
//       "old": { "id": 2, "name": "b" },  
//       "new": { "id": 2, "name": "x" }  
//     }  
//   ]  
// }

Use Case 2: Specify primary key for comparison

When the data sources for comparison are views and tables, since the view data may not have an id but only a business primary key, it is necessary to specify the primary key for comparison.

const res = diff(  
  [  
    { id: 1, dataId: 'I', name: 'a' },  
    { id: 2, dataId: 'II', name: 'b' },  
    { id: 3, dataId: 'III', name: 'c' },  
    { id: 4, dataId: 'IV', name: 'd' },  
    { id: 5, dataId: 'V', name: 'e' },  
  ],  
  [ // From view, no primary key  
    { dataId: 'I', name: 'a' },  
    { dataId: 'II', name: 'x' },  
    { dataId: 'VII', name: 'e' },  
  ],  
  'dataId'  
);  
console.log(JSON.stringify(res, null, '  '));  
// Output  
// {  
//   "added": [  
//     { "dataId": "VII", "name": "e" }  
//   ],  
//   "removed": [  
//     { "id": 3, "dataId": "III", "name": "c" },  
//     { "id": 4, "dataId": "IV", "name": "d" },  
//     { "id": 5, "dataId": "V", "name": "e" }  
//   ],  
//   "changed": [  
//     {  
//       "old": { "id": 2, "dataId": "II", "name": "b" },  
//       "new": { "dataId": "II", "name": "x" }  
//     }  
//   ]  
// }

2. Database Backup: Xtrabackup

【Tool Introduction】:

xtrabackup is an online hot backup tool based on InnoDB developed by Vadim, CTO of Percona. It is open-source, free, supports online hot backups, has fast backup and recovery speeds, occupies less disk space, and supports various backup forms under different conditions.

Xtrabackup includes two main tools: xtrabackup and innobackupex:

xtrabackup can only back up tables using the InnoDB and XtraDB engines, and cannot back up tables using the MyISAM engine.

innobackupex is a Perl script that wraps xtrabackup, supporting simultaneous backups of InnoDB and MyISAM, but requires a global read lock when backing up MyISAM. Additionally, MyISAM does not support incremental backups.

【Features】:

Hot Backup: Xtrabackup supports hot backups, allowing backups to be made while the database is running without stopping the database service.
Incremental Backup: Xtrabackup provides incremental backup functionality, allowing the creation of incremental backups that only include changes based on a previous full backup, thus reducing the time and space required for backups.
Compression and Streaming Backup: Xtrabackup supports compressing backup data to save storage space and can stream backup data to other servers, facilitating the transfer and storage of backup data.
Recovery: Xtrabackup provides recovery tools that can restore the entire database to a specific point in time using backup data.

【Code Example】:

Here is a simple example of using Xtrabackup for database backup and recovery.

# Create full backup  
xtrabackup --backup --target-dir=/path/to/backup  

# Create incremental backup  
xtrabackup --backup --target-dir=/path/to/incremental_backup --incremental-basedir=/path/to/backup  

# Restore full backup  
xtrabackup --prepare --target-dir=/path/to/backup  

# Restore incremental backup  
xtrabackup --prepare --apply-log-only --target-dir=/path/to/incremental_backup --incremental-dir=/path/to/backup  

# Restore database  
xtrabackup --copy-back --target-dir=/path/to/backup

The commands here demonstrate the basic usage of full backup, incremental backup, and recovery. Actual usage may require parameter adjustments based on database version and specific needs.

【XtraBackup Installation】:

MySQL 5.7 requires version 2.4

Installation Guide

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm  
yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

【Configure Scheduled Backups】:

Upload xtrabackup.sh to the server at /www/backup/xtrabackup.sh

#!/bin/bash  
timestamp=$(date +%Y%m%d_%H%M%S)  
rm -rf /www/backup/xtrabackup_data/  
mkdir -p /www/backup/xtrabackup_data  
xtrabackup --backup --user=root --port=3306 --password=123456 --target-dir=/www/backup/xtrabackup_data &>> /www/wwwlogs/xtrabackup.log  
mkdir -p /www/backup/xtrabackup_data_history  
zip -r /www/backup/xtrabackup_data_history/xtrabackup_data_$timestamp.zip /www/backup/xtrabackup_data  
echo "  backup file output====>  /www/backup/xtrabackup_data_history/xtrabackup_data_$timestamp.zip"

Use Linux Crontab to schedule the execution of /www/backup/xtrabackup.sh every day at 1 AM

【Using Xtrabackup Backup Files for Recovery】:

Extract the backup file
First, you need to extract the Xtrabackup backup file. Typically, backup files are archived in .tar or .tar.gz format. You can use the tar command to extract the backup file:

$ tar -xvf backup.tar.gz

Stop MySQL Service
Before restoring the backup, you need to stop the MySQL server. You can use the following command to stop the MySQL server:

$ sudo service mysql stop

Prepare the Backup File
After extracting the backup file, you need to prepare the backup file using the innobackupex tool. This tool will restore all InnoDB tablespaces of the backup file to the specified directory. You can use the following command to prepare the backup file:

$ innobackupex --apply-log /path/to/backup/directory

Restore the Backup
Once the backup file is prepared, you can use the innobackupex tool to restore the backup to the MySQL server. You can use the following command to restore the backup:

$ innobackupex --copy-back /path/to/backup/directory

Change File Permissions
After the backup restoration is complete, you need to change the permissions of the data directory so that the MySQL server can access it. You can use the following command to change the directory permissions:

$ sudo chown -R mysql:mysql /var/lib/mysql

Start MySQL Service
Finally, you need to start the MySQL server and verify whether the backup has been successfully restored. You can use the following command to start the MySQL server:

$ sudo service mysql start

【FAQ】

Question: _resource_request_log occupies too much disk space
Question Fix:

1. Data Cleanup: Schedule a task to clean up historical data in _resource_request_log  
2. Reduce New Data: jianghuConfig: { ignoreListOfResourceRequestLog: [  
        'allPage.getConstantList', 'allPage.httpUploadByStream', 'allPage.httpUploadByBase64', 'allPage.httpDownloadByBase64',  
        'socket.disconnect', 'socket.connect', 'index.pingRecord', 'chat.getGroupInfo',  
        'allPage.getUserGroupRoleList', 'allPage.userInfo', 'chat.getMessageHistory'  
      ]}  
3. Optimize Table Space: Add `optimize table _resource_request_log;` to the scheduled task

These two tools are used for database comparison and backup, providing important support for developers in database management and maintenance.

## 1. Data Comparison Tool: Diff

【Tool Introduction】:

Jianghu JS provides a basic list diff tool for data synchronization, which can quickly calculate the comparison between two lists, including which elements have been added, deleted, or modified.

【Using Diff】:

- Import diff

```javascript  
const diff = require('@jianghujs/jianghu/app/common/diffUtil');  
```

- Usage

```javascript  
const res = diff(oldList, newList, primaryKey);  
// or  
const res = diff(oldList, newList); // primaryKey = 'id'  
```  
> Parameter Description:  
> - oldList: The old data list to compare  
> - newList: The new data list to compare  
> - primaryKey: The primary key used for comparison; defaults to 'id' if not specified. When specified, the comparison process will ignore the id field in the data and use the primaryKey field instead to compare other non-id fields.

- Use Case 1: Compare using id as the primary key  
```javascript  
const res = diff(  
  [  
    { id: 1, name: 'a' },  
    { id: 2, name: 'b' },  
    { id: 3, name: 'c' },  
    { id: 4, name: 'd' },  
    { id: 5, name: 'e' },  
  ],  
  [  
    { id: 1, name: 'a' },  
    { id: 2, name: 'x' },  
    { id: 7, name: 'e' },  
  ]  
);  
console.log(JSON.stringify(res, null, '  '));  
// Output  
// {  
//   "added": [  
//     { "id": 7, "name": "e" }  
//   ],  
//   "removed": [  
//     { "id": 3, "name": "c" },  
//     { "id": 4, "name": "d" },  
//     { "id": 5, "name": "e" }  
//   ],  
//   "changed": [  
//     {  
//       "old": { "id": 2, "name": "b" },  
//       "new": { "id": 2, "name": "x" }  
//     }  
//   ]  
// }  
```

- Use Case 2: Specify primary key for comparison

When the data sources for comparison are views and tables, since the view data may not have an id but only a business primary key, it is necessary to specify the primary key for comparison.  
```javascript  
const res = diff(  
  [  
    { id: 1, dataId: 'I', name: 'a' },  
    { id: 2, dataId: 'II', name: 'b' },  
    { id: 3, dataId: 'III', name: 'c' },  
    { id: 4, dataId: 'IV', name: 'd' },  
    { id: 5, dataId: 'V', name: 'e' },  
  ],  
  [ // From view, no primary key  
    { dataId: 'I', name: 'a' },  
    { dataId: 'II', name: 'x' },  
    { dataId: 'VII', name: 'e' },  
  ],  
  'dataId'  
);  
console.log(JSON.stringify(res, null, '  '));  
// Output  
// {  
//   "added": [  
//     { "dataId": "VII", "name": "e" }  
//   ],  
//   "removed": [  
//     { "id": 3, "dataId": "III", "name": "c" },  
//     { "id": 4, "dataId": "IV", "name": "d" },  
//     { "id": 5, "dataId": "V", "name": "e" }  
//   ],  
//   "changed": [  
//     {  
//       "old": { "id": 2, "dataId": "II", "name": "b" },  
//       "new": { "dataId": "II", "name": "x" }  
//     }  
//   ]  
// }  
```

## 2. Database Backup: Xtrabackup

【Tool Introduction】:

[xtrabackup](http://www.percona.com/software/percona-xtrabackup) is an online hot backup tool based on InnoDB developed by Vadim, CTO of Percona. It is open-source, free, supports online hot backups, has fast backup and recovery speeds, occupies less disk space, and supports various backup forms under different conditions.

Xtrabackup includes two main tools: xtrabackup and innobackupex:

> - xtrabackup can only back up tables using the InnoDB and XtraDB engines, and cannot back up tables using the MyISAM engine.

> - innobackupex is a Perl script that wraps xtrabackup, supporting simultaneous backups of InnoDB and MyISAM, but requires a global read lock when backing up MyISAM. Additionally, MyISAM does not support incremental backups.

【Features】:

- Hot Backup: Xtrabackup supports hot backups, allowing backups to be made while the database is running without stopping the database service.  
- Incremental Backup: Xtrabackup provides incremental backup functionality, allowing the creation of incremental backups that only include changes based on a previous full backup, thus reducing the time and space required for backups.  
- Compression and Streaming Backup: Xtrabackup supports compressing backup data to save storage space and can stream backup data to other servers, facilitating the transfer and storage of backup data.  
- Recovery: Xtrabackup provides recovery tools that can restore the entire database to a specific point in time using backup data.

【Code Example】:

Here is a simple example of using Xtrabackup for database backup and recovery.

```bash  
# Create full backup  
xtrabackup --backup --target-dir=/path/to/backup

# Create incremental backup  
xtrabackup --backup --target-dir=/path/to/incremental_backup --incremental-basedir=/path/to/backup

# Restore full backup  
xtrabackup --prepare --target-dir=/path/to/backup

# Restore incremental backup  
xtrabackup --prepare --apply-log-only --target-dir=/path/to/incremental_backup --incremental-dir=/path/to/backup

# Restore database  
xtrabackup --copy-back --target-dir=/path/to/backup  
```

The commands here demonstrate the basic usage of full backup, incremental backup, and recovery. Actual usage may require parameter adjustments based on database version and specific needs.

【XtraBackup Installation】:

> MySQL 5.7 requires version 2.4  
>  
> [Installation Guide](https://www.percona.com/doc/percona-xtrabackup/2.4/installation/yum_repo.html#installing-percona-xtrabackup-from-percona-yum-repository)  
```bash  
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm  
yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm  
```

【Configure Scheduled Backups】:

1. Upload **xtrabackup.sh** to the server at `/www/backup/xtrabackup.sh`  
```bash  
#!/bin/bash  
timestamp=$(date +%Y%m%d_%H%M%S)  
rm -rf /www/backup/xtrabackup_data/  
mkdir -p /www/backup/xtrabackup_data  
xtrabackup --backup --user=root --port=3306 --password=123456 --target-dir=/www/backup/xtrabackup_data &>> /www/wwwlogs/xtrabackup.log  
mkdir -p /www/backup/xtrabackup_data_history  
zip -r /www/backup/xtrabackup_data_history/xtrabackup_data_$timestamp.zip /www/backup/xtrabackup_data  
echo "  backup file output====>  /www/backup/xtrabackup_data_history/xtrabackup_data_$timestamp.zip"  
```  
2. Use Linux Crontab to schedule the execution of `/www/backup/xtrabackup.sh` every day at 1 AM  
![](/media/202203/2022-03-14_170505_909457.png)

【Using Xtrabackup Backup Files for Recovery】:

1. Extract the backup file  
First, you need to extract the Xtrabackup backup file. Typically, backup files are archived in .tar or .tar.gz format. You can use the tar command to extract the backup file:

```bash  
$ tar -xvf backup.tar.gz  
```

2. Stop MySQL Service  
Before restoring the backup, you need to stop the MySQL server. You can use the following command to stop the MySQL server:

```bash  
$ sudo service mysql stop  
```

3. Prepare the Backup File  
After extracting the backup file, you need to prepare the backup file using the innobackupex tool. This tool will restore all InnoDB tablespaces of the backup file to the specified directory. You can use the following command to prepare the backup file:

```bash  
$ innobackupex --apply-log /path/to/backup/directory  
```

4. Restore the Backup  
Once the backup file is prepared, you can use the innobackupex tool to restore the backup to the MySQL server. You can use the following command to restore the backup:

```bash  
$ innobackupex --copy-back /path/to/backup/directory  
```

5. Change File Permissions  
After the backup restoration is complete, you need to change the permissions of the data directory so that the MySQL server can access it. You can use the following command to change the directory permissions:

```bash  
$ sudo chown -R mysql:mysql /var/lib/mysql  
```

6. Start MySQL Service  
Finally, you need to start the MySQL server and verify whether the backup has been successfully restored. You can use the following command to start the MySQL server:

```bash  
$ sudo service mysql start  
```

【FAQ】

Question: _resource_request_log occupies too much disk space  
Question Fix:  
```javascript  
1. Data Cleanup: Schedule a task to clean up historical data in _resource_request_log  
2. Reduce New Data: jianghuConfig: { ignoreListOfResourceRequestLog: [  
        'allPage.getConstantList', 'allPage.httpUploadByStream', 'allPage.httpUploadByBase64', 'allPage.httpDownloadByBase64',  
        'socket.disconnect', 'socket.connect', 'index.pingRecord', 'chat.getGroupInfo',  
        'allPage.getUserGroupRoleList', 'allPage.userInfo', 'chat.getMessageHistory'  
      ]}  
3. Optimize Table Space: Add `optimize table _resource_request_log;` to the scheduled task  
```  
These two tools are used for database comparison and backup, providing important support for developers in database management and maintenance.