#数据库的备份与恢复
文章目录
##数据库常用备份方案
冷备份：数据库服务关闭，把数据库对应的数据目录下的数据文件拷贝一份 物理备份
热备份：数据库服务正常运行的时候，直接对数据库备份
数据库备份的方式手段
|全量备份 |全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长 |
|-|
|增量备份 |增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行 |
|差异备份 |备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
###mysql备份工具mysqldump
/语法：
mysqldump [OPTIONS] database [tables …]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
/常用的OPTIONS：
mysqldump -uUSERNAME //指定数据库用户名
mysqldump -hHOST //指定服务器主机，请使用ip地址
mysqldump -pPASSWORD //指定数据库用户的密码
mysqldump -P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
备份整个数据库(全备)
mysql> create database runtime;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use runtime;
Database changed
mysql> create table xiaoqi(id int not null,name varchar(10) not null,age tinyint(3));
Query OK, 0 rows affected (0.02 sec)
mysql> create table yunjisuan(id int not null,name varchar(10) not nulll,age tinyint(3));
Query OK, 0 rows affected (0.00 sec)
mysql> create table yunwei(id int not null,name varchar(10) not null,agge tinyint(3));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| xiaoqi |
| yunjisuan |
| yunwei |
+-------------------+
3 rows in set (0.00 sec)
mysql> quit
Bye
[root@localhost ~]# ls
anaconda-ks.cfg
[root@localhost ~]# mysqldump -uroot -p123456 --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
[root@localhost ~]# ls
all-20220728151047.sql anaconda-ks.cfg
备份runtime库的xiaoqi表和yunwei表
[root@localhost ~]# mysqldump -uroot -p1123 runtime xiaoqi yunwei > table-$(date '+%Y%m%d%H%M%S').sql
[root@localhost ~]# ls
all-20220729111047.sql anaconda-ks.cfg table-20220729111141.sql
备份runtime库
[root@localhost ~]# mysqldump -uroot -p123456 --databases runtime > runtime-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure. //这不是报错，只是密码不安全而已
[root@localhost ~]# ls
all-20220729111047.sql runtime-20220729111402.sql
anaconda-ks.cfg table-20220729111141.sql
模拟误删runtime数据库
mysql> drop database runtime;
Query OK, 3 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------|
4 rows in set (0.00 sec)
###mysql数据恢复
恢复runtime数据库
[root@localhost ~]# ls
all-20220729111047.sql runtime-20220729111402.sql
anaconda-ks.cfg table-2022072911141.sql
[root@localhost ~]# mysql -uroot -p123456 < runtime-20220728151702.sql
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
5 rows in set (0.00 sec)
恢复runtime数据库的xiaoqi表和yunwei表
mysql> use runtime;
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 table-20220728151141.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)
.....
mysql> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| xiaoqi |
| yunjisuan |
| yunwei |
+-------------------+
3 rows in set (0.00 sec)
模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database runtime;
Query OK, 3 rows affected (0.01 sec)
mysql> drop database linux;
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 ~]# ls
all-20220729111047.sql runtime-20220729111402.sql
anaconda-ks.cfg table-20220729111141.sql
[root@localhost ~]# mysql -uroot -p123456 < all-20220728151047.sql
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
5 rows in set (0.00 sec)
###差异备份与恢复
开启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
server-id=1 //设置服务器标识符
log-bin=mysql_bin //开启二进制日志功能
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
对数据库进行完全备份
[root@localhost ~]# mysql -uroot -p1123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
| linux |
+--------------------+
6 rows in set (0.00 sec)
mysql> show tables from linux;
+------------------+
| Tables_in_linux |
+------------------+
| tb_course |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from linux.tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | MySQL |
| 3 | Python |
| 4 | Go |
| 5 | C++ |
| 6 | HTML |
+----+-------------+
6 rows in set (0.08 sec)
mysql> select * from linux.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 | xiaoqi | 20 | 男 | 180 | 7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)
完全备份
[root@localhost ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%Y%m%d%H%M%S').sql
[root@localhost ~]# ls
all-20220729113505.sql
增加新内容
[root@localhost ~]# mysql -uroot -p1123
mysql> use linux;
Database changed
mysql> insert tb_course values(7,'SQL'),(8,'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 | HTML |
| 7 | SQL |
| 8 | PHP |
+----+-------------+
8 rows in set (0.00 sec)
####mysql差异备份恢复
模拟误删数据
[root@localhost ~]# mysql -uroot -p1123 -e 'drop database linux;'
[root@localhost ~]# mysql -uroot -p1123 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
刷新创建新的二进制日志
[root@localhost ~]# ll /opt/data
total 123008
-rw-r-----. 1 mysql mysql 56 Jul 26 17:00 auto.cnf
-rw-------. 1 mysql mysql 1680 Jul 26 17:00 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 26 17:00 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 26 17:00 client-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 26 17:00 client-key.pem
-rw-r-----. 1 mysql mysql 892 Jul 28 15:37 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 28 16:13 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 28 16:13 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 17:00 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 28 15:52 ibtmp1
-rw-r-----. 1 mysql mysql 47462 Jul 28 15:37 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 Jul 28 15:29 mysql
-rw-r-----. 1 mysql mysql 592 Jul 28 16:11 mysql_bin.000002
-rw-r-----. 1 mysql mysql 19 Jul 28 15:52 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 Jul 28 15:37 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Jul 26 17:00 performance_schema
-rw-------. 1 mysql mysql 1680 Jul 26 17:00 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Jul 26 17:00 public_key.pem
drwxr-x---. 2 mysql mysql 134 Jul 28 15:29 runtime
-rw-r--r--. 1 mysql mysql 1112 Jul 26 17:00 server-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 26 17:00 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Jul 26 17:00 sys
刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -p1123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data
total 123012
-rw-r-----. 1 mysql mysql 56 Jul 26 17:00 auto.cnf
-rw-------. 1 mysql mysql 1680 Jul 26 17:00 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 26 17:00 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 26 17:00 client-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 26 17:00 client-key.pem
-rw-r-----. 1 mysql mysql 892 Jul 28 15:37 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 28 16:13 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 28 16:13 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 17:00 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 28 15:52 ibtmp1
-rw-r-----. 1 mysql mysql 47529 Jul 28 16:15 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 Jul 28 15:29 mysql
-rw-r-----. 1 mysql mysql 639 Jul 28 16:16 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 Jul 28 16:16 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 Jul 28 16:16 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 Jul 28 15:37 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Jul 26 17:00 performance_schema
-rw-------. 1 mysql mysql 1680 Jul 26 17:00 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Jul 26 17:00 public_key.pem
drwxr-x---. 2 mysql mysql 134 Jul 28 15:29 runtime
-rw-r--r--. 1 mysql mysql 1112 Jul 26 17:00 server-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 26 17:00 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Jul 26 17:00 sys
恢复完全备份
[root@localhost ~]# mysql -uroot -p1123 < all-20220729113505.sql
[root@localhost ~]# mysql -uroot -p1123 -e'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| linux |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
[root@localhost ~]# mysql -uroot -p123456 -e'show tables from linux;'
+------------------+
| Tables_in_linux |
+------------------+
| tb_course |
| tb_students_info |
+------------------+
[root@localhost ~]# mysql -uroot -p123456 -e 'select * from linux.tb_course;'
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | MySQL |
| 3 | Python |
| 4 | Go |
| 5 | C++ |
| 6 | HTML |
+----+-------------+
[root@localhost ~]# mysql -uroot -p123456 -e 'select * from linux.tb_students_info;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+------+------+--------+-----------+
| 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 | xiaoqi | 20 | 男 | 180 | 7 |
+----+--------+------+------+--------+-----------+
检查误删数据库的位置在什么地方
[root@localhost ~]# cd /opt/data
[root@localhost data]# ls
auto.cnf ib_logfile1 mysql.pid
ca-key.pem ibtmp1 performance_schema
ca.pem linux private_key.pem
client-cert.pem localhost.localdomain.err public_key.pem
client-key.pem mysql runtime
ib_buffer_pool mysql_bin.000002 server-cert.pem
ibdata1 mysql_bin.000003 server-key.pem
ib_logfile0 mysql_bin.index sys
[root@localhost data]# cd
[root@localhost ~]# mysql -uroot -p1123
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 | 1 | 123 | Server ver: 5.7.38-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 1 | 292 | BEGIN |
| mysql_bin.000002 | 292 | Table_map | 1 | 348 | table_id: 142 (linux.tb_course) |
| mysql_bin.000002 | 348 | Write_rows | 1 | 401 | table_id: 142 flags: STMT_END_F |
| mysql_bin.000002 | 401 | Xid | 1 | 432 | COMMIT /* xid=535 */ |
| mysql_bin.000002 | 432 | Anonymous_Gtid | 1 | 497 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 497 | Query | 1 | 592 | drop database linux |
| mysql_bin.000002 | 592 | Rotate | 1 | 639 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
10 rows in set (0.04 sec)
二进制日志转换文本文件
[root@localhost ~]# cd /opt/data/
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000002 > /opt/mysql_bin002.txt
使用mysqlbinlog恢复差异备份
[root@localhost data]# mysqlbinlog --stop-position=497 /opt/data/mysql_bin.000002 | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# mysql -uroot -p1123 -e 'select * from linux.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 | HTML |
| 7 | SQL |
| 8 | PHP |
+----+-------------+
##mysql多实例部署
软件下载
下载二进制格式的mysql软件包
[root@localhost src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
--2022-07-29 11:50:18-- 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)... 223.119.203.77, 2600:1417:a000:691::2e31, 2600:1417:a000:6b5::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|223.119.203.77|: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]
.......
Saving to: ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz’
mysql-5.7.38-linu 100%[=============>] 643.57M 874KB/s in 11m 38s
2022-07-28 17:10:59 (944 KB/s) - ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz’ saved [674830866/674830866]
配置用户和组并解压二进制程序至/usr/local下
//创建用户和组
[root@localhost src]# groupadd -r mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql
解压软件至/usr/local
[root@localhost src]# ls
debug kernels mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# cd
[root@localhost ~]# ls /usr/local/
bin games lib libexec sbin src
etc include lib64 mysql-5.7.38-linux-glibc2.12-x86_64 share
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64/'
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 May 19 2020 bin
drwxr-xr-x. 2 root root 6 May 19 2020 etc
drwxr-xr-x. 2 root root 6 May 19 2020 games
drwxr-xr-x. 2 root root 6 May 19 2020 include
drwxr-xr-x. 2 root root 6 May 19 2020 lib
drwxr-xr-x. 3 root root 17 Jul 28 16:39 lib64
drwxr-xr-x. 2 root root 6 May 19 2020 libexec
lrwxrwxrwx. 1 root root 36 Jul 28 17:18 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 28 17:17 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 May 19 2020 sbin
drwxr-xr-x. 5 root root 49 Jul 28 16:39 share
drwxr-xr-x. 2 root root 6 May 19 2020 src
修改目录/usr/local/mysql的属主属组
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql
[root@localhost ~]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 Jul 28 17:18 /usr/local/mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
配置环境变量
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost ~]# . /etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
创建各实例数据存放的目录
[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 17:25 3306
drwxr-xr-x. 2 mysql mysql 6 Jul 28 17:25 3307
drwxr-xr-x. 2 mysql mysql 6 Jul 28 17:25 3308
[root@localhost ~]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 3308
3 directories, 0 files
初始化各实例
初始化3306实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2022-07-28T09:28:20.485678Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T09:28:20.616372Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T09:28:20.637721Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T09:28:20.694789Z 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: 9ec415d5-0e57-11ed-bcb7-000c29bf993e.
2022-07-28T09:28:20.695258Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T09:28:20.936665Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T09:28:20.936679Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T09:28:20.936966Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T09:28:21.044939Z 1 [Note] A temporary password is generated for root@localhost: yxoy3BXoOc+h
[root@localhost ~]# echo 'yxoy3BXoOc+h' > 3306_pass
初始化3307实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2022-07-28T09:29:40.380628Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T09:29:40.502300Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T09:29:40.520941Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T09:29:40.575037Z 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: ce60d82b-0e57-11ed-bf62-000c29bf993e.
2022-07-28T09:29:40.575651Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T09:29:40.688272Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T09:29:40.688284Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T09:29:40.688554Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T09:29:40.820002Z 1 [Note] A temporary password is generated for root@localhost: ZOtnGvW=h1-+
[root@localhost ~]# echo 'ZOtnGvW=h1-+' > 3307_pass
初始化3308实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2022-07-28T09:30:38.540400Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T09:30:38.669738Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T09:30:38.689122Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T09:30:38.742794Z 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: f10c8a11-0e57-11ed-8104-000c29bf993e.
2022-07-28T09:30:38.744240Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T09:30:39.107249Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T09:30:39.107261Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T09:30:39.107535Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T09:30:39.137765Z 1 [Note] A temporary password is generated for root@localhost: lmBj>B_O+5dr
[root@localhost ~]# echo 'lmBj>B_O+5dr' > 3308_pass
安装perl
[root@localhost ~]# yum -y install perl
配置配置文件/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 ~]# 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 Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
初始化密码
[root@localhost ~]# ls
3306_pass 3307_pass 3308_pass anaconda-ks.cfg
[root@localhost ~]# cat 3306_pass
yxoy3BXoOc+h
[root@localhost ~]# mysql -uroot -p'yxoy3BXoOc+h' -S /tmp/mysql3306.sockmysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@localhost ~]# yum -y install ncurses-compat-libs
[root@localhost ~]# mysql -uroot -p'yxoy3BXoOc+h' -S /tmp/mysql3306.sockmysql: [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.38
mysql> set password = password('yhm12345');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# cat 3307_pass
ZOtnGvW=h1-+
[root@localhost ~]# mysql -uroot -p'ZOtnGvW=h1-+' -S /tmp/mysql3307.sock -e 'set password = password("yhm12345");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure. //不是报错，是密码不安全而已
[root@localhost ~]# cat 3308_pass
lmBj>B_O+5dr
[root@localhost ~]# mysql -uroot -p'lmBj>B_O+5dr' -S /tmp/mysql3308.sock -e 'set password = password("yhm12345");' --connect-expired-password
1475
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
