MySQL 5.7.30 installation / upgrade (all possible pits are here)

Posted May 29, 20208 min read

wedge

Because the MySQL version installed on the previous computer is relatively old, it is probably the 5.1 version, which does not support the JSON field function. The editor product developed by the latest development department uses the function of the JSON field. Therefore, you need to upgrade the MySQL version. The target version of the upgrade is MySQL 5.7.30(although the latest version has reached 8.x, but 5.7 is basically enough). Found that there will be some pits during the upgrade installation process, so use this article to record.

Uninstall the old version

First, you need to uninstall the old version. Uninstalling is actually very simple. Just 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
//It should be noted that:"msyql" is the service name, if the service name is not specified during installation,
//The default name is MySQL(not case sensitive under windows);
//If the name is specified during installation, the above name needs to be modified to the specified name.

remove msyql

One thing to note is that after we delete the MySQL service, it may still be in the list of windows services, and it does not matter where you leave it, but if you are clean, you must delete it, you need to delete the registry Refer to the figure below for the corresponding key in the middle. Of course, for our upgrade, we need to install the windows service later, which can be directly covered.

Picture of the registration form

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

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.

Download MySQL

The download address is: https://dev.mysql.com/downloads/mysql/
Click the link above to enter the download page. Since the latest version is 8.x, the default version is 8.x. You can click on "Looking for the latest GA
version Select the version and download the installation-free version:
Download

Unzip MySQL

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

Directory

Configure environment variables

Add D:Program Files(x86) mysql-5.7.30-winx64bin after the system variable path, as shown in the figure.
Environment Variables

Create a configuration file my.ini

The installation package of version 5.7.30 defaults without the configuration file my.ini. So you need to create your own to create a my.ini file. Manually create the my.ini file, then enter the following:

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

[mysqld]
# Set port 3306
port = 3306
# Set 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 character set used by the server defaults to the latin1 character set with 8-bit encoding
character-set-server = utf8
# The default storage engine that will be used when participating in the new table
default-storage-engine = INNODB

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 the ANSI encoding format, and may be accidentally saved in other formats, such as UTF-8). Otherwise, subsequent services will not start. If you are not sure what format my.ini is, you can save it as:
Format ANSI

my.ini Note 2

If you are upgrading from a lower version to 5.7, you may directly copy the previous configuration file in order to retain the previous configuration. At this time, we must pay attention, because many low-level parameters are not suitable for high-level versions. If it is not modified, it will cause subsequent service startup failure.

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

# table_cache = 256 //Low version
table_open_cache = 256 //High version

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

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

Then after the startup fails, you can view the error reported through the log, such as:
Error log

More possible error parameters:

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

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

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

The windows service failed to start, the error details will not be identified, only a simple failure to start the service. Can only be viewed through the configuration log file.
Service failed to start

Install MySQL service

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

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

Screenshot below:
Installation Service

After executing the command, it will prompt that the service installation is successful.

Note one

It should be noted that there may be an error in the Install/Remove of the Service Denied!
Reason:This error message will appear when installing cmd under normal user mode permissions. Run cmd in administrator mode:
Run cmd as administrator
If there is no execution permission for other steps, please run it with the province of the administrator.

Note 2

Some computers will have an error that MSVCP120.dll cannot be found:
Error
This error is caused by not installing vcredist
Download vcredist address:[ https://www.microsoft.com/zh-CN/download/details.aspx?id=40784] ( https://www.microsoft.com/zh-CN/download/details.aspx ? id = 40784)
For installation, the default location is sufficient. After successful installation, reinstall the service.

Initialize MySQL

MySQL5.7 does not come with a data directory, so it is relatively simple to initialize MySQL and produce the data directory. You can 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 will create an empty password, while the latter will create A random password. Therefore, use "mysqld --initialize", preferably add "--console", you can print out the random password(note that "--console" is limited to windows)

console

console

-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, and it does not matter if it is brought.

Reference link for data initialization:[ https://dev.mysql.com/doc/mysql-installation-excerpt/5.7/en/data-directory-initialization.html] ( https://dev.mysql.com/doc/mysql -installation-excerpt/5.7/en/data-directory-initialization.html)

After the initialization is complete, a data directory will be generated.

Note one

If there is an error during initialization:"--initialize specified but the data directory has files in it. Aborting." That is because you may have manually created the data directory, and it contains files(such as the upgrade may do this operation), you need to do this time The only thing is to delete the data directory, as for the upgrade, you can consider 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:DevelopMySQLmysql-8.0.12-winx64data

Start the MySQL service

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

net start MySQL

image.png

Set MySQL Service Password

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

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

Note one

If the uninitialized password is not initialized during the previous initialization, and the initialization password is accidentally forgotten, the following problem will occur, and the login and password cannot be changed:
"Access denied for user 'root' @ 'localhost'"

Solved by the following steps:

Add skip-grant-tables

Then add skip-grant-tables in the configuration file my.ini:
image.png

Restart the MySQL service

First stop the MySQL service, and then start the MySQL service, you can:
image.png

change Password

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

To change the password through sql statement, first use mysql; then update to update the password:
Change Password

Then exit mysql.

Remove skip-grant-tables and restart MySQL service

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

Upgrade old MySQL data to 5.7

To upgrade old data, if it is upgraded from version 5.x, basically only need to copy the relevant data files to the data folder. It should be noted that if you use innodb, you also need to copy over files such as "ibdata". Whether the report will not exist:

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

innodb is a popular database engine of MYSQL database, supports transactions(row level), ibdata is used to store the data of the files, and the table files in the folder of the library name are just structures, because the new version of mysql tries innodb by default, so The ibdata1 file exists by default, and the data table without this file will be wrong.

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

repair table tablename

After the above operations are normal, it is ok. If you still have problems, 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, and then do not continue the problem, it may be difficult to locate)
https://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html

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

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

The full text above.

For more exciting content, please pay attention to the public account "Uncle ITman Biao".
ITman Uncle Biao public account