Detailed explanation of the steps for deploying MySQL multi-instance in Linux environment

The key to deploying MySQL multi-instances under Linux is to configure independent data directories and configuration files for each instance. Specific steps: 1. Create an independent instance directory; 2. Copy and modify the configuration file to ensure that the datadir and port parameters of each instance are unique; 3. Use mysql_install_db to initialize the database of each instance; 4. Register each instance as a system service for management; 5. Reasonably allocate system resources and perform performance tuning, and back up data regularly. Only by understanding the principles behind these steps can we effectively avoid errors and ensure the stable operation of multiple instances.

Play with MySQL multiple instances under Linux: A sharing of experience of an old bird

Many friends asked me how to deploy multiple MySQL instances on Linux systems, which cannot be done simply by copying and pasting. In this article, I will take you into the deep understanding of this process. It is not just a simple step, but more importantly, understanding the principles behind it and how to avoid those crazy pitfalls. After reading, you will be able to independently deploy and manage multiple MySQL instances and have a deeper understanding of the underlying mechanism of MySQL.

Basic knowledge lays the foundation: The limitations of single instances

Before we start, we need to understand why multiple instances are needed. A MySQL instance has only one listening port and can only serve one application. If you have multiple applications that require using MySQL databases, or need to isolate different database environments (such as development, testing, production), then a single instance seems to be overwhelming. Multi-instance deployment can perfectly solve this problem, making your MySQL service more flexible and robust.

Core: The magic of data directories and configuration files

The key to deploying multiple instances is to cleverly utilize data directories and configuration files. Each MySQL instance needs to have its own independent data directory (storage database files) and configuration files (my.cnf). In the configuration file, the most important parameters are datadir (data directory) and port (listening port). Remember, these two parameters must be unique in different instances.

Let's look at a practical example. Suppose we want to deploy two instances, named mysql57 and mysql80:

 <code class="language-bash"># 创建两个实例目录<br>sudo mkdir -p /data/mysql57 /data/mysql80</code><h1 id="Copy-the-configuration-files-in-the-MySQL-installation-directory-and-modify-the-port-and-data-directory"> Copy the configuration files in the MySQL installation directory and modify the port and data directory</h1><p> sudo cp /etc/my.cnf /etc/my.cnf.mysql57<br> sudo cp /etc/my.cnf /etc/my.cnf.mysql80</p><h1 id="Modify-etc-my-cnf-mysql"> Modify /etc/my.cnf.mysql57</h1><p> sudo sed -i 's/port=3306/port=3307/g' /etc/my.cnf.mysql57<br> sudo sed -i 's#datadir=.*/var/lib/mysql#datadir=/data/mysql57#g' /etc/my.cnf.mysql57</p><h1 id="Modify-etc-my-cnf-mysql"> Modify /etc/my.cnf.mysql80</h1><p> sudo sed -i 's/port=3306/port=3308/g' /etc/my.cnf.mysql80<br> sudo sed -i 's#datadir=.*/var/lib/mysql#datadir=/data/mysql80#g' /etc/my.cnf.mysql80</p><h1 id="Initialize-the-database"> Initialize the database</h1><p> sudo mysql_install_db --user=mysql --datadir=/data/mysql57<br> sudo mysql_install_db --user=mysql --datadir=/data/mysql80</p><h1 id="Start-the-instance-the-commands-need-to-be-adjusted-according-to-your-MySQL-version-and-installation-method"> Start the instance (the commands need to be adjusted according to your MySQL version and installation method)</h1><p> sudo systemctl start mysqld.mysql57<br> sudo systemctl start mysqld.mysql80</p><h1 id="verify"> verify</h1><p> sudo mysql -P 3307 -u root -p<br> sudo mysql -P 3308 -u root -p </p>

Code interpretation and trap evasion

In this code, we use the sed command to modify the configuration file, which is an efficient batch modification method. However, be sure to double-check the modified configuration file to ensure there are no unexpected errors. A small error may cause the instance to fail to start or even the data is corrupted.

In addition, the mysql_install_db command is used to initialize the database, which is a key step in creating a new instance. This command requires root permissions and requires the correct data directory to be specified.

Advanced Tips: System Service Management

To facilitate management, it is recommended to register each MySQL instance as a system service. This way, you can use the systemctl command to start, stop and restart the instance. The specific registration method depends on your Linux distribution and MySQL installation method, please refer to the relevant documentation.

Performance Tuning and Best Practices

Multi-instance deployment consumes a lot of system resources, so resource allocation needs to be planned reasonably. The configuration file of each instance needs to be tuned according to actual conditions, such as adjusting the cache size, limiting the number of connections, etc. At the same time, data must be backed up regularly to prevent data loss. Code specifications and comments are also important, which facilitate future maintenance and upgrades.

Summary: It's not just steps, but also understanding

Remember that deploying multiple instances of MySQL is not just about copying the steps, but more importantly, understanding the principles behind it. Only by understanding the importance of data catalogs, configuration files and system service management can we truly master this technology and be able to deal with various emergencies. I hope this article can help you successfully complete the deployment of MySQL multi-instances and provide some help for your database management path. I wish you all the best!

The above is the detailed content of Detailed explanation of the steps for deploying MySQL multi-instance in Linux environment. For more information, please follow other related articles on the PHP Chinese website!