MySQL tutorial on how to deploy multiple instances on a single machine using mysqld_multi
|
Preface Everyone should have realized that with the development of hardware, multi-core Linux systems have become a general trend, and MySQL is a single-process multi-threaded system, so its inherent utilization of multi-process is not very high. Although version 5.6 has made a lot of improvements in this regard, it has not reached 100%. Therefore, in order to make full use of system resources, MySQL has its own supplement, which is to deploy multiple instances, one instance per port. mysqld_multi is designed to manage multiple mysqld processes running on the same machine, with each process using different socket files and listening on different ports. mysqld_multi can start, shut down, or report the status of these mysqld processes in batches. Without further ado, let’s take a look at the detailed introduction. 1. MySQL compilation and installation:cd /usr/local/src wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz tar zxvf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz mv mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql cd /usr/local/mysql useradd mysql mkdir -p /data/mysql chown -R mysql:mysql /data/mysql ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql cp support-files/my-default.cnf /etc/my.cnf cp support-files/mysql.server /etc/init.d/mysqld 2. Prepare the first multi-instance 33072.1 Create a directory: mkdir /usr/local/mysql3307 chown -R mysql.mysql /usr/local/mysql3307/ mkdir -p /data/mysql3307 chown -R mysql.mysql /data/mysql3307 mkdir -p /home/data/mysql3307/binlog chown -R mysql.mysql /home/data/mysql3307 2.2 Configuration File [root@zhdya01 ~]# vim /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during installation, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin log = /var/log/mysqld_multi.log [mysqld1] socket = /usr/local/mysql3307/mysql.sock port = 3307 pid-file = /usr/local/mysql3307/mysql.pid datadir = /data/mysql3307 log_bin=/home/data/mysql3307/binlog server-id = 1 innodb_buffer_pool_size = 128M innodb_flush_log_at_trx_commit = 0 2.3 Initialize the database /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3307 3. Prepare the second multi-instance 33083.1 Create a directory: mkdir /usr/local/mysql3308 chown -R mysql.mysql /usr/local/mysql3308/ mkdir -p /data/mysql3308 chown -R mysql.mysql /data/mysql3308 mkdir -p /home/data/mysql3308/binlog chown -R mysql.mysql /home/data/mysql3308 3.2 Configuration File [root@zhdya01 ~]# vim /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during installation, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin log = /var/log/mysqld_multi.log [mysqld1] socket = /usr/local/mysql3307/mysql.sock port = 3307 pid-file = /usr/local/mysql3307/mysql.pid datadir = /data/mysql3307 log_bin=/home/data/mysql3307/binlog server-id = 1 innodb_buffer_pool_size = 128M innodb_flush_log_at_trx_commit = 0 [mysqld2] socket = /usr/local/mysql3308/mysql.sock port = 3308 pid-file = /usr/local/mysql3308/mysql.pid datadir = /data/mysql3308 log_bin=/home/data/mysql3308/binlog server-id = 2 innodb_buffer_pool_size = 128M innodb_flush_log_at_trx_commit = 0 3.3 Initialize the database /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3308 4. Start multiple instances/etc/init.d/mysqld start mysqld_multi --defaults-extra-file=/etc/my.cnf start 1,2 [root@zhdya01 bin]# !net netstat -lntp -lntp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1241/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2087/master tcp6 0 0 :::3306 :::* LISTEN 4406/mysqld tcp6 0 0 :::3307 :::* LISTEN 4197/mysqld tcp6 0 0 :::3308 :::* LISTEN 3359/mysqld tcp6 0 0 :::8080 :::* LISTEN 2222/java tcp6 0 0 :::22 :::* LISTEN 1241/sshd tcp6 0 0 ::1:25 :::* LISTEN 2087/master 5. Check the startup status[root@zhdya01 bin]# mysqld_multi --defaults-extra-file=/etc/my.cnf report Reporting MySQL servers MySQL server from group: mysqld1 is running MySQL server from group: mysqld2 is running 6. Stop multiple instances[root@zhdya01 bin]# mysqld_multi --defaults-extra-file=/etc/my.cnf stop 1,2 [root@zhdya01 bin]# mysqld_multi --defaults-extra-file=/etc/my.cnf report Reporting MySQL servers MySQL server from group: mysqld1 is not running MySQL server from group: mysqld2 is not running 7. Log in to the MySQL instance separately[root@zhdya01 bin]# mysql --socket=/usr/local/mysql3307/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.35-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> Summarize The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM. You may also be interested in:
|
<<: Nginx configuration cross-domain request Access-Control-Allow-Origin * detailed explanation
>>: How to implement an array lazy evaluation library in JavaScript
Recommend
Native js to achieve accordion effect
In actual web page development, accordions also a...
Detailed tutorial on installing mysql-8.0.13 (zip installation) on windows 10 system
Installation Environment Description •System vers...
Detailed explanation of CSS BEM writing standards
BEM is a component-based approach to web developm...
How to deploy redis in linux environment and install it in docker
Installation Steps 1. Install Redis Download the ...
How to use IDEA to create a web project and publish it to tomcat
Table of contents Web Development 1. Overview of ...
arcgis.js controls the display range of the map body to automatically bounce back when it exceeds the area (implementation ideas)
Table of contents background Effect Ideas backgro...
A simple example of using Vue3 routing VueRouter4
routing vue-router4 keeps most of the API unchang...
A brief analysis of the four import methods and priorities in CSS
First: 4 ways to introduce CSS There are four way...
Case analysis of several MySQL update operations
Table of contents Case Study Update account balan...
CSS makes tips boxes, bubble boxes, and triangles
Sometimes our pages will need some prompt boxes o...
Incomplete solution for using input type=text value=str
I encountered a very strange problem today. Look a...
How to create a stored procedure in MySQL and add records in a loop
This article uses an example to describe how to c...
Vue custom bullet box effect (confirmation box, prompt box)
This article example shares the specific code of ...
Interpretation of syslogd and syslog.conf files under Linux
1: Introduction to syslog.conf For different type...
Detailed explanation of MySQL combined query
Using UNION Most SQL queries consist of a single ...