1.7. Mysql多实例
1.7.1.多实例介绍
什么是数据库多实例
MySQL多实例就是在一台服务器上同时开启多个不同的服务端口（如：3306、3307等），同时运行多个MySQL服务进程，这些服务进程通过不同的Socket监听不同的服务端口来提供服务。多实例可能是MySQL的不同版本,也可能是MySQL的同一版本实现
多实例的好处
可有效利用服务器资源。当单个服务器资源有剩余时，可以充分利用剩余资源提供更多的服务，且可以实现资源的逻辑隔离节约服务器资源。例如公司服务器资源紧张，但是数据库又需要各自尽量独立的提供服务，并且还需要到主从复制等技术，多实例就是最佳选择
多实例弊端
存在资源互相抢占的问题。比如：当某个数据库实例并发很高或者SQL查询慢时，整个实例会消耗大量的CPU、磁盘I/O等资源，导致服务器上面其他的数据库实例在提供服务的质量也会下降,所以具体的需求要根据自己的实际情况而定。
1.7.2.Mysql多实例常见的配置方案
- 单一的配置文件、单一启动程序多实例部署方式
MySQL官方文档提到的单一配置文件、单一启动程序多实例部署方式
耦合度太高，一个配置文件不好管理。不是很推荐。 - 多配置文件、多启动程序部署方式
多配置文件、多启动程序部署方式是针对每个实例都有独立的配置文件和目录,管理灵活,此方案耦合度较低
工作开发和运维的统一原则：降低耦合度。所以建议的此方式
1.7.3.实战案例 ：CentOS 8 实现mariadb的yum安装的多实例
1.7.3.1.实战目的
CentOS 8 yum安装mariadb-10.3.17并实现三个实例
1.7.3.2.环境要求
一台系统CentOS 8.X主机
1.7.3.3.前提准备
关闭SElinux
关闭防火墙
时间同步
1.7.3.4.实现步骤
1.7.3.4.1.安装mariadb
[root@localhost ~]#yum -y install mariadb-server
1.7.3.4.2.准备三个实例的目录
[root@localhost ~]#mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
[root@localhost ~]#chown -R mysql.mysql /mysql #修改目录的属主属组，否则无法正常使用
[root@localhost ~]#tree -d /mysql/
/mysql/
├── 3306
│ ├── bin
│ ├── data
│ ├── etc
│ ├── log
│ ├── pid
│ └── socket
├── 3307
│ ├── bin
│ ├── data
│ ├── etc
│ ├── log
│ ├── pid
│ └── socket
└── 3308
├── bin
├── data
├── etc
├── log
├── pid
└── socket
1.7.3.4.3.生成数据库文件
[root@localhost ~]#mysql_install_db --user=mysql --datadir=/mysql/3306/data
[root@localhost ~]#mysql_install_db --user=mysql --datadir=/mysql/3307/data
[root@localhost ~]#mysql_install_db --user=mysql --datadir=/mysql/3308/data
1.7.3.4.4.准备配置文件
[root@localhost ~]#vim /mysql/3306/etc/my.cnf
[root@localhost ~]#sed 's/3306/3307/' /mysql/3306/etc/my.cnf > /mysql/3307/etc/my.cnf
[root@localhost ~]#sed 's/3306/3308/' /mysql/3306/etc/my.cnf > /mysql/3308/etc/my.cnf
[root@localhost ~]#cat /mysql/330*/etc/my.cnf
[mysqld]
port=3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
log-error=/mysql/3306/log/mysql.log
pid-file=/mysql/3306/pid/mysql.pid
[mysqld]
port=3307
datadir=/mysql/3307/data
socket=/mysql/3307/socket/mysql.sock
log-error=/mysql/3307/log/mysql.log
pid-file=/mysql/3307/pid/mysql.pid
[mysqld]
port=3308
datadir=/mysql/3308/data
socket=/mysql/3308/socket/mysql.sock
log-error=/mysql/3308/log/mysql.log
pid-file=/mysql/3308/pid/mysql.pid
1.7.3.4.5.准备启动脚本
[root@localhost ~]#vim /mysql/3306/bin/mysqld
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/bin"
mysql_basedir="/mysql"
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} shutdown
fi
}
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: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac
[root@localhost ~]#sed 's/3306/3307/' /mysql/3306/bin/mysqld >/mysql/3307/bin/mysqld
[root@localhost ~]#sed 's/3306/3308/' /mysql/3306/bin/mysqld >/mysql/3308/bin/mysqld
[root@localhost ~]#chmod +x /mysql/3307/bin/mysqld
[root@localhost ~]#chmod +x /mysql/3308/bin/mysqld
1.7.3.4.6.启动服务
[root@localhost ~]#/mysql/3306/bin/mysqld start
Starting MySQL...
[root@localhost ~]#/mysql/3307/bin/mysqld start
Starting MySQL...
[root@localhost ~]#/mysql/3308/bin/mysqld start
Starting MySQL...
[root@localhost ~]#ss -ntl | grep 330*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
1.7.3.4.7.登录实例
[root@localhost ~]#mysql -uroot -S /mysql/3306/socket/mysql.sock #指定sock登录
MariaDB [(none)]> \s;
--------------
mysql Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.3.28-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /mysql/3306/socket/mysql.sock
Uptime: 9 min 47 sec
Threads: 6 Questions: 4 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 11 Queries per second avg: 0.006
--------------
ERROR: No query specified
MariaDB [(none)]> Bye
[root@localhost ~]#mysql -h127.0.0.1 -P3307 #指定端口登录
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
#注意如果此时要停止服务需要手动输入root密码 此时初始密码为空
[root@centos8 ~]#/mysql/3308/bin/mysqld stop
Stoping MySQL...
Enter password:
1.7.3.4.8.修改root密码
[root@localhost ~]#mysqladmin -uroot -S /mysql/3306/socket/mysql.sock password 'sunxiang'
1.7.3.4.9.测试连接
[root@localhost ~]#mysql -uroot -S /mysql/3306/socket/mysql.sock #此次不输入密码无法登录
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]#mysql -uroot -S /mysql/3306/socket/mysql.sock -p'sunxiang'
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> exit
Bye
1.7.3.4.10.修改启动脚本
已经设置好密码之后，此时可以修改启动脚本，在其中添加密码信息，之后停止服务无需再次输入密码
[root@localhost ~]#cat /mysql/3306/bin/mysqld
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd="sunxiang"
cmd_path="/usr/bin"
[root@localhost ~]#/mysql/3306/bin/mysqld stop
Stoping MySQL...
1.7.3.4.11. 开机启动
[root@localhost ~]#cat /etc/rc.d/rc.local | grep -v '#'
for i in {3306..3308};do /mysql/$i/bin/mysqld start;d
[root@localhost ~]#chmod +x /etc/rc.d/rc.local #添加可执行权限很重要
2. SQL 语言
2.1. SQL语言的语法标准
2.1.1. 关系型数据库的常见组件
· 数据库：database
· 表：table，行：row 列：column
· 索引：index
· 视图：view
· 存储过程：procedure
· 存储函数：function
· 触发器：trigger
· 事件调度器：event scheduler，任务计划
· 用户：user
· 权限：privilege
2.1.2. SQL 语言规范
· 在数据库系统中，SQL 语句不区分大小写，建议用大写
· SQL语句可单行或多行书写，默认以 ” ; ” 结尾
· 关键词不能跨多行或简写
· 用空格和TAB 缩进来提高语句的可读性
· 子句通常位于独立行，便于编辑，提高可读性
注释：
SQL标准：
#单行注释，注意有空格
-- 注释内容
#多行注释
/*注释内容
注释内容
注释内容*/
MySQL注释：
# 注释内容
2.1.3.SQL语句分类
DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER
DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE
软件开发：CRUD
DQL：Data Query Language 数据查询语言
SELECT
DCL：Data Control Language 数据控制语言
GRANT，REVOKE
TCL：Transaction Control Language 事务控制语言
COMMIT，ROLLBACK，SAVEPOINT
2.1.3. SQL语句构成
关健字Keyword组成子句clause，多条clause组成语句
SELECT * #SELECT子句
FROM products #FROM子句
WHERE price>666 #WHERE子句
获取SQL 命令使用帮助：
官方帮助：
https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
查看SQL帮助
mysql> HELP KEYWORD
2.1.4. 字符集和排序
早期MySQL版本默认为latin1，从MySQL8.0开始默认字符集已经为 utf8mb4
建议：修改mysql8.0以前的默认字符集为utf8mb4
查看支持所有字符集
SHOW CHARACTER SET;
SHOW CHARSET;
查看支持所有排序规则
SHOW COLLATION;
#注意
utf8_general_ci不区分大小写
utf8_bin 区分大小写
查看当前使用的排序规则
SHOW VARIABLES LIKE 'collation%';
设置服务器默认的字符集
vim /etc/my.cnf #配置文件是那个就修改哪个默认是这个
[mysqld]
character-set-server=utf8mb4
设置mysql客户端默认的字符集
vim /etc/my.cnf
#针对mysql这一特定客户端
[mysql]
default-character-set=utf8mb4
#针对所有MySQL客户端
[client]
default-character-set=utf8mb4
2.1.5.实例修改10.3.28-MariaDB服务器的默认字符集
#查看服务器的默认字符集
MariaDB [(none)]> \s;
--------------
mysql Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.3.28-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1 #默认为latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /mysql/3306/socket/mysql.sock
Uptime: 45 min 48 sec
Threads: 6 Questions: 6 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 11 Queries per second avg: 0.002
--------------
ERROR: No query specified
#修改配置文件，重启服务
[root@localhost ~]#cat /mysql/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
log-error=/mysql/3306/log/mysql.log
pid-file=/mysql/3306/pid/mysql.pid
character-set-server=utf8mb4
[root@localhost ~]#/mysql/3306/bin/mysqld restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
#再次查看服务器的默认字符集
MariaDB [(none)]> \s;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***
--------------
mysql Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.3.28-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4 #修改为utf8mb4
Db characterset: utf8mb4
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /mysql/3306/socket/mysql.sock
Uptime: 8 sec
2.1.6.实例修改10.3.28-MariaDB客户端的默认字符集
#修改客户端配置文件
[root@localhost ~]#vim /etc/my.cnf.d/client.cnf
[client]
default-character-set=utf8mb4
#客户端登录服务器查看默认字符集
MariaDB [(none)]> \s;
--------------
mysql Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 9
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.3.28-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4 #修改为utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /mysql/3306/socket/mysql.sock
Uptime: 6 min 53 sec