Shulou(Shulou.com)06/01 Report--

What the editor wants to share with you this time is how to solve the problem of installation and upgrade of MySQL 5.7.30. The article is rich in content. Interested friends can learn about it. I hope you can get something after reading this article.

Wedge

As the previous version of MySQL installed on the computer is relatively old, about version 5.1, does not support the JSON field function. The editor products developed by the latest development department use the function of the JSON field. Therefore, you need to upgrade the MySQL version, and the target version of the upgrade is MySQL 5.7.30 (although the latest version is 8.x, 5.7 is basically sufficient). It is found that there will be some pits during the upgrade installation process, so use this article to record it.

Uninstall the old version

First of all, you need to uninstall the old version, which is actually quite simple. You only need to remove the MySQL service, first open CMD, then cd to the bin directory of MySQL, and then enter the following command to remove the MySQL service:

Mysqld-remove mysql

/ / it should be noted that "msyql" is the service name. If the service name is not specified during installation,

/ / the default name is MySQL (case-insensitive under windows)

/ / if a name is specified during installation, the above name needs to be modified to the specified name.

It is important to note that after we delete the MySQL service, it may still be in the list of windows services, and it doesn't matter where you keep it, but if you are addicted to cleanliness and must delete it, you need to delete the corresponding key in the registry, see the figure below. Of course, for our upgrade, we also need to install the windows service, which can be overwritten directly.

A diagram of the registry

Then delete the old MySQL-related folders and delete the configuration of the relevant environment variables.

It is important to note that if the data file and the MySQL program are in the same folder, you need to keep the data.

Install version 5.7.30

The next step is to install version 5.7.30, and the first step is to download it.

Download MySQL

Download address: https://dev.mysql.com/downloads/mysql/

Click the link above to go to the download page. Since the latest version is 8.x, the default is 8.x. You can click "Looking for the latest GA"

Version "Select the version, and then download the installation-free version:

Decompress MySQL

After the download is complete, extract it to the directory you want to install, for example, my directory is:

D:\ Program Files (x86)\ mysql-5.7.30-winx64

Configure environment variables

Append D:\ Program Files (x86)\ mysql-5.7.30-winx64\ bin to the system variable path, as shown.

Create a profile my.ini

The default installation package for version 5.7.30 is without the configuration file my.ini. So you need to create your own file to create a my.ini. Create the my.ini file manually and enter the following:

[mysql] # set mysql client default character set default-character-set=utf8 [mysqld] # set port 3306 port = 330mm set mysql installation directory basedir=D:\ Program Files (x86)\ mysql-5.7.30-winx64# set mysql database data storage directory datadir=D:\ Program Files (x86)\ mysql-5.7.30-winx64\ data# run maximum number of connections character set default used by max_connections=200# server Consider the default storage engine default-storage-engine=INNODB that the 8-bit coded latin1 character set character-set-server=utf8# will use when participating in the new table

There will be some pits here that need to be avoided.

My.ini Note 1

My.ini must be saved in ANSI format (the configuration file defaults to ANSI encoding format, which may be accidentally saved to another format, such as UTF-8). Otherwise, subsequent services will fail to start. If you are not sure what format my.ini is, you can save it as:

My.ini Note 2

If you are upgrading from a lower version to version 5.7, you may copy the previous configuration file directly in order to retain the previous configuration. It is important to pay attention at this time, because many of the previous low-version parameters are no longer suitable for the higher version. If it is not modified, it will cause the subsequent service startup to fail.

For example, one of the parameters that caused the error encountered by the author is:

# table_cache=256 / / lower version of table_open_cache=256 / / higher version of

If you must keep the configuration of the old version, and are not very familiar with the parameter changes of the new version, you can assume the log function in the configuration file, so that when you report an error again, you can check the log for details. Configure the log path as follows:

Log_error=D:/mysql-5.7/error.log

Then, after the startup fails, you can check the error report through the log, such as:

More possible error parameters:

Unknown variable 'key-buffer=256M' / / just remove the change item.

Unknown variable 'table-cache=512' / / can be changed to table_open_cache.

Unknown variable 'thread-concurrency=8' / / remove the change item, 5.7 has been abandoned.

If the windows service fails to start, it will not identify the error details, but will simply say that the service failed to start. It can only be viewed through the configuration log file.

Install the MySQL service

The most important step is to install the MySQL service. First, go to the cmd interface, CD to the bin directory of MySQL (be sure to execute it under the bin directory here), and execute the following command:

Mysqld-- install [service name, default is MySQL]

The screenshot is as follows:

After executing the command, you will be prompted that the service installation was successful.

Note 1

It is important to note that there may be Install/Remove of the Service Denied! The prompt is wrong.

Reason: this error message appears when cmd installs mysql under normal user mode permissions. To run cmd in administrator mode:

If there is no permission to execute the other steps, please use the administrator province to run them.

Note 2

Some computers will get the error that MSVCP120.dll cannot be found:

This error is caused by not installing vcredist

Download vcredist address: https://www.microsoft.com/zh-CN/download/details.aspx?id=40784

Install, the location is available by default, after the installation is successful, reinstall the service.

Initialize MySQL

MySQL5.7 does not have a data directory, so you need to initialize MySQL and produce a data directory, which is also relatively simple. Execute the following command:

Mysqld-initialize-insecure-user=mysql

It is important to note that some articles introduce the difference between "mysqld-- initialize", "mysqld-- initialize-insecure" and "mysqld-- initialize" in that the former creates an empty password while the latter creates a random password. Therefore, using "mysqld-initialize", it is better to add "- console" to print out the random password (note that "- console" is limited to windows)

-user=msyql is mainly used to authorize the data directory to the mysql account under linux or unix systems. It doesn't have to be used under windows, and it doesn't matter with it.

Data initialization reference link: https://dev.mysql.com/doc/mysql-installation-excerpt/5.7/en/data-directory-initialization.html

After initialization is complete, the data directory is generated.

Note 1

If there is an error in initialization: "- initialize specified but the data directory has files in it. Aborting." it is because you may have manually created the data directory and contains files (such as upgrading may do this), at this time, all you need to do is delete the data directory. As for the upgrade, you can consider it later.

At this time, the data directory refers to the directory specified in the my.ini file, not necessarily the directory under the mysql installation file:

Datadir=D:\ Develop\ MySQL\ mysql-8.0.12-winx64\ data

Start the MySQL service

To start MySQL service under windows, you can use the following command:

Net start MySQL

Set the MySQL service password

You can set a password using mysqladmin. After starting the MySQL service, enter the following command:

Enter the old password (through the previous operation, the old password is empty), and then enter the new password, confirm the new password, you can set successfully.

Note 1

If the unempty password is not initialized during the previous initialization, and you accidentally forget the initialization password, the following problems will occur and you will not be able to log in and change the password:

"Access denied for user 'root'@'localhost'"

Solve the problem through the following steps:

Add skip-grant-tables

Add skip-grant-tables to the configuration file my.ini:

Restart the MySQL service

Stop the MySQL service first, and then start the MySQL service to:

Modify the password

Log in to mysql first. No password is required at this time, as follows:

Change the password through the sql statement, first use mysql, and then update update the password:

Then quit mysql.

Delete skip-grant-tables and restart MySQL service

First remove the skip-grant-tables from the my.ini, and then net stop mysql, net start mysql restart the service.

MySQL old data upgrade to 5.7

To upgrade old data, if you are upgrading from version 5.x, you basically only need to copy the relevant data files to the data folder. It is important to note that if you use innodb, you also need to copy files such as "ibdata". Otherwise, it will report a situation that does not exist:

Error: 1146: Table'a contentment doesn't exist

Innodb is a popular MYSQL database engine, supporting transactions (row level), ibdata is used to store the data of files, and the table files in the folder of the library name are just structures. As the new version of mysql tries innodb by default, ibdata1 files exist by default, and some data tables without this file will make errors.

If there is data corruption during the upgrade process, you can use the repair command to fix it:

Repair table tablename

Normally, after the above operations, it will be fine. If there is a problem, you can try to use the upgrade command mysql_upgrade to upgrade the data (it is also recommended to use the change command to upgrade, otherwise the problem may be difficult to locate later).

Https://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html

/ / upgrade mysql,mysql_upgrade to check for incompatible tables and update grant table; mysql_upgrade-uroot-p

The upgrade speed depends on the size of the data directory.

After reading this article on how to solve the problem of installing and upgrading MySQL 5.7.30, if you think the article is well written, you can share it with more people.

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.