#全部備份
[root@localhost home]# docker  exec  c_mysql sh -c 'exec mysqldump --all-databases -uroot -p123456 --all-databases' > /home/movice202302.sql
#備份數(shù)據(jù)轉(zhuǎn)移
[root@localhost home]# docker cp /home/movice202302.sql salve-mysql:/var # salve-mysql 是容器 
#全部恢復(fù)
root@6faa12ee2d96:/# mysql -uroot -p123456 < /var/movice202302.sql

#查看恢復(fù)數(shù)據(jù)庫(kù)情況：
mysql -uroot -p123456 -e 'drop database SCHOOL;'
mysql -uroot -p123456-e 'SHOW DATABASES;'

[root@localhost home]# docker exec c_mysql sh -c 'exec mysqldump  -uroot -p123456 movice_fuli' > /home/movie0216.sql
Warning: Using a password on the command line interface can be insecure.

導(dǎo)出數(shù)據(jù)庫(kù)表數(shù)據(jù)
mysqldump -uroot -pdbpasswd -t db_name >db.sql;

root@localhost home]# docker exec c_mysql sh -c 'exec mysqldump  -uroot -p123456 -t movice_fuli' > /home/movie.sql

導(dǎo)出數(shù)據(jù)庫(kù)表結(jié)構(gòu)
mysqldump -uroot -pdbpasswd -d db_name >db.sql;
root@localhost home]# docker exec c_mysql sh -c 'exec mysqldump  -uroot -p123456 -d movice_fuli' > /home/movie.sql
導(dǎo)出數(shù)據(jù)庫(kù)中某個(gè)表的表結(jié)構(gòu)
mysqldump -uroot -pdbpasswd -d db_name table_name >db.sql
root@localhost home]# docker exec c_mysql sh -c 'exec mysqldump  -uroot -p123456 -d movice_fuli user' > /home/movie.sql
導(dǎo)出數(shù)據(jù)庫(kù)中某個(gè)表的表結(jié)構(gòu)和表數(shù)據(jù)
mysqldump -uroot -pdbpasswd db_name table_name >db.sql;

root@localhost home]# docker exec c_mysql sh -c 'exec mysqldump  -uroot -p123456  movice_fuli user' > /home/movie.sql

root@localhost ~]# cd /usr
[root@localhost usr]# ls
bin  etc  games  include  lib  lib64  libexec  local  sbin  share  src  tmp
[root@localhost usr]# mkdir data
[root@localhost usr]# ls
bin  data  etc  games  include  lib  lib64  libexec  local  sbin  share  src  tmp
[root@localhost usr]# cd data
[root@localhost data]# ls 
[root@localhost data]# touch back_clean.sh 
[root@localhost data]# touch backup .sh
[root@localhost data]# mkdir logs
[root@localhost data]# mkdir mysql_bak
[root@localhost data]# ls
back_clean.sh  backup  logs  mysql_bak
[root@localhost data]# rm backup
rm：是否刪除普通空文件 "backup"？y
[root@localhost data]# touch backup.sh
[root@localhost data]# ls
back_clean.sh  backup.sh  logs  mysql_bak

vi backup.sh

BACKUP_ROOT=/usr/data/
BACKUP_FILEDIR=$BACKUP_ROOT/mysql_bak
#當(dāng)前日期
DATE=$(date +%Y%m%d)
# 獲取容器
mysqlid=docker ps  -aqf "name=c_mysql"
#查詢所有數(shù)據(jù)庫(kù)
DATABASES=$(docker exec -i ${mysqlid}  mysql  -uroot -p123456 -e "show databases" | grep -Ev "Database|sys|information_schema|performance_schema|mysql")
#循環(huán)數(shù)據(jù)庫(kù)進(jìn)行備份
for db in $DATABASES
do
echo
if [[ "${db}" =~ "+" ]] || [[ "${db}" =~ "|" ]];then
echo "jump over ${db}"
else
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------
docker exec -i ${mysqlid}  mysqldump -uroot -p123456 --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gz
echo ${db}
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------
echo
fi
done

echo "備份完成"

vi back_clear.sh
echo ----------CLEAN BEGIN----------
find /usr/data/mysql_bak/ -mtime +7 -name "*.gz" -exec rm -rf {} \;
echo ----------CLEAN COMPLETE---------

#每天02:00自動(dòng)清理大于7天的mysql備份
00 2 * * * /usr/data/backup_clean.sh > /usr/data/logs/backup_full_clean.log 2>&1

#每天11:00自動(dòng)備份mysql
00 11 * * * /usr/data/backup.sh > /usr/data/logs/backup.log 2>&1