重建mysql从库、表数据迁移

一、背景

1、xtrabackup+ systemd管理

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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

启动文件

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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保存时间、格式等

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

[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服务

?

1	systemctl stop mysql_slave.service

2、重建mysql从库的数据卷

?

1 2 3 4

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

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

3、临时开放防火墙端口

?

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

4、开启接收服务

?

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

5、主库操作

?

1 2 3

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从库节点执行以下命令：

?

1 2 3 4 5

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从库

?

1 2 3 4 5

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主从同步

?

1 2 3

start slave;   show slave status\G;

三、使用脚本重建从库

1、依赖

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

openeuler-22.03-LTS:

?

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

centos-7:

?

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

注：

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

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

?

1 2 3 4 5 6 7 8 9

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、脚本

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342

#!/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、脚本依赖文件

?

1 2 3

# cat mysql_slave_init.template <br> 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文件的映射

?

1 2 3 4 5 6 7

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

?

1 2 3 4

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、迁移脚本

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87

#!/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文件就会自动删除，当文件比较大的时候，就需要考虑保持会话的存在直至数据传输完成