CentOS7之mysql多实例

环境准备，一台CentOS7

安装软件

[root@localhost ~]# yum install mariadb-server -y

创建三个实例的目录

[root@localhost ~]# mkdir /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid} -p

改所属主，组

[root@localhost ~]# chown -R mysql.mysql /mysql

生成数据库文件

[root@localhost ~]# mysql_install_db --user=mysql --datadir=/mysql/3306/data
[root@localhost ~]# mysql_install_db --user=mysql --datadir=/mysql/3307/data
[root@localhost ~]# mysql_install_db --user=mysql --datadir=/mysql/3308/data

准备配置文件

# 3306
[root@localhost ~]# vim /mysql/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
log-error=/mysql/3306/log/mysql.log
pid-file=/mysql/3306/pid/mysql.pid

# 3307
[root@localhost ~]# sed 's/3306/3307/' /mysql/3306/etc/my.cnf > /mysql/3307/etc/my.cnf

# 3308
[root@localhost ~]# sed 's/3306/3308/' /mysql/3306/etc/my.cnf > /mysql/3308/etc/my.cnf

编写启动关闭脚本

[root@localhost ~]# vim /mysql/3306/bin/mysqld
#!/bin/bash
# date: 2021-05-12
# autho: xuanlv

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

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
}
stop_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
}
restart_mysql(){
    printf "Restarting MySQL...\n"
    stop_mysql
    sleep 2
    start_mysql
}

case $1 in
start)
    start_mysql
    ;;
stop)
    stop_mysql
    ;;
restart)
    restart_mysql
    ;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

授权

[root@localhost ~]# chmod +x /mysql/3306/bin/mysqld

拷贝成剩下两个实例启动脚本,记住3307,3308脚本端口一定要改

[root@localhost ~]# cp /mysql/3306/bin/mysqld /mysql/3307/bin/mysqld
[root@localhost ~]# cp /mysql/3306/bin/mysqld /mysql/3308/bin/mysqld

启动脚本

[root@localhost ~]# /mysql/3306/bin/mysqld start
[root@localhost ~]# /mysql/3307/bin/mysqld start
[root@localhost ~]# /mysql/3308/bin/mysqld start

查看端口3306,3307,3308

[root@localhost ~]# ss -tan
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                                        *:*    

测试登陆

[root@localhost ~]# mysql -h127.0.0.1 -P3307
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.68-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)]> 

查看当前端口

MariaDB [(none)]> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]>

修改root密码

[root@localhost ~]# mysqladmin -uroot -S /mysql/3306/socket/mysql.sock password '123456'
[root@localhost ~]# mysqladmin -uroot -S /mysql/3307/socket/mysql.sock password '123456'
[root@localhost ~]# mysqladmin -uroot -S /mysql/3308/socket/mysql.sock password '123456'

测试修改的密码

[root@localhost ~]# mysql -uroot -h127.0.0.1 -P3306
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -uroot -h127.0.0.1 -p123456 -P3306
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.68-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)]>

关闭mysql

[root@localhost ~]# /mysql/3306/bin/mysqld stop
[root@localhost ~]# /mysql/3307/bin/mysqld stop
[root@localhost ~]# /mysql/3308/bin/mysqld stop

目前使用的查看字符集

MariaDB [(none)]> show variables like 'charact%';

改字符集

[root@localhost ~]# vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
default-character-set=utf8mb4