Multi-instance: the second MySQL series

Multi-instance configuration for MySQL

? in a physical machine requires multiple test environments, then you need to build a database of multiple instances, multiple instances of the meaning is to run multiple programs, the instance and the instance has no effect. Be aware that the ports you are listening to need to be different.

Environment: CentOS7.5, compile and install MariaDB-10.2.15 version, software installation directory:/app/mysql/

? 1) Create a running directory environment

[[email protected] ~]# mkdir -p /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data,bin}[[email protected] ~]# chown -R mysql:mysql /mysqldb/

? 2) Initializing the database

[[email protected] ~]# cd /app/mysql/[[email protected] mysql]# scripts/mysql_install_db --datadir=/mysqldb/3306/data/ --user=mysql --basedir=/app/mysql/ [[email protected] mysql]# scripts/mysql_install_db --datadir=/mysqldb/3307/data/ --user=mysql --basedir=/app/mysql/[[email protected] mysql]# scripts/mysql_install_db --datadir=/mysqldb/3308/data/ --user=mysql --basedir=/app/mysql/

The above is compiled and installed, the installation directory is/app/mysql/, you need to enter the software installation directory and then execute the initialization script, if it is a yum installed package, then run the mysql_install_db command directly

? 3) Provide configuration files and modify as needed

[[email protected] mysql]# cp support-files/my-huge.cnf /mysqldb/3306/etc/my.cnf[[email protected] mysql]# cp support-files/my-huge.cnf /mysqldb/3307/etc/my.cnf[[email protected] mysql]# cp support-files/my-huge.cnf /mysqldb/3308/etc/my.cnf

[[email protected] mysqldb]# cd /mysqldb/[[email protected] mysqldb]# vim 3306/etc/my.cnf[mysqld]port        = 3306datadir     = /mysqldb/3306/datasocket      = /mysqldb/3306/socket/mysql.sock[[email protected] mysqldb]# vim 3307/etc/my.cnf  #按以上配置示例更改[[email protected] mysqldb]# vim 3308/etc/my.cnf

? 4) Provide service startup script

[[email protected]~]# Cat Mysqld#脚本示例#!/bin/bashport=3306#需要修改为当前实例的端口号mysql_user="Root"mysql_pwd=""cmd_path="/app/mysql/bin"  #安装目录下的binmysql_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}Shutdownfi}Function_restart_mysql (){    printf "Restarting mysql...\n"    Function_stop_mysql    Sleep2Function_start_mysql} Case  $inchStart)    Function_start_mysql;;Stop)    Function_stop_mysql;;Restart)    Function_restart_mysql;;*)    printf "Usage:${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n "Esac

[[email protected] ~]# cp mysqld /mysqldb/3306/bin/[[email protected] ~]# cp mysqld /mysqldb/3307/bin/[[email protected] ~]# cp mysqld /mysqldb/3308/bin/[[email protected] ~]# vim /mysqldb/3306/bin/mysqldport=3306[[email protected] ~]# vim /mysqldb/3307/bin/mysqldport=3307[[email protected] ~]# vim /mysqldb/3308/bin/mysqldport=3308

? 5) Modify the script file permissions to prevent the password from being seen by others

[[email protected] ~]# chmod 700 /mysqldb/3306/bin/mysqld [[email protected] ~]# chmod 700 /mysqldb/3307/bin/mysqld  

? 6) Start the service

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

? 7) connection test

[[email protected] ~]# mysql-s/mysqldb/3306/socket/mysql.sock #使用-S Specify socket file server Version:10.2.15-mariadb-log  Source distributionmariadb [(none)]> show variables like '%port '; #查看端口是否是3306 +---------------------+-------+| variable_name | Value |+---------------------+-------+| Extra_port | 0 | | Large_files_support | On | | Port | 3306 | | Report_port | 3306 |+---------------------+-------+4 rows in Set (0.00 sec) [[email protected] ~]# mysql-s/mysqldb/3307/socket/m Ysql.sock #再连接测试一下3307和3308Server version:10.2.15-mariadb-log Source distributionmariadb [(none)]> Show variables Like '%port '; +---------------------+-------+| variable_name | Value |+---------------------+-------+| Extra_port | 0 | | Large_files_support | On | | Port | 3307 | | Report_port | 3307 |+---------------------+-------+4 rows in Set (0.00 sec) [[email protected] ~]# mysql-s/mysqldb/3308/socket/m Ysql.sOckserver version:10.2.15-mariadb-log Source distributionmariadb [(none)]> show variables like '%port '; +----------- ----------+-------+| variable_name | Value |+---------------------+-------+| Extra_port | 0 | | Large_files_support | On | | Port | 3308 | | Report_port | 3308 |+---------------------+-------+4 rows in Set (0.00 sec)

Multi-Instance Build success!

? 8) Use this command to stop the instance

[[email protected] ~]# /mysqldb/3306/bin/mysqld stop

? 9) Last step: Add a password to the root user

[[email protected] ~]# mysql-s/mysqldb/3307/socket/mysql.sock Server version:10.2.15-mariadb-log Source DISTRIBUTIONMARIADB [(none)]> update mysql.user set Password=password ("Your_password") where user= ' root '; Query OK, 4 rows Affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows Affected (0.00 sec) MariaDB [(None)]> Select User,host,password from mysql.user;+------+-----------+-- -----------------------------------------+| user | Host | Password |+------+-----------+-------------------------------------------+| Root | localhost | *9e72259ba9214f692a85b240647c4d95b0f2e08b | | Root | Centos7 | *9e72259ba9214f692a85b240647c4d95b0f2e08b | | Root | 127.0.0.1 | *9e72259ba9214f692a85b240647c4d95b0f2e08b | | Root | :: 1 |      *9e72259ba9214f692a85b240647c4d95b0f2e08b | | |                                           localhost |      || |                                           Centos7 | |+------+-----------+-------------------------------------------+6 rows in Set (0.00 sec) [[email protected] ~]# mysql-s/mysqldb/3307/socket/ Mysql.sock-uroot-p ' Your_password ' #指定密码, log in again ok~

Finally, add your password to the bin/mysqld script file to prevent the service from starting

@ ^_^ @ 2018.06.04 23:27

Multi-instance: the second MySQL series