• 时间:
  • 分类: mysql

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

标签: mysql5.7升级