重建mysql从库、表数据迁移

一、背景

1、xtrabackup+ systemd管理

cat /etc/systemd/system/mysql_slave.service 
[Unit]
Description=MySQL Slave Container
After=docker.service
Requires=docker.service

[Service]
TimeoutStartSec=60
RuntimeDirectory=mysqld
RuntimeDirectoryMode=0777
ExecStartPre=-/usr/bin/docker rm -f mysql_slave
ExecStartPre=-/usr/bin/docker pull "ip:5000/mysql:5.7"
ExecStartPre=/usr/bin/docker volume create mysqls_data
ExecStartPre=/usr/bin/docker volume create mysqls_log
ExecStart=/backup/mysql/mysql_slave.run
ExecStop=-/usr/bin/docker stop mysql_slave
Restart=on-failure
RestartSec=10

[Install]
WantedBy=multi-user.target

启动文件

cat /backup/mysql/mysql_slave.run
#!/bin/bash
/usr/bin/docker run -i \
    -e MYSQL_ROOT_PASSWORD=xxx \
    -e MYSQL_DATABASE=xxx \
    -e LANG=C.UTF-8 \
    -v /backup/mysql/mysql_slave_my.cnf:/etc/mysql/my.cnf \
    -v /var/run/mysqld:/var/run/mysqld \
    -v mysqls_data:/var/lib/mysql \
    -v mysqls_log:/var/log/mysql \
    --privileged=true \
    --net=host \
    --rm \
    --name=mysql_slave \
    "ip:5000/mysql:5.7"

2、mysql的配置文件

bin log 部分，设置bin log保存时间、格式等

[mysql]
default-character-set = utf8


[mysqld]
# Server options
tmpdir = /tmp
transaction_isolation = READ-COMMITTED
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
symbolic-links = 0

# Server System Variables
character_set_server = utf8
max_allowed_packet = 32M
max_connections = 2000
max_connect_errors = 10000
key_buffer_size = 32M
sort_buffer_size = 16M
join_buffer_size = 4M
read_buffer_size = 32M
read_rnd_buffer_size = 32M
bulk_insert_buffer_size = 16M
query_cache_type = 0
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 3
lower_case_table_names = 1
wait_timeout = 86400    
interactive_timeout = 43200
explicit_defaults_for_timestamp = 1
secure_file_priv = NULL
skip_name_resolve = ON

# Binary Logging Options and Variables
server-id = 1
binlog-format = ROW
log-bin = mysql-bin
log_error = /var/log/mysql/mysql.err
binlog-ignore-db = mysql
binlog_cache_size = 16M
expire_logs_days = 99


innodb_buffer_pool_size = 8589934592

3、bin log三种格式 

MySQL 的二进制日志（binlog）是用于记录更改数据库状态的日志文件，主要用于数据恢复或复制。

MySQL 支持三种不同的 binlog 格式：Statement、Row 和 Mixed。

1. Statement-Based Logging (声明式日志)

描述

  • 在这种模式下，MySQL 记录的是执行的 SQL 语句，而不是实际数据更改的行。
  • 每当执行一个更改数据的 SQL 语句（如 INSERT、UPDATE 或 DELETE）时，相关的 SQL 语句会以文本形式记录到 binlog 中。

特点

  • 优点：

    • 存储空间占用较少，因为只记录 SQL 语句，而非数据的具体变化。
    • 任何能够被 SQL 引擎执行的更改都可以被记录，保持了灵活性。

  • 缺点：

    • 在某些情况下，由于当前上下文的差异（如函数的非确定性行为、时间戳等），相同的 SQL 语句在主从服务器上可能导致不一致。
    • 可能会导致某些语句在复制时无法完全重放，由于不同时刻的承诺或环境差异，导致更新数据时可能出现问题。

2. Row-Based Logging (行式日志)

描述

  • 在行式日志中，MySQL 记录的是实际更改的表的行数据，而非整个 SQL 语句。
  • 当对表进行 INSERT、UPDATE 或 DELETE 操作时，具体更改的行信息（包括哪些行发生了什么更改）会被记录。

特点

  • 优点：

    • 数据的准确性高，因为每个行的更改都有明确记录。
    • 复制时更可靠，不会受到 SQL 语句上下文的影响。

  • 缺点：

    • 相对于声明式日志，行式日志占用的存储空间较大，因为每个更改都记录了具体的行信息。
    • 如果涉及到大批量的数据变更，日志的体积增长可能会很快。

3. Mixed Logging (混合日志)

描述

  • 混合日志模式结合了声明式和行式日志的优点。根据具体 SQL 语句的特性，MySQL 可以选择使用声明式或行式日志。
  • 对于简单的语句，MySQL 可能选择声明式日志，而对于那些可能出现复制不一致的复杂语句，选择行式日志。

特点

  • 优点：

    • 在性能与数据一致性之间提供了更好的平衡。
    • 充分利用声明式日志的存储效率，同时在必要时确保数据完整性和准确性。

  • 缺点：

    • 在一些情况下，可能会导致理解和管理上的复杂性，因为不同语句使用不同的日志记录方法。
    • 依赖于 MySQL 的内部逻辑决定使用哪种日志模式。

总结

选择使用哪种 binlog 格式要考虑应用程序的需求、数据一致性要求以及性能考虑。通常来说，在主从复制环境中，行式日志因其数据的准确性和一致性，提供了更强的保障，

而声明式日志由于其较小的存储需求和较好的性能，适合不要求极高一致性的场景。混合模式则是一个折衷方案，能灵活调整。

二、实操

在mysql slave节点操作

1、停止mysql服务

systemctl stop mysql_slave.service

2、重建mysql从库的数据卷

docker volume rm mysqls_data
docker volume rm mysqls_log
docker volume create mysqls_data
docker volume create mysqls_log

注意:清理目录后，不要再启动mysql从库服务

3、临时开放防火墙端口

firewall-cmd --zone=public --add-port=20000/tcp

4、开启接收服务

nc -l 20000|xbstream -x -C /healsci/docker/volumes/mysqls_data/_data/

5、主库操作

xtrabackup --defaults-file=/backup/mysql/mysql_master_my.cnf --backup --user=root --password=xxx 
--host=127.0.0.1 --port=3306 --datadir=/healsci/docker/volumes/mysqlm_data/_data/ 
--parallel=8 --compress --compress-threads=8 --stream=xbstream | nc node3 20000

备份期间，不能执行ddl操作，否则备份会失败。
当执行结束后，最后一行显示有 completed OK!，代表备份操作顺利完成。

6、在mysql从库节点执行以下命令：

firewall-cmd --zone=public --remove-port=20000/tcp

xtrabackup --decompress --remove-original --parallel=8 --target-dir=/heal/docker/volumes/mysqls_data/_data

xtrabackup --prepare --use-memory=4G --target-dir=/heal/docker/volumes/mysqls_data/_data

7、启动mysql从库

systemctl start mysql_slave.service

mysql -uroot -pxxx -S /var/run/mysqld/mysqld.sock

CHANGE MASTER TO MASTER_HOST='ip',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx;

MASTER_LOG_FILE和MASTER_LOG_POS值获取：

当用主库备份时，查询备份目录（/heal/docker/volumes/mysqls_data/_data/xtrabackup_info）文件中的binlog_pos行，对应filename和position

8、启动mysql主从同步

start slave;

show slave status\G;

补充：

1、sql 删除bin log文件

# 删除bin log文件
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

# 删除编号小于 'mysql-bin.xxxxx’
PURGE BINARY LOGS TO 'mysql-bin.xxxxx’;

2、忽略不写入bin log 的库(不支持到表级别)

binlog-ignore-db = mysql

https://www.jb51.net/database/3376468fd.htm

待补充

三、使用脚本重建从库

1、依赖

使用xtrabackup程序采用流的方式备份主库

openeuler-22.03-LTS:

percona-xtrabackup-24,percona-toolkit,qpress,nmap,tmux,jq,yq

centos-7:

percona-xtrabackup-24,percona-toolkit,qpress,nmap,tmux.nmap-ncat

注：

qpress 是一个高效的文件压缩工具，特别适合用于快速压缩和解压缩文件。qpress 经常与数据库备份工具（如 Percona XtraBackup）一起使用，用于高效地压缩备份文件

percona-toolkit 是一个工具集，大致包含数据一致性检查、查询性能诊断、数据库复制管理、数据归档、索引优化

ls /usr/bin/pt-*
/usr/bin/pt-align                  /usr/bin/pt-fingerprint          /usr/bin/pt-mongodb-index-check   /usr/bin/pt-secure-collect  /usr/bin/pt-table-checksum
/usr/bin/pt-archiver               /usr/bin/pt-fk-error-logger      /usr/bin/pt-mongodb-query-digest  /usr/bin/pt-show-grants     /usr/bin/pt-table-sync
/usr/bin/pt-config-diff            /usr/bin/pt-heartbeat            /usr/bin/pt-mongodb-summary       /usr/bin/pt-sift            /usr/bin/pt-table-usage
/usr/bin/pt-deadlock-logger        /usr/bin/pt-index-usage          /usr/bin/pt-mysql-summary         /usr/bin/pt-slave-delay     /usr/bin/pt-upgrade
/usr/bin/pt-diskstats              /usr/bin/pt-ioprofile            /usr/bin/pt-online-schema-change  /usr/bin/pt-slave-find      /usr/bin/pt-variable-advisor
/usr/bin/pt-duplicate-key-checker  /usr/bin/pt-k8s-debug-collector  /usr/bin/pt-pg-summary            /usr/bin/pt-slave-restart   /usr/bin/pt-visual-explain
/usr/bin/pt-fifo-split             /usr/bin/pt-kill                 /usr/bin/pt-pmp                   /usr/bin/pt-stalk
/usr/bin/pt-find                   /usr/bin/pt-mext                 /usr/bin/pt-query-digest          /usr/bin/pt-summary

2、脚本

#!/bin/bash

#Manually defined mysql node

mysql_master_node=node1
if [[ ! -z ${mysql_master_node} ]];then
  mysql_master_volume=/healsci/docker/volumes/mysqlm_data/_data
fi

mysql_slave_node=node2
if [[ ! -z ${mysql_slave_node} ]];then
  mysql_slave_volume=/healsci/docker/volumes/mysqls_data/_data
fi

#add this option ,ssh connect to remote server,will not 
#ask you if need add new server fingerprint or change one
#this oprion stay add or modify fingerprint at known_hosts
readonly ssh_option="-o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -o LogLevel=QUIET"

#Get os cpu numbers and xtrabackup use half
readonly cpu_num=$(cat /proc/cpuinfo|grep processor|wc -l)
readonly half_cpu_num=`expr ${cpu_num} / 2`

readonly firewalld_port=20000
readonly status_pid="/tmp/mysql_rebuild.pid"
readonly backup_log="/var/log/mysql_backup.log"
readonly slave_info="/tmp/slave_info"

readonly mysql_master_file="/backup/mysql/mysql_master_my.cnf"
readonly mysql_slave_file="/backup/mysql/mysql_slave_my.cnf"
readonly mysql_unix_sock="/var/run/mysqld/mysqld.sock"
readonly mysql_master_service="mysql_master.service"
readonly mysql_slave_service="mysql_slave.service"
readonly xtrabackup_backup_option="--defaults-file=${mysql_master_file} --no-version-check --backup --user=root --password=xxx --socket=${mysql_unix_sock} --parallel=${half_cpu_num} --compress --compress-threads=${half_cpu_num} --stream=xbstream --target-dir=/tmp"
readonly xtrabackup_decompress_option="--decompress --remove-original --parallel=${half_cpu_num}"
readonly xtrabackup_prepare_option="--prepare --use-memory=4G"

readonly pt_slave_find_option="--resolve-address --report-format=hostname"
readonly mysql_dsn="u=root,p=xxx,S=/var/run/mysqld/mysqld.sock"

function find_mysql_slave_node()
{
  mysql_slave_node=$(ssh root@${mysql_master_node} "pt-slave-find ${pt_slave_find_option} ${mysql_dsn} 2>/dev/null|grep -|cut -d' ' -f3|cut -d'(' -f 2|cut -d')' -f 1")
  if [[ -z "${mysql_slave_node}" ]];then
    echo "Can't found any mysql slave node.Exit"
    exit 0
  fi
}

function find_mysql_data_volume()
{
  if [[ $1 == "master" ]];then
    mysql_container_name=$(ssh root@${mysql_master_node} 'docker ps --format "table {{.Names}}"|grep mysql_master')
    mysql_master_volume=$(ssh root@${mysql_master_node} docker inspect ${mysql_container_name}|jq -r '.[]|.Mounts|.[]|select(.Name=="mysqlm_data")|.Source')
    if [[ -z "${mysql_master_volume}" ]];then
      echo "Can't find mysql master data volume.Exit"
      exit 0
    fi
  elif [[ $1 == "slave" ]];then
    mysql_container_name=$(ssh root@${mysql_slave_node} 'docker ps --format "table {{.Names}}"|grep mysql')
    mysql_slave_volume=$(ssh root@${mysql_slave_node} docker inspect ${mysql_container_name}|jq -r '.[]|.Mounts|.[]|select(.Name=="mysqls_data")|.Source')
    if [[ -z "${mysql_slave_volume}" ]];then
      echo "Can't find mysql slave data volume.Exit"
      exit 0
    fi
  else
    echo "Can't find mysql any data volume.Exit"
    exit 0
  fi
}

function manager_firewalld_port()
{
  if [[ $1 == "add" ]];then
    ssh root@${mysql_slave_node} "firewall-cmd --zone=public --add-port=${firewalld_port}/tcp 1>/dev/null 2>&1"
  elif [[ $1 == "remove" ]];then
    ssh root@${mysql_slave_node} "firewall-cmd --zone=public --remove-port=${firewalld_port}/tcp 1>/dev/null 2>&1"
  else
    echo "Can't manager firewalld port for mysql backup.Exit"
    exit 0
  fi
}

function create_session_tmux()
{
  ssh ${ssh_option} root@$1 "tmux kill-server 1>/dev/null 2>&1"
  ssh ${ssh_option} root@$1 "tmux new -s $2 -d"
  ssh ${ssh_option} root@$1 "tmux send -t $2 '$3 && exit' Enter"
}

function test_node_port()
{
  port_status=$(nmap -T4 -p ${firewalld_port} ${mysql_slave_node}|grep "${firewalld_port}/tcp"|grep open|wc -l)
}

function get_slave_info()
{
  #Get slave info from file,because mysql slave service have been stop by backup mysql
  if [[ -f ${slave_info} ]];then
    local list=($(cat ${slave_info}))
    if [[ "x${#list[@]}" == "x2" ]];then
      mysql_slave_node=${list[0]}
      mysql_slave_volume=${list[1]}
    else
      echo "Can't find usefull info from ${slave_info}.Exit"
      exit 0
    fi
  else
    if [[ -z ${mysql_slave_node} ]];then
      echo "Can't find mysql slave info.Exit"
      exit 0
    fi
  fi
}

function reset_mysql_repl()
{
  get_slave_info
  local tmux_en=$(ssh root@${mysql_slave_node} "tmux ls 2>/dev/null|grep mysql_prepare|wc -l")
  if [[ ${tmux_en} -eq 1 ]];then
    echo "Mysql prepare stay running.Exit"
    exit 0
  elif [[ ${tmux_en} -eq 0 ]];then
    local check_log=$(ssh root@${mysql_slave_node} "tail -n 1 ${backup_log}|grep 'completed OK!'|wc -l")
    if [[ ${check_log} -eq 1 ]];then
      #Before start mysql check if set super_read_only is on
      ssh ${ssh_option} root@${mysql_slave_node} "sed -i 's/^\(super_read_only.*\)/#\1/g' ${mysql_slave_file}"
      skip_slave=$(ssh ${ssh_option} root@${mysql_slave_node} "egrep skip-slave-start ${mysql_slave_file}|wc -l")
      if [[ ${skip_slave} -eq 0 ]];then
        ssh ${ssh_option} root@${mysql_slave_node} "sed -i '$ a skip-slave-start' ${mysql_slave_file}"
      elif [[ ${skip_slave} -eq 1 ]];then
        ssh ${ssh_option} root@${mysql_slave_node} "sed -i 's/  #skip-slave-start$/skip-slave-start/g' ${mysql_slave_file}"
      else
        echo "Configure mysql slave fail.Exit."
        exit 0
      fi
      ssh ${ssh_option} root@${mysql_slave_node} "systemctl start ${mysql_slave_service}"
      sleep 10
      #Test if mysql slave start finally
      while :
      do
        ssh ${ssh_option} root@${mysql_slave_node} "test -S ${mysql_unix_sock}"
        if [[ $? -eq 0 ]];then
          break
        else
          sleep 5
        fi
      done
      scp -Brq ${mysql_slave_node}:${mysql_slave_volume}/xtrabackup_info /tmp
      local mysql_bin_file=$(egrep "binlog_pos" /tmp/xtrabackup_info|awk -v FS="'" '{print $2}')
      local mysql_position=$(egrep "binlog_pos" /tmp/xtrabackup_info|awk -v FS="'" '{print $4}')
      cp -f ./mysql_slave_init.template /tmp/mysql_slave_init.sql
      sed -i -e "s/master_ip/${master_node_ip}/g" -e "s/mysql-bin.000001/${mysql_bin_file}/g" -e "s/MASTER_LOG_POS=1/MASTER_LOG_POS=${mysql_position}/g" /tmp/mysql_slave_init.sql
      scp -Brq /tmp/mysql_slave_init.sql ${mysql_slave_node}:/tmp
      #Setup mysql slave replication and start it
      ssh ${ssh_option} root@${mysql_slave_node} "mysql < /tmp/mysql_slave_init.sql"
      ssh ${ssh_option} root@${mysql_slave_node} "sed -i 's/  skip-slave-start$/#skip-slave-start/g' ${mysql_slave_file}"
      ssh ${ssh_option} root@${mysql_slave_node} "sed -i 's/  #\(super_read_only.*\)/\1/g' ${mysql_slave_file}"
      ssh ${ssh_option} root@${mysql_slave_node} "mysql -e 'set global super_read_only=on;'"
      ssh ${ssh_option} root@${mysql_master_node} "/usr/bin/rm -f ${slave_info} ${status_pid} '/tmp/mysql_slave_init.sql' '/tmp/xtrabackup_info'"
      ssh ${ssh_option} root@${mysql_slave_node} "/usr/bin/rm -f /tmp/mysql_slave_init.sql"
      echo "Script rebuil mysql on ${mysql_slave_node} is finally.Exit"
      exit 0
    else
      echo "Mysql prepare status wrong.Exit"
      exit 0
    fi
  else
    echo "Mysql prepare staying running.Exit"
    exit 0
  fi
}

function prepare_mysql_slave()
{
  echo -e "\033[31m Script will clean ${mysql_slave_volume} at ${mysql_slave_node}. \033[0m"
  echo -e "\033[31m This is the last chance to give up this operation. \033[0m"
  read -n1 -p "Are you sure to continue rebuild mysql slave?[Yy/Nn]:" answer
  case ${answer} in
    Y|y)
      #Prepare mysql slave
      #1:Stop mysql slave service
      ssh ${ssh_option} root@${mysql_slave_node} "systemctl stop ${mysql_slave_service} 1>/dev/null 2>&1"
      echo -e "\nWait stop ${mysql_slave_service} at ${mysql_slave_node}"
      while :
      do
        ssh ${ssh_option} root@${mysql_slave_node} "test -S ${mysql_unix_sock}"
        if [[ $? -eq 1 ]];then
          break
        else
          sleep 30
        fi
      done
      #2:Recreate mysql slave volumes(include date and log volumes)
      local volume_list=($(ssh ${ssh_option} root@${mysql_slave_node} "docker volume ls --quiet|grep mysql"))
      for volume_name in ${volume_list[@]}
      do
        ssh ${ssh_option} root@${mysql_slave_node} "docker volume rm ${volume_name} 1>/dev/null 2>&1 && docker volume create ${volume_name} 1>/dev/null 2>&1"
      done
      ;;
    *)
      echo "Stop rebuild mysql slave.Exit"
      exit 0
      ;;
  esac
}

function mysql_backup()
{
  get_slave_info
  prepare_mysql_slave
  manager_firewalld_port add
  #Start nc to get mysql backup stream data
  local rev_command="nc -l ${firewalld_port}|xbstream -x -C ${mysql_slave_volume}"
  create_session_tmux ${mysql_slave_node} mysql_slave "${rev_command}"
  test_node_port
  if [[ ${port_status} -eq 1 ]];then
    #Start backup mysql to mysql slave node
    backup_command="xtrabackup ${xtrabackup_backup_option} --datadir=${mysql_master_volume} 2>${backup_log}|nc ${mysql_slave_node} ${firewalld_port}"
    create_session_tmux ${mysql_master_node} mysql_backup "${backup_command}"
    #Change pid file to 1
    echo 1 > ${status_pid}
    echo "Start backup mysql to ${mysql_slave_node}.Please waiting later to run this script again to check if backup is success.Exit"
    exit 0
  else
    echo "Port ${firewalld_port} on ${mysql_slave_node} status isn't open.Exit"
    exit 0
  fi
}

function mysql_decompress()
{
  get_slave_info
  local tmux_en=$(ssh ${ssh_option} root@${mysql_master_node} "tmux ls 2>/dev/null|grep mysql_backup|wc -l")
  #if tmux have session mysq_backup,this mean mysql backup stay running,exit script.
  if [[ ${tmux_en} -eq 1 ]];then
    echo "Mysql backup stay running.Exit"
    exit 0
  elif [[ ${tmux_en} -eq 0 ]];then
    #Remove firewalld port
    manager_firewalld_port remove
    local check_log=$(tail -n 1 ${backup_log}|grep "completed OK!"|wc -l)
    if [[ ${check_log} -eq 1 ]];then
      decompress_command="xtrabackup ${xtrabackup_decompress_option} --target-dir=${mysql_slave_volume} 1>${backup_log} 2>&1"
      create_session_tmux ${mysql_slave_node} mysql_decompress "${decompress_command}"
      echo 2 > ${status_pid}
      echo "Start decompress mysql data at ${mysql_slave_node}.Please waiting later to run this script again to check if decompress is success.Exit"
      exit 0
    else
      echo "Script check status is wrong.Exit"
      exit 0
    fi
  else
    echo "Script check status is wrong.Exit"
    exit 0
  fi
}

function mysql_prepare()
{
  get_slave_info
  local tmux_en=$(ssh root@${mysql_slave_node} "tmux ls 2>/dev/null|grep mysql_decompress|wc -l")
  if [[ ${tmux_en} -eq 1 ]];then
    echo "Mysql decompress stay running.Exit"
    exit 0
  elif [[ ${tmux_en} -eq 0 ]];then
    local check_log=$(ssh root@${mysql_slave_node} "tail -n 1 ${backup_log}|grep 'completed OK!'|wc -l")
    if [[ ${check_log} -eq 1 ]];then
      prepare_command="xtrabackup ${xtrabackup_prepare_option} --target-dir=${mysql_slave_volume} 1>${backup_log} 2>&1"
      create_session_tmux ${mysql_slave_node} mysql_prepare "${prepare_command}"
      echo 3 > ${status_pid}
      echo "Start prepare mysql data at ${mysql_slave_node}.Please waiting later to run this script again to check if prepare is success.Exit"
      exit 0
    else
      echo "Script check status is wrong.Exit"
      exit 0
    fi
  else
    echo "Script check status is wrong.Exit"
    exit 0
  fi
}

#Main
#Find out mysql master node
if [[ -z ${mysql_master_node} ]];then
  node_list=($(egrep "node[0-5]" /backup/node-list|cut -d',' -f 1))
  for node_name in ${node_list[@]}
  do
    mysql_master_en=$(ssh ${ssh_option} root@${node_name} "systemctl --type service|grep ${mysql_master_service}|grep active|grep running|wc -l")
    if [[ ${mysql_master_en} -eq 1 ]];then
      mysql_master_node=${node_name}
      break
    fi
  done
fi
master_node_ip=$(grep ${mysql_master_node} /backup/node-list|awk -v FS=',' '{print $2}')
if [[ -z ${mysql_master_node} ]];then
  echo "Can't found any mysql master service on cluster.Exit"
  exit 0
fi
if [[ -z "${mysql_master_volume}" ]];then
  find_mysql_data_volume master
fi
if [[ ! -f ${slave_info} ]];then
  if [[ -z ${mysql_slave_node} ]];then
    find_mysql_slave_node
    echo "${mysql_slave_node}" > ${slave_info}
  fi
  if [[ -z "${mysql_slave_volume}" ]];then
    find_mysql_data_volume slave
    echo "${mysql_slave_volume}" >> ${slave_info}
  fi
fi


#pid file,0 is no start,1 is mysql backup,2 is mysql decompress,3 is mysql prepare
if [[ -f "${status_pid}" && -s "${status_pid}" ]];then
  run_status=$(cat ${status_pid})
  if [[ ${run_status} != [0-3] ]];then
    echo 0 > ${status_pid}
  fi
else
  echo 0 > ${status_pid}
fi
run_status=$(cat ${status_pid})
case ${run_status} in
  0)
    mysql_backup
    ;;
  1)
    mysql_decompress
    ;;
  2)
    mysql_prepare
    ;;
  3)
    reset_mysql_repl
    ;;
  *)
    ;;
esac

3、脚本依赖文件

# cat mysql_slave_init.template 

CHANGE MASTER TO MASTER_HOST="master_ip", MASTER_PORT=3306, MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1;
start slave;

注意：

从库执行sql文本时候，mysql < /tmp/mysql_slave_init.sql , 这里依赖于socket文件的映射

mysql> SHOW VARIABLES LIKE 'socket';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| socket        | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+
1 row in set (0.00 sec)

获取bin_log、log_positon

local mysql_bin_file=$(egrep "binlog_pos" /tmp/xtrabackup_info|awk -v FS="'" '{print $2}')
local mysql_position=$(egrep "binlog_pos" /tmp/xtrabackup_info|awk -v FS="'" '{print $4}')

sed -i -e s/master_ip/192.168.1.231/g -e s/mysql-bin.000001/mysql-bin.000014/g -e s/MASTER_LOG_POS=1/MASTER_LOG_POS=484569852/g /tmp/mysql_slave_init.sql

四、mysql 表数据迁移

1、MySQL中的.cfg、.ibd和.frm文件是与InnoDB存储引擎相关的文件。它们存储了有关数据库表的不同类型的信息：

.frm 文件

.frm文件是MySQL数据库中的表定义文件。对于每个表，MySQL都会在表所在的数据库目录下创建一个与表同名的.frm文件。此文件包含了表结构的元数据，包括列的定义（如数据类型、大小），索引信息，以及其他表属性。无论使用哪种存储引擎，都需要.frm文件来描述表的结构。从MySQL 5.7.31开始，.frm文件逐渐被废除，表结构元数据被保存在数据字典中。

.ibd 文件

  • .ibd 文件是 InnoDB 存储引擎的表空间文件，代表单个表的存储。在 MySQL 中，如果你使用了 InnoDB 的独立表空间模式（通过设置 innodb_file_per_table=ON），那么每个表的数据和索引都会存储在一个独自的 .ibd 文件中。

主要特点

  • 表结构与数据：每个 .ibd 文件包含表的结构信息和数据行的实际存储。这意味着表的所有行以及相关的索引信息都存储在这个文件中。
  • 文件命名：文件名通常是表名加上 .ibd 后缀。例如，如果表名为 my_table，则文件名为 my_table.ibd。
  • 独立性：每个 .ibd 文件是独立的，允许在管理数据库时更方便地备份、迁移或删除表。
  • 元数据：.ibd 文件还包含该表的元数据，如索引信息、数据行的格式等。

ibdata1 文件

  • ibdata1 是 InnoDB 的共享表空间文件（和其他共享文件，例如 undo 日志、元数据等）。在默认情况下（即没有启用 innodb_file_per_table），所有的 InnoDB 表的数据和索引都被存储在这个文件中。

主要特点

  • 全局表空间：ibdata1 是一个全局的表空间，所有 InnoDB 表的元数据、数据、索引等都存储在其中。该文件是共享的，包含了所有表的空间信息。
  • 增量增长：随着数据的插入或更新，ibdata1 文件会根据需要不断增长。它不会自动收缩。
  • 回滚日志：除了存储表数据，ibdata1 还存储 InnoDB 的撤消日志（undo log），这些用于处理事务回滚等操作。
  • 表元数据：它存储了 InnoDB 表的结构信息和元数据，也包括 InnoDB 的内部控制信息。

总结

  • .ibd 文件用于存储每个 InnoDB 表的具体数据和索引，支持独立管理和操作，适用于需要单表管理的场景。
  • ibdata1 文件是全局的共享表空间，包含所有 InnoDB 表的元数据和信息，同时承担了事务处理等功能，适用于不使用独立表空间的设置。

.cfg 文件

对于运行在MySQL 5.6和更高版本上的InnoDB表，.cfg文件用于保存表的压缩信息。当你使用mysqldump工具的—innodb-optimize-keys选项导出表结构为SQL时（或者其他表传输操作，比如Transportable Tablespaces功能），此文件会被创建。.cfg文件中存储了有关如何压缩表的信息，如行格式、键块大小等。这个文件不是用于常规操作，仅在表导出和导入之间传输压缩信息时才会用到。

请注意，从MySQL 8.0 版本开始，MySQL引入了Data Dictionary的概念替代.frm文件，数据字典是一个包含所有服务器对象元数据的事务性数据结构。在MySQL 8.0及以后的版本中，.frm文件和一些相关文件已经不再使用。

2、迁移脚本

#!/bin/bash
src_db_host='node1'
dest_db_host='node3'
dest_file_host='node2'

src_volume_name='mysqlm_data'
dest_volume_name='mysqlm_data'

dest_file_path_root='/dasda/database_backup/'

mysql_user='root'
mysql_password='xxxx'

date=$(date +%Y%m%d)

databases_tables1=(
'pbm_app.a_predict_flowing'
'pbm_app.patient_service_record'
'pbm_app.wait_beds_event'
)

start_time=$(date +'%Y-%m-%d %H:%M:%S')
start_timestamp=$(date +%s)

# 导出表结构并创建表
for d_t in "${databases_tables1[@]}"
do
    IFS="." read -ra database2table <<< "${d_t}"
    db="${database2table[0]}"
    tb="${database2table[1]}"

    # 建库
    mysql -h"${dest_db_host}" -u"${mysql_user}" -p"${mysql_password}" -e "CREATE DATABASE IF NOT EXISTS \`${db}\`;" 2> /dev/null

    # 建表 
    sql=$(mysqldump --column-statistics=0 -u"${mysql_user}" -p"${mysql_password}" -h"${src_db_host}" "${db}" "${tb}" --no-data 2>&1 | tail -n +2)    
    mysql -h "${dest_db_host}" -u"${mysql_user}" -p"${mysql_password}" -e "USE ${db}; ${sql}" 2> /dev/null
    
    # 目标实例上删除.ibd文件
    mysql -h "${dest_db_host}" -u"${mysql_user}" -p"${mysql_password}" -e "USE "${db}"; ALTER TABLE "${tb}" DISCARD TABLESPACE;" 2> /dev/null
    
    # 原实例上导出.ibd 和 .cfg
    mysql -u"${mysql_user}" -h"${src_db_host}" -p"${mysql_password}"  -e "FLUSH TABLES "${db}"."${tb}" FOR EXPORT; SELECT SLEEP(7200);" 2> /dev/null &  
    export_pid=$!

    # 在后台开始同步.ibd 和 .cfg 文件到目标实例
    ssh root@"${src_db_host}" "rsync -aP /healsci/docker/volumes/"${src_volume_name}"/_data/"${db}"/"${tb}".{ibd,cfg} root@"${dest_db_host}":/healsci/docker/volumes/"${dest_volume_name}"/_data/"${db}"/" &
    rsync_pid=$!

    # 确认文件同步完毕并验证stat值
    while true; do
        local_stat_ibd=$(stat -c%s "/healsci/docker/volumes/${dest_volume_name}/_data/${db}/${tb}.ibd" 2> /dev/null)
        remote_stat_ibd=$(ssh  root@"${src_db_host}" "stat -c%s /healsci/docker/volumes/${src_volume_name}/_data/${db}/${tb}.ibd" 2> /dev/null)
        local_stat_cfg=$(stat  -c%s "/healsci/docker/volumes/${dest_volume_name}/_data/${db}/${tb}.cfg" 2> /dev/null)
        remote_stat_cfg=$(ssh root@"${src_db_host}" "stat -c%s /healsci/docker/volumes/${src_volume_name}/_data/${db}/${tb}.cfg" 2> /dev/null)

        if [[ -n "$local_stat_ibd" && -n "$remote_stat_ibd" && "$local_stat_ibd" == "$remote_stat_ibd" && -n "$local_stat_cfg" && -n "$remote_stat_cfg" && "$local_stat_cfg" == "$remote_stat_cfg" ]]; then
            break
        fi
        sleep 5
    done

    # .cfg和.ibd文件一致，可结束MySQL会话
    kill "${export_pid}"
    
    # 确保rsync进程已经完成
    wait "${rsync_pid}" 2> /dev/null
    
    # 解锁原表
    mysql -h"${src_db_host}" -u"${mysql_user}" -p"${mysql_password}" -e 'UNLOCK TABLES;' 2> /dev/null

    # 同步文件到备份节点
    ssh root@"${dest_file_host}" "mkdir -p "${dest_file_path_root}""${date}"/"${db}"/"
    scp /healsci/docker/volumes/"${dest_volume_name}"/_data/"${db}"/"${tb}".*  root@"${dest_file_host}":"${dest_file_path_root}""${date}"/"${db}"/

    # 新实例刷新
    mysql -h"${dest_db_host}" -u"${mysql_user}" -p"${mysql_password}" -e "USE "${db}"; ALTER TABLE "${tb}" IMPORT TABLESPACE;" 2> /dev/null
done

end_time=$(date +'%Y-%m-%d %H:%M:%S')
end_timestamp=$(date +%s)
elapsed_seconds=$((end_timestamp - start_timestamp))
hours=$((elapsed_seconds / 3600))
minutes=$(( (elapsed_seconds / 60) % 60))
seconds=$((elapsed_seconds % 60))
 
echo "Total time taken: $hours hours $minutes minutes $seconds seconds."

关键点：

mysql会话一旦关闭cfg、ibd文件就会自动删除，当文件比较大的时候，就需要考虑保持会话的存在直至数据传输完成

 

posted @ 2024-01-09 22:38  凡人半睁眼  阅读(160)  评论(0)    收藏  举报