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

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

1. mysql数据库备份与恢复
   1.1 数据库常用备份方案
   数据库备份方案：

全量备份
增量备份
差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。
###1.2 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 |
| mysql              |
| performance_schema |
| sys                |
| zhan               |
+--------------------+
5 rows in set (0.14 sec)

mysql> use zhan;
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_zhan   |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# ls
anaconda-ks.cfg  initial-setup-ks.cfg  pass
[root@localhost ~]# mysqldump -uroot -p123456 --all-databases > all-20220729.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20220729.sql  anaconda-ks.cfg  initial-setup-ks.cfg  pass

//备份zhan库的tb_course表和tb_students_info表

[root@localhost ~]# mysqldump -uroot -p123456 zhan tb_course tb_students_info > tb.mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20220729.sql  anaconda-ks.cfg  initial-setup-ks.cfg  pass  tb.mysql
[root@localhost ~]# 

//备份zhan库

[root@localhost ~]# mysqldump -uroot -p123456 --databases zhan > zhan.mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20220729.sql  anaconda-ks.cfg  initial-setup-ks.cfg  pass  tb.mysql  zhan.mysql
[root@localhost ~]# 

//模拟误删zhan数据库

mysql> drop database zhan;
Query OK, 2 rows affected (0.16 sec)

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

1.3 mysql数据恢复

//恢复zhan数据库

[root@localhost ~]# mysql -uroot -p123456 <  zhan.mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
[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                |
| zhan               |
+--------------------+

//恢复zhan数据库的tb_students_info表和tb_course表

mysql> use zhan;
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> source tb.mysql;
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)

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)

Query OK, 0 rows affected, 1 warning (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)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.10 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)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 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)

Query OK, 0 rows affected (0.02 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)

Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

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, 1 warning (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)

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_zhan   |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

//模拟删除整个数据库

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

mysql> drop database zhan;
Query OK, 2 rows affected (0.00 sec)

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

//恢复整个数据库

[root@localhost ~]# ls
all-20220729.sql  anaconda-ks.cfg  initial-setup-ks.cfg  pass  tb.mysql  zhan.mysql
[root@localhost ~]# mysql -uroot -p123456 <  all-20220729.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[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                |
| zhan               |
+--------------------+

1.4 差异备份与恢复
1.4.1 mysql差异备份
开启MySQL服务器的二进制日志功能

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /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=10
log-bin=mysql_bin

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

对数据库进行完全备份

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

mysql> show tables from zhan;
+------------------+
| Tables_in_zhan   |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

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

mysql> select * from zhan.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 ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20220728.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20220728.sql  all-20220729.sql  anaconda-ks.cfg  initial-setup-ks.cfg  pass  tb.mysql  zhan.mysql
[root@localhost ~]# ll
total 880
-rw-r--r--. 1 root root 877769 Jul 29 11:31 all-20220728.sql
-rw-r--r--. 1 root root      0 Jul 29 11:30 all-20220729.sql
-rw-------. 1 root root   1101 Jun 30 14:44 anaconda-ks.cfg
-rw-r--r--. 1 root root   1392 Jun 30 14:57 initial-setup-ks.cfg
-rw-r--r--. 1 root root     13 Jul 26 19:07 pass
-rw-r--r--. 1 root root   3071 Jul 29 11:08 tb.mysql
-rw-r--r--. 1 root root   3210 Jul 29 11:09 zhan.mysql
[root@localhost ~]# 

//增加新内容

mysql> insert into tb_course (course_name) values('Word'),('PHP');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | Word        |
|  7 | PHP         |
+----+-------------+
7 rows in set (0.00 sec)

1.4.2 mysql差异备份恢复
模拟误删数据

[root@localhost ~]# mysql -uroot -p123456 -e 'drop database zhan;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[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 123020
-rw-r-----. 1 mysql mysql       56 Jul 26 18:55 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 26 18:55 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 18:55 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 18:55 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 26 18:55 client-key.pem
-rw-r-----. 1 mysql mysql      507 Jul 29 11:22 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:35 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 18:55 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:35 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:31 ibtmp1
-rw-r-----. 1 mysql mysql    59719 Jul 29 11:22 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 29 11:19 mysql
-rw-r-----. 1 mysql mysql        6 Jul 29 11:22 mysql.pid
-rw-r-----. 1 mysql mysql      588 Jul 29 11:35 mysql_bin.000003
-rw-r-----. 1 mysql mysql       19 Jul 29 11:31 mysql_bin.index
drwxr-x---. 2 mysql mysql     8192 Jul 26 18:55 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 26 18:55 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 26 18:55 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 18:55 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 26 18:55 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 26 18:55 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 123024
-rw-r-----. 1 mysql mysql       56 Jul 26 18:55 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 26 18:55 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 18:55 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 18:55 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 26 18:55 client-key.pem
-rw-r-----. 1 mysql mysql      507 Jul 29 11:22 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:35 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 18:55 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:35 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:31 ibtmp1
-rw-r-----. 1 mysql mysql    59719 Jul 29 11:22 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 29 11:19 mysql
-rw-r-----. 1 mysql mysql        6 Jul 29 11:22 mysql.pid
-rw-r-----. 1 mysql mysql      635 Jul 29 11:37 mysql_bin.000003
-rw-r-----. 1 mysql mysql      154 Jul 29 11:37 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 Jul 29 11:37 mysql_bin.index
drwxr-x---. 2 mysql mysql     8192 Jul 26 18:55 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 26 18:55 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 26 18:55 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 18:55 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 26 18:55 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 26 18:55 sys

恢复完全备份

[root@localhost ~]# mysql -uroot -p123456 < all-20220728.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[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                |
| zhan               |
+--------------------+

[root@localhost ~]# mysql -uroot -p123456 -e 'show tables from zhan;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_zhan   |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
[root@localhost ~]#  mysql -uroot -p123456 -e 'select * from zhan.tb_course;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
+----+-------------+

恢复差异备份

[root@localhost ~]# ll /opt/data
total 123860
-rw-r-----. 1 mysql mysql       56 Jul 26 18:55 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 26 18:55 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 18:55 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 18:55 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 26 18:55 client-key.pem
-rw-r-----. 1 mysql mysql      507 Jul 29 11:22 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:40 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 18:55 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:40 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:31 ibtmp1
-rw-r-----. 1 mysql mysql    59719 Jul 29 11:22 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 29 11:38 mysql
-rw-r-----. 1 mysql mysql        6 Jul 29 11:22 mysql.pid
-rw-r-----. 1 mysql mysql      635 Jul 29 11:37 mysql_bin.000003
-rw-r-----. 1 mysql mysql   857971 Jul 29 11:38 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 Jul 29 11:37 mysql_bin.index
drwxr-x---. 2 mysql mysql     8192 Jul 26 18:55 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 26 18:55 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 26 18:55 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 18:55 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 26 18:55 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 26 18:55 sys
drwxr-x---. 2 mysql mysql      118 Jul 29 11:38 zhan

//检查误删数据库的位置在什么地方

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    |        10 |         123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000003 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 219 | Query          |        10 |         291 | BEGIN                                 |
| mysql_bin.000003 | 291 | Table_map      |        10 |         346 | table_id: 142 (zhan.tb_course)        |
| mysql_bin.000003 | 346 | Write_rows     |        10 |         400 | table_id: 142 flags: STMT_END_F       |
| mysql_bin.000003 | 400 | Xid            |        10 |         431 | COMMIT /* xid=487 */                  |
| mysql_bin.000003 | 431 | Anonymous_Gtid |        10 |         496 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 496 | Query          |        10 |         588 | drop database zhan                    |
| mysql_bin.000003 | 588 | Rotate         |        10 |         635 | mysql_bin.000004;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
10 rows in set (0.00 sec)

//使用mysqlbinlog恢复差异备份

[root@localhost ~]#  mysqlbinlog --stop-position=496 /opt/data/mysql_bin.000003 |mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456 -e 'select * from zhan.tb_course;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | Word        |
|  7 | PHP         |
+----+-------------+

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

[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug  kernels  mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# grouadd -r mysql

//创建用户和组

[root@localhost src]# groupadd -r mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql

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

[root@localhost src]# tar xfs mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/
[root@localhost src]# cd
[root@localhost ~]# ll /usr/local/
total 0
drwxr-xr-x. 2 root root   6 Jun 22  2021 bin
drwxr-xr-x. 2 root root   6 Jun 22  2021 etc
drwxr-xr-x. 2 root root   6 Jun 22  2021 games
drwxr-xr-x. 2 root root   6 Jun 22  2021 include
drwxr-xr-x. 2 root root   6 Jun 22  2021 lib
drwxr-xr-x. 3 root root  17 Jun 30 14:35 lib64
drwxr-xr-x. 2 root root   6 Jun 22  2021 libexec
drwxr-xr-x. 9 root root 129 Jul 26 18:20 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 Jun 22  2021 sbin
drwxr-xr-x. 5 root root  49 Jun 30 14:35 share
drwxr-xr-x. 2 root root   6 Jun 22  2021 src
[root@localhost ~]# id mysql
uid=1001(mysql) gid=974(mysql) groups=974(mysql)
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.37-linux-glibc2.12-x86_64/'
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root   6 Jun 22  2021 bin
drwxr-xr-x. 2 root root   6 Jun 22  2021 etc
drwxr-xr-x. 2 root root   6 Jun 22  2021 games
drwxr-xr-x. 2 root root   6 Jun 22  2021 include
drwxr-xr-x. 2 root root   6 Jun 22  2021 lib
drwxr-xr-x. 3 root root  17 Jun 30 14:35 lib64
drwxr-xr-x. 2 root root   6 Jun 22  2021 libexec
lrwxrwxrwx. 1 root root  36 Jul 26 18:35 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 26 18:20 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 Jun 22  2021 sbin
drwxr-xr-x. 5 root root  49 Jun 30 14:35 share
drwxr-xr-x. 2 root root   6 Jun 22  2021 src

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

[root@localhost local]# chown -R mysql.mysql mysql
[root@localhost local]# ll mysql -d
lrwxrwxrwx. 1 mysql mysql 36 Jul 26 18:35 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
//把原文件也改一下
[root@localhost local]# chown -R mysql.mysql mysql-5.7.37-linux-glibc2.12-x86_64/
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root  root    6 Jun 22  2021 bin
drwxr-xr-x. 2 root  root    6 Jun 22  2021 etc
drwxr-xr-x. 2 root  root    6 Jun 22  2021 games
drwxr-xr-x. 2 root  root    6 Jun 22  2021 include
drwxr-xr-x. 2 root  root    6 Jun 22  2021 lib
drwxr-xr-x. 3 root  root   17 Jun 30 14:35 lib64
drwxr-xr-x. 2 root  root    6 Jun 22  2021 libexec
lrwxrwxrwx. 1 mysql mysql  36 Jul 26 18:35 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Jul 26 18:20 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root  root    6 Jun 22  2021 sbin
drwxr-xr-x. 5 root  root   49 Jun 30 14:35 share
drwxr-xr-x. 2 root  root    6 Jun 22  2021 src

//添加环境变量

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

[root@localhost mysql]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin
[root@localhost mysql]# which mysql
/usr/local/mysql/bin/mysql
[root@localhost mysql]# ln -s /usr/local/mysql/include /usr/include/mysql
[root@localhost mysql]# ll /usr/include/
total 0
drwxr-xr-x. 4 root root 34 Jun 30 14:40 gnome-boxes
lrwxrwxrwx. 1 root root 24 Jul 26 18:45 mysql -> /usr/local/mysql/include
drwxr-xr-x. 2 root root 27 Jun 30 14:36 python3.6m
[root@localhost mysql]# ls
LICENSE  README  bin  docs  include  lib  man  share  support-files
[root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
~                                                                                                               
~                          
[root@localhost mysql]# ldconfig
[root@localhost mysql]# pwd
/usr/local/mysql
[root@localhost mysql]# vim /etc/man_db.conf 
找到这行添加下面一行东西
#
MANDATORY_MANPATH                       /usr/man
MANDATORY_MANPATH                       /usr/share/man
MANDATORY_MANPATH                       /usr/local/share/man
MANDATORY_MANPATH                       /usr/local/mysql/man
#---------------------------------------------------------

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

[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll /opt/data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul 28 22:11 3306
drwxr-xr-x. 2 mysql mysql 6 Jul 28 22:11 3307
drwxr-xr-x. 2 mysql mysql 6 Jul 28 22:11 3308

初始化各实例

[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2022-07-28T14:11:32.753515Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T14:11:33.047309Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T14:11:33.073928Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T14:11:33.078588Z 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: 2f0415f0-0e7f-11ed-8b80-000c29087110.
2022-07-28T14:11:33.079179Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T14:11:33.677872Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T14:11:33.677898Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T14:11:33.678225Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T14:11:33.946986Z 1 [Note] A temporary password is generated for root@localhost: ab5UpT0lrh,7
[root@localhost ~]# echo ' ab5UpT0lrh,7'
 ab5UpT0lrh,7
[root@localhost ~]# echo ' ab5UpT0lrh,7'> 3306
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2022-07-28T14:12:34.985249Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T14:12:35.256115Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T14:12:35.327796Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T14:12:35.332171Z 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: 541f3aac-0e7f-11ed-9865-000c29087110.
2022-07-28T14:12:35.332736Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T14:12:36.041715Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T14:12:36.041741Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T14:12:36.042142Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T14:12:36.404153Z 1 [Note] A temporary password is generated for root@localhost: vtOBK;Z:H3lj
[root@localhost ~]# echo 'vtOBK;Z:H3lj' > 3307
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2022-07-28T14:13:05.039423Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T14:13:05.396962Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T14:13:05.422059Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T14:13:05.426741Z 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: 660f4b8e-0e7f-11ed-9c1d-000c29087110.
2022-07-28T14:13:05.427320Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T14:13:05.689274Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T14:13:05.689325Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T14:13:05.689729Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T14:13:05.883560Z 1 [Note] A temporary password is generated for root@localhost: 6ydg.znoUrEO
[root@localhost ~]# echo '6ydg.znoUrEO' > 3308

安装perl

[root@localhost ~]# yum -y install perl
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 3:28:46 ago on Thu Jul 28 18:44:54 2022.
Dependencies resolved.
================================================================================================================
 Package                           Arch        Version                                     Repository      Size
================================================================================================================
Installing:
 perl                              x86_64      4:5.26.3-421.el8                            appstream       73 k
Installing dependencies:
 dwz                               x86_64      0.12-10.el8                                 appstream      109 k
 efi-srpm-macros                   noarch      3-3.el8                                     

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

[root@localhost ~]# vim /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 ~]# mysqld_multi start 3306
[root@localhost ~]# mysqld_multi start 3307
[root@localhost ~]# mysqld_multi start 3308
[root@localhost ~]# ss -antl
State      Recv-Q Send-Q     Local Address:Port                    Peer Address:Port
LISTEN     0      128                    *:22                                 *:*
LISTEN     0      100            127.0.0.1:25                                 *:*
LISTEN     0      80                    :::3307                              :::*
LISTEN     0      80                    :::3308                              :::*
LISTEN     0      128                   :::22                                :::*
LISTEN     0      100                  ::1:25                                :::*
LISTEN     0      80                    :::3306                              :::*

初始化密码

mysql> set password = password('zhan123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p'zhan123!' -S /tmp/mysql3306.sock
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 3
Server version: 5.7.37 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> exit
Bye



3307
[root@localhost ~]# mysql -uroot -p'YvCPN&9a0=r+' -S /tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]# mysql -uroot -p'YvCPN&9a0=r+' -S /tmp/mysql3307.sock
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 2
Server version: 5.7.37

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> set password = password('zhan123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p'zhan123!' -S /tmp/mysql3307.sock
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 3
Server version: 5.7.37 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> 

3308
[root@localhost ~]# mysql -uroot -p'4nJD>shidakk' -S /tmp/mysql3308.sock
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 2
Server version: 5.7.37

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> set password = password('zhan123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p'zhan123!' -S /tmp/mysql3308.sock
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 3
Server version: 5.7.37 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>