扩容LVM卷导致lvm元数据丢失的恢复过程

一、问题描述

因某次MySQL binlog占用过高扩容时，是直接对云盘操作，而扩容直接操作了lvm卷而未操作云盘分区，并随后执行了扩容的partprobe，resize2fs卷等操作；最后，显示并未扩容成功，重启系统后，无法进入，包文件系统错误，无法加载，注释后重启，发现LVM信息丢失。

参考：recover-lvm2-partition、serverfault、TestDisk工具恢复分区指导

二、过程复现及修复

2.1、过程复现

fdisk /dev/mapper/mysql_data-mysql  #执行了d分区操作，主从应该在云盘分区操作
e2fsck -f /dev/mapper/mysql_data-mysql
fuser -m -v -i -k /opt/mysql
umount /dev/mapper/mysql_data-mysql /opt/mysql
resize2fs /dev/mapper/mysql_data-mysql
1
2
3
4
5

重启后报错如下：

注释故障盘后，重启进入系统，执行pvs vgs lvs 发现无之前的相应的PV,VG,LV 信息，至此，lvm信息丢失。因此，必须考虑恢复lvm信息才能进一步恢复数据；实际LVM的元数据信息是会在lvm变动那一刻自动备份的，目录保存在/etc/lvm/目录下，vg在archive目录下，lv在backup下；即LVM 会默认存储用户对 PV/VG/LV 的每一步操作，并自动把当前的 VG 的信息备份到一个文件里面，位置是 /etc/lvm/backup/VG 名或者 /etc/lvm/archive/VG 名。这个文件里面记录的东西大概跟 vgdisplay/pvdisplay/lvdisplay 输出的信息一致，里面也包括了对于恢复 VG 信息至关重要的 PV UUID。综上，我们可以利用这些备份进行进行LVM恢复。

回顾：The superblock in a filesystem contains important metadata about the filesystem, such as its size, block size, empty and filled blocks and their counts, and other critical information。super block 是硬盘分区开头——开头的第一个byte是byte 0，从 byte 1024开始往后的一部分数据。由于 block size最小是 1024 bytes，所以super block可能是在block 1中（此时block 的大小正好是 1024 bytes），也可能是在block 0中。 超级块中的数据其实就是文件卷的控制信息部分，也可以说它是卷资源表，有关文件卷的大部分信息都保存在这里。例如：硬盘分区中每个block的大小、硬盘分区上一共有多少个block group、以及每个block group中有多少个inode。超级块是指描述文件系统的大小、类型和状态以及关于其他元数据结构的信息的文件系统元数据（数据的数据），它对于文件系统来说非常重要。因此，对于每个文件系统，超级块保留在多个冗余副本中。如果分区的超级块损坏，我们将无法通过操作系统挂载分区的文件系统，因为系统无法识别该文件系统数据。而Magic numbers 是文件的前几个字节，对于特定文件类型来说是唯一的，也称为文件签名，对用户是透明的，无法看到。文件系统的magic number 存储在Superblock中，操作系统将使用这个magic number来确定与磁盘 /dev/sxx 相关联的文件系统；超级块错误中的 Bad magic number 清楚地表明了操作系统无法使用超级块数据确定 /dev/sdb 的文件系统类型。如果遇到这种报错，可利用dumpe2fs和mke2fs来确认super-block的备份，其中dumpe2fs 将在已安装或已卸载的磁盘上工作，但 mke2fs 需要卸载磁盘。

The “Bad Magic Number in Super-block” error can occur due to various reasons, such as a sudden power outage, hardware issues, or an incorrect shutdown of the system.To fix this error, you can use alternate superblocks. The filesystem maintains multiple backup copies of the superblock in different locations for such scenarios.

dumpe2fs /dev/mapper/mysql_data-mysql |grep -i superblock

dumpe2fs 1.42.9 (28-Dec-2013)
dumpe2fs: Bad magic number in super-block while trying to open /dev/mapper/mysql_data-mysql
Couldn't find valid filesystem superblock
#方式2
mke2fs -n /dev/mapper/mysql_data-mysql 

mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
67108864 inodes, 268434432 blocks
13421721 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
8192 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
	32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
	4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968, 
	102400000, 214990848

fsck -b 32768 /dev/sdb   #or
mount sb=32768 /dev/sdb /mountpoint    #or
e2fsck -b 32768 /dev/sdb

If we are getting the same error with superblock located on 32768, repeat the step with another superblock location.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

2.2、LVM及文件系统修复

#列出在Linux中恢复LVM元数据的备份文件
vgcfgrestore --list mysql_data   #输出如下

 File:		/etc/lvm/archive/mysql_data_00000-369742915.vg
  Couldn't find device with uuid gzdJP8-ltvz-l2Kd-WfJD-335J-NrDd-mhwi7w.
  VG name:    	mysql_data
  Description:	Created *before* executing 'vgcreate mysql_data /dev/sdb1'
  Backup Time:	Thu Jun 16 15:17:40 2022

   
  File:		/etc/lvm/archive/mysql_data_00001-1831552697.vg
  VG name:    	mysql_data
  Description:	Created *before* executing 'lvcreate -n mysql -l 100%VG mysql_data'
  Backup Time:	Thu Jun 16 15:19:52 2022
 
  File:		/etc/lvm/backup/mysql_data
  VG name:    	mysql_data
  Description:	Created *after* executing 'vgs'
  Backup Time:	Mon Oct  2 21:46:48 2023

#查看重启后的sdb1磁盘的UUID，原来的是sdd盘号，对应sdd1，重启后变成了sdb1
pvdisplay /dev/sdb1
  --- Physical volume ---
  PV Name               /dev/sdb1
  VG Name               mysql_data
  PV Size               <1024.00 GiB / not usable <3.97 MiB
  Allocatable           yes (but full)
  PE Size               4.00 MiB
  Total PE              262143
  Free PE               0
  Allocated PE          262143
  PV UUID               gzdJP8-ltvz-l2Kd-WfJD-335J-NrDd-mhwi7w  #这里是更正后，原来的没截图；正常如果这里的ID与原来的不一样，也会导致vg无法识别，从而无法正常加载lvm信息

#备份
dd if=/dev/sdb1 of=/data/sdb1.img
#备份MBR分区表
dd if=/dev/sda of=/path/to/backup bs=512 count=1 skip=1
#恢复分区表，然后输入“x”进入高级模式，在其中选择“e”来编辑分区表，并将备份数据写入主分区表
fdisk /dev/sda

#查看lvm备份信息获取原UUID
less /etc/lvm/backup/mysql_data

#使用原来的 PV UUID 来创建 PV，并使用自动备份的文件来恢复信息。

pvcreate /dev/sdb1 -u gzdJP8-ltvz-l2Kd-WfJD-335J-NrDd-mhwi7w --restorefile /etc/lvm/backup/mysql_data -vv #加--test 参数测试命令执行后再执行，-u=--uuid，-ff参数强制重做

……
      Reading VG #orphans_lvm2
      Processing device /dev/sda1.
      Processing device /dev/sdb1.
      Processing device /dev/sdc1.
      /dev/sdc1: No lvm label detected
    Wiping signatures on new PV /dev/sdc1.
      /dev/sdc1: size is 3145725952 sectors
      devices/default_data_alignment not found in config: defaulting to 1
      Device /dev/sdc1: queue/minimum_io_size is 512 bytes.
      Device /dev/sdc1: queue/optimal_io_size is 0 bytes.
      /dev/sdc1: Setting PE alignment to 2048 sectors.
      Device /dev/sdc1: alignment_offset is 0 bytes.
      /dev/sdc1: Setting PE alignment offset to 0 sectors.
      Using limited metadata area size on /dev/sdc1 with value 1044480 (limited by pe_start of 1048576).
    Set up physical volume for "/dev/sdc1" with 3145725952 available sectors.
      Scanning for labels to wipe from /dev/sdc1
    Zeroing start of device /dev/sdc1.
    Writing physical volume data to disk "/dev/sdc1".
      /dev/sdc1: Writing label to sector 1 with stored offset 32.
  Physical volume "/dev/sdc1" successfully created.
      Unlocking /run/lock/lvm/P_orphans




pvdisplay   /dev/sdb1 #验证

#恢复 VG
vgcfgrestore  -f /etc/lvm/backup/mysql_data mysql_data -v  #也可以用--test

#验证
vgs
vgscan
vgchange -ay mysql_data  #激活vg分区

lvs  #验证显示正常

mount /dev/mysql_data/mysql /opt/mysql #报错，无法挂载

#检查 superblock and magic number
dumpe2fs -h /dev/mysql_data/mysql
dumpe2fs -h /dev/sdb |grep -i magic

#安装xxd命令，查看magic number
yum install vim-common -y



#检查文件系统
tune2fs -l /dev/mysql_data/mysql #报错

tune2fs 1.42.9 (28-Dec-2013)
tune2fs: Bad magic number in super-block while trying to open /dev/mysql_data/mysql
Couldn't find valid filesystem superblock.

#查看文件系统状态
debugfs -w  /dev/mysql_data/mysql

#检查超级快
dumpe2fs /dev/mysql_data/mysql | grep superblock

dumpe2fs 1.42.9 (28-Dec-2013)
dumpe2fs: Bad magic number in super-block while trying to open /dev/mysql_data/mysql
Couldn't find valid filesystem superblock.

#正常输出如下
dumpe2fs /dev/mapper/mysql_datas-data |grep superblock
dumpe2fs 1.42.9 (28-Dec-2013)
  Primary superblock at 0, Group descriptors at 1-128
  Backup superblock at 32768, Group descriptors at 32769-32896
  Backup superblock at 98304, Group descriptors at 98305-98432
  Backup superblock at 163840, Group descriptors at 163841-163968
  Backup superblock at 229376, Group descriptors at 229377-229504
  Backup superblock at 294912, Group descriptors at 294913-295040
  Backup superblock at 819200, Group descriptors at 819201-819328
  Backup superblock at 884736, Group descriptors at 884737-884864
  Backup superblock at 1605632, Group descriptors at 1605633-1605760
  Backup superblock at 2654208, Group descriptors at 2654209-2654336
  Backup superblock at 4096000, Group descriptors at 4096001-4096128
  Backup superblock at 7962624, Group descriptors at 7962625-7962752
  Backup superblock at 11239424, Group descriptors at 11239425-11239552
  Backup superblock at 20480000, Group descriptors at 20480001-20480128
  Backup superblock at 23887872, Group descriptors at 23887873-23888000
  Backup superblock at 71663616, Group descriptors at 71663617-71663744
  Backup superblock at 78675968, Group descriptors at 78675969-78676096
  Backup superblock at 102400000, Group descriptors at 102400001-102400128
  Backup superblock at 214990848, Group descriptors at 214990849-214990976


#找不到超级快，使用以下命令查找
mke2fs -n /dev/mysql_data/mysql   #-n参数，表并不会实际执行文件系统操作
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
67108864 inodes, 268434432 blocks
13421721 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
8192 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
	32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
	4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968, 
	102400000, 214990848
	
e2fsck -b 32768 /dev/mysql_data/mysql   #尝试用32768超级块，重建超级块，注意该命令有风险，最为最后决断之操作

e2fsck -b 78675968 /dev/mapper/mysql_data-mysql   #如下所示，重建失败
e2fsck 1.42.9 (28-Dec-2013)
e2fsck: Invalid argument while trying to open /dev/mapper/mysql_data-mysql

The superblock could not be read or does not describe a correct ext2
filesystem.  If the device is valid and it really contains an ext2
filesystem (and not swap or ufs or something else), then the superblock
is corrupt, and you might try running e2fsck with an alternate superblock:
    e2fsck -b 8193 <device>

#终极大招
mke2fs -S /dev/mapper/mysql_data-mysql 
mke2fs 1.42.9 (28-Dec-2013)
Discarding device blocks: done                            
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
67108864 inodes, 268434432 blocks
13421721 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
8192 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
	32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
	4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968, 
	102400000, 214990848

Allocating group tables: done                            
Writing superblocks and filesystem accounting information: done     

tune2fs -l /dev/mysql_data/mysql   #验证，检查文件系统
tune2fs 1.42.9 (28-Dec-2013)
Filesystem volume name:   <none>
Last mounted on:          <not available>
Filesystem UUID:          a460879d-a6a7-4f64-9fca-29a6a184d1f1
Filesystem magic number:  0xEF53
Filesystem revision #:    1 (dynamic)
Filesystem features:      ext_attr resize_inode dir_index filetype sparse_super
Filesystem flags:         signed_directory_hash 
Default mount options:    user_xattr acl
Filesystem state:         clean with errors
Errors behavior:          Continue
Filesystem OS type:       Linux
Inode count:              67108864
Block count:              268434432
Reserved block count:     13421721
Free blocks:              264204269
Free inodes:              67108864
First block:              0
Block size:               4096
Fragment size:            4096
Reserved GDT blocks:      960
Blocks per group:         32768
Fragments per group:      32768
Inodes per group:         8192
Inode blocks per group:   512
Filesystem created:       Tue Oct  3 01:42:18 2023
Last mount time:          n/a
Last write time:          Tue Oct  3 01:48:35 2023
Mount count:              0
Maximum mount count:      -1
Last checked:             Tue Oct  3 01:42:18 2023
Check interval:           0 (<none>)
Reserved blocks uid:      0 (user root)
Reserved blocks gid:      0 (group root)
First inode:              11
Inode size:	          256
Required extra isize:     28
Desired extra isize:      28
Default directory hash:   half_md4
Directory Hash Seed:      f55bc2d5-b091-4311-a198-7d7cf9ec8b2d

#修复文件系统
e2fsck -fy /dev/mysql_data/mysql  #结果恢复失败

e2fsck 1.42.9 (28-Dec-2013)
Resize inode not valid.  Recreate? yes

Pass 1: Checking inodes, blocks, and sizes
Root inode is not a directory.  Clear? yes

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244

2.3、主从切换，使用从库

#正常需要先将主库只读

set global read_only=ON;
set global super_read_only=ON;

#从库确认同步状态正常
show slave status\G  #输出如下
Slave_IO_Running，Slave_SQL_Running状态为YES，Seconds_Behind_Master为0

#从库停掉复制进程并清空主从信息

stop slave;
reset slave all;  #提前备份relay-log;或
RESET MASTER;   #清除从服务器上的binlog文件和索引；主服务器有硬件故障，无法清除binlog日志，需要手动备份binlog日志，并复制到另一台服务器上；
RESET SLAVE;    #清除从服务器上的主从状态信息

set global read_only=off;
set global super_read_only=off;

#主库降级未备库，主库上执行

CHANGE MASTER TO MASTER_HOST='172.18.1.19',MASTER_USER='repl',MASTER_PORT=3306,MASTER_PASSWORD='xxx',master_auto_position=1 ;
start slave;
show slave status\G

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

注意：出现故障后不要重启，不要重启，不要重启！！！

三、附录

3.1、xfs和ext4的区别

centos7默认文件系统是xfs， xfs是一种非常优秀的日志文件系统，它是SGI公司设计的。xfs被称为业界最先进的、最具可升级性的文件系统技术；xfs是一个64位文件系统，最大支持8EB减1字节的单个文件系统，实际部署时取决于宿主操作系统的最大块限制。对于一个32位Linux系统，文件和文件系统的大小会被限制在16TB；xfs在很多方面确实做的比ext4好，ext4受限制于磁盘结构和兼容问题，可扩展性和scalability确实不如xfs。

centos6默认文件系统是ext4， ext4是第四代扩展文件系统（英语：Fourth EXtended filesystem，缩写为ext4）是linux系统下的日志文件系统，是ext3文件系统的后继版本（ centos5默认文件系统是ext3）； ext4的文件系统容量达到1EB，而文件容量则达到16TB。理论上支持无限数量的子目录；

3.2、LVM 云盘扩容

云盘扩容
fdisk /dev/xxx  #磁盘分区扩容
pvresize /dev/xxx  #卷扩容
lvextend -L +增加容量 逻辑卷路径
1
2
3
4

3.3、MySQL sql文件大导入慢

通常情况下，执行SQL文件导入数据过程中的速度受多种因素影响。其中，网络传输速度、服务器配置、SQL语句优化以及SQL文件的大小等都可能对导入速度产生影响。SQL文件过大导致MySQL导入SQL太慢的原因之一。如果SQL文件太大，MySQL服务器需要花费更长的时间来读取和处理数据。在导入SQL文件时，MySQL会检查表中的索引和约束是否已存在，如果表中已经存在相同的索引或约束，则不需要再次创建，但如果要导入的表上有索引和约束，MySQL在插入数据时会对每一行进行检查，这会显著增加导入数据的时间。因此，为了加快导入SQL的速度，建议在导入SQL文件之前先删除表中的索引和约束。默认情况下，在导入SQL文件时，MySQL会使用自动提交事务的方式。如果SQL文件中包含大量的数据操作语句，那么将频繁提交事务，从而影响导入速度。可临时禁用自动事务提交，手动在导入数据后提交事务。如果服务器性能不是很好，sql文件又大，可拆分大型SQL文件，考虑将其拆分为多个较小的文件，并分批导入，这样可以减少单个导入操作的负载和时间。sql大文件还会为导致MySQL需要更多时间来逐行解析和插入数据，大量的插入操作同时会导致磁盘IO瓶颈。

mysqldump导出与source在mysql中导入数据是常用的数据恢复方法；但经常会遇到sql全备的文件越来越大，source或者mysql -uroot -p database < mysql.sql；source适用于需要与数据库交互的复杂导入任务。它还可以用于执行部分 SQL文件或在交互式会话中逐行执行 SQL 语句。在效率方面，两种方式的性能差异通常不大。导入速度更多取决于 SQL 文件的大小、数据库服务器的性能和网络连接质量。对于大型 SQL文件，可能会花费更多时间来完成导入。一般，对于简单的导入任务，使用 mysql 命令是更方便；而对于需要与数据库进行交互或执行复杂导入任务的情况，使用source 命令更为适合。也可以用mysqlimport -u username -p database file.sql进行导入，适合更多格式的sql；

如果你现场使用的是InnoDB存储引擎，InnoDB存储引擎对于大型数据表的处理速度更快，特别是在并发写入时。其他引擎的考虑临时更换；

如果现场的MySQL版本支持并行导入，可以尝试使用多个线程同时导入数据。通过使用 --parallel 或 --threads 参数，可以指定并行导入的线程数量。比如：mysql -u username -p --database your_database --parallel=4 < your_file.sql

综上，我们可通过如下方法改善sql导入慢问题：

1）优化代导入数据库的一些参数，减轻sql文件自身的一些固有现在

1、控制 MySQL 磁盘写入策略 以及 数据安全性 的两个关键参数： innodb_flush_log_at_trx_commit 和 sync_binlog

参数：innodb_flush_log_at_trx_commit，用于控制redo日志写入log file以及落盘时机

①如果设置为0，log buffer将每秒一次地写入log file中，并且同时进行log file的flush(刷新到磁盘中)操作。此模式下，在事务提交时，不主动触发写入磁盘的操作；

②如果设置为1，此模式下，在每次事务提交时，mysql都会把log buffer的数据写入log file中，并且同时进行log file的flush(刷新到磁盘中)操作；默认值1

③如果设置为2，此模式下，在每次事务提交时，mysql都会把log buffer的数据写入log file中，但是不会同时进行log file的flush(刷新到磁盘中)操作，会每秒执行一次log file的flush(刷新到磁盘中)操作。

注意：由于进程调度策略问题，不能保证"每秒执行一次flush(刷新到磁盘中)操作"100%的"每一秒执行一次"。

可把innodb_flush_log_at_trx_commit这个参数改为0 ，相关经验表明配置后source快接近100倍
SHOW VARIABLES LIKE ‘innodb_flush_log_at_trx_commit’ ;
SET GLOBAL innodb_flush_log_at_trx_commit =0; #完成后恢复
SET GLOBAL innodb_flush_log_at_trx_commit =1;

参数：sync_binlog，控制binlog同步磁盘的时机

①如果设置N=0，像操作系统刷新其他文件的机制一样，mysql不会同步到磁盘中去，而是依赖操作系统来刷新binary log。

②如果设置N>0，mysql在每写N次二进制日志binary log时，会调用fdatasync()函数将二进制日志binary log同步到磁盘中去。默认值1；或者可以直接关闭binlog日志：set sql_log_bin=0;或set sql_log_bin=off;导入后再开启重启数据库。

注意：如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。

最佳实践：相关经验表明
当innodb_flush_log_at_trx_commit=1和sync_binlog=1时，写入操作性能最差；
当innodb_flush_log_at_trx_commit=2和sync_binlog=2时，写入操作达到最高性能；

①当innodb_flush_log_at_trx_commit设置为0时，mysqld进程崩溃会导致上一秒所有事务数据丢失。

②当innodb_flush_log_at_trx_commit和sync_binlog都为1时最为安全，mysqld进程崩溃或者服务器crash的情况下，binary log只有可能最多丢失一个事务。

③当innodb_flush_log_at_trx_commit设置为2时，只有在服务器崩溃或断电情况下，上一秒所有事务数据才可能丢失。

2、调整sql存储参数
innodb_buffer_pool_size：增大 InnoDB 的缓冲池大小，以便更多的数据能够驻留在内存中。SET GLOBAL innodb_buffer_pool_size = 3G;推荐可设置值为系统内存的30%，然后重启服务验证；
max_allowed_packet：增大允许的最大包大小，以确保能够处理大型SQL语句。
key_buffer_size：增大，如果使用

3、表的最大容量问题 max_allowed_packet，如果SQL文件中有非常大的BLOB数据，可能需要增大max_allowed_packet的值
show variables like ‘%max_allowed_packet%’;
select @@max_allowed_packet
修改，重启mysq才能生效
set global max_allowed_packet = 102410141024;

4、表的大小写敏感检查
show variables like “%case%”; #lower_case_table_names = 1 不区分大小写

5、字符集确认
SHOW VARIABLES LIKE ‘character_set_server’;
查看数据库默认字符集：
SHOW CREATE DATABASE database_name;
查看表的字符集：
SHOW CREATE TABLE table_name;
查看列的字符集：
SHOW FULL COLUMNS FROM table_name;
查看连接的字符集：
SHOW VARIABLES LIKE ‘character_set_connection’;
查看客户端字符集：
SHOW VARIABLES LIKE ‘character_set_client’;

注意：BLOB (Binary Large Object) 数据是一种数据库中用于存储二进制数据的数据类型。BLOB 数据可以存储图像、音频、视频、文档等任意类型的二进制数据。BLOB 数据通常用于存储大型文件或复杂的数据结构，例如图像文件或数据库备份。它可以存储在数据库表中的 BLOB 列中，或者以文件的形式存储在文件系统中，并在数据库中存储文件的路径。BLOB 数据是一种用于存储二进制数据的数据库数据类型，可以存储各种类型的文件和复杂的数据结构。

2）禁用索引和约束：在导入过程中禁用索引和约束可以显著加快导入速度，导入过程中没有索引和约束可能会导致数据完整性问题，请谨慎使用。

ALTER TABLE your_table_name DISABLE KEYS;
SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE your_table_name ENABLE KEYS;
SET FOREIGN_KEY_CHECKS = 1;

MySQL默认开启了autocommit模式，每执行一条insert语句都会自动commit一次，严重影响导入速度。可关闭自动提交和约束检查
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
ALTER TABLE your_table DISABLE KEYS;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;
重新建立索引
ALTER TABLE your_table ENABLE KEYS;

3）拆分大sql

split -l 1000 file.sql split_ #按照每个文件包含 1000 行的方式拆分为多个小文件，文件名以 "split_" 开头，注意导入时需注意顺序，按顺序执行

#安装
pip install splitsql

#使用splitsql进行拆分：
splitsql -f input.sql -d output_dir -r path_rule_sql_file   #-f表示输入SQL文件的路径，-d为拆分文件输出的目录，-r为定义拆分规则的SQL文件，可以自定义规则，以适应不同的需求
1
2
3
4
5
6
7

另外还可借助工具：splitsql是一款用于将SQL文件拆分为多个文件的工具，它可以将大的SQL文件分成每个表一个文件，或者按照自定义规则将SQL文件拆分成多个小的文件，使得SQL文件更加易于维护和管理。

使用splitsql，可以轻松地将SQL文件按照数据库或表定义拆分，拆分后的每个文件可独立执行，非常适合于开发大型项目的DBA和开发人员。

4）手动开启事务

start transaction;
source .../mysql.sql
commit;  #手动提交

#恢复二进制日志
set sql_log_bin=on;
#恢复事务自动提交
set autocommit=1;
1
2
3
4
5
6
7
8

3.4、Rsync命令回顾

#语法
rsync   -arP -e "ssh -i 密钥路径"  用户名@云服务器ip:云服务器文件路径 本地文件夹路径


#常用参数：

--progress： 显示拷贝进度

--partial：保留不完整文件，实现断点续传

--partial-dir=DIR：指定不完整文件的存储目录，而不是默认存储到目的地目录。

-P：包含--progress和--partial

--rsh=ssh：使用ssh方式传输文件，注意：如果之前设置过ssh免密码登录，那么此时也就不需要密码了，非常方便

-v：显示详细信息

-a：归档模式。也就是以递归方式传输文件，并保持所有文件属性。

-r：递归方式传输文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3.5、辅助命令screen

#使用screen后台运行，可随时调取观察任务进度
#优点：单独开一个会话放到后台，无效的报警信息会被打印到该后台终端，而不会被写入sql文件，而且可以满足交互式场景下后台运行。
#安装screen
yum -y install screen
#创建有名字的screen任务(执行该命令后，其实就进入了screen的shell环境,此时执行的操作 都是放在screen里的)
screen -S task_name
//然后，按下 Ctrl+a 然后按下 d。这样会话将会被分离，而你可以回到原来的终端窗口。在 screen 会话中，可以快速切换窗口来管理多个会话。按下 Ctrl+a 然后按下 n 即可切换到下一个窗口，按下 Ctrl+a 然后按下 p 即可切换到上一个窗口。按下 Ctrl+a 然后按下 c 可以在当前会话中创建一个新的窗口。

#查看到系统中所有的screen任务的pid
screen -ls
#或者ps -ef来查找screen的任务
ps -ef | grep task_name
#进入screen任务以后，就可以运行相要后台跑的任务了
mysqldump --skip-lock-tables  -h  10.x.x.x   -P 3306  -uroot  >backup_20230525.sql  -p'test_password' &
#然后把该screen放后台：方法1：快捷键  ctrl+a+d；方法2：再开一个终端
screen -d task_name
#进入screen任务
screen -r pid  #或者
screen -r task_name
#删除screen任务
screen -r task_name
//按下 Ctrl+a 然后按下 t 可以在屏幕上显示时间。按下 Ctrl+a 然后按下 : 可以进入命令模式，在命令行中可以输入消息。这些消息会被显示在所有窗口的底部。
//exit或 Ctrl+d退出screen窗口
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23