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
Vue implements sample code for dragging files from desktop to web page (can display pictures/audio/video)
Effect If you use it, please optimize the code an...
Quickly solve the Chinese input method problem under Linux
Background: I'm working on asset reporting re...
Using puppeteer to implement webpage screenshot function on linux (centos)
You may encounter the following problems when ins...
Docker and iptables and implementation of bridge mode network isolation and communication operations
Docker provides multiple networks such as bridge,...
MySQL InnoDB monitoring (system layer, database layer)
MySQL InnoDB monitoring (system layer, database l...
Vue uses Baidu Maps to realize city positioning
This article shares the specific code of Vue usin...
JavaScript to implement login form
This article example shares the specific code of ...
Introduction to MySQL database performance optimization
Table of contents Why optimize? ? Where to start?...
Implementation of Redis master-slave cluster based on Docker
Table of contents 1. Pull the Redis image 2. Crea...
Implementation of form submission in html
Form submission code 1. Source code analysis <...
Click on the anchor link in JS to scroll smoothly and adjust to the top position freely
Click on the anchor link to scroll smoothly and a...
Design theory: people-oriented design concept
<br />When thoughts were divided into East a...
How to use Portainer to build a visual interface for Docker
Portainer Introduction Portainer is a graphical m...
NestJs uses Mongoose to operate MongoDB
I recently started learning the NestJs framework....
How to distinguish MySQL's innodb_flush_log_at_trx_commit and sync_binlog
The two parameters innodb_flush_log_at_trx_commit...