MySQL数据库备份

【自学菜鸟】：有不当之处敬请大佬斧正。

本文主要练习cp,tar等归档工具进行物理备份和mysqldump逻辑备份工具，前者适用于所有的存储引擎，冷备、完全备份、部分备份，后者适用于所有的存储引擎，支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备。

一、MySQL数据库备份

1、cp,tar等归档工具备份数据库文件

（1）连接mysql并查看当前数据库

[root@test02 log]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.19-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> show databases;  #查看数据库信息，以mob_base_info数据库为例
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mob_ad              |
| mob_base_info       |
| mob_center          |
| mob_config          |
| mob_customer_center |
| mob_data            |
| mob_device_security |
| mob_gift            |
| mob_history         |
| mysql               |
| performance_schema  |
| sys                 |
| test                |
+---------------------+
14 rows in set (0.00 sec)

（2）以mob_base_info数据库为例

向mob_base_info下所有表读锁（当MySQL的一个进程为某一表开启读锁之后，其他的进程包含自身都没有权利去修改这表表的内容。但是所有的进程还是可以读出表里面的内容的。但是不能实现更新。）

mysql> use mob_base_info;
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> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

#测试读锁的效果

mysql> select shop_name from shop_info where shop_name='匡威';
+-----------+
| shop_name |
+-----------+
| 匡威      |
| 匡威      |
+-----------+
2 rows in set (0.01 sec)
mysql> update shop_info set shop_name='匡威test' where shop_name='匡威';
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock  #无法做update操作，但能select。
mysql>quit
[root@test02 /]# cp -a /usr/local/mysql/data/mob_base_info/* /backup  #保留权限复制数据库源文件
备注：也可以直接将所有数据库源文件所在文件夹复制或者打包
[root@test02 /]# find /usr/local/mysql/data/* -type d -exec cp -a {} /backup \; #复制所有数据库存放路径的所有文件夹
或者
[root@test02 backup]# find /usr/local/mysql/data/* -type d -exec tar -zcf  /backup/backup.tar.gz {} \; 

2、mysqldump逻辑工具备份数据

（1）备份全部数据库（包含操作create database/use database/drop table/create table/lock table/insert into table）

[root@test02 backup]# mysqldump -uroot -phht3306 --all-databases >/backup/backup.sql;

（2）备份全部数据库（额外增加CHANGE MASTER TO）

[root@test02 backup]# mysqldump -uroot -phht3306 --master-data=1 --all-databases  >/backup/backup.sql;
#--master-data=1是为了记录CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000019', MASTER_LOG_POS=2913

（3）备份全部数据库（额外增加事件和存储过程/函数）

[root@test02 backup]#  mysqldump -uroot -phht3306 -E -R --all-databases >/backup/backup4.sql;  #-E 事件  -R存储过程和函数

（4）备份全部数据库表结构

[root@test02 backup]# mysqldump -uroot -phht3306 -d --all-databases >/backup/backup0709.sql

（5）备份单个数据或者某几个数据库

[root@test02 backup]# mysqldump -uroot -phht3306  --databases test mysql >/backup/backup0709-1.sql

（6）如果同个服务器存在多实例，则需要注明ip和端口

[root@test02 backup]# mysqldump -uroot -phht3306 -h192.168.0.143 -P3306 --databases test mysql >/backup/backup0709-1.sql