mysql实现多实例 -云博客

mysql实现多实例

# 数据库 2024-05-05 14:23 0 26 来源：云博客

> mariadb安装
yum install mariadb-server

   > 创建相关目录,及设置权限
   mkdir /mysqldb; mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pv
   chown -R mysql:mysql /mysqldb

#目录结构如下
   /mysqldb
   ├── 3306
   │   ├── data
   │   ├── etc
   │   ├── log
   │   ├── pid
   │   └── socket
   ├── 3307
   │   ├── data
   │   ├── etc
   │   ├── log
   │   ├── pid
   │   └── socket
   └── 3308
       ├── data
       ├── etc
       ├── log
       ├── pid
       └── socket

   > 初始化数据库
   systemctl start mariadb; show variables like ‘basedir%‘; #查看mariadb安装路径
   mysql_install_db --datadir=/mysqldb/3306/data --user=mysql --basedir=/usr/
   mysql_install_db --datadir=/mysqldb/3307/data --user=mysql --basedir=/usr/
   mysql_install_db --datadir=/mysqldb/3308/data --user=mysql --basedir=/usr/

   > 拷贝并修改配置文件
   cp /etc/my.cnf /mysqldb/3306/etc/
   cp /etc/my.cnf /mysqldb/3307/etc/
   cp /etc/my.cnf /mysqldb/3308/etc/

   vi /etc/my.cnf /mysqldb/3306/etc/my.cnf
   [mysqld]
   port=3306
   datadir=/mysqldb/3306/data
   socket=/mysqldb/3306/socket/mysql.sock
   # Disabling symbolic-links is recommended to prevent assorted security risks
   symbolic-links=0
   # Settings user and group are ignored when systemd is used.
   # If you need to run mysqld under a different user or group,
   # customize your systemd unit file for mariadb according to the
   # instructions in http://fedoraproject.org/wiki/Systemd

   [mysqld_safe]
   log-error=/mysqldb/3306/log/mariadb.log
   pid-file=/mysqldb/3306/pid/mariadb.pid

   #
   # include all files from the config directory
   #
   #!includedir /etc/my.cnf.d

   #其它2个实例的配置文件参考第1个实例的配置文件
   cp -a ./3306/etc/my.cnf ./3307/etc/my.cnf; cp -a ./3306/etc/my.cnf ./3308/etc/my.cnf

   > 准备启动脚本,设置权限
   cp mysqld /mysqldb/3306/; cp mysqld /mysqldb/3307/; cp mysqld /mysqldb/3308/
   chmod 700 /mysqldb/3306/mysqld; chmod 700 /mysqldb/3307/mysqld; chmod 700 /mysqldb/3308/mysqld

 #!/bin/bash # port=3306 #注意端口 mysql_user="root" mysql_pwd="centos" #口令 cmd_path="/usr/bin"#2进制程序目录 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 "Stoping MySQL...\n" ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown fi } function_restart_mysql() { printf "Restarting MySQL...\n" function_stop_mysql sleep 2 function_start_mysql } case $1 in start) function_start_mysql ;; stop) function_stop_mysql ;; restart) function_restart_mysql ;; *) printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n" esac

   > 启动单个实例,注意如mariadb服务已经启动,请先停止该服务
   systemctl stop mariadb
   /mysqldb/3306/mysqld start; /mysqldb/3307/mysqld start; /mysqldb/3308/mysqld start

   > 查看端口信息
   ss -nutl

   > 连接测试,设置密码
   mysql -S /mysqldb/3308/socket/mysql.sock
   show variables like ‘%port%‘;

 MariaDB [(none)]> show variables like ‘%port%‘; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | extra_port | 0 | | innodb_import_table_from_xtrabackup | 0 | | innodb_support_xa | ON | | large_files_support | ON | | port | 3306 | | progress_report_time | 5 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | +-------------------------------------+-------+ 10 rows in set (0.00 sec) MariaDB [(none)]> select user,host,password from mysql.user; +------+------------+----------+ | user | host | password | +------+------------+----------+ | root | localhost | | | root | snowyts-ts | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | snowyts-ts | | +------+------------+----------+ 6 rows in set (0.00 sec) MariaDB [(none)]> update mysql.user set password=password(‘centos‘) where user=‘root‘; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 MariaDB [(none)]> select user,host,password from mysql.user; +------+------------+-------------------------------------------+ | user | host | password | +------+------------+-------------------------------------------+ | root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED | | root | snowyts-ts | *128977E278358FF80A246B5046F51043A2B1FCED | | root | 127.0.0.1 | *128977E278358FF80A246B5046F51043A2B1FCED | | root | ::1 | *128977E278358FF80A246B5046F51043A2B1FCED | | | localhost | | | | snowyts-ts | | +------+------------+-------------------------------------------+ 6 rows in set (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec)