迁移mssql数据库到mysql mysql 迁移数据库

转载

ctaxnews 2024-06-14 12:26:06

文章标签 迁移mssql数据库到mysql php ci mysql 文章分类 MySQL 数据库

Auth: Jin
Date: 2014-04-03

前端操作,后续测试对外开放不在本文

准备阶段
操作阶段
后续阶段

需求：
phpcms和anquanzuo在一个mysql实例中
anquanzuo有一张视图为phpcms三张表部分字段的字段和组合
phpcms迁移到master-slave的mmm架构
anquanzuo迁移到ndb架构

一、准备阶段
信息采集和权限授予
(一)phpcms
1.字符集
环境字符集

3304-phpcms>show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

一般情况下应该是utf8
表字符集,注意不是默认字符集的情况
3304-phpcms> show table status where Collation!='utf8_general_ci';
比如latin1_swedish_ci的情况
Article_Article         | InnoDB |      10 | Compact    |   24303 |           3045 |    74022912 |               0 |       409600 |   6291456 |           NULL | 2013-05-10 17:50:03 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |2.存储引擎
3304-phpcms> show table status where Engine!='MyISAM';                                                                 
+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-----------------------------------+                                                              
| Name                  | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment                           |                                                              
+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-----------------------------------+                                                              
| v9_npostchannel       | InnoDB |      10 | Compact    |    13 |           1260 |       16384 |               0 |            0 | 1012924416 |             23 | 2012-07-19 16:58:09 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |                                   |
| v9_npostdata          | InnoDB |      10 | Compact    | 33697 |            451 |    15220736 |               0 |            0 | 1012924416 |          33518 | 2013-11-19 16:55:38 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |                                   |
| v9_npostinfo          | InnoDB |      10 | Compact    |   129 |            127 |       16384 |               0 |            0 | 1012924416 |            154 | 2013-11-19 15:15:35 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |                                   |
| v9_npostinfo_20131119 | InnoDB |      10 | Compact    |   101 |            162 |       16384 |               0 |            0 | 1012924416 |            108 | 2013-11-19 15:15:35 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |                                   |
| v9_router             | InnoDB |      10 | Compact    |    39 |            420 |       16384 |               0 |        16384 | 1012924416 |            361 | 2014-03-20 18:08:23 | NULL        | NULL       | utf8_general_ci   |     NULL |                | è·¯ç”±è§„åˆ™è¡¨                   |
| v9_session            | MEMORY |      10 | Fixed      |    12 |           1096 |     8384880 |       129491304 |       378048 |     129328 |           NULL | NULL                | NULL        | NULL       | utf8_general_ci   |     NULL |                |                                   |
| v9_sso_session        | MEMORY |      10 | Fixed      |     0 |           1096 |           0 |       129491304 |            0 |          0 |           NULL | NULL                | NULL        | NULL       | utf8_general_ci   |     NULL |                |                                   |
| v9_times              | MEMORY |      10 | Fixed      |     6 |            172 |     8384640 |       120236600 |      1118480 |        172 |           NULL | NULL                | NULL        | NULL       | utf8_general_ci   |     NULL |                |                                   |
+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-----------------------------------+
8 rows in set (0.02 sec)

master-slave结构中MyISAM不同步，InnoDB，MEMORY可以同步（后发现线上MyISAM也同步，这个再做测试确认一下）
ndb中MyISAM，InnoDB，MEMORY都不会同步，都必须转换为ndbcluster
确认需要把库中所有MyISAM转换为InnoDB，MEMORY保持不变。

3.索引
mysql-client没有一条语句可以查看所有的索引（我目前不知道），可以写shell全部查看

vim getindex.sh 
MYSQLBIN=/usr/local/mysql/bin/mysql
IP=192.168.201.121
PORT=3304
USER=root
PASSWD=password
DB=phpcms
TABS=`${MYSQLBIN} -h${IP} -P${PORT} -u${USER} -p${PASSWD} ${DB} -e"show tables\G"|grep -v row|awk '{print $2}'`
for TAB in ${TABS};do
    ${MYSQLBIN} -h${IP} -P${PORT} -u${USER} -p${PASSWD} ${DB} -e"show index from ${TAB};"
done

因源数据存储引擎为MyISAM，迁移后要修改为InnoDB

InnoDB 不支持FULLTEXT字段索引
sh getindex.sh |grep FULL
v9_search       1       data    1       data    NULL    33231   NULL    NULL            FULLTEXT
v9_search_keyword       1       data    1       data    NULL    NULL    NULL    NULL            FULLTEXT
这两个表的索引，是迁移时需要修改的。4、存储过程
3304-phpcms>show PROCEDURE status;
Empty set (0.00 sec)
mysqldump -R参数5、Trigger
3304-phpcms>SELECT TRIGGER_NAME FROM information_schema.`TRIGGERS` where TRIGGER_SCHEMA='phpcms';
+----------------+
| TRIGGER_NAME   |
+----------------+
| article_insert |
| article_update |
| article_delete |
+----------------+

和开发确认已经没有使用，无需修改

6、视图
show table status where Comment='view';
视图和普通表一样，导出时没特殊的参数，但需要有了解

（二）anquanzuo
按前面的步骤准备

3304-anquanzuo>show table status where comment='view';
+------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| Name             | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| carseats_article | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
+------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+--

主要要处理的是这个视图

二、操作阶段
（一）phpcms
放入mmm
1、导出

jump 217 
cd /opt/backup
mysqldump -h 192.168.201.121 -P3304 -u dbproxy -ppassword phpcms > /opt/backup/phpcms-mmm_20140331.sql
cp /opt/backup/phpcms-mmm_20140331.sql /opt/backup/phpcms-mmm_20140331_bak.sql2、修改存储引擎
sed -i s/MyISAM/InnoDB/ /opt/backup/phpcms-mmm_20140331.sql3、删除FULLTEXT索引
CREATE TABLE `v9_search` (
  `searchid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `typeid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `adddate` int(10) unsigned NOT NULL,
  `data` text NOT NULL,
  `siteid` smallint(5) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`searchid`),
  KEY `typeid` (`typeid`,`id`),
  KEY `siteid` (`siteid`),--删除，
  FULLTEXT KEY `data` (`data`) --删除部分
) ENGINE=InnoDB AUTO_INCREMENT=33705 DEFAULT CHARSET=utf
CREATE TABLE `v9_search_keyword` (
  `keyword` char(20) NOT NULL,
  `pinyin` char(20) NOT NULL,
  `searchnums` int(10) unsigned NOT NULL,
  `data` char(20) NOT NULL,
  UNIQUE KEY `keyword` (`keyword`),
  UNIQUE KEY `pinyin` (`pinyin`), --删除，
  FULLTEXT KEY `data` (`data`) --删除部分
) ENGINE=InnoDB DEFAULT CHARSET=utf8
注意不能
sed -i /FULLTEX/d /opt/backup/phpcms-mmm_20140331.sql
这样删除还多了一个,号4、导入目标mysql-master
mysql -h 127.0.0.1 -P 3304 -u root -ppassword 
create database phpcms;
use phpcms
source /opt/backup/phpcms-mmm_20140331.sql5.slave确认
jump 2
mysql -h 127.0.0.1 -P 3304 -u root -ppassword phpcms

确认表数量，拉一个表内容看看。

（二）anquanzuo
操作部分见

实际在千兆内网中的远程表查询速度也非常慢，接近一分钟了。

远程表是三个表的组合的视图，没有索引。所以，最终这个库没有到NDB上，还是phpcms一个实例

三、后续阶段
1.修改备份源
2.修改phpmyadmin源

总结：
(一)必须了解迁移的数据库的情况，以及目标环境的特性，比如架构，存储引擎等
1、架构，是单点还是master-slave或者是NDB,是否使用mysql-proxy以及其他技术，了解这个技术的特性和限制
2、迁移前需要了解字符集，存储引擎（和复制索引有关），索引，存储过程，触发器，视图。
（二）明确知道各种变化的处理
1、Charset & Collation
字符集的变化

比如，源库环境是utf8但是表和表数据是latin1
show table status where Collation!='utf8_general_ci';
解决办法：
导出时加原来的字符集而不是默认的utf8
mysqldump --default-character-set=latin1 -h 192.168.201.102 -P3308 -udbproxy -ppasswd vcity > ./vcity.sql
导入时修改client的字符集为原来数据的字符集
mysql -h127.0.0.1 -P3307 -uroot -ppasswd --default-character-set=latin1 vcity < ./vcity.sql
或者
mysql -h127.0.0.1 -P3307 -uroot -ppasswd
create database vcity
SET NAMES latin1
use vcity;
source ./vcity.sql

注：使用source较快

2、Engine 存储引擎的变化
必须新架构为M-S或者NDB,需要支持该架构复制的存储应请

M-S
sed -i s/MyISAM/InnoDB/ dbname.sql
NDB
sed -i s/MyISAM/ndbcluster/ dbname.sql
sed -i s/InnoDB/ndbcluster/ dbname.sql

3、Index 索引
比如MyISAM变化为InnoDB要删除FULLTEXT字段的索引

4、Procedure 存储过程
有存储,mysqldump需要加-R参数

5、Trigger 触发器
目前不知道在dump如何导出

6、View 视图
迁移时和普通表一样，只是要注意试图的数据是当前库还是当前实例的其他库，如果是前实例的其他库，那需要其他库已经存在

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：Android如何让modem变成usb0 怎么设置modem

下一篇：python与js的交互 python与js的区别

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

迁移mssql数据库到mysql mysql 迁移数据库

迁移mssql数据库到mysql mysql 迁移数据库

51CTO博客