上篇文章介绍了MySQL中索引应用,从本篇文章往后,将介绍MySQL运行维护相关内容,首先介绍一下MySQL单服务器多实例的搭建过程!
一、单机多实例的优缺点
1、优点
(1)单机多实例的部署方式可以充分利用服务器资源
(2)如果后期业务量会扩展,使用多实例将数据分开存储,有利于后期的数据迁移
2、缺点
(1)由于多实例部署在一台机器上,会造成一定程度上的服务器资源争用(包括:CPU,内存,IO,网络等)
(2)单机上的多实例容灾能力较差,比如:单台服务器磁盘如果损坏,所有实例的数据可能会丢失
二、单机多实例的搭建过程
1、软件和环境准备
(1)通用二进制格式下载地址:https://dev.mysql.com/get/Dow...
(2)主机IP:192.168.0.10
2、将下载的软件上传到远程服务器上,然后解压,重命名。以下介绍过程中使用的是5.6.39版本,和5.6.40版本没有太大区别
[root@WB-BLOG local]# tar xf mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz
[root@WB-BLOG local]# mv mysql-5.6.39-linux-glibc2.12-x86_64 mysql-5.6.39
3、创建mysql用户和多实例的目录
[root@WB-BLOG local]# useradd mysql -s /sbin/nologin -M
[root@WB-BLOG local]# mkdir -pv /mysql_data/{3306,3307}/data
[root@WB-BLOG local]# tree /mysql_data/
/mysql_data/
├── 3306
│ └── data
└── 3307
└── data
4、配置3306端口对应的实例
[root@WB-BLOG local]# cd mysql-5.6.39
[root@WB-BLOG mysql-5.6.39]# cp support-files/my-default.cnf /mysql_data/3306/my.cnf
[root@WB-BLOG mysql-5.6.39]# cd /mysql_data/3306/
[root@WB-BLOG 3306]# vim my.cnf
编辑端口为3306的实例的配置文件,写入如下内容:
[mysql]
socket = /mysql_data/3306/data/mysql.sock
[mysqld]
event_scheduler = ON
character-set-server = UTF8
innodb_buffer_pool_size = 128M
basedir = /usr/local/mysql-5.6.39
datadir = /mysql_data/3306/data
port = 3306
server_id = 3
socket = /mysql_data/3306/data/mysql.sock
innodb_file_per_table = 1
skip-name-resolve
innodb_flush_log_at_trx_commit = 2
log_bin = /mysql_data/3306/data/mysql-bin
log_bin_index = /mysql_data/3306/data/mysql-bin-index
binlog_format = mixed
log_error = /mysql_data/3306/data/mysql-error
pid_file = /mysql_data/3306/data/mysql.pid
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
max_connections = 1000
max_allowed_packet = 16M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
5、配置3307端口对应的实例
将/mysql_data/3306目录下的my.cnf复制一份到/mysql_data/3307目录下,并修配置文件中的端口,server-id和数据目录的路径,如下:
[root@WB-BLOG 3306]# cd ../3307/
[root@WB-BLOG 3307]# cp ../3306/my.cnf .
[root@WB-BLOG 3307]# vim my.cnf
在端口为3307的实例对应的配置文件中写入如下内容:
[mysql]
socket = /mysql_data/3307/data/mysql.sock
[mysqld]
event_scheduler = ON
character-set-server = UTF8
innodb_buffer_pool_size = 128M
basedir = /usr/local/mysql-5.6.39
datadir = /mysql_data/3307/data
port = 3307
server_id = 4
socket = /mysql_data/3307/data/mysql.sock
innodb_file_per_table = 1
skip-name-resolve
innodb_flush_log_at_trx_commit = 2
log_bin = /mysql_data/3307/data/mysql-bin
log_bin_index = /mysql_data/3307/data/mysql-bin-index
binlog_format = mixed
log_error = /mysql_data/3307/data/mysql-error
pid_file = /mysql_data/3307/data/mysql.pid
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
max_connections = 1000
max_allowed_packet = 16M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
注意:上述的内容对比之后可以发现,只有端口和server-id不同,所以可以使用sed命令完成替换,如下:
[root@WB-BLOG 3307]# sed -i "s#3306#3307#g" my.cnf
[root@WB-BLOG 3307]# sed -i "s#server-id=3#server-id=4#g" my.cnf
6、查看目录结构是否正常,如下所示表示的结果表示正常。然后将/mysql_data/3306和/mysql_data/3307目录授权给mysql用户
[root@WB-BLOG 3307]# tree /mysql_data/
/mysql_data/
├── 3306
│ ├── data
│ └── my.cnf
└── 3307
├── data
└── my.cnf
[root@WB-BLOG 3307]# chown -R mysql:mysql /mysql_data/{3306,3307}
[root@WB-BLOG 3307]# ls -l /mysql_data/
total 8
drwxr-xr-x. 3 mysql mysql 4096 May 22 04:45 3306
drwxr-xr-x. 3 mysql mysql 4096 May 22 04:51 3307
7、初始化3306实例和3307实例的数据目录
[root@WB-BLOG 3307]# cd /usr/local/mysql-5.6.39
[root@WB-BLOG mysql-5.6.39]# ./scripts/mysql_install_db --basedir=/usr/local/mysql-5.6.39 --datadir=/mysql_data/3306/data/ --user=mysql
[root@WB-BLOG mysql-5.6.39]# ./scripts/mysql_install_db --basedir=/usr/local/mysql-5.6.39 --datadir=/mysql_data/3307/data/ --user=mysql
#从初始化的控制台日志判断是否初始化成功,看到两个单行的OK表示成功,如下
2018-05-22 04:58:48 0 [Note] /usr/local/mysql-5.6.39/bin/mysqld (mysqld 5.6.39-log) starting as process 3642 ...
OK
...
2018-05-22 04:58:54 0 [Note] /usr/local/mysql-5.6.39/bin/mysqld (mysqld 5.6.39-log) starting as process 3664 ...
OK
#查看3306和3307实例的数据目录是否正常,是否有初始化之后的系统表
[root@WB-BLOG mysql-5.6.39]# ls /mysql_data/3306/data/
ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
[root@WB-BLOG mysql-5.6.39]# ls /mysql_data/3307/data/
ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
8、使用mysqld_safe命令测试实例是否可以正常启动
[root@WB-BLOG mysql-5.6.39]# cd bin/
[root@WB-BLOG bin]# ./mysqld_safe --defaults-file=/mysql_data/3306/my.cnf --datadir=/mysql_data/3306/data/ &
#查看进程是否正常启动
[root@WB-BLOG bin]# netstat -tunlp | grep mysql
tcp 0 0 :::3306 :::* LISTEN 4050/mysqld
如上结果表示3306实例启动正常,可以用此方法测试3307是否可以正常启动。
9、手动编写针对每个实例的启动脚本
(1)修改3306和3307实例的密码,修改方式为使用跳过授权表的方式启动,然后登陆修改,可以参考第一篇博文,MySQL的多种安装方式中有介绍,使用的命令如下,不再详述
[root@WB-BLOG 3306]# /usr/local/mysql-5.6.39/bin/mysqld_safe --defaults-file=/mysql_data/3306/my.cnf --datadir=/mysql_data/3306/data/ --skip-grant-tables &
[root@WB-BLOG ~]# mysql -uroot -p -P3306 -S /mysql_data/3306/data/mysql.sock
mysql> update user set password = password('root');
mysql> flush privileges;
(2)编写3306实例的启动脚本,如下:
[root@WB-BLOG bin]# cd /mysql_data/3306/
[root@WB-BLOG 3306]# vim mysqld
写入如下内容:
#!/bin/bash
#
MYSQL_BASE_PATH=/usr/local/mysql-5.6.39
MYSQL_PORT=3306
MYSQL_3306_BASEDIR=/mysql_data/3306
MYSQL_SOCK=${MYSQL_3306_BASEDIR}/data/mysql.sock
MYSQL_CONF=${MYSQL_3306_BASEDIR}/my.cnf
MYSQL_DATADIR=${MYSQL_3306_BASEDIR}/data
MYSQL_USER=root
MYSQL_PASS=root
#When No Input
function Usage(){
echo "Please Usage ./mysqld {start|stop|restart|status}"
exit 2
}
#Start MySQL
function start_mysql() {
if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
echo "MySQL is already running..."
else
${MYSQL_BASE_PATH}/bin/mysqld_safe --defaults-file=${MYSQL_CONF} --datadir=${MYSQL_DATADIR} > /dev/null 2>&1 &
sleep 2
if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
echo "MySQL start success!"
else
echo "MySQL start failure.View logs and try again."
fi
fi
}
#Stop MySQL
function stop_mysql(){
if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
${MYSQL_BASE_PATH}/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PASS} -P${MYSQL_PORT} -S ${MYSQL_SOCK} shutdown > /dev/null 2>&1 &
sleep 2
if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
echo "MySQL stop failure..."
else
echo "MySQL stop success!"
fi
else
echo "MySQL is not running..."
fi
}
#Restart MySQL
function restart_mysql(){
stop_mysql
sleep 2
start_mysql
}
#MySQL status
function mysql_status(){
if [ `ps -ef| grep mysql | grep ${MYSQL_PORT}|grep -v grep | wc -l` -gt 1 ]; then
echo "MySQL is running..."
else
echo "MySQL is stopped."
fi
}
case $1 in
start)
start_mysql
;;
stop)
stop_mysql
;;
restart)
restart_mysql
;;
status)
mysql_status
;;
*)
Usage
;;
esac
授予mysqld脚本可执行权限,然后启动:
[root@WB-BLOG 3306]# chmod +x mysqld
[root@WB-BLOG 3306]# ./mysqld start
#查看运行状态
[root@WB-BLOG 3306]# ./mysqld status
MySQL is running...
(3)将3306实例中的mysqld脚本拷贝一份到/mysqld_data/3307目录下,然后修改端口及实例的目录,最终内容如下:
#!/bin/bash
#
MYSQL_BASE_PATH=/usr/local/mysql-5.6.39
MYSQL_PORT=3307
MYSQL_3307_BASEDIR=/mysql_data/3307
MYSQL_SOCK=${MYSQL_3307_BASEDIR}/data/mysql.sock
MYSQL_CONF=${MYSQL_3307_BASEDIR}/my.cnf
MYSQL_DATADIR=${MYSQL_3307_BASEDIR}/data
MYSQL_USER=root
MYSQL_PASS=root
#When No Input
function Usage(){
echo "Please Usage ./mysqld {start|stop|restart|status}"
exit 2
}
#Start MySQL
function start_mysql() {
if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
echo "MySQL is already running..."
else
${MYSQL_BASE_PATH}/bin/mysqld_safe --defaults-file=${MYSQL_CONF} --datadir=${MYSQL_DATADIR} > /dev/null 2>&1 &
sleep 2
if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
echo "MySQL start success!"
else
echo "MySQL start failure.View logs and try again."
fi
fi
}
#Stop MySQL
function stop_mysql(){
if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
${MYSQL_BASE_PATH}/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PASS} -P${MYSQL_PORT} -S ${MYSQL_SOCK} shutdown > /dev/null 2>&1 &
sleep 2
if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
echo "MySQL stop failure..."
else
echo "MySQL stop success!"
fi
else
echo "MySQL is not running..."
fi
}
#Restart MySQL
function restart_mysql(){
stop_mysql
sleep 2
start_mysql
}
#MySQL status
function mysql_status(){
if [ `ps -ef| grep mysql | grep ${MYSQL_PORT}|grep -v grep | wc -l` -gt 1 ]; then
echo "MySQL is running..."
else
echo "MySQL is stopped."
fi
}
case $1 in
start)
start_mysql
;;
stop)
stop_mysql
;;
restart)
restart_mysql
;;
status)
mysql_status
;;
*)
Usage
;;
esac
10、单机多实例的登录
(1)常规登录方法
[root@WB-BLOG ~]# mysql -uroot -proot -h127.0.0.1 -P3307 -S /mysql_data/3307/data/mysql.sock
参数解释:
-S:指定示例对应的Socket文件
注意:单机多实例的登录需要指定待登录示例对应的socket文件。
(2)为了防止每次登陆MySQL时需要带一对参数,编写一个方便登陆的脚本mysql_login.sh,将登录所需参数写入到脚本中,内容如下:
[root@WB-BLOG mysql_data]# cat mysql_login.sh
#!/bin/bash
#
SERVER_IP=127.0.0.1
MYSQL_BASE_PATH=/usr/local/mysql-5.6.39
MYSQL_01_PORT=3306
MYSQL_02_PORT=3307
#MYSQL USER AND PASS
MYSQL_01_USER=root
MYSQL_01_PASS=root
MYSQL_02_USER=root
MYSQL_02_PASS=root
MYSQL_01_BASEDIR=/mysql_data/3306
MYSQL_02_BASEDIR=/mysql_data/3307
MYSQL_01_SOCK=${MYSQL_01_BASEDIR}/data/mysql.sock
MYSQL_02_SOCK=${MYSQL_02_BASEDIR}/data/mysql.sock
echo "1> mysql-3306"
echo "2> mysql-3307"
read -p "Please Input the Login Server Number:[1,2]:" INPUT
case $INPUT in
1)
${MYSQL_BASE_PATH}/bin/mysql -u${MYSQL_01_USER} -p${MYSQL_01_PASS} -P${MYSQL_01_PORT} -h${SERVER_IP} -S ${MYSQL_01_SOCK} --prompt='mysql-server-3306> '
;;
2)
${MYSQL_BASE_PATH}/bin/mysql -u${MYSQL_02_USER} -p${MYSQL_02_PASS} -P${MYSQL_02_PORT} -h${SERVER_IP} -S ${MYSQL_02_SOCK} --prompt='m
ysql-server-3307> '
;;
*)
echo "Wrong Input.Please run mysql_login.sh again."
;;
esac
脚本中的登录命令参数说明:
prompt:指定登录之后的mysql命令行提示符,如果同时打开多个shell窗口,可以在每一个mysql的命令行窗口指定命令提示符,防止数据库的误操作。
脚本的运行效果:
[root@WB-BLOG mysql_data]# ./mysql_login.sh
1> mysql-3306
2> mysql-3307
Please Input the Login Server Number:[1,2]:1
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql-server-3306>
#登录成功
至此,MySQL的多实例搭建介绍完毕,后面的主从异步复制介绍打算使用MySQL单机多实例来部署,如果上述介绍有什么疑问或者问题,可以在下方留言指出,欢迎转发评论!