时间：2021-07-01 10:21:17 帮助过：12人阅读
/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地导入dump出来的文件，因为它是在插入所有行后创建索引的。该选项只适合MyISAM表，默认为打开状态。使用--skip-disable-keys禁。‘CHANGE MASTER TO..‘语句中增加‘MASTER_HOST=<host>，MASTER_PORT=<port>‘ 。备份MySQL数据库的命令：
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
备份MySQL数据库某个(些)表：
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
同时备份多个MySQL数据库：
mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql
仅仅备份数据库结构：
mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
备份服务器上所有数据库：
mysqldump –all-databases > allbackupfile.sql
还原MySQL数据库的命令：
mysql -hhostname -uusername -ppassword databasename < backupfile.sql
mysql> use hellodb
Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
8 rows in set (0.00 sec)
# mysqldump -uroot -hlocalhost -p --databases hellodb --lock-all-tables --flush-logs --master-data=2 >/opt/backup/hellodb_`date +‘%F-%H-%M‘`.sql
Enter password:
# ll /opt/backup/hellodb_2017-03-07-00-31.sql
-rw-r--r-- 1 root root 7943 3月 7 00:31 /opt/backup/hellodb_2017-03-07-00-31.sql
# cat /opt/backup/hellodb_2017-03-07-00-31.sql
-- MySQL dump 10.13 Distrib 5.6.31, for linux-glibc2.5 (x86_64)
--
-- Host: localhost Database: hellodb
-- ------------------------------------------------------
-- Server version 5.6.31-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=‘+00:00‘ */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO‘ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000025‘, MASTER_LOG_POS=120;
............
注意上面的MASTER_LOG_FILE和MASTER_LOG_POS这两个参数。在备份后我们可以通过show master status;命令查看的结合和前面两个参数的一致。
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000025 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> create table newdb(Name char(30));
Query OK, 0 rows affected (0.56 sec)
mysql> insert into newdb values(‘bols‘);
Query OK, 1 row affected (0.51 sec)
mysql> insert into newdb values(‘longls‘);
Query OK, 1 row affected (0.00 sec)
mysql> select * from newdb;
+--------+
| Name |
+--------+
| bols |
| longls |
+--------+
2 rows in set (0.00 sec)
在删除之前我们可以看到当前数据库中记录的二进制日志的状态：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000025 | 691 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> drop database hellodb;
Query OK, 8 rows affected (0.01 sec)
删除数据后我们可以通过二进制日志查看到当前二进制日志记录的信息：
# mysqlbinlog /data/mydata/mysql-bin.000025 |tail -25
#170305 2:21:24 server id 1 end_log_pos 461 CRC32 0x10b86693 Xid = 1250
COMMIT/*!*/;
# at 461
#170305 2:21:24 server id 1 end_log_pos 546 CRC32 0xcd30b1a8 Query thread_id=1 exec_time=166332 error_code=0
SET TIMESTAMP=1488651684/*!*/;
BEGIN
/*!*/;
# at 546
#170305 2:21:24 server id 1 end_log_pos 660 CRC32 0xb5f6b9c4 Query thread_id=1 exec_time=166332 error_code=0
SET TIMESTAMP=1488651684/*!*/;
insert into newdb values(‘longls‘)
/*!*/;
# at 660
#170305 2:21:24 server id 1 end_log_pos 691 CRC32 0x402cec06 Xid = 1251
COMMIT/*!*/;
# at 691
#170305 2:21:24 server id 1 end_log_pos 792 CRC32 0xd6897d83 Query thread_id=1 exec_time=166352 error_code=0
SET TIMESTAMP=1488651684/*!*/;
drop database hellodb
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
通过查看二进制日志我们可以查到在删除数据库之前Position位置应该是691，那么我们就可以根据这个在进行一次的数据增量备份：
# mysqlbinlog --start-position=120 --stop-position=691 /data/mydata/mysql-bin.000025> /opt/backup/hellodb_`date ‘+%F-%H-%M‘`.sql
# ll /opt/backup/hellodb_2017-03-07-00-3*
-rw-r--r-- 1 root root 7943 3月 7 00:31 /opt/backup/hellodb_2017-03-07-00-31.sql
-rw-r--r-- 1 root root 2254 3月 7 00:36 /opt/backup/hellodb_2017-03-07-00-36.sql
下面进行恢复数据前的准备操作：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000025 | 792 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000026 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
关闭二进制日志的记录功能：
mysql> set session sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
导入备份数据：
mysql> source /opt/backup/hellodb_2017-03-07-00-31.sql;
mysql> source /opt/backup/hellodb_2017-03-07-00-36.sql
验证数据：
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| newdb |
| scores |
| students |
| teachers |
| toc |
+-------------------+
8 rows in set (0.00 sec)
mysql> select * from newdb;
+--------+
| Name |
+--------+
| bols |
| longls |
+--------+
2 rows in set (0.00 sec)
开启二进制日志：
mysql> set session sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
1、数据备份文件要和数据库分开存放，同时二进制日志文件必须放在有冗余的设备上。
2、备份后要经常观察备份文件的是否正确。
3、由于这个例子是但库的备份，如果在MySQL上有多个数据库就要同时备份多个，如果只备份一个的话恢复其中一个会影响到别的数据库的，毕竟二进制文件记录的并不只是一个库的数据而是所有的数据库数据。
4、如果数据量大可以使用Xtrabackup这个强大的备份工具。
ref：
http://www.cnblogs.com/xuejie/archive/2013/01/11/2856911.html
mysqldump.md
标签：ppa 增量备份 contain 权限 password recovery complete float oca