First, the implementation process

SQL statement Execution
Link layer:
- Connection protocol: TCP/IP, socket
- Provides authentication: user name, password, IP, and socket
- Provide professional connection threads: receive SQL. Returns the result
The SQL layer
- Receives upper-level SQL statements
- Validation syntax:SQL_MODE
- Semantic check: four kinds of statement type judgment
- Parsers, optimizers, and actuators
- Use Redis and Tair instead of query caching based on query caching (disabled by default)
- Provide logging
Storage engine layer

2. Single-instance configuration

2.1 Directory Planning

/opt/software/mysql # mysql # install directory /data/mysql # Each instance is named with a port numberCopy the code

2.2 Download Dependency

If mariadb has been installed on your system before, uninstall it first

yum remove -y mariadb* 
yum install -y epel-release
yum update -y
yum install -y cmake gcc-c++ ncurses-devel perl-Data-Dumper boost-doc boost-devel libaio-devel
yum install -y net-tools tree bash-completion lrzsz
Copy the code

2.3 installation

mkdir -p /opt/software && cd /opt/software
pwdWget tar XVF - https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.20-linux-glibc2 Mysql -5.7.20-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql rm -rf Mysql - 5.7.20 - Linux - glibc2.12 - x86_64. Tar. Gz && lsCopy the code

2.4 Adding environment Variables

vim /etc/profile
export PATH=/opt/software/mysql/bin:$PATH
source /etc/profile
Copy the code

2.5 Creating Directories and MySQL Users

MySQL services run in a non-root user environment, so we create a MySQL user first, and then create the related data directory:

Useradd mysql mkdir -p /data/mysql/330{6,7,8,9}/{data,logs,backup} tree /data/mysql/330* chown -r mysql:mysql /opt/software/mysql/* chown -R mysql:mysql /data/mysql/*Copy the code

The following results are obtained

2.6 Initializing the Database

Ensure that the directory where data is stored is empty to avoid unnecessary problems

rm -rf /data/mysql/3306/data/*
mysqld --initialize-insecure  --user=mysql --basedir=/opt/software/mysql --datadir=/data/mysql/3306/data 
Copy the code

2.7 Writing a Configuration File

cat  > /etc/my.cnf <<EOF [mysqld] user=mysql basedir=/opt/software/mysql datadir=/data/mysql/3306/data server_id=6 port=3306 Socket = / TMP/mysql. The sock log_error = / data/mysql / 3306 / logs/mysql_error log # / mysql specified error log in the socket = / TMP/mysql. The sock EOF
Copy the code

cd support-files/
touch /data/mysql/3306/logs/mysql_error.log Create file manually
chown -R /data/mysql/3306/logs/ Mysql > alter user root
./mysql.server start

find / -name mysql.sock
mysql -uroot -p
show databases;
select user.host from mysql.user;
Copy the code

2.8 Using systemctl to Manage MySQL Services

cat  > /etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
Copy the code

netstat -lnp|grep 330 Display the port that is currently enabled
./mysql.server stop
systemctl start mysqld.service
#systemctl start/restart/stop/status/enable/disable mysqld
mysql -uroot -p -S /tmp/mysql.sock

show processlist; View the current client
kill 4# delete client connection ID
Copy the code

2.9 User Management

User rights and remote user management

grant all on *.* to root@'localhost' identified by '123';
grant all on *.* to root@The '%' identified by '123';
flush privileges;
Copy the code

select user,host from mysql.user;
Copy the code

3. Multi-instance configuration

3.1 introduction

A single machine has multiple instances of MySQL running, listening on different ports and providing different services

Data isolation, logical isolation

Multiple instances are implemented with multiple profiles and multiple initiators

3.2 Closing a Single instance

systemctl stop mysqld
mv /etc/my.cnf /etc/my.cnf.bak
Copy the code

3.3 Creating profiles for each instance

mkdir -p /data/mysql/330{6.7.8.9}/logs/errorlog
chown -R mysql:mysql /data/mysql/ *Copy the code

cat > /data/mysql/3307/my.cnf <<EOF
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql.sock
log_error=/data/mysql/3307/logs/errorlog/mysql.log
port=3307
server_id=7
[client]
socket=/data/mysql/3307/mysql.sock
EOF

cat > /data/mysql/3308/my.cnf <<EOF
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3308/data
socket=/data/mysql/3308/mysql.sock
log_error=/data/mysql/3308/logs/errorlog/mysql.log
port=3308
server_id=8
[client]
socket=/data/mysql/3308/mysql.sock
EOF

cat > /data/mysql/3309/my.cnf <<EOF
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3309/data
socket=/data/mysql/3309/mysql.sock
log_error=/data/mysql/3309/logs/errorlog/mysql.log
port=3309
server_id=9
[client]
socket=/data/mysql/3309/mysql.sock
EOF
Copy the code

3.4 Checking Whether the operation is successful

#bash
ls /data/mysql/3307/
# backup data logs my.cnf
cat /data/mysql/3307/my.cnf 
Copy the code

-- [mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql.sock
log_error=/data/mysql/3307/logs/errorlog/mysql.log
port=3307
server_id=7
-- [client]
socket=/data/mysql/3307/mysql.sock
Copy the code

3.5 license

chown -R mysql.mysql /data/mysql/ *Copy the code

3.6 the initialization

systemctl stop mysqld
mv /etc/my.cnf /etc/my.cnf.bak
Copy the code

mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3307/data --basedir=/opt/software/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3308/data --basedir=/opt/software/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3309/data --basedir=/opt/software/mysql
Copy the code

3.7 Using Systemctl to Manage Multiple Instances

Note: 3306 uses mysqld.service file, 3307 needs to be modified

at  > /etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf
LimitNOFILE = 5000
EOF

cat  > /etc/systemd/system/mysqld3308.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3308/my.cnf
LimitNOFILE = 5000
EOF

cat  > /etc/systemd/system/mysqld3309.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3309/my.cnf
LimitNOFILE = 5000
EOF
Copy the code

3.8 start

systemctl start mysqld3307.service systemctl start mysqld3308.service systemctl start mysqld3309.service systemctl start  mysqld.serviceCopy the code

3.9 Connection Management

Connect to the specified instance

Method 1: Socket-based connection (local connection)

mysql -uroot -p123 -S /temp/mysql.sock -e "select @@server_id" mysql -uroot -p -S /data/mysql/3307/mysql.sock -e "select  @@server_id" mysql -uroot -p -S /data/mysql/3308/mysql.sock -e "select @@server_id" mysql -uroot -p -S /data/mysql/3309/mysql.sock -e "select @@server_id"Copy the code

Method 2: IP address-based (remote connection)

Mysql -uroot -p123-p3306 -e "select @@server_id" mysql -uroot -p -h 127.0.0.1 -p3307 -e "select @@server_id" mysql -uroot -p -h 127.0.0.1 -p3307 -e "select @@server_id"Copy the code

The difference between

3306
- Configuration file directory: /etc/my.cnf
- Start systemctl start mysql
- Mysql.sock: /temp/mysql.sock
The 3307-3309
- Configuration file location: in the respective port number directory
- Systemctl starts from mysqld337.service
- systemctl start mysqld3307
- Mysql. The sock: / data/Mysql / 3307 / Mysql. The sock

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

MySQL execution process, single-instance, multi-instance configuration

First, the implementation process

2. Single-instance configuration

2.1 Directory Planning

2.2 Download Dependency

2.3 installation

2.4 Adding environment Variables

2.5 Creating Directories and MySQL Users

2.6 Initializing the Database

2.7 Writing a Configuration File

2.8 Using systemctl to Manage MySQL Services

2.9 User Management

3. Multi-instance configuration

3.1 introduction

3.2 Closing a Single instance

3.3 Creating profiles for each instance

3.4 Checking Whether the operation is successful

3.5 license

3.6 the initialization

3.7 Using Systemctl to Manage Multiple Instances

3.8 start

3.9 Connection Management

MySQL execution process, single-instance, multi-instance configuration

First, the implementation process

2. Single-instance configuration

2.1 Directory Planning

2.2 Download Dependency

2.3 installation

2.4 Adding environment Variables

2.5 Creating Directories and MySQL Users

2.6 Initializing the Database

2.7 Writing a Configuration File

2.8 Using systemctl to Manage MySQL Services

2.9 User Management

3. Multi-instance configuration

3.1 introduction

3.2 Closing a Single instance

3.3 Creating profiles for each instance

3.4 Checking Whether the operation is successful

3.5 license

3.6 the initialization

3.7 Using Systemctl to Manage Multiple Instances

3.8 start

3.9 Connection Management

Related Posts

Some lessons from Golang’s development blog

Java image compression generates thumbnails and watermarks

Protect your cloud native apps with 3 K8S native controls