MySQL 5.7.30 installation / upgrade (all possible pits are here)
Posted May 29, 2020 • 8 min read
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.
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.
The download address is:
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:
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
Configure environment variables
Add D:Program Files(x86) mysql-5.7.30-winx64bin after the system variable path, as shown in the figure.
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:
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:
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.
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]
After executing the command, it will prompt that the service installation is successful.
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:
If there is no execution permission for other steps, please run it with the province of the administrator.
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] ( https://www.microsoft.com/zh-CN/download/details.aspx ? id = 40784)
For installation, the default location is sufficient. After successful installation, reinstall the service.
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)
-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.
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
Set MySQL Service Password
Use mysqladmin to set a password. After starting the MySQL service, enter the following command:
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.
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:
Then add skip-grant-tables in the configuration file my.ini:
Restart the MySQL service
First stop the MySQL service, and then start the MySQL service, you can:
First log in to mysql, no password is required at this time, as follows:
To change the password through sql statement, first use mysql; then update to update the 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)
//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".