Multi-instance management skills in MySQL

MySQL is a very popular relational database management system that is widely used in various fields. In actual application scenarios, we may need to run multiple MySQL instances simultaneously on the same server to meet the needs of different applications. This article will introduce multi-instance management techniques in MySQL to help you better manage multiple MySQL instances.

1. Install MySQL multiple instances

When installing MySQL, we can choose to install multiple MySQL instances. Installing multiple MySQL instances is the same as installing a single MySQL instance. You just need to select "Custom" mode and specify the installation directory when running the installer.

2. Configuring MySQL multiple instances

After the installation is complete, we need to configure parameters for each MySQL instance. The following are some parameters that need attention:

Port: By default, MySQL uses port 3306. Each instance can be assigned a different port to ensure independence from each other.

Data directory: Each instance needs to use a different data directory to avoid mutual interference. The data directory can be set to any location, but it is best to choose a separate disk partition.

Log files: Each instance needs to have a different log file for easy tracking and troubleshooting.

The following is a snippet of a sample MySQL multi-instance configuration file:

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /var/log/mysqld_multi.log

[mysqld1]
port = 3307
datadir = /var/lib/mysql1
socket = /var/lib/mysql1/mysql.sock
pid-file = /var/run/mysql/mysqld1.pid
log-bin = /var/log/mysql/mysql-bin.log
...

[mysqld2]
port = 3308
datadir = /var/lib/mysql2
socket = /var/lib/mysql2/mysql.sock
pid-file = /var/run/mysql/mysqld2.pid
log-bin = /var/log/mysql/mysql-bin.log
...

3. Starting and shutting down a MySQL instance

The mysqld_multi script can be used to start and shut down MySQL instance. The following are common commands for mysqld_multi:

Start all MySQL instances:

$ sudo /usr/bin/mysqld_multi start

Close all MySQL instances:

$ sudo /usr/bin/mysqld_multi stop

Start the specified MySQL instance:

$ sudo /usr/bin/mysqld_multi start 1

Close the specified MySQL instance:

$ sudo /usr/bin/mysqld_multi stop 1

4. Permission Management

In a multi-instance environment, we need to assign different users and passwords to each instance to ensure data security Safety and protection. You can use the GRANT and REVOKE commands to assign different permissions to each instance.

The following is a sample SQL command:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

5. Monitoring MySQL Instances

We need to monitor the performance and health of each instance. You can use MySQL's own monitoring tools, such as Explain, Show Status, and Show Processlist commands, to monitor the performance of the MySQL instance.

6. Backup and Recovery

In a multi-instance environment, backup and recovery are more complex than with a single instance. Each instance's data and log files need to be backed up separately, with troubleshooting guidance provided to recover from failures. You can use MySQL's own backup tools mysqldump and mysqlhotcopy, or you can use third-party backup tools.

In short, when managing multiple MySQL instances, we need to maintain the independence and security of each instance and perform necessary maintenance tasks such as backup and monitoring. Through effective multi-instance management, we can better meet the needs of different applications and improve the reliability and maintainability of MySQL servers.

The above is the detailed content of Multi-instance management skills in MySQL. For more information, please follow other related articles on the PHP Chinese website!