1200字范文 > Mysql数据备份与恢复——Docker版

Mysql数据备份与恢复——Docker版

时间：2023-08-06 21:50:39

工作上由于需要将旧服务器停用换用新服务器，而旧服务器上存放的mysql数据库，查看磁盘得知大概50G以上，如果采用工具将数据导出成json再到新服务上执行，其过程花费时间太长了，至少得半天…

所以想着能否直接将数据库上的data目录数据直接复制到新的服务器上，然后利用docker重新创建新的容器，这样时间肯定快，在此过程中，由于不了解其原理，踩了很多坑，查找很多博客，大多数都不够详细，或者不符合自己实际情况。特此记录一下!

说明：

mysql版本为8.0.26部署在centos8上，使用docker部署数据磁盘采用映射方式，映射位置为：/usr/local/mysql，内容：conf、data、mysql-files

一、数据备份

1、总览目录结构

2、将data文件夹压缩或者直接导出到本地进行备份，位置：/user/local/mysql/data

3、将conf文件夹压缩或者直接导出到本地进行备份，位置：/user/local/mysql/conf

4、查看f文件，到时恢复时需要

# Copyright (c) , Oracle and/or its affiliates. All rights reserved.## This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License as published by# the Free Software Foundation; version 2 of the License.## This program is distributed in the hope that it will be useful,# but WITHOUT ANY WARRANTY; without even the implied warranty of# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the# GNU General Public License for more details.## You should have received a copy of the GNU General Public License# along with this program; if not, write to the Free Software# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA## The MySQL Server configuration file.## For explanations see# /doc/mysql/en/server-system-variables.html[mysqld]pid-file = /var/run/mysqld/mysqld.pidsocket= /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql #mysql容器里数据存储位置，这里最好不变port = 3306secure-file-priv= ''default_authentication_plugin= mysql_native_passwordcharacter-set-server=utf8mb4server-id = 1expire_logs_days = 7binlog_format=ROWmax_connections = 5000max_connect_errors = 10table_open_cache = 4096event_scheduler = ONskip-name-resolve#lower_case_table_names = 1 #这里注释掉是因为启动时报错group_concat_max_len = 102400max_allowed_packet = 100Mbinlog_cache_size = 32Mmax_heap_table_size = 256Mread_rnd_buffer_size = 64Msort_buffer_size = 256Mjoin_buffer_size = 512Mthread_cache_size = 300log_bin_trust_function_creators=1key_buffer_size = 256Mread_buffer_size = 32Mread_rnd_buffer_size = 128Mbulk_insert_buffer_size = 512Msql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES######READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLEtransaction_isolation = READ-COMMITTEDtmp_table_size = 512Mlog-bin=mysql-binbinlog_format=mixedexpire_logs_days = 15long_query_time = 5####### InnoDBinnodb_buffer_pool_size = 512Minnodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 32Minnodb_log_file_size = 1024Minnodb_log_files_in_group = 4innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120#innodb_force_recovery=1[mysqldump]quickmax_allowed_packet = 102400M[mysql]no-auto-rehash[myisamchk]key_buffer = 16Msort_buffer_size = 16Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit = 65535log-error=/var/log/mysql_error.log# Custom config should go here!includedir /etc/mysql/conf.d/

二、数据恢复

1、在新的服务器上利用docker方式按照mysql

创建mysql映射目录，此处：/usr/local/mysql

mkdir /usr/local/mysql

将前面备份的conf目录上传到/usr/local/mysql下在/usr/local/mysql下创建data目录和mysql-files目录

mkdir /usr/local/mysql/datamkdir /usr/local/mysql/mysql-files

执行安装mysql命令

docker run -di --name mysql --network=host -e MYSQL_ROOT_PASSWORD='^`(/Das@KK321(' -e TZ=Asia/Shanghai -v /usr/local/mysql/data:/var/lib/mysql -v /usr/local/mysql/conf:/etc/mysql -v /usr/local/mysql/mysql-files:/var/lib/mysql-files mysql:8.0.26

查看启动状况，运行成功看到3306端口启动后，进行下一步

docker logs -f mysql