本文参考资料链接:https://www.cnblogs.com/xuchenliang/p/6843990.html
关于mysql 多实例的介绍与优点,本人就不一一叙述了,请参考资料链接里的文章解释的很明白,本文是对上述资料的补充与更新;so,废话不多说了直接来到安装部署环节,开始撸起我们最爱的Linux小命令,
一、Mysql多实例安装部署
1、下载mysql 免编译二进制包
mysql5.7 下载链接: https://dev.mysql.com/downloads/mysql/5.7.html#downloads
mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
2、解压和迁移
tar -xvf /usr/ZGL/zhugeliang/local/mysql/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
如果没有放到你想要的位置 可以选择 cp 或 mv,我这里选择直接将解压包放到我想要解压的位置
3、关闭iptables关闭selinux
阿里云Linux 默认这两项都是关闭的,因此可以跳过此步骤
临时关闭:service iptables stop
永久关闭:chkconfig iptables off
关闭selinux
vi /etc/sysconfig/selinux
将SELINUX修改为DISABLED,即SELINUX=DISABLED
4、创建mysql用户
关于创建mysql 用户这一步骤,如果对Linux 用户权限这块不太懂的同学,可以选择直接用root用户操作,
这里需注意在配置文件/etc/my.cnf中设置操作用户为root
[mysqld]
user=root
basedir = /usr/ZGL/zhugeliang/local/mysql-5.7.22
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
设置自定义权限用户,这里举个例子
groupadd -g 27 mysql
useradd -u 27 -g mysql mysql
id mysql
uid=501(mysql) gid=501(mysql) groups=501(mysql)
5、创建相关目录
mkdir -p /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/ {mysql_3306,mysql_3307}
mkdir /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3306/ {data,log,tmp}
mkdir /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3307/ {data,log,tmp}
6、设置目录权限
chown -R mysql:mysql /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/
chown -R mysql:mysql /usr/ZGL/zhugeliang/local/mysql-5.7.22/
7、添加环境变量
echo 'export PATH=$PATH:/usr/ZGL/zhugeliang/local/mysql-5.7.22/bin' >> /etc/profile
重新加载配置文件 source /etc/profile
8、复制my.cnf文件到etc目录
此步骤只针对mysql5.6以下版本,5.7版本已废除默认配置文件
cp /usr/ZGL/zhugeliang/local/mysql-5.6.22/support-files/my-default.cnf /etc/my.cnf
9、修改my.cnf配置文件
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld_multi]
mysqld = /usr/ZGL/zhugeliang/local/mysql-5.7.22/bin/mysqld_safe
mysqladmin = /usr/ZGL/zhugeliang/local/mysql-5.7.22/bin/mysqladmin
log = /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysqld_multi.log
[mysqld]
user=root
basedir = /usr/ZGL/zhugeliang/local/mysql-5.7.22
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld3306]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3306/data
port=3306
server_id=3306
socket=/tmp/mysql_3306.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3306/log/slow.log
log-error = /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3306/log/error.log
binlog_format = mixed
log-bin = /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3306/log/mysql3306_bin
[mysqld3307]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3307/data
port=3307
server_id=3307
socket=/tmp/mysql_3307.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3307/log/slow.log
log-error = /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3307/log/error.log
binlog_format = mixed
log-bin = /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3307/log/mysql3307_bin
10、初始化数据库
mysql5.7以后初始化数据库已废弃mysql_install_db
bin/mysqld --user=root --basedir=/usr/ZGL/zhugeliang/local/mysql-5.7.22 --datadir=/usr/ZGL/zhugeliang/local/mysql- 5.7.22/data/mysql/mysql_3306/data --initialize
bin/mysqld --user=root --basedir=/usr/ZGL/zhugeliang/local/mysql-5.7.22 --datadir=/usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3307/data --initialize
此处需注意记住初始化后数据库自动创建的默认密码
10.1、查看数据库是否初始化成功
cd /usr/ZGL/zhugeliang/local/mysql-5.7.22/data/mysql/mysql_3306/data
ls
看到此文件下有如下文件或文件夹,标示初始化成功
mysql、performance_schema 、sys 、auto.cnf 、ib_buffer_pool、ib_logfile0、ib_logfile1、ibdata1、ibtmp1
10.2、设置启动文件
cp /usr/ZGL/zhugeliang/local/mysql-5.7.22/support-files/mysql.server /etc/init.d/mysql
10.3、mysqld_multi进行多实例管理
启动全部实例:/usr/ZGL/zhugeliang/local/mysql-5.7.22/bin/mysqld_multi start
查看全部实例状态:/usr/ZGL/zhugeliang/local/mysql-5.7.22/bin/mysqld_multi report
启动单个实例:/usr/ZGL/zhugeliang/local/mysql-5.7.22/bin/mysqld_multi start 3306
停止单个实例:/usr/ZGL/zhugeliang/local/mysql-5.7.22/bin/mysqld_multi stop 3306
查看单个实例状态:/usr/ZGL/zhugeliang/local/mysql-5.7.22/bin/mysqld_multi report 3306
10.4、启动全部实例
/usr/ZGL/zhugeliang/local/mysql-5.7.22/bin/mysqld_multi start
/usr/ZGL/zhugeliang/local/mysql-5.7.22/bin/mysqld_multi report
10.5、修改密码
mysql -S /tmp/mysql_3306.sock 登录3306mysql服务
set password for root@'localhost'=password('123456');
flush privileges;
10.6、设置远程连接用户
grant all privileges on *.* to root@'%' identified by "password";
flush privileges
10.7、外部软件登录数据库
使用Navicate 输入ip 用户名 密码 登录成功