MySQL 5.7.30 Installation and Upgrade Issues Detailed Tutorial

wedge

Because the MySQL version installed on the computer was relatively old, probably version 5.1, it did not support the JSON field function. The latest editor product developed by the development department uses the function of JSON fields. Therefore, you need to upgrade the MySQL version. The target version for the upgrade is MySQL 5.7.30 (although the latest version is 8.x, 5.7 is basically sufficient). I found some pitfalls during the upgrade and installation process, so I used this article to record them.

Uninstall old version

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

mysqld --remove mysql
//Note: "msyql" is the service name. If you do not specify a service name during installation,
//The default name is MySQL (not case sensitive under Windows);
// If a name is specified during installation, the above name needs to be changed to the specified name.

One thing to note is that after we delete the MySQL service, it may still be in the Windows service list. It doesn’t matter where you keep it, but if you are a germaphobe and must delete it, you need to delete the corresponding key in the registry, refer to the figure below. Of course, for our upgrade, we will need to install the Windows service later, which can be directly overwritten.

Registry diagram

Then delete the old MySQL related folders and the related environment variable configurations.

It should be noted that if the data file and the MySQL program are in the same folder, the data needs to be retained.

Install version 5.7.30

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

Download MySQL

The download address is: 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 version is 8.x. You can click on the “Looking for the latest GA
version "Select the version, and then download the free installation version:

Unzip MySQL

After downloading, unzip it to the directory you want to install it, for example, my directory is:
D:\Program Files (x86)\mysql-5.7.30-winx64

Configuring environment variables

Add D:\Program Files (x86)\mysql-5.7.30-winx64\bin after the system variable path, as shown in the figure.

Create a configuration file my.ini

The installation package of version 5.7.30 does not come with the configuration file my.ini by default. So you need to create a my.ini file yourself. Manually create the my.ini file and enter the following content:

[mysql]
# Set the default character set of the mysql client to default-character-set=utf8

[mysqld]
# Set port 3306 port = 3306
# Set the mysql installation directory basedir=D:\Program Files (x86)\mysql-5.7.30-winx64
# Set the data storage directory of the mysql database datadir=D:\Program Files (x86)\mysql-5.7.30-winx64\data
# Run the maximum number of connections max_connections=200
# The default character set used by the server is the 8-bit latin1 character set character-set-server=utf8
# The default storage engine that will be used when adding a new table default-storage-engine=INNODB

There are some pitfalls here that need to be avoided.

my.ini Note 1

my.ini must be saved in ANSI format (the configuration file is encoded in ANSI format by default and may be accidentally saved in other formats, such as UTF-8), otherwise subsequent services may not be able to start. If you are not sure what format my.ini is in, you can save it as:

my.ini Note 2

If you are upgrading from an earlier version to version 5.7, you may want to simply copy the previous configuration file to preserve the previous configuration. You should pay attention at this time, because many parameters of the previous low versions are no longer suitable for the high versions. If it is not modified, subsequent service startup will fail.

For example, one of the parameters that caused the error I encountered was:

#table_cache=256 // low version table_open_cache=256 // high version

If you must keep the configuration of the old version and are not familiar with the parameter changes of the new version, you can add the logging function to the configuration file. In this way, when an error occurs, you can view 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 view the error through the log, such as:

More possible error parameters:

unknown variable 'key-buffer=256M' //Just remove the change.

unknown variable 'table-cache=512' //Change it to table_open_cache.

unknown variable 'thread-concurrency=8' //Remove the change, 5.7 has been abandoned.

When a Windows service fails to start, the error details will not be marked, and it will simply say that the service failed to start. Can only be viewed through the configuration log file.

Install MySQL Service

The most important step is to install the MySQL service. First enter the cmd interface, CD to the bin directory of MySQL (note that you must execute in the bin directory here), and execute the following command:

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

The screenshots are as follows:

After executing the command, it will prompt that the service is installed successfully.

Note 1

It should be noted that there may be an error message saying Install/Remove of the Service Denied!
Reason: When installing MySQL using cmd under normal user mode permissions, such an error message will appear. Run cmd in administrator mode:

If there is no execution permission for other steps, please run them as an administrator.

Note 2

Some computers will have an 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 the default, after the installation is successful, reinstall the service.

Initialize MySQL

MySQL 5.7 does not have a data directory, so you need to initialize MySQL and create a data directory. This is also relatively simple. Just execute the following command:

mysqld --initialize-insecure --user=mysql

It should be noted that some articles introduce the use of "mysqld --initialize" here. The difference between "mysqld --initialize-insecure" and "mysqld --initialize" is that the former creates a blank password, while the latter creates a random password. Therefore, use "mysqld --initialize", preferably with "--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 is not necessary to use it under Windows.

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 will be generated.

Note 1

If an error occurs during initialization: “–initialize specified but the data directory has files in it. Aborting.” That’s because you may have manually created the data directory and it contains files (for example, you may have done this during an upgrade). In this case, all you need to do is delete the data directory. You can consider upgrading later.

The data directory mentioned here 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 MySQL Service

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

net start MySQL

Set the MySQL service password

Use mysqladmin to set the password. After starting the MySQL service, enter the following command:

Enter the old password (through the previous operation, the old password is blank), then enter the new password, confirm the new password, and the setting is successful.

Note 1

If you did not initialize the password during the previous initialization, and accidentally forgot the initialization password, the following problems will occur, and you will not be able to log in or change the password:
"Access denied for user 'root'@'localhost'"

Solve it by following these steps:

Add skip-grant-tables

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

Restart MySQL service

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

Change Password

First log in to mysql, no password is required at this time, as follows:

To modify the password through SQL statements, first use mysql; then update the password:

Then exit mysql.

Delete skip-grant-tables and restart the MySQL service

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

Upgrade old MySQL data to 5.7

To upgrade old data, if you are upgrading from version 5.x, you basically just need to copy the relevant data files to the data folder. It should be noted that if innodb is used, files such as "ibdata" need to be copied over. Otherwise, the report does not exist:

error: 1146: Table 'a_content' doesn't exist

Innodb is a popular database engine for MYSQL database that supports transactions (row level). Ibdata is used to store file data, and the table files in the folder with the library name are just structures. Since the new version of MySQL uses innodb by default, the ibdata1 file exists by default. If this file is missing, some data tables will fail.

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

repair table tablename

After the above operations are completed normally, it will be fine. If there are still problems, you can try to use the upgrade command mysql_upgrade to upgrade the data (it is also recommended to use this command to upgrade, otherwise subsequent problems may be difficult to locate)
https://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html

//Upgrade mysql, mysql_upgrade checks for incompatible tables and updates the grant table;
mysql_upgrade -uroot -p

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

Summarize

This is the end of this article about the detailed installation and upgrade tutorial of MySQL 5.7.30. For more relevant MySQL 5.7.30 installation and upgrade content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!