数据库备份与恢复及多实例部署_--single-transaction --flush-logs --master-data=2 -CSDN博客

本文链接：https://blog.csdn.net/qq_53078033/article/details/126047471

数据库备份与恢复及多实例部署

文章目录

数据库备份与恢复及多实例部署
mysql多实例部署
数据库备份与恢复及多实例部署
mysql多实例部署

数据库的备份

冷备份:数据库服务关闭，把数据库对于的数据目录下的数据文件拷贝一份物理备份（不建议使用）
温备份:??
热备份:数据库服务正常运行的时候，直接对数据库备份（常用）
数据库热备份的方式手段：

全量备份：将数据库的所有数据全部备份
增量备份：基于第一次全备的基础上增加备份，第二次增备是基于上一次增备的基础上（优点：备份快，占用少。缺点：恢复数据难要按照增备的顺序恢复，顺序乱了恢复不了一般不用）
差异备份：第一次是全备；第二次差异是备份第一次完备产生变化的信息然后出入到全备中，第三次备份基于上一次全备变化的信息和第二次的信息，在插入一份到上一次全备中

mysql备份工具mysqldump

//语法：
mysqldump [OPTIONS] database [tables …]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]

//常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

热备份

1.恢复表

[root@localhost ~]# mysqldump zuoye students > zuoye
[root@localhost ~]# vim zuoye
[root@localhost ~]# mysql -uroot -p@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> use zuoye
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> delete from students where id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | Jim    |   24 | 女   |    175 |         2 |
|  6 | John   |   21 | 女   |    172 |         4 |
|  7 | Lily   |   22 | 男   |    165 |         4 |
|  8 | Susan  |   23 | 男   |    170 |         5 |
|  9 | Thomas |   22 | 女   |    178 |         5 |
| 10 | Tom    |   23 | 女   |    165 |         5 |
| 11 | LiMing |   22 | 男   |    180 |         7 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)

mysql> 

[root@localhost ~]# mysql -uroot  -p@123 zuoye < zuoye
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# 


mysql> select * from students;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | Jim    |   24 | 女   |    175 |         2 |
|  6 | John   |   21 | 女   |    172 |         4 |
|  7 | Lily   |   22 | 男   |    165 |         4 |
|  8 | Susan  |   23 | 男   |    170 |         5 |
|  9 | Thomas |   22 | 女   |    178 |         5 |
| 10 | Tom    |   23 | 女   |    165 |         5 |
| 11 | LiMing |   22 | 男   |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)

mysql>

恢复数据库

[root@localhost ~]# mysql  -uroot -p@123 mysql < zuoye
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zbc                |
| zuoye              |
+--------------------+
6 rows in set (0.00 sec)

mysql>

差异备份与恢复

mysql差异备份

开启MySQL服务器的二进制日志功能

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve

server-id=1         //设置服务器标识符
log-bin=mysql_bin    //开启二进制日志功能

[root@zzz ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. ERROR! The server quit without updating PID file (/opt/data/mysql.pid).
[root@zzz ~]# 

[root@zzz ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%y')
[root@zzz ~]# ls
all-22  home         mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz  passwd  yyds
file    ifcfg-ens33  outfile                                     ss
[root@zzz ~]# 


对数据进行增删
mysql> delete from tb_course where id =1
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> insert tb_course(course_name) values('english');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | english     |
+----+-------------+
5 rows in set (0.00 sec)

mysql> 
mysql> drop database zuoye;
Query OK, 1 row affected (0.00 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 


[root@zzz ~]# ll /opt/data/
total 122988
-rw-r-----. 1 mysql mysql       56 Jul 27 05:57 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 05:57 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 05:57 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 05:57 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 27 05:57 client-key.pem
-rw-r-----. 1 mysql mysql      311 Jul 29 13:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 14:03 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 14:03 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 05:57 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 14:00 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jul 27 05:57 mysql
-rw-r-----. 1 mysql mysql      858 Jul 29 14:03 mysql_bin.000002
-rw-r-----. 1 mysql mysql       19 Jul 29 14:00 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 29 13:57 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 27 05:57 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 27 05:57 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 05:57 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 05:57 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 05:57 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 05:57 sys
-rw-r-----. 1 mysql mysql    28465 Jul 29 13:57 zzz.err
[root@zzz ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@zzz ~]# ll /opt/data/
total 122992
-rw-r-----. 1 mysql mysql       56 Jul 27 05:57 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 05:57 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 05:57 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 05:57 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 27 05:57 client-key.pem
-rw-r-----. 1 mysql mysql      311 Jul 29 13:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 14:05 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 14:05 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 05:57 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 14:00 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jul 27 05:57 mysql
-rw-r-----. 1 mysql mysql      905 Jul 29 14:05 mysql_bin.000002
-rw-r-----. 1 mysql mysql      154 Jul 29 14:05 mysql_bin.000003
-rw-r-----. 1 mysql mysql       38 Jul 29 14:05 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 29 13:57 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 27 05:57 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 27 05:57 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 05:57 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 05:57 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 05:57 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 05:57 sys
-rw-r-----. 1 mysql mysql    28465 Jul 29 13:57 zzz.err
[root@zzz ~]# cd /opt/data/
[root@zzz data]# cat mysql_bin.index 
./mysql_bin.000002
./mysql_bin.000003
[root@zzz data]#

完全恢复数据库

[root@zzz data]# cd 
[root@zzz ~]# mysql < all-22.sql 
[root@zzz ~]# mysql < /root/all-22.sql 
[root@zzz ~]# 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zuoye              |
+--------------------+
5 rows in set (0.00 sec)

mysql> 
mysql> use zuoye
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select *from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
+----+-------------+
5 rows in set (0.00 sec)

mysql>

恢复差异备份

mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000002 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 219 | Query          |        10 |         292 | BEGIN                                 |
| mysql_bin.000002 | 292 | Table_map      |        10 |         348 | table_id: 143 (zuoye.tb_course)       |
| mysql_bin.000002 | 348 | Write_rows     |        10 |         396 | table_id: 143 flags: STMT_END_F       |
| mysql_bin.000002 | 396 | Xid            |        10 |         427 | COMMIT /* xid=482 */                  |
| mysql_bin.000002 | 427 | Anonymous_Gtid |        10 |         492 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 492 | Query          |        10 |         565 | BEGIN                                 |
| mysql_bin.000002 | 565 | Table_map      |        10 |         621 | table_id: 143 (zuoye.tb_course)       |
| mysql_bin.000002 | 621 | Delete_rows    |        10 |         667 | table_id: 143 flags: STMT_END_F       |
| mysql_bin.000002 | 667 | Xid            |        10 |         698 | COMMIT /* xid=484 */                  |
| mysql_bin.000002 | 698 | Anonymous_Gtid |        10 |         763 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 763 | Query          |        10 |         858 | drop database zuoye                   |
| mysql_bin.000002 | 858 | Rotate         |        10 |         905 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)

mysql> 


[root@zzz ~]# mysqlbinlog --stop-position=794 /opt/data/mysql_bin.000002 |mysql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql多实例部署

1.下载二进制格式的mysql软件包

[root@zzz ~]#  wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
--2022-07-29 15:28:28--  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 23.199.216.78, 2600:1406:3400:681::2e31, 2600:1406:3400:68d::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.199.216.78|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz [following]
--2022-07-29 15:28:30--  https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 23.33.16.230
Connecting to cdn.mysql.com (cdn.mysql.com)|23.33.16.230|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 674830866 (644M) [application/x-tar-gz]
Saving to: ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz’

mysql-5.7.38-linux-glib 100%[===============================>] 643.57M  1.60MB/s    in 4m 33s  

2022-07-29 15:33:04 (2.36 MB/s) - ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz’ saved [674830866/674830866]

[root@zzz ~]#

2.配置用户和组并解压二进制程序至/usr/local下
//创建用户和组

[root@zzz ~]# groupadd -r mysql
[root@zzz ~]# useradd -M -s /sbin.nologin -r -g mysql mysql
[root@zzz ~]# id mysql
uid=994(mysql) gid=665(mysql) groups=665(mysql)
[root@zzz ~]#

3.解压软件至/usr/local/

[root@zzz ~]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@zzz ~]# cd /usr/local/
[root@zzz local]# ls
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.38-linux-glibc2.12-x86_64  share
[root@zzz local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64/'
[root@zzz local]# ll
total 0
drwxr-xr-x. 2 root root   6 May 11  2019 bin
drwxr-xr-x. 2 root root   6 May 11  2019 etc
drwxr-xr-x. 2 root root   6 May 11  2019 games
drwxr-xr-x. 2 root root   6 May 11  2019 include
drwxr-xr-x. 2 root root   6 May 11  2019 lib
drwxr-xr-x. 2 root root   6 May 11  2019 lib64
drwxr-xr-x. 2 root root   6 May 11  2019 libexec
lrwxrwxrwx. 1 root root  36 Jul 29 15:42 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 29 15:40 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 May 11  2019 sbin
drwxr-xr-x. 5 root root  49 Jun 28 02:50 share
drwxr-xr-x. 2 root root   6 May 11  2019 src
[root@zzz local]#

3.修改目录/usr/local/mysql的属主属组

[root@zzz local]# chown -R mysql.mysql /usr/local/mysql
[root@zzz local]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 Jul 29 15:42 /usr/local/mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
[root@zzz local]#

4.配置环境变量

[root@zzz local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@zzz local]# . /etc/profile.d/mysql.sh
[root@zzz local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@zzz local]#

5.创建各实例数据存放的目录

[root@zzz local]# 
[root@zzz local]# mkdir -p /opt/data/{3306,3307,3308}
[root@zzz local]# chown -R mysql.mysql /opt/data/
[root@zzz local]# ll /opt/data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul 29 15:46 3306
drwxr-xr-x. 2 mysql mysql 6 Jul 29 15:46 3307
drwxr-xr-x. 2 mysql mysql 6 Jul 29 15:46 3308
[root@zzz local]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 3308

3 directories, 0 files
[root@zzz local]#

6.初始化实例

[root@zzz ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2022-07-29T07:48:49.626067Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-29T07:48:49.908611Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-29T07:48:49.946727Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-29T07:48:50.014836Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e2606034-0f12-11ed-b7d3-000c29c7a87b.
2022-07-29T07:48:50.016338Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-29T07:48:50.131818Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:48:50.131843Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:48:50.132290Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-29T07:48:50.323184Z 1 [Note] A temporary password is generated for root@localhost: mO0ZJLw4g?6k
[root@zzz ~]# echo 'mO0ZJLw4g?6k'
mO0ZJLw4g?6k
[root@zzz ~]# echo 'mO0ZJLw4g?6k' > /root/3306passwd
[root@zzz ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2022-07-29T07:49:58.016431Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-29T07:49:58.203443Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-29T07:49:58.239447Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-29T07:49:58.296321Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0b134dbc-0f13-11ed-bbcc-000c29c7a87b.
2022-07-29T07:49:58.297926Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-29T07:49:58.479904Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:49:58.479961Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:49:58.480406Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-29T07:49:58.498303Z 1 [Note] A temporary password is generated for root@localhost: wIwte:pJE3QA
[root@zzz ~]# echo 'wIwte:pJE3QA' > /root/3307passwd
[root@zzz ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2022-07-29T07:50:39.459311Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-29T07:50:39.666551Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-29T07:50:39.700837Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-29T07:50:39.761079Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 23ca52e7-0f13-11ed-bc54-000c29c7a87b.
2022-07-29T07:50:39.762412Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-29T07:50:39.960420Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:50:39.960439Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:50:39.960837Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-29T07:50:40.003629Z 1 [Note] A temporary password is generated for root@localhost: tVe!>tP.v8r,
[root@zzz ~]# echo 'tVe!>tP.v8r,' > /root/3308passwd
[root@zzz ~]#

7.安装perl

[root@zzz ~]# yum -y install perl

8.配置配置文件/etc/my.cnf

[root@zzz ~]# vim /etc/my.cnf
[root@zzz ~]# cat /etc/my.cnf 
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log

[root@zzz ~]#

9.启动实例

[root@zzz ~]# mysqld_multi start 3306
[root@zzz ~]# mysqld_multi start 3307
[root@zzz ~]# mysqld_multi start 3308
[root@zzz ~]# ss -antl
State       Recv-Q      Send-Q            Local Address:Port             Peer Address:Port      
LISTEN      0           128                     0.0.0.0:22                    0.0.0.0:*         
LISTEN      0           80                            *:3306                        *:*         
LISTEN      0           80                            *:3307                        *:*         
LISTEN      0           80                            *:3308                        *:*         
LISTEN      0           128                        [::]:22                       [::]:*         
[root@zzz ~]#

10.初始化密码

[root@zzz ~]# mysql -uroot -p'mO0ZJLw4g?6k' -S /tmp/mysql3306.sock -e 'set password = password("@123");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@zzz ~]# cat 3307passwd 
wIwte:pJE3QA
[root@zzz ~]# mysql -uroot -p'wIwte:pJE3QA' -S /tmp/mysql3307.sock -e 'set password = password("@123");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@zzz ~]# mysql -uroot -p'tVe!>tP.v8r,' -S /tmp/mysql3308.sock -e 'set password = password("@123");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@zzz ~]#