MySQL 常见数据拆分办法

在生产环境中，由于业务的增长或者业务的拆分，DBA经常需要拆库操作。那么我们常见的拆库手段有哪些呢？

我这里提供几种解决办法：

1. 使用mysqldump 把表逻辑倒出，然后再source 到其它地方
2. 使用xtrabackup 把表、或者库逻辑备份出，然后再recovery出一个实例
3. 使用MySQL自带的表空间转移（Transport）［这个需要MySQL 5.6.6 以上版本支持］

I: 先来看一下MySQL 的 Transport 表空间的特性吧

比如我们要把  tab_test1 从 db_A 移动到 db_B ，那么我们需要做这么一系列动作：

Step 1: use db_A; show create table tab_test1;（首先，拿到需要的表结构）
Step 2: use db_B; create table tab_test1; alter tale tab_test1 discard tablespace;(discard tablespace 就是把ibd文件删掉，只留下.frm文件)（其次，到目标库删除掉idb文件）
Step 4: use db_A; flush table test122 for export;（把相关表的内存数据写入到数据库）
Step 5: 拷贝 tab_test1.ibd 文件到 db_B/   目录下;（操作系统层执行拷贝ibd文件操作）
Step 6: use db_A; unlock tables; （源库上解锁表）
Step 7: use db_B; alter table tab_test1 import tablespace;（目标库执行表空间导入）

执行完上边几个步骤，我们就完成了 在库之间迁移 某些个表的操作了。

II: 再来看一下 xtrabackup 导出部分表，以达到迁移目的

 Step 1: 我们需要使用xtrabackup 备份部分表

nohup perl /home/ddb/tmp/xtrabackup/bin/innobackupex-1.5.1 --ibbackup=/home/tmp/xtrabackup/bin/xtrabackup_55 --user=root --password=123 --defaults-file=/home/my.cnf  --slave-info  --tables-file=/mnt/back_tables.txt /mnt/backup_1 
 
Step 2:  应用日志，进行恢复操作

nohup perl /home/tmp/percona-xtrabackup-2.1.9-Linux-x86_64/bin/innobackupex-1.5.1 --ibbackup=/home/tmp/percona-xtrabackup-2.1.9-Linux-x86_64/bin/xtrabackup_55 --export --apply-log --defaults-file=/home/node-1-1/backup-my.cnf /home/node-1-1/ > /tmp/r.log 2>&1 &
 
Step 3: 重新起实例，配置复制关系，只复制部分表

my.cnf 文件配置： Replicate_Wild_Do_Table/Replicate_Wild_Ignore_Table
 
执行完上边几个步骤，我们就完成了表的迁移操作，并且新起了个实例，达到了分库的目的。
 

III：最后我们就看一下 mysqldump 的姿势吧

额， 这个就不多啰嗦了吧， mysqldump 做的事情就是 一些逻辑的导出， 倒出来的数据我们是可以用人肉可见的。    这个需要注意的一点就是  --single-transcation 选项
 
关于以上几种办法，我们在实际工作中需要 考虑 服务器的资源、内存容量等， 需要结合实际情况来使用 合适的办法进行拆库操作。

赞 (0)

相关推荐

  • MYSQL数据库数据拆分之分库分表总结

    数据存储演进思路一:单库单表 单库单表是最常见的数据库设计,例如,有一张用户(user)表放在数据库db中,所有的用户都可以在db库中的user表中查到. 数据存储演进思路二:单库多表 随着用户数量的增加,user表的数据量会越来越大,当数据量达到一定程度的时候对user表的查询会渐渐的变慢,从而影响整个DB的性能.如果使用mysql, 还有一个更严重的问题是,当需要添加一列的时候,mysql会锁表,期间所有的读写操作只能等待. 可以通过某种方式将user进行水平的切分,产生两个表结构完全一样的

  • MySQL 常见数据拆分办法

    在生产环境中,由于业务的增长或者业务的拆分,DBA经常需要拆库操作.那么我们常见的拆库手段有哪些呢? 我这里提供几种解决办法: 1. 使用mysqldump 把表逻辑倒出,然后再source 到其它地方 2. 使用xtrabackup 把表.或者库逻辑备份出,然后再recovery出一个实例 3. 使用MySQL自带的表空间转移(Transport)［这个需要MySQL 5.6.6 以上版本支持］ I: 先来看一下MySQL 的 Transport 表空间的特性吧 比如我们要把  tab_tes

  • MySQL 常见的数据表设计误区汇总

    误区一:过多的数据列 MySQL 存储引擎的 API 是按照行缓冲区方式从服务端和存储引擎复制数据.服务端将缓冲区数据解码成数据列.然而,将行缓冲区的格式转换为数据行数据结构的列可能会代价很高.MyISAM 固定使用与服务端匹配的行格式,因此无需转换.然而,MyISAM 的可变行格式以及 InnoDB 的行格式总是需要进行转换.转换的代价依赖于列的数量.如果当数据表的列超过上百列的时候,会引起很高的 CPU 资源消耗--即便是使用到的列很少.曾经看过一篇文章,指的是一个多语言的解决方案,直接简单

  • mysql根据逗号将一行数据拆分成多行数据

    mysql根据逗号将一行数据拆分成多行数据

    目录 分隔效果 命令行解释 涉及函数 分隔效果 -- 分隔前 1,2,3,4 -- 分隔后 1 2 3 4 分隔命令行 SELECT substring_index(substring_index('1,2,3,4',',', b.help_topic_id + 1), ',', -1) result FROM mysql.help_topic b where b.help_topic_id < (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ','

  • PHP+MySQL存储数据常见中文乱码问题小结

    本文实例总结了PHP+MySQL存储数据常见中文乱码问题.分享给大家供大家参考,具体如下: PHP+MySQL出现中文乱码的常见原因: 1. MYSQL数据库的编码是utf8,与PHP网页的编码格式不一致,就会造成MYSQL中的中文乱码. 2. 使用MYSQL中创建表.或者选择字段时设置的类型不是utf8,而网页编码不是utf8,也可能造成MYSQL中文乱码. 3. PHP页面的字符集与数据库的编码不一致. 4. PHP连接MYSQL数据库,操作是设定的语句指定的编码和页面编码,PHP页面编码不

  • mysql误删数据后快速恢复的办法推荐

    目录 第一步:保证mysql已经开启binlog,查看命令: 第二步:进入binlog文件目录,找出日志文件 第三步:切换到mysqlbinlog目录 第四步:通过mysqlbinlog工具命令查看数据库增删改查记录(必须切换到mysqlbinlog目录才有效) 第五步:利用第四步输出的sql语句或者txt文本进行语句过滤,重新插入数据或更新数据 总结 手抖不小心把表里的数据删除或修改错误怎么办?该如何快速恢复呢?遇到这样的问题怎么办?希望下面这篇文章能够帮助到你! 第一步:保证mysql已经开

  • 快速解决mysql导数据时,格式不对、导入慢、丢数据的问题

    如果希望一劳永逸的解决慢的问题,不妨把你的mysql升级到mysql8.0吧,mysql8.0默认的字符集已经从latin1改为utf8mb4,因此现在UTF8的速度要快得多,在特定查询时速度提高了1800%! 但是如果时间等不及,就先用下面的办法快速解决一下. 问题一:格式不对(常出现时间格式不对的情况): 方法1:将excel文件另存为csv,再导入数据库: 方法2:导入的第一步时,默认编码方式是65001(UTF-8),可以尝试选择[10008 (MAC - Simplified Chin

  • 数据库 MySQL中文乱码解决办法总结

    数据库 MySQL中文乱码解决办法总结

    MySQL中文乱码解决办法 前言: MySQL是我们项目中非常常用的数据型数据库.但是因为我们需要在数据库保存中文字符,所以经常遇到数据库乱码情况.下面就来介绍一下如何彻底解决数据库中文乱码情况. 1.中文乱码 1.1.中文乱码 create table user(name varchar(11)); # 创建user表 insert into table user("carl"); # 添加数据 select * from user; insert into user value(&

  • MySQL截取和拆分字符串函数用法示例

    本文实例讲述了MySQL截取和拆分字符串函数用法.分享给大家供大家参考,具体如下: 首先说截取字符串函数: SUBSTRING(commentid,9) 这个很简单,从第9个字符开始截取到最后.SUBSTRING的参数有三个,最后一个是截取的长度,默认是到结尾,负数是倒数第几位. 接着说拆分字符串函数: SUBSTRING_INDEX(commentid, '-', 1) 这个就稍稍复杂一些了,他的意思是以 - 进行拆分字符串,从第一个关键词开始取前面所有的字符串.如果上面的第三个参数修改为 -

  • php查询mysql大量数据造成内存不足的解决方法

    本文实例分析了php查询mysql大量数据造成内存不足的解决方法.分享给大家供大家参考.具体分析如下: 一.问题 使用php查询mysql大数据量的时候,程序尚未执行完毕,跳出警告: Fatal error:  Allowed memory size of 100663296 bytes exhausted (tried to allocate 103 bytes) 错误提示:php所分配到的100M内存被占用完毕. 二.解决方法: 最简单的解决办法是:在执行文件的头部增加: ini_set('

随机推荐