centos mysql数据迁移技巧

CentOS环境下MySQL数据迁移实用技巧

一、迁移前的准备工作

迁移前需完成以下关键步骤，避免数据丢失或配置错误：

1. 备份源数据库：使用mysqldump工具备份源数据库，确保数据完整性。例如，备份单个数据库可使用mysqldump -u [用户名] -p[密码] [数据库名] > backup.sql；备份所有数据库则添加--all-databases选项。建议对备份文件进行压缩（如mysqldump ... | gzip > backup.sql.gz）以节省传输空间。
2. 检查MySQL版本兼容性：源服务器与目标服务器的MySQL版本需兼容（如主从复制要求版本一致或目标版本更高）。可通过mysql --version命令查看版本信息。
3. 安装必要依赖：若使用Percona XtraBackup等物理备份工具，需提前安装依赖包（如perl-module-install.noarch、libaio、libaio-devel），避免安装失败。
4. 规划迁移时间：选择业务低峰期（如夜间）进行迁移，减少对线上业务的影响。

二、常用迁移方法及技巧

1. 逻辑备份与恢复（适用于小规模数据库）

逻辑备份通过SQL语句导出数据，适合结构化数据迁移，步骤如下：

• 备份源数据库：使用mysqldump生成SQL文件（如mysqldump -u root -p mydb > mydb_backup.sql）。
• 传输备份文件：使用scp（加密传输）或rsync（增量传输）将SQL文件传输到目标服务器（如scp mydb_backup.sql user@target_server:/tmp）。
• 恢复数据库：在目标服务器上创建目标数据库（mysql -u root -p -e "CREATE DATABASE mydb;"），然后导入备份文件（mysql -u root -p mydb < /tmp/mydb_backup.sql）。
• 技巧：若备份文件较大，可添加--single-transaction选项（InnoDB表）避免锁表，或使用--compress选项减少传输数据量。

2. 物理备份与恢复（适用于大规模/生产环境）

物理备份直接复制数据文件，速度快且支持增量备份，适合大型数据库，常用Percona XtraBackup工具：

• 安装XtraBackup：下载对应版本的XtraBackup（如CentOS 7使用percona-xtrabackup-2.4），编译安装（cmake ... && make && sudo make install）。
• 备份源数据库：使用innobackupex命令备份（innobackupex --user=mysql --password=123456 /path/to/backup），完成后会生成backup-my.cnf（备份配置）和xtrabackup_logfile（日志）文件。
• 准备备份：在源服务器上执行innobackupex --apply-log /path/to/backup，将备份文件转换为可恢复状态。
• 传输与恢复：将备份目录传输到目标服务器，解压后修改MySQL配置文件（/etc/my.cnf）中的datadir（如指向/new_data/mysql），再执行innobackupex --copy-back /path/to/backup恢复数据。
• 技巧：恢复后需调整数据目录权限（chown -R mysql:mysql /new_data/mysql），避免MySQL无法访问文件。

3. 数据目录迁移（适用于更改存储路径）

若需将MySQL数据从默认路径（/var/lib/mysql）迁移至新路径（如/data/mysql），需修改数据目录配置：

• 停止MySQL服务：使用systemctl stop mysqld确保数据一致性，禁止使用kill命令强制停止。
• 迁移数据文件：创建新目录（mkdir -p /data/mysql），复制旧数据文件（rsync -av /var/lib/mysql/* /data/mysql/），保留文件权限和属性。
• 修改配置文件：编辑/etc/my.cnf，在[mysqld]部分添加datadir=/data/mysql，保存后退出。
• 调整SELinux上下文：若启用了SELinux，需设置正确的上下文（semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"），否则MySQL无法访问新目录。
• 重启服务与验证：重启MySQL（systemctl restart mysqld），通过SHOW VARIABLES LIKE 'datadir';查询数据目录是否生效，登录MySQL检查数据完整性。

4. MySQL主从复制（适用于实时同步）

主从复制可实现源数据库与目标数据库的实时数据同步，适合需要高可用的场景：

• 配置主服务器：修改主服务器的my.cnf（添加server-id=1、log_bin=/var/log/mysql/mysql-bin.log、binlog_do_db=[数据库名]），重启MySQL；创建复制用户（CREATE USER 'repl'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES;）；执行SHOW MASTER STATUS;记录二进制日志文件名（如mysql-bin.000001）和位置（如123）。
• 配置从服务器：修改从服务器的my.cnf（添加server-id=2、relay_log=/var/log/mysql/mysql-relay-bin.log、log_bin=/var/log/mysql/mysql-bin.log、replicate_do_db=[数据库名]），重启MySQL；执行CHANGE MASTER TO MASTER_HOST='主服务器IP', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=123;；启动复制（START SLAVE;）。
• 检查复制状态：执行SHOW SLAVE STATUS\G，确认Slave_IO_Running和Slave_SQL_Running均为Yes，表示复制正常。

三、迁移后的验证与优化

1. 数据完整性检查：登录目标数据库，执行SHOW DATABASES;查看数据库是否存在；进入目标数据库，执行SHOW TABLES;查看表是否完整；对比源数据库与目标数据库的行数（如SELECT COUNT(*) FROM table_name;）。
2. 功能测试：执行插入、更新、删除等操作，验证数据库功能是否正常；若有应用程序连接数据库，需测试应用是否能正常访问目标数据库。
3. 性能优化：根据目标服务器的硬件资源（如CPU、内存、磁盘），调整MySQL配置文件（如innodb_buffer_pool_size设置为物理内存的70%-80%、max_connections根据并发量调整），提升数据库性能。
4. 清理旧数据：确认目标数据库运行稳定（至少3-7天）后，可删除源服务器上的旧数据目录（如rm -rf /var/lib/mysql），释放磁盘空间。