分类 默认分类 下的文章

手动隔离-为SQL语句的执行计划创建隔离配置

SQL只要执行计划不变,每次都会被直接终止

使用以下任一包函数为SQL语句的执行计划创建隔离配置:DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_IDDBMS_SQLQ .CREATE_QUARANTINE_BY_SQL_TEX

DECLARE
quarantine_config VARCHAR2(30);
BEGIN
quarantine_config:= DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID( SQL_ID => ’8vu7s907prbgr’, PLAN_HASH_VALUE => '3488063716');
END;
/

DECLARE
quarantine_config VARCHAR2(30);
BEGIN

quarantine_config:= DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID( SQL_ID => '152sukb473gsk'); 

END;
/
DECLARE

quarantine_config VARCHAR2(30); 

BEGIN
quarantine_config:= DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT( SQL_TEXT => to_clob('select count(*) from emp'));
END;

手动隔离-在隔离配置中指定隔离阈值
可指定如下隔离阈值:
-CPU time
-Elapsed time
-I/O in megabytes
-Number of physical I/O requests
-Number of logical I/O requests

BEGIN
DBMS_SQLQ.ALTER_QUARANTINE(

       QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4', 
       PARAMETER_NAME => 'CPU_TIME', 
       PARAMETER_VALUE => '5'); 

DBMS_SQLQ.ALTER_QUARANTINE(

      QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4', 
       PARAMETER_NAME => 'ELAPSED_TIME', PARAMETER_VALUE => '10'); 

END;

curl -o /dev/null -s -w %{http_code}:%{time_connect}:%{time_starttransfer}:%{time_total} https://www.elooy.com;

-o /dev/null 丢弃输出
-s 静默模式
-w 格式化输出, 变量有url_effective, http_code, http_connect, time_total,time_namelookup, time_connect, time_appconnect, time_pretransfer, time_redirect,time_starttransfer, size_download, size_upload, size_header, size_request, speed_download, speed_upload, content_type,num_connects, num_redirects, redirect_url, ftp_entry_path, ssl_verify_result

# 每隔5秒访问一次
while true; do curl -o /dev/null -s -w %{http_code}:%{time_connect}:%{time_starttransfer}:%{time_total} https://www.elooy.com; echo;sleep 5;done

命令的用法:

Usage:
 crontab [options] file
 crontab [options]
 crontab -n [hostname]

Options:
 -u <user>  define user
 -e         edit user's crontab
 -l         list user's crontab
 -r         delete user's crontab
 -i         prompt before deleting
 -n <host>  set host in cluster to run users' crontabs
 -c         get host in cluster to run users' crontabs
 -s         selinux context
 -x <mask>  enable debugging

任务格式: 分钟 小时 星期 命令

名称数值范围
分钟0~59
小时0~23
1~31
1~12
星期0~7 (0/7表示星期天)
时间格式描述
*表示每分钟\小时\天\月\星期
n-m表示n~m分\时\日\月\星期
*/n表示间隔分钟\小时\天
a,b,c表示指定的第a, b, c分钟\小时\天\星期

下面测试mysql_safe启动失败后的问题:
5.7下运行:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
) engine=Myisam
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
) ;

替换成8.0的程序,运行
mysql@node1:/home/db/mysql$ mysqld_safe --user=mysql --datadir=/mysqldata/data
2020-02-22T03:37:50.656134Z mysqld_safe Logging to '/mysqldata/logs/mysql_error.log'.
2020-02-22T03:37:50.682648Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/data
2020-02-22T03:38:18.557227Z mysqld_safe mysqld from pid file /mysqldata/data/mysqld.pid ended

error.log显示:
2020-02-22T11:38:13.629178+08:00 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2020-02-22T11:38:15.154654+08:00 2 [ERROR] [MY-013140] [Server] The 'partitioning' feature is not available; you need to remove '--skip-partition' or use MySQL built with '-DWITH_PARTITION_STORAGE_ENGINE=1'
2020-02-22T11:38:15.154693+08:00 2 [ERROR] [MY-013140] [Server] Can't find file: './test/employees.frm' (errno: 0 - Success)
2020-02-22T11:38:15.154703+08:00 2 [ERROR] [MY-013137] [Server] Can't find file: './test/employees.frm' (OS errno: 0 - Success)
2020-02-22T11:38:15.154711+08:00 2 [ERROR] [MY-010348] [Server] Error in reading file ./test/employees.frm
2020-02-22T11:38:15.154726+08:00 2 [ERROR] [MY-010758] [Server] Error in creating TABLE_SHARE from employees.frm file.
2020-02-22T11:38:16.311130+08:00 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2020-02-22T11:38:16.311156+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2020-02-22T11:38:16.614585+08:00 0 [Warning] [MY-011068] [Server] The syntax 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead.
2020-02-22T11:38:18.515869+08:00 0 [System] [MY-010910] [Server] /home/db/mysql/product/bin/mysqld: Shutdown complete (mysqld 8.0.11) MySQL Community Server - GPL.
2020-02-22T03:38:18.557227Z mysqld_safe mysqld from pid file /mysqldata/data/mysqld.pid ended

重新用5.7程序启动,登录数据库解决问题
mysql@node1:/home/db/mysql$ mysqld_safe --user=mysql --datadir=/mysqldata/data
root@localhost 11:48:37 [(none)]>use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed, 1 warning
root@localhost 11:49:10 [test]>alter table test.employees engine=innodb ;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost 11:49:11 [test]>shutdown ;
Query OK, 0 rows affected (0.00 sec)

替换8.0启动
mysql@node1:/home/db/mysql$ mysqld_safe --user=mysql --datadir=/mysqldata/data

升级:
mysql_upgrade -uroot -pRoot@0101

重启:
mysqladmin -u root -pRoot@0101 shutdown
service mysqld start

注意:
1、替换为8.0程序启动失败时,数据库可以退回到5.7,手动解决不兼容问题,重复操作可成功
2、替换为8.0程序启动成功时,数据库不可以退回到5.7

5.7 -> 8.0
checkForServerUpgrade (mysql shell 8.0工具)用法:
[root@node1 mysql]# yum -y isntall mysql-shell-8.0.19-1.el7.x86_64.rpm

mysql@node1:/home/db/mysql$ mysqlsh -- util check-for-server-upgrade { --user=root --host=localhost --port=13306 } --target-version=8.0.11 --output-format=JSON --config-path=/etc/my.cnf

工具很详细地说明了,升级之前要修改的错误,以及版本升级后默认参数更改的注意事项

一、5.7更新前检查:

1、 table和trigger检查
obsolete data types,
obsolete functions,
orphan .frm files,
InnoDB tables that use nonnative partitioning, or triggers that have a missing or empty definer or an invalid creation context (indicated by the character_set_client, collation_connection, Database Collation attributes displayed by SHOW TRIGGERS or the INFORMATION_SCHEMA TRIGGERS table)

mysqlcheck -u root -p --all-databases --check-upgrade
2、 没有不支持引擎的分区表
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
AND CREATE_OPTIONS LIKE '%partitioned%';
办法:
ALTER TABLE table_name ENGINE = INNODB;

ALTER TABLE table_name REMOVE PARTITIONING;

3、 在5.7的mysql系统表空间中没有表与8.0数字典同名的
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE LOWER(TABLE_SCHEMA) = 'mysql'
and LOWER(TABLE_NAME) IN
(
'catalogs',
'character_sets',
'collations',
'column_statistics',
'column_type_elements',
'columns',
'dd_properties',
'events',
'foreign_key_column_usage',
'foreign_keys',
'index_column_usage',
'index_partitions',
'index_stats',
'indexes',
'parameter_type_elements',
'parameters',
'resource_groups',
'routines',
'schemata',
'st_spatial_reference_systems',
'table_partition_values',
'table_partitions',
'table_stats',
'tables',
'tablespace_files',
'tablespaces',
'triggers',
'view_routine_usage',
'view_table_usage'
);
显示出来有同名,必须改名字
e.g
RENAME TABLE t3 to tt3 ;

4、 没有外键约束名字超过64个字符
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN
(SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

超过64个字符更改

5、 没有table或procedure在enum 或 set 字段长度超过255个字符或1020字节
6、 5.7中没有安装在8.0中不支持的特征
6.1 表的存储引擎在8.0中是不支持
6.2 数据开启的选项和系统参数在8.0中是被移除的

二、in-place更新
1、提交或回滚XA事务
2、加密表空间
ALTER INSTANCE ROTATE INNODB MASTER KEY;
3、设置innodb_fast_shutdown
SET GLOBAL innodb_fast_shutdown = 1; -- fast shutdown
SET GLOBAL innodb_fast_shutdown = 0; -- slow shutdown
4、关库
mysqladmin -u root -p shutdown
5、解压MySQL8.0二进制安装,并替换旧

6、开启mysql8.0,用现在的数据目录
mysqld_safe --user=mysql --datadir=/path/to/existing-datadir
如果有加密表空间,带参数--early-plugin-load指定加密插件

开启时,自动清理工作:
6.1 在—datadir目录,创建一个目录backup_metadata_57,迁移.frm, .par, .TRG, .TRN, or .isl 到backup_metadata_57
backup_metadata_57 保留旧文件系统的结构
6.2 在mysql数据库里,重命名event和proc 表 为event_backup_57 和proc_backup_57

如果这步失败,服务会恢复对--datadir的所有更改。这种情况,你应该删除所有redo log ,用相同的—datadir 开启数据库,并修改问题。用slow shutdown关闭 mysql5.7并再次开启msyql8.0 服务

7、 如果8.0开启成功,执行mysql_upgrade
mysql_upgrade -u root –p
mysql_upgrade:
7.1会检查所有与当前版本不兼容的数据库里的表,
7.2执行5.7升到8.0之间需求的修改,
7.3 更新Performance Schema, INFORMATION_SCHEMA 和 sys schema objects到8.0

Mysql_upgrade 不更新help table中内容

8、 重启数据库,确保所有的更改生效
mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/existing-datadir