Mysql数据备份与恢复——Docker版
工作上由于需要将旧服务器停用换用新服务器，而旧服务器上存放的mysql数据库，查看磁盘得知大概50G以上，如果采用工具将数据导出成json再到新服务上执行，其过程花费时间太长了，至少得半天…
所以想着能否直接将数据库上的data目录数据直接复制到新的服务器上，然后利用docker重新创建新的容器，这样时间肯定快，在此过程中，由于不了解其原理，踩了很多坑，查找很多博客，大多数都不够详细，或者不符合自己实际情况。特此记录一下!
说明：
- mysql版本为8.0.26
- 部署在centos8上，使用docker部署
- 数据磁盘采用映射方式，映射位置为：/usr/local/mysql，内容：conf、data、mysql-files
一、数据备份
1、总览目录结构
mysql文件夹
|-- conf文件夹
| |-- conf.d文件夹 #停mysql迁移
| |-- my.cnf #停mysql迁移
| `-- my.cnf.fallback #停mysql迁移
|-- data文件夹
| |-- #innodb_temp文件夹 #
| |-- mysql文件夹 #
| |-- performance_schema文件夹 #
| |-- sys文件夹 #
| |-- (其他文件夹，此处就是自己创的数据库，例如：feparks等等) #停mysql迁移
| |-- #ib_16384_0.dblwr #
| |-- #ib_16384_1.dblwr #
| |-- auto.cnf #停mysql覆盖
| |-- ca-key.pem #
| |-- ca.pem #
| |-- client-cert.pem #
| |-- client-key.pem #
| |-- ecology #
| |-- ib_buffer_pool #停msyql覆盖
| |-- ibdata1 #
| |-- ib_logfile0 #停msyql覆盖
| |-- ib_logfile1 #停msyql覆盖
| |-- ib_logfile2 #（看情况是否删除或覆盖）
| |-- ib_logfile3 #
| |-- ibdata1 #停msyql覆盖
| |-- mysql.ibd #停msyql覆盖
| |-- mysql-bin.000001 #
| |-- mysql-bin.000002 #
| |-- mysql-bin.000003 #
| |-- mysql-bin.index #
| |-- private_key.pem #
| |-- public_key.pem #
| |-- server-cert.pem #
| |-- server-key.pem #
| |-- undo_001 #
| |-- undo_002 #
`-- mysql-files #空文件夹即可
2、将data文件夹压缩或者直接导出到本地进行备份，位置：/user/local/mysql/data
3、将conf文件夹压缩或者直接导出到本地进行备份，位置：/user/local/mysql/conf
4、查看my.cnf文件，到时恢复时需要
# Copyright (c) 2017, 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
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql #mysql容器里数据存储位置，这里最好不变
port = 3306
secure-file-priv= ''
default_authentication_plugin= mysql_native_password
character-set-server=utf8mb4server-id = 1
expire_logs_days = 7
binlog_format=ROWmax_connections = 5000
max_connect_errors = 10
table_open_cache = 4096
event_scheduler = ON
skip-name-resolve
#lower_case_table_names = 1 #这里注释掉是因为启动时报错
group_concat_max_len = 102400max_allowed_packet = 100M
binlog_cache_size = 32M
max_heap_table_size = 256M
read_rnd_buffer_size = 64M
sort_buffer_size = 256M
join_buffer_size = 512M
thread_cache_size = 300
log_bin_trust_function_creators=1
key_buffer_size = 256M
read_buffer_size = 32M
read_rnd_buffer_size = 128M
bulk_insert_buffer_size = 512M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES######READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = READ-COMMITTED
tmp_table_size = 512M
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days = 15
long_query_time = 5####### InnoDB
innodb_buffer_pool_size = 512M
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 4
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
#innodb_force_recovery=1[mysqldump]
quick
max_allowed_packet = 102400M[mysql]
no-auto-rehash[myisamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M[mysqlhotcopy]
interactive-timeout[mysqld_safe]
open-files-limit = 65535
log-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/data
mkdir /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
2、将mysql停服务，再操作数据拷贝
docker stop mysql
3、将备份到data数据上传到/usr/local/mysql/data下，⚠️注意：不是所有的文件进行覆盖拷贝的！！！！
覆盖文件如下（去参考前面写的总览目录结构）：
-
ibdata1
-
ib_logfile0
-
ib_logfile1
-
ib_buffer_pool
-
auto.cnf
-
mysql.ibd
注意：ib_logfile2和ib_logfile3文件根据实际情况来，如果旧服务器上的data中有此文件，就需要进行覆盖，否则就进行删除，不然会报错：ib_logfile2 is of diffrent size…
4、完成拷贝后，启动mysql容器
docker start mysql
