多实例启动mariadb10.6.8（源码安装）

多实例启动mariadb10.6.8（源码安装）

1、源码安装mariadb10.6.8的情况介绍

1、安装目录：/usr/local/mysql/
2、数据目录 ：/mysqldb/
/mysqldb/3307/data/
/mysqldb/3308/data/
/mysqldb/3309/data/
3、多实例的端口：3007、3008、3009

2、多实例配置

2.1 创建各种配置文件

# 创建多组配置文件
[root@centos7 ~]# mkdir -p /mysqldb/{3307,3308,3309}/{etc,socket,pid,log,data,bin}
[root@centos7 ~]# chown -R mysql:mysql /mysqldb/

# 使用指定的数据目录初始化数据库
[root@centos7 ~]# cd /usr/local/mysql/
[root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3307/data/ --user=mysql --basedir=/usr/local/mysql/
[root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3308/data/ --user=mysql --basedir=/usr/local/mysql/
[root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3309/data/ --user=mysql --basedir=/usr/local/mysql/

# 使用示例配置文件，并修改配置文件
[root@centos7 mysql]# cp support-files/wsrep.cnf /mysqldb/3307/etc/my.cnf
[root@centos7 mysql]# cp support-files/wsrep.cnf /mysqldb/3308/etc/my.cnf
[root@centos7 mysql]# cp support-files/wsrep.cnf /mysqldb/3309/etc/my.cnf
[root@centos7 mysqldb]# cd /mysqldb/
[root@centos7 mysqldb]# vim 3307/etc/my.cnf
[mysqld]
port        = 3307
datadir     = /mysqldb/3307/data
socket      = /mysqldb/3307/socket/mysql.sock
[root@centos7 mysqldb]# vim 3308/etc/my.cnf  # 按以上配置示例更改
[root@centos7 mysqldb]# vim 3309/etc/my.cnf

2.2 创建启动脚本

[root@centos7 ~]# cat mysqld  # 脚本示例
#!/bin/bash
 
port=3307  # 需要修改为当前实例的端口号
mysql_user="root"
mysql_pwd=""     # 初始化root无密码
cmd_path="/usr/local/mysql/bin"  # 安装目录下的bin
mysql_basedir="/mysqldb"  # 实例数据库文件所在目录
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"    # socket文件，用于登录数据库
 
function_start_mysql()    # 启动mysql
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
    else
      printf "MySQL is running...\n"
      exit
    fi
}
 
 
function_stop_mysql()    # 停止mysql
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}
 
 
function_restart_mysql()    # 重启mysql
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}
 
case $1 in      # 参数1，映射不同的值，调用不同的函数
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"    # 异常提示
esac

2.3 配置启动脚本并启动服务

# 将启动脚本复制到不同的目录，并修改监听的端口号
[root@centos7 ~]# cp mysqld /mysqldb/3307/bin/
[root@centos7 ~]# cp mysqld /mysqldb/3308/bin/
[root@centos7 ~]# cp mysqld /mysqldb/3309/bin/
[root@centos7 ~]# vim /mysqldb/3307/bin/mysqld
port=3307
[root@centos7 ~]# vim /mysqldb/3308/bin/mysqld
port=3308
[root@centos7 ~]# vim /mysqldb/3309/bin/mysqld
port=3309

# 修改脚本文件权限，防止密码被别人看到
[root@centos7 ~]# chmod 700 /mysqldb/3307/bin/mysqld 
[root@centos7 ~]# chmod 700 /mysqldb/3308/bin/mysqld  
[root@centos7 ~]# chmod 700 /mysqldb/3309/bin/mysqld 

# 启动服务
[root@centos7 ~]# service mysqld stop  # 保证自己原来的服务停止，释放3306端口，这步可以不做。
[root@centos7 ~]# /mysqldb/3307/bin/mysqld start  # 启动服务
[root@centos7 ~]# /mysqldb/3308/bin/mysqld start
[root@centos7 ~]# /mysqldb/3309/bin/mysqld start
[root@centos7 ~]# ss -tnl  # 如果看到三个实例监听的端口都打开后说明服务启动正常
LISTEN 0 80 :::3307 :::*
LISTEN 0 80 :::3308 :::*
LISTEN 0 80 :::3309 :::*

2.4 连接测试

[root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock  # 使用-S指定套接字文件
Server version: 10.2.15-MariaDB-log Source distribution
MariaDB [(none)]> show variables like '%port';  # 查看端口是否是3306
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| extra_port               | 0     |
| innodb_deadlock_report   | full  |
| large_files_support      | ON    |
| port                     | 3307  |
| report_port              | 3307  |
| require_secure_transport | OFF   |
+--------------------------+-------+
6 rows in set (0.001 sec)

[root@centos7 ~]# mysql -S /mysqldb/3308/socket/mysql.sock  #再连接测试一下3307和3308
Server version: 10.2.15-MariaDB-log Source distribution
MariaDB [(none)]> show variables like '%port';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| extra_port               | 0     |
| innodb_deadlock_report   | full  |
| large_files_support      | ON    |
| port                     | 3308  |
| report_port              | 3308  |
| require_secure_transport | OFF   |
+--------------------------+-------+
6 rows in set (0.001 sec)

[root@centos7 ~]# mysql -S /mysqldb/3309/socket/mysql.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.8-MariaDB Source distribution

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 '%port';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| extra_port               | 0     |
| innodb_deadlock_report   | full  |
| large_files_support      | ON    |
| port                     | 3309  |
| report_port              | 3309  |
| require_secure_transport | OFF   |
+--------------------------+-------+
6 rows in set (0.001 sec)

# 停止实例
[root@centos7 ~]# /mysqldb/3307/bin/mysqld stop

2.5 设置root密码

[root@centos7 ~]# mysql -S /mysqldb/3308/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.8-MariaDB Source distribution

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 '123456';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant all privileges on *.* to 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> select user,host,password from mysql.user;
+-------------+-----------+-------------------------------------------+
| User        | Host      | Password                                  |
+-------------+-----------+-------------------------------------------+
| mariadb.sys | localhost |                                           |
| root        | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql       | localhost | invalid                                   |
|             | localhost |                                           |
|             | server    |                                           |
| root        | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------+-----------+-------------------------------------------+
6 rows in set (0.001 sec)

[root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock -uroot -p'123456' # 指定密码登录