PROGRAMMING
mysql 备份和修改表
mysql 备份和修改表
|
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 |
#!/bin/bash DB_USER='ro_all_db' DATE=`date -d"today" +%Y%m%d` TIME=`date "+%Y-%m-%d %H:%M:%S"` MYSQL_SOURCE='172.20.x.x' # DB_PASSWORD='xxxx' echo '--------------开始备份:开始时间为 '$TIME BEGIN=`date "+%Y-%m-%d %H:%M:%S"` BEGIN_T=`date -d "$BEGIN" +%s` echo '===>备份'$MYSQL_SOURCE'的mysql实例，开始时间为 '$BEGIN BACKUP_DIR=/data/backup/$DATE/$MYSQL_SOURCE; mkdir -p $BACKUP_DIR; for i in `mysql --ssl-mode=DISABLED -h$MYSQL_SOURCE -u$DB_USER -p'mypasswd' -B iuap_apcom_supportcenter -e "show tables;" | grep 'multilang_'`; do echo "-->开始备份表$i" mysqldump --ssl-mode=DISABLED --skip-opt -h$MYSQL_SOURCE -u$DB_USER -p'mypasswd' iuap_apcom_supportcenter $i > $BACKUP_DIR/$i.sql echo "-->表${i}已经备份完成" done cd $BACKUP_DIR mkdir $BACKUP_DIR/replace_sql for i in `ls *.sql`; do cat $i | sed -n '/^INSERT INTO/p'|sed 's/^INSERT INTO/REPLACE INTO/g'> $BACKUP_DIR/replace_sql/$i done cd $BACKUP_DIR/replace_sql let num=1 echo "--------------开始修改:开始时间为 `date "+%Y-%m-%d %H:%M:%S"`" for i in `ls *.sql`; do echo "$num-->开始修改表：$i，开始时间为 `date "+%Y-%m-%d %H:%M:%S"`" mysql -h172.20.1.1 -upremises -pmypasswd iuap_apcom_supportcenter < $i echo "$num-->完成修改表：$i，完成时间为 `date "+%Y-%m-%d %H:%M:%S"`" let num+=1 done TIME=`date "+%Y-%m-%d %H:%M:%S"` echo "--------------结束修改:时间为`date "+%Y-%m-%d %H:%M:%S"`" |