MySQL基本管理(四)

第1章 MySQL的连接管理

1.1 MySQL基础管理命令

1.1.1 查看MySQL版本信息

[root@db02 ~]# mysql --version
# 或
[root@db02 ~]# mysql -V

1.1.2 查看MySQL帮助信息

[root@db02 ~]# mysql --help
# 或
[root@db02 ~]# mysql -?

1.2 连接管理的方式

1.2.1 本地连接到在同一主机上运行的服务器

[root@db02 ~]# mysql -uroot -p12345678

1.2.2 远程连接到在其他主机上运行的服务器

[root@db02 ~]# mysql -uroot -p12345678 -h 10.0.0.42

1.3 常见的特定于客户机的连接选项

-u <user_name> 或 --host=<user_name> # 连接用户
-p<password> # 连接密码
-h <host_name> 或 --host=<host_name> # 连接主机IP
--protocol=<protocol_name> # 连接协议
-P <port_number> 或 --port=<port_number> # 连接端口
-S <socket_name> 或 --socket=<socket_name> # 指定Socket路径

1.4 数据库的启动流程介绍

图片[1]|MySQL基本管理(四)|leon的博客

第2章 MySQL的启动和关闭(启动脚本介绍)

2.1 启动MySQL

图片[2]|MySQL基本管理(四)|leon的博客

  • 启动MySQL的方式:
  1. 预编译时指定启动选项
  2. 命令行指定启动选项
  3. 使用配置文件指定启动选项

2.2 关闭MySQL

2.2.1 使用mysqladmin命令

mysqladmin shutdown

2.2.2 使用脚本关闭

servive mysqld stop

2.2.3 利用系统进程管理命令关闭MySQL

kill pid # 这里的pid为数据库服务对应的进程号
killall mysqld # 这里的mysqld是数据库服务对应的进程名字
pkill mysqld # 这里的mysqld是数据库服务对应的进程名字
提示:可通过如下地址查看生产高并发环境野蛮粗鲁杀死数据库导致故障企业案例:

第3章 MySQL数据库配置文件详解

3.1 配置标签分类

图片[3]|MySQL基本管理(四)|leon的博客

3.1.1 服务端标签

  • [server]:基本不使用
  • [mysqld]:常用
  • [mysqld_safe]:可以配置一些mysqld标签中无法配置的内容,很少使用

3.1.2 客户端标签

  • [client]:针对全局的配置
  • [mysql] | [mysqladmin] | [mysqldump]:只针对标签内命令生效的配置

3.2 mysqld常用配置信息

图片[4]|MySQL基本管理(四)|leon的博客

3.2.1 配置文件中组的简短示例:

[mysqld]
Basedir=…
Datadir=…
[mysql]
Socket=…

3.3 配置文件读取顺序

图片[5]|MySQL基本管理(四)|leon的博客

假设4个配置文件都存在,同时使用–defaults-extra-file指定了参数文件,如果这时有一个 “参数变量”在5个配置文件中都出现了,那么后面的配置文件中的参数变量值会覆盖前面配置文件中的参数变量值,就是说会使用~/.my.cnf中设置的值。

提示:如果使用./bin/mysqld_safe 守护进程启动mysql数据库时,使用了 –defaults-file=<配置文件的绝对路径>参数,这时只会使用这个参数指定的配置文件。

第4章 多实例简介及配置

4.1 什么是MySQL多实例

简单的说,就是在一台机器上开启多个不同的服务端口(如:3306,3307),运行多个MySQL服务进程,这些服务进程通过不同的socket监听不同的服务端口来提供各自的服务.

这些MySQL多实例共用一套MySQL安装程序,使用不同(也可以相同)的my.cnf配置文件、启动程序,数据文件。在提供服务时,多实例MySQL在逻辑上看来是各自独立的,多个实例的自身是根据配置文件对应的设定值,来取得服务器的相关硬件资源多少。

作个比喻,MySQL多实例相当于房子的多个卧室一样,每个实例可以看作一个卧室,整个服务器就是一套房子,服务器的硬件资源(cpu/mem/disk )可以看作房子的卫生间、厨房、厅一样,是房子的公用资源,北漂蜗居在一起,肯定要共用上述资源.这样想我们就应该明白了。

4.1.1 MySQL多实例示意图

图片[6]|MySQL基本管理(四)|leon的博客

多实例补充:其实很多服务都可以有多实例,例如nginx、Apache、Haproxy、Redis、Memcache等都可以配置多实例,这在门户网站使用的很广泛。

4.2 MySQL多实例的作用与问题

4.2.1 MySQL多实例的作用

  • 有效利用服务器资源

当单个服务器资源有剩余时,可以充分利用剩余的资源提供更多的服务。

  • 节约服务器资源

当公司资金紧张,但是数据库又需要各自尽量独立提供服务,而且,需要主从同步等技术时,多实例就再好不过了。

  • 资源互相抢占问题

当某个服务实例并发很高或者有慢查询时,整个实例会消耗更多的内存、CPU.磁盘I0资源,导致服务器上的其它的实例提供服务的质量下降.这就相当于大家住在一个房子的不同卧室一样,早晨起来上班,都要刷牙、洗脸等,这样卫生间就会长期占用,其它人就要等待一样的道理。不同实例获取的资源是相对独立的,无法像虚拟化一样完全隔离。

4.3 MySQL多实例的生产应用场景

4.3.1 资金紧张型公司的选择

若公司资金紧张,公司业务访问量又不太大,但又希望不同的业务的数据库服务各自尽量独立地提供服务而互相不受影响,同时还需要主从复制等技术提供备份或读写分离服务,那么多实例就再好不过了。比如:可以通过3台服务器部署9~15个实例,交叉做主从复制、数据备份及读写分离,这样就可达到9~15台服务器每个只装一个数据库才有的效果。这里要强调的是所谓的尽量独立是相对的。

4.3.2 并发访问不是特别大的业务

当公司业务访问量不太大的时候,服务器的资源基本都是浪费的,这时就很适合多实例的应用,如果对SQL语句的优化做的比较好,MySQL多实例会是一个很值得使用的技术,即使并发不是很大,合理分配好系统资源以及搭配好服务,也不会有太大问题。

4.3.3 门户网站应用MySQL多实例场景

门户网站通常都会使用多实例,因为配置硬件好的服务器,可节省IDC机柜空间,同时跑多实例也会减少硬件资源跑不满的浪费。比如百度公司的很多数据库都是多实例,不过一般是从库多实例,例如某部门中使用的IBM服务器为48核CPU,内存96GB,一台服务器跑3~4个实例,此外sina网也是用的多实例,内存48GB左右。

说明:sina网的数据库单机1~4个数据库实例的居多。其中又数1~2个的最多,因为大业务占用的机器比较多。服务器是DELL R510的居多,CPUE521048GB内存,磁盘12*300G SAS,做RAID10,此为门户网站的服务器配置参考。

另外,sina网站安装数据库时,一般采用编译安装的方式,并且会在进行优化之后做成rpm包,以便统一使用。

4.4 MySQL多实例常见的配置方案

4.4.1 单一配置文件、单一启动程序多实例部署方案

下面是MySQL官方文档提到的单一配置文件、单一启动程序多实例部署方案。

my.cnf配置文件示例(mysql手册里提到的方法)如下:

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin= /usr/bin/mysqladmin
user = mysql

[mysqld1]
socket = /var/lib/mysql/mysql.sock
port =3306
pid-file =/var/lib/mysql/mysql.pid
datadir =/var/lib/mysql/
user = mysql

[mysqld2]
socket = /mnt/data/db1/mysql.sock
port =3302
pid-file =/mnt/data/db1/mysql.pid
datadir =/mnt/data/db1/
user = mysql
skip-name-resolve
server-id=10
default-storage-engine=innodb
innodb_buffer_pool_size=512M
innodb_additional_mem_pool=10M
default_character_set=utf8
character_set_server=utf8
#read-only
relay-log-space-limit=3G
expire_logs_day=20

启动命令:

mysqld_multi --config-file=/data/mysql/my_multi.cnf start 1,2
说明:该方案缺点是耦合度太高,一个配置文件不好管理,开发和运维的统一原则就是降低耦合度,因此不推荐此方案。

4.4.2 多配置文件、多启动程序部署方案

多配置文件、多启动程序部署方案,是推荐的多实例方案。以下为配置实例。

4.4.2.1 MySQL双实例目录信息

[root@host /]# tree /data.
/data
|--3306
| |--data # 3306实例的数据文件
| |--my.cnf # 3306实例的配置文件
| --mysql # 实例的启动文件
|--3307
|--data # 3306实例的数据文件
|--my.cnf # 3306实例的配置文件
--mysql # 实例的启动文件

4 directories, 4 files
说明:这里的配置文件my.cnf、启动程序mysql都是独立的文件,数据文件data目录也是独立的。

4.5 配置多实例MySQL数据库

4.5.1 系统环境

[root@db02 ~]# cat /etc/redhat-release
CentOS release 6.9 (Final)
[root@db02 ~]# uname -r
2.6.32-696.el6.x86_64

4.5.2 二进制安装MySQL

安装方法参照《MySQL数据库多种安装方法介绍》

4.5.3 创建多实例的数据文件目录和日志文件

在企业中,通常以/data目录作为MySQL多实例总的根目录,然后规划不同的数字(即MySQL实例端口号)作为/data下面的二级目录,不同的二级目录对应的数字就作为MySQL实例的端口号,以区别不同的实例,数字对应的二级目录下包含mysql的数据文件,配置文件以及启动文件等。

配置3306、3307两个实例的目录如下:

[root@centos7 ~]# mkdir -p /data/{3306,3307}/data
[root@db02 ~]# touch /data/3306/mysql_3306.err
[root@db02 ~]# touch /data/3307/mysql_3307.err
提示:在生产环境中,一般为3~4个实例为佳。

4.5.4 创建MySQL多实例的配置文件

MySQL数据库默认为用户提供了一个配置文件模板:

[root@db02 ~]# ll /usr/local/mysql/support-files/*.cnf
-rw-r--r-- 1 mysql mysql 1126 Nov 13 11:25 /usr/local/mysql/support-files/my-default.cnf

[root@db02 ~]# vim /usr/local/mysql/support-files/my-default.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

上面是单实例的默认配置文件模板,如果配置多实例和单实例会有不同,为了让MySQL多实例之间彼此独立,因此要为每一个实例建立一个my.cnf配置文件和一个启动文件mysql,让它们分别对应自己的数据文件目录data。

4.5.4.1 3306实例配置文件

[root@db02 ~]# vim /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
log-bin = /data/3306/mysql-bin
server-id = 6

[mysqld_safe]
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid
4.5.4.2 3307实例配置文件

[root@db02 ~]# vim /data/3307/my.cnf
[client]
port = 3307
socket = /data/3307/mysql.sock

[mysqld]
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3307/data
log-bin = /data/3307/mysql-bin
server-id = 7

[mysqld_safe]
log-error=/data/3307/mysql_3307.err
pid-file=/data/3307/mysqld.pid

4.5.4.3 配置文件对比情况

[root@db02 ~]# diff /data/3306/my.cnf /data/3307/my.cnf
2,3c2,3
< port = 3306
< socket = /data/3306/mysql.sock
---
> port = 3307
> socket = /data/3307/mysql.sock
7,8c7,8
< port = 3306
< socket = /data/3306/mysql.sock
---
> port = 3307
> socket = /data/3307/mysql.sock
10,12c10,12
< datadir = /data/3306/data
< log-bin = /data/3306/mysql-bin
< server-id = 6
---
> datadir = /data/3307/data
> log-bin = /data/3307/mysql-bin
> server-id = 7
15,16c15,16
< log-error=/data/3306/oldboy_3306.err
< pid-file=/data/3306/mysqld.pid
---
> log-error=/data/3307/oldboy_3307.err
> pid-file=/data/3307/mysqld.pid
\ No newline at end of file

图片[7]|MySQL基本管理(四)|leon的博客

提示:有关配置文件的参数说明可以根据my-innodb-heavy-4G.cnf或查找相关资料,进行完整注释。

4.5.5 创建MySQL多实例的启动文件

4.5.5.1 3306实例的启动文件

[root@db02 ~]# vim /data/3306/mysql
#!/bin/sh
#init
port=3306
mysql_user="root"
CmdPath="/usr/local/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
mysqld_pid_file_path=/data/3306/3306.pid
start(){
    if [ ! -e "$mysql_sock" ];then
        printf "Starting MySQL...\n"
        /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysql
        d_pid_file_path 2>&1 > /dev/null &
        sleep 3
    else
        printf "MySQL is running...\n"
        exit 1
   fi
}
stop(){
    if [ ! -e "$mysql_sock" ];then
        printf "MySQL is stopped...\n"
        exit 1
    else
        printf "Stoping MySQL...\n"
        mysqld_pid=`cat "$mysqld_pid_file_path"`
        if (kill -0 $mysqld_pid 2>/dev/null)
        then
            kill $mysqld_pid
            sleep 2
        fi
    fi
}

restart(){
    printf "Restarting MySQL...\n"
    stop
    sleep 2
    start
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
     restart)
        restart
        ;;
     *)
        printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

4.5.5.2 3307实例的启动文件

[root@db02 ~]# vim /data/3307/mysql
#!/bin/sh
#init
port=3307
mysql_user="root"
CmdPath="/usr/local/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
mysqld_pid_file_path=/data/3307/3307.pid
start(){
    if [ ! -e "$mysql_sock" ];then
        printf "Starting MySQL...\n"
        /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysql
        d_pid_file_path 2>&1 > /dev/null &
        sleep 3
    else
        printf "MySQL is running...\n"
        exit 1
    fi
}
stop(){
    if [ ! -e "$mysql_sock" ];then
        printf "MySQL is stopped...\n"
        exit 1
    else
        printf "Stoping MySQL...\n"
        mysqld_pid=`cat "$mysqld_pid_file_path"`
        if (kill -0 $mysqld_pid 2>/dev/null)
        then
            kill $mysqld_pid
            sleep 2
        fi
    fi
}

restart(){
    printf "Restarting MySQL...\n"
    stop
    sleep 2
    start
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    restart)
        restart
        ;;
    *)
        printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

4.5.5.3 启动文件对比情况

[root@db02 ~]# diff /data/3306/mysql /data/3307/mysql
10c10
< port=3306
---
> port=3307
14c14
< mysqld_pid_file_path=/data/3306/3306.pid
---
> mysqld_pid_file_path=/data/3307/3307.pid

图片[8]|MySQL基本管理(四)|leon的博客

提示:在多实例启动文件中,启动MySQL不同实例服务所执行的命令实质是有区别的。

4.5.6 启动多实例的命令实质

  • 3306实例启动命令:
mysql_safe --efaults-file=/data/3306/my.cnf &> /dev/null &
  • 3307实例启动命令:
mysql_safe --efaults-file=/data/3307/my.cnf &> /dev/null &

4.5.7 关闭多实例的命令实质

  • 3306实例关闭命令:
mysqladmin -u root -p123456 -S /data/3306/mysql.sock shutdown
  • 3307实例关闭命令:
mysqladmin -u root -p123456 -S /data/3307/mysql.sock shutdown

4.5.8 配置MySQL多实例的文件权限

4.5.8.1 设置/data目录权限

[root@db02 ~]# chown -R mysql.mysql /data
[root@db02 ~]# ll /data
total 8
drwxr-xr-x 2 mysql mysql 4096 Nov 14 16:06 3306
drwxr-xr-x 2 mysql mysql 4096 Nov 14 16:06 3307

4.5.8.2 设置MySQL启动文件执行权限

[root@db02 ~]# chmod 700 /data/3306/mysql
[root@db02 ~]# chmod 700 /data/3307/mysql

4.5.8.3 查看权限设置结果

[root@db02 ~]# ll /data/3306/mysql
-rwx------ 1 mysql mysql 1048 Nov 14 16:05 /data/3306/mysql
[root@db02 ~]# ll /data/3307/mysql
-rwx------ 1 mysql mysql 1048 Nov 14 16:05 /data/3307/mysql

4.5.9 加入MySQL相关命令到全局路径

4.5.9.1 方法一:在PATH变量前增加/usr/local/mysql/bin路径,并追加到/etc/profile文件中:

echo "PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
source /etc/profile

4.5.9.2 方法二:直接使用软链接将文件链接到/usr/local/sbin目录下:

ln -s /usr/local/mysql/bin/* /usr/local/sbin/

4.5.10 初始化MySQL多实例的数据库文件

4.5.10.1 初始化MySQL数据库

[root@db02 ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data/ --user=mysql
[root@db02 ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data/ --user=mysql
提示:–basedir=/usr/local/mysqlMySQL的安装路径,–datadir为不同的实例数据目录。

4.5.10.2 初始化数据库的原理及结果说明

初始化数据库的实质就是创建基础的数据库系统的库文件,例如:生成MySQL库表等。初始化数据库后查看对应实例的数据目录,可以看到多了如下文件:

[root@db02 ~]# ll /data/3306/data/
total 110604
-rw-rw---- 1 mysql mysql 12582912 Nov 14 16:16 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Nov 14 16:16 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Nov 14 16:16 ib_logfile1
drwx------ 2 mysql mysql 4096 Nov 14 16:16 mysql
drwx------ 2 mysql mysql 4096 Nov 14 16:16 performance_schema
drwx------ 2 mysql mysql 4096 Nov 14 16:15 test

4.5.11 启动MySQL多实例数据库

4.5.11.1 启动多实例

[root@db02 ~]# /data/3306/mysql start
Starting MySQL...
[root@db02 ~]# /data/3307/mysql start
Starting MySQL...

4.5.11.2 查看启动状态

[root@db02 ~]# netstat -lntup | grep mysql
tcp 0 0 :::3306 :::* LISTEN 44060/mysqld
tcp 0 0 :::3307 :::* LISTEN 44268/mysqld

 

温馨提示:本文最后更新于2022-12-20 20:57:52,已超过483天没有更新。某些文章具有时效性,若文章内容或图片资源有错误或已失效,请联系站长。谢谢!
转载请注明本文链接:https://blog.leonshadow.cn/763482/562.html
© 版权声明
THE END
喜欢就支持一下吧
点赞0 分享