MariaDB 10.3 MyRocks 实例搭建，mariadbmyrocks

MariaDB 10.3 MyRocks 实例搭建，mariadbmyrocks

运行环境：CentOS 7.5+MariaDB 10.3 +MyRocks 存储引擎

背景：
想搭建一个运行默认存储引擎是MyRocks的MySQL实例，来和InnoDB比对下。

1.规划：
由于我的电脑资源有限，在单主机上搭建多实例。前面运行了由3个默认存储引擎为InnoDB的实例。
端口：3304
数据目录：/data/mysql/node4
socket文件：
PID文件：

2.参数文件配置：/etc/my.cnf
[mysqld3304]
plugin_load=server_audit=server_audit.so 
user                            = mysql
datadir                         = /data/mysql/node4
port                            = 3304
pid-file                        = /tmp/mysql_node4.pid
socket                          = /tmp/mysql_node4.sock
server-id                       = 2
rocksdb
skip-innodb
plugin-load-add                 =ha_rocksdb.so
default-storage-engine          =rocksdb
default-tmp-storage-engine      =MyISAM
character-set-client-handshake  = FALSE
character-set-server            = utf8mb4
collation-server                = utf8mb4_bin
init_connect                   ='SET NAMES utf8mb4'
binlog_format                  =ROW
transaction-isolation          =READ-COMMITTED
#rocksdb
rocksdb_max_open_files=-1
rocksdb_max_background_jobs=8
rocksdb_max_total_wal_size=512M
rocksdb_block_size=16384
rocksdb_block_cache_size=1G
rocksdb_table_cache_numshardbits=6

# rate limiter
rocksdb_bytes_per_sync=4194304
rocksdb_wal_bytes_per_sync=4194304
rocksdb_rate_limiter_bytes_per_sec=104857600 #100MB/s

# triggering compaction if there are many sequential deletes
rocksdb_compaction_sequential_deletes_count_sd=1
rocksdb_compaction_sequential_deletes=199999
rocksdb_compaction_sequential_deletes_window=200000

# read free replication
#rocksdb_rpl_lookup_rows=0

#rocksdb_default_cf_options=write_buffer_size=128m;target_file_size_base=32m;max_bytes_for_level_base=512m;level0_file_num_compaction_trigger=4;level0_slowdown_writes_trigger=10;level0_stop_writes_trigger=15;max_write_buffer_number=4;compression_per_level=kLZ4Compression;bottommost_compression=kZSTD;compression_opts=-14:1:0;block_based_table_factory={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=0};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true;memtable_prefix_bloom_size_ratio=0.05;prefix_extractor=capped:12;compaction_pri=kMinOverlappingRatio

rocksdb_override_cf_options=cf_link_pk={prefix_extractor=capped:20};rev:cf_link_id1_type={prefix_extractor=capped:20}

interactive_timeout            = 172800
wait_timeout                   = 172800
tmp-table-size                 = 8M
max-heap-table-size            = 4M
query-cache-type               = 0
query-cache-size               = 64M
max-connections                = 80
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 512M
log-error                      = error.log
slow-query-log                 = 1
slow-query-log-file            = slow.log
long_query_time                = 0.2
min_examined_row_limit         = 100
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       =ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sysdate-is-now                 = 1
innodb-strict-mode             = 1
sort_buffer_size              = 2M
join_buffer_size              = 2M
key_buffer_size               = 64M
read_buffer_size              = 512K
read_rnd_buffer_size          = 256K
binlog_cache_size             = 2M
bulk_insert_buffer_size       = 64M
lower_case_table_names        = 1
relay-log                     = mysql_relay.log
relay_log_recovery            =1
slave-net-timeout             =60
relay_log_purge               =0
sync_binlog                   = 1
#log-bin                       =mysql_bin.log
expire-logs-days              =1
relay_log_recovery             = 1
slave_skip_errors              = ddl_exist_errors
explicit_defaults_for_timestamp =true

由于MariaDB 中的MyRocks有些特性暂不支持就先把参数屏蔽掉，比如LZ4 和read free replication暂不支持。

其中重要的参数有内存设置rocksdb_block_cache_size ，次参数等同于InnoDB中的innodb_buffer_pool_size。
另外需要指定默认的存储引擎参数：default-storage-engine
3.创建一个数据目录：
#mkdir -p /data/mysql/node4
4.数据库实例初始化：
/usr/local/mysql/scripts/mysql_install_db  --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/node4

5.启动实例：
/etc/init.d/mysqld_multi start 3304

6.登录验证和创建远程登录的用户：
# mysql -p -S /tmp/mysql_node4.sock 
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.3.8-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like 'default%engine%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| default_storage_engine     | ROCKSDB |
| default_tmp_storage_engine | MyISAM  |
+----------------------------+---------+
2 rows in set (0.001 sec)

MariaDB [(none)]> show variables like 'rocksdb_block_cache_size';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| rocksdb_block_cache_size | 1073741824 |
+--------------------------+------------+
1 row in set (0.001 sec)
MariaDB [(none)]> grant all privilege on *.* to root@'%' identified by 'oracle' with grant option;
MariaDB [(none)]>flush privileges;

配置完成基本可以满足功能测试验证的需要了。