Thursday, 21 March 2019

MySQL Upgrade Checklist

Standalone DB Instance Upgrade Checklist :
1) Querying system configuration ( GLOBAL_VARIABLES, SESSION_VATIABLES ) & system status ( GLOBAL_STATUS, SESSION_STATUS ) tables from information_schema has to be changed to performance_schema. 
The INFORMATION_SCHEMA has tables that contain system and status variable information. As of MySQL 5.7.6, the Performance Schema also contains system and status variable tables. The Performance Schema tables are intended to replace theINFORMATION_SCHEMA tables, which are deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.
2) As of MySQL 5.7.6, for Linux systems on which MySQL is installed using RPM packages, server startup and shutdown now is managed using systemd rather than mysqld_safe, and mysqld_safe is not installed. This may require some adjustment to the manner in which you specify server options. 
3) The location of mysql_install_db becomes less material as of MySQL 5.7.6 because as of that version it is deprecated in favor of mysqld --initialize (or mysqld --initialize-insecure). 
In MySQL 5.7.5, the executable binary version of mysql_install_db is located in the bin installation directory, whereas the Perl version was located in the scripts installation directory. For upgrades from an older version of MySQL, you may find a version in both directories. To avoid confusion, remove the version in the scripts directory. For fresh installations of MySQL 5.7.5 or later, mysql_install_db is only found in the bin directory, and the scripts directory is no longer present.
4) Strict SQL mode for transnational storage engines (STRICT_TRANS_TABLES) is now enabled by default. The changes to the default SQL mode result in a default sql_mode system variable value with these modes enabled:ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION.
5) The Password column of the mysql.user table was removed in MySQL 5.7.6. All credentials are stored in the authentication_string column, including those formerly stored in the Password column.
6) As of MySQL 5.7.5, support for passwords that use the older pre-4.1 password hashing format is removed. Applications that use any feature no longer supported must be modified.
For the old_passwords system variable, a value of 1 (produce pre-4.1 hash values) is no longer permitted.
The --secure-auth option to the server and client programs is the default, but is now a no-op. It is deprecated and will be removed in a future MySQL release.
The --skip-secure-auth option to the server and client programs is no longer supported and using it produces an error.
The secure_auth system variable permits only a value of 1; a value of 0 is no longer permitted.
The OLD_PASSWORD() function is removed.
7) In MySQL 5.6.6, the YEAR(2) data type was deprecated. In MySQL 5.7.5, support for YEAR(2) is removed. 
8) Upgrade temporal columns as part of MySQL Upgrade.
As of MySQL 5.7.7, CHECK TABLE ... FOR UPGRADE reports a table as needing a rebuild if it contains old temporal columns in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision) and theavoid_temporal_upgrade system variable is disabled. This helps mysql_upgrade to detect and upgrade tables containing old temporal columns. If avoid_temporal_upgrade is enabled, FOR UPGRADE ignores the old temporal columns present in the table; consequently, mysql_upgrade does not upgrade them.
9)  As of MySQL 5.7.2, the server requires account rows in the mysql.user table to have a nonempty plugincolumn value and disables accounts with an empty value.
10) It is possible for a column DEFAULT value to be valid for the sql_mode value at table-creation time but invalid for the sql_mode value when rows are inserted or updated.
11) Several changes were made to the audit log plugin for better compatibility with Oracle Audit Vault. For upgrading purpose, the main issue is that the default format of the audit log file has changed.
If you previously used an older version of the audit log plugin, use this procedure to avoid writing new-format log entries to an existing log file that contains old-format entries:
  1. Stop the server.
  2. Rename the current audit log file manually. This file will contain only old-format log entries.
  3. Update the server and restart it. The audit log plugin will create a new log file, which will contain only new-format log entries.
12) To simplify InnoDB tablespace discovery during crash recovery, new redo log record types were introduced in MySQL 5.7.5. This enhancement changes the redo log format. Before performing an in-place upgrade, perform a clean shutdown using an innodb_fast_shutdown setting of 0 or 1. A slow shutdown using innodb_fast_shutdown=0 is a recommended.
13) As of MySQL 5.7.9, the redo log header of the first redo log file (ib_logfile0) includes a format version identifier and a text string that identifies the MySQL version that created the redo log files. This enhancement changes the redo log format, requiring that MySQL be shutdown cleanly useing innodb_fast_shutdown=0.
14) In MySQL 5.7.9, DYNAMIC replaces COMPACT as the implicit default row format for InnoDB tables. A new configuration option,innodb_default_row_format, specifies the default InnoDB row format.
After upgrading to 5.7.9, any new tables that you create will use the row format defined by innodb_default_row_format unless you explicitly define a row format (ROW_FORMAT).
For existing tables that do not explicitly define a ROW_FORMAT option or that use ROW_FORMAT=DEFAULT, any operation that rebuilds a table also silently changes the row format of the table to the format defined by innodb_default_row_format. Otherwise, existing tables retain their current row format setting.
15) Need to understand about the optimizer changes from SQL Changes part of the document : https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html
16) In MySQL 5.7 memory allocation for Performance Schema buffers doesn’t happen at server start-up but is instead based on the actual runtime requirement.
Reference : http://mysqlserverteam.com/new-in-mysql-5-7-performance-schema-scalable-memory-allocation/
17) SHOW ENGINE INNODB MUTEX output is removed. Comparable information can be generated by creating views on Performance Schema tables.
18) The InnoDB Tablespace Monitor and InnoDB Table Monitor are removed. For the Table Monitor, equivalent information can be obtained from InnoDB INFORMATION_SCHEMA tables.
19) The innodb_use_sys_malloc and innodb_additional_mem_pool_size system variables, which were deprecated in MySQL 5.6.3, were removed.
20) log-warnings: Log some noncritical warnings to the log file. Deprecated in MySQL 5.7.2.
21) log_backward_compatible_user_definitions: Whether to log CREATE/ALTER USER, GRANT in backward-compatible fashion. Removed in MySQL 5.7.9.
22) thread_concurrency: Permits the application to give the threads system a hint for the desired number of threads that should be run at the same time. Removed in MySQL 5.7.2.
23) mysql_upgrade should not be used when the server is running with --gtid-mode=ON. See GTID mode and mysql_upgrade for more information.
24) If you have created a user-defined function (UDF) with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different nonconflicting name. 

Features Added in MySQL 5.7 :

2) MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password. For more information, see Section 6.3.7, “Password Management”.
3) Administrators can lock and unlock accounts for better control over who can log in. For more information, see Section 6.3.11, “User Account Locking”.
4) To make it easier to support secure connections, MySQL servers compiled using OpenSSL can automatically generate missing SSL and RSA certificate and key files at startup. See Section 6.4.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”. MySQL distributions include a mysql_ssl_rsa_setup utility that can be invoked manually to create SSL and RSA key and certificate files. For more information, see Section 4.4.5, “mysql_ssl_rsa_setup — Create SSL/RSA Files”.
5) Online ALTER TABLE.  ALTER TABLE now supports a RENAME INDEX clause that renames an index. The change is made in place without a table-copy operation. It works for all storage engines. 
6) InnoDB temporary table metadata is no longer stored to InnoDB system tables. Instead, a new table,INNODB_TEMP_TABLE_INFO, provides users with a snapshot of active temporary tables. The table contains metadata and reports on all user and system-created temporary tables that are active within a given InnoDB instance. The table is created when the first SELECT statement is run against it.
7) There is now a separate tablespace for all non-compressed InnoDB temporary tables. The new tablespace is always recreated on server startup and is located in DATADIR by default. A newly added configuration file option,innodb_temp_data_file_path, allows for a user-defined temporary data file path.
8) A new type of non-redo undo log for both normal and compressed temporary tables and related objects now resides in the temporary tablespace. For more information, see Section 14.4.12.1, “Temporary Table Undo Logs”.
9) Support is added to InnoDB for full-text parser plugins. For information about full-text parser plugins, see Full-Text Parser Plugins and Section 28.2.4.4, “Writing Full-Text Parser Plugins”.
10) InnoDB supports multiple page cleaner threads for flushing dirty pages from buffer pool instances. A new system variable,innodb_page_cleaners, is used to specify the number of page cleaner threads. The default value of 1 maintains the previous configuration in which there is a single page cleaner thread.  Multi-threaded page cleaner support (innodb_page_cleaners) is extended to shutdown and recovery phases.
11) InnoDB supports the Transportable Tablespace feature for partitioned InnoDB tables and individual InnoDB table partitions. This enhancement eases backup procedures for partitioned tables and enables copying of partitioned tables and individual table partitions between MySQL instances.
12) The innodb_buffer_pool_size parameter is dynamic, allowing you to resize the buffer pool without restarting the server. The resizing operation, which involves moving pages to a new location in memory, is performed in chunks. Chunk size is configurable using the new innodb_buffer_pool_chunk_size configuration option. You can monitor resizing progress using the new Innodb_buffer_pool_resize_status status variable. For more information, see Configuring InnoDB Buffer Pool Size Online.
13) InnoDB performs a bulk load when creating or rebuilding indexes. This method of index creation is known as a “sorted index build”. This enhancement, which improves the efficiency of index creation, also applies to full-text indexes. A new global configuration option, innodb_fill_factor, defines the percentage of space on each page that is filled with data during a sorted index build, with the remaining space reserved for future index growth. For more information, see Section 14.8.2.3, “Sorted Index Builds”.
14) You can truncate undo logs that reside in undo tablespaces. This feature is enabled using the innodb_undo_log_truncateconfiguration option. For more information, see Section 14.7.8, “Truncating Undo Tablespaces”.
15) InnoDB supports native partitioning. Previously, InnoDB relied on the ha_partition handler, which creates a handler object for each partition. With native partitioning, a partitioned InnoDB table uses a single partition-aware handler object. This enhancement reduces the amount of memory required for partitioned InnoDB tables.
16) DYNAMIC replaces COMPACT as the implicit default row format for InnoDB tables. A new configuration option,innodb_default_row_format, specifies the default InnoDB row format. For more information, see Section 14.11.2, “Specifying the Row Format for a Table”.
17) JSON support.  Beginning with MySQL 5.7.8, MySQL supports a native JSON type. JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements. 
18) EXPLAIN can be used to obtain the execution plan for an explainable statement executing in a named connection. For more information, see Section 8.8.4, “Obtaining Execution Plan Information for a Named Connection”.
19) Triggers.  Previously, a table could have at most one trigger for each combination of trigger event (INSERT, UPDATE, DELETE) and action time (BEFORE, AFTER). This limitation has been lifted and multiple triggers are permitted. For more information, seeSection 23.3, “Using Triggers”.
20) Generated Columns.  MySQL now supports the specification of generated columns in CREATE TABLE and ALTER TABLEstatements. 
21) Database name rewriting with mysqlbinlog.  Renaming of databases by mysqlbinlog when reading from binary logs written using the row-based format is now supported using the --rewrite-db option added in MySQL 5.7.1.
22) Multi-source replication is now possible.  MySQL Multi-Source Replication adds the ability to replicate from multiple masters to a slave. MySQL Multi-Source Replication topologies can be used to back up multiple servers to a single server, to merge table shards, and consolidate data from multiple servers to a single server. 
23) The CREATE USER statement now supports an IF NOT EXISTS clause that causes the statement to produce a warning for each named account that already exists, rather than an error. The ALTER USER and DROP USER statements now support an IF EXISTSclause that cause the statements to produce a warning for each named account that does not exist, rather than an error. For details, see CREATE USER Syntax, ALTER USER Syntax, and DROP USER Syntax.
24) The maximum length of MySQL user names has been increased from 16 to 32 characters, which provides greater flexibility in choosing the user name part of MySQL account names. 
25) A new client program, mysqlpump, provides an alternative to mysqldump.
26) Previously, MySQL supported only the TLSv1 protocol for encrypting connections. TLS support is now extended to enable a higher level of connection encryption:
  • When compiled using OpenSSL 1.0.1 or higher, MySQL supports the TLSv1, TLSv1.1, and TLSv1.2 protocols.
  • When compiled using the bundled version of yaSSL, MySQL supports the TLSv1 and TLSv1.1 protocols.
27) During data directory initialization or upgrade, MySQL now creates a 'mysql.session'@'localhost' reserved account. This account is used internally by plugins to access the server. It is locked so that it cannot be used for client connections.
27) MySQL Enterprise Edition now supports LDAP pluggable authentication of MySQL users. This enables MySQL Server to use LDAP (Lightweight Directory Access Protocol) to authenticate MySQL users by accessing directory services such as X.500. For more information, see Pluggable Authentication.
28) MySQL Enterprise Edition now includes a keyring_aws plugin that communicates with the Amazon Web Services Key Management Service as a back end for key generation and uses a local file for key storage. For more information, see The MySQL Keyring.
29) Replication: The group_replication_transaction_size_limit variable was added to enable you to protect a group against large transactions causing a failure. (Bug #84785, Bug #25510757)
30) Replication: Group Replication now supports SAVEPOINT SQL transactions.

Features Deprecated in MySQL 5.7

1) Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is deprecated.
2) IDENTIFIED BY PASSWORD 'hash_string' syntax for CREATE USER and GRANT. Instead, use IDENTIFIED WITHauth_plugin AS 'hash_string' for CREATE USER and ALTER USER, where the 'hash_string' value is in a format compatible with the named plugin.
3) The PASSWORD() function is deprecated and should be avoided in any context. Thus, SET PASSWORD ... = PASSWORD('auth_string') syntax is also deprecated. SET PASSWORD ... = 'auth_string' syntax is not deprecated; nevertheless, ALTER USER is now the preferred statement for assigning passwords.
4) The query cache is deprecated. Deprecation includes these items:
5) The global character_set_database and collation_database system variables are deprecated and will be removed in a future version of MySQL.
6) The global scope for the sql_log_bin system variable has been deprecated, and this variable can now be set with session scope only. The statement SET GLOBAL SQL_LOG_BIN now produces an error. It remains possible to read the global value ofsql_log_bin, but doing so produces a warning. You should act now to remove from your applications any dependencies on reading this value; the global scope sql_log_bin is removed in MySQL 8.0.
7) GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators), but relying on implicit GROUP BY sorting in MySQL 5.7 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use To produce a given sort order, use explicit ASC or DESC designators for GROUP BY columns or provide an ORDER BY clause. GROUP BY sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.
8) The ENCRYPT(), ENCODE(), DECODE(), DES_ENCRYPT(), and DES_DECRYPT() encryption functions are deprecated. Consider usingAES_ENCRYPT() and AES_DECRYPT() instead. The --des-key-file option, the have_crypt system variable, the DES_KEY_FILEoption for the FLUSH statement, and the HAVE_CRYPT CMake option also are deprecated.
9) The INFORMATION_SCHEMA INNODB_LOCKS and INNODB_LOCK_WAITS tables are deprecated, to be removed in MySQL 8.0, which provides replacement Performance Schema tables.
10) Treatment of \N as a synonym for NULL in SQL statements is deprecated and is removed in MySQL 8.0; use NULL instead.
11) PROCEDURE ANALYSE() syntax is deprecated.
12) The mysql_plugin utility is deprecated. Alternatives include loading plugins at server startup using the --plugin-load or --plugin-load-add option, or at runtime using the INSTALL PLUGIN statement.
13) For RPM-based packages, the permissions used to create the data directory (/var/lib/mysql) have been changed from 755 to 751. 
14) With the introduction of the data dictionary in MySQL 8.0, the --ignore-db-dir option and ignore_db_dirs system variable became superfluous and were removed in that version. Consequently, they are now deprecated in MySQL 5.7.
15) The --ledir option now is accepted only on the command line, not in option files.
16) InnoDB: The innodb_undo_logs configuration option is deprecated and will be removed in a future release. Theinnodb_rollback_segments configuration option performs the same function and should be used instead.

Features Removed in MySQL 5.7

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)