04. MySQL Cluster

Time:2021-12-3

1、 Software installation and deployment (stand-alone)

01. Catalog planning

MySQL version: 5.7.26
Installation method: binary installation
Deployment platform: CentOS 7.6
Host IP address: 192.168.66.57
Download address:

https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

Software Directory:

mkdir /opt/src
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

02. Data directory

mkdir -p /data/mysql

03. Create and manage MySQL users

I think we use binary installation, and the system will not automatically MySQL users

useradd -s /sbin/nologin -M mysql 

04. Authorize MySQL users

chown -R mysql:mysql /data/mysql/
chown -R mysql:mysql /opt/*

05. Unzip the source package

cd /opt/src
tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
#Create soft connection
ln -s /opt/src/mysql-5.7.20-linux-glibc2.12-x86_64 /opt/mysql

06. Setting environment variables

vim /etc/profile
export PATH=/opt/mysql/bin:$PATH
source /etc/profile

07. Removal of legacy environment

rpm -qa |grep mariadb
yum -y remove mariadb-libs-5.5.56-2.el7.x86_64
rm -rf /etc/my.cnf

08. Initialize database

mysqld --initialize-insecure --user=mysql  --basedir=/opt/mysql  --datadir=/data/mysql
vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/opt/mysql
datadir=/data/mysql
server_id=6
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

09. Configure startup script

#Default startup script
/opt/mysql/support-files/mysql.server start
#Let service manage MySQL
cp  /opt/mysql/support-files/mysql.server /etc/init.d/mysqld
#However, SYSTEMd manages MySQL scripts
vim /etc/systemd/system/mysqld.service  
[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/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

10. Start MySQL

systemctl start mysqld.service
mysql -V
mysql -uroot -p

11. Set MySQL password

mysqladmin -uroot -p password 123456

==-Single node scripting (binary package installation)-==

#!/bin/bash

#Time: 17:04, May 20, 2021
#Project: single host binary deployment MySQL
#1. Create directory
mkdir -p /data/mysql  &>/dev/null
#2. Create user
useradd -s /sbin/nologin -M mysql   &>/dev/null
#3. Download and unzip the software package
mkdir -p /opt/src
cd /opt/src/
if [ ! -f "mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz" ];then
    wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz 
    tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
    elif [ ! -d "mysql-5.7.20-linux-glibc2.12-x86_64" ];then
        tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
    else
    echo "already exists"
fi
#4. Create soft links
ln -s `pwd`/mysql-5.7.20-linux-glibc2.12-x86_64 /opt/mysql &>/dev/null
#5. Configure environment variables
cat >> /etc/profile << EOF
export PATH=/opt/mysql/bin:$PATH
EOF

source /etc/profile
sleep 3
#6. Authorize the directory
chown -R mysql:mysql /data/mysql/
chown -R mysql:mysql /opt/mysql
#7. Clean up the environment
yum -y remove `rpm -qa |grep mariadb` &>/dev/null
#8. Initialize the database - no password
mysqld --initialize-insecure --user=mysql  --basedir=/opt/mysql  --datadir=/data/mysql &>/dev/null
#9. Prepare configuration file
cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/opt/mysql
datadir=/data/mysql
server_id=6
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
EOF
#10. Configure startup script
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/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
#11. Start MySQL
systemctl daemon-reload
systemctl start mysqld.service
systemctl status mysqld.service
mysql -V

#11. Password modification
# mysqladmin -uroot -p password 123456
#================================= end of script================================

2、 Master slave replication

01. Introduction

1. Binary log based replication
2. The modification of the master database will record the binary log
3. The slave library will request new binary logs and play them back, and finally achieve master-slave data synchronization
4. Core functions of master-slave replication:
Secondary backup, handling physical damage
Expand new architectures: high availability, high performance, distributed architecture, etc

02. Prepare multiple instances

Prepare multiple instance directories

mkdir -p /data/330{7,8,9}/data

Prepare profile

cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

Initialize three sets of data

mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql

SYSTEMd manages multiple instances

cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service

vim mysqld3307.service
ExecStart=/opt/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
ExecStart=/opt/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
ExecStart=/opt/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf

to grant authorization

chown -R mysql.mysql /data/*

start-up

systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

Validate multiple instances

netstat -lnp|grep 330
mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"

03. Copy process

(1) 2 or more database instances
(2) The binary log needs to be opened for the main library
(3) server_ Different IDS should be used to distinguish different nodes
(4) The master database needs to establish a dedicated replication slave
(5) The secondary library should “make up lessons” by backing up the primary library and restoring it
(6) Manually tell the library to copy some information (IP port user pass, starting point of binary log)
(7) A dedicated replication thread should be started from the library

04. Set up master-slave replication

Check whether the main database opens the binary log and the service of the node (instance)_ Is the ID the same

egrep "log_bin|server_id" /data/330*/my.cnf

Master library create replication user

mysql -uroot -S /data/3307/mysql.sock -e "grant replication slave on *.* to [email protected]'192.168.66.%' identified by '123'"

Back up the master library and restore to the slave library

mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction  -R --triggers >/tmp/full.sql
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=653;
mysql -S /data/3308/mysql.sock
mysql>source /backup/full.sql

Tell the slave library the information of the master library

#View command help
help change master to
[[email protected] ~]# mysql -S /data/3308/mysql.sock 

CHANGE MASTER TO 
MASTER_HOST='192.168.66.57',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=2987,
MASTER_CONNECT_RETRY=10;

Open master-slave dedicated thread from library

start slave ;

Check replication status

mysql>show slave  status \G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

05. Write data test

Create a database in the master database, create a new table, insert data, and then view the database information from the slave database. It is found that the slave database completely backs up the information of the master database to its own local database.