分类目录归档：MySQL

MySQL数据库文件丢失恢复

发表于 2023 年 12 月 10 日 由 惜分飞

客户服务器重启,mysql相关数据文件丢失
20231206095845

通过底层工具进行分析,无法正确恢复数据库名字,一个个单个ibd文件(而且很多本身是错误的)
20231210224010
对于这种情况,通过mysql block扫描恢复出来page文件
20231210224106
恢复出来客户需要数据
20231210224248
20231207215458
这个客户出现该故障的原因大概率是由于文件系统损坏导致.最终可以比较好的恢复,主要是故障之后第一时间保护了现场,没进行任何的写入覆盖,如果覆盖了神仙也没有办法.如果此类问题无法自行解决或者恢复效果不好,可以联系我们进行技术支持,最大限度抢救和数据,减少损失
电话/微信:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

发表在 MySQL恢复 | 标签为 , , , | 评论关闭

A____Z____RECOVER____DATA勒索恢复

发表于 2023 年 11 月 21 日 由 惜分飞

有客户MySQL数据库被黑,业务库中表被删除,并创建A____Z____RECOVER____DATA库,里面有一张readme表,内容为:

mysql> select * from readme \G;
*************************** 1. row ***************************
zh_content: 请尽快与我们取得联系，否则我们将会公布你的数据库信息在网络中，联系邮件：datacenterback@keemail.me
en_content: 请尽快与我们取得联系，否则我们将会公布你的数据库信息在网络中，联系邮件：datacenterback@keemail.me
     email: datacenterback@keemail.me
1 row in set (0.00 sec)

a_z_recover_data

对于这种情况,本质就是mysql drop 库或者drop表级别的恢复,通过反删除软件恢复,可惜恢复效果很差（底层发生了大量的覆盖）
os-recovery
对于这种情况,只能采用底层block级别恢复,通过底层扫描分析
20231121211906
并解析扫描结果恢复需要数据
20231121212028
对于类似这种A____Z____RECOVER____DATA勒索恢复,建议先对系统进行镜像或者快照,然后按照先os层面恢复,在block级别恢复的方法处理,如果无法自行解决,可以联系我们进行技术支持,最大限度抢救和数据,减少损失
电话/微信:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com
另外建议加强系统和mysql安全加固,数据库尽量不要暴露在公网上

发表在 MySQL恢复 | 标签为 , , , | 评论关闭

unknown variable ‘default-character-set=utf8′ 处理

发表于 2023 年 5 月 22 日 由 惜分飞

分享一例由于mysql参数配置不当,当时mysql无法正常启动的case

[root@XIFENFEI ~]# service mysql start
Redirecting to /bin/systemctl start mysql.service
Job for mysqld.service failed because the control process exited with error code. 
See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@XIFENFEI ~]# systemctl status mysqld.service
 mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2023-05-22 16:09:57 CST; 13s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 101951 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
  Process: 102545 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=1/FAILURE)

May 22 16:09:53 RHAA08-0901 systemd[1]: Starting LSB: start and stop MySQL...
May 22 16:09:57 RHAA08-0901 mysqld[102545]: Starting MySQL.... ERROR! 
            The server quit without updating PID file (/www/se....pid).
May 22 16:09:57 RHAA08-0901 systemd[1]: mysqld.service: control process exited, code=exited status=1
May 22 16:09:57 RHAA08-0901 systemd[1]: Failed to start LSB: start and stop MySQL.
May 22 16:09:57 RHAA08-0901 systemd[1]: Unit mysqld.service entered failed state.
May 22 16:09:57 RHAA08-0901 systemd[1]: mysqld.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

mysql日志

2023-05-22T06:59:57.646266Z 0 [Warning] option 'max_allowed_packet': unsigned value 107374182400 adjusted to 1073741824
2023-05-22T06:59:57.646480Z 0 [Note] --secure-file-priv is set to NULL. 
          Operations related to importing and exporting data are disabled
2023-05-22T06:59:57.646548Z 0 [Note] /www/server/mysql/bin/mysqld (mysqld 5.7.41-log) starting as process 79451 ...
2023-05-22T06:59:57.726167Z 0 [Note] InnoDB: PUNCH HOLE support available
2023-05-22T06:59:57.726257Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2023-05-22T06:59:57.726269Z 0 [Note] InnoDB: Uses event mutexes
2023-05-22T06:59:57.726286Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2023-05-22T06:59:57.726296Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2023-05-22T06:59:57.726306Z 0 [Note] InnoDB: Using Linux native AIO
2023-05-22T06:59:57.726975Z 0 [Note] InnoDB: Number of pools: 1
2023-05-22T06:59:57.727161Z 0 [Note] InnoDB: Using CPU crc32 instructions
2023-05-22T06:59:57.733999Z 0 [Note] InnoDB: Initializing buffer pool, total size = 4G, instances = 8, chunk size = 128M
2023-05-22T06:59:58.051369Z 0 [Note] InnoDB: Completed initialization of buffer pool
2023-05-22T06:59:58.087559Z 0 [Note] InnoDB: If the mysqld execution user is authorized,
 page cleaner thread priority can be changed. See the man page of setpriority().
2023-05-22T06:59:58.099727Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2023-05-22T06:59:58.202034Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-05-22T06:59:58.202150Z 0 [Note] InnoDB: Setting file '/www/server/data/ibtmp1' size to 12 MB. 
Physically writing the file full; Please wait ...
2023-05-22T06:59:58.242233Z 0 [Note] InnoDB: File '/www/server/data/ibtmp1' size is now 12 MB.
2023-05-22T06:59:58.244128Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2023-05-22T06:59:58.244163Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2023-05-22T06:59:58.244910Z 0 [Note] InnoDB: Waiting for purge to start
2023-05-22T06:59:58.295153Z 0 [Note] InnoDB: 5.7.41 started; log sequence number 1184000067
2023-05-22T06:59:58.295727Z 0 [Note] InnoDB: Loading buffer pool(s) from /www/server/data/ib_buffer_pool
2023-05-22T06:59:58.296135Z 0 [Note] Plugin 'FEDERATED' is disabled.
2023-05-22T06:59:58.296288Z 0 [Note] InnoDB: Buffer pool(s) load completed at 230522 14:59:58
2023-05-22T06:59:58.299095Z 0 [ERROR] unknown variable 'default-character-set=utf8'
2023-05-22T06:59:58.299131Z 0 [ERROR] Aborting

2023-05-22T06:59:58.299179Z 0 [Note] Binlog end
2023-05-22T06:59:58.299530Z 0 [Note] Shutting down plugin 'ngram'
2023-05-22T06:59:58.299560Z 0 [Note] Shutting down plugin 'partition'
2023-05-22T06:59:58.299572Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2023-05-22T06:59:58.299588Z 0 [Note] Shutting down plugin 'ARCHIVE'
2023-05-22T06:59:58.299599Z 0 [Note] Shutting down plugin 'MyISAM'
2023-05-22T06:59:58.299626Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2023-05-22T06:59:58.299641Z 0 [Note] Shutting down plugin 'MEMORY'
2023-05-22T06:59:58.299657Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2023-05-22T06:59:58.299797Z 0 [Note] Shutting down plugin 'CSV'
2023-05-22T06:59:58.299811Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2023-05-22T06:59:58.299822Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2023-05-22T06:59:58.299832Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2023-05-22T06:59:58.299843Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2023-05-22T06:59:58.299853Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2023-05-22T06:59:58.299863Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2023-05-22T06:59:58.299873Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2023-05-22T06:59:58.299883Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2023-05-22T06:59:58.299893Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2023-05-22T06:59:58.299904Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2023-05-22T06:59:58.299914Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2023-05-22T06:59:58.299924Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2023-05-22T06:59:58.299934Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2023-05-22T06:59:58.299944Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2023-05-22T06:59:58.299954Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2023-05-22T06:59:58.299964Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2023-05-22T06:59:58.299974Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2023-05-22T06:59:58.299984Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2023-05-22T06:59:58.299995Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2023-05-22T06:59:58.300005Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2023-05-22T06:59:58.300015Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2023-05-22T06:59:58.300025Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2023-05-22T06:59:58.300035Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2023-05-22T06:59:58.300045Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2023-05-22T06:59:58.300055Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2023-05-22T06:59:58.300066Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2023-05-22T06:59:58.300075Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2023-05-22T06:59:58.300085Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2023-05-22T06:59:58.300095Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2023-05-22T06:59:58.300105Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2023-05-22T06:59:58.300116Z 0 [Note] Shutting down plugin 'InnoDB'
2023-05-22T06:59:58.300271Z 0 [Note] InnoDB: FTS optimize thread exiting.
2023-05-22T06:59:58.300437Z 0 [Note] InnoDB: Starting shutdown...
2023-05-22T06:59:58.400764Z 0 [Note] InnoDB: Dumping buffer pool(s) to /www/server/data/ib_buffer_pool
2023-05-22T06:59:58.401153Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 230522 14:59:58
2023-05-22T07:00:00.340582Z 0 [Note] InnoDB: Shutdown completed; log sequence number 1184000086
2023-05-22T07:00:00.343128Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2023-05-22T07:00:00.343157Z 0 [Note] Shutting down plugin 'sha256_password'
2023-05-22T07:00:00.343165Z 0 [Note] Shutting down plugin 'mysql_native_password'
2023-05-22T07:00:00.343347Z 0 [Note] Shutting down plugin 'binlog'
2023-05-22T07:00:00.344547Z 0 [Note] /www/server/mysql/bin/mysqld: Shutdown complete

提示比较明显由于default-character-set=utf8参数设置不当当时,检查my.cnf配置，发现
在mysqld中配置了default-character-set=utf8,该参数正确配置为:character-set-server = utf8,设置正确参数值之后,mysql启动正常

[root@XIFENFEI data]# service mysql start
Redirecting to /bin/systemctl start mysql.service
[root@XIFENFEI data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.41-log Source distribution

Copyright (c) 2000, 2023, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 365xxxy             |
| xxxxhu         |
| xxxiuye              |
| mysql              |
| performance_schema |
| xxxxchegn         |
| sxxxxp               |
| sys                |
| xxxxc               |
+--------------------+
10 rows in set (0.00 sec)
发表在 MySQL | 评论关闭