Mysql备份和恢复
来源：互联网 发布：大数据电商平台 编辑：程序博客网 时间：2024/06/05 10:06
MySQL的备份和还原
备份：副本
RAID1，RAID10：保证硬件损坏而不会业务中止；
DROP TABLE mydb.tb1;
备份类型：
热备份、温备份和冷备份
热备份：读、写不受影响；
温备份：仅可以执行读操作；
冷备份：离线备份；读、写操作均中止；
物理备份和逻辑备份
物理备份：复制数据文件；
逻辑备份：将数据导出至文本文件中；
完全备份、增量备份和差异备份；
完全备份：备份全部数据；
增量备份：仅备份上次完全备份或增量备份以后变化的数据；
差异备份：仅备份上次完全备份以来变化的数据；
在线：物理完全备份
还原：
备份什么：
数据、配置文件、二进制日志、事务日志
热备份：
MyISAM: 温备份
InnoDB: xtrabackup, mysqldump
物理备份：速度快
逻辑备份：速度慢、丢失浮点数精度；方便使用文本处理工具直接对其处理、可移植能力强；
逻辑备份：
1、浮点数据丢失精度；
2、备份出的数据更占用存储空间；压缩后可大大节省空间；
3、不适合对大数据库做完全备份；
备份策略：完全+增量；完全+差异
MySQL备份工具：
mysqldump: 逻辑备份工具、MyISAM(温)、InnoDB(热备份)
mysqlhotcopy：物理备份工具、温备份
文件系统工具：
cp：冷备
lv: 逻辑卷的快照功能，几乎热备；
mysql> FLUSH TABLES;
mysql> LOCK TABLES
创建快照：释放锁，而后复制数据
第三组工具：
ibbackup: 商业工具
xtrabackup: 开源工具
mysqldump: 逻辑备份
mysqldump（完全备份）+ 二进制日志
完全+增量：
备份单个数据库，或库中特定表
mysqldump DB_NAME [tb1] [tb2]
–master-data={0|1|2}
0: 不记录二进制日志文件及路位置；
1：以CHNAGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器；
2：以CHANGE MASTER TO的方式记录位置，但默认为被注释；
–lock-all-tables：锁定所有表
mysql> flush tables with read lock;
mysql> unlock tables;
–flush-logs: 执行日志flush；
如果指定库中的表类型均为InnoDB，可使用–single-transaction启动热备；
备份多个库：
–all-databases: 备份所有库
–databases DB_NAME,DB_NAME,…: 备份指定库
--events：事件--routines：存储过程--triggers：触发器备份策略：每周完全+每日增量
完全备份：mysqldump
演示：
[root@node3 data]# mysqldump -uroot -p jiaowu > /root/jiaowu.sql
mysql> create database studb;
[root@node3 data]# mysql studb < /root/jiaowu.sql
[root@node3 ~]# mysqldump -uroot -p –lock-all-tables –flush-logs –all-databases –master-data=2 > /root/all.sql
增量备份：备份二进制日志文件(flush logs)
演示：
[root@node3 ~]# mysqldump -uroot -p –lock-all-tables –flush-logs –all-databases –master-data=2 > /root/all.sql
Enter password:
[root@node3 ~]# ls
all.sql cmake-2.8.5 install.log mysql-5.6.17
anaconda-ks.cfg cmake-2.8.5.tar.gz install.log.syslog mysql-5.6.17.tar.gz
[root@node3 ~]# less all.sql
[root@node3 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.17-log Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> purge binary logs to 'mysql.000004'; Query OK, 0 rows affected (0.06 sec) mysql> show binary logs; +--------------+-----------+ | Log_name | File_size | +--------------+-----------+ | mysql.000004 | 120 | +--------------+-----------+ 1 row in set (0.00 sec) mysql> use studb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from tutors; +-----+--------------+--------+------+ | TID | Tname | Gender | Age | +-----+--------------+--------+------+ | 2 | HuangYaoshi | M | 63 | | 3 | Miejueshitai | F | 72 | | 4 | OuYangfeng | M | 76 | | 6 | YuCanghai | M | 56 | | 7 | Jinlunfawang | M | 67 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | | 10 | tom | F | 80 | | 11 | tom | F | 80 | | 12 | DingDian | M | 25 | | 13 | HuFei | M | 31 | +-----+--------------+--------+------+ 11 rows in set (0.00 sec) mysql> delete from tutors where age > 60; Query OK, 6 rows affected (0.29 sec) mysql> select * from tutors; +-----+-------------+--------+------+ | TID | Tname | Gender | Age | +-----+-------------+--------+------+ | 6 | YuCanghai | M | 56 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | | 12 | DingDian | M | 25 | | 13 | HuFei | M | 31 | +-----+-------------+--------+------+ 5 rows in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> \q Bye [root@node3 ~]# cd /mydata/data/ [root@node3 data]# ls auto.cnf ib_logfile1 mysql.000005 node3.student.com.pid students ibdata1 mysql mysql.index performance_schema test ib_logfile0 mysql.000004 node3.student.com.err studb [root@node3 data]# cp mysql.000004 /root/ [root@node3 data]# mysqlbinlog mysql.000004 > /root/first.sql [root@node3 data]# [root@node3 data]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.6.17-log Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use studb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into tutors (tname) values ('stu123'); Query OK, 1 row affected (0.08 sec) mysql> select * from tutors; +-----+-------------+--------+------+ | TID | Tname | Gender | Age | +-----+-------------+--------+------+ | 6 | YuCanghai | M | 56 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | | 12 | DingDian | M | 25 | | 13 | HuFei | M | 31 | | 14 | stu123 | M | NULL | +-----+-------------+--------+------+ 6 rows in set (0.00 sec) mysql> \q Bye [root@node3 data]# ls auto.cnf ib_logfile1 mysql.000005 node3.student.com.pid students ibdata1 mysql mysql.index performance_schema test ib_logfile0 mysql.000004 node3.student.com.err studb [root@node3 data]# cp mysql.000005 /root/ [root@node3 data]# rm -rf * [root@node3 data]# ls [root@node3 data]# service mysqld stop ERROR! MySQL server PID file could not be found! [root@node3 data]# [root@node3 data]# killall mysqld [root@node3 data]# cd [root@node3 ~]# cd /usr/local/mysql/ [root@node3 mysql]# script script scriptreplay [root@node3 mysql]# script script scriptreplay [root@node3 mysql]# ls bin data include lib my.cnf README share support-files COPYING docs INSTALL-BINARY man mysql-test scripts sql-bench [root@node3 mysql]# scripts/mysql_install_db --user=mysql --datadir=mydata/data st. If that path was really intended, please create that directory path and then restart this script. cript. [root@node3 mysql]# [root@node3 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data ver option (see documentation for more details). s 2015-06-07 20:51:03 1735 [Note] InnoDB: The InnoDB memory heap is disabled s 2015-06-07 20:51:03 1735 [Note] InnoDB: Compressed tables use zlib 1.2.3 2015-06-07 20:51:03 1735 [Note] InnoDB: Using CPU crc32 instructions 2015-06-07 20:51:03 1735 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2015-06-07 20:51:03 1735 [Note] InnoDB: Completed initialization of buffer pool not exist: a new database to be created! 2015-06-07 20:51:03 1735 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB ait... MB B ogfile0 2015-06-07 20:51:12 1735 [Warning] InnoDB: New log files created, LSN=45781 2015-06-07 20:51:12 1735 [Note] InnoDB: Doublewrite buffer not found: creating new 2015-06-07 20:51:12 1735 [Note] InnoDB: Doublewrite buffer created 2015-06-07 20:51:12 1735 [Note] InnoDB: 128 rollback segment(s) are active. ables. d les. ed. 2015-06-07 20:51:12 1735 [Note] InnoDB: Waiting for purge to start 2015-06-07 20:51:12 1735 [Note] InnoDB: 5.6.17 started; log sequence number 0 2015-06-07 20:51:13 1735 [Note] Binlog end 2015-06-07 20:51:13 1735 [Note] InnoDB: FTS optimize thread exiting. 2015-06-07 20:51:13 1735 [Note] InnoDB: Starting shutdown... 5977 OK (see documentation for more details). s 2015-06-07 20:51:15 1758 [Note] InnoDB: The InnoDB memory heap is disabled s 2015-06-07 20:51:15 1758 [Note] InnoDB: Compressed tables use zlib 1.2.3 2015-06-07 20:51:15 1758 [Note] InnoDB: Using CPU crc32 instructions 2015-06-07 20:51:15 1758 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2015-06-07 20:51:15 1758 [Note] InnoDB: Completed initialization of buffer pool 2015-06-07 20:51:15 1758 [Note] InnoDB: Highest supported file format is Barracuda. 2015-06-07 20:51:15 1758 [Note] InnoDB: 128 rollback segment(s) are active. 2015-06-07 20:51:15 1758 [Note] InnoDB: Waiting for purge to start 2015-06-07 20:51:15 1758 [Note] InnoDB: 5.6.17 started; log sequence number 1625977 2015-06-07 20:51:15 1758 [Note] Binlog end 2015-06-07 20:51:15 1758 [Note] InnoDB: FTS optimize thread exiting. 2015-06-07 20:51:15 1758 [Note] InnoDB: Starting shutdown... 5987 OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h node3.student.com password 'new-password' Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com WARNING: Found existing config file ./my.cnf on the system. Because this file might be in use, it was not replaced, but was used in bootstrap (unless you used --defaults-file) and when you later start the server. The new default config file was created as ./my-new.cnf, please compare it with your file and take the changes you need. WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server [root@node3 mysql]# service mydqld start mydqld: unrecognized service [root@node3 mysql]# service mysqld start Starting MySQL..... SUCCESS! [root@node3 mysql]# cd [root@node3 ~]# mysql -uroot -p < all.sql Enter password: [root@node3 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.17-log Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use studb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from tutors; +-----+--------------+--------+------+ | TID | Tname | Gender | Age | +-----+--------------+--------+------+ | 2 | HuangYaoshi | M | 63 | | 3 | Miejueshitai | F | 72 | | 4 | OuYangfeng | M | 76 | | 6 | YuCanghai | M | 56 | | 7 | Jinlunfawang | M | 67 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | | 10 | tom | F | 80 | | 11 | tom | F | 80 | | 12 | DingDian | M | 25 | | 13 | HuFei | M | 31 | +-----+--------------+--------+------+ 11 rows in set (0.02 sec) [root@node3 ~]# mysql -uroot -p < first.sql Enter password: [root@node3 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.17-log Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use studb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from tutors; +-----+-------------+--------+------+ | TID | Tname | Gender | Age | +-----+-------------+--------+------+ | 6 | YuCanghai | M | 56 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | | 12 | DingDian | M | 25 | | 13 | HuFei | M | 31 | +-----+-------------+--------+------+ 5 rows in set (0.00 sec) mysql> mysql> \q Bye [root@node3 ~]# mysqlbinlog mysql.000005 > secound.sql [root@node3 ~]# mysql -uroot -p < secound.sql Enter password: [root@node3 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.17-log Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use studb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from tutors; +-----+-------------+--------+------+ | TID | Tname | Gender | Age | +-----+-------------+--------+------+ | 6 | YuCanghai | M | 56 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | | 12 | DingDian | M | 25 | | 13 | HuFei | M | 31 | | 14 | stu123 | M | NULL | +-----+-------------+--------+------+ 6 rows in set (0.00 sec) mysql> \q Bye [root@node3 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.6.17-log Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use studb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from tutors; +-----+-------------+--------+------+ | TID | Tname | Gender | Age | +-----+-------------+--------+------+ | 6 | YuCanghai | M | 56 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | | 12 | DingDian | M | 25 | | 13 | HuFei | M | 31 | | 14 | stu123 | M | NULL | +-----+-------------+--------+------+ 6 rows in set (0.00 sec) mysql> delete from tutors where tname='stu123'; Query OK, 1 row affected (0.04 sec) mysql> select * from tutors; +-----+-------------+--------+------+ | TID | Tname | Gender | Age | +-----+-------------+--------+------+ | 6 | YuCanghai | M | 56 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | | 12 | DingDian | M | 25 | | 13 | HuFei | M | 31 | +-----+-------------+--------+------+ 5 rows in set (0.00 sec) mysql> \1 ERROR: Unknown command '\1'. -> \q Bye [root@node3 ~]# mysqlbinlog mysql.000005 | mysql -uroot -p Enter password: [root@node3 ~]# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@node3 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.6.17-log Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use studb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from tutors; +-----+-------------+--------+------+ | TID | Tname | Gender | Age | +-----+-------------+--------+------+ | 6 | YuCanghai | M | 56 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | | 12 | DingDian | M | 25 | | 13 | HuFei | M | 31 | | 14 | stu123 | M | NULL | +-----+-------------+--------+------+ 6 rows in set (0.00 sec) mysql> \q Bye 0 0
- MySQL 备份和恢复
- MySQL备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL备份和恢复
- MySQL备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- mysql备份和恢复
- MySQL 备份和恢复
- 张氏读写锁，c++信号量实现
- C++ bitset的使用简介
- 104. Maximum Depth of Binary Tree(菜鸟刷题第一天)
- linux编译驱动之 make modules SUBDIRS
- C++：顺序容器及顺序容器适配器（stack、queue、priority_queue）
- Mysql备份和恢复
- Spark Shuffle内存分析
- delphi(客户端) socket 与 PHP_socket(服务器） 通信的例子
- dz论坛程序备份搬家
- 常用oracle函数备份
- 动态创建mfc控件
- java 泛型
- JS加载js文件或css文件和判断是否加载该js或css
- 网络之Http字段介绍
