imgprix.com
Following is the list that needs to be reviewed and cleaned up before upgrading to MySQL 8.0
The transactional Data Dictionary DD.
In MySQL 8.0.* several new DD tables are created in MySQL schema so the user tables which conflicting names need to dropped or renamed prior to upgrade.
Now we have to check which table/tables needs to be changed,
# we can do this by using below information_schema query.
mysql> 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');
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| mysql | catalogs |
+--------------+------------+
1 row in set (0.00 sec)
Okay! so here we have to change the name of the table or to remove!!!Lets change the name of the table and proceed…
mysql>ALTER TABLE catalogs RENAME user_catalogs; Query OK, 0 rows affected (0.05 sec) Or we can drop the table as well Note: Before dropping the table take the backup of the table. mysql> DROP TABLE catalogs; Query OK, 0 rows affected (0.06 sec
- Non-Native Partitioning
In MySQL 8.0.* Non-Native Partitioning is completely removed, so we need to identify those tables and should be removeOr altered to use InnoDB engine.
Here we can identify the tables using the below query.
mysql> SELECT TABLE_SCHEMA, FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb', 'ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| partitions | p1_key |
+--------------+------------+
mysql> show create table p1_key;
CREATE TABLE `p1_key` (
`id` int(11) DEFAULT NULL,
`purchased` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
SUBPARTITION BY HASH ( TO_DAYS(purchased))
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2000) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
Such table/tables should be altered.
ALTER TABLE p1_key ENGINE = INNODB; Query OK, 0 rows affected (0.09 sec) OR ALTER TABLE p1_key REMOVE PARTITIONING; Query OK, 0 rows affected (0.06 sec)
Create Trigger prior to 5,0.17 did not support definer attributes.such triggers have persisted until MySQL 5.7 can not be upgrade.
These triggers can be found out by running mysqlcheck with check-upgrade option.
[root@mydbopslabs15 mysql]# ./mysqlcheck --user=root --socket=/var/lib/mysql/mysql.sock --databases triggers --check-upgrade [root@mydbopslabs15 mysql]# ./client/mysqlcheck --user=root --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --databases triggers --check-upgrade triggers.t1 Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. Warning : No definer attribute for trigger 'triggers'.'t1_bi'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert_1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_update_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. Warning : No definer attribute for trigger 'triggers'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. status : OK triggers.t2 OK mysql> check table t1; +-------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_before_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. | | triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'t1_bi'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. | | triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_insert_1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. | | triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. | | triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_insert_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. | | triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_before_update_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. | | triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_before_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. | | triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. | | triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. | | triggers.t1 | check | status | OK | +-------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.01 sec) mysql> select definer, trigger_name from INFORMATION_SCHEMA.TRIGGERS where definer=''; +---------+------------------------+ | definer | trigger_name | +---------+------------------------+ | | trg_t1_before_insert | | | t1_bi | | | trg_t1_after_insert_1 | | | trg_t1_after_insert | | | trg_t1_after_insert_3 | | | trg_t1_before_update_3 | | | trg_t1_before_update | | | trg_t1_after_update | | | trg1 | +---------+------------------------+ 9 rows in set (0.02 sec) mysql>
So, these tables should be dumped and reloaded to fix the issue
- In MySQL 8.0.* constraint name exceeds 64 chars is not supported
Such tables can identified by running the following query:
mysql> 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); +--------------+----------------------------------------------------------------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------+----------------------------------------------------------------------+ | test | имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк | +--------------+----------------------------------------------------------------------+ 1 row in set (0.00 sec) ----------------------- Current Version: mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.23 | +-----------+ 1 row in set (0.02 sec)
Prerequisites:
1. Install MySQL shell utility. [root@mydbopslabs15 mysql]# yum install mysql-shell 2. connect your MySQL Shell using 'mysqlsh' [root@mydbopslabs15 mysql]# mysqlsh MySQL Shell 8.0.12 Copyright (c) 2016, 2018, 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 '\?' for help; '\quit' to exit. MySQL JS >
Run the shell utility by the command
Ex:
MySQL JS > util.checkForServerUpgrade("root@localhost:3306");
Please provide the password for 'root@localhost:3306': ***********
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): y
The MySQL server at localhost:3306 will now be checked for compatibility issues for upgrade to MySQL 8.0...
MySQL version: 5.7.23 - MySQL Community Server (GPL)
1) Usage of old temporal type
No issues found
2) Usage of db objects with names conflicting with reserved keywords in 8.0
No issues found
3) Usage of utf8mb3 charset
No issues found
4) Table names in the mysql schema conflicting with new tables in 8.0
No issues found
5) Foreign key constraint names longer than 64 characters
No issues found
6) Usage of obsolete MAXDB sql_mode flag
No issues found
7) Usage of obsolete sql_mode flags
No issues found
8) Usage of partitioned tables in shared tablespaces
No issues found
9) Usage of removed functions
No issues found
10) Issues reported by 'check table x for upgrade' command
No issues found
Errors: 0
Warnings: 0
Notices: 0
No known compatibility errors or issues for upgrading the target server to MySQL 8 were found.
0
Take a full backup of all the databases using logical or physical, in my case i am using mysqldump[xtrabackup or mysqldump]
[root@mydbopslabs15 mysql]# mysqldump -uroot -p'**********' --all-databases --complete-insert --triggers --events >all-db.sql
Stop mysqld service
[root@mydbopslabs15 mysql]# service mysqld status Redirecting to /bin/systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) since Sat 2018-09-15 12:40:03 UTC; 18min ago
Rename data diractory and my.cnf as old_mysql & old_my.cnf
[root@mydbopslabs15 lib]# mv mysql old_mysql [root@mydbopslabs15 lib]# mv /etc/my.cnf /etc/_old_my.cnf [root@mydbopslabs15 etc]# less /etc/ | grep -i cnf -rw-r--r--. 1 root root 960 Jun 8 10:27 old_my.cnf [root@mydbopslabs15 etc]# less /etc/ | grep -i mysql -rw-r--r--. 1 root root 960 Jun 8 10:27 old_mysql
Enable MySQL 8.0 repo and disable MySQL 5.7 repo in /etc/yum.reos.d
[root@mydbopslabs15 vagrant]# vi /etc/yum.repos.d/mysql-community.repo [mysql57-community] name=MySQL 5.7 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/ enabled=0 //Disabled gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql [mysql80-community] name=MySQL 8.0 Community Server baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/ enabled=1 //Enabled gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Run the yum update command for mysql-server
[root@mydbopslabs15 etc]# yum update mysql-server Dependencies Resolved =================================================================================================================================================================================================================== Package Arch Version Repository Size =================================================================================================================================================================================================================== Updating: mysql-community-server x86_64 8.0.12-1.el7 mysql80-community 349 M Updating for dependencies: mysql-community-client x86_64 8.0.12-1.el7 mysql80-community 26 M mysql-community-common x86_64 8.0.12-1.el7 mysql80-community 541 k mysql-community-libs x86_64 8.0.12-1.el7 mysql80-community 2.2 M Transaction Summary =================================================================================================================================================================================================================== Upgrade 1 Package (+3 Dependent packages) Updated: mysql-community-server.x86_64 0:8.0.12-1.el7 Dependency Updated: mysql-community-client.x86_64 0:8.0.12-1.el7 mysql-community-common.x86_64 0:8.0.12-1.el7 mysql-community-libs.x86_64 0:8.0.12-1.el7 Complete [root@mydbopslabs15 etc]# mysql -V mysql Ver 8.0.12 for Linux on x86_64 (MySQL Community Server - GPL)
Remove newly created datadir(mysql) & my.cnf file
[root@mydbopslabs15 lib]# rm -rf mysql [root@mydbopslabs15 lib]# rm -rf /etc/my.cnf
Remane the old_mysql and old_my.cnf file
[root@mydbopslabs15 lib]# mv old_mysql/ mysql [root@mydbopslabs15 lib]# mv /etc/old_my.cnf /etc/my.cnf
Start MySQL service
[root@mydbopslabs15 mysql]# service mysqld start Redirecting to /bin/systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Sat 2018-09-15 13:04:02 UTC; 14s ago
login MySQL
mydbopslabs15 lib]# mysql -uroot -p nter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.12 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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> \s -------------- mysql Ver 8.0.12 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 9 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.12 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 51 sec
Exit MySQL(\q)
Now check for mysql upgrade
[root@mydbopslabs15 lib]# mysql_upgrade -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 Upgrade process completed successfully.
Check the MySQL Version
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.02 sec)
DBA DIARY 