MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

41 | 怎么最快地复制一张表?

林晓斌 2019-02-15
我在上一篇文章最后,给你留下的问题是怎么在两张表中拷贝数据。如果可以控制对源表的扫描行数和加锁范围很小的话,我们简单地使用 insert … select 语句即可实现。
当然,为了避免对源表加读锁,更稳妥的方案是先将数据写到外部文本文件,然后再写回目标表。这时,有两种常用的方法。接下来的内容,我会和你详细展开一下这两种方法。
为了便于说明,我还是先创建一个表 db1.t,并插入 1000 行数据,同时创建一个相同结构的表 db2.t。
create database db1;
use db1;
create table t(id int primary key, a int, b int, index(a))engine=innodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
create database db2;
create table db2.t like db1.t
假设,我们要把 db1.t 里面 a>900 的数据行导出来,插入到 db2.t 中。

mysqldump 方法

一种方法是,使用 mysqldump 命令将数据导出成一组 INSERT 语句。你可以使用下面的命令:
mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
把结果输出到临时文件。
这条命令中,主要参数含义如下:
–single-transaction 的作用是,在导出数据的时候不需要对表 db1.t 加表锁,而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法;
–add-locks 设置为 0,表示在输出的文件结果里,不增加" LOCK TABLES t WRITE;" ;
–no-create-info 的意思是,不需要导出表结构;
–set-gtid-purged=off 表示的是,不输出跟 GTID 相关的信息;
–result-file 指定了输出文件的路径,其中 client 表示生成的文件是在客户端机器上的。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(30)

  • poppy
    关于思考题,我理解是备库的同步线程其实相当于备库的一个客户端,由于备库的会把binlog中t.csv的内容写到/tmp/SQL_LOAD_MB-1-0中,如果load data命令不加'local'表示读取服务端的文件,文件必须在secure_file_priv指定的目录或子目录,此时可能找不到该文件,主备同步执行会失败。而加上local的话,表示读取客户端的文件,既然备份线程都能在该目录下创建临时文件/tmp/SQL_LOAD_MB-1-0,必然也有权限访问,把该文件传给服务端执行。

    作者回复: 👍这是其中一个原因

    2019-02-15
    7
  • 长杰
    课后题答案
    不加“local”,是读取服务端的文件,这个文件必须在 secure_file_priv 指定的目录或子目录下;而备库的apply线程执行时先讲csv内容读出生成tmp目录下的临时文件,这个目录容易受secure_file_priv的影响,如果备库改参数设置为Null或指定的目录,可能导致load操作失败,加local则不受这个影响。

    作者回复: 👍

    2019-02-17
    5
  • ☆appleう
    通知对方更新数据的意思是: 针对事务内的3个操作:插入和更新两个都是本地操作,第三个操作是远程调用,这里远程调用其实是想把本地操作的那两条通知对方(对方:远程调用),让对方把数据更新,这样双方(我和远程调用方)的数据达到一致,如果对方操作失败,事务的前两个操作也会回滚,主要是想保证双方数据的一致性,因为远程调用可能会出现网络延迟超时等因素,极端情况会导致事务10s左右才能处理完毕,想问的是这样耗时的事务会带来哪些影响呢?

    设计的初衷是想这三个操作能原子执行,只要有不成功就可以回滚,保证两方数据的一致性

    耗时长的远程调用不放在事务中执行,会出现我这面数据完成了,而对方那面由于网络等问题,并没有更新,这样两方的数据就出现不一致了



    作者回复: 嗯 了解了

    这种设计我觉得就是会对并发性有比较大的影响。
    一般如果网络状态不好的,会建议把这个更新操作放到消息队列。
    就是说
    1. 先本地提交事务。
    2. 把通知这个动作放到消息队列,失败了可以重试;
    3. 远端接收事件要设置成可重入的,就是即使同一个消息收到两次,也跟收到一次是相同的效果。
    2 和3 配合起来保证最终一致性。

    这种设计我见到得比较多,你评估下是否符合你们业务的需求哈

    2019-02-15
    1
    4
  • undifined
    老师,用物理导入的方式执行 alter table r import tablespace 时 提示ERROR 1812 (HY000): Tablespace is missing for table `db1`.`r`. 此时 db1/ 下面的文件有 db.opt r.cfg r.frm r.ibd t.frm t.ibd;这个该怎么处理

    执行步骤:

    mysql> create table r like t;
    Query OK, 0 rows affected (0.01 sec)

    mysql> alter table r discard tablespace;
    Query OK, 0 rows affected (0.01 sec)

    mysql> flush table t for export;
    Query OK, 0 rows affected (0.00 sec)

    cp t.cfg r.cfg
    cp t.ibd r.ibd

    mysql> unlock tables;
    Query OK, 0 rows affected (0.01 sec)

    mysql> alter table r import tablespace;
    ERROR 1812 (HY000): Tablespace is missing for table `db1`.`r`.

    作者回复: 应该就是评论区其他同学帮忙回复的权限问题了吧?

    2019-02-15
    1
    4
  • ☆appleう
    老师,我想问一个关于事务的问题,一个事务中有3个操作,插入一条数据(本地操作),更新一条数据(本地操作),然后远程调用,通知对方更新上面数据(如果远程调用失败会重试,最多3次,如果遇到网络等问题,远程调用时间会达到5s,极端情况3次会达到15s),那么极端情况事务将长达5-15s,这样会带来什么影响吗?

    作者回复: “通知对方更新上面数据” 是啥概念,如果你这个事务没提交,其他线程也看不到前两个操作的结果的。

    设计上不建议留这么长的事务哈,最好是可以先把事务提交了,再去做耗时的操作。

    2019-02-15
    4
  • lionetes
    mysql> select * from t;
    +----+------+
    | id | name |
    +----+------+
    | 1 | Bob |
    | 2 | Mary |
    | 3 | Jane |
    | 4 | Lisa |
    | 5 | Mary |
    | 6 | Jane |
    | 7 | Lisa |
    +----+------+
    7 rows in set (0.00 sec)

    mysql> create table tt like t;
    Query OK, 0 rows affected (0.03 sec)

    mysql> alter table tt discard tablespace;
    Query OK, 0 rows affected (0.01 sec)

    mysql> flush table t for export;
    Query OK, 0 rows affected (0.01 sec)

    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)

    mysql> alter table tt import tablespace;
    Query OK, 0 rows affected (0.03 sec)

    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | t |
    | t2 |
    | tt |
    +----------------+
    3 rows in set (0.00 sec)

    mysql> select * from t;
    +----+------+
    | id | name |
    +----+------+
    | 1 | Bob |
    | 2 | Mary |
    | 3 | Jane |
    | 4 | Lisa |
    | 5 | Mary |
    | 6 | Jane |
    | 7 | Lisa |
    +----+------+
    7 rows in set (0.00 sec)

    mysql> select * from tt;
    +----+------+
    | id | name |
    +----+------+
    | 1 | Bob |
    | 2 | Mary |
    | 3 | Jane |
    | 4 | Lisa |
    | 5 | Mary |
    | 6 | Jane |
    | 7 | Lisa |
    +----+------+
    7 rows in set (0.00 sec)



    ll 后 查看 tt.cfg 文件没有自动删除 5.7mysql

    -rw-r-----. 1 mysql mysql 380 2月 15 09:51 tt.cfg
    -rw-r-----. 1 mysql mysql 8586 2月 15 09:49 tt.frm
    -rw-r-----. 1 mysql mysql 98304 2月 15 09:51 tt.ibd

    作者回复: 你说得对,👍细致

    import动作 不会自动删除cfg文件,我图改一下

    2019-02-15
    3
  • PengfeiWang
    老师,您好:
            文中“–add-locks 设置为 0,表示在输出的文件结果里,不增加" LOCK TABLES t WRITE;" 是否是笔误,--add-locks应该是在insert语句前后添加锁,我的理解此处应该是--skip-add-locks,不知道是否是这样?

    作者回复: 嗯嗯,命令中写错了,是--add-locks=0,
    效果上跟--skip-add-locks是一样的哈
    👍细致

    2019-02-18
    2
  • 库淘淘
    如果不加local 如secure_file_priv 设置为null 或者路径 可能就不能成功,这样加了之后可以保证执行成功率不受参数secure_file_priv影响。 还有发现物理拷贝文件后,权限所属用户还得改下,不然import tablespace 会报错找不到文件,老师是不是应该补充上去,不然容易踩坑。

    作者回复: 嗯嗯,有同学已经踩了,
    我加个说明进去,多谢提醒

    2019-02-15
    2
  • 信信
    老师好,唯一索引的加next-key lock时,会退化会记录锁。这中间会先拿到间隙锁再释放,还是从一开始就不会获取间隙锁,直接只获取记录锁呢?

    作者回复: 在我们这篇的例子里面,insert duplicate key后导致加锁这个,是不会退化的哦。

    如果是说我们在21篇讲的加锁规则里面, 这个退化的效果就是直接不加间隙锁

    2019-02-15
    2
  • 尘封
    老师mysqldump导出的文件里,单条sql里的value值有什么限制吗默认情况下,假如一个表有几百万,那mysql会分为多少个sql导出?
    问题:因为从库可能没有load的权限,所以local

    作者回复: 好问题,

    会控制单行不会超过参数net_buffer_length,这个参数是可以通过--net_buffer_length 传给mysqldump 工具的

    2019-02-15
    2
  • 小灰灰
    老师您好,想问下如果是迁移5000W左右的一张表,使用导出CSV文件的方式效率高吗?

    作者回复: 导出挺快的,但是导入就不好办了。~

    2019-04-16
    1
  • 老师好,这个/tmp/SQL_LOAD_MB-1-0 是应该在主库上面,还是备库上面?为啥我执行完是在主库上面出现了这个文件呢?

    作者回复: 就是在MySQL的运行进程所在的主机上

    2019-03-14
    1
  • AstonPutting
    老师,mysqlpump能否在平时代替mysqldump的使用?

    作者回复: 我觉得是

    2019-02-22
    1
    1
  • 夜空中最亮的星(华仔)
    学习完老师的课都想做dba了
    2019-02-15
    1
  • 请老师给我看看mysql报错内容是哪里的问题,谢谢老师,一直关注你很久了
    2019-09-26
  • 关于mysqldump方法导出,老师我有问题
    mysqldump -h 127.0.0.1 -P 3306 -u root --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

    报错
    [SQL]mysqldump -h 127.0.0.1 -P 3306 -u root --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

    [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -h 127.0.0.1 -P 3306 -u root --add-locks=0 --no-create-info --single-t' at line 1

    windows里面的navicat操作的时候报这个错,什么原因啊
    2019-09-26
  • philips
    老师您好,如果跨数据库复制大表的话有没有类似物理拷贝的方式可以用吗?我自己试验tablespace的方法失败了。
    2019-08-12
  • godtrue
    老师,好!
    我目前的理解,一个数据库可以包含多张表,每张表的数据在逻辑上是按照B+树的结构存储的。请问物理上,一个数据库的信息是怎么存放在磁盘上的。猜测一个库会对应一个目录,一张表会对应若干文件,用于存储表结构和数据,表数据存储的格式是什么?经过压缩或加密处理嘛?怎么和逻辑结构映射起来?另外,数据量巨大的化会分文件嘛?如果分,有什么分的原则呢?如果让你设计一个数据库,数据存储这块怎么设计呢?
    2019-08-08
  • beyondkmp
    一条 INSERT 语句里面会包含多个 value 对,会加快插入速度,主要是从哪几个方面加快呢?我目前能想到的是:不用每条语句都要重复分析一遍,还有其它原因吗?网络连接应该不算,可以使用长连接,事务应该也不算,多条插入也可以当成一个事务。
    2019-06-03
  • 吴宇晨
    老师,如果我想a表导入b表,但是只要ab表主键可能重复,我只要把不重复的导入b,用什么方法好呢,因为数据量比较大,之前用insert ignore导致线上服务sql超时
    2019-03-26
收起评论
30
返回
顶部