MySQL数据备份

Mysql基本操作

连接mysql/退出

?

1	mysql [-h主机地址] -u用户名 －p[用户密码]      / exit

显示数据库列表

?

1	show databases;

显示库中的数据表

?

1 2 3

use mysql； //打开库   show tables;

显示数据表的结构

?

1	describe 表名; --简写：desc 表名

备份

• 备份指定数据库(包含建表语句和插入数据的insert语句)

?

1	mysqldump [-h主机地址] [-P端口] -uroot -pPassword [database name] > database.dump

• 跨主机备份(可以将host1上的sourceDb复制到host2的targetDb，前提是host2主机上已经创建targetDb数据库)　　

?

1	mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb

恢复

• 从备份文件恢复数据库

?

1	mysql [-h主机地址] [-P端口] -uroot -pPassword [database name] < [backup file name]

• 登录到mysqlShell命令行方式

?

1 2 3

user db;   source *.dump;

参数

--opt/建表语句包含drop table if exists tableName；insert之前包含一个锁表语句lock tables tableName write，insert之后包含unlock tables

--databases 【database name...】/指定要备份多个数据库

--all-databases/备份所有数据库

--no-data/只备份表结构

-C/ 指示主机间的数据传输使用数据压缩

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485

1）、--all-databases  , -A   导出全部数据库。   mysqldump -uroot -p --all-databases   2）、--all-tablespaces  , -Y   导出全部表空间。   mysqldump -uroot -p --all-databases --all-tablespaces   3）、--no-tablespaces  , -y   不导出任何表空间信息。   mysqldump -uroot -p --all-databases --no-tablespaces   4）、--add-drop-database   每个数据库创建之前添加drop数据库语句。   mysqldump -uroot -p --all-databases --add-drop-database   5）、--add-drop-table   每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)   mysqldump -uroot -p --all-databases  (默认添加drop语句)   mysqldump -uroot -p --all-databases –skip-add-drop-table  (取消drop语句)   6）、--add-locks   在每个表导出之前增加LOCK TABLES并且之后UNLOCK  TABLE。(默认为打开状态，使用--skip-add-locks取消选项)   mysqldump -uroot -p --all-databases  (默认添加LOCK语句)   mysqldump -uroot -p --all-databases –skip-add-locks   (取消LOCK语句)   7）、--allow-keywords   允许创建是关键词的列名字。这由表名前缀于每个列名做到。   mysqldump -uroot -p --all-databases --allow-keywords   8）、--apply-slave-statements   在'CHANGE MASTER'前添加'STOP SLAVE'，并且在导出的最后添加'START SLAVE'。   mysqldump -uroot -p --all-databases --apply-slave-statements   9）、-character-sets-dir   字符集文件的目录   mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets   10）、--comments   附加注释信息。默认为打开，可以用--skip-comments取消   mysqldump -uroot -p --all-databases  (默认记录注释)   mysqldump -uroot -p --all-databases --skip-comments   (取消注释)   11）、--compatible   导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，   要使用几个值，用逗号将它们隔开。它并不保证能完全兼容，而是尽量兼容。   mysqldump -uroot -p --all-databases --compatible=ansi   12）、--compact   导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys   mysqldump -uroot -p --all-databases --compact   13）、--complete-insert,  -c   使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。   mysqldump -uroot -p --all-databases --complete-insert   14）、--compress, -C   在客户端和服务器之间启用压缩传递所有信息   mysqldump -uroot -p --all-databases --compress   15）、--create-options,  -a   在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)   mysqldump -uroot -p --all-databases   16）、--databases,  -B   导出几个数据库。参数后面所有名字参量都被看作数据库名。   mysqldump -uroot -p --databases test mysql   17）、--debug   输出debug信息，用于调试。默认值为：d:t,/tmp/mysqldump.trace   mysqldump -uroot -p --all-databases --debug   mysqldump -uroot -p --all-databases --debug=” d:t,/tmp/debug.trace”   18）、--debug-check   检查内存和打开文件使用说明并退出。   mysqldump -uroot -p --all-databases --debug-check   19）、--debug-info   输出调试信息并退出   mysqldump -uroot -p --all-databases --debug-info   20）、--default-character-set   设置默认字符集，默认值为utf8   mysqldump -uroot -p --all-databases --default-character-set=utf8   21）、--delayed-insert   采用延时插入方式（INSERT DELAYED）导出数据   mysqldump -uroot -p --all-databases --delayed-insert   22）、--delete-master-logs   master备份后删除日志. 这个参数将自动激活--master-data。   mysqldump -uroot -p --all-databases --delete-master-logs   23）、--disable-keys   对于每个表，用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地导入dump出来的文件，因为它是在插入所有行后创建索引的。该选项只适合MyISAM表，默认为打开状态。   mysqldump -uroot -p --all-databases   24）、--dump-slave   该选项将主的binlog位置和文件名追加到导出数据的文件中(show slave status)。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，会在change前加上注释。该选项将会打开--lock-all-tables，除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0。   mysqldump -uroot -p --all-databases --dump-slave=1   mysqldump -uroot -p --all-databases --dump-slave=2   25）、--master-data   该选项将当前服务器的binlog的位置和文件名追加到输出文件中(show master status)。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE  MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。   mysqldump -uroot -p --host=localhost --all-databases --master-data=1;   mysqldump -uroot -p --host=localhost --all-databases --master-data=2;   26）、--events, -E   导出事件。   mysqldump -uroot -p --all-databases --events   27）、--extended-insert,  -e   使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用--skip-extended-insert取消选项。   mysqldump -uroot -p --all-databases   mysqldump -uroot -p --all-databases--skip-extended-insert   (取消选项)   28）、--fields-terminated-by   导出文件中忽略给定字段。与--tab选项一起使用，不能用于--databases和--all-databases选项   mysqldump -uroot -p test test --tab=”/home/mysql” --fields-terminated-by=”#”   29）、--fields-enclosed-by   输出文件中的各个字段用给定字符包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项   mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#”   30）、--fields-optionally-enclosed-by   输出文件中的各个字段用给定字符选择性包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项   mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#” --fields-optionally-enclosed-by  =”#”   31）、--fields-escaped-by   输出文件中的各个字段忽略给定字符。与--tab选项一起使用，不能用于--databases和--all-databases选项   mysqldump -uroot -p mysql user --tab=”/home/mysql” --fields-escaped-by=”#”   32）、--flush-logs   开始导出之前刷新日志。   请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。   mysqldump -uroot -p --all-databases --flush-logs   33）、--flush-privileges   在导出mysql数据库之后，发出一条FLUSH  PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。   mysqldump -uroot -p --all-databases --flush-privileges   34）、--force   在导出过程中忽略出现的SQL错误。   mysqldump -uroot -p --all-databases --force   36）、--help   显示帮助信息并退出。   mysqldump --help   37）、--hex-blob   使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB。   mysqldump -uroot -p --all-databases --hex-blob   38）、--host, -h   需要导出的主机信息   mysqldump -uroot -p --host=localhost --all-databases   39）、--ignore-table   不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……   mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user   40）、--include-master-host-port   在--dump-slave产生的'CHANGE  MASTER TO..'语句中增加'MASTER_HOST=<host>，MASTER_PORT=<port>'    mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port   41）、--insert-ignore   在插入行时使用INSERT IGNORE语句.   mysqldump -uroot -p --host=localhost --all-databases --insert-ignore   42）、--lines-terminated-by   输出文件的每行用给定字符串划分。与--tab选项一起使用，不能用于--databases和--all-databases选项。   mysqldump -uroot -p --host=localhost test test --tab=”/tmp/mysql”  --lines-terminated-by=”##”   43）、--lock-all-tables,  -x   提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。   mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables   44）、--lock-tables,  -l   开始导出前，锁定所有表。用READ  LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。   请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。   mysqldump -uroot -p --host=localhost --all-databases --lock-tables   45）、--log-error   附加警告和错误信息到给定文件   mysqldump -uroot -p --host=localhost --all-databases  --log-error=/tmp/mysqldump_error_log.err   46）、--max_allowed_packet   服务器发送和接受的最大包长度。   mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240   47）、--net_buffer_length   TCP/IP和socket连接的缓存大小。   mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024   48）、--no-autocommit   使用autocommit/commit 语句包裹表。   mysqldump -uroot -p --host=localhost --all-databases --no-autocommit   49）、--no-create-db,  -n   只导出数据，而不添加CREATE DATABASE 语句。   mysqldump -uroot -p --host=localhost --all-databases --no-create-db   50）、--no-create-info,  -t   只导出数据，而不添加CREATE TABLE 语句。   mysqldump -uroot -p --host=localhost --all-databases --no-create-info   51）、--no-data, -d   不导出任何数据，只导出数据库表结构。   mysqldump -uroot -p --host=localhost --all-databases --no-data   52）、--no-set-names,  -N   等同于--skip-set-charset   mysqldump -uroot -p --host=localhost --all-databases --no-set-names   53）、--opt   等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert,--lock-tables,  --set-charset,--disable-keys 该选项默认开启,  可以用--skip-opt禁用.   mysqldump -uroot -p --host=localhost --all-databases --opt   54）、--order-by-primary   如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。   mysqldump -uroot -p --host=localhost --all-databases --order-by-primary   55）、--password, -p   连接数据库密码   56）、--pipe(windows系统可用)   使用命名管道连接mysql   mysqldump -uroot -p --host=localhost --all-databases --pipe   57）、--port, -P   连接数据库端口号   58）、--protocol   使用的连接协议，包括：tcp, socket, pipe, memory.   mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp   59）、--quick, -q   不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项。   mysqldump -uroot -p --host=localhost --all-databases   mysqldump -uroot -p --host=localhost --all-databases --skip-quick   60）、--quote-names,-Q   使用（`）引起表和列名。默认为打开状态，使用--skip-quote-names取消该选项。   mysqldump -uroot -p --host=localhost --all-databases   mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names   61）--replace   使用REPLACE INTO 取代INSERT INTO.   mysqldump -uroot -p --host=localhost --all-databases --replace   62）、--result-file,  -r   直接输出到指定文件中。该选项应该用在使用回车换行对（\\r\\n）换行的系统上（例如：DOS，Windows）。该选项确保只有一行被使用。   mysqldump -uroot -p --host=localhost --all-databases--result-file=/tmp/mysqldump_result_file.txt   63）、--routines, -R   导出存储过程以及自定义函数。   mysqldump -uroot -p --host=localhost --all-databases --routines   64）、--set-charset   添加'SET NAMES default_character_set'到输出文件。默认为打开状态，使用--skip-set-charset关闭选项。   mysqldump -uroot -p --host=localhost --all-databases   mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset   65）、--single-transaction   该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK  TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。   mysqldump -uroot -p --host=localhost --all-databases --single-transaction   66）、--dump-date   将导出时间添加到输出文件中。默认为打开状态，使用--skip-dump-date关闭选项。   mysqldump -uroot -p --host=localhost --all-databases   mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date   67）、--skip-opt   禁用–opt选项.   mysqldump -uroot -p --host=localhost --all-databases --skip-opt   68）、--socket,-S   指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock   mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock   69）、--tab,-T   为每个表在给定路径创建tab分割的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上。注意使用--tab不能指定--databases参数   mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"   70)、--tables   覆盖--databases (-B)参数，指定需要导出的表名，在后面的版本会使用table取代tables。   mysqldump -uroot -p --host=localhost --databases test --tables test   71）、--triggers   导出触发器。该选项默认启用，用--skip-triggers禁用它。   mysqldump -uroot -p --host=localhost --all-databases --triggers   72）、--tz-utc   在导出顶部设置时区TIME_ZONE='+00:00' ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。   mysqldump -uroot -p --host=localhost --all-databases --tz-utc   73）、--user, -u   指定连接的用户名。   74）、--verbose, --v   输出多种平台信息。   75）、--version, -V   输出mysqldump版本信息并退出   76）、--where, -w   只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。   mysqldump -uroot -p --host=localhost --all-databases --where=” user=’root’”   77）、--xml, -X   导出XML格式.   mysqldump -uroot -p --host=localhost --all-databases --xml   78）、--plugin_dir   客户端插件的目录，用于兼容不同的插件版本。   mysqldump -uroot -p --host=localhost --all-databases--plugin_dir=”/usr/local/lib/plugin”   79）、--default_auth   客户端插件默认使用权限。   mysqldump -uroot -p --host=localhost --all-databases--default-auth=”/usr/local/lib/plugin/<PLUGIN>”

结合Linux的cron命令实现定时备份

• 比如需要在每天凌晨1:30备份某个主机上的所有数据库并压缩dump文件为gz格式，那么可在/etc/crontab配置文件中加入下面代码行：

?

1	30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz

　　 前面5个参数分别表示分钟、小时、日、月、年，星号表示任意。date '+%m-%d-%Y'得到当前日期的MM-DD-YYYY格式。

一个完整的Shell脚本备份MySQL数据库示例

?

1 2 3 4 5 6 7 8 9

#vi /backup/backup.sh !bin/bash cd /backup echo "You are in backup dir" mv backup* /oldbackup echo "Old dbs are moved to oldbackup folder" File = backup-$Now.sql mysqldump -u user -p password database-name > $File echo "Your database backup successfully completed"

 上面脚本文件保存为backup.sh，并且系统中已经创建两个目录/olcbackup和/backup。每次执行backup.sh时都会先将/backup目录下所有名称为backup开头的文件移到/oldbackup目录。

      为上述脚本制定执行计划如下：

?

1 2	#crontab -e 30 1 * * * /backup.sh

mysqldump全量备份+mysqlbinlog二进制日志增量备份

 从mysqldump备份文件恢复数据会丢失掉从备份点开始的更新数据，所以还需要结合mysqlbinlog二进制日志增量备份。确保my.ini或者my.cnf中包含下面的配置已启用二进制日志，或者mysqld ---log-bin：

?

1 2	[mysqld] log-bin=mysql-bin

　　mysqldump命令必须带上--flush-logs选项以生成新的二进制日志文件（这样恢复方便，无需从备份前最后一个bin_log里某节点或时间恢复）：

?

1	mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql

　　 这样生成的增量二进制日志文件比如为mysql-bin.000003，那么恢复数据时如下：

?

1 2	shell> mysql -uroot -pPwd < backup_sunday_1_PM.sql shell> mysqlbinlog mysql-bin.000003 | mysql -uroot -pPwd

　　此外mysqlbinlog还可以指定--start-date、--stop-date、--start-position和--stop-position参数，用于精确恢复数据到某个时刻之前或者跳过中间某个出问题时间段恢复数据，直接摘录MySQL文档说明中相关内容如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

5.9.3.1. 指定恢复时间 对于MySQL 4.1.4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2005年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入： mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 \      | mysql -u root -pmypwd 该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使日期和时间再次运行mysqlbinlog： mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 | mysql -u root -pmypwd 在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。 5.9.3.2. 指定恢复位置 也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为： mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql 该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容： mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 | mysql -u root -pmypwd mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 | mysql -u root -pmypwd \ 上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间。