MariaDB 10.3.8 单主机搭建多实例数据库服务，mariadb10.3.8

MariaDB 10.3.8 单主机搭建多实例数据库服务，mariadb10.3.8

运行环境：CentOS 7.5+MariaDB 10.3.8
1.规划：
实例端口号   数据目录         socket文件                 PID文件
3301      /data/mysql/node1  /tmp/mysql_node1.sock    /tmp/mysql_node1.pid
3302      /data/mysql/node2  /tmp/mysql_node2.sock    /tmp/mysql_node2.pid
3303      /data/mysql/node3  /tmp/mysql_node3.sock    /tmp/mysql_node3.pid

2.加压mariadb的二进制包：
#tar -xzvf mariadb-10.3.8-linux-systemd-x86_64.tar.gz -C /usr/local/
#cd /usr/local/
#mv mariadb-10.3.8-linux-systemd-x86_64/ mysql

3.准备my.cnf配置文件：
[mysqld_multi]
mysqld                          = /usr/local/mysql/bin/mysqld
mysqladmin                      = /usr/local/mysql/bin/mysqladmin
log                             = /home/mysql/mysqld_multi.log
user                            = root
pass                            = oracle

[mysqld3301]
plugin_load=server_audit=server_audit.so 
user                            = mysql
datadir                         = /data/mysql/node1
port                            = 3301
pid-file                        = /tmp/mysql_node1.pid
socket                          = /tmp/mysql_node1.sock
server-id                       = 2
default-storage-engine         =InnoDB
character-set-client-handshake = FALSE
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
init_connect                   ='SET NAMES utf8mb4'
performance-schema-instrument='memory/%=COUNTED'
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        = 1G
log-error                      = mysql_error.log
slow-query-log                 = 1
slow-query-log-file            = mysql_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
thread_stack                  = 256K
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
binlog_format                 =ROW
expire-logs-days              =1
relay_log_recovery             = 1
slave_skip_errors              = ddl_exist_errors
explicit_defaults_for_timestamp =true
.....
后面的配置只需要把端口和文件修改即可其他的参数都可以设置为一样或者根据自己的情况自定义：
[mysqld3302]
user                            = mysql
datadir                         = /data/mysql/node2
port                            = 3302
pid-file                        = /tmp/mysql_node2.pid
socket                          = /tmp/mysql_node2.sock
[mysqld3303]
user                            = mysql
datadir                         = /data/mysql/node3
port                            = 3303
pid-file                        = /tmp/mysql_node3.pid
socket                          = /tmp/mysql_node3.sock

4.数据初始化：
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/node1

# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/node2

# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/node3

5.拷贝文件和启动数据库实例：
# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

[root@node1 node2]# /etc/init.d/mysqld_multi report
Reporting MariaDB servers
MariaDB server from group: mysqld3301 is running
MariaDB server from group: mysqld3302 is not running
MariaDB server from group: mysqld3303 is not running
[root@node1 node2]# /etc/init.d/mysqld_multi stop
[root@node1 node2]# /etc/init.d/mysqld_multi start
[root@node1 node2]# /etc/init.d/mysqld_multi report
Reporting MariaDB servers
MariaDB server from group: mysqld3301 is running
MariaDB server from group: mysqld3302 is running
MariaDB server from group: mysqld3303 is running

可以启动和关闭每个数据库实例。
可以查看MySQL的端口：
# netstat -nultp |grep -i mysql
tcp6       0      0 :::3301                 :::*                    LISTEN      5411/mysqld         
tcp6       0      0 :::3302                 :::*                    LISTEN      5717/mysqld         
tcp6       0      0 :::3303                 :::*                    LISTEN      5720/mysqld  

6.设置mysql服务的环境变量：
[root@node1 node2]# cat /etc/profile.d/mysql.sh 
export PATH=$PATH:/usr/local/mysql/bin
[root@node1 node2]# source /etc/profile.d/mysql.sh 

7.设置可以远程登录的账号：
[root@node1 node2]# mysql -p -S /tmp/mysql_node1.sock 
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
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)]> grant all privileges on *.* to root@'%' identified by 'oracle' with grant option;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
每个实例都需要设置下。
这样单主机三个MySQL实例就搭建完毕了。