MySQL进阶，数据恢复,mysql多实例部署

已于 2022-08-02 14:12:23 修改
阅读量78 收藏
点赞数
文章标签： 数据库 mysql 服务器
于 2022-07-31 14:29:36 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_48619783/article/details/126085294
版权

MySQL进阶.数据恢复，多实例部署

文章目录

1.mysql数据库备份与恢复

1.1数据库常用备份方案

数据库备份方案：

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

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> 
mysql> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| tb_course         |
| tb_students_info  |
+-------------------+
2 rows in set (0.00 sec)
[root@hwf ~]# mysqldump -uroot -p123 --all-databases > /opt/beifen/all-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@hwf ~]# ls /opt/beifen/
all-20220729153113.sql

//备份runtime数据库
[root@hwf ~]# mysqldump -uroot -p123 --databases runtime > /opt/beifen/runtime-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@hwf ~]# ls /opt/beifen/
all-20220729153113.sql  runtime-20220729153733.sql

//备份runtime数据库i里的tb_students_info 和tb_course表
[root@hwf ~]# mysqldump -uroot -p123  runtime tb_students_info tb_course > /opt/beifen/table-$(date '+%Y%m$d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@hwf ~]# ls /opt/beifen/
 all-20220729153113.sql   runtime-20220729153733.sql  'table-202207$d154250.sql'
1.2mysql数据恢复
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

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> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| tb_course         |
| tb_students_info  |
+-------------------+
2 rows in set (0.00 sec)
mysql> drop table tb_students_info;		//模拟数据丢失
Query OK, 0 rows affected (0.15 sec)

mysql> 
mysql> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| tb_course         |
+-------------------+
1 row in set (0.00 sec)
mysql> source /opt/beifen/table-20220729155314.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)

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

//恢复runtime数据库数据
mysql> drop database runtime;			//模拟删除
Query OK, 2 rows affected (0.00 sec)

mysql> source /opt/beifen/runtime-20220729153733.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)

Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

2.差异备份与恢复

2.1MySQL差异备份

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

[root@hwf ~]# vim /etc/my.cnf 
[root@hwf ~]# 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		//开启二进制日志功能
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[root@hwf ~]# systemctl restart mysqld.service 	//修改配置文件重启服务

对数据库进行完全备份

[root@hwf ~]# mysqldump -uroot -p123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > /opt/beifen/all-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@hwf ~]# ls /opt/beifen/
all-20220729160305.sql  runtime-20220729153733.sql  table-20220729155314.sql

//新增内容
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database hwf;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hwf                |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
2.2mysql 差异备份恢复
//模拟误删数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hwf                |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database hwf;
Query OK, 0 rows affected (0.00 sec)

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

//刷新创建新的二进制日志
[root@hwf ~]# ll /opt/data/
total 123076
-rw-r----- 1 mysql mysql       56 Jul 27 15:11 auto.cnf
-rw------- 1 mysql mysql     1680 Jul 27 15:11 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Jul 27 15:11 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Jul 27 15:11 client-cert.pem
-rw------- 1 mysql mysql     1680 Jul 27 15:11 client-key.pem
-rw-r----- 1 mysql mysql   117394 Jul 29 15:59 hwf.err
-rw-r----- 1 mysql mysql      447 Jul 29 15:59 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jul 29 15:59 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jul 29 15:59 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jul 27 15:11 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 29 16:03 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Jul 27 15:11 mysql
-rw-r----- 1 mysql mysql      458 Jul 29 16:06 mysql_bin.000002
-rw-r----- 1 mysql mysql       19 Jul 29 16:03 mysql_bin.index
-rw-r----- 1 mysql mysql        5 Jul 29 15:59 mysql.pid
drwxr-x--- 2 mysql mysql     8192 Jul 27 15:11 performance_schema
-rw------- 1 mysql mysql     1680 Jul 27 15:11 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Jul 27 15:11 public_key.pem
drwxr-x--- 2 mysql mysql      118 Jul 29 15:56 runtime
-rw-r--r-- 1 mysql mysql     1112 Jul 27 15:11 server-cert.pem
-rw------- 1 mysql mysql     1680 Jul 27 15:11 server-key.pem
drwxr-x--- 2 mysql mysql     8192 Jul 27 15:11 sys
[root@hwf ~]# mysqladmin -uroot -p123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@hwf ~]# ll /opt/data/
total 123080
-rw-r----- 1 mysql mysql       56 Jul 27 15:11 auto.cnf
-rw------- 1 mysql mysql     1680 Jul 27 15:11 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Jul 27 15:11 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Jul 27 15:11 client-cert.pem
-rw------- 1 mysql mysql     1680 Jul 27 15:11 client-key.pem
-rw-r----- 1 mysql mysql   117394 Jul 29 15:59 hwf.err
-rw-r----- 1 mysql mysql      447 Jul 29 15:59 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jul 29 15:59 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jul 29 15:59 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jul 27 15:11 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 29 16:03 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Jul 27 15:11 mysql
-rw-r----- 1 mysql mysql      505 Jul 29 16:07 mysql_bin.000002
-rw-r----- 1 mysql mysql      154 Jul 29 16:07 mysql_bin.000003
-rw-r----- 1 mysql mysql       38 Jul 29 16:07 mysql_bin.index
-rw-r----- 1 mysql mysql        5 Jul 29 15:59 mysql.pid
drwxr-x--- 2 mysql mysql     8192 Jul 27 15:11 performance_schema
-rw------- 1 mysql mysql     1680 Jul 27 15:11 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Jul 27 15:11 public_key.pem
drwxr-x--- 2 mysql mysql      118 Jul 29 15:56 runtime
-rw-r--r-- 1 mysql mysql     1112 Jul 27 15:11 server-cert.pem
-rw------- 1 mysql mysql     1680 Jul 27 15:11 server-key.pem
drwxr-x--- 2 mysql mysql     8192 Jul 27 15:11 sys

//恢复完全备份

[root@hwf ~]# mysql -uroot -p123 < /opt/beifen/all-20220729160305.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@hwf ~]#
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

//恢复差异备份

[root@hwf data]# ll				//查看二进制日志文件
total 124100
-rw-r----- 1 mysql mysql       56 Jul 27 15:11 auto.cnf
-rw------- 1 mysql mysql     1680 Jul 27 15:11 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Jul 27 15:11 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Jul 27 15:11 client-cert.pem
-rw------- 1 mysql mysql     1680 Jul 27 15:11 client-key.pem
-rw-r----- 1 mysql mysql   117394 Jul 29 15:59 hwf.err
-rw-r----- 1 mysql mysql      447 Jul 29 15:59 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jul 29 16:11 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jul 29 16:11 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jul 27 15:11 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 29 16:03 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Jul 29 16:10 mysql
-rw-r----- 1 mysql mysql      505 Jul 29 16:07 mysql_bin.000002
-rw-r----- 1 mysql mysql   858245 Jul 29 16:10 mysql_bin.000003
-rw-r----- 1 mysql mysql       38 Jul 29 16:07 mysql_bin.index
-rw-r----- 1 mysql mysql        5 Jul 29 15:59 mysql.pid
drwxr-x--- 2 mysql mysql     8192 Jul 27 15:11 performance_schema
-rw------- 1 mysql mysql     1680 Jul 27 15:11 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Jul 27 15:11 public_key.pem
drwxr-x--- 2 mysql mysql      118 Jul 29 16:10 runtime
-rw-r--r-- 1 mysql mysql     1112 Jul 27 15:11 server-cert.pem
-rw------- 1 mysql mysql     1680 Jul 27 15:11 server-key.pem
drwxr-x--- 2 mysql mysql     8192 Jul 27 15:11 sys
[root@hwf data]#  mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000002 > /opt/l_bin002.txt			//将二进制日志文件转换为文本文档
[root@hwf data]# 
[root@hwf opt]# mysqlbinlog --stop-position=375 /opt/data/mysql_bin.000002 |mysql -uroot -p123		//差异恢复
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@hwf opt]# 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

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


//使用时间恢复
mysql> drop database hwf;				//模拟数据删除
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
[root@hwf opt]# mysqlbinlog --stop-datetime='22-07-29 16:06:09' /opt/data/mysql_bin.000002 |mysql -uroot -p123		//使用时间恢复
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hwf                |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

//使用start恢复
[root@hwf opt]# mysqlbinlog --start-position=458 /opt/data/mysql_bin.000002 |mysql -uroot -p123		//从操作id458开始往后面恢复
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@hwf opt]# 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

3.MySQL多实例部署

3.1下载二进制的mysql软件包
[root@hwf ~]# ls			//我将事先下好的包拉取进来
anaconda-ks.cfg  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  pubic
[root@hwf ~]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/			//解压到/usr/local/下
[root@hwf ~]# ls /usr/local/
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.38-linux-glibc2.12-x86_64  sbin  share  src
[root@hwf ~]# 
[root@hwf ~]# useradd -M -r -s /sbin/nologin mysql		//创建mysql用户和组
[root@hwf ~]# id mysql
uid=995(mysql) gid=992(mysql) groups=992(mysql)
[root@hwf ~]#
[root@hwf local]# ln -s mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
[root@hwf 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 28 14:19 lib64
drwxr-xr-x. 2 root root   6 Jun 22  2021 libexec
lrwxrwxrwx  1 root root  36 Jul 31 13:31 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x  9 root root 129 Jul 31 13:27 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 Jun 22  2021 sbin
drwxr-xr-x. 5 root root  49 Jun 28 14:19 share
drwxr-xr-x. 2 root root   6 Jul 22 17:09 src

//将mysql的属组和属主修改为mysql用户
[root@hwf local]# chown -R mysql.mysql /usr/local/mysql
[root@hwf 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 28 14:19 lib64
drwxr-xr-x. 2 root  root    6 Jun 22  2021 libexec
lrwxrwxrwx  1 mysql mysql  36 Jul 31 13:31 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x  9 root  root  129 Jul 31 13:27 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root  root    6 Jun 22  2021 sbin
drwxr-xr-x. 5 root  root   49 Jun 28 14:19 share
drwxr-xr-x. 2 root  root    6 Jul 22 17:09 src


//配置环境变量
[root@hwf bin]# echo 'export PATH=//usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@hwf bin]# source /etc/profile.d/mysql.
//映射头文件
[root@hwf mysql]# ln -s /usr/local/mysql/include/ /usr/include/mysql

[root@hwf mysql]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib

//生效mysql的man
[root@hwf 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@hwf mysql]# mkdir -p /opt/data/{3306,3307,3308}
[root@hwf mysql]# ls /opt/data/
3306  3307  3308

//将3306，3307，3308的属组属主修改为mysql
[root@hwf mysql]# chown -R mysql.mysql /opt/data/
[root@hwf mysql]# ll /opt/data/
total 0
drwxr-xr-x 2 mysql mysql 6 Jul 31 13:43 3306
drwxr-xr-x 2 mysql mysql 6 Jul 31 13:43 3307
drwxr-xr-x 2 mysql mysql 6 Jul 31 13:43 3308

//初始化各实例
[root@hwf ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql			//初始化3306
2022-07-31T05:46:15.516601Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T05:46:15.797181Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T05:46:15.819077Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T05:46:15.823454Z 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: 17c35365-1094-11ed-98fd-000c291ef509.
2022-07-31T05:46:15.858222Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T05:46:15.950582Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T05:46:15.950606Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T05:46:15.951074Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T05:46:15.992082Z 1 [Note] A temporary password is generated for root@localhost: WOBLpalhs5+d


[root@hwf ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql			//初始化3307
2022-07-31T05:46:48.011134Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T05:46:48.430413Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T05:46:48.452787Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T05:46:48.463848Z 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: 2b37dad7-1094-11ed-9baf-000c291ef509.
2022-07-31T05:46:48.464394Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T05:46:48.752367Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T05:46:48.752398Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T05:46:48.752727Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T05:46:48.890163Z 1 [Note] A temporary password is generated for root@localhost: fjptht6h&h:O



[root@hwf ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql				//初始化3308
2022-07-31T05:47:02.753743Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T05:47:03.005328Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T05:47:03.028793Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T05:47:03.032825Z 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: 33e6e783-1094-11ed-9d1b-000c291ef509.
2022-07-31T05:47:03.033180Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T05:47:03.238049Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T05:47:03.238110Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T05:47:03.238472Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T05:47:03.282822Z 1 [Note] A temporary password is generated for root@localhost: qe7//plC:W(y

//安装perl

[root@hwf ~]# dnf -y install perl

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

[root@hwf ~]# 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@hwf ~]# mysqld_multi start 3307
[root@hwf ~]# mysqld_multi start 3308
[root@hwf ~]# mysqld_multi start 3306
[root@hwf ~]# ss -anlt
State      Recv-Q      Send-Q           Local Address:Port           Peer Address:Port     Process     
LISTEN     0           128                    0.0.0.0:111                 0.0.0.0:*                    
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                       [::]:111                    [::]:*                    
LISTEN     0           128                          *:80                        *:*                    
LISTEN     0           128                       [::]:22                     [::]:*                    
LISTEN     0           128                          *:443                       *:*

//初始化密码登录

[root@hwf ~]# mysql -uroot -pWOBLpalhs5+d -S /tmp/mysql3306.sock 
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@hwf ~]# dnf provides libncurses.so.5
Last metadata expiration check: 0:01:33 ago on Sun 31 Jul 2022 02:01:25 PM CST.
ncurses-compat-libs-6.1-9.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : base
Matched from:
Provide    : libncurses.so.5

[root@hwf ~]# dnf -y install ncurses-compat-libs		//安装缺少的软件

[root@hwf ~]# mysql -uroot -pWOBLpalhs5+d -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 4
Server version: 5.7.38

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 ('123');		//修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@hwf ~]# mysql -uroot -p123 -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 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> 

//登录3307
[root@hwf ~]# mysql -uroot -p'fjptht6h&h:O' -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.38

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 ('123');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@hwf ~]# mysql -uroot -p'123' -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.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> 


//登录3308
[root@hwf ~]# mysql -uroot -p'qe7//plC:W(y' -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.38

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

mysql> exit
Bye
[root@hwf ~]# mysql -uroot -p'123' -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.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> 


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

mysql> exit
Bye
[root@hwf ~]# mysql -uroot -p'123' -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.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>
1we11
关注
MySQL文档资料，文档包含三个部分：基础、进阶、运维
05-31
6. **升级与迁移**：从一个版本升级到另一个版本，或者在不同环境间迁移MySQL实例的策略。 7. **云服务集成**：在AWS、Google Cloud或Azure等云平台上部署和管理MySQL的要点。 通过这三个篇章的学习，读者可以逐步...
MySQL面试题：从基础到进阶全面解析
10-20
6. 备份和恢复：掌握如何进行全量和增量备份，以及如何在数据丢失时进行恢复。 7. 性能调优：学习使用慢查询日志、调整参数配置、优化查询语句等方法提升MySQL性能。 8. 大数据处理：探讨如何处理大量数据，如分批...
mysql进阶(备份与恢复)
Lu鹿夫人的博客
08-18 145
mysql进阶 二进制格式mysql安装 //二进制格式的mysql软件包 [root@lwq-client ~]# cd /usr/src/ [root@lwq-client src]# ls debug kernels [root@lwq-client src]# ls debug kernels mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz ...
MySQL高级进阶(九)、备份
JingLisen的博客
09-16 1541
小景哥哥博客 MySQL高级进阶(九)、备份 MySQL数据本身提供的备份：mysqldump、ibbackup、replication。 第三方工具：xtrabacup、LVM快照备份。 根据备份的方法不同，可以将备份分为： Hot Backup(热备) Cold Backup(冷备) Warm Backup(温备) Hot Backup是指数据库运行中直接备份，对正在运行的数据库操作没有任何的影响，也就是Online Backup。Cold Backup是指备份操作是在数据库停止的情况下
mysql的进阶使用（备份）
weixin_44435182的博客
02-21 125
1.mysql配置文件 mysql的配置文件为/etc/my.cnf 配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效 /etc/my.cnf --&gt; /etc/mysql/my.cnf --&gt; --default-extra-file=/PATH/TO/CONF_FILE --&gt; ~/.my.cnf mysql常用配置文件参数： 参数 说明 ...
8 - MySQL数据读写分离｜MySQL多实例
shengweiit的博客
01-14 959
第二种方式：通过服务实现 搭建提供读写分离服务功能的服务器（程序访问时 连接的不是数据库服务器本机，而是连接读写分离服务器，有读写分离服务器提供数据库连接服务）第一种方式：通过程序实现（程序在编写访问数据库服务的脚本 执行select命令时，固定连接从服务器 执行insert命令时 固定连接master数据库服务器）如果主从结构中的从服务器宕机了，就实现不了读写分离了，会把读写请求都给主服务器。从服务器本机表里添加一条记录 （在从服务器添加的新数据 主服务器不会同步）分别发送给不同数据库服务处理。
MYSQL 多实例部署和xtrabackup
2401_83703866的博客
04-19 901
其它面试题（springboot、mybatis、并发、java中高级面试总结等）《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！……自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
MYSQL 多实例部署和xtrabackup(1)
2401_83703835的博客
04-19 419
Java架构进阶面试及知识点文档笔记这份文档共498页，其中包括Java集合，并发编程，JVM，Dubbo,Redis，Spring全家桶，MySQL，Kafka等面试解析及知识点整理Java分布式高级面试问题解析文档其中都是包括分布式的面试问题解析，内容有分布式消息队列，Redis缓存，分库分表，微服务架构，分布式高可用，读写分离等等！互联网Java程序员面试必备问题解析及文档学习笔记Java架构进阶视频解析合集《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》
mysql王者进阶下载 pdf_MySQL王者晋级之路
weixin_39928099的博客
02-28 409
第1部分　倔强青铜篇第1章　MySQL简介与主流分支版本1.1　MySQL简介1.2　MySQL主流的分支版本1.2.1　官方版本的MySQL1.2.2　Percona Server1.2.3　MariaDB第2章　MySQL数据库的安装、启动和关闭2.1　下载MySQL二进制软件包2.2　安装前系统环境检测2.3　MySQL 5.6版本的安装过程2.4　创建密码2.5　关闭MySQL数据库2.6...
MySQL statefulset_Kubernetes进阶之StatefulSet有状态部署
weixin_35723352的博客
02-03 340
K8s有状态应用部署目录：分为两类1.Headless Service2.StatefulSet• 部署有状态应用• 解决Pod独立生命周期，保持Pod启动顺序和唯一性稳定，唯一的网络标识符，持久存储有序，优雅的部署和扩展、删除和终止有序，滚动更新应用场景：数据库说在前面的话，像我们的Mysql或者Redis了，Zookerper等等这些适不适合部署在K8s中，其实呢不是太适合，但部署在里面也可以...
MySQL入门到精通—进阶主题篇
weixin_57499387的博客
06-12 1567
高可用与灾备（Replication、Failover、Clustering）、分布式数据库系统、日志管理（Binary Log、Error Log、General Log、Slow Query Log）、数据迁移与数据导入导出、使用 MySQL 工具（MySQL Workbench、mysqladmin、mysqldump）。
MySQL数据库运维.pdf
06-16
此外，还讲解了历史数据迁移和数据恢复的实际操作，确保在灾难发生时能够迅速恢复服务。 最后，常用工具的使用如mysqladmin、Percona Toolkit等，能够帮助DBA更高效地进行日常管理和维护任务。这些工具能够提升工作...
PHP＋MySQL动态网站开发全程实例
11-07
最后，了解如何部署和维护PHP+MySQL网站，包括配置服务器环境（如Apache或Nginx），设置PHP配置文件（php.ini），以及定期备份和恢复数据库。 总之，《PHP+MySQL动态网站开发全程实例》涵盖了从基础到进阶的PHP和...
实用SQL小总结
UntifA的博客
09-29 1310
SQL记录
点评项目-2-完善注册登录业务
m0_75138009的博客
09-29 533
需要处理的业务：在网页发送 /user/code 路径下的 post 请求后，我们需要检验手机号后，向合法的手机号发送一个随机生成的电话号。
Spring的热部署工具和数据库密码加盐操作
2301_77053417的博客
09-28 700
加密过程：用户输入的密码+盐值 --> 字符串1，然后使用md5对字符串1加密得到字符串2，然后字符串2+盐值 --> 字符串3，这个字符串3就是存储在数据库中的密文。背景：如果我们的密码明文存储在数据库中，要是被窃取了是非常的危险的，所以我们在数据库中存储的密码不能是明文的，需要进行加密后存储，也就是数据库中存储的是密文。解密过程：取出数据库中的盐值和用户输入的密码得到字符串1，使用md5对字符串1加密得到字符串2，然后验证字符串2和数据库中的密文即可。做好以上的步骤，你项目中的热部署就已经做好了。
World of Warcraft [CLASSIC][80][Grandel] Call to Arms: Victory in Wintergrasp
最新发布
qq 117791303
10-02 242
Wintergrasp 冬拥湖 120 VS 120
k8s搭建一主三从的mysql8集群---无坑
oopxiajun博客专栏
09-27 1037
对于构建基于MySQL的大规模、高性能应用来讲，需要使用水平扩展(集群)的数据库架构方式。在MySQL内建的复制功能可以实现，通过为服务器配置一个或多个备库的方式来进行数据同步。同时复制功能不仅有利于构建高性能的应用，也是高可用性、可扩展性、容灾、备份以及数据仓库等工作的基础。复制的基本原理是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。
MySQL新手指南：快速部署与理解安装过程
这本新手必备的MySQL安装部署中文指南为学习者提供了一个从入门到进阶的完整路径，无论你是初学者还是想深入学习MySQL的开发者，都将从中受益匪浅。对于那些希望通过实践操作来掌握MySQL的人来说，这份资源是不可或...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

1we11

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值