一行命令
mysqldump -h172.168.15.222 -P3406 -uroot -p123456 -C --databases artisan |mysql -h172.168.15.221 -P3406 -uroot -p123456 zfdc
-C 压缩
Shell 导入导出
【导出】
#!/bin/bash # 以下配置信息请根据现场实际情况修改 mysql_user="root" #MySQL备份用户 mysql_password="123456" #MySQL备份用户的密码 mysql_host="172.168.15.222" mysql_port="3406" mysql_charset="utf8" #MySQL编码 backup_db_arr=("chkproof" "framework" "zfdc" "zfmg") #要备份的数据库名称,多个用空格分开隔开 backup_location=/home/ap/mysqlbackup03 #备份数据存放位置,末尾请不要带"/", 不存在则创建文件夹 # 本行开始以下不需要修改 # 连接到mysql数据库,无法连接则备份退出 mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<end use mysql; select host,user from user where user='root' and host='localhost'; exit end flag=`echo $?` if [ $flag != "0" ]; then echo "ERROR:Can't connect mysql server! please check config or server ! backup stop!" exit else echo "MySQL connect ok! Please wait......" # 判断有没有定义备份的数据库,如果定义则开始备份,否则退出备份 if [ "$backup_db_arr" != "" ];then for dbname in ${backup_db_arr[@]} do echo "database【$dbname】backup start...may cost servral minutes depends on the size of database , please waiting for a while ..." `mkdir -p $backup_location` # 如果有创建数据库的权限 就使用下面这一行带有 -B 的指令 #`mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B $dbname --default-character-set=$mysql_charset > $backup_location/$dbname.sql` # 前提:数据库用户创建好。 建行分配的应用账号没有创建DB的权限,所以导出的脚本中 不包含 create database. `mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --default-character-set=$mysql_charset > $backup_location/$dbname.sql` flag=`echo $?` if [ $flag == "0" ];then echo "database $dbname success backup to $backup_location/$dbname.sql" echo " " else echo "database $dbname backup fail!" fi done else echo "ERROR:No database to backup! Please check config ! backup stop" exit fi echo "All database backup success!" exit fi
【导入】
#!/bin/bash # 以下配置信息请根据现场实际情况修改 mysql_user="root" mysql_password="123456" mysql_host="172.168.15.200" mysql_port="3406" mysql_charset="utf8" #MySQL编码 backup_location=/home/ap/mysqlbackup03 # 本行开始以下不需要修改 # 连接到mysql数据库,无法连接则备份退出 mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<end use mysql; select host,user from user where user='root' and host='localhost'; exit end flag=`echo $?` if [ $flag != "0" ]; then echo "ERROR:Can't connect mysql server! please check config or server ! import stop!" exit else echo "MySQL connect ok! Please wait......" # 判断有没有配置DB文件存放的位置 if [ "$backup_location" != "" ];then for zffile in `ls $backup_location`; do echo "file【$zffile】import start...may cost servral minutes depends on the size of database , please waiting for a while ..." database=`echo $zffile | awk -F "." '{ print $1}'` echo "$database importing..." `mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $database < $backup_location/$zffile` flag=`echo $?` if [ $flag == "0" ];then echo "$zffile import successfully" echo " " else echo "$zffile import fail!!!" fi done else echo "ERROR:No file to import! Import stop" exit fi echo "All database import success!" exit fi