Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

In Linux systems, by default MySQL (or MariaDB, a popular MySQL fork) does not directly support running multiple MySQL instances on the same machine, each using a different port and independent password system. However, you can achieve this requirement by configuring multiple MySQL instances (also known as multi-instance). This usually involves configuring different ports, data directories, configuration files, and possibly user permissions for each instance.

Here are the basic steps to configure multiple MySQL instances:

1. Preparation

Make sure you have a working MySQL installation. This guide assumes you are using MySQL, but if you are using MariaDB, the steps are very similar.

2. Create a new data directory

Create a new data directory for each MySQL instance:

here/data/mysql1and/data/mysql2These are the data directories of two different instances.

3. Copy and modify the configuration file

The default configuration file for MySQL is usually/etc/my.cnf(or/etc/mysql/my.cnf, depending on your distribution). You will need to create a new configuration file for each instance.

• Copy the configuration file template (if it does not exist, you can create it from the default configuration):

• Modify the following section of each configuration file:

  • [mysqld]Section SettingsdatadirFor newly created data directories (such as/data/mysql1or/data/mysql2）。
  • Set up differentport(For example, the first instance uses 3306 and the second instance uses 3307).
  • You may also need to setsocketpaths, making sure they are unique.
  • (Optional) Settingsuserandlog_errorand other options to ensure that the instance runs independently.

4. Initialize the data directory (if not already initialized)

For new MySQL instances, you need to initialize the data directory. This usually involves runningmysqld --initializeormysql_install_db(Depending on your MySQL version.) Note that this may overwrite any existing data.

5. Start the MySQL instance

You need to write a startup script for each instance or usesystemdUnit files to manage them. This is usually complex and needs to be customized according to your specific needs and system environment.

A simplified approach is to usemysqld_safeOr run directly in the command linemysqld, but this is not suitable for production environments.

6. Set password and permissions

Each instance has a default root password after initialization (MySQL 5.7.6 and higher). You need to set a separate password and user permissions for each instance.

• Log in to each instance (using--socketand--portoptions).
• useALTER USERStatement to set the root password.
• Create a new user and authorize it.

7. Maintenance and Monitoring

Make sure you have monitoring and backup strategies in place to maintain your multiple MySQL instances.

Notice

• The above steps are a high-level overview and may need to be adjusted depending on your specific environment.
• Running multiple MySQL instances can increase system complexity and resource consumption.
• Make sure your system firewall and network configuration allow access to each instance from the appropriate ports.