Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "installation and configuration of MySQL8". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Download MySQL 8

MySQL 8 download site.

[note] you need to select the operating system from the Select Operating System drop-down menu. Otherwise, the website will download the operating system detected by the default browser.

Install MySQL 8

The process of setting up MySQL varies depending on the target operating system.

L Microsoft Windows and MacOS users can download and run the GUI-based installer. This guides the user through the setup process and provides default values for common configuration options.

L Linux users can download the general binaries provided by Oracle or install MySQL through the package management system of the Linux distribution.

Considerations when installing MySQL on Linux:

L MySQL is at least the latest version and may not be available in the default software repository for a given Linux distribution. For example, on Fedora Linux, you might need to manually add a MySQL repository to the package management system and then install from that repository.

Unless you completely uninstall the previous version, you cannot switch from using generic Linux binaries to the version provided by the distribution package manager. (and vice versa. The best way is to use the same allocation path as much as possible to maintain consistency

MySQL can also be used as a Docker image. You can change many of the most common options in MySQL by passing command-line tags to the container. If you need finer-grained control, you can create a custom configuration file and pass it to the container. (if you are an Docker expert, you can further customize the container image by adding layers.)

Another option that Oracle provides for many platforms is generic binary archiving without an installer. This needs to be set up manually, mainly by creating and editing configuration files. The advantage is that you can easily delete the entire file later by simply deleting the binary and its supporting files.

Upgrade from previous version to MySQL 8

If you want to upgrade from a previous version of MySQL to MySQL 8, you can choose two general strategies for handling upgrades.

[note] only upgrades are supported from the GA version of MySQL 7 (preferably the latest GA version). Upgrades to non-GA versions of MySQL 7 and earlier versions of MySQL, such as MySQL 6, are not guaranteed to be valid.

L upgrade locally. This involves replacing the existing MySQL 7 binaries with MySQL 8 binaries.

Export and re-import. MySQL's documentation calls this process "logical upgrade." This can be done using the mysqldump or mysqlpump utility.

In either case, you should ensure that the database to be upgraded has no legacy compatibility issues. At the same time, pay attention to the upgrade method of the platform you are running, because the upgrade process of Windows or MacOS systems is different from the Linux process.

Configure MySQL 8

MySQL has a lot of configuration options, but fortunately, in the early stages of the installation or upgrade process, many are not needed.

Edit MySQL 8 profile

MySQL 8's Microsoft Windows and MacOS graphics installer automatically selects reasonable default values to let users know the most important options. These selections are recorded in the configuration file used by MySQL. In addition, linux users will have a basic profile with some automatically generated default values, although not many settings have been added by default on Linux.

To find the location of the MySQL configuration file, first run mysql-help from shell, and then look for "Default options are read from the following files in the given order." It's okay. The configuration file and its location will be listed there.

If the default values for all options in the MySQL configuration file are not listed in the configuration file, you can look for them in the documentation of the file.

A good way to do this is to use the MySQL Workbench application included in the MySQL installation to edit the options file. MySQL Workbench provides a tabbed GUI that lists each possible setting in the file and a functional description of each setting. Some unexpected circumstances can also make the MySQL Workbench configuration file more difficult, such as accidentally inserting a linebreak that does not belong to it.

Initially, MySQL Workbench did not have an option file registered with a specific MySQL connection. To add an options file, perform the following steps:

1. On the MySQL Workbench home page, right-click the instance connection to which you added the option file, and then select Edit Connection.

two。 Under the system profile tab, fill in the full path to the option file in the Configuration File line.

3. Click "Test Connection" to test to make sure everything is all right.

4. The next time you open the connection, you can use "Instance | Options File." The way to edit options file settings from the left tree menu.

Set the root password for MySQL 8

If there are any caveats in the MySQL setup, set the root password for the server from the start to reduce unnecessary security risks.

MySQL 8's Windows and MacOS installers prompt the user for a root password during installation, and some Linux installers for MySQL 8, such as Ubuntu's apt-get-based installation, allow users to provide a root MySQL password.

In other cases, such as when using Fedora's yum or dnf installer, the root password is randomly set and echoed in the MySQL error log. You can use the command sudo grep 'temporary password' / var/log/mysqld.log. Show it. But the password should be changed as soon as possible and must be as complex as possible.

New authentication method using MySQL 8

If you want to use MySQL Workbench in the environment recommended by Linux, you need to pay attention to another configuration option in MySQL 8.

When you install MySQL 8, you will see the option for the new SHA-256 password authentication method. This method is much safer than the traditional "native password" method, so it is recommended as the default method for new installations.

However, the main disadvantage of this new approach is that it is not compatible with the latest version of MySQL client software, especially MySQL Workbench.

[note] the latest version of the client software cannot install all Linux versions from the default installation path. That is, if you install a brand new MySQL 8, such as Ubuntu 18.04, from the operating system's MySQL repository, then the accompanying copy of MySQL Workbench will not support SHA-256 authentication!

Solution:

Download the development version of MySQL Workbench. It all depends on whether or not you have built a development version for your operating system, but most Linux seems to have a newly developed binary version, which is usually the easiest path.

If you only allow connections to MySQL from localhost, you can avoid using SHA-256 authentication without losing too much in terms of security. This is not necessarily your permanent solution, you can use the old connection protocol until you get the latest version of the client software of the system, and then change to the new authentication method. However, this means that you must change the authentication method in the MySQL profile and change the MySQL user account to match.

L build client software from scratch. This is the most laborious solution, and it is only recommended for those who really have no other choice.

Primary MySQL 8 configuration option

Most of the options in MySQL 8 do not need to be set in advance. Some of the client security protocols mentioned above should be decided as soon as possible whether to use the GUI installation wizard or manually edit the configuration file. Here are a few others:.

Other MySQL users

If possible, try not to use a root account to connect to applications that use MySQL. Create an account with full permissions only on the database required by the application. MySQL Workbench applications can do this with just a few clicks.

MySQL connection protocol

MySQL 8 can accept incoming connections to the server in a number of ways:

L TCP/ IP . This is the most extensive way to connect to an MySQL instance, allowing both local and remote connections. Overall, it is also the slowest because traffic must pass through the entire TCP / IP stack, but this usually exists only in extreme performance cases. In cases where remote client connections are required, TCP / IP is an easy way anyway. But at the same time, you need to pay close attention to the security of the connection.

L Unix sockets/named pipes . These are two roughly the same functions in Unix / Linux and Windows. Sockets and pipes are best when the client or application is on the same system as the server, because data can be transferred faster between the client and server, or such a connection can be published over the network, but this is troublesome, in which case you'd better use only TCP / IP.

L shared memory. This is the fastest connection option between the server and the client, but it is also the least flexible. It works only when the server and the client are on the same physical system, and it may be difficult for the server to detect whether the client has timed out or been terminated, resulting in the accumulation of unterminated connections and a waste of resources.

[note] on Unix systems, Unix sockets is used by default for any connection to localhost to improve performance.

MySQL Log option

In addition to normal error logs, MySQL can maintain three other types of logs, all of which are disabled by default, but you can enable debugging as needed during configuration.

L General . This provides a detailed summary of the operation of MySQL from startup to shutdown.

L Slow query . You can record any query that takes more than x seconds to execute (the number is configurable) for debugging.

L BINLOG. The abbreviation for "binary log", which can be configured to contain a binary dump of all modifications to the database file. Obviously, this can have a significant impact on performance, so you should not enable it unless you try to debug a really thorny problem.

By default, logs are not cleared automatically, so they must be deleted periodically through the mysql-log-rotate script. The exception is Binlogs, which expires after 30 days by default.

MySQL Migration and installation Tips

If you are migrating an existing MySQL database to MySQL 8.0, the rule is simple:

L make a full backup of the data.

L use only the databases in MySQL 5.7for local upgrades. Otherwise, you have two options:

A) Export and re-import data

B) upgrade the existing MySQL instance to 5.7, and then upgrade locally.

Considerations when creating a new database in MySQL 8.0:

L use InnoDB for all new tables. InnoDB is now the default choice for newly created tables in MySQL. Why? Because for a while, the traditional MyISAM storage engine had some advantages over the new InnoDB, especially full-text indexing. But now MySQL 8.0 InnoDB supports full-text indexing, in addition to row-level locking, transaction transactions, crash recovery, foreign key constraints and many other functional databases, there are many other functions that should not be abandoned.

Use the utf8mb4 character set, but study the collation used. MySQL 8 now supports the utf8mb4 character set (an implementation of UTF-8), which can store up to 4 bytes per character to be as widely compatible with Unicode as possible. By default, utf8mb4 is selected for the new database, but it doesn't make sense to choose something else. At the same time, the collation you use follows the requirements of the application. By default, MySQL 8 uses the generic utf8mb4_0900_ai_ci collation, but the collation is not accented and case-sensitive; to do this, you can use utf8mb4_0900_as_cs instead. If most of your work is done in a specific language, you may want more language-specific collations.

This is the end of "installation and configuration of MySQL8". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.