sh 将mysql中所有的索引保存到一个文件中_mysql 多个索引同一个文件-优快云博客

本文介绍了一种通过脚本从MySQL服务器获取多个表的索引信息，并将这些信息加载到另一MySQL数据库的方法。该脚本利用特定命令从performance_schema中抽取索引数据，再通过LOAD DATA LOCAL INFILE命令将其导入目标数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

##IP            ftp server IP
##ftp_USER      ftp server LOGIN USER
##ftp_PASSWORD  ftp server ftp_PASSWORD
##SyncDataDir   DC文件下载到71服务器的地址
##mysql_dc  DC MYSQL SERVER config 
##mysql_bc  BC MYSQL SERVER config 
ftp_IP=172.16.60.191
##ftp_IP=172.16.18.6  #测试地址
ftp_USER=bc
ftp_PASSWORD="bc1qaz@WSX"
db=BCSourceData_Sync
SyncDataDir=/work/bcjob/SyncData
## 下面这两个是生产环境中的配置
#mysql_dc="mysql -h172.16.60.77 -uTB_BC -pda-tb-bc-pw -P 3309"
#mysql_bc="mysql -h172.16.60.71 -uBCAppAccount -p31eq#!EQ -P 3306"
## 测试环境
mysql_72="mysql -h172.16.60.72 -ubc_reader -pbc@$^read -P 3306"
mysql_bc="mysql -h172.16.18.203 -uroot -p1qaz -P 3306"
cd $SyncDataDir




 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.cond_instances ; " >> $SyncDataDir/All_Index.tsv`                                           
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.events_waits_current ; " >> $SyncDataDir/All_Index.tsv`                                     
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.events_waits_history ; " >> $SyncDataDir/All_Index.tsv`                                     
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.events_waits_history_long ; " >> $SyncDataDir/All_Index.tsv`                                
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.events_waits_summary_by_instance ; " >> $SyncDataDir/All_Index.tsv`                         
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.events_waits_summary_by_thread_by_event_name ; " >> $SyncDataDir/All_Index.tsv`             
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.events_waits_summary_global_by_event_name ; " >> $SyncDataDir/All_Index.tsv`                
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.file_instances ; " >> $SyncDataDir/All_Index.tsv`                                           
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.file_summary_by_event_name ; " >> $SyncDataDir/All_Index.tsv`                               
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.file_summary_by_instance ; " >> $SyncDataDir/All_Index.tsv`                                 
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.mutex_instances ; " >> $SyncDataDir/All_Index.tsv`                                          
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.performance_timers ; " >> $SyncDataDir/All_Index.tsv`                                       
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.rwlock_instances ; " >> $SyncDataDir/All_Index.tsv`                                         
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.setup_consumers ; " >> $SyncDataDir/All_Index.tsv`                                          
 `$mysql_72 -N -e " set names "utf8";set character_set_database= "utf8";  SHOW INDEX FROM performance_schema.setup_instruments ; " >> $SyncDataDir/All_Index.tsv`                                        








`$mysql_bc -e " LOAD DATA LOCAL INFILE '$SyncDataDir/All_Index.tsv' INTO TABLE   test.All_Index CHARACTER SET utf8;"`