ERROR 1787 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
根据错误说明使用一个空事务来跳过异常,代码如下:
1 2 3 4 5
STOP SLAVE; SET GTID_NEXT=# binlog中事务的下一个位置; BEGIN; COMMIT; # 产生一个空事务并提交 SET GTID_NEXT="AUTOMATIC"; START SLAVE;
#!/bin/bash # 注意:只能用在InnoDB表引擎的数据库上 # Usage: inno_restore_database.sh <db_name_to_restore_to> <db_backup_directory> 末尾不需要 / # mysql数据库磁盘位置 datadir=/var/lib/mysql myport=3306 # 随意指定 mysqlfrmport=3310 #username=dbrestoredude #password=dbrestoredudespasswd ####################################################################################### # Gotta be root. if [ $UID -ne 0 ]; then echo "Run this as root" ; exit ; fi
database=$1 restoredir=$2 # mysqlfrm检查 which mysqlfrm > /dev/null 2>&1 if [ $? -ne 0 ] ; then echo "Could not find the mysqlfrm utility. Install mysql-utilities."; exit ; fi # mysql目录检查 if [ ! -f $datadir/mysql/user.frm ] ; then echo "MySQL datadir not correct" ; exit ; fi # Check the restore directory, looking for a cfg/exp/idb for each frm. if [ ! -f $restoredir/db.opt ] ; then echo "Restore directory invalid, couldn't find db.opt in it"; exit ; fi stoperror=0 for restorename in $restoredir/*.frm do chkname=$(echo $restorename|sed s/.frm$//) for exten in cfg exp ibd do if [ ! -f $chkname.$exten ] ; then stoperror=1 ; fi done done if [ $stoperror -eq 1 ] ; then echo "Could not file valid restore directory files (need a cfg, exp and ibd for each frm)" echo "Did you specify a valid database directory within a backup?" echo "Did you prepare or apply-log to the backup directory?" exit fi # Get username and password if the fields are blank. if [ -z $username ] ; then read -p "Username: " username ; fi if [ -z $password ] ; then read -s -p "Password: " password ; echo ; fi # Check mysql permissions for the given user. stoperror=0 grants=$(mysql -B -u $username -p$password mysql -e "show grants for current_user"|grep 'ON *.* TO') if [ $? -ne 0 ] ; then exit ; fi if [[ $grants == *"ALL PRIVILEGES"* ]] ; then stoperror=1 ; fi if [[ $grants == *CREATE* ]] && [[ $grants == *DROP* ]] && [[ $grants == *ALTER* ]] ; then stoperror=1 ; fi if [ $stoperror != 1 ] ; then echo "User $username does not have global CREATE, DROP and ALTER" ; exit ; fi ################################################################################ # 删除存在的数据库 mysql -B -u $username -p$password -e "DROP DATABASE IF EXISTS $database" # 创建数据库 mysql -B -u $username -p$password -e "CREATE DATABASE $database" # get directory for the original DB name backupdb=$(find $restoredir -maxdepth 0 -type d -printf "%f\n" |cut -d '/' -f 1) # 使用mysqlfrm来生产建表语句并且执行 流程: # mysqlfrm生成语句-管道->mysql # | sed s/'ENGINE=InnoDB' / 'ENGINE=InnoDB ROW_FORMAT=compact' | 用来指定行格式 echo "Importing create table statements from frm files..." ; echo mysqlfrm -q --user=root --server=$username:$password@localhost:$myport --port=$mysqlfrmport $restoredir | grep -vE "^#|WARNING: Using a password on the command line interface can be insecure." | sed s/^$/';'/ | sed s/^'CREATE TABLE `'$backupdb/'CREATE TABLE `'$database/ | mysql -B -u $username -p$password echo "Table structure imported." # 主要代码,循环执行删除表空间,移动文件,恢复表空间 for frmname in $restoredir/*.frm do tablename=$(find $frmname -printf "%f\n"|sed s/.frm$//) # ALTER TABLE ... DISCARD TABLESPACE mysql -B -u $username -p$password $database -e "ALTER TABLE $tablename DISCARD TABLESPACE" for exten in exp ibd #cfg :: exp ibd cfg 文件取决于数据库版本 do which rsync > /dev/null 2>&1 if [ $? -eq 0 ] then rsync --progress $restoredir/$tablename.$exten $datadir/$database/$tablename.$exten else \cp -v $restoredir/$tablename.$exten $datadir/$database/$tablename.$exten fi chown $(find $datadir/$database/$tablename.frm -printf "%u.%g") $datadir/$database/$tablename.$exten done # ALTER TABLE ... IMPORT TABLESPACE mysql -B -u $username -p$password $database -e "ALTER TABLE $tablename IMPORT TABLESPACE" done echo "All done"