在Windows中运行多个MySQL实例

在Windows中可以从命令行为每个实例使用合适的操作参数来手动启动多个MySQL实例或者通过以Windows服务方式来安装多个服务器来运行。

1.在windows中MySQL的base目录中创建两个新实例的数据目录data3307,data3308

2.为每个新实例设置配置文件来指定相关选项
my3307.cnf文件内容如下:

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[mysqld]
# 设置3307端口
port = 3307
# 设置mysql的安装目录
basedir=D:\mysql-5.7.25-win32
# 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错
datadir=D:\mysql-5.7.25-win32\data3307
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3307.err
pid-file=D:\mysql-5.7.25-win32\mysqld3307.pid
socket =D:\mysql-5.7.25-win32\mysql3307.sock

my3308.cnf文件内容如下:

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[mysqld]
# 设置3308端口
port = 3308
# 设置mysql的安装目录
basedir=D:\mysql-5.7.25-win32
# 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错
datadir=D:\mysql-5.7.25-win32\data3308
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

3.初始化新数据库

D:\mysql-5.7.25-win32\bin>mysqld  --defaults-file=D:\mysql-5.7.25-win32\my3307.cnf --initialize --basedir=D:\mysql-5.7.25-win32 --datadir=D:\mysql-5.7.25-win32\data3307

D:\mysql-5.7.25-win32\bin>mysqld  --defaults-file=D:\mysql-5.7.25-win32\my3308.cnf --initialize --basedir=D:\mysql-5.7.25-win32 --datadir=D:\mysql-5.7.25-win32\data3308

4.启动数据库

D:\mysql-5.7.25-win32\bin>mysqld  --defaults-file=D:\mysql-5.7.25-win32\my3307.cnf


D:\mysql-5.7.25-win32\bin>mysqld  --defaults-file=D:\mysql-5.7.25-win32\my3308.cnf

每个服务器都在前台启动(在服务器稍后退出之前不会出现新的提示),所以您需要在单独的控制台窗口中发出这两个命令。

5.登录数据库修改密码

C:\Users\Administrator>mysql --port=3307 --host=127.0.0.1 --user=root --password=U0U?KinrdWHb
mysql: [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 6
Server version: 5.7.25

Copyright (c) 2000, 2019, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\Users\Administrator>mysql --port=3307 --host=127.0.0.1 --user=root --password=123456
mysql: [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 7
Server version: 5.7.25 Source distribution

Copyright (c) 2000, 2019, 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> exit


C:\Users\Administrator>mysql --port=3308 --host=127.0.0.1 --user=root
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.25

Copyright (c) 2000, 2019, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye


C:\Users\Administrator>mysql --port=3308 --host=127.0.0.1 --user=root --password=123456
mysql: [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 9
Server version: 5.7.25 Source distribution

Copyright (c) 2000, 2019, 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>

6.为了关闭数据库服务器,使用合适的端口号连接到每个实例执行下面的命令:

C:\Users\Administrator>mysqladmin --port=3307 --host=127.0.0.1 --user=root --password=123456 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

C:\Users\Administrator>mysqladmin --port=3308 --host=127.0.0.1 --user=root --password=123456 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

上面的配置允许客户端通过TCP/IP来进行连接。如果你的Windows版本支持命名管道并且你也想使用命名管道来连接,指定启用命名管道选项和指定它的名称。每个实例支持命名管道连接必须使用一个唯一的管道名。例如:
my3307.cnf文件内容如下:

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[mysqld]
# 设置3307端口
port = 3307
# 设置mysql的安装目录
basedir=D:\mysql-5.7.25-win32
# 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错
datadir=D:\mysql-5.7.25-win32\data3307
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3307.err
pid-file=D:\mysql-5.7.25-win32\mysqld3307.pid
enable-named-pipe
socket =D:\mysql-5.7.25-win32\mysql3307.sock

my3308.cnf文件内容如下:

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[mysqld]
# 设置3308端口
port = 3308
# 设置mysql的安装目录
basedir=D:\mysql-5.7.25-win32
# 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错
datadir=D:\mysql-5.7.25-win32\data3308
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

然后启动MySQL实例。想让客户端通过共享内存连接的过程与上面的过程类似。对每个连接使用–shared-memory选项并使用–shared-memory-base-name选项来为每个实例指定唯一的共享内存名字。

在Windows中作为Windows服务来启动多个MySQL实例
在Windows中,一个MySQL服务器可以以Windows服务来运行。为了设置多个MySQL服务,你必须确保每个实例使用不同的服务名另外其它参数每个实例必须都有唯一值。为了将MySQL服务器注册成为Windows服务,使用mysqld –install或mysqld –install-manual选项。

基于上面的信息,有几种方式来设置多个Windows服务。在注册Windows服务之前需要关闭并删除任何已经存在的Windows服务。

方法1
在一个标准的选项文件中指定所有服务选项。这样就需要为每个MySQL服务指定不同的服务名。假设端口为3307的MySQL实例的服务名为mysqld1,端口为3308的MySQL实例的服务名为mysqld2。那么D:\mysql-5.7.25-win32\my.ini设置如下:

[mysqld1]
# 设置3307端口
port = 3307
# 设置mysql的安装目录
basedir=D:\mysql-5.7.25-win32
# 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错
datadir=D:\mysql-5.7.25-win32\data3307
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3307.err
pid-file=D:\mysql-5.7.25-win32\mysqld3307.pid
socket =D:\mysql-5.7.25-win32\mysql3307.sock


[mysqld2]
# 设置3308端口
port = 3308
# 设置mysql的安装目录
basedir=D:\mysql-5.7.25-win32
# 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错
datadir=D:\mysql-5.7.25-win32\data3308
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

注册服务,使用完整服务路径名来确保Windows为每个服务注册正确的可执行程序:

D:\mysql-5.7.25-win32\bin>mysqld --install mysqld1
Service successfully installed.

D:\mysql-5.7.25-win32\bin>mysqld --install mysqld2
Service successfully installed.

为了启动MySQL服务,使用服务管理器或者使用net start servicename:

C:\Users\Administrator>net start mysqld1
mysqld1 服务正在启动 .
mysqld1 服务已经启动成功。


C:\Users\Administrator>net start mysqld2
mysqld2 服务正在启动 .
mysqld2 服务已经启动成功。

为了停止MySQL服务,使用服务管理器或者使用net stop servicename:
C:\Users\Administrator>net stop mysqld1
mysqld1 服务正在停止.
mysqld1 服务已成功停止。


C:\Users\Administrator>net stop mysqld2
mysqld2 服务正在停止.
mysqld2 服务已成功停止。

删除服务

D:\mysql-5.7.25-win32\bin>mysqld --remove mysqld1
Service successfully removed.

D:\mysql-5.7.25-win32\bin>mysqld --remove mysqld2
Service successfully removed.

方法2
为每个MySQL服务使用单独的文件来指定选项并且在注册服务时使用–defaults-file选项来告诉每个服务器所要使用的选项文件。在这种情况下,每个文件都将使用[mysqld]选项组。
my3307.cnf文件内容如下:

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[mysqld]
# 设置3307端口
port = 3307
# 设置mysql的安装目录
basedir=D:\mysql-5.7.25-win32
# 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错
datadir=D:\mysql-5.7.25-win32\data3307
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3307.err
pid-file=D:\mysql-5.7.25-win32\mysqld3307.pid
enable-named-pipe
socket =D:\mysql-5.7.25-win32\mysql3307.sock

my3308.cnf文件内容如下:

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[mysqld]
# 设置3308端口
port = 3308
# 设置mysql的安装目录
basedir=D:\mysql-5.7.25-win32
# 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错
datadir=D:\mysql-5.7.25-win32\data3308
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

将每个MySQL实例注册成Windows服务

D:\mysql-5.7.25-win32\bin>mysqld --install mysqld1 --defaults-file=D:\mysql-5.7.25-win32\my3307.cnf
Service successfully installed.


D:\mysql-5.7.25-win32\bin>mysqld --install mysqld2 --defaults-file=D:\mysql-5.7.25-win32\my3308.cnf
Service successfully installed.

启动服务

C:\Users\Administrator>net start mysqld1
mysqld1 服务正在启动 .
mysqld1 服务已经启动成功。


C:\Users\Administrator>net start mysqld2
mysqld2 服务正在启动 .
mysqld2 服务已经启动成功。

Linux中运行多个MySQL实例

这里将介绍使用mysqld_safe来启动多个MySQL实例。对于使用一个RPM发布版本安装的MySQL来说,在多数Linux平台中MySQL服务的启动和关闭是由systemd来管理的。在这些平台上没有安装msyqld_safe因为不需要。

一种在Linux上运行多个MySQL实例的方法是用不同的默认TCP/IP端口和Unix套接字文件编译不同的服务器,以便每个服务器都监听不同的网络接口。为了在每个不同的基本目录中进行编译,还会自动生成一个单独的、编译后的数据目录、日志文件和每个服务器的PID文件位置。

假设一个现有的5.6服务器被配置成TCP/IP端口为3306并且Unix socket文件为/tmp/mysql.sock。为了配置一个新的5.7.21服务器使用不同的操作参数,使用CMake命令进行编译:
shell> cmake . -DMYSQL_TCP_PORT=port_number \
-DMYSQL_UNIX_ADDR=file_name \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.21

这里,port_number和file_name必须与缺省的TCP/CP端口号和Unix socket文件路径名不同,并且CMAKE_INSTALL_PREFIX值指定的安装目录不能是现有MySQL安装目录相同的目录。

如果有一个MySQL服务器正在监听一个指定的端口号,你可以使用下面的命令来找出多个重要配置变量所使用的操作参数,包括base目录和Unix socket文件名:
shell> mysqladmin –host=host_name –port=port_number variables

通过命令所显示的信息,你也可以知道当配置另一个服务器时什么选项值将不能使用。

如果指定localhost作为主机名,mysqladmin缺省会使用一个Unix socket文件来进行连接而不是使用TCP/IP。为了显性指定连接协议,使用–protocol={TCP|SOCKET|PIPE|MEMORY}选项。

如果只是使用不同的Unix socket文件和TCP/IP端口来启动一个MySQL实例那么不需要编译一个新的MySQL服务器。可以使用相同的服务器二进制文件并且在运行时为每个MySQL实例使用不同的参数。一种方式是使用命令行选项:
shell>mysqld_safe –socket=file_name –port=port_number

为了启动第二个MySQL实例,给mysqld_safe提供不同的–socket和–port选项值并传一个–datadir=dir_name选项因此这个实例将使用不同的数据目录。

另一种方法是将每个MySQL实例的选项放入不同的选项文件,然后启动每个实例时使用–defaults-file选项来指定合适选项文件的路径。

shell> mysqld_safe --defaults-file=/usr/local/mysql/my.cnf
shell> mysqld_safe --defaults-file=/usr/local/mysql/my.cnf2

另一种方法来完成相同的功能是使用环境变量来设置Unix socket文件名和TCP/IP端口号:

shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock
shell> MYSQL_TCP_PORT=3307
shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT
shell> mysql_install_db --user=mysql
shell> mysqld_safe --datadir=/path/to/datadir &

这是一种快速启动第二个实例进行测试的方法。它的好处是设置的环境变量可以应用到从相同shell执行调用的任何客户端程序。因此对于这些客户端连接会自动指向第二实例。

另一种方法是在Linux中使用mysqld_multi脚本来管理多个MySQL实例。
下面来创建三个实例(实例的端口号为3307,3308,3309)
创建存储这三个实例的数据库文件目录

-bash-4.2$ mkdir mysql3307
-bash-4.2$ mkdir mysql3308
-bash-4.2$ mkdir mysql3309
-bash-4.2$ ls -lrt
总用量 4
drwxrwxrwx. 5 mysql mysql 4096 8月  22 21:46 mysql
drwxr-xr-x. 2 mysql mysql    6 9月   4 10:26 mysql3307
drwxr-xr-x. 2 mysql mysql    6 9月   4 10:26 mysql3308
drwxr-xr-x. 2 mysql mysql    6 9月   4 10:26 mysql3309

为每个实例创建一个选项文件(my3307.cnf,my3308.cnf,my3309.cnf)
my3307.cnf文件内容如下:

-bash-4.2$ cat my3307.cnf
[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql3307
bind-address=0.0.0.0
user=mysql
port=3307
log-error=/mysqldata/mysql3307/mysql.err
pid-file=/mysqldata/mysql3307/mysqld.pid
socket = /mysqldata/mysql3307/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true

my3308.cnf文件内容如下:

-bash-4.2$ cat my3308.cnf
[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql3308
bind-address=0.0.0.0
user=mysql
port=3308
log-error=/mysqldata/mysql3308/mysql.err
pid-file=/mysqldata/mysql3308/mysqld.pid
socket = /mysqldata/mysql3308/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true

my3309.cnf文件内容如下:

-bash-4.2$ cat my3309.cnf
[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql3309
bind-address=0.0.0.0
user=mysql
port=3309
log-error=/mysqldata/mysql3309/mysql.err
pid-file=/mysqldata/mysql3309/mysqld.pid
socket = /mysqldata/mysql3309/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true

初始化数据库

-bash-4.2$ mysqld  --defaults-file=/mysqlsoft/mysql/my3307.cnf --initialize --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql3307 --user=mysql
-bash-4.2$ mysqld  --defaults-file=/mysqlsoft/mysql/my3308.cnf --initialize --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql3308 --user=mysql
-bash-4.2$ mysqld  --defaults-file=/mysqlsoft/mysql/my3309.cnf --initialize --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql3309 --user=mysql

启动数据库

-bash-4.2$ mysqld_safe --defaults-file=/mysqlsoft/mysql/my3307.cnf &
[1] 10359
-bash-4.2$ 2019-09-05T09:39:35.467416Z mysqld_safe Logging to '/mysqldata/mysql3307/mysql.err'.
2019-09-05T09:39:35.545107Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3307


-bash-4.2$ mysqld_safe --defaults-file=/mysqlsoft/mysql/my3308.cnf &
[1] 10624
-bash-4.2$ 2019-09-05T09:42:28.457387Z mysqld_safe Logging to '/mysqldata/mysql3308/mysql.err'.
2019-09-05T09:42:28.532350Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3308


-bash-4.2$ mysqld_safe --defaults-file=/mysqlsoft/mysql/my3309.cnf &
[1] 10889
-bash-4.2$ 2019-09-05T09:45:03.772185Z mysqld_safe Logging to '/mysqldata/mysql3309/mysql.err'.
2019-09-05T09:45:03.847584Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3309

修改每个MySQL实例的用户密码并关闭实例

-bash-4.2$ mysql --port=3307 --host=127.0.0.1 --user=root --password=nCohVRg-=7LP 
mysql: [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 3
Server version: 5.7.26

Copyright (c) 2000, 2019, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
-bash-4.2$ mysqladmin --port=3307 --host=127.0.0.1 --user=root --password=123456 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.


-bash-4.2$ mysql --port=3308 --host=127.0.0.1 --user=root --password=g*tV/I%#s6j#
mysql: [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 2
Server version: 5.7.26

Copyright (c) 2000, 2019, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
-bash-4.2$ mysqladmin --port=3308 --host=127.0.0.1 --user=root --password=123456 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

-bash-4.2$ mysql --port=3309 --host=127.0.0.1 --user=root --password=eIsXkThGK5*4
mysql: [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 2
Server version: 5.7.26

Copyright (c) 2000, 2019, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
-bash-4.2$ mysqladmin --port=3309 --host=127.0.0.1 --user=root --password=123456 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

使用mysqld_multi来管理多个MySQL实例
修改配置文件my.cnf增加以下内容(增加三个实例的选项参数)

[mysqld_multi]
mysqld=/mysqlsoft/mysql/bin/mysqld_safe
mysqladmin =/mysqlsoft/mysql/bin/mysqladmin
log =/mysqlsoft/mysql/mysqld_multi.log

[mysqld1]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql3307
bind-address=0.0.0.0
user=mysql
port=3307
log-error=/mysqldata/mysql3307/mysql.err
pid-file=/mysqldata/mysql3307/mysqld.pid
socket = /mysqldata/mysql3307/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true


[mysqld2]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql3308
bind-address=0.0.0.0
user=mysql
port=3308
log-error=/mysqldata/mysql3308/mysql.err
pid-file=/mysqldata/mysql3308/mysqld.pid
socket = /mysqldata/mysql3308/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true


[mysqld3]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql3309
bind-address=0.0.0.0
user=mysql
port=3309
log-error=/mysqldata/mysql3309/mysql.err
pid-file=/mysqldata/mysql3309/mysqld.pid
socket = /mysqldata/mysql3309/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true

使用mysqld_multi来启动实例

[mysql@localhost ~]$ mysqld_multi start 1

报错了,从日志可以看到启动实例时调用了两次,我这里的测试环境在创建多个实例之前创建了一个mysqld实例(也许存在影响)

[mysql@localhost mysql]$ tail -f mysqld_multi.log 
Starting MySQL servers

2019-09-06T05:40:07.558168Z mysqld_safe Logging to '/mysqldata/mysql3307/mysql.err'.
2019-09-06T05:40:07.563783Z mysqld_safe Logging to '/mysqldata/mysql3307/mysql.err'.
2019-09-06T05:40:07.618543Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3307
2019-09-06T05:40:07.623821Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3307

root@localhost mysql3307]# tail -f mysql.err
2019-09-06T06:24:30.964335Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2019-09-06T06:24:31.964485Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2019-09-06T06:24:31.964573Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2019-09-06T06:24:32.964723Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2019-09-06T06:24:32.964812Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2019-09-06T06:24:33.964935Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2019-09-06T06:24:33.964987Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2019-09-06T06:24:34.965105Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2019-09-06T06:24:34.965178Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2019-09-06T06:24:35.965292Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2019-09-06T06:24:35.965340Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2019-09-06T06:24:36.965460Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2019-09-06T06:24:36.965509Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2019-09-06T06:24:37.965632Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

所以这里在使用mysqld_multi来启动实例时使用–defaults-file

[mysql@localhost bin]$ mysqld_multi --defaults-file=/mysqlsoft/mysql/my.cnf start 1
[mysql@localhost bin]$ mysqld_multi --defaults-file=/mysqlsoft/mysql/my.cnf start 2,3

[mysql@localhost mysql]$ tail -f mysqld_multi.log 
Starting MySQL servers

2019-09-06T06:49:50.003877Z mysqld_safe Logging to '/mysqldata/mysql3307/mysql.err'.
2019-09-06T06:49:50.096954Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3307
mysqld_multi log file version 2.16; run: 五 9月  6 14:59:33 2019

Starting MySQL servers

2019-09-06T06:59:33.644263Z mysqld_safe Logging to '/mysqldata/mysql3308/mysql.err'.
2019-09-06T06:59:33.650226Z mysqld_safe Logging to '/mysqldata/mysql3309/mysql.err'.
2019-09-06T06:59:33.704593Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3308
2019-09-06T06:59:33.710937Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3309

到此使用mysqld_multi来管理多个实例的操作就完成了。

MySQL Server Startup Script

MySQL Server Startup Script
在Unix和类似于Unix的系统中的MySQL发布版本包含一个名叫mysql.server的脚本,它将调用mysqld_safe来启动MySQL服务器。它也可以用于像Linux和Solaris系统上使用System V-style来运行指令来启动和停止系统服务。也可以被macOS启动项目来启动MySQL。

mysql.server是MySQL源码树中的脚本名称。其安装后的名称可能不一样,例如,mysqld或mysql。下面将会介绍将mysql.server脚本调整成适合于你系统的名称。注意对于某些Linux平台,使用RPM或Debian包安装的MySQL包含用来管理MySQL服务器启动和关闭的systemd的支持。在这些平台上,mysql.server和mysqld_safe不需要安装因为不需要它们。

为了使用mysql.server来手动启动或停止MySQL服务,可以从命令调用它并使用start或stop参数:

[root@localhost support-files]# ./mysql.server stop
Shutting down MySQL.. SUCCESS!
[root@localhost support-files]# ./mysql.server start
Starting MySQL.. SUCCESS!

相关日志信息如下:

2019-08-22T13:29:13.429419Z 0 [Note] Giving 0 client threads a chance to die gracefully
2019-08-22T13:29:13.429508Z 0 [Note] Shutting down slave threads
2019-08-22T13:29:13.429535Z 0 [Note] Forcefully disconnecting 0 remaining clients
2019-08-22T13:29:13.429553Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2019-08-22T13:29:13.429630Z 0 [Note] Binlog end
2019-08-22T13:29:13.460102Z 0 [Note] Shutting down plugin 'ngram'
2019-08-22T13:29:13.460187Z 0 [Note] Shutting down plugin 'partition'
2019-08-22T13:29:13.460214Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2019-08-22T13:29:13.460235Z 0 [Note] Shutting down plugin 'ARCHIVE'
2019-08-22T13:29:13.460277Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2019-08-22T13:29:13.460405Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2019-08-22T13:29:13.460446Z 0 [Note] Shutting down plugin 'MyISAM'
2019-08-22T13:29:13.460553Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2019-08-22T13:29:13.460599Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2019-08-22T13:29:13.460619Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2019-08-22T13:29:13.460637Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2019-08-22T13:29:13.460681Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2019-08-22T13:29:13.460736Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2019-08-22T13:29:13.460776Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2019-08-22T13:29:13.460898Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2019-08-22T13:29:13.460920Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2019-08-22T13:29:13.460938Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2019-08-22T13:29:13.460956Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2019-08-22T13:29:13.460973Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2019-08-22T13:29:13.460991Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2019-08-22T13:29:13.461008Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2019-08-22T13:29:13.461026Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2019-08-22T13:29:13.461044Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2019-08-22T13:29:13.461089Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2019-08-22T13:29:13.461140Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2019-08-22T13:29:13.461161Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2019-08-22T13:29:13.461180Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2019-08-22T13:29:13.461220Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2019-08-22T13:29:13.461286Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2019-08-22T13:29:13.461306Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2019-08-22T13:29:13.461325Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2019-08-22T13:29:13.461343Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2019-08-22T13:29:13.461361Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2019-08-22T13:29:13.461378Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2019-08-22T13:29:13.461396Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2019-08-22T13:29:13.461414Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2019-08-22T13:29:13.461431Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2019-08-22T13:29:13.461450Z 0 [Note] Shutting down plugin 'InnoDB'
2019-08-22T13:29:13.461776Z 0 [Note] InnoDB: FTS optimize thread exiting.
2019-08-22T13:29:13.462092Z 0 [Note] InnoDB: Starting shutdown...
2019-08-22T13:29:13.562479Z 0 [Note] InnoDB: Dumping buffer pool(s) to /mysqldata/mysql/ib_buffer_pool
2019-08-22T13:29:13.563934Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 190822 21:29:13
2019-08-22T13:29:14.807857Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2530229
2019-08-22T13:29:14.810644Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2019-08-22T13:29:14.810690Z 0 [Note] Shutting down plugin 'MEMORY'
2019-08-22T13:29:14.810707Z 0 [Note] Shutting down plugin 'CSV'
2019-08-22T13:29:14.810721Z 0 [Note] Shutting down plugin 'sha256_password'
2019-08-22T13:29:14.810731Z 0 [Note] Shutting down plugin 'mysql_native_password'
2019-08-22T13:29:14.810961Z 0 [Note] Shutting down plugin 'binlog'
2019-08-22T13:29:14.811432Z 0 [Note] /mysqlsoft/mysql/bin/mysqld: Shutdown complete

2019-08-22T13:30:24.969246Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2019-08-22T13:30:24.969441Z 0 [Note] /mysqlsoft/mysql/bin/mysqld (mysqld 5.7.26) starting as process 32558 ...
2019-08-22T13:30:24.980591Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-08-22T13:30:24.980678Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-08-22T13:30:24.980740Z 0 [Note] InnoDB: Uses event mutexes
2019-08-22T13:30:24.980758Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-08-22T13:30:24.980779Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-08-22T13:30:24.981460Z 0 [Note] InnoDB: Number of pools: 1
2019-08-22T13:30:24.981799Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-08-22T13:30:24.985591Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-08-22T13:30:25.002296Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-08-22T13:30:25.007382Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-08-22T13:30:25.021394Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-08-22T13:30:25.063462Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-08-22T13:30:25.063830Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-08-22T13:30:25.758116Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-08-22T13:30:25.760396Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-08-22T13:30:25.760448Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-08-22T13:30:25.761681Z 0 [Note] InnoDB: Waiting for purge to start
2019-08-22T13:30:25.811963Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 2530229
2019-08-22T13:30:25.812330Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysqldata/mysql/ib_buffer_pool
2019-08-22T13:30:25.813642Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-08-22T13:30:25.819058Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190822 21:30:25
2019-08-22T13:30:25.824582Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2019-08-22T13:30:25.825236Z 0 [Warning] CA certificate ca.pem is self signed.
2019-08-22T13:30:25.827375Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2019-08-22T13:30:25.827463Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2019-08-22T13:30:25.829361Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2019-08-22T13:30:25.859593Z 0 [Note] Event Scheduler: Loaded 0 events
2019-08-22T13:30:25.859938Z 0 [Note] /mysqlsoft/mysql/bin/mysqld: ready for connections.
Version: '5.7.26'  socket: '/mysqlsoft/mysql/mysql.sock'  port: 3306  Source distribution

mysql.server进入到MySQL安装目录,然后调用mysqld_safe。为了让某些特定的用户能够运行服务,在全局/etc/my.cnf选项文件中为[mysqld]组增加一个合适的user选项,后面将会介绍(如果你将二进制版本的MySQL安装在非标准位置,那么可能必须要修改mysql.server脚本。修改它是为了在运行mysqld_safe之前进入到合适的目录中。如果这样做,当将来升级MySQL时你修改的mysql.server版本可能会被覆盖,创建一个你修改版本的副本可以进行重新安装)。

mysql.server stop通过发送一个信号给它来停止MySQL服务。也可以通过执行mysqladmin shutdown来手动关闭服务。

[root@localhost bin]# ./mysqladmin -uroot -p123456 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

日志信息如下:

2019-08-22T13:44:57.170282Z 0 [Note] Giving 0 client threads a chance to die gracefully
2019-08-22T13:44:57.170357Z 0 [Note] Shutting down slave threads
2019-08-22T13:44:57.170919Z 0 [Note] Forcefully disconnecting 0 remaining clients
2019-08-22T13:44:57.171016Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2019-08-22T13:44:57.171140Z 0 [Note] Binlog end
2019-08-22T13:44:57.172096Z 0 [Note] Shutting down plugin 'ngram'
2019-08-22T13:44:57.172142Z 0 [Note] Shutting down plugin 'partition'
2019-08-22T13:44:57.172203Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2019-08-22T13:44:57.172225Z 0 [Note] Shutting down plugin 'ARCHIVE'
2019-08-22T13:44:57.172259Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2019-08-22T13:44:57.172317Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2019-08-22T13:44:57.172348Z 0 [Note] Shutting down plugin 'MyISAM'
2019-08-22T13:44:57.172398Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2019-08-22T13:44:57.172428Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2019-08-22T13:44:57.172441Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2019-08-22T13:44:57.172452Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2019-08-22T13:44:57.172476Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2019-08-22T13:44:57.172506Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2019-08-22T13:44:57.172564Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2019-08-22T13:44:57.172579Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2019-08-22T13:44:57.172590Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2019-08-22T13:44:57.172601Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2019-08-22T13:44:57.172612Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2019-08-22T13:44:57.172623Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2019-08-22T13:44:57.172634Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2019-08-22T13:44:57.172644Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2019-08-22T13:44:57.172655Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2019-08-22T13:44:57.172666Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2019-08-22T13:44:57.172676Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2019-08-22T13:44:57.172687Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2019-08-22T13:44:57.172698Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2019-08-22T13:44:57.172708Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2019-08-22T13:44:57.172734Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2019-08-22T13:44:57.172746Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2019-08-22T13:44:57.172769Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2019-08-22T13:44:57.172802Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2019-08-22T13:44:57.172814Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2019-08-22T13:44:57.172838Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2019-08-22T13:44:57.172849Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2019-08-22T13:44:57.172872Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2019-08-22T13:44:57.172884Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2019-08-22T13:44:57.172895Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2019-08-22T13:44:57.172906Z 0 [Note] Shutting down plugin 'InnoDB'
2019-08-22T13:44:57.173010Z 0 [Note] InnoDB: FTS optimize thread exiting.
2019-08-22T13:44:57.173195Z 0 [Note] InnoDB: Starting shutdown...
2019-08-22T13:44:57.274546Z 0 [Note] InnoDB: Dumping buffer pool(s) to /mysqldata/mysql/ib_buffer_pool
2019-08-22T13:44:57.275296Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 190822 21:44:57
2019-08-22T13:44:58.528547Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2530257
2019-08-22T13:44:58.531650Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2019-08-22T13:44:58.531698Z 0 [Note] Shutting down plugin 'MEMORY'
2019-08-22T13:44:58.531736Z 0 [Note] Shutting down plugin 'CSV'
2019-08-22T13:44:58.531752Z 0 [Note] Shutting down plugin 'sha256_password'
2019-08-22T13:44:58.531764Z 0 [Note] Shutting down plugin 'mysql_native_password'
2019-08-22T13:44:58.531967Z 0 [Note] Shutting down plugin 'binlog'
2019-08-22T13:44:58.532460Z 0 [Note] /mysqlsoft/mysql/bin/mysqld: Shutdown complete

为了在服务器上自动启动和停止MySQL服务,你必须在你的/etc/rc*文件中合适的地方添加启动和停止命令:
.如果使用Linux服务RPM包(MySQL-server-VERSION.rpm)或一个原生的Linux包安装,那么mysql.server脚本可能以mysqld或mysql的名称安装到/etc/init.d目录中。

.如果使用源码或二进制版本来安装MySQL时不能自动安装mysql.server,可以手动安装这个脚本。它可以在MySQL安装目录或MySQL源码树下的support-files目录中找到。将这个脚本复制到/etc/init.d目录中并命名为mysql并且修改权限让其它可以执行:

[root@cs2 ~]# cp /mysqlsoft/mysql/support-files/mysql.server  /etc/init.d/mysqld
[root@cs2 ~]# chmod 755 /etc/init.d/mysqld

在安装脚本后,依赖于操作系统命令需要在系统启动时来激活它。在Linux上,你可以使用chkconfig:

[root@localhost init.d]# chkconfig --add mysqld

在有一些Linux系统中,也需要执行以下命令来完全启用mysqld脚本:

[root@localhost init.d]# chkconfig --level 345 mysqld on

在FreeBSD系统中,启动脚本通常应该在/usr/local/etc/rc.d/目录中,安装的mysql.server脚本就是/usr/local/etc/rc.d/mysql.server.sh来启用自动启动。rc(8)手册页指出,只有当脚本的基本名称与*.sh shell文件名模式匹配时,才会执行该目录中的脚本。该目录中存在的任何其他文件或目录都将被默认忽略。

作为上述设置的一种替代方案,有些操作系统也会使用/etc/rc.local或/etc/init.d/boot.local来在操作系统启动时启动额外的服务。为了使用这种方法来启动MySQL。需要在合适的启动文件中增加类似下面的命令:

/bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &'

对于其它系统,咨询你操作系统文档来查看如何安装启动脚本。

mysql.server会读取选项文件中的[mysql.server]和[mysqld]部分的选项。为了向后兼容,它也会读取[mysql_server]部分的选项,但是现在你应该将它重命名为[mysql.server]。

可以在全局/etc/my.cnf文件中为mysql.server增加选项。一个常见的my.cnf文件可能看到类似于以下所示:

[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
user=mysql
port=3306
socket = /mysqlsoft/mysql/mysql.sock

[mysql.server]
basedir=/usr/local/mysql

mysql.server脚本支持下表中所列出的选项。如果指定,他们必须被存储在一个选项文件中,而不是在命令行中设置。mysql.server只支持start和stop作为命令行参数

表4.4 mysql.server选项文件选项
Option Name Description Type
basedir MySQL安装目录路径 目录名
datadir MySQL数据目录路径 目录名
pid-file 服务器将它的进程ID需要写入的文件 文件名
service-startup-timeout 服务器在启动时所等待的时间长度 整数

basedir=dir_name
MySQL安装目录路径

datadir=dir_name
MySQL数据目录路径

pid-file=file_name
服务器将它的进程ID需要写入的文件的路径名。如果这个选项没有指定,mysql.server将使用缺省值host_name.pid这种PID文件值传送给mysql_safe会覆盖在[mysqld_safe]选项文件组中所指定的任何值。因为mysql.server会读取[mysqld]选项文件组但不会读取[mysqld_safe]组,你也可以确保通过在[mysqld_safe]和[mysqld]组中设置相同的pid-file值使用在手动从mysql.server脚本中调用mysqld_safe时可以得到相同的值。

service-startup-timeout=seconds
确认服务器启动时可以等待的时间长度。如果服务器在这个时间内没有启动成功,mysql.server会抛出错误并退出。缺省时间为900秒。0意味着在启动时不会执行等待。负数意味着将会永久等待(不会超时)。

PostgreSQL 行安全策略

行安全策略除可以通过GRANT使用 SQL 标准的 特权系统之外,表还可以具有 行安全性策略,它针对每一个用户限制哪些行可以 被普通的查询返回或者可以被数据修改命令插入、更新或删除。这种 特性也被称为行级安全性。默认情况下,表不具有 任何策略,这样用户根据 SQL 特权系统具有对表的访问特权,对于 查询或更新来说其中所有的行都是平等的。

当在一个表上启用行安全性时(使用 ALTER TABLE … ENABLE ROW LEVEL SECURITY),所有对该表选择行或者修改行的普通访问都必须被一条 行安全性策略所允许(不过,表的拥有者通常不服从行安全性策略)。如果 表上不存在策略,将使用一条默认的否定策略,即所有的行都不可见或者不能 被修改。应用在整个表上的操作不服从行安全性,例如TRUNCATE和 REFERENCES。

行安全性策略可以针对特定的命令、角色或者两者。一条策略可以被指定为 适用于ALL命令,或者SELECT、 INSERT、UPDATE或者DELETE。 可以为一条给定策略分配多个角色,并且通常的角色成员关系和继承规则也适用。

要指定哪些行根据一条策略是可见的或者是可修改的,需要一个返回布尔结果 的表达式。对于每一行,在计算任何来自用户查询的条件或函数之前,先会计 算这个表达式(这条规则的唯一例外是leakproof函数, 它们被保证不会泄露信息,优化器可能会选择在行安全性检查之前应用这类 函数)。使该表达式不返回true的行将不会被处理。可以指定独立的表达式来单独控制哪些行可见以及哪些行被允许修改。策略表达式会作为查询的一部分运行并且带有运行该查询的用户的特权,但是安全性定义者函数可以被用来访问对调用用户不可用的数据。

具有BYPASSRLS属性的超级用户和角色在访问一个表时总是 可以绕过行安全性系统。表拥有者通常也能绕过行安全性,不过表拥有者 可以选择用ALTER TABLE … FORCE ROW LEVEL SECURITY来服从行安全性。

用和禁用行安全性以及向表增加策略是只有表拥有者具有的特权。

策略的创建可以使用CREATE POLICY命令,策略的修改 可以使用ALTER POLICY命令,而策略的删除可以使用 DROP POLICY命令。要为一个给定表启用或者禁用行 安全性,可以使用ALTER TABLE命令。

每一条策略都有名称并且可以为一个表定义多条策略。由于策略是表相 关的,一个表的每一条策略都必须有一个唯一的名称。不同的表可以拥有 相同名称的策略。

当多条策略适用于一个给定查询时,它们会被用OR 组合起来,这样只要任一策略允许,行就是可访问的。这类似于一个给定 角色具有它所属的所有角色的特权的规则。

作为一个简单的例子,这里是如何在account关系上 创建一条策略以允许只有managers角色的成员能访问行, 并且只能访问它们账户的行:
CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers USING (manager = current_user);

上述政策隐式地提供了一个with check子句来标识它的using子句,因此这个约束应用于通过命令来选所择的行(因此一个管理者不能select,update或delete现有属于不同管理都的行)和通过命令来修改的行(因此属于不同管理者的行不能通过insert或update来创建)。

如果没有指定角色或者指定的用户名为public,那么这个熏将应用给系统中的所有用户。 为了允许所有用户只访问在一个user表中的行记录,可以使用如下一个简单和策略:
CREATE POLICY user_policy ON users USING (user_name = current_user);

这与前面的示例类似

要对添加到表中的行与可见行使用不同的策略,可以组合多个策略。这对策略将允许所有的用户来查看users表中的所有行,但只能修改属于他们自己的行记录:
CREATE POLICY user_sel_policy ON users FOR SELECT USING (true);

CREATE POLICY user_mod_policy ON users USING (user_name = current_user);

在SELECT命令中,使用OR组合来使用这两个策略,最终的效果是可以选择所有行。在其他命令类型中,只应用第二个策略,因此效果与前面相同。

行安全策略也可以使用alter table命令来禁用。禁用行安全策略不会删除在表上所定义的任何策略,它们只是被忽略。然后表中的所有行都可见并且能被修改,服从于标准的SQL特权系统。

下面是一个较大的例子,它展示了这种特性如何被用于生产环境。表 passwd模拟了一个 Unix 口令文件:
— 简单的口令文件例子

jydb=# CREATE TABLE passwd (
jydb(# user_name text UNIQUE NOT NULL,
jydb(# pwhash text,
jydb(# uid int PRIMARY KEY,
jydb(# gid int NOT NULL,
jydb(# real_name text NOT NULL,
jydb(# home_phone text,
jydb(# extra_info text,
jydb(# home_dir text NOT NULL,
jydb(# shell text NOT NULL
jydb(# );
CREATE TABLE

–创建用户:

jydb=# CREATE ROLE admin;
CREATE ROLE
jydb=# CREATE ROLE bob;
CREATE ROLE
jydb=# CREATE ROLE alice;
CREATE ROLE

— 向表中插入数据

jydb=# INSERT INTO passwd VALUES('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT 0 1
jydb=# INSERT INTO passwd VALUES('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT 0 1
jydb=# INSERT INTO passwd VALUES('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');
INSERT 0 1

–确保在表上启用行级安全性

jydb=# ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;
ALTER TABLE

创建策略
— 管理员能看见所有行并且增加任意行

jydb=# CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
CREATE POLICY

–普通用户可以看见所有行

jydb=# CREATE POLICY all_view ON passwd FOR SELECT USING (true);
CREATE POLICY

–普通用户可以更新它们自己的记录,但是限制普通用户可用的 shell

jydb=# CREATE POLICY user_mod ON passwd FOR UPDATE
jydb-#   USING (current_user = user_name)
jydb-#   WITH CHECK (
jydb(#     current_user = user_name AND
jydb(#     shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
jydb(#   );
CREATE POLICY

–允许admin有所有普通权限

jydb=# GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
GRANT

–普通用户只在公共列上得到选择访问权限

jydb=# GRANT SELECT
jydb-# (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
jydb-# ON passwd TO public;
GRANT

— 允许普通用户更新特定行

jydb=# GRANT UPDATE
jydb-# (pwhash, real_name, home_phone, extra_info, shell)
jydb-# ON passwd TO public;
GRANT

对于任意安全性设置来说,重要的是测试并确保系统的行为符合预期。 使用上述的例子,下面展示了权限系统工作正确:

–admin 可以看到所有的行和字段

jydb=# set role admin;
SET
jydb=> table passwd;
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info |  home_dir   |   shell   
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | xxx    |   2 |   1 | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

jydb=> select * from passwd;
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info |  home_dir   |   shell   
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | xxx    |   2 |   1 | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

— 测试 Alice 能做什么

jydb=> set role alice;
SET
jydb=>  table passwd;
ERROR:  permission denied for relation passwd
jydb=> select * from passwd;
ERROR:  permission denied for relation passwd
jydb=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info |  home_dir   |   shell   
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)


jydb=> update passwd set user_name = 'joe';
ERROR:  permission denied for relation passwd

–Alice 被允许更改她自己的 real_name,但不能改其他的

jydb=> update passwd set real_name = 'Alice Doe';
UPDATE 1
jydb=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
jydb=> update passwd set shell = '/bin/xx';
ERROR:  new row violates row-level security policy for table "passwd"
jydb=> delete from passwd;
ERROR:  permission denied for relation passwd
jydb=> insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for relation passwd

— Alice 可以更改她自己的口令;行级安全性会悄悄地阻止更新其他行

jydb=> update passwd set pwhash = 'abc';
UPDATE 1

引用完整性检查(例如唯一或主键约束和外键引用)总是会绕过行级安全策略以保证数据完整性得到维护。在开发模式和行级安全策略时必须小心避免 “隐蔽通道”通过这类引用完整性检查泄露信息。

在某些环境中确保不应用行级安全策略是很重要的。例如,当执行备份时,如果行级安全策略默默地造成备份操作忽略了一些行数据这将是灾难性的。在这种情部钙,你可以将row_security配置参数设置为off。这本身不会绕过行级安全策略,如果任何查询结果因为行级安全策略而被过滤掉记录时就是抛出一个错误,然后就可以找到错误原因并修复它。

在上面的例子中,策略表达式只考虑了要被访问或被更新行中的当前值。这是最简单并且表现最好的情况。如果可能,最好设计行级安全策略应用来以这种方式工作。 如果需要参考其他行或者其他表来做出策略的决定,可以在策略表达式中通过使用子-SELECTs或者包含SELECT的函数来实现。不过要注意这类访问可能会导致竞争条件,在不小心的情况下这可能会导致信息泄露。作为一个例子,考虑下面的表设计:
–定义权限组

jydb=> CREATE TABLE groups (group_id int PRIMARY KEY,group_name text NOT NULL);
CREATE TABLE
jydb=> INSERT INTO groups VALUES
jydb->   (1, 'low'),
jydb->   (2, 'medium'),
jydb->   (5, 'high');
INSERT 0 3
jydb=> GRANT ALL ON groups TO alice;
GRANT
jydb=> GRANT SELECT ON groups TO public;
GRANT
jydb=> select * from groups;
 group_id | group_name 
----------+------------
        1 | low
        2 | medium
        5 | high
(3 rows)

–定义用户的权限级别

jydb=# CREATE TABLE users (user_name text PRIMARY KEY,
jydb(#                     group_id int NOT NULL REFERENCES groups);
CREATE TABLE
jydb=# INSERT INTO users VALUES
jydb-#   ('alice', 5),
jydb-#   ('bob', 2),
jydb-#   ('mallory', 2);
INSERT 0 3
jydb=# GRANT ALL ON users TO alice;
GRANT
jydb=# GRANT SELECT ON users TO public;
GRANT

jydb=# CREATE ROLE mallory;
CREATE ROLE

jydb=# select * from users;
 user_name | group_id 
-----------+----------
 alice     |        5
 bob       |        2
 mallory   |        2
(3 rows)

–保存的信息的表将被保护

jydb=# CREATE TABLE information (info text,
jydb(#                           group_id int NOT NULL REFERENCES groups);
CREATE TABLE
jydb=# INSERT INTO information VALUES
jydb-#   ('barely secret', 1),
jydb-#   ('slightly secret', 2),
jydb-#   ('very secret', 5);
INSERT 0 3
jydb=# ALTER TABLE information ENABLE ROW LEVEL SECURITY;
ALTER TABLE

–对于用户的安全策略group_id大于等于行的group_id的,这行记录应该是可见的或可被更新的

jydb=# CREATE POLICY fp_s ON information FOR SELECT
jydb-#   USING (group_id < = (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY
jydb=# CREATE POLICY fp_u ON information FOR UPDATE
jydb-#   USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY

--我们只依赖于行级安全性来保护信息表

jydb=# GRANT ALL ON information TO public;
GRANT

现在假设alice希望更改information表中的”slightly secret”的信息,但是觉得用户mallory不应该看到该行中的新内容,因此她这样做:

jydb=# BEGIN;
BEGIN
jydb=# UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE 1
jydb=# UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
UPDATE 1
jydb=# COMMIT;
COMMIT

jydb=> select * from users;
 user_name | group_id 
-----------+----------
 alice     |        5
 bob       |        2
 mallory   |        1
(3 rows)

jydb=> select * from information;
        info         | group_id 
---------------------+----------
 barely secret       |        1
 very secret         |        5
 secret from mallory |        2
(3 rows)

–检查用户mallory是否可以查看information表中的group_id=2的记录

jydb=> set role mallory ;
SET
jydb=> SELECT * FROM information WHERE group_id = 2;
 info | group_id 
------+----------
(0 rows)


jydb=> SELECT * FROM information;
     info      | group_id 
---------------+----------
 barely secret |        1
(1 row)

可以看到现有用户mallory因为users表中的group_id被修改为1了,所以不能查看表information中的group_id为2的记录了。

这看起来是安全的,没有窗口可供用户mallory看到”secret from mallory”字符串。不过,这里有一种竞争条件。如果mallory正在并行地做:
SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

并且她的事务处于READ COMMITTED模式,她就可能看到”secret from mallory”字符串。如果她的事务在alice做完之后就到达information表的行记录,这就会发生。它会阻塞等待alice的事务提交,然后拜FOR UPDATE子句所赐取得更新后的行内容。不过,对于来自users的隐式SELECT,它不会取得一个已更新的行, 因为子-SELECT没有FOR UPDATE,相反会使用查询开始时取得的快照读取users行。因此策略表达式会测试mallory的权限级别的旧值并且允许她看到被更新的行。

有多种方法能解决这个问题。一种简单的答案是在行安全性策略中的 子-SELECT里使用SELECT … FOR SHARE。 不过,这要求在被引用表(这里是users)上授予 UPDATE特权给受影响的用户,这可能不是我们想要的(但是另一条行安全性策略可能被应用来阻止它们实际使用这个特权,或者子-SELECT可能被嵌入到一个安全性定义者函数中)。 还有,在被引用的表上过多并发地使用行共享锁可能会导致性能问题, 特别是表更新比较频繁时。另一种解决方案(如果被引用表上的更新 不频繁就可行)是在更新被引用表时对它取一个排他锁,这样就没有 并发事务能够检查旧的行值了。或者我们可以在提交对被引用表的更新 之后、在做依赖于新安全性情况的更改之前等待所有并发事务结束。

oracle rac 单个实例不能生成awr报告的问题

同事对rac集群生成性能报告时发现rac集群有一个实例没有生成awr快照,另一个实例快照正常。下面是具体处理步骤。
1号实例没有生成awr快照

SQL> select SNAP_ID,END_INTERVAL_TIME,instance_number from dba_hist_snapshot where instance_number=1;

no rows selected

2号实快照正常

SQL> set long 200   
SQL> set linesize 200
SQL> select * from ( select SNAP_ID,END_INTERVAL_TIME,instance_number from dba_hist_snapshot where instance_number=2 order by SNAP_ID desc) where rownum < =10;

   SNAP_ID END_INTERVAL_TIME                                                           INSTANCE_NUMBER
---------- --------------------------------------------------------------------------- ---------------
     24405 17-AUG-19 07.00.47.595 PM                                                                 2
     24404 17-AUG-19 06.00.42.150 PM                                                                 2
     24403 17-AUG-19 05.00.37.041 PM                                                                 2
     24402 17-AUG-19 04.00.31.774 PM                                                                 2
     24401 17-AUG-19 03.00.26.414 PM                                                                 2
     24400 17-AUG-19 02.00.21.176 PM                                                                 2
     24399 17-AUG-19 01.00.16.316 PM                                                                 2
     24398 17-AUG-19 12.00.10.997 PM                                                                 2
     24397 17-AUG-19 11.00.05.446 AM                                                                 2
     24396 17-AUG-19 10.00.59.801 AM                                                                 2

10 rows selected.

mmon进程与awr快照相关,mmnl与ash相关,如是查看两个实例的mmon与mmnl进程
2号实例

[root@db2 ~]# ps -ef | grep mmon
root     128329 127956  0 18:11 pts/2    00:00:00 grep mmon
oracle   201527      1  0  2018 ?        17:17:11 ora_mmon_RLZY2
[root@db2 ~]# ps -ef | grep mmnl
root     131772 127956  0 18:17 pts/2    00:00:00 grep mmnl
oracle   201531      1  0  2018 ?        1-06:06:24 ora_mmnl_RLZY2

1号实例

[root@db1 ~]# ps -ef | grep mmon
root     239020 238963  0 18:52 pts/2    00:00:00 grep mmon
[root@db1 ~]# ps -ef | grep mmnl
root     239052 238963  0 18:52 pts/2    00:00:00 grep mmnl

可以看到1号实例没有mmon与mmnl进程了。

如是查看1号实例的mmon进程的跟踪文件

[root@db1 trace]# ls -lrt *mmon*.trc
-rw-r----- 1 oracle asmadmin 1351052 Jan 19  2018 RLZY1_mmon_20073.trc
-rw-r----- 1 oracle asmadmin  173031 Jan 22  2018 RLZY1_mmon_49119.trc
[root@db1 trace]# more RLZY1_mmon_49119.trc
Trace file /u01/app/oracle/diag/rdbms/rlzy/RLZY1/trace/RLZY1_mmon_49119.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      db1
Release:        3.8.13-68.3.4.el6uek.x86_64
Version:        #2 SMP Tue Jul 14 15:03:36 PDT 2015
Machine:        x86_64
Instance name: RLZY1
Redo thread mounted by this instance: 1
Oracle process number: 36
Unix process pid: 49119, image: oracle@db1 (MMON)


*** 2018-01-19 13:55:20.030
*** SESSION ID:(1369.1) 2018-01-19 13:55:20.030
*** CLIENT ID:() 2018-01-19 13:55:20.030
*** SERVICE NAME:() 2018-01-19 13:55:20.030
*** MODULE NAME:() 2018-01-19 13:55:20.030
*** ACTION NAME:() 2018-01-19 13:55:20.030
 
minact-scn slave-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000

*** 2018-01-19 14:00:20.643
minact-scn master-status: grec-scn:0x0e0e.55ad96ef gmin-scn:0x0e0e.55abf256 gcalc-scn:0x0e0e.55ac2a0a

..........
KEBM: MMON action policy violation. 'Block Cleanout Optim, Undo Segment Scan' viol=1; err=12751
minact-scn master-status: grec-scn:0x0e0e.5f0ebf8c gmin-scn:0x0e0e.5f0eac2e gcalc-scn:0x0e0e.5f0ead91
DDE rules only execution for: ORA 12751

*** 2018-01-22 07:06:04.060
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
Executing ASYNC actions
----- START DDE Action: 'ORA_12751_DUMP' (Sync) -----
Runtime exceeded 300 seconds
Time limit violation detected at:
ksedsts()+465< -kspol_12751_dump()+145<-dbgdaExecuteAction()+1065<-dbgerRunAction()+109<-dbgerRunActions()+4134<-dbgexPhaseII()+1873<-dbgexProcessError()+2680<-dbgeExecuteForError()+88<-dbgePostErrorKGE()+2136<-dbkePostKGE_kgsf()+71<-kge
selv()+276<-ksesecl0()+162
<-ksucin()+147<-kcbzwb()+2727<-kcbgtcr()+31325<-ktucloUsMinScn()+539<-ktucloUsegScan()+992<-ksb_run_managed_action()+384<-ksbcti()+2490<-ksbabs()+1735<-kebm_mmon_main()+209<-ksbrdp()+1045<-opirip()+623<-opidrv()+603<-sou2o()+103<-opimai
_real()+250<-ssthrdmain()+265
<-main()+201<-__libc_start_main()+253Current Wait Stack:
 0: waiting for 'gc buffer busy acquire'
    file#=0x5, block#=0x278, class#=0x49
    wait_id=255378 seq_num=59358 snap_id=1
    wait times: snap=5 min 5 sec, exc=5 min 5 sec, total=5 min 5 sec
    wait times: max=infinite, heur=5 min 5 sec
    wait counts: calls=358 os=358
    in_wait=1 iflags=0x15a2
There is at least one session blocking this session.
  Dumping 1 direct blocker(s):
    inst: 1, sid: 990, ser: 1
  Dumping final blocker:
    inst: 1, sid: 990, ser: 1
Wait State:
  fixed_waits=0 flags=0x22 boundary=(nil)/-1
Session Wait History:
    elapsed time of 0.000061 sec since current wait
 0: waited for 'gc cr block 2-way'
    =0x5, =0x258, =0x47
    wait_id=255377 seq_num=59357 snap_id=1
    wait times: snap=0.000478 sec, exc=0.000478 sec, total=0.000478 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000122 sec of elapsed time
 1: waited for 'gc cr block 2-way'
    =0x5, =0x228, =0x45
    wait_id=255376 seq_num=59356 snap_id=1
    wait times: snap=0.000741 sec, exc=0.000741 sec, total=0.000741 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000120 sec of elapsed time
 2: waited for 'gc cr block 2-way'
    =0x5, =0x138, =0x43
    wait_id=255375 seq_num=59355 snap_id=1
    wait times: snap=0.000528 sec, exc=0.000528 sec, total=0.000528 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000111 sec of elapsed time
 3: waited for 'gc cr block 2-way'
    =0x5, =0xb8, =0x41
    wait_id=255374 seq_num=59354 snap_id=1
    wait times: snap=0.000583 sec, exc=0.000583 sec, total=0.000583 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000139 sec of elapsed time
 4: waited for 'gc cr block 2-way'
    =0x5, =0x110, =0x37
    wait_id=255373 seq_num=59353 snap_id=1
    wait times: snap=0.000541 sec, exc=0.000541 sec, total=0.000541 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000110 sec of elapsed time
 5: waited for 'gc cr block 2-way'
    =0x5, =0x100, =0x35
    wait_id=255372 seq_num=59352 snap_id=1
    wait times: snap=0.000629 sec, exc=0.000629 sec, total=0.000629 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000158 sec of elapsed time
 6: waited for 'gc cr block 2-way'
    =0x5, =0xf0, =0x33
    wait_id=255371 seq_num=59351 snap_id=1
    wait times: snap=0.000617 sec, exc=0.000617 sec, total=0.000617 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000128 sec of elapsed time
 7: waited for 'gc cr block 2-way'
    =0x5, =0xe0, =0x31
    wait_id=255370 seq_num=59350 snap_id=1
    wait times: snap=0.000561 sec, exc=0.000561 sec, total=0.000561 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000124 sec of elapsed time
 8: waited for 'gc cr block 2-way'
    =0x5, =0xd0, =0x2f
    wait_id=255369 seq_num=59349 snap_id=1
    wait times: snap=0.000565 sec, exc=0.000565 sec, total=0.000565 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000128 sec of elapsed time
 9: waited for 'gc cr block 2-way'
    =0x5, =0xc0, =0x2d
    wait_id=255368 seq_num=59348 snap_id=1
    wait times: snap=0.000555 sec, exc=0.000555 sec, total=0.000555 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000125 sec of elapsed time
Sampled Session History of session 1369 serial 1
---------------------------------------------------
The sampled session history is constructed by sampling
the target session every 1 second. The sampling process
captures at each sample if the session is in a non-idle wait,
an idle wait, or not in a wait. If the session is in a
non-idle wait then one interval is shown for all the samples
the session was in the same non-idle wait. If the
session is in an idle wait or not in a wait for
consecutive samples then one interval is shown for all
the consecutive samples. Though we display these consecutive
samples  in a single interval the session may NOT be continuously
idle or not in a wait (the sampling process does not know).
 
The history is displayed in reverse chronological order.
 
sample interval: 1 sec, max history 120 sec
---------------------------------------------------
  [121 samples,                                            07:04:03 - 07:06:03]
    waited for 'gc buffer busy acquire', seq_num: 59358
      p1: 'file#'=0x5
      p2: 'block#'=0x278
      p3: 'class#'=0x49
      time_waited: >= 120 sec (still in wait)
---------------------------------------------------
Sampled Session History Summary:
  longest_non_idle_wait: 'gc buffer busy acquire'
  [121 samples, 07:04:03 - 07:06:03]
      time_waited: >= 120 sec (still in wait)
---------------------------------------------------
----- END DDE Action: 'ORA_12751_DUMP' (SUCCESS, 1 csec) -----
----- END DDE Actions Dump (total 1 csec) -----
KEBM: MMON action policy violation. 'Block Cleanout Optim, Undo Segment Scan' viol=1; err=12751
minact-scn master-status: grec-scn:0x0e0e.5f0ec4ce gmin-scn:0x0e0e.5f0eac2e gcalc-scn:0x0e0e.5f0ead91

*** 2018-01-22 07:11:11.071
DDE rules only execution for: ORA 12751
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----

ORA12751的错误原因是陈旧的SYS对象统计数据会导致生成次优执行计划,从而使AWR自动刷新从操作的语句运行更长时间和超时。

解决方法就是收集新的SYS对象统计信息,为优化器提供更好的统计信息,并生成更高效的执行计划

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

PL/SQL procedure successfully completed.

下面就是重启mmon和mmnl进程

SQL> alter system enable restricted session;

System altered.

SQL> alter system disable restricted session;

System altered.

查看alert日志可以看到mmon和mmnl进程已经重启了

Sat Aug 17 19:18:22 2019
Starting background process MMON
Sat Aug 17 19:18:22 2019
Starting background process MMNL
MMON started with pid=399, OS id=10373 
Sat Aug 17 19:18:22 2019
MMNL started with pid=405, OS id=10375 
ALTER SYSTEM enable restricted session;
Sat Aug 17 19:18:25 2019
Some DDE async actions failed or were cancelled
Sat Aug 17 19:18:25 2019
Sweep [inc][48021]: completed
Sweep [inc][48011]: completed
Sweep [inc][48002]: completed
Sweep [inc][35010]: completed
Sweep [inc][34706]: completed
Sweep [inc][34242]: completed
Sweep [inc][33546]: completed
Sweep [inc][33394]: completed
Sweep [inc2][48021]: completed
Sweep [inc2][48011]: completed
Sweep [inc2][48002]: completed
Sweep [inc2][35010]: completed
Sweep [inc2][34706]: completed
Sweep [inc2][34242]: completed
Sweep [inc2][33546]: completed
Sweep [inc2][33394]: completed
minact-scn: Inst 1 is a slave inc#:30 mmon proc-id:10373 status:0x2
minact-scn status: grec-scn:0x0e0e.61cb2e9c gmin-scn:0x0e0e.5f0eac2e gcalc-scn:0x0e0e.5f0ead91
Sat Aug 17 19:18:29 2019
db_recovery_file_dest_size of 10240 MB is 20.87% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sat Aug 17 19:18:42 2019
ALTER SYSTEM disable restricted session;

再查看1号实例的mmon与mmnl进程状态

[root@db1 ~]# ps -ef | grep mmnl
oracle    10375      1  0 19:18 ?        00:00:00 ora_mmnl_RLZY1
root      10611 238963  0 19:18 pts/2    00:00:00 grep mmnl

[root@db1 ~]# ps -ef | grep mmon
oracle    10373      1  7 19:18 ?        00:00:02 ora_mmon_RLZY1
root      10630 238963  0 19:18 pts/2    00:00:00 grep mmon
过了两个小时去查看1号实例已经生成了两条快照信息
SQL> set long 200
SQL> set linesize 200
SQL> select * from ( select SNAP_ID,END_INTERVAL_TIME,instance_number from dba_hist_snapshot where instance_number=1 order by SNAP_ID desc) where rownum < =10;

   SNAP_ID END_INTERVAL_TIME                                                           INSTANCE_NUMBER
---------- --------------------------------------------------------------------------- ---------------
     24407 17-AUG-19 09.00.58.595 PM                                                                 1
     24406 17-AUG-19 08.00.40.244 PM                                                                 1

到此问题解决了。

Proudly powered by WordPress | Indrajeet by Sus Hill.