互联技术 - 技术文章

【微软：Win10 月活跃设备同比呈两位数增长，疫情再次证明了 PC 的重要性】

技术文章 October 30, 2020

据外媒 Windows Central 报道，微软透露称，消费者和教育领域月活跃 Windows 10 设备数量同比呈现两位数增长，但官方没有公布具体的数字。纳德拉表示，在过去 9 个月全球疫情期间，Windows 和 PC 再次证明了自身的重要性，他认为包括远程学习、远程工作等多种活动，仍依赖于 PC 以及 PC 上所安装的软件去完成，微软将会把更多精力放在 Windows 系统开发上，并减少一些不必要的 Bug，以改善用户体验和口碑。

Upgrade Paths

技术文章 October 27, 2020

Upgrade from MySQL 5.7 to 8.0 is supported. However, upgrade is only supported between General Availability (GA) releases. For MySQL 8.0, it is required that you upgrade from a MySQL 5.7 GA release (5.7.9 or higher). Upgrades from non-GA releases of MySQL 5.7 are not supported.
Upgrading to the latest release is recommended before upgrading to the next version. For example, upgrade to the latest MySQL 5.7 release before upgrading to MySQL 8.0.
Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.6 to 8.0 is not supported.
Once a release series reaches General Availability (GA) status, upgrade within the release series (from one GA version to another GA version) is supported. For example, upgrading from MySQL 8.0.x to 8.0.y is supported. (Upgrade involving development-status non-GA releases is not supported.) Skipping a release is also supported. For example, upgrading from MySQL 8.0.x to 8.0.z is supported. MySQL 8.0.11 is the first GA status release within the MySQL 8.0 release series.

Using MySQL Shell 8.0.11 “upgrade checker” to upgrade from MySQL 5.7 to MySQL 8.0 successfully

技术文章 October 27, 2020

We are really excited about MySQL 8.0 new features (https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html) and our consultants spend several hours weekly, testing new features and doing research on how best we can create value for our customers from having those in production. Being an pure-play MySQL consulting, support and remote DBA services company, We are fully accountable for our customer database infrastructure operations performance, scalability, high availability and reliability. As we are aggressive about gaining maximum results from MySQL 8 investments made by our customers, We are equally conservative (our customer data reliability is critical for us !) on adopting new features, until we are fully confident after several rounds of testing (at different scales on multiple platforms) and technical review (we engage both internal and external consultants for acceptance) and acceptance before deployment in production infrastructure. In the previous versions of MySQL, before every upgrade our consultants manually spend several hours testing compatibility but MySQL 8 made this simple by introducing “upgrade checker” javascript with MySQL Shell 8.0.11 , In this blog we are writing about “upgrade checker” utility and upgrade from MySQL 5.7 to MySQL 8.0 .

Using MySQL Shell 8.0.11 “upgrade checker”

Typical “upgrade checker” run will look similar to this:

 MySQL  JS >  util.checkForServerUpgrade("root@localhost:3306")
Please provide the password for 'root@localhost:3306': **********
The MySQL server at localhost:3306 will now be checked for compatibility issues for upgrade to MySQL 8.0...
MySQL version: 5.7.22-log - MySQL Community Server (GPL)

1) Usage of db objects with names conflicting with reserved keywords in 8.0
  No issues found

2) Usage of utf8mb3 charset
  Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.

  sakila.actor.first_name - column's default character set: utf8
  sakila.actor.last_name - column's default character set: utf8
  sakila.actor_info.first_name - column's default character set: utf8
  sakila.actor_info.last_name - column's default character set: utf8
  sakila.actor_info.film_info - column's default character set: utf8
  sakila.address.address - column's default character set: utf8
  sakila.address.address2 - column's default character set: utf8
  sakila.address.district - column's default character set: utf8
  sakila.address.postal_code - column's default character set: utf8
  sakila.address.phone - column's default character set: utf8
  sakila.category.name - column's default character set: utf8
  sakila.city.city - column's default character set: utf8
  sakila.country.country - column's default character set: utf8
  sakila.customer.first_name - column's default character set: utf8
  sakila.customer.last_name - column's default character set: utf8
  sakila.customer.email - column's default character set: utf8
  sakila.customer_list.name - column's default character set: utf8
  sakila.customer_list.address - column's default character set: utf8
  sakila.customer_list.zip code - column's default character set: utf8
  sakila.customer_list.phone - column's default character set: utf8
  sakila.customer_list.city - column's default character set: utf8
  sakila.customer_list.country - column's default character set: utf8
  sakila.customer_list.notes - column's default character set: utf8
  sakila.film.title - column's default character set: utf8
  sakila.film.description - column's default character set: utf8
  sakila.film.rating - column's default character set: utf8
  sakila.film.special_features - column's default character set: utf8
  sakila.film_list.title - column's default character set: utf8
  sakila.film_list.description - column's default character set: utf8
  sakila.film_list.category - column's default character set: utf8
  sakila.film_list.rating - column's default character set: utf8
  sakila.film_list.actors - column's default character set: utf8
  sakila.film_text.title - column's default character set: utf8
  sakila.film_text.description - column's default character set: utf8
  sakila.language.name - column's default character set: utf8
  sakila.nicer_but_slower_film_list.title - column's default character set: utf8
  sakila.nicer_but_slower_film_list.description - column's default character set: utf8
  sakila.nicer_but_slower_film_list.category - column's default character set: utf8
  sakila.nicer_but_slower_film_list.rating - column's default character set: utf8
  sakila.nicer_but_slower_film_list.actors - column's default character set: utf8
  sakila.sales_by_film_category.category - column's default character set: utf8
  sakila.sales_by_store.store - column's default character set: utf8
  sakila.sales_by_store.manager - column's default character set: utf8
  sakila.staff.first_name - column's default character set: utf8
  sakila.staff.last_name - column's default character set: utf8
  sakila.staff.email - column's default character set: utf8
  sakila.staff.username - column's default character set: utf8
  sakila.staff.password - column's default character set: utf8
  sakila.staff_list.name - column's default character set: utf8
  sakila.staff_list.address - column's default character set: utf8
  sakila.staff_list.zip code - column's default character set: utf8
  sakila.staff_list.phone - column's default character set: utf8
  sakila.staff_list.city - column's default character set: utf8
  sakila.staff_list.country - column's default character set: utf8

3) Usage of use ZEROFILL/display length type attributes
  Notice: The following table columns specify a ZEROFILL/display length attributes. Please be aware that they will be ignored in MySQL 8.0

  sakila.customer.active - tinyint(1)
  sakila.staff.active - tinyint(1)

4) Issues reported by 'check table x for upgrade' command
  No issues found

5) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

6) Usage of old temporal type
  No issues found

7) Foreign key constraint names longer than 64 characters
  No issues found

8) Usage of obsolete MAXDB sql_mode flag
  No issues found

9) Usage of obsolete sql_mode flags
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Usage of removed functions
  No issues found

No fatal errors were found that would prevent a MySQL 8 upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
1

MySQL JS > util.checkForServerUpgrade("root@localhost:3306")

Please provide the password for 'root@localhost:3306': **********

The MySQL server at localhost:3306 will now be checked for compatibility issues for upgrade to MySQL 8.0...

MySQL version: 5.7.22-log - MySQL Community Server (GPL)

1) Usage of db objects with names conflicting with reserved keywords in 8.0

No issues found

2) Usage of utf8mb3 charset

Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.

sakila.actor.first_name - column's default character set: utf8

sakila.actor.last_name - column's default character set: utf8

sakila.actor_info.first_name - column's default character set: utf8

sakila.actor_info.last_name - column's default character set: utf8

sakila.actor_info.film_info - column's default character set: utf8

sakila.address.address - column's default character set: utf8

sakila.address.address2 - column's default character set: utf8

sakila.address.district - column's default character set: utf8

sakila.address.postal_code - column's default character set: utf8

sakila.address.phone - column's default character set: utf8

sakila.category.name - column's default character set: utf8

sakila.city.city - column's default character set: utf8

sakila.country.country - column's default character set: utf8

sakila.customer.first_name - column's default character set: utf8

sakila.customer.last_name - column's default character set: utf8

sakila.customer.email - column's default character set: utf8

sakila.customer_list.name - column's default character set: utf8

sakila.customer_list.address - column's default character set: utf8

sakila.customer_list.zip code - column's default character set: utf8

sakila.customer_list.phone - column's default character set: utf8

sakila.customer_list.city - column's default character set: utf8

sakila.customer_list.country - column's default character set: utf8

sakila.customer_list.notes - column's default character set: utf8

sakila.film.title - column's default character set: utf8

sakila.film.description - column's default character set: utf8

sakila.film.rating - column's default character set: utf8

sakila.film.special_features - column's default character set: utf8

sakila.film_list.title - column's default character set: utf8

sakila.film_list.description - column's default character set: utf8

sakila.film_list.category - column's default character set: utf8

sakila.film_list.rating - column's default character set: utf8

sakila.film_list.actors - column's default character set: utf8

sakila.film_text.title - column's default character set: utf8

sakila.film_text.description - column's default character set: utf8

sakila.language.name - column's default character set: utf8

sakila.nicer_but_slower_film_list.title - column's default character set: utf8

sakila.nicer_but_slower_film_list.description - column's default character set: utf8

sakila.nicer_but_slower_film_list.category - column's default character set: utf8

sakila.nicer_but_slower_film_list.rating - column's default character set: utf8

sakila.nicer_but_slower_film_list.actors - column's default character set: utf8

sakila.sales_by_film_category.category - column's default character set: utf8

sakila.sales_by_store.store - column's default character set: utf8

sakila.sales_by_store.manager - column's default character set: utf8

sakila.staff.first_name - column's default character set: utf8

sakila.staff.last_name - column's default character set: utf8

sakila.staff.email - column's default character set: utf8

sakila.staff.username - column's default character set: utf8

sakila.staff.password - column's default character set: utf8

sakila.staff_list.name - column's default character set: utf8

sakila.staff_list.address - column's default character set: utf8

sakila.staff_list.zip code - column's default character set: utf8

sakila.staff_list.phone - column's default character set: utf8

sakila.staff_list.city - column's default character set: utf8

sakila.staff_list.country - column's default character set: utf8

3) Usage of use ZEROFILL/display length type attributes

Notice: The following table columns specify a ZEROFILL/display length attributes. Please be aware that they will be ignored in MySQL 8.0

sakila.customer.active - tinyint(1)

sakila.staff.active - tinyint(1)

4) Issues reported by 'check table x for upgrade' command

No issues found

5) Table names in the mysql schema conflicting with new tables in 8.0

No issues found

6) Usage of old temporal type

No issues found

7) Foreign key constraint names longer than 64 characters

No issues found

8) Usage of obsolete MAXDB sql_mode flag

No issues found

9) Usage of obsolete sql_mode flags

No issues found

10) Usage of partitioned tables in shared tablespaces

No issues found

11) Usage of removed functions

No issues found

No fatal errors were found that would prevent a MySQL 8 upgrade, but some potential issues were detected. Please ensure that the re

Upgrading to MySQL 8.0

技术文章 October 27, 2020

MySQL 8 uses a global data dictionary containing information about database objects in transactional tables. In previous versions, the dictionary data was stored in metadata files and non-transactional system tables. You need to upgrade your data directory from the file-based structure to the data-dictionary structure.

Just like a downgrade, you can upgrade using two methods:

In-place upgrade
Logical upgrade

You also should check a few prerequisites before the upgrade.

Getting ready

Check for obsolete datatypes or triggers that have a missing or empty definer or an invalid creation context:

shell> sudo mysqlcheck -u root -p --all-databases --check-upgrade

There must be no partitioned tables that use a storage engine that does not have native partitioning support. To identify these tables, execute this query:

shell> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb', 'ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';

If there are any of these tables, change them to InnoDB:

mysql> ALTER TABLE table_name ENGINE = INNODB;

Or remove the partitioning:

mysql> ALTER TABLE table_name REMOVE PARTITIONING;

There must be no tables in the MySQL 5.7 mysql system database that have the same name as a table used by the MySQL 8.0 data dictionary. To identify tables with those names, execute this 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_type_elements', 'columns', 'events', 'foreign_key_column_usage', 'foreign_keys', 'index_column_usage', 'index_partitions', 'index_stats', 'indexes', 'parameter_type_elements', 'parameters', 'routines', 'schemata', 'st_spatial_reference_systems', 'table_partition_values', 'table_partitions', 'table_stats', 'tables', 'tablespace_files', 'tablespaces', 'triggers', 'version', 'view_routine_usage', 'view_table_usage');

There must be no tables that have foreign key constraint names longer than 64 characters. To identify tables with constraint names that are too long, execute this query:

mysql> SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE LENGTH(CONSTRAINT_NAME) > 64;

Tables not supported by MySQL 8.0, such as ndb, should be moved to InnoDB:

mysql> ALTER TABLE tablename ENGINE=InnoDB;

How to do it...

Just like the previous recipe, the following subsections will take you through the details with various systems, bundles, and so on.

In-place upgrades

Here is an outline of the steps:

Shut down the old MySQL version.
Replace the old MySQL binaries or packages with the new ones (detailed steps for different types of installation methods are covered).
Restart MySQL on the existing data directory.
Run the mysql_upgrade utility.
In the MySQL 5.7 server, if there are encrypted InnoDB tablespaces, rotate the keyring master key by executing this statement:

mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

Here are the detailed steps:

Configure your MySQL 5.7 server to perform a slow shutdown. With a slow shutdown, InnoDB performs a full purge and change buffer merge before shutting down, which ensures that the undo logs are empty and the data files are fully prepared in case of file format differences between releases. This step is the most important because, without it, you will end up with the following error:

[ERROR] InnoDB: Upgrade after a crash is not supported.

This redo log was created with MySQL 5.7.18. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Shut down the MySQL server as described in the Starting or Stopping MySQL 8 section.

Upgrade the MySQL binaries or packages.

YUM-based systems

Switch the repositories:

shell> sudo yum-config-manager --disable mysql57-community
shell> sudo yum-config-manager --enable mysql80-community

Verify that mysql80-community is enabled:

shell> sudo yum repolist all | grep mysql8
mysql80-community/x86_64             MySQL 8.0 Community Server  enabled:     16
mysql80-community-source             MySQL 8.0 Community Server  disabled

Run the yum update:

shell> sudo yum update mysql-server

APT-based systems

Reconfigure the apt to switch to MySQl 8.0:

shell> sudo dpkg-reconfigure mysql-apt-config

Run apt-get update:

shell> sudo apt-get update

Remove the current version:

shell> sudo apt-get remove mysql-community-server mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server mysql-community-server-core -y
shell> sudo apt-get autoremove

Install MySQL 8:

shell> sudo apt-get update
shell> sudo apt-get install mysql-server
shell> sudo apt-get install libmysqlclient21

Using RPM or APT bundles

Uninstall the existing packages (refer to the Uninstalling MySQL 8 section) and install the new packages, which can be downloaded fromMySQL Downloads (refer to the Installing MySQL 8.0 using RPM or DEB files section).

Using Generic Binaries

If you have installed MySQL through binaries, you have to remove the symlink to the old version (refer to the Uninstalling MySQL 8 section) and do a fresh installation (refer to the Installing MySQL on Linux using generic binaries section).

Start the MySQL 8.0 server (refer to the Starting or Stopping MySQL 8 to start MySQL section). If there are encrypted InnoDB tablespaces, use the --early-plugin-load option to load the keyring plugin.

The server automatically detects whether data dictionary tables are present. If not, the server creates them in the data directory , populates them with metadata, and then proceeds with its normal startup sequence. During this process, the server upgrades metadata for all database objects, including databases, tablespaces, system and user tables, views, and stored programs (stored procedures and functions, triggers, event scheduler events). The server also removes files that previously were used for metadata storage. For example, after upgrading, you will notice that your tables no longer have .frm files.

The server creates a directory named backup_metadata_57 and moves the files used by MySQL 5.7 into it. The server renames the event and proc tables to event_backup_57 and proc_backup_57. If this upgrade fails, the server reverts all changes to the data directory. In this case, you should remove all redo log files, start your MySQL 5.7 server on the same data directory, and fix the cause of any errors. Then, perform another slow shutdown of the 5.7 server and start the MySQL 8.0 server to try again.

Run the mysql_upgrade utility:

shell> sudo mysql_upgrade -u root -p

mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL. It makes any remaining changes required in the mysql system database between MySQL 5.7 and MySQL 8.0, so that you can take advantage of new privileges or capabilities. mysql_upgrade also brings the performance schema, INFORMATION_SCHEMA, and sys schema objects up to date for MySQL 8.0.

Restart the MySQL server (refer to the Starting or Stopping MySQL 8 to start MySQL section).

Logical Upgrades

Here is an outline of the steps:

Export existing data from the old MySQL version using mysqldump
Install the new MySQL version
Load the dump file into the new MySQL version
Run the mysql_upgrade utility

Here are the detailed steps:

You need to take a logical backup of the database (refer to Chapter 7, Backups for a quicker backup called mydumper):

shell> mysqldump -u root -p --add-drop-table --routines --events --all-databases --ignore-table=mysql.innodb_table_stats --ignore-table=mysql.innodb_index_stats --force > data-for-upgrade.sql

Shut down the MySQL server (refer to the Starting or Stopping MySQL 8 section).
Install the new MySQL version (refer to the methods mentioned in the In-place upgrades section).
Start the MySQL server (refer to the Starting or Stopping MySQL 8 section).
Reset the temporary root password:

shell> mysql -u root -p
Enter password: **** (enter temporary root password from error log)

mysql> ALTER USER USER() IDENTIFIED BY 'your new password';

Restore the backup (this may take a long time depending up on the size of the backup). Refer to Chapter 8, Restoring Data for a quick restoration method called myloader:

shell> mysql -u root -p --force < data-for-upgrade.sql

Run the mysql_upgrade utility:

shell> sudo mysql_upgrade -u root -p

Restart the MySQL server (refer to the Starting or Stopping MySQL 8 section).

About Us

Since 1996, our company has been focusing on domain name registration, web hosting, server hosting, website construction, e-commerce and other Internet services, and constantly practicing the concept of "providing enterprise-level solutions and providing personalized service support". As a Dell Authorized Solution Provider, we also provide hardware product solutions associated with the company's services.

Contact Us

Address: No. 2, Jingwu Road, Zhengzhou City, Henan Province

Phone: 0086-371-63520088

QQ：76257322

Website: 800188.com

E-mail: This email address is being protected from spambots. You need JavaScript enabled to view it.

Shop By Categories

【微软：Win10 月活跃设备同比呈两位数增长，疫情再次证明了 PC 的重要性】

Upgrade Paths

Using MySQL Shell 8.0.11 “upgrade checker” to upgrade from MySQL 5.7 to MySQL 8.0 successfully

Upgrading to MySQL 8.0

Getting ready

How to do it...

In-place upgrades

YUM-based systems

APT-based systems

Using RPM or APT bundles

Using Generic Binaries

Logical Upgrades

About Us

Contact Us

Links

Featured Articles

Follow us