Teach you step by step to build Mysql single machine multiple instances

Mysql single-machine multi-instance setup instructions

0 Introduction

With the development of Internet technology, the amount of data is becoming larger and larger, and we are in urgent need of a large storage and large analysis system. Although there are data storage methods such as NoSQL database and Hadoop file storage that can solve this problem, relational databases still have their advantages, especially the processing of structured data, and the performance is still excellent. Or, in terms of the company's project development costs, the use of relational databases is simpler and easier to maintain than nosql databases.
Therefore, this article introduces the first step in using Mycat (of course, this step is not necessary) and learns how to build multiple instances of Mysql on a single machine to deal with the problem of slow querying of large amounts of data.

1 Startup item

vim /etc/apparmor.d/usr.sbin.mysqld
/etc/init.d/apparmor reload

AppArmor (Application Armor) is a security module of the Linux kernel. AppArmor allows system administrators to associate each program with a security profile, thereby limiting the functions of the program. . Simply put, AppArmor is an access control system similar to SELinux, through which you can specify which files a program can read, write or run, whether it can open network ports, etc. As a supplement to the traditional Unix discretionary access control module, AppArmor provides a mandatory access control mechanism, which has been integrated into the 2.6 version of the Linux kernel.
View detailed information:
Apparmor——Mandatory access control system in the Linux kernel
http://www.cnblogs.com/-Lei/a...

2 Create new The data directory of the instance

mkdir /var/lib/mysql2   创建目录
chown mysql /var/lib/mysql2  给mysql用户权限

3 Create the database and initialize the database

• mysql 5.7 or below
  mysql_install_db --user=mysql --datadir=/var/lib/ mysql2

• mysql 5.7 or above

1. ##--user=mysql --datadir=/var/lib/mysql2

4 Configure multi-instance configuration files

[mysqld_multi]  
mysqld     = /install/mysql/bin/mysqld_safe  
mysqladmin = /install/mysql/bin/mysqladmin  
user       = root  
  
# The MySQL server  
[mysqld1]  
port            = 3306  
socket          = /tmp/mysql.sock  
datadir         =/var/lib/mysql  
pid-file        =/var/lib/mysql/mysql.pid  
user            =mysql  
  
log-bin         =master-bin  
log-bin-index           =master-bin.index  
...
[mysqld2]  
port            = 3307  
socket          =/tmp/mysql2.sock  
datadir         =/var/lib/mysql2  
pid-file        =/var/lib/mysql2/mysql.pid  
user            =mysql  
...

5 Start an instance

mysqld_multi   --defaults-file=/etc/mysql/my_multi.cnf start 1
mysqld_multi   --defaults-file=/etc/mysql/my_multi.cnf start 2

6 Log in to Mysql

# 登陆Mysql服务器，执行mysql命令进入mysql控制台
mysql -uroot -P3307 -p -S/tmp/mysql2.sock
# 不用输入密码，直接回车
# 查看当前用户
select User from mysql.user;
# 创建Mysql用户test，并赋权限
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
GRANT GRANT OPTION ON *.* TO 'test'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'test'@'%';

Recommended learning: "

mysql video tutorial"

The above is the detailed content of Teach you step by step to build Mysql single machine multiple instances. For more information, please follow other related articles on the PHP Chinese website!