mysql 数据库迁移与备份

mysql 数据库迁移与备份

概述

因为服务器性能老化，需要更换设备，或者原有设备存储空间不足等情况的出现，需要对原有数据库进行数据迁移

方法

1.使用mysqldump的方式导出成sql文件再进行导入（当数据量过大时，此方法运行时间长，且文件过大会导致内存异常而失败，好处是稳妥且运行正常的情况下能够保证整体数据的稳定性和安全性）（本文使用的是此方法）

2.将原有数据库的mysql的datadir文件直接打包压缩成新的文件放入新的服务器的datadir的文件地址下，在my.cnf文件中配置文件地址与之对应即可

方法一步骤：

1.查看老服务器数据的存储位置

show variables like '%dir%';
找到显示中datadir的文件地址（此处是数据库数据的存储地址）

2.停止该数据库连接的所有外部增删读写的相关程序，保证备份新库的数据一致性

3.使用mysqldump导出文件信息

#备份全部数据库
mysqldump -uroot -p --all-databases > 文件名.sql

#备份一个数据库
mysqldump -uroot -p --databases 备份数据库名 > /指定地址/数据库名.sql
 
#备份多个数据库
mysqldump -uroot -p --databases 备份数据库1 备份数据库2 > /指定地址/备份数据库.sql

#备份库中的部分表
mysqldump -uroot -p 指定数据库 表1 表2 ... > /指定地址/表文件.sql

#备份某些数据到并排除某些表
mysqldump -uroot -p 数据库名 --ignore-table=表1 --ignore-table=表2 > /指定地址/文件名.sql

4.找到与原服务器一样的数据库安装包，准备在新服务器下安装数据库安装包

5.开始在新服务器上安装mysql

-、安装前准备
1.卸载MariaDB
安装MySQL的话会和MariaDB的文件冲突，所以需要先卸载掉MariaDB。
1.1 查看是否安装mariadb
rpm -qa|grep mariadb

1.2 卸载
rpm -e --nodeps 文件名

1.3 检查是否卸载干净
rpm -qa|grep mariadb

2.检查依赖
2.1 查看是否安装libaio
rpm -qa|grep libaio
如果没有安装则执行
yum -y install libaio //安装libaio 

2.2 查看是否安装numactl
rpm -qa|grep numactl
如果没有安装则执行
yum -y install numactl //安装numactl 

二、安装MySQL
1.下载安装包 由于安装包已下好，所以不多赘述，wget方式下载请自行百度
注：本文安装包上传到了 /usr/local/ 目录下，使用的安装包为mysql-8.0.28-el7-x86_64.tar.gz

2.进入安装包目录
cd /usr/local/
解压安装包
tar -zxvf mysql-8.0.28-el7-x86_64.tar.gz

3.重命名
将解压后的文件夹重命名为mysql
mv mysql-8.0.28-el7-x86_64/ mysql

4.创建存储数据文件
在重命名后的mysql文件夹中创建data文件夹
mkdir mysql/data

5.设置用户组并赋权
创建用户组
groupadd mysql
创建用户
-r：创建系统用户
-g：指定用户组
useradd -r -g mysql mysql
更改属主和数组
chown -R mysql:mysql /usr/local/mysql/
更改权限
chmod -R 755 /usr/local/mysql/

6.初始化MySQL
进入MySQL的bin目录
cd /usr/local/mysql/bin/
初始化
./mysqld --initialize --user=mysql（指定用户） --datadir=/usr/local/mysql/data（此处的数据地址必须要指定好，放在存储比较大的数据盘上） --basedir=/usr/local/mysql（安装根目录这个必须为安装地址名不要搞错了） --lower_case_table_names=1（为0区分大小写，为1不区分大小写）
初始化完成后会打印一个随机密码，后面会用到，最后一行有个password请复制最后一行信息密码信息到你的本地先进行暂时存储。

7.配置参数文件
vi /etc/my.cnf
配置文件修改为以下内容,也可以根据自己需要设置参数。

[mysqld]
bind-address=0.0.0.0
port=3306
user=mysql
#此处basedir和datadir地址必须和初始化地址一致否则启动会不成功
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
log-error=/usr/local/mysql/data/mysql.err
pid-file=/usr/local/mysql/data/mysql.pid
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true

[client]
port=3306
socket=/usr/local/mysql/data/mysql.sock
host=localhost

[mysqld_safe]
#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

配置后修改 /etc/my.cnf 的权限为644
chmod 644 /etc/my.cnf

8.启动MySQL
/usr/local/mysql/support-files/mysql.server start
如果此启动时报错Linux The server quit without updating PID file
问题很大，这个要慌一下，需要探讨的自己去找原因百度解决，
目前解决最快的方式是将mysql.pid文件在从老数据库里的datadir中复制到新数据库的datadir中，
并将原来的my.cnf文件从老数据库中位置复制到新的数据库中的/etc/文件夹下，
再次启动mysql 如果依旧报刚刚的错误 那么恭喜你，遇到硬茬子了，卸载重装，复盘前7步每个位置的设置

9.设置软连接，并重启MySQL
ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql 
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql 
ln -s /usr/local/mysql/mysql.sock /var/mysql.sock 
然后重启mysql
service mysql restart/systemctl restart mysql

10.给mysql配置环境变量否则只能在/usr/local/mysql/bin目录下才能使用命令访问mysql 需要./mysql -uroot -p
配置好了环境变量，就可以不用每次想要使用mysql时都要到/usr/local/mysql/bin
配置环境变量Vi /etc/profile
在文件后面加上环境变量
export PATH=$PATH:/usr/local/mysql/bin
更新配置文件
source /etc/profile

11.修改密码（高敏操作 看完再改）
使用mysql -uroot -p进行登录操作 此时输入在初始化时生成的初始化密码，两种方法任选其一
（1）alter user 'root'@'localhost' identified by 'password（指定密码）'; 
（2）set password for root@localhost = 'password（指定密码）';   
exit退出后重新进入检查管理员密码是否修改成功，如果你能接受原始密码的复杂度也可以不修改

12.开放远程连接
use mysql; 
update user set user.Host='%' where user.User='root'; 
flush privileges; //刷新权限

13.打开3306端口
（1）如果防火墙没必要可以直接关闭防火墙
systemctl stop firewalld
（2）如果防火墙有必要需要指定端口打开请输入一下命令
netstat -tunlp | grep 3306 //查看3306端口是否已经打开 没打开继续执行以下命令
（tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 8186/mysqld）如果显示此信息 则不需要执行下面两条命令 展示此命令确保mysql正常启动
firewall-cmd --permanent --add-port=3306/tcp 
firewall-cmd --reload    

14.mysql相关命令
启动
service mysql start/systemctl start mysql
停止
service mysql stop/systemctl stop mysql
重启
service mysql restart/systemctl restart mysql
查看状态
service mysql status/systemctl status mysql

15.设置mysql开机自启
将服务文件拷贝到 /etc/init.d下，并重命名为mysqld
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
赋予可执行权限
chmod +x /etc/init.d/mysqld
添加服务
chkconfig --add mysqld
显示服务列表
chkconfig --list
注：如果看到mysqld的服务，并且3,4,5都是on的话则成功，如果是off，则执行
chkconfig --level 345 mysqld on
重启系统
reboot
重启后查看mysql是否开机自启动
ps -ef|grep mysql

16.在老服务器上使用命令访问新服务器上的mysql，或者使用mysql相关工具进行验证，如navicat，sqlyog等
命令验证：mysql -h ip地址：port端口号 -uroot -p 输入密码 看看是能正常访问

6.向新服务器开始导入数据

#将已经备份好的数据传输到新服务器的指定文件夹下
scp /指定地址/文件名.sql root@ip地址:/指定地址/指定文件夹/

#开始导入sql文件
两种方式
（1）mysql -uroot -p < 文件名.sql 建议使用此方法
（2）先进入数据库 （这个方法数据文件过大容易失败）
    mysql -uroot -p Enter password
    source /指定地址/文件名.sql

#如果每个数据库再进行访问不是用root进行访问还需要设置新用户和访问密码，访问权限
CREATE USER '新用户名'@'%' IDENTIFIED WITH mysql_native_password BY '新用户的密码';//
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, 
Event, EXECUTE, GRANT OPTION, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `数据库名`.* TO `指定数据库用户（新用户）`@`%`;

#检查存储过程，触发器，事件，函数（需要在数据导入成功后执行下列语句比对老库和新库这些有没有差异）
检查触发器
select trigger_name from information_schema.triggers where TRIGGER_SCHEMA = '数据库名';
检查事件
select event_name from information_schema.events where event_schema = '数据库名';
检查存储过程和函数
select SPECIFIC_NAME from information_schema.ROUTINES where ROUTINE_SCHEMA = '数据库名';

如果新库中没有则需要在老库中进行导出，执行以下命令

导出触发器（因为触发器可以随着mysqldump导出数据的时候带出一般不需要特地导出触发器）
mysqldump -uroot -p --triggers --no-create-info --no-data 数据库名 > /指定地址/triggers.sql
（这个命令使用--triggers选项来导出触发器，--no-create-info选项用于跳过表结构的导出，--no-data选项用于跳过数据的导出。你可以根据需要调整这些选项。执行命令后，系统会提示你输入数据库用户的密码。输入密码后，触发器将被导出到指定的SQL文件中。）

导出事件
mysqldump -E -ndt 数据库名 -u root -p > /指定地址/events.sql

导出函数和存储过程
mysqldump -R -ndt 数据库名 -u root -p > /指定地址/procedures.sql

#使用scp命令将导出数据传到新服务器上

#在新服务器上先进入数据库，然后使用use命令进入指定数据库，最后使用source命令导入sql文件

7.将之前所有的服务连接到老服务器上的数据库地址修改为新数据库的地址，如果账号密码和以前不一样还需要一并在配置文件上修改，并启动

8.打开页面验证本次迁移是否成功

9.注意如果导入不成功，或者界面打不开，多半的原因是数据库地址没有修改或者是新服务器的3306端口没有打开，请执行上面的第五步中的第13个小步骤打开端口，其它问题请自行处理

方法二步骤

注意：此方法在进行转移时需要将整个datadir文件假进行压缩后传输到新的服务器再进行解压

详细参考大佬：【可爱的小张666】的《Linux MySQL迁移DATA目录到新服务器上(免安装版本MySQL升级到安装版本MySQL)，通过迁移旧版本数据库的DATA目录到新版本数据库的DATA目录中。》

地址：https://blog.csdn.net/xtaypyvi123456/article/details/124693078：