GreatSQL

https://greatsql.cn/docs/
Admin
GreatSQL database is an open-source and free database that can meet financial-grade application...
Tokens:619,925
Snippets:6,834
Trust Score:6.4
Update:3 months ago
Show doc for...
Tokens:
Raw
### Compile and Install GreatSQL from Source

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/6-install-with-source-code.html

Execute this command to start the compilation and installation of GreatSQL from source. It sets up environment variables, configures the build with CMake, and then runs make and make install. Log output is redirected to /tmp/greatsql-make.log.

```bash
export MAJOR_VERSION=8 && \
MINOR_VERSION=4 && \
PATCH_VERSION=4 && \
RELEASE=4 && \
REVISION=d73de75905d && \
OPT_DIR=/opt && \
GLIBC=`ldd --version | head -n 1 | awk '{print $NF}'` && \
ARCH=`uname -p` && \
OS=`grep '^ID=' /etc/os-release | sed 's/.*"\(.*\)".*/\1/ig'` && \
GREATSQL=GreatSQL-${MAJOR_VERSION}.${MINOR_VERSION}.${PATCH_VERSION}-${RELEASE}-${OS}-glibc${GLIBC}-${ARCH} && \
MAKELOG=/tmp/greatsql-make.log && \
BOOST="boost_1_77_0" && \
DEST_DIR=${OPT_DIR}/${GREATSQL} && \
cd /opt/greatsql-8.4.4-4/ && \
mkdir bld && \
cd bld && \
cmake .. \
-DBUILD_CONFIG=mysql_release \
-DCMAKE_BUILD_TYPE=RelWithDebInfo \
-DCMAKE_EXE_LINKER_FLAGS=" -ljemalloc" \
-DCOMPILATION_COMMENT="GreatSQL, Release ${RELEASE}, Revision ${REVISION}" \
-DMAJOR_VERSION=${MAJOR_VERSION} -DMINOR_VERSION=${MINOR_VERSION} -DPATCH_VERSION=${PATCH_VERSION} \
-DBOOST_INCLUDE_DIR=${OPT_DIR}/${BOOST} \
-DLOCAL_BOOST_DIR=${OPT_DIR}/${BOOST} \
-DCMAKE_INSTALL_PREFIX=${DEST_DIR} \
-DWITH_ZLIB=bundled \
-DWITH_NUMA=ON \
-DWITH_TOKUDB=OFF \
-DWITH_ROCKSDB=OFF \
-DROCKSDB_DISABLE_AVX2=1 \
-DROCKSDB_DISABLE_MARCH_NATIVE=1 \
-DGROUP_REPLICATION_WITH_ROCKSDB=OFF \
-DALLOW_NO_SSE42=ON \
-DMYSQL_MAINTAINER_MODE=OFF \
-DFORCE_INSOURCE_BUILD=1 \
-DWITH_NDB=OFF \
-DWITH_NDBCLUSTER_STORAGE_ENGINE=OFF \
-DWITH_NDBCLUSTER=OFF \
-DWITH_UNIT_TESTS=OFF \
-DWITH_SSL=system \
-DWITH_SYSTEMD=ON \
-DWITH_AUTHENTICATION_LDAP=OFF \
-DWITH_PAM=1 \
-DWITH_LIBEVENT=bundled \
-DWITH_LDAP=system \
-DWITH_SYSTEM_LIBS=ON \
-DWITH_LZ4=bundled \
-DWITH_PROTOBUF=bundled \
-DWITH_RAPIDJSON=bundled \
-DWITH_ICU=bundled \
-DWITH_READLINE=system \
-DWITH_ZSTD=bundled \
-DWITH_FIDO=bundled \
-DWITH_KEYRING_VAULT=ON \
>> ${MAKELOG} 2>&1 && \
make -j14 >> ${MAKELOG} 2>&1 && \
make -j14 install >> ${MAKELOG} 2>&1
```

--------------------------------

### Start GreatSQL with custom my.cnf via command line

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/9-custom-cnf-file-path.html

When starting GreatSQL directly from the command line, use the --defaults-file option to specify a custom configuration file path. This example sets the path to /opt/my.cnf.

```bash
# 自定义配置文件路径为 /opt/my.cnf
/usr/local/GreatSQL-8.4.4-4-Linux-glibc2.28-x86_64/bin/mysqld --defaults-file=/opt/my.cnf &
```

--------------------------------

### Install GreatDB HA Plugin via SQL

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/5-2-ha-mgr-vip.html

Alternatively, install the greatdb_ha plugin after the database instance has started by executing the SQL command 'INSTALL PLUGIN greatdb_ha SONAME 'greatdb_ha.so';'.

```sql
INSTALL PLUGIN greatdb_ha SONAME 'greatdb_ha.so';

```

--------------------------------

### Start GreatSQL Server

Source: https://greatsql.cn/docs/8.4.4-4/8-mgr/2-mgr-install-deploy.html

Start the GreatSQL server using the specified configuration file in the background.

```bash
/usr/local/GreatSQL-8.4.4-4-Linux-glibc2.28-x86_64/bin/mysqld --defaults-file=/etc/my.cnf &
```

--------------------------------

### Enable and Start Docker Service

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/4-install-with-docker.html

Enable the Docker service to start on boot and then start the service immediately. This ensures Docker is running and available.

```bash
systemctl enable docker
systemctl start docker
```

--------------------------------

### Start GreatSQL with custom my.cnf via mysqld_safe

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/9-custom-cnf-file-path.html

Alternatively, use the mysqld_safe command with the --defaults-file option to specify a custom configuration file path. This example sets the path to /opt/my.cnf.

```bash
# 自定义配置文件路径为 /opt/my.cnf
/usr/local/GreatSQL-8.4.4-4-Linux-glibc2.28-x86_64/bin/mysqld_safe --defaults-file=/opt/my.cnf &
```

--------------------------------

### Install sysbench on Ubuntu

Source: https://greatsql.cn/docs/8.4.4-4/12-dev-guide/12-3-7-gen-sysbench-data.html

Use apt-get to install sysbench on Ubuntu systems. This command ensures sysbench is installed with its default configurations.

```bash
apt-get install sysbench -y

```

--------------------------------

### Install wget Package

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/3-5-anolis-install.html

Install the wget utility, which is used to download the GreatSQL binary package from the specified URL. This is a prerequisite for downloading the installation files.

```bash
yum install -y wget
```

--------------------------------

### Install Docker

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/4-install-with-docker.html

Install Docker using yum/dnf package manager. This is a straightforward method for setting up Docker.

```bash
yum install -y docker
```

--------------------------------

### Start GreatSQL Service

Source: https://greatsql.cn/docs/8.4.4-4/3-quick-start/3-1-quick-start-with-rpm.html

Execute this command to start the GreatSQL service after configuration changes.

```bash
systemctl start mysqld

```

--------------------------------

### Compile and Install patchelf from Source

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/6-install-with-source-code.html

Compiles and installs the patchelf utility from its source code. If patchelf is available via your system's package manager, it is recommended to install it directly using yum/dnf or apt.

```bash
cd /opt/patchelf-0.14.5 && ./bootstrap.sh && ./configure && \
make && make install
```

--------------------------------

### Install Turbo Plugin

Source: https://greatsql.cn/docs/8.4.4-4/1-docs-intro/relnotes/changes-greatsql-8-0-32-27.html

Install the Turbo plugin to enable high-performance parallel query execution.

```sql
greatsql> INSTALL PLUGIN turbo SONAME 'turbo.so';
```

--------------------------------

### Install and Configure Audit Log Filter

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/5-4-security-audit.html

After starting the GreatSQL instance, install the audit log filter component and create a filter rule to log specific user operations. This example configures logging for 'query' and 'table_access' events for the user 'greatsql@%'.

```sql
-- 安装和启用审计日志过滤器组件
greatsql> USE mysql;
greatsql> SOURCE /usr/local/GreatSQL/share/audit_log_filter_linux_install.sql;

-- 创建过滤器，记录用户 greatsql@% 的所有操作
greatsql> SELECT audit_log_filter_set_filter('greatsql_filter',
'{
  "filter": {
    "log": false,
    "class": [
      {
        "name": "connection",
        "event": [
          { "name": "connect", "log": false },
          { "name": "disconnect", "log": false }
        ]
      },
      {
        "name": "query",
        "log": true
      },
      {
        "name": "table_access",
        "event": [
          { "name": "read", "log": true },
          { "name": "write", "log": true },
          { "name": "ddl", "log": false },
          { "name": "dcl", "log": false }
        ]
      }
    ]
  }
}');

-- 分配过滤器给用户 greastql@%
greatsql> SELECT audit_log_filter_set_user('greatsql@%', 'greatsql_filter');

-- 刷新配置（非必须）
greatsql> SELECT audit_log_filter_flush();

-- 验证配置
greatsql> SELECT * FROM mysql.audit_log_filter;
greatsql> SELECT * FROM mysql.audit_log_user;

```

--------------------------------

### Start Database Instance (Full Backup Only)

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/5-5-clone-compressed-and-incrment-backup.html

If only performing a full backup restore, start the GreatSQL service using the prepared my.cnf file.

```bash
cd /data/restore/20240618
/usr/local/GreatSQL-8.0.32-26-Linux-glibc2.28-x86_64/bin/mysqld --defaults-file=./my.cnf &

```

--------------------------------

### Start Database Instance After Restore

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/5-5-clone-compressed-and-incrment-backup.html

After applying all full and incremental backups, start the database instance normally. Ensure there are no errors during startup to confirm a successful restore.

```bash
cd /data/restore/20240618
/usr/local/GreatSQL-8.0.32-26-Linux-glibc2.28-x86_64/bin/mysqld --defaults-file=./my.cnf &
```

--------------------------------

### Explicitly Start a Read-Only Transaction

Source: https://greatsql.cn/docs/8.4.4-4/12-dev-guide/12-6-1-trx-control.html

This example demonstrates how to explicitly start a transaction in READ ONLY mode using `START TRANSACTION READ ONLY`. The `trx_is_read_only` column in `information_schema.INNODB_TRX` will be set to 1 for such transactions.

```sql
greatsql> START TRANSACTION READ ONLY;
greatsql> SELECT * FROM t1;
+----+----+--------+
| id | c1 | c2     |
+----+----+--------+
|  1 |  1 | row111 |
|  3 |  3 | row33  |
|  4 |  4 | row4   |
+----+----+--------+

greatsql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 421166643830136
                 trx_state: RUNNING
               trx_started: 2024-04-25 06:47:39
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 63
                 trx_query: SELECT * FROM information_schema.INNODB_TRX
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 1
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL

```

--------------------------------

### Implicit Commit by Starting a New Transaction

Source: https://greatsql.cn/docs/8.4.4-4/12-dev-guide/12-6-1-trx-control.html

Starting a new transaction with `BEGIN` implicitly commits any preceding uncommitted transaction. This example shows inserting a row and then starting a new transaction, which commits the insert.

```sql
greatsql> BEGIN;
greatsql> SELECT * FROM t1;
+----+----+--------+
| id | c1 | c2     |
+----+----+--------+
|  1 |  1 | row111 |
|  3 |  3 | row33  |
+----+----+--------+

greatsql> INSERT INTO t1 VALUES(4, 4, 'row4');

greatsql> BEGIN; /* 再次开启新事务，会发起隐式提交 */

greatsql> SELECT * FROM t1;
+----+----+--------+
| id | c1 | c2     |
+----+----+--------+
|  1 |  1 | row111 |
|  3 |  3 | row33  |
|  4 |  4 | row4   |
+----+----+--------+

```

--------------------------------

### Create Tables for INSERT ALL Example

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/sql-compat/5-3-easyuse-ora-syntax-insert-all.html

Sets up the necessary tables (t1, t2, t3) with appropriate schemas for demonstrating the INSERT ALL functionality.

```sql
greatsql> CREATE TABLE t3(id INT PRIMARY KEY, cc VARCHAR(10));
greatsql> CREATE TABLE t2 LIKE t3;
greatsql> CREATE TABLE t1 LIKE t3;
greatsql> INSERT INTO t3 VALUES(1, 'test1'),(2,'test2'), (3,'test3');
```

--------------------------------

### pt-upgrade Basic Comparison Example

Source: https://greatsql.cn/docs/8.4.4-4/6-oper-guide/10-5-pt-development.html

Example of comparing two database instances using host arguments and a slow query log file.

```bash
pt-upgrade h=host1 h=host2 slow.log

```

--------------------------------

### Execute Ansible Playbook for Installation

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/5-install-with-ansible.html

Navigate to the Ansible playbook directory and run the `greatsql.yml` playbook to automate the installation, initialization, systemd service setup, and MGR configuration.

```bash
cd /opt/greatsql/GreatSQL-8.0.32-25-Linux-glibc2.17-x86_64-minimal-ansible
ls -la
ansible-playbook ./greatsql.yml

```

--------------------------------

### Prepare my.cnf for Restore

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/5-5-clone-compressed-and-incrment-backup.html

Create a basic my.cnf configuration file for the restored instance. Key parameters like basedir, datadir, and lower_case_table_names should match the original instance. 'skip-networking' is recommended for test environments.

```bash
$ cd /data/restore/20240618
$ cat my.cnf

[mysqld]
basedir = /usr/local/GreatSQL-8.0.32-26-Linux-glibc2.28-x86_64
datadir = /data/restore/20240618
user = mysql
socket = mysql.sock
lower_case_table_names = 1
skip-networking

```

--------------------------------

### Example pt-mysql-summary Command

Source: https://greatsql.cn/docs/8.4.4-4/6-oper-guide/10-2-pt-configuration.html

An example command to run pt-mysql-summary with specific connection parameters. Ensure to replace placeholders with your actual credentials and host information.

```bash
pt-mysql-summary --user=root --password=greatsql --host=localhost --port=3306

```

--------------------------------

### Configure Ansible Installation Variables

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/5-install-with-ansible.html

Modify these variables in `vars.yml` to customize your GreatSQL installation and MGR cluster setup. Pay close attention to `mgr_seeds` and other parameters as noted.

```yaml
work_dir: /opt/greatsql/GreatSQL-8.0.32-25-Linux-glibc2.17-x86_64-minimal-ansible
extract_dir: /usr/local
data_dir: /data/GreatSQL
file_name: GreatSQL-8.0.32-25-Linux-glibc2.17-x86_64-minimal.tar.xz
base_dir: /usr/local/GreatSQL-8.0.32-25-Linux-glibc2.17-x86_64-minimal
my_cnf: /etc/my.cnf
mysql_user: mysql
mysql_port: 3306
mgr_user: GreatSQL
mgr_user_pwd: GreatSQL@2023
mgr_seeds: '172.16.16.10:33061,172.16.16.11:33061,172.16.16.12:33061'
mgr_single_mode: 1
wait_for_start: 60

```

--------------------------------

### Initialize Database for Sysbench

Source: https://greatsql.cn/docs/8.4.4-4/10-optimize/3-1-benchmark-sysbench.html

Prepare the database by creating necessary tables and loading initial data for the sysbench OLTP read-write test. Adjust connection parameters as needed.

```bash
cd /usr/local/share/sysbench
sysbench ./oltp_read_write.lua --db-driver=mysql --mysql-host=x.x.x.x --mysql-port=xxxx --mysql-user=x --mysql-password=x --mysql-db=sbtest --report-interval=1 --percentile=99 --rand-type=uniform --tables=16 --table_size=1000000 --threads=16 --time=600 prepare
```

--------------------------------

### SUBSTRB() Example: Negative Position

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/sql-compat/5-3-easyuse-ora-func-substrb.html

Illustrates using a negative position with SUBSTRB() to extract bytes starting from the end of the string. This example shows extracting from the 10th byte from the end.

```sql
greatsql> SELECT HEX(SUBSTRB(c1, -10, 4)), CONCAT(SUBSTRB(c1, -10, 4), '|'), c1 FROM t1;
+--------------------------+----------------------------------+-----------------------------------+
| HEX(SUBSTRB(c1, -10, 4)) | CONCAT(SUBSTRB(c1, -10, 4), '|') | c1                                |
+--------------------------+----------------------------------+-----------------------------------+
| E7ABAF20                 | 端 |                             | GreatSQL数据库默认端口3306        |
+--------------------------+----------------------------------+-----------------------------------+
```

--------------------------------

### Start and Verify MySQL Router Service

Source: https://greatsql.cn/docs/8.4.4-4/6-oper-guide/2-oper-rw-splitting.html

Starts the MySQL Router service and verifies that it is running and listening on the configured ports. This confirms the read/write splitting and load balancing setup is active.

```bash
$ systemctl start mysqlrouter

$ ps -ef | grep -v grep | grep mysqlrouter
mysqlro+  6026     1  5 09:28 ?        00:00:00 /usr/bin/mysqlrouter

$ netstat -lntp | grep mysqlrouter

... 
tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:6448            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:6449            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      6026/mysqlrouter

```

--------------------------------

### my.cnf Configuration Example

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/2-install-with-rpm.html

This is a comprehensive example of the my.cnf configuration file for GreatSQL. It includes settings for client, server, replication, MGR, and InnoDB. Ensure server_id and server_uuid are unique in replication or MGR setups.

```ini
#my.cnf
[client]
socket	= /data/GreatSQL/mysql.sock
[mysql]
loose-skip-binary-as-hex
prompt = "(\D)[\u@GreatSQL][\d]>"
no-auto-rehash
[mysqld]
user	= mysql
port	= 3306
#主从复制或MGR集群中，server_id记得要不同
#另外，实例启动时会生成 auto.cnf，里面的 server_uuid 值也要不同
#server_uuid的值还可以自己手动指定，只要符合uuid的格式标准就可以
server_id = 3306
basedir = /usr/
datadir	= /data/GreatSQL
socket	= mysql.sock
pid-file = mysql.pid
character-set-server = UTF8MB4
skip_name_resolve = ON
default_time_zone = "+8:00"
bind_address = "0.0.0.0"
secure_file_priv = /data/GreatSQL

# Performance
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 4096
table_definition_cache = 2048
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 64M
net_buffer_shrink_interval = 180
sql_generate_invisible_primary_key = ON
loose-lock_ddl_polling_mode = ON
loose-lock_ddl_polling_runtime = 200

# Logs
log_timestamps = SYSTEM
log_error = error.log
log_error_verbosity = 3
slow_query_log = ON
log_slow_extra = ON
slow_query_log_file = slow.log
long_query_time = 0.01
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = ON
log_slow_replica_statements = ON
log_slow_verbosity = FULL
log_bin = binlog
binlog_format = ROW
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_space_limit = 500G
binlog_rows_query_log_events = ON
binlog_expire_logs_seconds = 604800
binlog_checksum = CRC32
binlog_order_commits = OFF
gtid_mode = ON
enforce_gtid_consistency = ON

# Replication
relay-log = relaylog
relay_log_recovery = ON
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 16
replica_preserve_commit_order = ON
replica_checkpoint_period = 2
loose-rpl_read_binlog_speed_limit=0

# MGR
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
loose-group_replication_local_address = "172.16.16.10:33061"
loose-group_replication_group_seeds = "172.16.16.10:33061,172.16.16.11:33061,172.16.16.12:33061"
loose-group_replication_communication_stack = "XCOM"
loose-group_replication_recovery_use_ssl = OFF
loose-group_replication_ssl_mode = DISABLED
loose-group_replication_start_on_boot = OFF
loose-group_replication_bootstrap_group = OFF
loose-group_replication_exit_state_action = READ_ONLY
loose-group_replication_flow_control_mode = "DISABLED"
loose-group_replication_single_primary_mode = ON
loose-group_replication_enforce_update_everywhere_checks = OFF
loose-group_replication_majority_after_mode = ON
loose-group_replication_communication_max_message_size = 10M
loose-group_replication_arbitrator = OFF
loose-group_replication_single_primary_fast_mode = 1
loose-group_replication_request_time_threshold = 100
loose-group_replication_primary_election_mode = GTID_FIRST
loose-group_replication_unreachable_majority_timeout = 0
loose-group_replication_member_expel_timeout = 5
loose-group_replication_autorejoin_tries = 288
loose-group_replication_recovery_get_public_key = ON
loose-group_replication_donor_threshold = 100
report_host = "172.16.16.10"

# InnoDB
innodb_buffer_pool_size = 2G #如果是专用的数据库服务器，则可以设置为物理内存的50%-70%，视实际情况而定
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M
innodb_redo_log_capacity = 6G
innodb_doublewrite_files=64
innodb_doublewrite_pages = 128
innodb_max_undo_log_size = 4G
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_open_files = 65535
innodb_flush_method = O_DIRECT
innodb_use_fdatasync = ON
innodb_lru_scan_depth=9000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = ON
innodb_print_all_deadlocks = ON
innodb_online_alter_log_max_size = 4G
innodb_print_ddl_logs=OFF
innodb_status_file = ON
innodb_status_output = OFF
innodb_status_output_locks = ON
innodb_sort_buffer_size = 64M
innodb_adaptive_hash_index = OFF
innodb_numa_interleave = OFF
innodb_spin_wait_delay = 20
innodb_print_lock_wait_timeout_info=OFF
innodb_change_buffering = none
kill_idle_transaction = 300
innodb_data_file_async_purge = ON

#innodb monitor settings
#innodb_monitor_enable = "module_innodb,module_server,module_dml,module_ddl,module_trx,module_os,module_purge,module_log,module_lock,module_buffer,module_index,module_ibuf_system,module_buffer_page,module_adaptive_hash"

#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

```

--------------------------------

### Example: Create, Query, and Update a View

Source: https://greatsql.cn/docs/8.4.4-4/12-dev-guide/12-2-5-dp-view.html

Demonstrates creating a view 'v1' for Chinese cities, querying it, and then updating a record through the view.

```sql
greatsql> USE world;
greatsql> CREATE VIEW v1 AS SELECT * FROM city WHERE CountryCode = 'CHN';
greatsql> SELECT * FROM v1 LIMIT 5;

-- 支持用SHOW CREATE VIEW/TABLE 查看视图定义，二者等价
greatsql> SHOW CREATE TABLE v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `city`.`ID` AS `ID`,`city`.`Name` AS `Name`,`city`.`CountryCode` AS `CountryCode`,`city`.`District` AS `District`,`city`.`Population` AS `Population` from `city` where (`city`.`CountryCode` = 'CHN')
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci

greatsql> SHOW CREATE VIEW v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `city`.`ID` AS `ID`,`city`.`Name` AS `Name`,`city`.`CountryCode` AS `CountryCode`,`city`.`District` AS `District`,`city`.`Population` AS `Population` from `city` where (`city`.`CountryCode` = 'CHN')
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci

-- 更新视图并再次查询
greatsql> UPDATE v1 SET Population = 9696400 WHERE ID = 1890;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

greatsql> SELECT * FROM v1 WHERE ID = 1890;
+------+----------+-------------+----------+------------+
| ID   | Name     | CountryCode | District | Population |
+------+----------+-------------+----------+------------+
| 1890 | Shanghai | CHN         | Shanghai |    9696400 |
+------+----------+-------------+----------+------------+

```

--------------------------------

### Check GreatSQL Service Status (Success Example)

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/3-4-uos-install.html

This output confirms that the GreatSQL service has started successfully and is active.

```bash
$ systemctl status greatsql

...● greatsql.service - GreatSQL Server
   Loaded: loaded (/etc/systemd/system/greatsql.service; disabled; vendor preset: disabled)
   Active: active (running) since ...
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 793576 ExecStartPre=/usr/local/GreatSQL-8.4.4-4-Linux-glibc2.28-x86_64/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 793606 (mysqld)
   Memory: 2.5G
   CGroup: /system.slice/greatsql.service
           └─793606 /usr/local/GreatSQL-8.4.4-4-Linux-glibc2.28-x86_64/bin/mysqld

systemd[1]: Starting GreatSQL Server...
systemd[1]: Started GreatSQL Server.

```

```bash
$ ps -ef | grep mysqld

...mysql     818929       1  4 14:08 ?        00:00:07 /usr/local/GreatSQL-8.4.4-4-Linux-glibc2.28-x86_64/bin/mysqld

```

```bash
$ ss -lntp | grep mysqld

...LISTEN 0      70                 *:33060            *:*    users:(("mysqld",pid=60231,fd=38))
LISTEN 0      128                *:3306             *:*    users:(("mysqld",pid=60231,fd=43))

```

--------------------------------

### pt-visual-explain Best Practice Example

Source: https://greatsql.cn/docs/8.4.4-4/6-oper-guide/10-7-pt-performance.html

Example of using pt-visual-explain with the --clustered-pk option to format an EXPLAIN output from MySQL. This is useful for analyzing query plans with clustered primary keys.

```bash
mysql -uroot -p -e "explain select * from test_db.test_t1" |pt-visual-explain --clustered-pk

```

--------------------------------

### Verify Sample Database Installation

Source: https://greatsql.cn/docs/8.4.4-4/12-dev-guide/12-dev-guide.html

Use this command to check the status of tables within the 'employees' database after installation. This confirms that the import process was successful.

```bash
$ mysql -uroot -e 'SHOW TABLE STATUS' employees
+----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name                 | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| current_dept_emp     | NULL   |    NULL | NULL       |    NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2024-04-24 05:37:14 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
| departments          | InnoDB |      10 | Dynamic    |       9 |           1820 |       16384 |               0 |        16384 |         0 |           NULL | 2024-04-24 05:37:12 | 2024-04-24 05:37:15 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| dept_emp             | InnoDB |      10 | Dynamic    |  331143 |             36 |    12075008 |               0 |      5783552 |   4194304 |           NULL | 2024-04-24 05:37:13 | 2024-04-24 05:37:41 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| dept_emp_latest_date | NULL   |    NULL | NULL       |    NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2024-04-24 05:37:14 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
| dept_manager         | InnoDB |      10 | Dynamic    |      24 |            682 |       16384 |               0 |        16384 |         0 |           NULL | 2024-04-24 05:37:12 | 2024-04-24 05:37:41 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| employees            | InnoDB |      10 | Dynamic    |  299996 |             50 |    15220736 |               0 |            0 |   4194304 |           NULL | 2024-04-24 05:37:11 | 2024-04-24 05:37:26 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| expected_values      | InnoDB |      10 | Dynamic    |       6 |           2730 |       16384 |               0 |            0 |         0 |           NULL | 2024-04-24 05:40:54 | 2024-04-24 05:40:55 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| found_values         | InnoDB |      10 | Dynamic    |       5 |           3276 |       16384 |               0 |            0 |         0 |           NULL | 2024-04-24 05:40:54 | 2024-04-24 05:42:31 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| salaries             | InnoDB |      10 | Dynamic    | 2838709 |             33 |    95027200 |               0 |            0 |   4194304 |           NULL | 2024-04-24 05:37:14 | 2024-04-24 05:39:52 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| tchecksum            | InnoDB |      10 | Dynamic    | 3664655 |            132 |   484442112 |               0 |            0 | 212860928 |           NULL | 2024-04-24 05:40:55 | 2024-04-24 05:42:31 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| titles               | InnoDB |      10 | Dynamic    |  443318 |             46 |    20512768 |               0 |            0 |   4194304 |           NULL | 2024-04-24 05:37:13 | 2024-04-24 05:37:58 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
```

--------------------------------

### Launch GreatSQL Shell

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/3-1-centos-install.html

Starts the GreatSQL Shell, indicated by the MySQL Shell prompt, after dependencies are installed and the PATH is configured.

```bash
$ mysqlsh

... MySQL Shell 8.0.32
... Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS >

```

--------------------------------

### pt-fingerprint Command Usage

Source: https://greatsql.cn/docs/8.4.4-4/6-oper-guide/10-1-pt-practical.html

Basic command-line usage for the pt-fingerprint tool. No specific setup is required beyond having the tool installed.

```bash
pt-fingerprint [OPTIONS] [FILES]

```

--------------------------------

### List Sample Database Files

Source: https://greatsql.cn/docs/8.4.4-4/4-install-guide/7-load-sampledb.html

Verify the downloaded and extracted sample database files in the specified directory.

```bash
$ ls -l /tmp/testdb

drwxr-xr-x 2  500  500      100 Aug  1 06:06 sakila-db  #<--sakila 测试数据集
-rw------- 1 root root   732290 Aug  1 06:06 sakila-db.tar.gz
drwx------ 4 root root      460 Aug 29 15:33 test_db    #<--employees 测试数据集
-rw------- 1 root root 35607473 Aug 29 15:32 test_db-1.0.7.tar.gz
drwxr-xr-x 2  500  500       60 Aug  1 06:06 world-db   #<--world 测试数据集
-rw------- 1 root root    92916 Aug  1 06:06 world-db.tar.gz


```

--------------------------------

### Install mysqlbackup Component

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/5-5-clone-compressed-and-incrment-backup.html

Install the mysqlbackup component on the donor instance before performing a clone backup. This is a prerequisite for using the CLONE INSTANCE command.

```sql
INSTALL COMPONENT "file://component_mysqlbackup";

```

--------------------------------

### Handle GreatSQL RPM Dependency Errors

Source: https://greatsql.cn/docs/8.4.4-4/3-quick-start/3-1-quick-start-with-rpm.html

Example of dependency errors encountered during GreatSQL RPM installation. These can often be ignored for basic functionality.

```bash
$ rpm -ivh greatsql*rpm

... 
error: Failed dependencies:
        perl(Lmo) is needed by greatsql-server-8.4.4-4.1.el8.x86_64
        perl(Lmo::Meta) is needed by greatsql-server-8.4.4-4.1.el8.x86_64
        perl(Lmo::Object) is needed by greatsql-server-8.4.4-4.1.el8.x86_64
        perl(Lmo::Types) is needed by greatsql-server-8.4.4-4.1.el8.x86_64
        perl(Lmo::Utils) is needed by greatsql-server-8.4.4-4.1.el8.x86_64
        perl(Percona::Toolkit) is needed by greatsql-server-8.4.4-4.1.el8.x86_64
        perl(Quoter) is needed by greatsql-server-8.4.4-4.1.el8.x86_64
        perl(Transformers) is needed by greatsql-server-8.4.4-4.1.el8.x86_64


```

--------------------------------

### Create Table with Duplicate Indexes

Source: https://greatsql.cn/docs/8.4.4-4/6-oper-guide/10-5-pt-development.html

Example of creating a table and then adding duplicate and redundant indexes. This setup is used to demonstrate the functionality of pt-duplicate-key-checker.

```sql
-- 创建一张test_table表
CREATE TABLE `test_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  `age` INT DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 创建name字段的索引
greatsql> CREATE INDEX `idx_name` ON `test_table` (`name`);

-- 创建age字段的索引
greatsql> CREATE INDEX `idx_age` ON `test_table` (`age`);

-- 创建一个冗余的索引`idx_name_age`
greatsql> CREATE INDEX `idx_name_age` ON `test_table` (`name`, `age`);
```

--------------------------------

### Install Clone Plugin

Source: https://greatsql.cn/docs/8.4.4-4/6-oper-guide/4-5-clone.html

Install the Clone plugin using this SQL command. Ensure the `plugin_dir` option is correctly set.

```sql
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
```

--------------------------------

### Oracle Mode Setup and WHILE Loop SP Example

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/sql-compat/5-3-easyuse-ora-sp-while.html

Sets the SQL mode to ORACLE and creates a stored procedure that uses a WHILE loop to build a string. This example illustrates how to use the WHILE..LOOP..END LOOP construct in GreatSQL for Oracle compatibility.

```sql
greatsql> SET sql_mode = ORACLE;
greatsql> DELIMITER //

CREATE OR REPLACE PROCEDURE while_loop_sp() AS
  v1 INT;
  v2 VARCHAR(255);
BEGIN
  SET v1 = 1;
  SET v2 = 'GreatSQL SP WHILE LOOP ';

  WHILE v1 <= 5 loop
      SET v2 = CONCAT(v2, v1, ',');
      SET v1 = v1 + 1;
  END LOOP;
SELECT v2;
END; //

greatsql> CALL while_loop_sp() //
+-----------------------------------+
| v2                                |
+-----------------------------------+
| GreatSQL SP WHILE LOOP 1,2,3,4,5, |
+-----------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

```

--------------------------------

### Enable Built-in VIP Plugin

Source: https://greatsql.cn/docs/8.4.4-4/9-ha/5-ha-single-vlan.html

To enable the built-in VIP plugin, add 'greatdb_ha.so' to 'plugin_load_add' in the configuration or install it dynamically after the database instance starts.

```ini
plugin_load_add=greatdb_ha.so
```

```sql
INSTALL PLUGIN greatdb_ha SONAME 'greatdb_ha.so';
```

--------------------------------

### Set Up Keyring Directory and File Permissions

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/5-4-security-encrypt-with-gmssl.html

Create a dedicated directory for the master keyring file, ensuring it is separate from the datadir. Set appropriate ownership and permissions for the keyring directory and configuration files.

```shell
# datadir is /data/GreatSQL, ensure separation
mkdir /data/keyring

chown -R mysql:mysql /data/keyring /usr/local/GreatSQL-8.4.4-4-Linux-glibc2.28-x86_64/lib/plugin/component_keyring_file.cnf /usr/local/GreatSQL-8.4.4-4-Linux-glibc2.28-x86_64/bin/mysqld.my

chmod 750 /data/keyring
chmod 0640 /usr/local/GreatSQL-8.4.4-4-Linux-glibc2.28-x86_64/lib/plugin/component_keyring_file.cnf /usr/local/GreatSQL-8.4.4-4-Linux-glibc2.28-x86_64/bin/mysqld.my
```

--------------------------------

### Run BenchmarkSQL Stress Test

Source: https://greatsql.cn/docs/8.4.4-4/10-optimize/3-4-benchmarksql.html

Execute the `runBenchmark.sh` script from the BenchmarkSQL root directory to start the performance test. Ensure Python 2.x is installed for OSCollector.

```bash
$ cd /usr/local/benchmarksql-5.0/run
$ ./runBenchmark.sh ./props.greatsql

```

--------------------------------

### Create and Execute a Stored Procedure

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/sql-compat/5-3-easyuse-ora-syntax-oracle-exec.html

This example demonstrates creating a simple stored procedure and then executing it using the EXEC CALL syntax. Ensure the procedure is dropped if it already exists.

```sql
-- 新建存储过程
greatsql> DROP PROCEDURE IF EXISTS `p1`;

greatsql> DELIMITER //
CREATE PROCEDURE `p1`()
BEGIN
  SELECT 1;
END; //

greatsql> EXEC CALL p1; //
```

--------------------------------

### Setup Test Tables and Data

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/sql-compat/5-3-easyuse-ora-sp-continue.html

These SQL statements create and initialize two test tables, t1 and t2, used in the subsequent stored procedure example.

```sql
greatsql> DROP TABLE IF EXISTS t1, t2;

greatsql> CREATE TABLE IF NOT EXISTS t1(id INT NOT NULL, c1 VARCHAR(100) NOT NULL);
greatsql> INSERT INTO t1 VALUES(1, 'row1'), (2, 'row2'), (3, 'row3');

greatsql> CREATE TABLE IF NOT EXISTS t2(id INT NOT NULL, c1 VARCHAR(100) NOT NULL);
greatsql> INSERT INTO t2 VALUES(10, 'row10'), (20, 'row20'), (30, 'row30');
```

--------------------------------

### Create Tables for MERGE INTO Example

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/sql-compat/5-3-easyuse-ora-syntax-merge-into.html

Sets up two tables, t1 and t2, with appropriate columns and keys to be used in MERGE INTO operations.

```sql
greatsql> CREATE TABLE t1 (
 id BIGINT(10) PRIMARY KEY,
 name VARCHAR(16),
 sale BIGINT(10),
 operatime BIGINT);

greatsql> CREATE TABLE t2(
 id BIGINT(10),
 name VARCHAR(16),
 sale BIGINT(20),
 UNIQUE KEY `idx_id` (`id`));
```

--------------------------------

### Example: Creating a 'test_greatsql' Table

Source: https://greatsql.cn/docs/8.4.4-4/12-dev-guide/12-2-3-dp-table.html

An example demonstrating the creation of a table named 'test_greatsql' with specific columns like id, name, gender, address, and timestamps, including auto-increment for id and default values for timestamps.

```sql
CREATE TABLE test_greatsql(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL,
  gender CHAR(1) NOT NULL,
  address VARCHAR(20) NOT NULL,
  gmt_create DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  gmt_modified DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY(id)
  );

```

--------------------------------

### SUBSTRB() Example: Extracting Bytes

Source: https://greatsql.cn/docs/8.4.4-4/5-enhance/sql-compat/5-3-easyuse-ora-func-substrb.html

Demonstrates extracting a specific number of bytes from a string starting at a given byte position. Note that multi-byte characters may be split, resulting in spaces.

```sql
greatsql> CREATE TABLE t1 ( c1 CHAR(20) );
greatsql> INSERT INTO t1 VALUES ('GreatSQL数据库默认端口3306');

-- 空格的16进制值是20
greatsql> SELECT HEX(SUBSTRB(c1, 8, 6)), CONCAT(SUBSTRB(c1, 8, 6), '|'), c1 FROM t1;
+------------------------+--------------------------------+-----------------------------------+
| HEX(SUBSTRB(c1, 8, 6)) | CONCAT(SUBSTRB(c1, 8, 6), '|') | c1                                |
+------------------------+--------------------------------+-----------------------------------+
| 4CE695B02020           | L数  |                         | GreatSQL数据库默认端口3306        |
+------------------------+--------------------------------+-----------------------------------+
```

```sql
greatsql> SELECT HEX(SUBSTRB(c1, 8, 7)), CONCAT(SUBSTRB(c1, 8, 7), '|'), c1 FROM t1;
+------------------------+--------------------------------+-----------------------------------+
| HEX(SUBSTRB(c1, 8, 7)) | CONCAT(SUBSTRB(c1, 8, 7), '|') | c1                                |
+------------------------+--------------------------------+-----------------------------------+
| 4CE695B0E68DAE         | L数据|                         | GreatSQL数据库默认端口3306        |
+------------------------+--------------------------------+-----------------------------------+
```

```sql
greatsql> SELECT HEX(SUBSTRB(c1, 9, 9)), CONCAT(SUBSTRB(c1, 9, 9), '|'), c1 FROM t1;
+------------------------+--------------------------------+-----------------------------------+
| HEX(SUBSTRB(c1, 9, 9)) | CONCAT(SUBSTRB(c1, 9, 9), '|') | c1                                |
+------------------------+--------------------------------+-----------------------------------+
| E695B0E68DAEE5BA93     | 数据库|                        | GreatSQL数据库默认端口3306        |
+------------------------+--------------------------------+-----------------------------------+
```
Tokens:
Raw