MYSQL多实例配置详解.md

mysql_mutil --config-file=/data/mysql/my_multi.cnf start 1,2,3,4,5 #启动的实例号s

//安装依赖
yum install libaio-devel -y

//创建数据存储目录
[root@WeiyiGeek ~]$ mkdir -p /data/{3306,3307}/data  //递归创建多个文件
[root@WeiyiGeek data]# ls /application/mysql/support-files/my-*  //数据库默认提供多个配置模板,根据硬件修改
/application/mysql/support-files/my-huge.cnf
/application/mysql/support-files/my-innodb-heavy-4G.cnf
/application/mysql/support-files/my-large.cnf
/application/mysql/support-files/my-medium.cnf
/application/mysql/support-files/my-small.cnf  //复制到 3306 3307 中

//我们为每一个实例建立一个配置文件,一个启动文件,对应数据文件;
cp /application/mysql/support-files/my-small.cnf /data/{3306,3307}/data/my.cnf
tree /data

//在vim编辑器下：%s/3306/3307/g，并修改my.cnf下server-id与其他实例不同id（server-id = 1或2总之不一样就行）
[client]
port            = 3306                                   
socket          = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user    = mysql
port    = 3306
socket  = /data/3306/mysql.sock
basedir = /application/mysql      #需要根据实际情况更改： 比如 yum 默认安装的mysql 8.0 /var/bin/mysql
datadir = /data/3306/data
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
#log_long_format
#log-error = /data/3306/error.log
#log-slow-queries = /data/3306/slow.log
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql

#下面这里是不一样的
server-id = 1
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0

[mysqldump]
quick
max_allowed_packet = 2M

[mysqld_safe]
log-error = /data/3306/mysql_3306.err
pid-file = /data/3306/mysqld.pid

#init
port=3306   #必须
mysql_user="root"  #需求更改
mysql_pwd="System123@"  #需求更改
CmdPath="/application/mysql/bin" 
mysql_sock="/data/${port}/mysql.sock" 
#startup function
function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
    else
      printf "MySQL is running...\n"
      exit
    fi
}

#stop function
function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
   fi
}

#restart function
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: /data/${port}/mmysqld.sh {start|stop|restart}\n"
esac

#授权
chown -R mysql.mysql /data
find /data/ -type f -name "mysql"|xargs chmod +x

#初始化脚本
cd /application/mysql/scripts/
./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql

//单实例初始化
./mysql_install_db --user=mysql

#多实例数据库停止与启动：(在mmysqld.sh文件中)
[启动]
/application/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &
/application/mysql/bin/mysqld_safe --defaults-file = /data/3307/my.cnf 2>&1 > /dev/null &

[停止]
mysqladmin -usystem -psystem -S /data/3306/mysql.sock shutdwon
mysqladmin -usystem -psystem -S /data/3307/mysql.sock shutdwon

#启动脚本
/data/3306/mmysqld.sh restart

#可以利用一个shell添加一个实例3308端口Mysql：
#!/usr/bin
mkdir /data/3308/data -p
cp /data/3307/my.cnf /data/3308/
cp /data/3307/mmysqld.sh /data/3308/
sed -i 's#3307#3308#g' /data/3308/my.cnf
sed -i 's#3307#3308#g' /data/3308/mmysqld.sh
chmod +x /data/3308/mysql
chown -R mysql.mysql /data/3308/
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308/data/ --user=mysql
/data/3308/mysql start

#1. 创建一个mysql用户和组并设置/sbin/nologin
#2. 实例化文件目录以及权限
mkdir -vp /data/{3306,3307}/{data,binlog}
chown -R mysql:mysql /usr/local/mysql/ /data

#3. 创建多实例配置文件
# /data/3306/my.cnf
#----------------------------------
[client]
port            = 3306                                   
socket          = /data/3306/mysql.sock
default-character-set=utf8   # 设置mysql客户端默认字符集 

[mysqld]
user    = mysql
port    = 3306
socket  = /data/3306/mysql.sock
basedir = /usr/local/mysql           #需要根据实际情况路径更改
datadir = /data/3306/data
log-error= /data/3306/mysql-error.log
log-bin = /data/3306/binlog
server-id = 3306
character-set-server=utf8  #服务端默认字符集 
explicit_defaults_for_timestamp  #时区


# /data/3307/my.cnf
#------------------------------------
[client]
port            = 3307                                   
socket          = /data/3307/mysql.sock
default-character-set=utf8   # 设置mysql客户端默认字符集 

[mysqld]
user    = mysql
port    = 3307
socket  = /data/3307/mysql.sock
basedir = /usr/local/mysql           #需要根据实际情况路径更改
datadir = /data/3307/data
log-error= /data/3307/mysql-error.log
log-bin = /data/3307/binlog
server-id = 3307
character-set-server=utf8  #服务端默认字符集 
explicit_defaults_for_timestamp  #时区



#4. 初始化数据库以及启动
mysqld --defaults-file=/data/3306/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql --explicit_defaults_for_timestamp
mysqld --defaults-file=/data/3307/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql --explicit_defaults_for_timestamp

mysqld_safe --defaults-file=/data/3306/my.cnf --user=mysql &
mysqld_safe --defaults-file=/data/3307/my.cnf --user=mysql &

#5. 查看登录密码 + 登录数据库
cat mysql-error.log | grep "passowrd"   #在设置的log文件下
root@localhost: <S!Ka6+8f6MJ
root@localhost: i?+WDyVav8L?

#登陆数据库
mysql -uroot -p'yxi9CG<9D5go' -S /data/3306/mysql.sock
mysql -uroot -p'i?+WDyVav8L?' -S /data/3307/mysql.sock
ALTER USER 'root'@'localhost' IDENTIFIED BY 'System123@';

## 1. 创建一个mysql用户和组并设置/sbin/nologin
groupadd -g 500 mysql
useradd -r -g mysql -s /sbin/nologin mysql

## 2. 创建多实例化目录
mkdir -vp /data/mysqldata/{{3306,3307}/{data,tmp,binlog,innodb_ts,innodb_log,logs},backup,scripts}
touch /data/mysqldata/3306/tmp/mysql-error.log /data/mysqldata/3307/tmp/mysql-error.log

## 3. 授权建立得目录（非常重要 常常会使mysql报错）
chown -R mysql:mysql /data/

## 4. 在实例化文件中写一个mysql.cnf配置
# my.cnf  我这里以3307为例
[client]
default-character-set=utf8   # 设置mysql客户端默认字符集 
port = 3307 
socket = /data/mysqldata/3307/mysql.sock 

# The MySQL server 
[mysqld] 
port = 3307 
mysqlx_port = 33070 
user = mysql
server-id = 3307
socket = /data/mysqldata/3307/mysql.sock 
mysqlx_socket=/data/mysqldata/3307/mysqlx.sock 
pid-file = /data/mysqldata/3307/mysql.pid 
basedir = /usr/local/mysql8 
datadir = /data/mysqldata/3307/data 
tmpdir = /data/mysqldata/3307/tmp #非必须
log-bin = /data/mysqldata/3307/binlog/mysql-bin
log-error = /data/mysqldata/3307/mysql-error.log
explicit_defaults_for_timestamp
character-set-server=utf8  #服务端默认字符集 

#------------------------- 配置文档分割线 ------------------------------------#

[root@centos7 ]$ cp /3307/my.cnf ../3306/my.cnf
[root@centos7 ]$ sed -i 's/3307/3306/g' ../3306/my.cnf

#初始化实例与启动数据库：
/usr/local/mysql8/bin/mysqld --defaults-file=/data/mysqldata/3307/3307.cnf --initialize --user=mysql --basedir=/usr/local/mysql8
/usr/local/mysql8/bin/mysqld --defaults-file=/data/mysqldata/3306/3306.cnf --initialize --user=mysql --basedir=/usr/local/mysql8

#实例化后在我们配置的mysql-error.log查看密码;
[root@centos7 3307]# grep "password" /data/mysqldata/3306/mysql-error.log
2019-01-18T02:58:28.529902Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: l>(J9/P8dt*f

#启动数据库
/usr/local/mysql8/bin/mysqld_safe --defaults-file=/data/mysqldata/3307/3307.cnf --user=mysql &
/usr/local/mysql8/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/3306.cnf --user=mysql &

#登录数据库以及密码更改：
mysql -u root -p';!u>iE9hf(ah' -S /data/mysqldata/3307/mysql.sock
mysql -u root -p'fllyBv!ud9gu' -S /data/mysqldata/3306/mysql.sock

mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY 'System123@';   #密码强度是有要求的，这也是MYSQL8的特性 
Query OK, 0 rows affected (0.14 sec)
mysql>ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'System123@!';  #认证方式

###1. 创建相关目录与实例化目录权限：###
[root@WeiyiGeek ~]# mkdir -vp /usr/local/mysql
mkdir: created directory `/usr/local/mysql`

* 创建 MySQL 软链接 （不起作用）
ln -s /usr/local/mysql-5.7.24-linux-glibc2.12-i686/ /usr/local/mysql

* 创建 用户建立 软链接
groupadd -g 500 mysql 
useradd -r -g mysql -u 500 -s /sbin/nologin mysql

* 在 MySQL 二进制包目录中创建 mysql-files 目录 [MySQL 数据导入/导出数据专放目录]
mkdir -v /usr/local/mysql/mysql-files
mkdir -vp /data/mysql_data{1..4}/{data,logs}
chown mysql.mysql -R /usr/local/mysql/mysql-files /data/mysql_data{1..4}


###2.编辑my.cnf文档:####
 mysqld_multi --example > my.cnf

#单一配置文件部署方案
* 配置 MySQL 配置文件 /etc/my.cnf
[mysqld_multi]
mysqld    = /usr/local/mysql/bin/mysqld 
mysqladmin = /usr/local/mysql/bin/mysqladmin
log        = /tmp/mysql_multi.log
 
[mysqld1]
# 设置数据目录　[多实例中一定要不同]
datadir = /data/mysql_data1
# 设置sock存放文件名　[多实例中一定要不同]
socket = /tmp/3306mysql.sock
# 设置监听开放端口　[多实例中一定要不同]
port = 3306
# 设置运行用户
user = mysql
# 关闭监控
performance_schema = off
# 设置innodb 缓存大小
innodb_buffer_pool_size = 32M
# 设置监听IP地址
bind_address = 0.0.0.0
# 关闭DNS 反向解析
skip-name-resolve = 0
server-id = 1
basedir = /usr/local/mysql
 
[mysqld2]
datadir = /data/mysql_data2
socket = /tmp/3307mysql.sock
port = 3307
user = mysql
server-id = 2
basedir = /usr/local/mysql


####3. 初始化数据库：#####
#mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql_3306/data
#或者采用 --defaults-file=/etc/my.cnf  --initialize-insecure #建立的密码为空   --user=mysql 指定用户
mysqld --defaults-file=/data/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1 --explicit_defaults_for_timestamp  
mysqld --initialize --basedir=/usr/local/mysql8/ --datadir=/app/mysql/mysqldata/3306/data/ --explicit_defaults_for_timestamp  

#各实例开启 SSL 连接
/usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/app/mysql/mysqldata/3306/data/   

#查看数据库是否初始化成功
mysqld_multi进行多实例管理

启动全部实例：/usr/local/mysql/bin/mysqld_multi start
查看全部实例状态：/usr/local/mysql/bin/mysqld_multi report 
启动单个实例：/usr/local/mysql/bin/mysqld_multi start 3306 
停止单个实例：/usr/local/mysql/bin/mysqld_multi stop 3306 
查看单个实例状态：/usr/local/mysql/bin/mysqld_multi report 3306 

#登录以及修改密码：
mysql的root用户初始密码是空，所以需要登录mysql进行修改密码，下面以3306为例： 
mysql -S /tmp/mysql_3306.sock   
set password for root@'localhost'=password('123456');   #<=5.7.x
flush privileges;

# 1. 与 MySQL 8.X 多实例化 （多配置文件）建立
mkdir -vp /app/mysql/mysqldata/{{3307,3308}/{data,tmp,binlog,innodb_ts,innodb_log,undo},backup,scripts}
touch /app/mysql/mysqldata/3307/mysql-error.log /app/mysql/mysqldata/3308/mysql-error.log
chown -R mysql:mysql /app/

#2. 使用mysqld_multi，需要3个配置文件，其中两个是用于初始化，另一个是用于mysqld_multi使用：
[mysqld_multi] 
mysqld = /usr/local/mysql8/bin/mysqld_safe
mysqladmin = /usr/local/mysql8/bin/mysqladmin 
log = /app/mysql/mysqldata/mysqld_multi.log 
user = root

[mysqld3307]
port=3307 
user=mysql
basedir=/usr/local/mysql8
socket=/tmp/mysql3307.sock 
pid-file=/app/mysql/mysqldata/3307/mysql3307.pid 
datadir=/app/mysql/mysqldata/3307/data 
log-error=/app/mysql/mysqldata/3307/mysql-error.log
explicit_defaults_for_timestamp

#3. 初始化：
mysqld --initialize --defaults-file=/app/my.cnf --user=mysql --datadir=/app/mysql/mysqldata/3307/data --basedir=/usr/local/mysql8

$ mysqld --initialize --user=root --datadir=/app/mysql/mysqldata/3307/data --basedir=/usr/local/mysql8 --defaults-file=/etc/mysql.cnf
    root@localhost: I0pFT4aNqh#r
$ mysqld --initialize --user=root --datadir=/app/mysql/mysqldata/3308/data --basedir=/usr/local/mysql8
    root@localhost: F,3Dv%mRLY*f


#4. 开启及其登录
/usr/local/mysql8/bin/mysqld_safe --port=3308 --user=root --basedir=/usr/local/mysql8 --socket=/app/mysql/mysqldata/3308/mysql3308.sock --pid-file=/app/mysql/mysqldata/3308/mysql3308.pid --log-error=/app/mysql/mysqldata/3308/mysql-error.log --explicit_defaults_for_timestamp &
/usr/local/mysql8/bin/mysqld --basedir=/usr/local/mysql8 --datadir=/app/mysql/mysqldata/3308/data --plugin-dir=/usr/local/mysql8/lib/plugin --user=root --explicit-defaults-for-timestamp --log-error=/app/mysql/mysqldata/3308/mysql-error.log --pid-file=/app/mysql/mysqldata/3308/mysql3308.pid --socket=/app/mysql/mysqldata/3308/mysql3308.sock --port=3308 &

/usr/local/mysql8/bin/mysqld_safe --port=3307 --user=root --basedir=/usr/local/mysql8 --socket=/app/mysql/mysqldata/3307/mysql3307.sock --pid-file=/app/mysql/mysqldata/3307/mysql3307.pid --log-error=/app/mysql/mysqldata/3307/mysql-error.log --explicit_defaults_for_timestamp &
/usr/local/mysql8/bin/mysqld --basedir=/usr/local/mysql8 --datadir=/app/mysql/mysqldata/3307/data --plugin-dir=/usr/local/mysql8/lib/plugin --user=root --explicit-defaults-for-timestamp --log-error=/app/mysql/mysqldata/3307/mysql-error.log --pid-file=/app/mysql/mysqldata/3307/mysql3307.pid --socket=/app/mysql/mysqldata/3307/mysql3307.sock --port=3307 &

mysql -uroot -p'F,3Dv%mRLY*f' -S /app/mysql/mysqldata/3308/mysql3308.sock
mysql -uroot -p'-KdgrGN1pkR:' -S /app/mysql/mysqldata/3307/mysql3307.sock

#本地登陆：
mysql -S /data/3306/mysql.sock    #需要指定socket

#远程登录(非本机LINUX)：端口号是实例中的端口（逻辑连接位置）
mysql –h主机名 -u用户名 –p密码 –P3306
mysql> system mysql -S /data/3307/mysql.sock    #（直接登录到3306的数据库）

#为用户增加密码，修改密码
mysqladmin -S /data/3306/mysql.sock -uroot password '123.asd'
mysqladmin -S /data/3307/mysql.sock –uroot –p123.asd password '123456'

#即这样得形式mysql -uroot -p  #使之不在从命令行中输入账号密码
username = $1
password = $2
printf "Usage: /data/${port}/mysql username password {start|stop|restart}\n"

[root@WeiyiGeek ~]$ find /data/ -type f -name "mysql" -exec chown root:root {} \;
[root@WeiyiGeek ~]$ find /data/ -type f -name "mysql" -exec chmod 700 {} \;
[root@WeiyiGeek ~]$ find /data/ -type f -name "mysql" -exec ls -lsh {} \;
4.0K -rwx------. 1 root root 1013 6月  26 22:38 /data/3306/mysql
4.0K -rwx------. 1 root root 1013 6月  26 22:36 /data/3307/mysql

[root@WeiyiGeek ~]$ /data/3306/mysql root 123.asd start #实际不建议这种形式容易泄露密码
Starting MySQL...
[root@WeiyiGeek ~]$ /data/3307/mysql root 123.asd start
Starting MySQL...

[root@WeiyiGeek ~]$ ss -tlnp 
*:3307