MySQL5.7.27升级到MySQL8.0.13
环境检查
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.27 |
+-----------+
1 row in set (0.00 sec)
mysql> exit
Bye
检查不在使用的数据库类型或触发器是否缺失或空的定义符忙活着无效的创建上下文
[root@my5 ~]# mysqlcheck -u root -p --all-databases --check-upgrade
Enter password:
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
sys.sys_config OK
test.a OK
test.b OK
检查数据字典
[root@my5 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.27 MySQL Community Server (GPL)
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> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb','ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| test | p_range |
+--------------+------------+
1 row in set, 1 warning (0.01 sec)
mysql> alter table p_range engine= innodb;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb','ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';
Empty set (0.01 sec)
不能有 MySQL 8.0 的数据字典使用过的表名称，要识别具有这些名称的表
mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE LOWER(TABLE_SCHEMA) = 'mysql' AND LOWER(TABLE_NAME) IN ('catalogs', 'character_set', 'collations', 'column_type_elements', 'columns', 'events', 'foreign_key_column_usage', 'foreign_keys', 'index_column_usage', 'index_partitions', 'index_stats', 'indexes', 'parameter_type_elements', 'parameters', 'routines', 'schemeta', 'st_spatial_reference_systems', 'table_partition_values', 'table_partitions', 'table_stats', 'tables', 'tablespace_files', 'tablespaces', 'triggers', 'version', 'view_routine_usage', 'view_table_usage');
Empty set (0.00 sec)
不能存在外间的约束名称超过 64 个字符的表
mysql> SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE LENGTH(CONSTRAINT_NAME) > 64;
Empty set (0.01 sec)
mysql> exit
Bye
停止MySQL服务下载MySQL8.0.13二进制安装包
[root@my5 ~]# systemctl stop mysqld.service
[root@my5 ~]# cd /usr/local/
bin/ etc/ games/ include/ lib/ lib64/ libexec/ sbin/ share/ src/
[root@my5 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz -P /opt
--2020-04-06 20:23:12-- https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz
正在解析主机 dev.mysql.com (dev.mysql.com)... 137.254.60.11
正在连接 dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 302 Found
位置：https://cdn.mysql.com//archives/mysql-8.0/mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz [跟随至新的 URL]
--2020-04-06 20:23:13-- https://cdn.mysql.com//archives/mysql-8.0/mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 23.1.245.43
正在连接 cdn.mysql.com (cdn.mysql.com)|23.1.245.43|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：393852364 (376M) [text/plain]
正在保存至: “/opt/mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz”
100%[====================================================================================================================>] 393,852,364 485KB/s 用时 12m 39s
2020-04-06 20:35:53 (507 KB/s) - 已保存 “/opt/mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz” [393852364/393852364])
[root@my5 ~]# xz -d /opt/mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz
[root@my5 ~]# ls /opt
mysql-8.0.13-linux-glibc2.12-x86_64.tar
[root@my5 opt]# cd /usr/local/
[root@my5 local]# ls
bin etc games include lib lib64 libexec sbin share src
[root@my5 local]# tar xf /opt/mysql-8.0.13-linux-glibc2.12-x86_64.tar
[root@my5 local]# ls
bin etc games include lib lib64 libexec mysql-8.0.13-linux-glibc2.12-x86_64 sbin share src
[root@my5 local]# cd mysql-8.0.13-linux-glibc2.12-x86_64/
[root@my5 mysql-8.0.13-linux-glibc2.12-x86_64]# ls
bin docs include lib LICENSE LICENSE.router man README README.router share support-files
[root@my5 mysql-8.0.13-linux-glibc2.12-x86_64]# cd ..
[root@my5 local]# pwd
/usr/local
[root@my5 local]# ln -sf mysql-8.0.13-linux-glibc2.12-x86_64 mysql
[root@my5 local]# ls
bin etc games include lib lib64 libexec mysql mysql-8.0.13-linux-glibc2.12-x86_64 sbin share src
[root@my5 local]# cd mysql
[root@my5 mysql]# ls
bin docs include lib LICENSE LICENSE.router man README README.router share support-files
[root@my5 mysql]# mkdir mysql-files
[root@my5 mysql]# chmod 750 mysql-files
[root@my5 mysql]# chown -R mysql .
[root@my5 mysql]# chgrp -R mysql .
[root@my5 mysql]# ls -lh
总用量 452K
drwxr-xr-x. 2 mysql mysql 4.0K 4月 6 20:40 bin
drwxr-xr-x. 2 mysql mysql 86 4月 6 20:40 docs
drwxr-xr-x. 3 mysql mysql 4.0K 4月 6 20:40 include
drwxr-xr-x. 6 mysql mysql 4.0K 4月 6 20:40 lib
-rw-r--r--. 1 mysql mysql 328K 10月 7 2018 LICENSE
-rw-r--r--. 1 mysql mysql 100K 10月 7 2018 LICENSE.router
drwxr-xr-x. 4 mysql mysql 30 4月 6 20:40 man
drwxr-x---. 2 mysql mysql 6 4月 6 20:41 mysql-files
-rw-r--r--. 1 mysql mysql 687 10月 7 2018 README
-rw-r--r--. 1 mysql mysql 700 10月 7 2018 README.router
drwxr-xr-x. 28 mysql mysql 4.0K 4月 6 20:40 share
drwxr-xr-x. 2 mysql mysql 90 4月 6 20:40 support-files
初始化MySQL8.0.13设置启动脚本及启动脚本
[root@my5 mysql]# bin/mysqld --initialize --user=mysql
[root@my5 mysql]# bin/mysql_ssl_rsa_setup
[root@my5 mysql]# cp support-files/mysql.server /etc/init.d/mysql
[root@my5 mysql]# cd support-files/mysql
mysqld_multi.server mysql-log-rotate mysql.server
[root@my5 mysql]# export PATH=$PATH:/usr/local/mysql/bin
[root@my5 mysql]# mkdir /etc/mysql
[root@my5 mysql]# cd /etc/my.cnf.d/
[root@my5 my.cnf.d]# ls
[root@my5 etc]# cd mysql
启动排错
一坑
[root@my5 mysql]# ls
[root@my5 mysql]# systemctl daemon-reload
[root@my5 mysql]# systemctl start mysql
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
[root@my5 mysql]# journalctl -xe
-- Subject: Unit NetworkManager-dispatcher.service has finished start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit NetworkManager-dispatcher.service has finished starting up.
--
-- The start-up result is done.
4月 06 20:37:56 my5 nm-dispatcher[6546]: req:1 'dhcp4-change' [ens33]: new request (3 scripts)
4月 06 20:37:56 my5 nm-dispatcher[6546]: req:1 'dhcp4-change' [ens33]: start running ordered scripts...
4月 06 20:45:26 my5 polkitd[5068]: Registered Authentication Agent for unix-process:6598:494849 (system bus name :1.34 [/usr/bin/pkttyagent --notify-fd 5 --fa
4月 06 20:45:26 my5 systemd[1]: Reloading.
4月 06 20:45:27 my5 polkitd[5068]: Unregistered Authentication Agent for unix-process:6598:494849 (system bus name :1.34, object path /org/freedesktop/PolicyK
4月 06 20:45:32 my5 polkitd[5068]: Registered Authentication Agent for unix-process:6616:495455 (system bus name :1.35 [/usr/bin/pkttyagent --notify-fd 5 --fa
4月 06 20:45:32 my5 systemd[1]: Starting LSB: start and stop MySQL...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has begun starting up.
4月 06 20:45:32 my5 mysql[6622]: my_print_defaults: Can't read dir of '/etc/mysql/conf.d/' (OS errno 2 - No such file or directory)
4月 06 20:45:32 my5 mysql[6622]: my_print_defaults: [ERROR] Fatal error in defaults handling. Program aborted!
4月 06 20:45:32 my5 mysql[6622]: **Starting MySQL.my_print_defaults: Can't read dir of '/etc/mysql/conf.d/' (OS errno 2 - No such file or directory)**
4月 06 20:45:32 my5 mysql[6622]: my_print_defaults: [ERROR] Fatal error in defaults handling. Program aborted!
4月 06 20:45:32 my5 mysql[6622]: my_print_defaults: Can't read dir of '/etc/mysql/conf.d/' (OS errno 2 - No such file or directory)
4月 06 20:45:33 my5 mysql[6622]: my_print_defaults: [ERROR] Fatal error in defaults handling. Program aborted!
4月 06 20:45:33 my5 mysql[6622]: Logging to '/usr/local/mysql/data/my5.err'.
4月 06 20:45:33 my5 mysql[6622]: ERROR! The server quit without updating PID file (/usr/local/mysql/data/my5.pid).
4月 06 20:45:33 my5 systemd[1]: mysql.service: control process exited, code=exited status=1
4月 06 20:45:33 my5 systemd[1]: Failed to start LSB: start and stop MySQL.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has failed.
--
-- The result is failed.
4月 06 20:45:33 my5 systemd[1]: Unit mysql.service entered failed state.
4月 06 20:45:33 my5 systemd[1]: mysql.service failed.
4月 06 20:45:34 my5 polkitd[5068]: Unregistered Authentication Agent for unix-process:6616:495455 (system bus name :1.35, object path /org/freedesktop/PolicyK
[
[root@my5 mysql]# /etc/mysql/conf.d/
-bash: /etc/mysql/conf.d/: 没有那个文件或目录
[root@my5 mysql]# mkdir -p /etc/mysql/conf.d/
二坑
[root@my5 mysql]# systemctl start mysql
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
[root@my5 mysql]# journalctl -xe
4月 06 21:44:26 my5 systemd[1]: Starting Network Manager Script Dispatcher Service...
-- Subject: Unit NetworkManager-dispatcher.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit NetworkManager-dispatcher.service has begun starting up.
4月 06 21:44:26 my5 dhclient[5445]: bound to 172.16.103.129 -- renewal in 719 seconds.
4月 06 21:44:26 my5 dbus[5070]: [system] Successfully activated service 'org.freedesktop.nm_dispatcher'
4月 06 21:44:26 my5 systemd[1]: Started Network Manager Script Dispatcher Service.
-- Subject: Unit NetworkManager-dispatcher.service has finished start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit NetworkManager-dispatcher.service has finished starting up.
--
-- The start-up result is done.
4月 06 21:44:26 my5 nm-dispatcher[7370]: req:1 'dhcp4-change' [ens33]: new request (3 scripts)
4月 06 21:44:26 my5 nm-dispatcher[7370]: req:1 'dhcp4-change' [ens33]: start running ordered scripts...
4月 06 21:46:03 my5 polkitd[5068]: Registered Authentication Agent for unix-process:7394:858544 (system bus name :1.46 [/usr/bin/pkttyagent --notify-fd 5 --fa
4月 06 21:46:03 my5 systemd[1]: Starting LSB: start and stop MySQL...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has begun starting up.
4月 06 21:46:03 my5 mysql[7400]: Starting MySQL.2020-04-06T13:46:03.987823Z mysqld_safe error: log-error set to '/var/logs/mysql/logerror.log', however file d
4月 06 21:46:04 my5 mysql[7400]: **ERROR! The server quit without updating PID file (/usr/local/mysql/data/mysql.pid).**
4月 06 21:46:04 my5 systemd[1]: mysql.service: control process exited, code=exited status=1
4月 06 21:46:04 my5 systemd[1]: Failed to start LSB: start and stop MySQL.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has failed.
--
-- The result is failed.
4月 06 21:46:04 my5 systemd[1]: Unit mysql.service entered failed state.
4月 06 21:46:04 my5 systemd[1]: mysql.service failed.
4月 06 21:46:04 my5 polkitd[5068]: Unregistered Authentication Agent for unix-process:7394:858544 (system bus name :1.46, object path /org/freedesktop/PolicyK
[
[root@my5 mysql.conf.d]# vi mysql.cnf
[root@my5 mysql.conf.d]# cd /usr/local/mysql/data/
-bash: cd: /usr/local/mysql/data/: 没有那个文件或目录
[root@my5 mysql]# cd ..
[root@my5 local]# cat /etc/mysql/mysql.conf.d/mysql.cnf
[mysqld]
user = mysql
pid-file = /usr/local/mysql/data/mysql.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /usr/local/mysql/data
basedir = /usr/local/mysql
log-error = /var/log/logerror.log
plugin-dir = /usr/local/mysql/lib/plugin
bind-address = 0.0.0.0
[root@my5 local]# cd /usr/local/mysql/data
[root@my5 data]# cd /var/run/mysqld/
[root@my5 mysqld]# cd /usr/local/mysql/data
[root@my5 data]# cd /usr/local/mysql
[root@my5 mysql]# cd /usr/local/mysql/lib/plugin
[root@my5 mysql]# cat /var/log/logerror.log
2020-04-06T14:05:27.344516Z 0 [Warning] [MY-010143] [Server] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
2020-04-06T14:05:27.344579Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) starting as process 9505
2020-04-06T14:05:27.354045Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2020-04-06T14:05:27.354192Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-04-06T14:05:27.354202Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-06T14:05:27.354665Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
2020-04-06T14:07:19.608780Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-04-06T14:07:19.608837Z 0 [Warning] [MY-010143] [Server] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
2020-04-06T14:07:19.608896Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) starting as process 9816
2020-04-06T14:07:19.617567Z 1 **[ERROR] [MY-011011] [Server] Failed to find valid data directory.**
2020-04-06T14:07:19.617673Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-04-06T14:07:19.617683Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-06T14:07:19.618105Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
[root@my5 mysql]# cd /usr/local/mysql/data/
[root@my5 data]# ls
binlog.index
[root@my5 data]# ls -lh
总用量 0
-rw-r-----. 1 mysql mysql 0 4月 6 21:56 binlog.index
[root@my5 data]# cd ..
[root@my5 mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8mb4
[root@my5 mysql]# cd /var/lib/mysql
[root@my5 mysql]# ls
auto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 mysql_upgrade_info private_key.pem server-cert.pem sys
ca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 mysql performance_schema public_key.pem server-key.pem test
[root@my5 mysql]# cd mysql
[root@my5 mysql]# ls
columns_priv.frm func.MYD help_topic.frm proc.MYI slave_relay_log_info.frm time_zone_leap_second.ibd
columns_priv.MYD func.MYI help_topic.ibd procs_priv.frm slave_relay_log_info.ibd time_zone_name.frm
columns_priv.MYI general_log.CSM innodb_index_stats.frm procs_priv.MYD slave_worker_info.frm time_zone_name.ibd
db.frm general_log.CSV innodb_index_stats.ibd procs_priv.MYI slave_worker_info.ibd time_zone_transition.frm
db.MYD general_log.frm innodb_table_stats.frm proxies_priv.frm slow_log.CSM time_zone_transition.ibd
db.MYI gtid_executed.frm innodb_table_stats.ibd proxies_priv.MYD slow_log.CSV time_zone_transition_type.frm
db.opt gtid_executed.ibd ndb_binlog_index.frm proxies_priv.MYI slow_log.frm time_zone_transition_type.ibd
engine_cost.frm help_category.frm ndb_binlog_index.MYD server_cost.frm tables_priv.frm user.frm
engine_cost.ibd help_category.ibd ndb_binlog_index.MYI server_cost.ibd tables_priv.MYD user.MYD
event.frm help_keyword.frm plugin.frm servers.frm tables_priv.MYI user.MYI
event.MYD help_keyword.ibd plugin.ibd servers.ibd time_zone.frm
event.MYI help_relation.frm proc.frm slave_master_info.frm time_zone.ibd
func.frm help_relation.ibd proc.MYD slave_master_info.ibd time_zone_leap_second.frm
[root@my5 mysql]# cd ..
[root@my5 mysql]# ls
auto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 mysql_upgrade_info private_key.pem server-cert.pem sys
ca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 mysql performance_schema public_key.pem server-key.pem test
[root@my5 mysql]# cd ..
[root@my5 lib]# ls
alternatives chrony dhclient initramfs machines mlocate mysql-files NetworkManager plymouth postfix rpm-state stateless systemd up2date yum
authconfig dbus games logrotate misc mysql mysql-keyring os-prober polkit-1 rpm rsyslog supportinfo tuned vmware
[root@my5 lib]# cd /etc/mysql/mysql.conf.d/
[root@my5 mysql.conf.d]# ls
mysql.cnf
修改配置文件中的数据文件路径
[root@my5 mysql.conf.d]# cat mysql.cnf
[mysqld]
user = mysql
pid-file = /usr/local/mysql/data/mysql.pid
socket = /var/run/mysqld/mysqld.sock
datadir =/var/lib/mysql/
basedir = /usr/local/mysql
log-error = /var/log/logerror.log
plugin-dir = /usr/local/mysql/lib/plugin
bind-address = 0.0.0.0
[root@my5 mysql.conf.d]# systemctl start mysql
[root@my5 mysql.conf.d]# systemctl status mysql
● mysql.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysql; bad; vendor preset: disabled)
Active: active (running) since 一 2020-04-06 22:15:04 CST; 5s ago
Docs: man:systemd-sysv-generator(8)
Process: 10191 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=0/SUCCESS)
CGroup: /system.slice/mysql.service
├─10210 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=root --datadir=/var/lib/mysql/ --pid-file=/usr/local/mysql/data/mysql.pid
└─10485 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=root --l...
4月 06 22:15:00 my5 systemd[1]: Starting LSB: start and stop MySQL...
4月 06 22:15:04 my5 mysql[10191]: Starting MySQL.... SUCCESS!
4月 06 22:15:04 my5 systemd[1]: Started LSB: start and stop MySQL.
[root@my5 mysql.conf.d]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@my5 mysql.conf.d]# mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.13 MySQL Community Server - GPL
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> select version();
+-----------+
| version() |
+-----------+
| 8.0.13 |
+-----------+
1 row in set (0.00 sec)
mysql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql> use mysql
Database changed
mysql> show tables;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec)
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper
Connection id: 9
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.13 MySQL Community Server - GPL
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 7 min 43 sec
Threads: 2 Questions: 19 Slow queries: 0 Opens: 121 Flush tables: 2 Open tables: 97 Queries per second avg: 0.041
--------------
mysql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql> SET GLOBAL innodb_fast_shutdown = 1;
Query OK, 0 rows affected (0.00 sec)
启动后日志报错
[root@my5 mysql.conf.d]# cat /var/log/logerror.log
2020-04-06T13:53:35.090405Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-04-06T13:53:35.090542Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) starting as process 8514
mysqld: File './binlog.index' not found (OS errno 13 - Permission denied)
2020-04-06T13:53:35.104207Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-06T13:53:35.104321Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
2020-04-06T13:56:50.142640Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-04-06T13:56:50.142757Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) starting as process 8849
2020-04-06T13:56:50.165160Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2020-04-06T13:56:50.165698Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-04-06T13:56:50.165749Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-06T13:56:50.166617Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
2020-04-06T14:05:12.347574Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-04-06T14:05:12.347633Z 0 [Warning] [MY-010143] [Server] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
2020-04-06T14:05:12.347696Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) starting as process 9176
2020-04-06T14:05:12.365624Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2020-04-06T14:05:12.365765Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-04-06T14:05:12.365808Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-06T14:05:12.366313Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
2020-04-06T14:05:27.344409Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-04-06T14:05:27.344516Z 0 [Warning] [MY-010143] [Server] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
2020-04-06T14:05:27.344579Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) starting as process 9505
2020-04-06T14:05:27.354045Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2020-04-06T14:05:27.354192Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-04-06T14:05:27.354202Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-06T14:05:27.354665Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
2020-04-06T14:07:19.608780Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-04-06T14:07:19.608837Z 0 [Warning] [MY-010143] [Server] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
2020-04-06T14:07:19.608896Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) starting as process 9816
2020-04-06T14:07:19.617567Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2020-04-06T14:07:19.617673Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-04-06T14:07:19.617683Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-06T14:07:19.618105Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
2020-04-06T14:12:13.911935Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-04-06T14:12:13.911991Z 0 [Warning] [MY-010143] [Server] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
2020-04-06T14:12:13.912047Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) starting as process 10165
2020-04-06T14:12:13.922016Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2020-04-06T14:12:13.922201Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-04-06T14:12:13.922211Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-06T14:12:13.922647Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
2020-04-06T14:15:00.467674Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-04-06T14:15:00.467730Z 0 [Warning] [MY-010143] [Server] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
2020-04-06T14:15:00.467787Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) starting as process 10485
2020-04-06T14:15:02.372127Z 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-04-06T14:15:03.408499Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-04-06T14:15:03.414220Z 0 [ERROR] [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-001146 - Table 'mysql.component' doesn't exist
2020-04-06T14:15:03.414247Z 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-003543 - The mysql.component table is missing or has an incorrect definition.
2020-04-06T14:15:03.419483Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.user].
2020-04-06T14:15:03.419507Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.db].
2020-04-06T14:15:03.419512Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.tables_priv].
2020-04-06T14:15:03.419516Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.columns_priv].
2020-04-06T14:15:03.419519Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.procs_priv].
2020-04-06T14:15:03.419522Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.proxies_priv].
2020-04-06T14:15:03.419803Z 0 [ERROR] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 50, found 45. The table is probably corrupted
2020-04-06T14:15:03.419827Z 0 [Warning] [MY-010966] [Server] ACL table mysql.role_edges missing. Some operations may fail.
2020-04-06T14:15:03.419832Z 0 [Warning] [MY-010966] [Server] ACL table mysql.default_roles missing. Some operations may fail.
2020-04-06T14:15:03.419835Z 0 [Warning] [MY-010966] [Server] ACL table mysql.global_grants missing. Some operations may fail.
2020-04-06T14:15:03.419839Z 0 [Warning] [MY-010966] [Server] ACL table mysql.password_history missing. Some operations may fail.
2020-04-06T14:15:03.421196Z 0 [ERROR] [MY-010965] [Server] Missing system table mysql.global_grants; please run mysql_upgrade to create it.
2020-04-06T14:15:03.424439Z 0 [Warning] [MY-010727] [Server] System table 'func' is expected to be transactional.
2020-04-06T14:15:03.426499Z 0 [Warning] [MY-010405] [Repl] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2020-04-06T14:15:03.426566Z 0 [ERROR] [MY-010422] [Repl] Error in checking mysql.slave_master_info repository info type of TABLE.
2020-04-06T14:15:03.426580Z 0 [ERROR] [MY-010415] [Repl] Error creating master info: Error checking repositories.
2020-04-06T14:15:03.426585Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2020-04-06T14:15:03.426622Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
2020-04-06T14:15:03.429263Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.13' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-04-06T14:15:03.632312Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-04-06T14:19:55.221062Z 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-04-06T14:19:56.762322Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
2020-04-06T14:20:12.733040Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-04-06T14:20:12.733093Z 0 [Warning] [MY-010143] [Server] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
2020-04-06T14:20:12.733148Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) starting as process 10894
2020-04-06T14:20:12.982488Z 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-04-06T14:20:13.067382Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-04-06T14:20:13.070809Z 0 [ERROR] [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-001146 - Table 'mysql.component' doesn't exist
2020-04-06T14:20:13.070855Z 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-003543 - The mysql.component table is missing or has an incorrect definition.
2020-04-06T14:20:13.075629Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.user].
2020-04-06T14:20:13.075655Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.db].
2020-04-06T14:20:13.075662Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.tables_priv].
2020-04-06T14:20:13.075667Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.columns_priv].
2020-04-06T14:20:13.075671Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.procs_priv].
2020-04-06T14:20:13.075676Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.proxies_priv].
2020-04-06T14:20:13.076025Z 0 [ERROR] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 50, found 45. The table is probably corrupted
2020-04-06T14:20:13.076067Z 0 [Warning] [MY-010966] [Server] ACL table mysql.role_edges missing. Some operations may fail.
2020-04-06T14:20:13.076073Z 0 [Warning] [MY-010966] [Server] ACL table mysql.default_roles missing. Some operations may fail.
2020-04-06T14:20:13.076078Z 0 [Warning] [MY-010966] [Server] ACL table mysql.global_grants missing. Some operations may fail.
2020-04-06T14:20:13.076082Z 0 [Warning] [MY-010966] [Server] ACL table mysql.password_history missing. Some operations may fail.
2020-04-06T14:20:13.076426Z 0 **[ERROR] [MY-010965] [Server] Missing system table mysql.global_grants; please run mysql_upgrade to create it.**
2020-04-06T14:20:13.081105Z 0 [Warning] [MY-010727] [Server] System table 'func' is expected to be transactional.
2020-04-06T14:20:13.082938Z 0 [Warning] [MY-010405] [Repl] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2020-04-06T14:20:13.082970Z 0 [ERROR] [MY-010422] [Repl] Error in checking mysql.slave_master_info repository info type of TABLE.
2020-04-06T14:20:13.082980Z 0 [ERROR] [MY-010415] [Repl] Error creating master info: Error checking repositories.
2020-04-06T14:20:13.082986Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2020-04-06T14:20:13.082991Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
2020-04-06T14:20:13.084374Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.13' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-04-06T14:20:13.237027Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
[root@my5 lib64]# mysql_upgrade -u root -p
mysql_upgrade: error while loading shared libraries: libssl.so.1.0.0: cannot open shared object file: No such file or directory
[root@my5 lib64]# locate mysql_upgrade
/usr/bin/mysql_upgrade
/usr/local/mysql-8.0.13-linux-glibc2.12-x86_64/bin/mysql_upgrade
/usr/local/mysql-8.0.13-linux-glibc2.12-x86_64/man/man1/mysql_upgrade.1
/usr/share/man/man1/mysql_upgrade.1.gz
/var/lib/mysql/mysql_upgrade_info
[root@my5 lib64]# /usr/local/mysql-8.0.13-linux-glibc2.12-x86_64/bin/mysql_upgrade -h 127.0.0.1 -u root -p
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK
mysql.engine_cost OK
mysql.func OK
mysql.general_log OK
mysql.global_grants OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.password_history OK
mysql.plugin OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.role_edges OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
sys.sys_config OK
test.a OK
test.b OK
test.c OK
test.p_range OK
Upgrade process completed successfully.
Checking if update is needed.
[root@my5 lib64]# mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.13 MySQL Community Server - GPL
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> \u 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
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a |
| b |
| c |
| p_range |
+----------------+
4 rows in set (0.00 sec)
mysql> select * from p_range
-> ;
+----+----------+
| id | name |
+----+----------+
| 1 | deitylee |
| 7 | salene |
+----+----------+
2 rows in set (0.01 sec)
mysql>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
