mysql8导入导出数据库

1.连接数据库

mysql -u root -p

• 远程数据库

mysql -u root -p -h 192.168.1.100 -P 3307

2.导出

mysqldump -u 用户名 -p 数据库名>/mnt/usb/testdb.sql

mysqldump -u root -p testdb>/mnt/usb/testdb.sql

• 远程数据库

mysqldump -h 主机 -P 端口 -u 用户名 -p 数据库名>/mnt/usb/testdb.sql

mysqldump -h 192.168.1.100 -P 3307 -u root -p testdb>/mnt/usb/testdb.sql

3.导入

mysql -u 用户名 -p 数据库名</mnt/usb/testdb.sql

mysql -u root -p testdb</mnt/usb/testdb.sql

4.定制导出脚本

USER_NAME=$1
if [ ! -n "$USER_NAME" ]; then echo "User name can't be empty!" exit 0 
fi
PWD=$2
if [ ! -n "$PWD" ]; then echo "Password name can't be empty!" exit 0 
fi
echo "Password is $PWD"
DB_NAME=$3

if [ ! -n "$DB_NAME" ]; then echo "Database name can't be empty!" exit 0 
fi
PORT=$4
if [ ! -n "$PORT" ]; then PORT=3306 
fi
BACK_PATH=$5
if [ ! -n "$BACK_PATH" ]; then BACK_PATH='./db-back' 
fi
HOST=$6
if [ ! -n "$HOST" ]; then HOST='127.0.0.1' 
fi

BACK_FILE_NAME=_"$PORT"_$(date '+%Y%m%d%H%M%S')

if [ -a $BACK_PATH ];
    then
        echo "Back path is '$BACK_PATH'."
    else
        mkdir $BACK_PATH
fi
cd $BACK_PATH

mysqldump -h $HOST -P $PORT -u$USER_NAME -p$2 $DB_NAME>$DB_NAME$BACK_FILE_NAME.sql

• demo

sh backup.sh root 111111 dbname 3306 /mnt/usb/db-backup