MySQL5.6数据迁移至MySQL5.7

一、实验目的

             主要实验mysql5.6数据迁移至5.7环境中，操作系统Cent7.5

             5.6安装包下载：https://downloads.mysql.com/archives/community/

 

 

 

二、安装MySQL5.6

 2.1创建目录 

[root@mysql56 ~]# mkdir /data/mysql/data -p
[root@mysql56 ~]# mkdir /application/ -p
[root@mysql56 ~]# mkdir /data/binlog -p

2.2创建用户

[root@mysql56 ~]# useradd -s /sbin/nologin mysql

2.3上传软件并解压重命名

[root@mysql56 application]# tar -zxvf mysql-5.6.49-linux-glibc2.12-x86_64_\(1\).tar.gz

2.4修改权限

[root@mysql56 ~]# chown -R mysql /data /application
[root@mysql56 application]# mv mysql-5.6.49-linux-glibc2.12-x86_64 mysql

2.5修改环境变量

vi /etc/profile
export PATH=/application/mysql/bin:$PATH
生效
source /etc/profile

2.6清除环境自带的mysql

yum -y remove mariadb mariadb-libs mariadb-server mariadb-devel

2.7安装依赖包

yum -y install make bison-devel ncures-devel libaio perl-Data-Dumper net-tools bison bison-devel gcc gcc-c++ cmake ncurses ncurses-developenssl openssl-devel curses-devel ncurses-devel

2.8初始化

[root@mysql56 ~]# /application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data

2.9准备配置文件

[root@mysql56 ~]# vi /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
server_id=10
user=mysql
log_bin=/data/binlog/mysql-bin
binlog_format=row
socket=/data/mysql.sock
[mysql]
socket=/data/mysql.sock

2.10配置启动文件

[root@mysql56 ~]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld

2.11启动数据库

[root@mysql56 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/data/mysql56.err'.
 SUCCESS! 

2.12修改登陆密码

[root@mysql56 ~]# mysqladmin -uroot -p -S /data/mysql.sock password
Enter password: 
New password: 
Confirm new password: 

2.13创建测试数据

mysql> create database dian;
Query OK, 1 row affected (0.00 sec)
mysql> use dian;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

三、数据迁移

3.1备份5.6数据库

[root@mysql56 ~]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers>/tmp/full56.sql

3.2拷贝5.6的备份文件到5.7

[root@mysql56 ~]# scp /tmp/full56.sql root@192.168.43.4:/tmp

3.3恢复数据到5.7

mysql> source /tmp/full56.sql

恢复完成后一定执行权限刷新！！！！！！！！！！

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

3.4执行升级操作

root@#localhost ~]# mysql_upgrade -uroot -p -S /data/3307/mysql.sock
Enter password: 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.1).
Found 0 sys functions, but expected 22. Re-installing the sys schema.
Upgrading the sys schema.
Checking databases.
binlog.t                                           OK
care2.d4                                           OK
dian.t1                                            OK
employtest.DEPT                                    OK
employtest.EMP                                     OK
gttid.t1                                           OK
he.t1                                              OK
school.course                                      OK
school.dd                                          OK
school.sc                                          OK
school.student                                     OK
school.teacher                                     OK
sys.sys_config                                     OK
worknode.guo                                       OK
worknode.li                                        OK
worknode.rui                                       OK
yu.t1                                              OK
Upgrade process completed successfully.
Checking if update is needed.

最后如果5.6还产生了新数据，则恢复增量日志。