2020年2月

下面测试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

安装Grid时,执行root.sh之前，在两个节点都打上补丁，再运行root.sh
补丁：18370031 （补丁要mos管方账号才能下）
$ /u01/app/11.2.0/grid/OPatch/opatch apply -local

现象：
Grid 11.2.0.4 Install fails when running root.sh on OL7, this affects both Oracle Clusterware and Oracle Restart Installation.

rootcrs.log/roothas.log confirms that ohasd/crsd failed to start

原因：

There is a known issue where OL7 expects to use systemd rather than initd for running processes and restarting them and root.sh does not handle this currently.

This was reported in the following Unpublished Bug

  Bug 18370031  - RC SCRIPTS (/ETC/RC.D/RC.* , /ETC/INIT.D/* ) ON OL7 FOR LUSTERWARE

解决办法：

Because Oracle Linux 7 (and Redhat 7) use systemd rather than initd for starting/restarting processes and runs them as a service the current software install of both 11.2.0.4 & 12.1.0.1 will not succeed because the ohasd process does not start properly.
In OL7 it needs to be set up as a service and patch fix for Bug 18370031 needs to be applied for this , BEFORE you run root.sh when prompted .

Need to apply the patch 18370031 for 11.2.0.4 .
And also its mentioned in 11gR2 Release Notes:https://docs.oracle.com/cd/E11882_01/relnotes.112/e23558/toc.htm#CJAJEBGG
During the Oracle Grid Infrastructure installation, you must apply patch 18370031 before configuring the software that is installed.
The timing of applying the patch is important and is described in detail in the Note 1951613.1 on My Oracle Support. This patch ensures that
the clusterware stack is configured to use systemd for clusterware processes, as Oracle Linux 7 uses systemd for all services.

  1. 进入debug模式

    perl -d p.pl

  2. 查看第几行代码

    l 10

查看函数代码

l 函数名
  1. 在函数f1上设置断点

    b f1

  2. 在指定44行设置断定

    b 44

  3. 查看所有断点

    L

  4. 单步调试，s进入调用函数，n直接下一语句不进入调用函数

    s
    n

7.继续直到指定的行

c 36

8.打印变量值

print $a

下载DBD-Oracle-1.80.tar.gz，地址如下：

https://metacpan.org/pod/DBD::Oracle

解压DBD-Oracle-1.80.tar.gz到/tmp下

在root用户添加如下环境变量：

 export ORACLE_BASE=/home/db/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0
export GRID_HOME=/home/db/grid/product/11.2.0
export LD_LIBRARY_PATH=:$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export LIBPATH=$LD_LIBRARY_PATH
export   PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GRID_HOME/bin:/usr/bin:/etc:/usr/sbin:

安装：

cd /tmp/DBD-Oracle-1.80
perl Makefile.PL
make
make install

测试perl访问oracle:

  #!/usr/bin/perl  
use DBI;  
$dbh = DBI->connect("dbi:Oracle:testdb", "system", "oracle") or die("DB connect error!\n");  
$sql = "select * from dual";  
$sth = $dbh->prepare($sql);  
$sth->execute() or die("error!");  
while(@rows = $sth->fetchrow_array)  
{  
        foreach(@rows)  
        {  
                print "$_\t";  
        }  
        print "\n";  
}  
$sth->finish;

显示'x'说明ok