mysqldump原理解析
来源：互联网 发布：c语言 fprintf 编辑：程序博客网 时间：2024/06/05 14:14
mysqldump原理解析
mysqldump过程中，server都执行了哪些操作呢？可以通过strace命令和打开general_log来查看。
1、strace
[mysql26@localhost ~]$ **strace mysqldump -uroot -p'db10$ZTE' --single-transaction --master-data=2 yzs t1 > 1.txt**execve("/home/mysql26/bin/mysqldump", ["mysqldump", "-uroot", "-pdb10$ZTE", "--single-transaction", "--master-data=2", "yzs", "t1"], [/* 19 vars */]) = 0brk(0) = 0x9a11000access("/etc/ld.so.preload", R_OK) = -1 ENOENT (No such file or directory)open("/etc/ld.so.cache", O_RDONLY) = 3fstat64(3, {st_mode=S_IFREG|0644, st_size=37531, ...}) = 0mmap2(NULL, 37531, PROT_READ, MAP_PRIVATE, 3, 0) = 0xb7f4d000close(3) = 0open("/lib/libpthread.so.0", O_RDONLY) = 3read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0P\270\315\0004\0\0\0"..., 512) = 512fstat64(3, {st_mode=S_IFREG|0755, st_size=129716, ...}) = 0mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7f4c000mmap2(0xcd7000, 94692, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0xcd7000mmap2(0xceb000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x13) = 0xceb000mmap2(0xced000, 4580, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0xced000close(3) = 0open("/lib/librt.so.1", O_RDONLY) = 3read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\200(\317\0004\0\0\0"..., 512) = 512fstat64(3, {st_mode=S_IFREG|0755, st_size=44060, ...}) = 0mmap2(0xcf1000, 33324, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0xcf1000mmap2(0xcf8000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x6) = 0xcf8000close(3) = 0open("/lib/libdl.so.2", O_RDONLY) = 3read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0P\212\312\0004\0\0\0"..., 512) = 512fstat64(3, {st_mode=S_IFREG|0755, st_size=16428, ...}) = 0mmap2(0xca8000, 12408, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0xca8000mmap2(0xcaa000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x1) = 0xcaa000close(3) = 0open("/usr/lib/libstdc++.so.6", O_RDONLY) = 3read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0pZ\4\0004\0\0\0"..., 512) = 512fstat64(3, {st_mode=S_IFREG|0755, st_size=5384191, ...}) = 0mmap2(NULL, 1042080, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x560000mmap2(0x653000, 20480, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0xf2) = 0x653000mmap2(0x658000, 26272, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x658000close(3) = 0open("/lib/libm.so.6", O_RDONLY) = 3read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\20\24\313\0004\0\0\0"..., 512) = 512fstat64(3, {st_mode=S_IFREG|0755, st_size=208352, ...}) = 0mmap2(0xcae000, 155760, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0xcae000mmap2(0xcd3000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x24) = 0xcd3000close(3) = 0open("/lib/libgcc_s.so.1", O_RDONLY) = 3read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0`6\263\0004\0\0\0"..., 512) = 512fstat64(3, {st_mode=S_IFREG|0755, st_size=46636, ...}) = 0mmap2(0xb32000, 48196, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0xb32000mmap2(0xb3d000, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0xa) = 0xb3d000close(3) = 0open("/lib/libc.so.6", O_RDONLY) = 3read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\340_\267\0004\0\0\0"..., 512) = 512fstat64(3, {st_mode=S_IFREG|0755, st_size=1611564, ...}) = 0mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7f4b000mmap2(0xb60000, 1332676, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0xb60000mprotect(0xc9f000, 4096, PROT_NONE) = 0mmap2(0xca0000, 12288, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x13f) = 0xca0000mmap2(0xca3000, 9668, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0xca3000close(3) = 0mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7f4a000set_thread_area({entry_number:-1 -> 6, base_addr:0xb7f4a8e0, limit:1048575, seg_32bit:1, contents:0, read_exec_only:0, limit_in_pages:1, seg_not_present:0, useable:1}) = 0mprotect(0xca0000, 8192, PROT_READ) = 0mprotect(0xcd3000, 4096, PROT_READ) = 0mprotect(0x653000, 16384, PROT_READ) = 0mprotect(0xcaa000, 4096, PROT_READ) = 0mprotect(0xcf8000, 4096, PROT_READ) = 0mprotect(0xceb000, 4096, PROT_READ) = 0mprotect(0xb5c000, 4096, PROT_READ) = 0munmap(0xb7f4d000, 37531) = 0set_tid_address(0xb7f4a928) = 7153set_robust_list(0xb7f4a930, 0xc) = 0futex(0xbfab1164, FUTEX_WAKE_PRIVATE, 1) = 0rt_sigaction(SIGRTMIN, {0xcdb3e0, [], SA_SIGINFO}, NULL, 8) = 0rt_sigaction(SIGRT_1, {0xcdb2e0, [], SA_RESTART|SA_SIGINFO}, NULL, 8) = 0rt_sigprocmask(SIG_UNBLOCK, [RTMIN RT_1], NULL, 8) = 0getrlimit(RLIMIT_STACK, {rlim_cur=10240*1024, rlim_max=RLIM_INFINITY}) = 0uname({sys="Linux", node="localhost.localdomain", ...}) = 0brk(0) = 0x9a11000brk(0x9a32000) = 0x9a32000stat64("/etc/my.cnf", 0xbfaad7f4) = -1 ENOENT (No such file or directory)stat64("/etc/mysql/my.cnf", 0xbfaad7f4) = -1 ENOENT (No such file or directory)stat64("/home/mysql26/etc/my.cnf", {st_mode=S_IFREG|0664, st_size=858, ...}) = 0open("/home/mysql26/etc/my.cnf", O_RDONLY|O_LARGEFILE) = 3fstat64(3, {st_mode=S_IFREG|0664, st_size=858, ...}) = 0mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7f56000read(3, "# For advice on how to change se"..., 4096) = 858read(3, "", 4096) = 0close(3) = 0munmap(0xb7f56000, 4096) = 0stat64("/home/mysql26/.my.cnf", 0xbfaad7f4) = -1 ENOENT (No such file or directory)stat64("/home/mysql26/.mylogin.cnf", 0xbfaad7f4) = -1 ENOENT (No such file or directory)write(2, "Warning: Using a password on the"..., 73Warning: Using a password on the command line interface can be insecure.) = 73stat64("/home/mysql26/share/charsets/Index.xml", {st_mode=S_IFREG|0644, st_size=18305, ...}) = 0open("/home/mysql26/share/charsets/Index.xml", O_RDONLY|O_LARGEFILE) = 3read(3, "<?xml version='1.0' encoding=\"ut"..., 18305) = 18305close(3) = 0futex(0x8403e00, FUTEX_WAKE_PRIVATE, 2147483647) = 0open("/etc/nsswitch.conf", O_RDONLY) = 3fstat64(3, {st_mode=S_IFREG|0644, st_size=1696, ...}) = 0mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7f56000read(3, "#\n# /etc/nsswitch.conf\n#\n# An ex"..., 4096) = 1696read(3, "", 4096) = 0close(3) = 0munmap(0xb7f56000, 4096) = 0open("/etc/ld.so.cache", O_RDONLY) = 3fstat64(3, {st_mode=S_IFREG|0644, st_size=37531, ...}) = 0mmap2(NULL, 37531, PROT_READ, MAP_PRIVATE, 3, 0) = 0xb7f4d000close(3) = 0open("/lib/libnss_files.so.2", O_RDONLY) = 3read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\300\30\0\0004\0\0\0"..., 512) = 512fstat64(3, {st_mode=S_IFREG|0755, st_size=46680, ...}) = 0mmap2(NULL, 41616, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x111000mmap2(0x11a000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x8) = 0x11a000close(3) = 0mprotect(0x11a000, 4096, PROT_READ) = 0munmap(0xb7f4d000, 37531) = 0open("/etc/services", O_RDONLY) = 3fcntl64(3, F_GETFD) = 0fcntl64(3, F_SETFD, FD_CLOEXEC) = 0fstat64(3, {st_mode=S_IFREG|0644, st_size=362031, ...}) = 0mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7f56000read(3, "# /etc/services:\n# $Id: services"..., 4096) = 4096read(3, "rotocol\nntp\t\t123/tcp\nntp\t\t123/ud"..., 4096) = 4096read(3, "767/udp\nrsync\t\t873/tcp\t\t\t\t# rsyn"..., 4096) = 4096read(3, "stmanager\ncvspserver\t2401/tcp\t\t\t"..., 4096) = 4096close(3) = 0munmap(0xb7f56000, 4096) = 0rt_sigaction(SIGPIPE, {0x1, [PIPE], SA_RESTART}, {SIG_DFL, [], 0}, 8) = 0socket(PF_FILE, SOCK_STREAM, 0) = 3**connect**(3, {sa_family=AF_FILE, path="/home/mysql26/bin/mysql.sock"...}, 110) = 0mmap2(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7e4a000setsockopt(3, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)setsockopt(3, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0recv(3, "T\0\0\0\n5.6.26-debug-log\0\7\0\0\0001o_k}R"..., 16384, 0) = 88send(3, "\270\0\0\1\5\242>\0\0\0\200\1!\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 188, 0) = 188recv(3, "\7\0\0\2\0\0\0\2\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "\36\0\0\0\3/*!40100 *SET @@SQL_MODE=''* "..., 34, 0) = 34recv(3, "\7\0\0\1\0\0\0\2\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "#\0\0\0\3/*!40103 *SET TIME_ZONE='+00"*..., 39, 0) = 39recv(3, "\7\0\0\1\0\0\0\2\0\0\0", 16384, 0) = 11fstat64(1, {st_mode=S_IFREG|0664, st_size=0, ...}) = 0mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7f56000ioctl(3, FIONREAD, [0]) = 0send(3, "\37\0\0\0\3*FLUSH /*!40101 LOCAL */ TAB*"..., 35, 0) = 35recv(3, "\7\0\0\1\0\0\0\2\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "\34\0\0\0\3*FLUSH TABLES WITH READ LOCK*", 32, 0) = 32recv(3, "\7\0\0\1\0\0\0\2\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "8\0\0\0\3*SET SESSION TRANSACTION ISO*"..., 60, 0) = 60recv(3, "\7\0\0\1\0\0\0\2\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "7\0\0\0\3*START TRANSACTION* /*!40100 "..., 59, 0) = 59recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "!\0\0\0\3*SHOW VARIABLES LIKE 'gtid\*\_"..., 37, 0) = 37recv(3, "\1\0\0\1\2T\0\0\2\3def\22information_schema"..., 16384, 0) = 210ioctl(3, FIONREAD, [0]) = 0send(3, "\23\0\0\0\3*SHOW MASTER STATUS*", 23, 0) = 23recv(3, "\1\0\0\1\5\32\0\0\2\3def\0\0\0\4File\0\f!\0\0\6\0\0\375\1\0"..., 16384, 0) = 238brk(0x9a54000) = 0x9a54000brk(0x9a52000) = 0x9a52000ioctl(3, FIONREAD, [0]) = 0send(3, "\16\0\0\0\3*UNLOCK TABLES*", 18, 0) = 18recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "\371\1\0\0\3*SELECT LOGFILE_GROUP_NAME*, "..., 509, 0) = 509recv(3, "\1\0\0\1\6V\0\0\2\3def\22information_schema"..., 16384, 0) = 473ioctl(3, FIONREAD, [0]) = 0send(3, "X\1\0\0\3*SELECT DISTINCT TABLESPACE_*"..., 348, 0) = 348recv(3, "\1\0\0\1\6P\0\0\2\3def\22information_schema"..., 16384, 0) = 489ioctl(3, FIONREAD, [0]) = 0send(3, "'\0\0\0\3*SHOW VARIABLES LIKE* 'ndbinf"..., 43, 0) = 43recv(3, "\1\0\0\1\2T\0\0\2\3def\22information_schema"..., 16384, 0) = 192ioctl(3, FIONREAD, [0]) = 0send(3, "\4\0\0\0\2yzs", 8, 0) = 8recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "\26\0\0\0\3*SHOW TABLES LIKE* 't1'", 26, 0) = 26recv(3, "\1\0\0\1\1Z\0\0\2\3def\22information_schema"..., 16384, 0) = 124ioctl(3, FIONREAD, [0]) = 0send(3, "\r\0\0\0\3*SAVEPOINT sp*", 17, 0) = 17recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "\34\0\0\0\3*show table status like* 't1'", 32, 0) = 32recv(3, "\1\0\0\1\22B\0\0\2\3def\22information_schema"..., 16384, 0) = 1493ioctl(3, FIONREAD, [0]) = 0send(3, "\34\0\0\0\3*SET SQL_QUOTE_SHOW_CREATE*=1", 32, 0) = 32recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "-\0\0\0\3*SET SESSION character_set_r*"..., 49, 0) = 49recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "\27\0\0\0\3*show create table* `t1`", 27, 0) = 27recv(3, "\1\0\0\1\2\33\0\0\2\3def\0\0\0\5Table\0\f?\0@\0\0\0\375\1"..., 16384, 0) = 264ioctl(3, FIONREAD, [0]) = 0send(3, "+\0\0\0\3*SET SESSION character_set_r*"..., 47, 0) = 47recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "\26\0\0\0\3*show fields from `t1`*", 26, 0) = 26recv(3, "\1\0\0\1\6F\0\0\2\3def\22information_schema"..., 16384, 0) = 532ioctl(3, FIONREAD, [0]) = 0send(3, ",\0\0\0\3*SELECT* /*!40001 SQL_NO_CACH"..., 48, 0) = 48recv(3, "\1\0\0\1\3#\0\0\2\3def\3yzs\2t1\2t1\3id1\3id1\f"..., 16384, 0) = 246write(1, "-- MySQL dump 10.13 Distrib 5.6"..., 1546) = 1546ioctl(3, FIONREAD, [0]) = 0send(3, "-\0\0\0\3*SET SESSION character_set_r*"..., 49, 0) = 49recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "\n\0\0\0\3use `yzs`", 14, 0) = 14recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "\34\0\0\0\3*select* @@collation_database", 32, 0) = 32recv(3, "\1\0\0\1\1*\0\0\2\3def\0\0\0\24@@collation_dat"..., 16384, 0) = 89ioctl(3, FIONREAD, [0]) = 0send(3, "\30\0\0\0\3SHOW TRIGGERS LIKE 't1'", 28, 0) = 28recv(3, "\1\0\0\1\vK\0\0\2\3def\22information_schema"..., 16384, 0) = 952ioctl(3, FIONREAD, [0]) = 0send(3, "+\0\0\0\3SET SESSION character_set_r"..., 47, 0) = 47recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "\31\0\0\0\3*ROLLBACK TO SAVEPOINT sp*", 29, 0) = 29recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11ioctl(3, FIONREAD, [0]) = 0send(3, "\25\0\0\0\3*RELEASE SAVEPOINT sp*", 25, 0) = 25recv(3, "\7\0\0\1\0\0\0\3\0\0\0", 16384, 0) = 11write(1, "/*!40000 *ALTER TABLE `t1` ENABLE*"..., 57) = 57fsync(1) = 0send(3, "\1\0\0\0\1", 5, 0) = 5shutdown(3, 2 /* send and receive */) = 0close(3) = 0munmap(0xb7e4a000, 1048576) = 0time(NULL) = 1511597961open("/etc/localtime", O_RDONLY) = 3fstat64(3, {st_mode=S_IFREG|0644, st_size=2819, ...}) = 0fstat64(3, {st_mode=S_IFREG|0644, st_size=2819, ...}) = 0mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7f55000read(3, "TZif2\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\4\0\0\0\4\0\0\0\0"..., 4096) = 2819close(3) = 0munmap(0xb7f55000, 4096) = 0clock_gettime(CLOCK_REALTIME, {1511597961, 322250554}) = 0write(1, "/*!40103 SET TIME_ZONE=@OLD_TIME"..., 471) = 471exit_group(0) 2、general_log
171125 0:19:21 7 Connect root@localhost on /*修改session级别的sql_mode为空，避免可能有些些sql_mode值会对备份产生影响*/ 7 Query /*!40100 SET @@SQL_MODE='' */ 7 Query /*!40103 SET TIME_ZONE='+00:00' */ /*强制刷新表缓存到磁盘，并关闭表。如果另一个会话已经加上表锁，那么该语句会被阻塞*/ 7 Query FLUSH /*!40101 LOCAL */ TABLES /*对整个实例加全局读锁，如果存在表锁将阻塞该语句*/ 7 Query FLUSH TABLES WITH READ LOCK /*在session级别将隔离级别设置成RR，因为开启一个一致性快照事务，需要在RR隔离级别下*/ 7 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ /*开启一个一致性快照事务*/ 7 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ /*查看是否开启GTID*/ 7 Query SHOW VARIABLES LIKE 'gtid\_mode' /*查看当前数据的binlog pos：对应的选项是--master-data=2*/ 7 Query SHOW MASTER STATUS /*释放全局读锁。因为MVCC的关系，整个事务中select的都是事务开始时的值，这里可以释放锁了*/ 7 Query UNLOCK TABLES 7 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='yzs' AND TABLE_NAME IN ('t1'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME 7 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='yzs' AND TABLE_NAME IN ('t1')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 7 Query SHOW VARIABLES LIKE 'ndbinfo\_version' 7 Init DB yzs 7 Query SHOW TABLES LIKE 't1' /*在一个database开始备份前，设置一个保存点*/ 7 Query SAVEPOINT sp /*查看整个表的状态*/ 7 Query show table status like 't1' /*对每个表的每个字段加上反引号'*/ 7 Query SET SQL_QUOTE_SHOW_CREATE=1 /*表结构的备份都是binary格式，所以需要先修改*/ 7 Query SET SESSION character_set_results = 'binary' /*查看整个表的定义语句*/ 7 Query show create table `t1` /*修改session数据结果返回字符集，备份数据需要使用数据原本的字符集*/ 7 Query SET SESSION character_set_results = 'utf8' /*查看整个表的字段信息*/ 7 Query show fields from `t1` /*查看表数据，结合show fields from t1，字段信息生成insert into语句*/ 7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1` 7 Query SET SESSION character_set_results = 'binary' 7 Query use `yzs` 7 Query select @@collation_database 7 Query SHOW TRIGGERS LIKE 't1' 7 Query SET SESSION character_set_results = 'utf8' /*表备份结束，回滚到保存点sp，以释放select *语句产生的MDL锁，如果不回滚，后续整个备份过程中无法对该表执行DDL操作*/ 7 Query ROLLBACK TO SAVEPOINT sp /*备份完成后，释放该保存点*/ 7 Query RELEASE SAVEPOINT sp 7 Quit 阅读全文
0 0
- mysqldump原理解析
- 根据日志解析mysqldump原理
- Mysqldump原理
- mysqldump原理及实践
- mysqldump的实现原理
- mysqldump原理分析
- mysqldump 命令选项解析
- mysqldump选项解析
- mysqldump备份原理及注意事项
- mysqldump原理和重要参数
- mysql备份工具 ：mysqldump mydumper Xtrabackup 原理
- mysqldump和xtrabackup备份原理实现说明
- mysqldump
- MysqlDump
- mysqldump
- mysqldump
- mysqldump
- mysqldump
- android Log详解
- 关于在两个jsp页面之间传递值的问题
- js延时触发
- [读书总结]-《哪有没时间这回事》
- C#调用DLL复杂函数结构体
- mysqldump原理解析
- 校招面试知识点复习之计算机网络
- git解决无法正常添加个别submodule
- 关于右值引用的粗略研究
- log4j 2 讲解
- 《SVM笔记系列之四》最优化问题的对偶问题
- 2017年Kali Linux更新源
- [LeetCode] Unique Paths II
- pass by value vs pass by reference
