mysql备份恢复

1.同实例拷贝数据库

shell> mysqldump db1 > dump.sql  
shell> mysqladmin create db2  
shell> mysql db2 < dump.sql

2. 数据库迁移

On Server 1:

shell> mysqldump --databases db1 > dump.sql  
Copy the dump file from Server 1 to Server 2.  

On Server 2:

shell> mysql < dump.sql

3. Dumping Stored Programs

Several options control how mysqldump handles stored programs (stored procedures and functions,
triggers, and events):

• --events: Dump Event Scheduler events  
• --routines: Dump stored procedures and functions
• --triggers: Dump triggers for tables

The --triggers option is enabled by default so that when tables are dumped, they are accompanied
by any triggers they have. The other options are disabled by default and must be specified explicitly to
dump the corresponding objects. To disable any of these options explicitly, use its skip form: --skipevents,
–skip-routines, or --skip-triggers.

4.dump表结构或者数据

The --no-data option tells mysqldump not to dump table data, resulting in the dump file containing
only statements to create the tables. Conversely, the --no-create-info option tells mysqldump to
suppress CREATE statements from the output, so that the dump file contains only table data.
For example, to dump table definitions and data separately for the test database, use these
commands:

shell> mysqldump --no-data test > dump-defs.sql  
shell> mysqldump --no-create-info test > dump-data.sql

For a definition-only dump, add the --routines and --events options to also include stored routine
and event definitions:

shell> mysqldump --no-data --routines --events test > dump-defs.sql  

5. 升级验证兼容性操作步骤

On the production server:

shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql  

On the upgraded server:

shell> mysql < dump-defs.sql  

验证功能正常后导入数据
On the production server:

shell> mysqldump --all-databases --no-create-info > dump-data.sql  

On the upgraded server:

shell> mysql < dump-data.sql  

检查数据，执行查询和应用验证

备份恢复方法

1、Making a Hot Backup with MySQL Enterprise Backup

2、Making Backups with mysqldump

备份

–master-data=1 导出文件标明binlog日志和position
–master-data=2 导出文件注释binlog日志和position

shell> mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot  -p > ./test.sql  

–single-transaction不会锁任何表

shell> mysqldump --single-transaction --flush-logs --master-data=2 \  
--all-databases > backup_sunday_1_PM.sql  
mysqldump --single-transaction --flush-logs --master-data=2 \  
--all-databases --delete-master-logs > backup_sunday_1_PM.sql

恢复方法

shell> mysql < backup_sunday_1_PM.sql  
shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql  

3、Making Backups by Copying Table Files

拷贝文件*.frm, *.MYD, and *.MYI files    
To get a consistent backup, stop the server or lock and flush the    
relevant tables:    
FLUSH TABLES tbl_list WITH READ LOCK; 

4、Making Delimited-Text File Backups

mysqldump导出

shell> mysqldump --tab=/tmp --fields-terminated-by=,    
--fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

此处--tab必须加上，否则就是正常的dump文件。/tmp需改为指定的安全路径

FIELDS、LINES 默认等于

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

mysqldump --tab生成两个文件t1.sqland t1.txt
.sql是创建表的sql文件 。 .txt是数据文件，是使用select … into outfile方式导出的。

命令执行需在local服务器上。如果客户端执行，则server和client端均需要有该文件夹，且.sql文件写到client，.txt文件写到server

知识点：0x0d0a代表16进制换行符\r\n    
--fields-enclosed-by=0x22 与'"'意思相同，如果部分平台无法识别'"'，可以使用16进制表示。   
select HEX('"') 结果为 22，16进制书写需加上0x

select … into outfile导出

select * into outfile '/var/lib/mysql-files/kdd2.dp' from sakila.actor limit 10; 

导出的文件不存在表结构，文件只能位于参数 secure_file_priv对应的文件夹下

load导入

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

mysqlimport导入

shell> mysqlimport --fields-terminated-by=,    
--fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt  

5、Making Incremental Backups by Enabling the Binary Log

执行命令FLUSH LOGS.

全量备份时，需要FLUSH LOGSor mysqldump --flush-logs

6、Making Backups Using Replication Slaves

7、Recovering Corrupt Tables

If you have to restore MyISAM tables that have become corrupt, try to recover them using REPAIR
TABLE or myisamchk -r first. That should work in 99.9% of all cases.

8、Making Backups Using a File System Snapshot

If you are using a Veritas file system, you can make a backup like this:

1. From a client program, execute FLUSH TABLES WITH READ LOCK.

2. From another shell, execute mount vxfs snapshot.
3. From the first client, execute UNLOCK TABLES.
4. Copy files from the snapshot.
5. Unmount the snapshot.
   Similar snapshot capabilities may be available in other file systems, such as LVM or ZFS