Mysql 5.7.19 free installation version encountered pitfalls (collection)

1. Download the 64-bit zip file from the official website.

2. Unzip the zip file to a location where the installation will be done.

3. If there is a previous MySQL, if it is installed, go to the control panel to uninstall it, and you also need to delete the residual files left by the previous MySQL. If it is free to install, use the mysqld -remove command to delete it. When uninstalling the previous files, remember to keep the data folder and my.ini file. (The contents of the data file can be restored; the my.ini file contains configuration reference configuration)

If there was a mysql service before, you need to stop the mysql service

4. Create a new my.ini file. The contents are the same as before. To modify:

no-beep (commented out with #)
basedir="C:/Program Files/MySQL/mysql-5.7.19-winx64"
datadir="C:/ProgramData/MySQL/mysql-5.7.19-winx64/data\" (where the new database is stored)
The my.ini file is placed in the C:\mysql-5.7.19-winx64 directory

5. Execute the command mysqld --initialize-insecure

There are two problem files, one is vs2013 c++ and the other is a dynamic link library.

Baidu finds these 2 files

msvcr120.dll
vcredist_x64.exe

6. Put the dll under system32, and run the exe directly to install it. Then run the step 5 command again.

7. It prompts that there is no corresponding directory. (Errcode: 2 - No such file or directory)

8. Go to the corresponding directory and try to manually generate a folder.

9. After creation, the command in step 5 finally works well. Check whether the corresponding directory has been generated.

10. After successful generation, use the command mysqld --install mysql

(--default-file=C:\mysql-5.7.19-winx64\my.ini, added this sentence. The server will not start. mysqld -remove , do not bring this parameter after reinstalling, and it can start normally this time)

11. If Service successfully installed . You can go to the service to see if there is a mysql service

12. Start the service

13. You need to log in after the service starts. But the password is initialized. So we have to reset

14. Add skip-grant-tables under [mysqld] in my.ini, skip permissions, and restart the service

15. You can enter directly by mysql -u root -p

16. UPDATE mysql.user SET authentication_string = PASSWORD('123456'), password_expired = 'N' WHERE User = 'root' AND Host = 'localhost' ;

17. Go to my.ini and comment out the skip-grant-tables you just added, and restart the service

18. Enter mysql -u root -p again and enter the password you just set

19. show databases ; if errors such as 1820 or 1862 occur. Explanation: The password needs to be reset. SET PASSWORD = PASSWORD('123456');

20. Next is to copy the database. You need to stop the mysql service before copying the database

21. Copy the previous data folder data to C:/ProgramData/MySQL/mysql-5.7.19-winx64/data\ directory. Remember that it is best not to overwrite it. Just rename the previous data and then restart the MySQL service.

22. Start CMD under the administrator command and execute the command:

mysql_upgrade -u root -p --force

Previous data recovery

23. Note that if there is no 'root'@'%' in the mysql.user table before, it means that remote access is not possible. You need to modify the remote access or create a new 'root'@'%' ('root'@'%' means that remote access can be made with root, and 'root'@'loaclhost' means that only local access is possible)

24. use mysql;

update user set host = '%' where user = 'root';
flush privileges;
(The following steps can be omitted)
select host, user from user; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

25. After setting up, other machines can access it. It is recommended to add a new 'root'@'%' instead of changing the previous localhost to %, which will make it inaccessible locally.

Note: It is best not to change the data after modifying the settings, otherwise it will be a mistake. Change 'root'@'loaclhost' to 'root'@'%', and then copy the database file over. But at this time it prompts that local login is not possible. It should be that the settings have been modified to not allow local login. Therefore, you need to copy the database first and restore the data before making any modifications or setting authorizations. In addition, the authorization of version 5.7.19 seems to be different from before and needs to be checked. When moving a database, be sure to back it up.

If the my.ini file is copied from a previous version, there may be a problem, such as the maximum number of connections. Here is an example:

[mysqld]
port=3306
basedir="C:/Program Files/MySQL/mysql-5.7.19-winx64"
datadir="C:/ProgramData/MySQL/mysql-5.7.19-winx64/data\"
max_connections=1000
character-set-server=utf8
default-storage-engine=INNODB 
lower_case_table_names = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
#skip-grant-tables
[client]
port=3306
default-character-set=utf8
[mysql]
default-character-set=utf8

Summarize

The above is the pitfalls encountered by the Mysql 5.7.19 free installation version that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!