mysql-部署多实例全过程

1、操作系统层准备工作
安装操作系统，关闭selinux 防火墙

2、数据相关准备工作
lvcreate -n lv_mysql -L 20G system
lvcreate -n lv_mysqldata -L 20G data
lvcreate -n lv_mysqldata1 -L 20G data
lvcreate -n lv_mysqldata2 -L 20G data
lvcreate -n lv_mysqldata3 -L 20G data
lvcreate -n lv_mysqldata4 -L 20G data
lvcreate -n lv_mysqld_muti -L 20G data

mkfs.xfs /dev/system/lv_mysql
mkfs.xfs /dev/data/lv_mysqldata
mkfs.xfs /dev/data/lv_mysqldata1
mkfs.xfs /dev/data/lv_mysqldata2
mkfs.xfs /dev/data/lv_mysqldata3
mkfs.xfs /dev/data/lv_mysqldata4
mkfs.xfs /dev/data/lv_mysqld_muti

mkdir /mysql
mkdir /mysqldata
mkdir /mysqldata1
mkdir /mysqldata2
mkdir /mysqldata3
mkdir /mysqldata4
mkdir /mysqld_muti

mount /dev/system/lv_mysql /mysql
mount /dev/data/lv_mysqldata /mysqldata
mount /dev/data/lv_mysqldata1 /mysqldata1
mount /dev/data/lv_mysqldata2 /mysqldata2
mount /dev/data/lv_mysqldata3 /mysqldata3
mount /dev/data/lv_mysqldata4 /mysqldata4
mount /dev/data/lv_mysqld_muti /mysqld_muti

cat "
/dev/system/lv_mysql /mysql xfs defaults 0 0
/dev/data/lv_mysqldata /mysqldata xfs defaults 0 0
/dev/data/lv_mysqldata1 /mysqldata1 xfs defaults 0 0
/dev/data/lv_mysqldata2 /mysqldata2 xfs defaults 0 0
/dev/data/lv_mysqldata3 /mysqldata3 xfs defaults 0 0
/dev/data/lv_mysqldata4 /mysqldata4 xfs defaults 0 0
/dev/data/lv_mysqld_muti /mysqld_muti xfs defaults 0 0
" >> /etc/fstab

chown -R mysql:mysql /mysqldata
chown -R mysql:mysql /mysqldata1
chown -R mysql:mysql /mysqldata2
chown -R mysql:mysql /mysqldata3
chown -R mysql:mysql /mysqldata4
chown -R mysql:mysql /mysqld_muti

3、安装数据库
3.1 下载并上传mysql数据库软件到 /mysql 下，并解压缩
tar -zxf mysql-advanced-5.7.29-linux-glibc2.12-x86_64.tar.gz
mv mysql-advanced-5.7.29-linux-glibc2.12-x86_64/ mysql-5.7.29
3.2 groupadd mysql
useradd -r -g mysql -s /bin/false mysql
cd /usr/local
ln -s /mysql/mysql-5.7.29/ mysql
3.3
mkdir -p /mysqldata/3306/socket/
>/mysqldata/3306/socket/mysql.sock
mkdir -p /mysqldata/3306/pid
mkdir -p /mysqldata/3306/tmp
mkdir -p /mysqldata/3306/dbdata
mkdir -p /mysqldata/3306/tmp
mkdir -p /mysqldata/3306/logfile/binlogs
mkdir -p /mysqldata/3306/logfile/slowlogs
mkdir -p /mysqldata/3306/logfile/genlogs
mkdir -p /mysqldata/3306/logfile/errlogs

mkdir -p /mysqldata1/3307/socket/
>/mysqldata1/3307/socket/mysql.sock
mkdir -p /mysqldata1/3307/pid
mkdir -p /mysqldata1/3307/tmp
mkdir -p /mysqldata1/3307/dbdata
mkdir -p /mysqldata1/3307/tmp
mkdir -p /mysqldata1/3307/logfile/binlogs
mkdir -p /mysqldata1/3307/logfile/slowlogs
mkdir -p /mysqldata1/3307/logfile/genlogs
mkdir -p /mysqldata1/3307/logfile/errlogs

mkdir -p /mysqldata2/3308/socket/
>/mysqldata2/3308/socket/mysql.sock
mkdir -p /mysqldata2/3308/pid
mkdir -p /mysqldata2/3308/tmp
mkdir -p /mysqldata2/3308/dbdata
mkdir -p /mysqldata2/3308/tmp
mkdir -p /mysqldata2/3308/logfile/binlogs
mkdir -p /mysqldata2/3308/logfile/slowlogs
mkdir -p /mysqldata2/3308/logfile/genlogs
mkdir -p /mysqldata2/3308/logfile/errlogs
mkdir -p /mysqldata3/3309/socket/
>/mysqldata3/3309/socket/mysql.sock
mkdir -p /mysqldata3/3309/pid
mkdir -p /mysqldata3/3309/tmp
mkdir -p /mysqldata3/3309/dbdata
mkdir -p /mysqldata3/3309/tmp
mkdir -p /mysqldata3/3309/logfile/binlogs
mkdir -p /mysqldata3/3309/logfile/slowlogs
mkdir -p /mysqldata3/3309/logfile/genlogs
mkdir -p /mysqldata3/3309/logfile/errlogs

mkdir -p /mysqldata4/3304/socket/
>/mysqldata4/3304/socket/mysql.sock
mkdir -p /mysqldata4/3304/pid
mkdir -p /mysqldata4/3304/tmp
mkdir -p /mysqldata4/3304/dbdata
mkdir -p /mysqldata4/3304/tmp
mkdir -p /mysqldata4/3304/logfile/binlogs
mkdir -p /mysqldata4/3304/logfile/slowlogs
mkdir -p /mysqldata4/3304/logfile/genlogs
mkdir -p /mysqldata4/3304/logfile/errlogs

chown mysql:mysql /mysqldata/3306/socket/mysql.sock
chown mysql:mysql /mysqldata1/3307/socket/mysql.sock
chown mysql:mysql /mysqldata2/3308/socket/mysql.sock
chown mysql:mysql /mysqldata3/3309/socket/mysql.sock
chown mysql:mysql /mysqldata4/3304/socket/mysql.sock

3.4
将下面两条添加到 /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
PS1='[\u@\H \w]\$'
~

4、初始化数据库：
4.1 先初始化 3306 。
下面内容保存到 /etc/my.cnf文件中

 ----根据实际情况做自己的my.cnf文件吧

4.2 初始化 3306 数据库
mysqld --initialize --user=mysql
mysql_ssl_rsa_setup
cp support-files/mysql.server /etc/init.d/mysqld
启动数据库
mysqld_safe --user=mysql &

4.3 建立脚本
cp /etc/my.cnf /mysqldata/3306/conf/my.cnf
mkdir /mysqldata/3306/scripts
[root@CentOS77-Mysql /mysqldata/3306/scripts]#ll
total 16
-rwxr-xr-x 1 mysql mysql 78 Apr 18 21:36 abort.sh
-rwxr-xr-x 1 mysql mysql 154 Apr 18 21:36 check.sh
-rwxr-xr-x 1 mysql mysql 404 Apr 18 21:36 start.sh
-rwxr-xr-x 1 mysql mysql 49 Apr 18 21:37 stop.sh
[root@CentOS77-Mysql /mysqldata/3306/scripts]#cat *
##abort.sh
#!/bin/sh

ps -ef|grep mysql | grep -v grep | awk '{print "kill -9 "$2}' | sh

##check.sh
#!/bin/sh

if [ $(ps -ef|grep mysqld_safe | grep -v grep | wc -l) -eq 1 ];then
echo "Mysql is running!"
else
echo "Mysql has stoped!"
fi

##start.sh
#!/bin/sh

if [ -e /mysqldata/3306/socket/mysql.sock.lock ] ;then
echo "/mysqldata/3306/socket/mysql.sock.lock 已存在，是否删除该文件，继续启动"
read _opt
if [ "${_opt}" != n ];then
rm -fr /mysqldata/3306/socket/mysql.sock.lock
else
exit 0
fi
fi

mysqld_safe --defaults-file=/mysqldata/3306/conf/my.cnf &
exit 0

##stop.sh
#!/bin/sh

mysqladmin --login-path=root shutdown

4.4 建立多实例数据库 3307 3308 3309 3304
将下面内容保存到 /etc/my.cnf

XXX  ----根据实际情况做自己的my.cnf文件吧  - 隐去了一部分

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /mysqld_multi/mysqld_multi_log/mysqld_multi.log

[mysqld1]
port = 3307
socket = /mysqldata1/3307/socket/mysql.sock
pid_file = /mysqldata1/3307/pid/mysqld.pid
secure_file_priv = /mysqldata1/3307/tmp
init_file = /mysqldata1/3307/dbdata/init.sql
datadir = /mysqldata1/3307/dbdata
tmpdir = /mysqldata1/3307/tmp
innodb_data_home_dir = /mysqldata1/3307/dbdata
innodb_log_group_home_dir = /mysqldata1/3307/dbdata
log_bin = /mysqldata1/3307/logfile/binlogs/mysql-bin
slow_query_log_file = /mysqldata1/3307/logfile/slowlogs/mysql-slow.log
general_log_file = /mysqldata1/3307/logfile/genlogs/mysql-general.log
log_error = /mysqldata1/3307/logfile/errlogs/mysql-error.log

[mysqld2]
port = 3308
socket = /mysqldata2/3308/socket/mysql.sock
pid_file = /mysqldata2/3308/pid/mysqld.pid
secure_file_priv = /mysqldata2/3308/tmp
init_file = /mysqldata2/3308/dbdata/init.sql
datadir = /mysqldata2/3308/dbdata
tmpdir = /mysqldata2/3308/tmp
innodb_data_home_dir = /mysqldata2/3308/dbdata
innodb_log_group_home_dir = /mysqldata2/3308/dbdata
log_bin = /mysqldata2/3308/logfile/binlogs/mysql-bin
slow_query_log_file = /mysqldata2/3308/logfile/slowlogs/mysql-slow.log
general_log_file = /mysqldata2/3308/logfile/genlogs/mysql-general.log
log_error = /mysqldata2/3308/logfile/errlogs/mysql-error.log

[mysqld3]
port = 3309
socket = /mysqldata3/3309/socket/mysql.sock
pid_file = /mysqldata3/3309/pid/mysqld.pid
secure_file_priv = /mysqldata3/3309/tmp
init_file = /mysqldata3/3309/dbdata/init.sql
datadir = /mysqldata3/3309/dbdata
tmpdir = /mysqldata3/3309/tmp
innodb_data_home_dir = /mysqldata3/3309/dbdata
innodb_log_group_home_dir = /mysqldata3/3309/dbdata
log_bin = /mysqldata3/3309/logfile/binlogs/mysql-bin
slow_query_log_file = /mysqldata3/3309/logfile/slowlogs/mysql-slow.log
general_log_file = /mysqldata3/3309/logfile/genlogs/mysql-general.log
log_error = /mysqldata3/3309/logfile/errlogs/mysql-error.log

[mysqld4]
port = 3304
socket = /mysqldata4/3304/socket/mysql.sock
pid_file = /mysqldata4/3304/pid/mysqld.pid
secure_file_priv = /mysqldata4/3304/tmp
init_file = /mysqldata4/3304/dbdata/init.sql
datadir = /mysqldata4/3304/dbdata
tmpdir = /mysqldata4/3304/tmp
innodb_data_home_dir = /mysqldata4/3304/dbdata
innodb_log_group_home_dir = /mysqldata4/3304/dbdata
log_bin = /mysqldata4/3304/logfile/binlogs/mysql-bin
slow_query_log_file = /mysqldata4/3304/logfile/slowlogs/mysql-slow.log
general_log_file = /mysqldata4/3304/logfile/genlogs/mysql-general.log
log_error = /mysqldata4/3304/logfile/errlogs/mysql-error.log

4.5 初始化 3307 3308 3309 3304 四个库
mysqld --initialize --user=mysql --basedir=/mysql/mysql-5.7.29 --datadir=/mysqldata1/3307/dbdata
mysqld --initialize --user=mysql --basedir=/mysql/mysql-5.7.29 --datadir=/mysqldata2/3308/dbdata
mysqld --initialize --user=mysql --basedir=/mysql/mysql-5.7.29 --datadir=/mysqldata3/3309/dbdata
mysqld --initialize --user=mysql --basedir=/mysql/mysql-5.7.29 --datadir=/mysqldata4/3304/dbdata

4.6 创建init.sql文件
>/mysqldata/3306/dbdata/init.sql
>/mysqldata1/3307/dbdata/init.sql
>/mysqldata2/3308/dbdata/init.sql
>/mysqldata3/3309/dbdata/init.sql
>/mysqldata4/3304/dbdata/init.sql
chown mysql:mysql /mysqldata/3306/dbdata/init.sql
chown mysql:mysql /mysqldata1/3307/dbdata/init.sql
chown mysql:mysql /mysqldata2/3308/dbdata/init.sql
chown mysql:mysql /mysqldata3/3309/dbdata/init.sql
chown mysql:mysql /mysqldata4/3304/dbdata/init.sql

5、取消强密码校验
[mysqld]
# disable passwd check #
#validate_password = off
validate_password_check_user_name =OFF
validate_password_length =3
validate_password_mixed_case_count =0
validate_password_number_count =0
validate_password_policy =0
validate_password_special_char_count=0

6、设置新密码
mysql -uroot -p --socket=/mysqldata1/3307/socket/mysql.sock
mysql -uroot -p --socket=/mysqldata3/3309/socket/mysql.sock
mysql -uroot -p --socket=/mysqldata2/3308/socket/mysql.sock
mysql -uroot -p --socket=/mysqldata4/3304/socket/mysql.sock
mysql -uroot -p'P@ssw0rd' --socket=/mysqldata4/3304/socket/mysql.sock
mysql -uroot -p'P@ssw0rd' --socket=/mysqldata3/3309/socket/mysql.sock
mysql -uroot -p'P@ssw0rd' --socket=/mysqldata2/3308/socket/mysql.sock
mysql -uroot -p'P@ssw0rd' --socket=/mysqldata1/3307/socket/mysql.sock

7、分别设置不同的标签
mysql_config_editor set -G mysql6 -u root -p -S /mysqldata/3306/socket/mysql.sock
mysql_config_editor set -G mysql4 -u root -p -S /mysqldata4/3304/socket/mysql.sock
mysql_config_editor set -G mysql9 -u root -p -S /mysqldata3/3309/socket/mysql.sock
mysql_config_editor set -G mysql8 -u root -p -S /mysqldata2/3308/socket/mysql.sock
mysql_config_editor set -G mysql7 -u root -p -S /mysqldata1/3307/socket/mysql.sock

登录 mysql --login-path=mysql4
这样就可以免密登录