[MySQL] 数据库复制异常恢复之使用mysqldump导数据

刺骨的言语ヽ痛彻心扉 2023-07-23 03:52 24阅读 0赞

环境：两台服务器双机备份，数据库采用主主复制（以下主机指HA集群的主机）

1、停止 HA 集群服务  
2、分别在主、备机上关闭 MySQL 主从复制和 MySQL 事件调度器。

mysql> stop slave;
    Query OK, 0 rows affected (0.01 sec)
    mysql> stop slave io_thread;
    Query OK, 0 rows affected (0.07 sec)
    mysql> set global event_scheduler=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@event_scheduler;
    +-------------------+
    | @@event_scheduler |
    +-------------------+
    | OFF               |
    +-------------------+
    1 row in set (0.00 sec)

3、使用mysqldump导出主机数据库里的数据。

说明：可以使用一个脚本封装导出、导入数据的命令。

4、以 root 用户分别登录主、备机，清空主、备数据库中的数据。

说明：可以导入一个初始建库.sql脚本

5、把主、备机上的 MySQL 数据库的日志清空。  
\# rm -rf mysql-bin.\*  
\# rm -rf  \*relay-bin\*

6、重启主、备机上的 MySQL。

7、MySQL 启动后，分别关闭主、备机上 MySQL 的事件调度器，并重置和开启 MySQL 的主从复制。

mysql> set global event_scheduler=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@event_scheduler;
    +-------------------+
    | @@event_scheduler |
    +-------------------+
    | OFF               |
    +-------------------+
    1 row in set (0.00 sec)
    mysql> reset slave;
    Query OK, 0 rows affected (0.00 sec)
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)

8、检查主、备机 MySQL 数据库的同步状态，正常则开始执行数据导入。

9、在主机上使用mysqldump导入步骤3 导出的数据，此时备机会自动同步主机数据库中导入的数据。

10、检查主、备机 MySQL 数据同步状态，确认主、备机数据库状态为如下显示。

mysql> show slave status\G;
    (返回内容有省略，关注以下行即可)
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
      Seconds_Behind_Master: 0
              Last_IO_Errno: 0
              Last_IO_Error:
             Last_SQL_Errno: 0
             Last_SQL_Error:
    Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

11、分别在主、备机上打开 MySQL 的事件调度器。

mysql> set global event_scheduler=1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@event_scheduler;
    +-------------------+
    | @@event_scheduler |
    +-------------------+
    | ON                |
    +-------------------+
    1 row in set (0.00 sec  )

12、在主机上启动 HA 集群服务。

附：步骤3和9，使用mysqldump导出、导入数据可以使用一个脚本完成（遍历所有表、打印日志），如下ImExportData.sh

#!/bin/bash
    MYSQL_PATH='/somedir/mysql/bin/'
    
    getTime(){
      timestamp=`date +'%Y-%m-%d %H:%M:%S'`
      echo "[$timestamp]: "
    }
    
    exportAction(){
      exportPath=$1
      echo "Exporting table..."
    
      if [ ! -d $exportPath ]; then
        mkdir -p $exportPath
        echo "`getTime`make file path $exportPath"
      fi
    
      for tableName in 'tbl tbl2 tbl3'
      do
        echo "`getTime`Exporting table $tableName"
        $MYSQL_PATH/mysqldump -u$user -p$pwd -q -e somedb $tableName > $exportPath/${tableName// /#}.sql 2>/dev/null
        echo "`getTime`Exporting table $tableName done"
      done
    
      echo "Export table done"
    }
    
    importAction(){
      importPath=$1
      echo "Importing table..."
    
      if [ ! -d $importPath ]; then
        echo "`getTime`can not find $importPath"
        return
      fi
    
      allFiles=`ls $importPath`
      for file in $allFiles
      do
        table=${file//#/ }
        table=${table/%.sql/}
        echo "`getTime`Importing table $table"
        $MYSQL_PATH/mysql -u$user -p$pwd somedb < $importPath/$file 2>/dev/null
        echo "`getTime`Importing table $table done"
      done
    }
    
    printHelp(){
      echo "Usage: ./ImportExportData.sh <dbuser> <dbpasswd> <operation> <filePath>"
      echo "  <operation>   : import (insert the exported data to db)"
      echo "                : export (dump the data from db)"
      echo "  <filePath>    : data file path"
    }
    
    #check user validation
    user=`whoami`
    if [ "$user" != "root" ]; then
       echo "The login user should be root."
       exit
    fi
    
    #check db status
    dbpid=`ps -ef | grep mysqld| grep -v grep | grep -v mysqld_safe | awk '{print $2}'`
    if [ "$dbpid" == "" ];then
       echo "mysqld isn't running, please start it..."
       exit
    fi
    
    user=$1
    passwd=$2
    op=$3
    path=$4
    
    if [ "$op" == "export" -a "$path" != "" ]; then
      exportAction $path
    elif [ "$op" == "import" -a "$path" != "" ]; then
      importAction $path
    else
      printHelp
    fi