前置条件：

1.新的mysql已经部署好并启动，集群主机可以正常访问该服务。

2.给集群提供访问mysql的用户及权限。

例如：

grant all privileges on *.* to 'root'@'%' identified by 'mysql!2017';

grant all privileges on *.* to 'root'@'localhost' identified by 'mysql!2017';

3.迁移期间保持旧的mysql正常运行。

注：如果新旧mysql版本存在差异需要替换对应的jdbc连接jar。

服务迁移：

ambari服务数据迁移

停止ambari服务：

ambari-server  stop

将旧mysql数据导入到新mysql：

导出：mysqldump  -hhost-10-1-236-52    -uroot -p123456  ambari > backupfile_ambari.sql

导入：mysql -hbhost-10-1-236-51 -uroot -pmysql!2017  ambari < backupfile_ambari.sql

修改ambari-server数据库配置：

[root@host-10-1-236-53 ~]# ambari-server setup

Using python  /usr/bin/python

Setup ambari-server

Checking SELinux...

SELinux status is 'disabled'

Customize user account for ambari-server daemon [y/n] (n)? n

Adjusting ambari-server permissions and ownership...

Checking firewall status...

Checking JDK...

Do you want to change Oracle JDK [y/n] (n)? n

Completing setup...

Configuring database...

Enter advanced database configuration [y/n] (n)? y

Configuring database...

==============================================================================

Choose one of the following options:

[1] - PostgreSQL (Embedded)

[2] - Oracle

[3] - MySQL

[4] - PostgreSQL

[5] - Microsoft SQL Server (Tech Preview)

[6] - SQL Anywhere

==============================================================================

Enter choice (3): 3

Hostname (host-10-1-236-52): host-10-1-236-51（新mysql对应主机名/ip）

Port (3306): 3306

Database name (ambari): ambari

Username (root): root

Enter Database Password (123456):

Configuring ambari database...

Copying JDBC drivers to server resources...

Configuring remote database connection properties...

WARNING: Before starting Ambari Server, you must run the following DDL against the database to create the schema: /var/lib/ambari-server/resources/Ambari-DDL-MySQL-CREATE.sql

Proceed with configuring remote database connection properties [y/n] (y)? y

Extracting system views...

.......

Adjusting ambari-server permissions and ownership...

Ambari Server 'setup' completed successfully.

启动ambari-server服务：

ambari-server start

查看日志无报错，访问页面正常，则迁移成功。

Hive服务数据迁移

通过ambari停止hive服务。

修改hive对应mysql配置指向新的mysql，并保存。

将旧mysql数据导入到新mysql：

导出：mysqldump  -hhost-10-1-236-52    -uroot -p123456  hive > backupfile_hive.sql

导入：mysql -hbhost-10-1-236-51 -uroot -pmysql!2017  hive < backupfile_hive.sql

通过ambari启动hive服务，检查hive访问是否正常，表是否都完整。

Ranger服务数据迁移

通过ambari停止ranger：

通过ambari修改ranger对应mysql配置指向新的mysql，并保存。

将旧mysql数据导入到新mysql：

导出：mysqldump  -hhost-10-1-236-52    -uroot -proot  ranger > backupfile_ranger.sql

导出：mysqldump  -hhost-10-1-236-52    -uroot -proot  ranger_audit > backupfile_ranger_audit.sql

导入：mysql -hbhost-10-1-236-51 -uroot -pmysql!2017  ranger < backupfile_ranger.sql

导入：mysql -hhost-10-1-236-51 -uroot -pmysql!2017  ranger_audit < backupfile_ranger_audit.sql

通过ambari-server启动ranger，查看如果界面都正常，则迁移成功。

注：如果新旧mysql版本有变化需要修改替换集群主机mysql-connector-java.jar

 mv  mysql-connector-java-5.1.42.jar      mysql-connector-java.jar

然后把新mysql-connector-java.jar同步到以下目录中：

/usr/share/java/mysql-connector-java.jar

/usr/hdp/2.4.0.0-169/ranger-admin/ews/lib/mysql-connector-java.jar

/usr/hdp/2.4.0.0-169/hive/lib/mysql-connector-java.jar

/var/lib/ambari-server/resources/mysql-connector-java.jar