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
备份整个数据库（全备）
先查看数据库里面有什么
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| durui |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use durui;
Database changed
mysql> show tables;
+------------------+
| Tables_in_durui |
+------------------+
| tb_course |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
[root@localhost ~]# mysqldump -uroot -p -hlocalhost --all-databases > all-$(date '+%y%d%m%H%M%S').sql
Enter password:
[root@localhost ~]# ls
all-223107152201.sql
备份durui库的tb_course表和tb_students_info表
[root@localhost ~]# mysqldump -uroot -p -hlocalhost --databases durui tb_course tb_students_info > dr-$(date '+%y%d%m%H%M%S').sql
Enter password:
[root@localhost ~]# ls
dr-223107152838.sql
备份durui数据库
[root@localhost ~]# mysqldump -uroot -p -hlocalhost --databases durui > durui-$(date '+%y%d%m%H%M%S').sql
Enter password:
[root@localhost ~]# ls
durui-223107153146.sql
模拟误删durui数据库
mysql> drop database durui;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql数据恢复
恢复durui数据库
[root@localhost ~]# mysql -uroot -hlocalhost -p < durui-223107153146.sql
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| durui |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
恢复durui数据库的 tb_course 表和tb_students_info表
mysql> show tables;
Empty set (0.00 sec)
mysql> source dr-223107152838.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
.......
.......
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_durui |
+------------------+
| tb_course |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
模拟删除整个数据库
mysql> drop database durui;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
[root@localhost ~]# mysql -uroot -p -hlocalhost < all-223107152201.sql
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| durui |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
差异备份与恢复
mysql差异备份
开启MySQL服务器的二进制日志功能
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=20 //设置服务器标识符
log-bin=mysql_bin //开启二进制日志功能
[root@localhost ~]# systemctl restart mysql.service 重启服务
对数据库进行完全备份
完全备份
[root@localhost ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%y%d%m%H%M%S').sql
[root@localhost ~]# ls
all-223107155547.sql
--single-transaction 开启事务日志
--flush-logs 刷新日志（新的二进制产生）
--master-data=2 指定master的标识符
--all-databases 全备
--delete-master-logs 删除主的日志
增加新内容
mysql> insert tb_students_info(name,age,sex,height,course_id) values('c',25,'男',160,1),('b',23,'男',158,2),('a',23,'女',185,1);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| 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 | c | 25 | 男 | 160 | 1 |
| 12 | b | 23 | 男 | 158 | 2 |
| 13 | a | 23 | 女 | 185 | 1 |
+----+--------+------+------+--------+-----------+
13 rows in set (0.00 sec)
删除两项内容
mysql> delete from tb_students_info where id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from tb_students_info where id = 4;
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 2 | Green | 23 | 男 | 158 | 2 |
| 3 | Henry | 23 | 女 | 185 | 1 |
| 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 | c | 25 | 男 | 160 | 1 |
| 12 | b | 23 | 男 | 158 | 2 |
| 13 | a | 23 | 女 | 185 | 1 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)
mysql差异备份恢复
模拟误删数据
[root@localhost ~]# mysql -uroot -p123456 -e 'drop database durui';
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;';
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
刷新创建新的二进制日志
[root@localhost ~]# ll /opt/data/
total 122976
-rw-r----- 1 mysql mysql 56 Jul 27 18:57 auto.cnf
-rw------- 1 mysql mysql 1680 Jul 27 18:57 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Jul 27 18:57 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Jul 27 18:57 client-cert.pem
-rw------- 1 mysql mysql 1676 Jul 27 18:57 client-key.pem
-rw-r----- 1 mysql mysql 896 Jul 31 15:50 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jul 31 16:08 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jul 31 16:08 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jul 27 18:57 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 31 15:55 ibtmp1
-rw-r----- 1 mysql mysql 13518 Jul 31 15:53 localhost.localdomain.err
drwxr-x--- 2 mysql mysql 4096 Jul 31 15:42 mysql
-rw-r----- 1 mysql mysql 1227 Jul 31 16:08 mysql_bin.000003
-rw-r----- 1 mysql mysql 19 Jul 31 15:55 mysql_bin.index （记录当前数据库操作正在送往那个日志里面）
-rw-r----- 1 mysql mysql 8 Jul 31 15:51 mysql.pid
drwxr-x--- 2 mysql mysql 8192 Jul 27 18:57 performance_schema
-rw------- 1 mysql mysql 1680 Jul 27 18:57 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jul 27 18:57 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Jul 27 18:57 server-cert.pem
-rw------- 1 mysql mysql 1680 Jul 27 18:57 server-key.pem
drwxr-x--- 2 mysql mysql 8192 Jul 27 18:57 sys
[root@localhost ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
total 122980
-rw-r----- 1 mysql mysql 56 Jul 27 18:57 auto.cnf
-rw------- 1 mysql mysql 1680 Jul 27 18:57 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Jul 27 18:57 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Jul 27 18:57 client-cert.pem
-rw------- 1 mysql mysql 1676 Jul 27 18:57 client-key.pem
-rw-r----- 1 mysql mysql 896 Jul 31 15:50 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jul 31 16:10 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jul 31 16:10 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jul 27 18:57 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 31 15:55 ibtmp1
-rw-r----- 1 mysql mysql 13518 Jul 31 15:53 localhost.localdomain.err
drwxr-x--- 2 mysql mysql 4096 Jul 31 15:42 mysql
-rw-r----- 1 mysql mysql 1274 Jul 31 16:10 mysql_bin.000003（曾经记录的东西全备及全备之上的操作）
-rw-r----- 1 mysql mysql 154 Jul 31 16:10 mysql_bin.000004（这个记录，现在及之后的操作，刷新一下就会再生成一个mysql_bin.000005）
-rw-r----- 1 mysql mysql 38 Jul 31 16:10 mysql_bin.index
-rw-r----- 1 mysql mysql 8 Jul 31 15:51 mysql.pid
drwxr-x--- 2 mysql mysql 8192 Jul 27 18:57 performance_schema
-rw------- 1 mysql mysql 1680 Jul 27 18:57 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jul 27 18:57 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Jul 27 18:57 server-cert.pem
-rw------- 1 mysql mysql 1680 Jul 27 18:57 server-key.pem
drwxr-x--- 2 mysql mysql 8192 Jul 27 18:57 sys
恢复完全备份
[root@localhost ~]# mysql -uroot -p123456 < all-223107155547.sql
mysql> use durui;
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> show tables;
+------------------+
| Tables_in_durui |
+------------------+
| tb_course |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from tb_students_info; 数据恢复到添加记录之前
+----+--------+------+------+--------+-----------+
| 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 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
恢复差异备份
[root@localhost data]# mysql -uroot -p123456
mysql> show binlog events in 'mysql_bin.000003';
+------------------+------+----------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+----------------------------------------+
| mysql_bin.000003 | 4 | Format_desc | 20 | 123 | Server ver: 5.7.38-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids | 20 | 154 | |
| mysql_bin.000003 | 154 | Anonymous_Gtid | 20 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 219 | Query | 20 | 292 | BEGIN |
| mysql_bin.000003 | 292 | Table_map | 20 | 362 | table_id: 175 (durui.tb_students_info) |
| mysql_bin.000003 | 362 | Write_rows | 20 | 448 | table_id: 175 flags: STMT_END_F |
| mysql_bin.000003 | 448 | Xid | 20 | 479 | COMMIT /* xid=952 */ |
| mysql_bin.000003 | 479 | Anonymous_Gtid | 20 | 544 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 544 | Query | 20 | 617 | BEGIN |
| mysql_bin.000003 | 617 | Table_map | 20 | 687 | table_id: 175 (durui.tb_students_info) |
| mysql_bin.000003 | 687 | Delete_rows | 20 | 742 | table_id: 175 flags: STMT_END_F |
| mysql_bin.000003 | 742 | Xid | 20 | 773 | COMMIT /* xid=960 */ |
| mysql_bin.000003 | 773 | Anonymous_Gtid | 20 | 838 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 838 | Query | 20 | 911 | BEGIN |
| mysql_bin.000003 | 911 | Table_map | 20 | 981 | table_id: 175 (durui.tb_students_info) |
| mysql_bin.000003 | 981 | Delete_rows | 20 | 1036 | table_id: 175 flags: STMT_END_F |
| mysql_bin.000003 | 1036 | Xid | 20 | 1067 | COMMIT /* xid=961 */ |
| mysql_bin.000003 | 1067 | Anonymous_Gtid | 20 | 1132 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 1132 | Query | 20 | 1227 | drop database durui |
| mysql_bin.000003 | 1227 | Rotate | 20 | 1274 | mysql_bin.000004;pos=4 |
+------------------+------+----------------+-----------+-------------+----------------------------------------+
20 rows in set (0.01 sec)
4-1274里面的记录都是再mysql_bin.000003 1274之后的在 mysql_bin.000004
把看不懂的二进制文件导出成txt文件就能看懂了
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000003 > /opt/mysql_bin003.txt
[root@localhost data]# cd ..
[root@localhost opt]# ls
data mysql_bin003.txt
[root@localhost opt]# vim mysql_bin003.txt
[root@localhost opt]# cat mysql_bin003.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220731 15:55:47 server id 20 end_log_pos 123 CRC32 0xe71aacfd Start: binlog v 4, server v 5.7.38-log created 220731 15:55:47
使用mysqlbinlog恢复差异备份（--stop-position 是恢复从最原始的到448这里，--star-position是从448开始恢复后面的）
[root@localhost opt]# mysqlbinlog --stop-position=448 /opt/data/mysql_bin.000003 |mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| 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 | c | 25 | 男 | 160 | 1 |
| 12 | b | 23 | 男 | 158 | 2 |
| 13 | a | 23 | 女 | 185 | 1 |
+----+--------+------+------+--------+-----------+
13 rows in set (0.01 sec)
mysql多实例部署
下载二进制格式的Mysql软件包
[root@localhost ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
mysql-5.7.38-linux-glibc2.12-x 77%[=======================================> ] 495.89M 2.02MB/s in 5m 33s
创建Mysql用户和组
[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -M -s /sbin/nologin -g mysql mysql
解压刚下载的mysql软件包，解压到/usr/local下，修改名字为mysql
[root@localhost ~]# tar -xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost local]# mv /usr/local/mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
[root@localhost local]# cd mysql/
[root@localhost mysql]# ll
total 268
drwxr-xr-x. 2 mysql mysql 4096 Jul 30 11:27 bin
drwxr-xr-x. 2 mysql mysql 39 Jul 30 11:27 docs
drwxr-xr-x. 3 mysql mysql 4096 Jul 30 11:27 include
drwxr-xr-x. 3 mysql mysql 117 Jul 30 11:27 lib
-rw-r--r--. 1 mysql mysql 259251 Mar 22 01:30 LICENSE
drwxr-xr-x. 4 mysql mysql 30 Jul 30 11:27 man
-rw-r--r--. 1 mysql mysql 566 Mar 22 01:30 README
drwxr-xr-x. 3 mysql mysql 21 Jul 30 11:27 share
配置环境变量
[root@localhost mysql]# echo 'export PATH=$PATH:/usr/local/mysql/bin/' > /etc/profile.d/mysql.sh
[root@localhost mysql]# . /etc/profile.d/mysql.sh
[root@localhost mysql]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin
创建各实例数据存放的目录
[root@localhost ~]# mkdir -p /opt/data/330{6,7,8}
[root@localhost ~]# chown -R mysql.mysql /opt/data
[root@localhost ~]# ll /opt/data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul 30 11:58 3306
drwxr-xr-x. 2 mysql mysql 6 Jul 30 11:58 3307
drwxr-xr-x. 2 mysql mysql 6 Jul 30 11:58 3308
初始化各实例
3306的
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3306/
...........
2022-07-30T04:39:16.517085Z 1 [Note] A temporary password is generated for root@localhost: D1cGc!pOnsi+
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3307/
.......
2022-07-30T04:40:02.902485Z 1 [Note] A temporary password is generated for root@localhost: iKKZpydg8e*r
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3308/
..........
2022-07-30T04:40:24.063310Z 1 [Note] A temporary password is generated for root@localhost: 28:palWH#:zu
配置配置文件/etc/my.cnf
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# 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@localhost ~]# cd /usr/lib/systemd/system
[root@localhost system]# cp sshd.service 3306.service 复制一个模板名字改为3306.service
[root@localhost system]# cp sshd.service 3307.service 复制一个模板名字改为3307.service
[root@localhost system]# cp sshd.service 3308.service 复制一个模板名字改为3308.service
配置3306
[root@localhost system]# vim 3306.service
[root@localhost system]# cat 3306.service
[Unit]
Description=3306 server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=mysqld_multi start 3306
ExecStop=ps -aux | grep mysql | grep -v "auto" | awk '{print $2}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
配置3307
[root@localhost system]# vim 3307.service
[root@localhost system]# cat 3307.service
[Unit]
Description=3307 server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=mysqld_multi start 3307
ExecStop=ps -aux | grep mysql | grep -v "auto" | awk '{print $2}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
配置3308
[root@localhost system]# vim 3308.service
[root@localhost system]# cat 3308.service
[Unit]
Description=3307 server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=mysqld_multi start 3308
ExecStop=ps -aux | grep mysql | grep -v "auto" | awk '{print $2}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@localhost ~]# systemctl daemon-reload 刷新配置
[root@localhost ~]# systemctl start 330* 开启服务
[root@localhost ~]# ss -anltup | grep 330*
tcp LISTEN 0 80 *:3306 *:* users:(("mysqld",pid=93119,fd=30))
tcp LISTEN 0 80 *:3307 *:* users:(("mysqld",pid=91947,fd=30))
tcp LISTEN 0 80 *:3308 *:* users:(("mysqld",pid=91968,fd=22))
初始化密码
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql3306.sock
Enter password:
MySQL [(none)]> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.01 sec)
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql3307.sock
Enter password:
MySQL [(none)]> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.01 sec)
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql3308.sock
Enter password:
MySQL [(none)]> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.01 sec)