mysql分批導(dǎo)出數(shù)據(jù)和分批導(dǎo)入數(shù)據(jù)庫(kù)
由于某些原因，比如說測(cè)試環(huán)境有很多庫(kù)，需要遷移到新的環(huán)境中，不需要導(dǎo)出系統(tǒng)庫(kù)的數(shù)據(jù)。而數(shù)據(jù)庫(kù)又有好多，如何才能將每個(gè)庫(kù)導(dǎo)出到的文件中呢？導(dǎo)入到一個(gè)文件的話，又很大，不好辦。
這里是這個(gè)在測(cè)試環(huán)境下的實(shí)驗(yàn)記錄，方便操作。
# 導(dǎo)出的腳本
#!/bin/bash ####################################################### # Function: dump databases # Author: davie # Date: 2019-05-20 # Version: 1.0 # Script name: /usr/local/scripts/mutiple_database_dump.sh ####################################################### Date_time=`date +%F_%H_%M` Host="192.168.100.100" User_name="admin" Pass_word="admin_password" Back_dir="/data/mysql/lww_dump/192.168.100.100_dump" Mysql_cmd="/usr/local/mysql/bin" if [ ! -d $"Back_dir" ] ;then mkdir -p "${Back_dir}" fi "${Mysql_cmd}"/mysql -h "${Host}" -u"${User_name}" -p"${Pass_word}" -e "show databases"|grep -Evi 'Database|information_schema|mysql|performance_schema' | while read db; do echo "$db"
"${Mysql_cmd}"/mysqldump -h "${Host}" -u"${User_name}" -p"${Pass_word}" --opt --hex-blob --default-character-set=utf8mb4 --skip-tz-utc --single-transaction --master-data=2 -B "${db}" | gzip> "${Back_dir}"/"${db}"."${Date_time}".sql.gz & done
?
# 導(dǎo)入腳本
# 導(dǎo)入腳本 #!/bin/bash ####################################################### # Function: import databases # Author: davie # Date: 2019-05-20 # Version: 1.0 # Script name: /usr/local/scripts/mutiple_database_import.sh ####################################################### Date_time=`date +%F_%H_%M` Host="192.168.100.101" User_name="admin" Pass_word="admin_password" Back_dir="/data/mysql/lww_dump/192.168.100.100_dump" Mysql_cmd="/usr/local/mysql/bin" Log_file="/data/mysql/lww/lww_dump/logs" ls "${Back_dir}" | while read dbfile; do /usr/bin/gunzip <"${Back_dir}"/"${dbfile}" | "${Mysql_cmd}"/mysql -h "${Host}" -u"${User_name}" -p"${Pass_word}" if [ $? -eq 0 ]; then echo "${Date_time}" >>"${Log_file}"/"${Host}".import.logs echo ""${dbfile}" ok" >>"${Log_file}"/"${Host}".import.logs else echo "${Date_time}" >>"${Log_file}"/"${Host}".import.logs echo ""${dbfile}" error" >>"${Log_file}"/"${Host}".import.logs fi done
?