ORA-00600 qosdExpStatRead expcnt mismatch

Oracle 12.2.0.1最近出现了ORA***211;0600错误,其详细信息如下:

ORA-00600: 内部错误代码, 参数: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.DBMS_STATS", line 1976
ORA-06512: 在 "SYS.DBMS_STATS", line 46935
ORA-06512: 在 "SYS.DBMS_STATS", line 47168
ORA-00600: 内部错误代码, 参数: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.DBMS_STATS", line 1976
ORA-06512: 在 "SYS.DBMS_STATS", line 46935
ORA-06512: 在 "SYS.DBMS_STATS", line 47168

根据Oracle官方给出的检查SQL语句,其执行结果如下:

引起访问题的原因是由于sys.exp_obj$.exp_cnt与sys.exp_stat$的行数据不匹配所造成的

解决方案
1.备份数据库涉及到修改数据字典

2. 修复问题

SQL>update sys.exp_obj$ a set exp_cnt=(select count(*) from sys.exp_stat$ b where
a.objn=b.objn and a.snapshot_id=b.snapshot_id ) where a.objn=124789;

SQL>commit;

3. 修改后,执行如下SQL确认是否还有记录,如果没有记录,说明已经修改成功,提交。

SQL>With b as (
select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by objn,snapshot_id)
select * from sys.exp_obj$ a, b where a.objn=b.objn and a.snapshot_id=b.snapshot_id
and a.EXP_CNT<>b.CNT;

修复后,通过一天的观察没有出现该问题了。

PostgreSQL使用表继承实现分区表

PostgreSQL9.6支持基本表的分区。这部分将描述为什么以及如何来实现表分区作为你数据库设计的一部分。

概述
分区指的是将逻辑上一的一个大表分成多个小的物理上的片(子表),分区可以提供以下好处:
.在某些情况下查询性能能够显著提升,特别是当那些访问压力大的行在一个分区或者少数几个分区时。分区可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。

.当查询或更新访问一个分区的大部分行时,可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问,这样可以改善性能。

.如果需求计划使用划分设计,可以通过增加或移除分区来完成批量载入和删除。ALTER TABLE NO INHERIT和DROP TABLE都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。

.很少使用的数据可以被迁移到便宜且较慢的存储介质上。

当一个表非常大时,分区所带来的好处是非常值得的。一个表何种情况下会从分区中获益取决于应用,一个经验法则是当表的尺寸超过了数据库服务器物理内存时,分区会为表带来好处。

当前,PostgreSQL支持通过表继承来实现分区。每个分区必须被创建为单个父表的子表。父表它本身正常来说是空的;它存在仅仅是代表整个数据库。在试图设置分区之前应该要先熟悉表继承。

在PostgreSQL中可以实现下列形式的分区:

范围分区
表被根据一个关键列或一组列划分为***221;范围***221;分区,不同的分区的范围之间没有重叠。例如,我们可以根据日期范围划分分区,或者根据特定业务对象的标识符划分分区。

列表分区
通过显式地列出每一个分区中出现的键值来划分表。

实现分区
要建立一个分区表,可以这样做:
1.创建一个***221;主***221;表,所有的分区都将继承它。
这个表将不包含数据。不要对这个表定义任何检查约束,除非你打算将这些约束应用到所有的分区。同样也不需要定义任何索引或者唯一约束。

2.创建一些继承于主表的***221;子***221;表。通常,这些表不会在从主表继承的列集中增加任何列。
们将这些子表认为是分区,尽管它们在各方面来看普通的PostgreSQL表(或者可能是外部表)。

3.为分区表增加表约束以定义每个分区中允许的键值。
典型的例子是:

CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )

要确保这些约束能够保证在不同分区所允许的键值之间不存在重叠。设置范围约束时一种常见的错误是:

CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )

这是错误的,因为键值200并没有被清楚地分配到某一个分区。注意在语法上范围划分和列表划分没有区别,这些术语只是为了描述方便而存在。

4.对于每一个分区,在关键列上创建一个索引,并创建其他我们所需要的索引(关键索引并不是严格必要的,但是在大部分情况下它都是有用的。如果我们希望键值是唯一的,则我们还要为每一个分区创建一个唯一或者主键约束。)

5.还可以有选择地定义一个触发器或者规则将插入到主表上的数据重定向到合适的分区上。

6.确保在postgresql.conf中constraint_exclusion配置参数没有被禁用。如果它被禁用,查询将不会被按照期望的方式优化。

例如,假设我们正在为一个大型的冰淇淋公司构建一个数据库。该公司测量每天在每一个区域的最高气温以及冰淇淋销售。在概念上,我们想要一个这样的表:

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);

insert into measurement values(1,date '2008-02-01',1,1);

由于该表的主要用途是为管理层提供在线报告,我们知道大部分查询将只会访问上周、上月或者上季度的数据。为了减少需要保存的旧数据的量,我们决定只保留最近3年的数据。在每一个月的开始,我们将删除最老的一个月的数据。

在这种情况下,我们可以使用分区来帮助我们满足对于测量表的所有不同需求。按照上面所勾勒的步骤,分区可以这样来建立:

1.主表是measurement表,完全按照以上的方式声明。

jydb=# CREATE TABLE measurement (
jydb(#     city_id         int not null,
jydb(#     logdate         date not null,
jydb(#     peaktemp        int,
jydb(#     unitsales       int
jydb(# );
CREATE TABLE

2.下一步我们为每一个活动月创建一个分区:

CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);

每一个分区自身都是完整的表,但是它们的定义都是从measurement表继承而来。

这解决了我们的一个问题:删除旧数据。每个月,我们所需要做的是在最旧的子表上执行一个DROP TABLE命令并为新一个月的数据创建一个新的子表。

3.我们必须提供不重叠的表约束。和前面简单地创建分区表不同,实际的表创建脚本应该是:

jydb=# CREATE TABLE measurement_y2006m02 (
jydb(#     CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE
jydb=# CREATE TABLE measurement_y2006m03 (
jydb(#     CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE
jydb=# CREATE TABLE measurement_y2008m02 (
jydb(#     CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE
jydb=# CREATE TABLE measurement_y2008m03
jydb-#   (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLE

4.我们可能在关键列上也需要索引:

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

在这里我们选择不增加更多的索引。

5.我们希望我们的应用能够使用INSERT INTO measurement ***230;并且数据将被重定向到合适的分区表。我们可以通过为主表附加一个合适的触发器函数来实现这一点。如果数据将只被增加到最后一个分区,我们可以使用一个非常简单的触发器函数:

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

完成函数创建后,我们创建一个调用该触发器函数的触发器:

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

我们必须在每个月重新定义触发器函数,这样它才会总是指向当前分区。而触发器的定义则不需要被更新。

我们也可能希望插入数据时服务器会自动地定位应该加入数据的分区。我们可以通过一个更复杂的触发器函数来实现之,例如:

jydb=# CREATE OR REPLACE FUNCTION measurement_insert_trigger()
jydb-# RETURNS TRIGGER AS $$
jydb$# BEGIN
jydb$#
jydb$#     IF ( NEW.logdate >= DATE '2006-03-01' AND
jydb$#             NEW.logdate < DATE '2006-04-01' ) THEN
jydb$#         INSERT INTO measurement_y2006m03 VALUES (NEW.*);
jydb$#     ELSIF ( NEW.logdate >= DATE '2008-02-01' AND
jydb$#             NEW.logdate < DATE '2008-03-01' ) THEN
jydb$#         INSERT INTO measurement_y2008m02 VALUES (NEW.*);
jydb$#     ELSE
jydb$#         RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
jydb$#     END IF;
jydb$#     RETURN NULL;
jydb$# END;
jydb$# $$
jydb-# LANGUAGE plpgsql;
CREATE FUNCTION



jydb=# CREATE TRIGGER insert_measurement_trigger
jydb-#     BEFORE INSERT ON measurement
jydb-#     FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
CREATE TRIGGER
jydb=# insert into measurement values(1,date '2006-03-03',1,1);
INSERT 0 0
jydb=# insert into measurement values(1,date '2008-02-03',1,1);
INSERT 0 0
jydb=# select * from measurement_y2006m03;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
       1 | 2006-03-03 |        1 |         1
(2 rows)

jydb=# select * from measurement_y2008m02;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
       1 | 2008-02-03 |        1 |         1
(5 rows)

jydb=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
       1 | 2006-03-03 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
       1 | 2008-02-03 |        1 |         1
(7 rows)

触发器的定义和以前一样。注意每一个IF测试必须准确地匹配它的分区的CHECK约束。当该函数比单月形式更加复杂时,并不需要频繁地更新它,因为可以在需要的时候提前加入分支。

注意: 在实践中,如果大部分插入都会进入最新的分区,最好先检查它。为了简洁,我们为触发器的检查采用了和本例中其他部分一致的顺序。

如我们所见,一个复杂的分区模式可能需要大量的DDL。在上面的例子中,我们需要每月创建一个新分区,所以最好能够编写一个脚本自动地生成所需的DDL。

管理分区
通常当初始定义的表倾向于动态变化时,一组分区会被创建。删除旧的分区并周期性地为新数据增加新分区是很常见的。划分的一个最重要的优点是可以通过操纵分区结构来使得这种痛苦的任务几乎是自发地完成,而不需要去物理地移除大量的数据。

移除旧数据的最简单的选项是直接删除不再需要的分区:

jydb=# DROP TABLE measurement_y2006m02;
DROP TABLE

这可以非常快地删除百万级别的记录,因为它不需要逐一地删除记录。

另一个经常使用的选项是将分区从被划分的表中移除,但是把它作为一个独立的表保留下来:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

这允许在数据被删除前执行更进一步的操作。例如,这是一个很有用的时机通过COPY、pg_dump或类似的工具来备份数据。这也是进行数据聚集、执行其他数据操作或运行报表的好时机。

相似地我们也可以增加新分区来处理新数据。我们可以在被划分的表中创建一个新的空分区:

jydb=# CREATE TABLE measurement_y2008m02 (
jydb(#     CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE

作为一种选择方案,有时创建一个在分区结构之外的新表更方便,并且在以后才将它作为一个合适的分区。这使得数据可以在出现于分区表中之前被载入、检查和转换:

jydb=# CREATE TABLE measurement_y2008m03
jydb-#   (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLE
jydb=# ALTER TABLE measurement_y2008m03 ADD CONSTRAINT y2008m03
jydb-#    CHECK ( logdate >= DATE '2008-03-01' AND logdate < DATE '2008-04-01' );
ALTER TABLE
jydb=# ALTER TABLE measurement_y2008m03 INHERIT measurement;
ALTER TABLE

分区与约束排除
约束排除是一种查询优化技术,它可以为按照以上方式定义的分区表提高性能。例如:

jydb=# SET constraint_exclusion = on;
SET
jydb=# SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
 count
-------
     3
(1 row)

如果没有约束排除,上述查询将扫描measurement表的每一个分区。在启用约束排除后,规划器将检查每一个分区的约束来确定该分区需不需要被扫描,因为分区中可能不包含满足查询WHERE子句的行。如果规划器能够证实这一点,则它将会把该分区排除在查询计划之外。

可以使用EXPLAIN命令来显示开启了constraint_exclusion的计划和没有开启该选项的计划之间的区别。一个典型的未优化的计划是:

jydb=# SET constraint_exclusion = off;
SET
jydb=# EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=107.47..107.48 rows=1 width=8)
   ->  Append  (cost=0.00..102.69 rows=1913 width=0)
         ->  Seq Scan on measurement  (cost=0.00..3.31 rows=62 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
(10 rows)

其中的某些或者全部分区将会使用索引扫描而不是全表顺序扫描,但是关键在于根本不需要扫描旧分区来回答这个查询。当我们开启约束排除后,对于同一个查询我们会得到一个更加廉价的计划:

jydb=# SET constraint_exclusion = on;
SET
jydb=# EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=72.80..72.81 rows=1 width=8)
   ->  Append  (cost=0.00..69.56 rows=1296 width=0)
         ->  Seq Scan on measurement  (cost=0.00..3.31 rows=62 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
(8 rows)

注意约束排除只由CHECK约束驱动,而非索引的存在。因此,没有必要在关键列上定义索引。是否在给定分区上定义索引取决于我们希望查询经常扫描表的大部分还是小部分。在后一种情况中索引将会发挥作用。

constraint_exclusion的默认(也是推荐)设置实际上既不是on也不是off,而是一个被称为partition的中间设置,这使得该技术只被应用于将要在分区表上工作的查询。设置on将使得规划器在所有的查询中检查CHECK约束,即使简单查询不会从中受益。

替代的分区方法
另一种将插入数据重定向到合适的分区的方法是在主表上建立规则而不是触发器,例如:

jydb=# CREATE RULE measurement_insert_y2006m03 AS
jydb-# ON INSERT TO measurement WHERE
jydb-# ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
jydb-# DO INSTEAD
jydb-# INSERT INTO measurement_y2006m03 VALUES (NEW.*);
CREATE RULE
jydb=# CREATE RULE measurement_insert_y2008m02 AS
jydb-# ON INSERT TO measurement WHERE
jydb-# ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb-# DO INSTEAD
jydb-# INSERT INTO measurement_y2008m02 VALUES (NEW.*);
CREATE RULE


jydb=# insert into measurement values(1,date '2006-03-02',1,1);
INSERT 0 0
jydb=# insert into measurement values(1,date '2008-02-02',1,1);
INSERT 0 0
jydb=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
(5 rows)

jydb=# select * from measurement_y2006m03;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
(1 row)

jydb=# select * from measurement_y2008m02;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
(4 rows)

一个规则比一个触发器具有明显更高的负荷,但是该负荷是由每个查询承担而不是每一个行,因此这种方法可能对于批量插入的情况有益。但是,在大部分情况下触发器方法能提供更好的性能。

注意COPY会忽略规则。如果希望使用COPY来插入数据,我们将希望将数据复制到正确的分区表而不是主表。COPY会引发触发器,因此如果使用触发器方法就可以正常地使用它。

规则方法的另一个缺点是如果一组规则没有覆盖被插入的数据,则该数据将被插入到主表中而不会发出任何错误。

分区也可以使用一个UNION ALL视图来组织。例如:

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2006m02
UNION ALL SELECT * FROM measurement_y2006m03
...
UNION ALL SELECT * FROM measurement_y2007m11
UNION ALL SELECT * FROM measurement_y2007m12
UNION ALL SELECT * FROM measurement_y2008m01;

但是,如果要增加或者删除单独的分区,就需要重新地创建视图。在实践中,相对于使用继承,这种方法很少被推荐。

警告
下面的警告适用于分区表:
.没有自动的方法来验证所有的CHECK约束是互斥的。创建代码来生成分区并创建或修改相关对象比手工写命令要更安全。

.这里展示的模式都假设分区的关键列从不改变,或者是其改变不足以导致它被移到另一个分区。一个尝试将行移到另一个分区的UPDATE会失败,因为CHECK约束的存在。如果我们需要处理这类情况,我们可以在分区表上放置合适的更新触发器,但是它会使得结构的管理更加复杂。

.如果我们在使用手工的VACUUM或ANALYZE命令,别忘了需要在每一个分区上都运行一次。以下的命令:
ANALYZE measurement;
只会处理主表。

.带有ON CONFLICT子句的INSERT 语句不太可能按照预期的方式工作,因为ON CONFLICT动作 只有在指定的目标关系(而非它的子关系)上有唯一违背的情况下才会被采用。

下面的警告适用于约束排除:
.只有在查询的WHERE子句包含常量(或者外部提供的参数)时,约束排除才会起效。例如,一个与非不变函数(例如CURRENT_TIMESTAMP)的比较不能被优化,因为规划器不知道该函数的值在运行时会落到哪个分区内。

.保持分区约束简单,否则规划器可能没有办法验证无需访问的分区。按前面的例子所示,为列表分区使用简单相等条件或者为范围分区使用简单范围测试。一个好的经验法则是分区约束应该只包含使用B-tree索引操作符的比较,比较的双方应该是分区列和常量。

.在约束排除期间,主表所有的分区上的所有约束都会被检查,所以大量的分区将会显著地增加查询规划时间。使用这些技术的分区在大约最多100个分区的情况下工作得很好,但是不要尝试使用成千个分区。

PostgreSQL 表继承

PostgreSQL实现了表继承,这对数据库设计者来说是一种有用的工具(SQL:1999及其后的版本定义了一种类型继承特性,但和这里介绍的继承有很大的不同)。让我们从一个例子开始:假设我们要为城市建立一个数据模型。每一个州有很多城市,但是只有一个首府。我们希望能够快速地检索任何特定州的首府城市。这可以通过创建两个表来实现:一个用于州首府,另一个用于不是首府的城市。然而,当我们想要查看一个城市的数据(不管它是不是一个首府)时会发生什么?继承特性将有助于解决这个问题。我们可以将capitals表定义为继承自cities表:

jydb=# CREATE TABLE cities (
jydb(# name text,
jydb(# population float,
jydb(# altitude int -- in feet
jydb(# );
CREATE TABLE
jydb=# CREATE TABLE capitals (
jydb(# state char(2)
jydb(# ) INHERITS (cities);
CREATE TABLE


jydb=# insert into cities values('Las Vegas',600,2174);
INSERT 0 1
jydb=# insert into cities values('Mariposa',500,1953);
INSERT 0 1
jydb=# insert into cities values('Madison',450,845);
INSERT 0 1
jydb=# insert into capitals values('Houston',400,745,'LA');
INSERT 0 1


jydb=# select * from cities;
   name    | population | altitude
-----------+------------+----------
 Las Vegas |        600 |     2174
 Mariposa  |        500 |     1953
 Madison   |        450 |      845
 Houston   |        400 |      745
(4 rows)

jydb=# select * from capitals;
  name   | population | altitude | state
---------+------------+----------+-------
 Houston |        400 |      745 | LA
(1 row)

在这种情况下,capitals表继承了它父表cities的所有列。州首府还有一个额外的列state用来表示它所属的州。

在PostgreSQL中,一个表可以从0个或者多个其他表继承,而对一个表的查询则可以引用一个表的所有行或者该表的所有行加上它所有的后代表。默认情况是后一种行为。例如,下面的查询将查找所有海拔高于500尺的城市的名称,包括州首府:

jydb=# SELECT name, altitude FROM cities WHERE altitude > 500;
   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
 Houston   |      745
(4 rows)

在另一方面,下面的查询将找到海拔超过500尺且不是州首府的所有城市:

jydb=# SELECT name, altitude FROM ONLY cities WHERE altitude > 500;
   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
(3 rows)

这里的ONLY关键词指示查询只被应用于cities上,而其他在继承层次中位于cities之下的其他表都不会被该查询涉及。很多我们已经讨论过的命令(如SELECT、UPDATE和DELETE)都支持ONLY关键词。

我们也可以在表名后写上一个*来显式地将后代表包括在查询范围内:

jydb=# SELECT name, altitude FROM cities* WHERE altitude > 500;
   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
 Houston   |      745
(4 rows)

*并不是必须的,因为它对应的行为是默认的(除非改变sql_inheritance配置选项的设置)。但是书写*有助于强调会有附加表被搜索。

在某些情况下,我们可能希望知道一个特定行来自于哪个表。每个表中的系统列tableoid可以告诉我们行来自于哪个表:

jydb=# SELECT c.tableoid, c.name, c.altitude FROM cities c WHERE c.altitude > 500;
 tableoid |   name    | altitude
----------+-----------+----------
    24653 | Las Vegas |     2174
    24653 | Mariposa  |     1953
    24653 | Madison   |      845
    24659 | Houston   |      745
(4 rows)

(如果重新生成这个结果,可能会得到不同的OID数字。)通过与pg_class进行连接可以看到实际的表名:

jydb=# SELECT p.relname, c.name, c.altitude
jydb-# FROM cities c, pg_class p
jydb-# WHERE c.altitude > 500 AND c.tableoid = p.oid;
 relname  |   name    | altitude
----------+-----------+----------
 cities   | Las Vegas |     2174
 cities   | Mariposa  |     1953
 cities   | Madison   |      845
 capitals | Houston   |      745
(4 rows)

另一种得到同样效果的方法是使用regclass伪类型, 它将象征性地打印出表的 OID:

jydb=# SELECT c.tableoid::regclass, c.name, c.altitude
jydb-# FROM cities c
jydb-# WHERE c.altitude > 500;
 tableoid |   name    | altitude
----------+-----------+----------
 cities   | Las Vegas |     2174
 cities   | Mariposa  |     1953
 cities   | Madison   |      845
 capitals | Houston   |      745
(4 rows)

继承不会自动地将来自INSERT或COPY命令的数据传播到继承层次中的其他表中。在我们的例子中,下面的INSERT语句将会失败:

jydb=# INSERT INTO cities (name, population, altitude, state) VALUES (’Albany’, NULL, NULL, ’NY’);
ERROR:  column "state" of relation "cities" does not exist
LINE 1: INSERT INTO cities (name, population, altitude, state) VALUE...

^

我们也许希望数据能被以某种方式被引入到capitals表中,但是这不会发生:INSERT总是向指定的表中插入。在某些情况下,可以通过使用一个规则(见第 39 章)来将插入动作重定向。但是这对上面的情况并没有帮助,因为cities表根本就不包含state列,因而这个命令将在触发规则之前就被拒绝。

父表上的所有检查约束和非空约束都将自动被它的后代所继承。其他类型的约束(唯一、主键和外键约束)则不会被继承。

一个表可以从多个父表继承,在这种情况下它拥有父表们所定义的列的并集。任何定义在子表上的列也会被加入到其中。如果在这个集合中出现重名列,那么这些列将被***221;合并***221;,这样在子表中只会有一个这样的列。重名列能被合并的前提是这些列必须具有相同的数据类型,否则会导致错误。可继承的检查约束和非空约束以类似的方式合并。因此,例如,如果任何列定义被标记为not-null,则合并列将被标记为not-null。如果检查约束具有相同的名称,则合并它们;如果条件不同,则合并将失败。

表继承通常是在子表被创建时建立,使用CREATE TABLE语句的INHERITS子句。一个已经被创建的表也可以另外一种方式增加一个新的父亲关系,使用ALTER TABLE的INHERIT变体。要这样做,新的子表必须已经包括和父表相同名称和数据类型的列。子表还必须包括和父表相同的检查约束和检查表达式。相似地,一个继承链接也可以使用ALTER TABLE的 NO INHERIT变体从一个子表中移除。动态增加和移除继承链接可以用于实现表划分

一种创建一个未来将被用做子女的新表的方法是在CREATE TABLE中使用LIKE子句。这将创建一个和源表具有相同列的新表。如果源表上定义有任何CHECK约束,LIKE的INCLUDING CONSTRAINTS选项可以用来让新的子表也包含和父表相同的约束。

当有任何一个子表存在时,父表不能被删除。当子表的列或者检查约束继承于父表时,它们也不能被删除或修改。如果希望移除一个表和它的所有后代,一种简单的方法是使用CASCADE选项删除父表

ALTER TABLE将会把列的数据定义或检查约束上的任何变化沿着继承层次向下传播。同样,删除被其他表依赖的列只能使用CASCADE选项。ALTER TABLE对于重名列的合并和拒绝遵循与CREATE TABLE同样的规则。

继承查询只对父表执行访问权限检查。因此,例如,对cities表授予update权限会意味着通过cities访问capitals表时也能更新capitals表。这体现了子表中的数据也在父表中。但是capitals表在没有额外地授权情况下不能被直接更新。以类似的方式,父表的行安全策略在执行继承查询时会应用到子表的行记录。子表的策略(如果有的话)仅当它是查询中显式命名的表时才应用;在这种情况下,任何附加到其父级的策略都将被忽略。

外部表也可以是继承层次 中的一部分,即可以作为父表也可以作为子表,就像常规表一样。如果一个外部表是继承层次的一部分,那么任何不被该外部表支持的操作也不被整个层次所支持。

在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实例。想让客户端通过共享内存连接的过程与上面的过程类似。对每个连接使用***211;shared-memory选项并使用***211;shared-memory-base-name选项来为每个实例指定唯一的共享内存名字。

在Windows中作为Windows服务来启动多个MySQL实例
在Windows中,一个MySQL服务器可以以Windows服务来运行。为了设置多个MySQL服务,你必须确保每个实例使用不同的服务名另外其它参数每个实例必须都有唯一值。为了将MySQL服务器注册成为Windows服务,使用mysqld ***211;install或mysqld ***211;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服务使用单独的文件来指定选项并且在注册服务时使用***211;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 ***211;host=host_name ***211;port=port_number variables

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

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

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

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

另一种方法是将每个MySQL实例的选项放入不同的选项文件,然后启动每个实例时使用***211;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来启动实例时使用***211;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来管理多个实例的操作就完成了。

Proudly powered by WordPress | Indrajeet by Sus Hill.