[臬榜* @. Z/ {) [6 M- w$ k- J7 {2 k" J
mysqldump -u userid -e -p db_Name > xxxxx.sql6 S* q$ X$ o5 @) Q* K+ w
臬箔table:mysqldump -p -u userid dbname tablename > 52avtv_dzx20.201506242307.sql1 u' a. o& a. C8 Q- H5 q( D
臬箸鞈摨: mysqldump -p -u userid --all-databases > all-database.sql + @, K# z5 h; D+ B- C! t% q
雿,臬啁桃鞈摨怎雿輻刻撣唾撖蝣潭憿,隞仿舀鞈摨怠交,雿輻刻撣唾銋亙臬箸頛摰) r) T* W9 [6 _" r) g
[芸臬箇瑽] : https://iter01.com/206843.html
% f) k; V/ X; H& X; u. y0 amysqldump--opt-d鞈摨怠-uroot-p>xxx.sql
2 d; T! Q1 |4 E4 @8 C. Q! Q[芸臬箄]$ G y, v; H7 C$ B
mysqldump-t鞈摨怠-uroot-p>xxx.sql
* E6 Z. ]$ ~! [: `8 t
" J: u7 Q3 ^4 U, X% S1 h' w; S/ M瘜冽: 嗡誑銝餈唳隞文隞賣蝬脩箇 "502 bad gateway",敶梢踹迸ginx,芾 --single-transaction --quick 撠勗末鈭,憒銝:
" d' Y+ L/ {' V. J& y0 o @3 t, umysqldump -u userid -e -p --single-transaction --quick db_Name > xxxxx.sql% b/ v. u. {, q y3 y! J
憿:mysqldump: Got error: 1017: Can't find file: 'pre_forum_rsscache' (errno: 2) when using LOCK TABLES
" D; t7 Q; C: y- U3 O- q! J1 XSol: 芾皂ysqldump銝--lock-tables=false撠勗臭誑閫瘙箏憿 H4 r# F+ _" Z
亥箇: mysqldump: Couldn't execute 'show create table `pre_forum_rsscache`': Can't find file: 'pre_forum_rsscache' (errno: 2) (1017),隞叼hpmyadmin餃交亦餌pre_forum_rsscache 雿輻其葉ls /var/lib/mysql/52avtv_dz/pre_forum_rsscache* 潛曉芣 pre_forum_rsscache.frm 1瑼獢 ,甇撣豢閰脰3瑼:9 j9 }0 Z% F+ j! p/ |
-rw-rw---- 1 mysql mysql 8852 Jun 3 16:05 pre_forum_rsscache.frm, y% G1 P1 u4 E( j
-rw-rw---- 1 mysql mysql 0 Jun 3 16:05 pre_forum_rsscache.MYD& T4 n2 I( P- c2 |
-rw-rw---- 1 mysql mysql 1024 Jun 3 16:05 pre_forum_rsscache.MYI
( C1 {3 K6 u( N5 S7 h$ ]Sol:
3 a# W/ }: V" O9 O私hpmyadmin ==SQL 銝隞:2 J t3 Z8 C' u0 s. J
- DROP TABLE IF EXISTS pre_forum_rsscache;
- CREATE TABLE pre_forum_rsscache (
- lastupdate int(10) unsigned NOT NULL DEFAULT '0',
- fid mediumint(8) unsigned NOT NULL DEFAULT '0',
- tid mediumint(8) unsigned NOT NULL DEFAULT '0',
- dateline int(10) unsigned NOT NULL DEFAULT '0',
- forum char(50) NOT NULL DEFAULT '',
- author char(15) NOT NULL DEFAULT '',
- `subject` char(80) NOT NULL DEFAULT '',
- description char(255) NOT NULL DEFAULT '',
- guidetype char(10) NOT NULL DEFAULT '',
- UNIQUE KEY tid (tid),
- KEY fid (fid,dateline)
- ) ENGINE=MYISAM DEFAULT CHARSET=utf8
銴鋆賭誨蝣
/ n0 x: G' p6 s隞賢停甇撣訾. y( ^& {( c* j- b
憿: 嗡蝙皂ariadb 5.5.68 隞賣箇頡rror:mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'pre\_portal\_topic\_pic'': Can't read dir of './db_name/' (errno: 24) (1018), B2 G0 z9 u; B5 j% q: {: W( F5 y/ Q
Sol: open_files_limit(default=962) 憭芸,寧4096,望澆撅祆廝ead-only,/etc/my.cnf
. E3 S1 h) g. L& \0 ]# ~ f) i, s/ m嗅 service mariadb restart
4 u ^6 U4 V' n0 k0 I3 M, D% a( u# r# N+ p2 C4 [
[璇隞嗅臬榜
d% n# n$ X/ F0 H% K! f2 N9 fmysqldump -u撣唾 -p撖蝣 -h銝餅 鞈摨 鞈銵 -w "sql璇隞" > 箄撓頝臬瑼獢
5 _0 `1 C5 H0 h" r V5 L& u; ?# q靘:
) S7 K, D% P8 L" }mysqldump -uroot -p123456 -hlocalhost -e AREA_UTF8 city -w "c_id<10 " > /home/web/a.txt7 i( j6 x/ f5 B
% ^" v" _2 P1 X--no-create-info嚗-t
) x: @, x' u5 t" W芸箄嚗銝瘛餃 CREATE TABLE 隤伐憒撠箸澆搴QL隤伐芣insert into典8 j2 \4 {6 ?& I0 K- n2 E6 h
--no-data嚗-d$ w# s6 s: N' O9 a$ C/ H
銝撠箔遙雿鞈嚗芸箄摨怎瑽 o8 l* B# U6 K5 Y
--quick嚗-q $ t# L, H3 p o- u6 A
典箏之鞈敺剁撘瑕嗅 MySQL Server 亥岷敺閮湔亥撓綽銝臬敺閮敺摮刻園銝准
0 I! ?: L+ G9 y i* {, i' ]3 P V! ?2 j
; R: @4 H) B, P$ D, S: Z[臬包
5 E7 Z) g4 O- S5 P& f/ ]4 Pmysql -u userid -p [-h localhost] db_Name < xxxxx.sql
; j. W+ A' v: V8 [- L+ E. A臬交鞈摨: mysql -u userid -p < all-database.sql
$ E/ I# h+ g, a6 d0 x% o臬乩table :
8 S% x# h+ o* K: h: ~ X& f/ G/ Ymysql -u userid -p -D dbname < cc5278_dzx20.ip2c.201506242307.sql9 K9 u$ t: j7 t' h: T% q: f$ ?3 d
P.S憒臬交憭芸之憒7GB,臬交獢銋瑁銝隞:
0 `, w8 |# s+ J) {7 e% K% q7 `mysql -uroot -p -e "set global net_buffer_length=1000000; set global max_allowed_packet=1000000000;"& G/ h0 H# `. X0 _! V ^# o1 h
! z( R' }. }, m) N: `, v8 G& k& j/ j
[臬箏蝡喳臬亙啣虫唬蜓璈]9 i, k3 J f1 A* C5 h; Y- m( x* P
mysqldump -u [SOURCE_userid] -h [SOURCE_IP] -e -q --password=[SOURCE_PASSWORD] SOURCE_db_Name | mysql -u [DISTANCE_userid] --password=[DISTANCE_PASSWORD] -h [DISTANCE_IP] DISTANCE_db_Name 5 ^8 d y4 K ~! s" T# s0 N+ V
[閮剖臬乩(批2Mb) by phpmyadmin]: H5 r" x. j4 Q. u% c7 n
http://www.av4u.info/forum.php?m ... e=1&extra=#pid203483 g1 u' n) |& i T" m+ a4 c% S8 d* N
# I) u5 F# ]1 p6 }, r5 A8 H5 @0 i% d& |* [9 `! F
9 E' [3 a" U( @$ E* g+ W+ A+ T9 z! H" V1 N* H
. N+ g4 c- ]; W% ]1 L% t' Z
: ?& c. |/ A. f3 J/ g7 D" |4 L! y/ \) E8 w/ o
* r L, J" O$ i- `- o3 B
; n5 _& v# W; L* Z# S& n6 _4 ~( `/ ~& r1 H( j5 I- O
: i+ e; S9 y( T8 ~! W8 o% K4 _% m5 r' x3 v
- M# a$ l4 P+ U% b6 v) w2 m8 c
, W5 z/ x3 ^% ?6 I: i) K |
|