MySQL is a popular open source database. In the development process, we often use read and write separation, separate libraries and tables, and distinguish development environment from pre-production environment. If only one machine works, you need to use multiple instances when doing a rehearsal.

There are three ways to start a multi-instance database:

  1. Run on different VMS to isolate the operating environment. The disadvantage of this approach is that it is expensive.
  2. Run the MySQL image in Docker. Similar to virtual machines, the operating environment is isolated, but the overhead is much lower than virtual machines.
  3. If you are using a source or compiled database installation, you need to configure MySQL multi-instance.

In the production environment, you are advised to run the database on a physical machine. This chapter describes the third method of configuring MySQL multi-instance.

Initializing the database

After the database is installed, initialize the database using the mysqld command.

mysqld --initialize-insecure --datadir=/home/mysql/3307/data --user=mysql;
mysqld --initialize-insecure --datadir=/home/mysql/3308/data --user=mysql;
mysqld --initialize-insecure --datadir=/home/mysql/3309/data --user=mysql;
Copy the code

Parameter Description:

–initialize-insecure For Mysql 5.7.6 and later, Mysql uses mysqld-initialize or mysqld-initialize -insecure to initialize the database. The former generates A random password, which can be found by searching for A temporary password is generated for in the output log of error.log. The latter uses an empty password. However, the former command is used by default when installing Mysql, which also generates a random password, which is saved in the Mysql log file.

–datadir Specifies the database storage directory

–user Specifies the database user. Note that the user should have read and write permissions on the datadir directory

Mysqld: Can’t create directory ‘/home/ mysql.3307 /data/’ (Errcode: 17 — File exists)

This is because Ubuntu Server has introduced AppArmor since 18.04.

AppArmor is a Linux security application similar to Selinux, but its default security policy defines that individual applications can access system resources and their respective privileges. If you do not set the service’s executable, it will not affect the service even if you change the owner group and 0777 permissions.

Edit the AppArmor profile:

vi /etc/apparmor.d/usr.sbin.mysqld
Copy the code

Allow data dir Access and add your MySQL database directory to the list.

Restart the AppArmor service: systemctl restart AppArmor

Then run the command to initialize the database.

Configuring Multiple Instances

MySQL runs multiple instances of mysqLD_MULTI.

New file/etc/mysql/mysql. Conf. D/mysqld_multi. CNF, content is as follows:

[mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = root #password = # nextcloud [mysqld3307] user = mysql pid-file = /home/mysql/3307/mysqld.pid socket = /home/mysql/3307/mysqld.sock port = 3307 Datadir = /home/ mysql.3307/data lc-messages-dir = /usr/share/mysql skip-external-locking bind-address = 0.0.0.0 key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover-options = BACKUP  query_cache_limit = 1M query_cache_size = 16M expire_logs_days = 10 max_binlog_size = 100M innodb_buffer_pool_size = 1G  innodb_io_capacity = 4000 # wordpress [mysqld3308] user = mysql pid-file = /home/mysql/3308/mysqld.pid socket = /home/mysql/3308/mysqld.sock port = 3308 datadir = /home/mysql/3308/data lc-messages-dir = /usr/share/mysql Skip-external-locking bind-address = 0.0.0.0 key_buffer_size = 16M MAX_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover-options = BACKUP query_cache_limit = 1M query_cache_size = 16M expire_logs_days = 10 max_binlog_size = 100M innodb_buffer_pool_size = 1G innodb_io_capacity = 4000 innodb_io_capacity = 4000 [mysqld3309] user = mysql pid-file = /home/mysql/3309/mysqld.pid socket = /home/mysql/3309/mysqld.sock port = 3309 datadir = /home/mysql/3309/data lc-messages-dir = /usr/share/mysql skip-external-locking bind-address = 0.0.0.0 key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover-options = BACKUP query_cache_limit  = 1M query_cache_size = 16M expire_logs_days = 10 max_binlog_size = 100M innodb_buffer_pool_size = 1G innodb_io_capacity = 4000Copy the code

[mysql3307], [mysql3308], [mysql3309] [mysql3309]

  • Restart the instance

Perform:

mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld_multi.cnf --user=mysql start
Copy the code

If you want to start a specified instance, you can use the instance name after the start argument, as in:

mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld_multi.cnf --user=mysql start 3307
Copy the code

Or, start specified multiple instances:

Mysqld_multi -- defaults - file = / etc/mysql/mysql. Conf. D/mysqld_multi. CNF - user = mysql start 3307330 8Copy the code
  • Check the status
mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld_multi.cnf --user=mysql report
Copy the code

  • Shut down the instance

Execute (you can also close the specified instance, as with start) :

mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld_multi.cnf --user=mysql stop
Copy the code

The instance was not closed when I executed the stop command. Ld_multi. CNF = mysqLD_multi. CNF = mysqLD_multi. Grant shutdown on. To ‘username’ @ ‘localhost’ IDENTIFIED by ‘password’. I used root to start, tried to use mysqladmin to execute the shutdown, can be successful.

Mysqladmin -h127.0.0.1 -p3309 -uroot shutdownCopy the code

All are “Siege Lion · Zheng” unless noted. Link to this article: engr-z.com/375.html