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
How to use mysql to complete the data generation in excel
Excel is the most commonly used tool for data ana...
Linux system prohibits remote login command of root account
ps: Here is how to disable remote login of root a...
In-depth understanding of MySQL self-connection and join association
1. MySQL self-connection MySQL sometimes needs to...
Detailed explanation of MySQL clustered index and non-clustered index
1. Clustered Index Table data is stored in the or...
Summary of Linux file directory management commands
touch Command It has two functions: one is to upd...
Solutions to problems using addRoutes in Vue projects
Table of contents Preface 1. 404 Page 1. Causes 2...
Friendly Alternatives to Find Tool in Linux
The find command is used to search for files in a...
Detailed explanation of the concepts, principles and common usage of MySQL stored procedures
This article uses examples to explain the concept...
Vue implements zoom in, zoom out and drag function
This article example shares the specific code of ...
JavaScript to achieve simple drag effect
This article shares the specific code of JavaScri...
Implementation of fastdfs+nginx cluster construction
1. Introduction to fastdfs 1. What is fastdfs Fas...
CSS style to center the HTML tag in the browser
CSS style: Copy code The code is as follows: <s...
How to automatically backup the script for Linux servers (mysql, attachment backup)
1. Create the backup.sh script file #!/bin/sh SOU...
Various methods to implement the prompt function of text box in html
You can use the attribute in HTML5 <input="...
JavaScript basics of this pointing
Table of contents this Method In the object Hidde...