【MySQL】数据备份导入导出

# 导出
mysqldump  -uroot -p123456 dbname > /data_back.dump
# 导入
mysql -uroot -p123456  dbname < /data_back.dump

docker

导出
docker exec mysql mysqldump -uroot -p123456 dbname > /data_back.dump
导入
docker exec mysql -uroot -p123456 dbname < /data_back.dump

 

授权

mysql -u root -p
mysql>ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
mysql>ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
mysql>FLUSH PRIVILEGES;
mysql>use mysql;
mysql>update user set user.Host='%'where user.User='root';
mysql>SELECT user, host, plugin, authentication_string FROM mysql.user;
mysql>flush privileges;
mysql>exit;

 

备份脚本移动

#!/bin/bash
DATE=`date +'%d'`
cd /data/back
mysqldump -uroot -p123456 --set-gtid-purged=off mydb > mydb.sql
if [ $? == 0 ];then
   tar  zcf mydb$DATE.tgz mydb.sql --remove-files && scp mydb$DATE.tgz root@192.168.1.100:/data/back 
fi