mariadb多实例

1.yum安装mariadb

yum install -y mariadb-server

也可以选择源码安装或二进制安装

2.创建三个实例的文件夹

mkdir /mysql/{3306,3307,3308}/{etc,pid,socket,log,data} -pv

3.修改文件夹所有者、所有组

chown -R mysql.mysql /mysql/

4.生成数据库文件

mysql_install_db --datadir=/mysql/3306/data --user=mysql

报错如下：

 报错未查明原因，尝试修改/etc/hosts文件无果。故采用帮助里的--force选项。

mysql_install_db --datadir=/mysqldb/3306/data --user=mysql --force

昨天写到一半忘记保存了，相关的图片记录都没了o(╥﹏╥)o

同样的，给3307和3308生成数据库文件。

生成后是这样的（主机重装了，懒得把主机名改回去了）：

5.复制并修改配置文件

cp /etc/my.cnf /mysqldb/3306/etc/
cd /mysqldb/3306/etc/
vim my.cnf
cat my.cnf 
cp my.cnf /mysqldb/3307/etc
cp my.cnf /mysqldb/3308/etc
vim my.cnf /mysqldb/3307/etc
vim my.cnf /mysqldb/3308/etc

修改后的配置文件，标红部分为修改的部分：

 6.复制启动程序

 在网上找到大家写好的启动程序，脚本如下，复制到各个目录中，命名为mysqld，并修改相应的端口和basedir

#!/bin/bash

port=3307
mysql_user="root"
mysql_pwd=""           
cmd_path="/usr/bin"
mysql_basedir="/mysqldb"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_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()
{
    if [ ! -e "$mysql_sock" ];then
        printf "MySQL is stopped...\n"
        exit
    else
        printf "Stopping MySQL..\n"
    #    ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
#此行注释的是关闭脚本时需要使用密码
        ${cmd_path}/mysqladmin -u ${mysql_user}  -S ${mysql_sock} shutdown
    fi
}

function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    funtions_stop_mysql
    sleep 2
    funtion_start_mysql
}

case $1 in 
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restat)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

7.修改权限

[root@iZ2ze1j28cfmj2nh7m199nZ ~]# chmod 700 /mysqldb/3306/mysqld 
[root@iZ2ze1j28cfmj2nh7m199nZ ~]# chmod 700 /mysqldb/3307/mysqld 
[root@iZ2ze1j28cfmj2nh7m199nZ ~]# chmod 700 /mysqldb/3308/mysqld

8.启动

[root@iZ2ze1j28cfmj2nh7m199nZ ~]# /mysqldb/3306/mysqld start
Starting MySQL...
[root@iZ2ze1j28cfmj2nh7m199nZ ~]# /mysqldb/3307/mysqld start
Starting MySQL...
[root@iZ2ze1j28cfmj2nh7m199nZ ~]# /mysqldb/3308/mysqld start
Starting MySQL...

[root@iZ2ze1j28cfmj2nh7m199nZ mysqldb]# ss -tnl
State      Recv-Q Send-Q                    Local Address:Port                                   Peer Address:Port              
LISTEN     0      50                                    *:3306                                              *:*                  
LISTEN     0      50                                    *:3307                                              *:*                  
LISTEN     0      50                                    *:3308                                              *:*                  
LISTEN     0      128                                   *:22                                                *:* 

9.连接

使用socket文件进行本地连接

[root@iZ2ze1j28cfmj2nh7m199nZ ~]# mysql -S /mysqldb/3306/socket/mysql.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.64-MariaDB 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 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]>

10.使用密码登录

添加密码

[root@iZ2ze1j28cfmj2nh7m199nZ ~]# mysql -S /mysqldb/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.64-MariaDB 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)]> update mysql.user set password=password('123') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye
[root@iZ2ze1j28cfmj2nh7m199nZ ~]# mysql -S /mysqldb/3306/socket/mysql.sock
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@iZ2ze1j28cfmj2nh7m199nZ ~]# mysql -S /mysqldb/3306/socket/mysql.sock -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.64-MariaDB 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)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> select user,password,host from user;
+------+-------------------------------------------+-------------------------+
| user | password                                  | host                    |
+------+-------------------------------------------+-------------------------+
| root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost               |
| root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | iz2ze1j28cfmj2nh7m199nz |
| root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | 127.0.0.1               |
| root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | ::1                     |
|      |                                           | localhost               |
|      |                                           | iz2ze1j28cfmj2nh7m199nz |
+------+-------------------------------------------+-------------------------+
6 rows in set (0.00 sec)

MariaDB [mysql]> 

 

很简单的小实验，但一路做下来也并不顺畅。磕磕绊绊中又加深了理解，动手做一做，才有收获。