Install multiple mysql on one computer

0.Key points

【0.1】Operation:
If Mysql has been installed on the computer before and is configured with Mysql environment variables, delete the Mysql environment variables first (you can use Notepad to save them temporarily), not sure If it has been configured, check the Path in the environment variable.

Repeat: If environment variables are configured, be sure to delete them!!!
Finally, it can be configured back, but it must not exist during the installation process.

【0.2】Instructions: (If you don’t want to read, you can skip to 1)
When configuring multiple mysql at the same time, you need to execute some installation commands of mysql. These commands will be executed in the Mysql installation root directory\bin\ folder of the corresponding Mysql to be installed to ensure that X The installation command executed by the version is applied to X, and the installation command executed by the Y version is applied to Y.

If the environment variables have been configured before, the environment variables are configured with version A of the Mysql installation root directory\bin\, and we all know that once the environment variables are configured, they will affect the whole world. That is to say, if environment variables are configured, even if you execute the installation command in the X version of Mysql installation root directory\bin\, the configured path will not It belongs to X, but to A, so there will be problems.

1.Download address

MySQL :: Download MySQL Community Server

2. Version selection
  • Select the required version number and operating system (this article uses Windows as an example)

  • Jump to the new page, find the “link” below, click to download
3. Folder preparation

  • In principle, as long as there is no Chinese in the path, it is recommended to just change the drive letter and leave the rest unchanged.

  • Select a drive (select drive D here) and create the D:/Program Files/MySQL/ folder

  • Unzip the downloaded compressed package to MYSQL

  • Manually create my.ini file and data folder

  • Introduction to file usage (can be skipped)

bin: This folder contains the MySQL executable file.
  Such as mysql.exe and mysqld.exe.
  mysql.exe is a MySQL client program used to connect and operate the MySQL server;
  mysqld.exe is the MySQL server program used to start and manage the MySQL database service.
  
  data: manually create one if it does not exist after decompression.
  This folder is the default storage path of the MySQL database, which contains the data files and log files of all databases.
  Each database has a corresponding folder, which contains the tables and data files of the database
  
  etc: If not, there is no need to create it (configure my.ini directly in the root directory later)
  This folder contains MySQL configuration files, such as my.ini (Windows) or my.cnf (Linux)
  In the configuration file, you can set the parameters of the MySQL server, such as port number, character set, and cache size.
  \t
  lib: This folder contains the MySQL dynamic link library file.
  Such as libmysql.dll (Windows) or libmysqlclient.so (Linux)
  These library files provide the MySQL API interface and can be used to develop MySQL client programs.
  \t
  share: This folder contains some shared files, such as character set files and error message files.
  The character set file defines the character sets supported by MySQL, and the error message file contains MySQL error codes and error messages.
  \t
4.my.ini file configuration
  • The files are as follows:
  • The port number needs to be changed in two places. The ports between multiple databases cannot be the same.
  • Paths between files are only separated by / or \
  • Modify basedir
  • Modify datadir
  • If MySQL version is 5.7.X, add login-free check in the last line
[mysqld]
# Set port 3306 here
port=3306
#Set the installation directory of mysql
basedir=D:/Program Files/MySQL/mysql-5.7.43-winx64
#Set the storage directory for the data of the mysql database
datadir=D:/Program Files/MySQL/mysql-5.7.43-winx64/data
#Maximum number of connections allowed
max_connections=200
#The number of allowed connection failures. This is to prevent someone from trying to attack the database system from this host
max_connect_errors=10
#The character set used by the server defaults to UTF8
character-set-server=utf8
# The default storage engine that will be used when creating new tables
default-storage-engine=INNODB
# Use "mysql_native_password" plug-in authentication by default
default_authentication_plugin=mysql_native_password
[mysql]
#Set the default character set of mysql client
default-character-set=utf8
[client]
#Set the port used by default when the mysql client connects to the server
port=3306
default-character-set=utf8
#Free login check
skip-grant-tables
5.Installation

  • Open the command prompt as administrator, and enter the bin folder of Mysql to be configured

If you are still using Windows PowerShell, when switching directories, add double quotes to “Program Files”.
For example: cd D:/"Program Files"/MySQL/mysql-5.7.43-winx64/bin

Note: All the following commands are executed under the corresponding Mysql installation folder bin. If you can execute the mysqld command in a folder other than bin, it means that you have configured the environment variables. First Delete and continue

  • Installation: Enter the command mysqld install service name --defaults-file="path"

    For example: mysqld install MYSQL57 --defaults-file="D:\Program Files\MySQL\mysql-5.7.43-winx64\bin"

  • Initialization: Enter the command mysqld --initialize

  • Start the service: Enter the command net start service name, the service name must be the same as the one set above

    For example: `net start MYSQL57

Note: If there is a problem with the above command, you can check if it matches point 9 of this article.

6. Password modification
  • Enter the bin folder of the corresponding version
  • Shut down the service net stop service name
  • Perform the following corresponding operations according to the version of Mysql
5.7.5 and lower versions
  • Make sure the login-free check has been configured in the my.ini configuration file above
  • Or use sudo mysqld --skip-grant-tables --skip-networking
-- Login
mysql -uroot -p
-- Just press Enter when prompted to enter the password.

-- Refresh permissions
flush privileges;

--MySQL 5.7.5 and previous versions use SET PASSWORD syntax to change passwords
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('Fill in password here');
5.7.6 and higher and lower versions
  • Make sure the login-free check has been configured in the my.ini configuration file above
  • Or use sudo mysqld --skip-grant-tables --skip-networking
Enter mysql -uroot -p
-- Just press Enter when prompted to enter the password.
-- Refresh permissions
flush privileges;

--MySQL 5.7.6 and later versions, use ALTER USER syntax to change the password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Fill in password here';

If there is an error in changing the password using the above method, you can modify the mysql.user table directly:

UPDATE mysql.user SET authentication_string = PASSWORD('Fill in password here')WHERE User = 'root' AND Host = 'localhost';FLUSH PRIVILEGES;

The password field has been removed from the mysql5.7 user table and changed to authentication_string.

Attention

–skip-grant-tables: This option will cause the MySQL server to skip the verification step, allowing all users to log in to the MySQL server anonymously without password verification and have all operating permissions.
Therefore, after configuring the password, delete the login-free check skip-grant-tables in my.ini configured in Mysql5.7.X above.

-- Start the database and change it to your own service name!!!
net start service name
8.X
  • Configuration:
-- Skip permission verification and log in to mysql
mysqld --shared-memory --skip-grant-tables
-- Note: Open a new window (administrator)!!!
Enter mysql -uroot -p
-- Just press Enter when prompted to enter the password.


--Switch to mysql library
use mysql;

-- Leave password blank
update user set authentication_string='' where user='root';

-- Refresh permissions
flush privileges;

--Set encryption rules and update new passwords, authorize
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Password here' PASSWORD EXPIRE NEVER;
alter user 'root'@'localhost' identified by 'fill in password here';
grant all privileges on *.* to "root"@'localhost';


-- Refresh permissions
flush privileges;

-- Start the database and change it to your own service name!!!
net start service name
  • Notice
Note: Since mysql 8.0 no longer supports the password function
Therefore the following command cannot be executed
update mysql.user set authentication_string=password('123456') where user='root';
  • Restart the service net start service name
7. Environment variable configuration

Select a commonly used configuration environment variable among the configured multiple versions of Mysql, and configure multiple only the first one is valid

Search for Edit system environment variables in the menu at the bottom of Windows

  • Environment variables (N)...
  • System environment variables (S)
  • Double-click Path
  • New(N)
  • Add Mysql decompression path\bin for example D:\Program Files\MySQL\MySQL Server 5.5\bin
8. Login

Use mysql -u username -p password -P port to log in, pay attention to the upper and lower case letters

9. Possible problems
  • The MySQL service is starting or stopping, please wait for a while and try again.
    • First open the command line window as an administrator. Note that you are an administrator, otherwise you will not have access permissions.
    • Enter the command tasklist| findstr "mysql" to find all Mysql processes
    • Enter the command taskkill /f /t /im mysqld.exe to kill all Mysql processes
    • Enter the command tasklist| findstr "mysql" to check whether there are any other mysql residual processes left until the process is completely killed.
    • After executing the above command, you can restart or stop the MYSQL service

Error:

  • Please type NET HELPMSG 3523 for additional help.

  • net start service failed to start

  • Repair the installation of MySQL service under Windows (the wrong mysqld was specified when installing the Windows service)

  • It may be caused by configuring Mysql environment variables.

 # Repeat the installation of MySQL service
  mysqld --install
  The service already exists!
  The current server installed: "C:\Program Files\MySQL\MySQL Server 8.0\mysqld" MySQL

Solution:

  • Method 1. Modify the registry: win + r Open Run, enter regedit, and then modify it as follows
# Find the registration path
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL

# Modify the ImagePath field in the right window
"C:\Program Files\MySQL\MySQL Server 8.0\mysqld" MySQL
# Change to
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld" MySQL

# Execute the installation service command again
mysqld --install
The service already exists!
The current server installed: "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld" MySQL

# It seems that the installation service path is correct, let us try to start it through the service command
net start mysql
MySQL service is starting.
The MySQL service has been started successfully.

Method 2. Delete the service, delete the environment variables, and reinstall
sc delete service name

Citation:

References:

  • Complete Tutorial on MySQL Installation – Versions 5.7 and 8.0
  • MySQL changes root user password
  • # MySQL 5.7 Forgot root password
  • Repair the installation of MySQL service under Windows
  • In-depth analysis of MySQL ERROR 1045 (28000)