\u4e0b\u9762\uff0c\u5c31mysql\u6570\u636e\u5e93\u8bef\u5220\u9664\u540e\u7684\u6062\u590d\u65b9\u6848\u8fdb\u884c\u8bf4\u660e\u3002<\/p>\n

MySQL\u6570\u636e\u6062\u590d\u65b9\u6cd5\u603b\u7ed3<\/strong><\/div>\n
1\u3001\u4f7f\u7528Mysql \u6570\u636e\u95ea\u56de\u5de5\u5177\u6062\u590d\u6570\u636e\u7684\u6587\u7ae0\uff1ahttp:\/\/blog.51cto.com\/qiuyt\/2095758<\/p>\n
2\u3001\u4eca\u5929\u7684\u4e3b\u89d2 \u3010Mysqldump\u3011\uff0c\u539f\u751f\u81ea\u5e26<\/p>\n
\u5de5\u4f5c\u573a\u666f<\/strong><\/div>\n
\uff081\uff09MySQL\u6570\u636e\u5e93\u6bcf\u665a12:00\u81ea\u52a8\u5b8c\u5168\u5907\u4efd\u3002<\/p>\n
\uff082\uff09\u67d0\u5929\u65e9\u4e0a\u4e0a\u73ed\uff0c9\u70b9\u7684\u65f6\u5019\uff0c\u4e00\u540c\u4e8b\u72af\u6655drop\u4e86\u4e00\u4e2a\u6570\u636e\u5e93\uff01<\/p>\n
\uff083\uff09\u9700\u8981\u7d27\u6025\u6062\u590d\uff01\u53ef\u5229\u7528\u5907\u4efd\u7684\u6570\u636e\u6587\u4ef6\u4ee5\u53ca\u589e\u91cf\u7684binlog\u6587\u4ef6\u8fdb\u884c\u6570\u636e\u6062\u590d\u3002<\/p>\n
\u6570\u636e\u6062\u590d\u601d\u8def<\/strong><\/div>\n
\uff081\uff09\u5229\u7528\u5168\u5907\u7684sql\u6587\u4ef6\u4e2d\u8bb0\u5f55\u7684CHANGE MASTER\u8bed\u53e5\uff0cbinlog\u6587\u4ef6\u53ca\u5176\u4f4d\u7f6e\u70b9\u4fe1\u606f\uff0c\u627e\u51fabinlog\u6587\u4ef6\u4e2d\u589e\u91cf\u7684\u90a3\u90e8\u5206\u3002<\/p>\n
\uff082\uff09\u7528mysqlbinlog\u547d\u4ee4\u5c06\u4e0a\u8ff0\u7684binlog\u6587\u4ef6\u5bfc\u51fa\u4e3asql\u6587\u4ef6\uff0c\u5e76\u5254\u9664\u5176\u4e2d\u7684drop\u8bed\u53e5\u3002<\/p>\n
\uff083\uff09\u901a\u8fc7\u5168\u5907\u6587\u4ef6\u548c\u589e\u91cfbinlog\u6587\u4ef6\u7684\u5bfc\u51fasql\u6587\u4ef6\uff0c\u5c31\u53ef\u4ee5\u6062\u590d\u5230\u5b8c\u6574\u7684\u6570\u636e\u3002<\/p>\n
\u56db\u3001\u5b9e\u4f8b\u8bf4\u660e<\/strong><\/div>\n
\u9996\u5148\uff0c\u8981\u786e\u4fddmysql\u5f00\u542f\u4e86binlog\u65e5\u5fd7\u529f\u80fd,\u5728\/etc\/my.cnf\u6587\u4ef6\u91cc\u7684[mysqld]\u533a\u5757\u6dfb\u52a0\uff1a<\/p>\n
\r\nlog-bin=mysql-bin\r\n<\/pre>\n
\u7136\u540e\u91cd\u542fmysql\u670d\u52a1<\/p>\n
\u5728ops\u5e93\u4e0b\u521b\u5efa\u4e00\u5f20\u8868customers<\/strong><\/span><\/div>\n
\r\nmysql> use ops;\r\nmysql> create table customers(\r\n-> id int not null auto_increment,\r\n-> name char(20) not null,\r\n-> age int not null,\r\n-> primary key(id)\r\n-> )engine=InnoDB;\r\nQuery OK, 0 rows affected (0.09 sec)\r\n\r\nmysql> show tables;\r\n+---------------+\r\n| Tables_in_ops |\r\n+---------------+\r\n| customers |\r\n+---------------+\r\n1 row in set (0.00 sec)\r\nmysql> desc customers;\r\n+-------+----------+------+-----+---------+----------------+\r\n| Field | Type | Null | Key | Default | Extra |\r\n+-------+----------+------+-----+---------+----------------+\r\n| id | int(11) | NO | PRI | NULL | auto_increment |\r\n| name | char(20) | NO | | NULL | |\r\n| age | int(11) | NO | | NULL | |\r\n+-------+----------+------+-----+---------+----------------+\r\n3 rows in set (0.02 sec)\r\nmysql> insert into customers values(1,\"wangbo\",\"24\");\r\nQuery OK, 1 row affected (0.06 sec)\r\nmysql> insert into customers values(2,\"guohui\",\"22\");\r\nQuery OK, 1 row affected (0.06 sec)\r\nmysql> insert into customers values(3,\"zhangheng\",\"27\");\r\nQuery OK, 1 row affected (0.09 sec)\r\nmysql> select * from customers;\r\n+----+-----------+-----+\r\n| id | name | age |\r\n+----+-----------+-----+\r\n| 1 | wangbo | 24 |\r\n| 2 | guohui | 22 |\r\n| 3 | zhangheng | 27 |\r\n+----+-----------+-----+\r\n3 rows in set (0.00 sec)\r\n<\/pre>\n
\u73b0\u5728\u8fdb\u884c\u5168\u5907\u4efd<\/strong><\/span><\/div>\n
\r\n[root@vm-002 ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >\/opt\/backup\/ops_$(date +%F).sql.gz\r\nEnter password: \r\n[root@vm-002 ~]# ls \/opt\/backup\/\r\nops_2018-07-03.sql.gz\r\n<\/pre>\n
\u53c2\u6570\u8bf4\u660e\uff1a<\/p>\n
\r\n-B\uff1a\u6307\u5b9a\u6570\u636e\u5e93\uff08\u5907\u4efd\u4e2d\u5e26\u6709\u521b\u5e93\u8bed\u53e5\uff09\r\n-F\uff1a\u5237\u65b0\u65e5\u5fd7\r\n-R\uff1a\u5907\u4efd\u5b58\u50a8\u8fc7\u7a0b\u7b49\r\n-x\uff1a\u9501\u8868 \uff08\u9501\u8868\u8fc7\u7a0b\u4f1a\u9020\u6210\u4e1a\u52a1\u65e0\u6cd5\u8bfb\u5199\uff0c\u53ef\u80fd\u4f1a\u51fa\u73b0502\uff09\r\n--master-data\uff1a\u5728\u5907\u4efd\u8bed\u53e5\u91cc\u6dfb\u52a0CHANGE MASTER\u8bed\u53e5\u4ee5\u53cabinlog\u6587\u4ef6\u53ca\u4f4d\u7f6e\u70b9\u4fe1\u606f\r\n<\/pre>\n
\u518d\u6b21\u63d2\u5165\u6570\u636e<\/strong><\/span><\/div>\n
\r\nmysql> insert into customers values(4,\"liupeng\",\"21\");\r\nQuery OK, 1 row affected (0.06 sec)\r\nmysql> insert into customers values(5,\"xiaoda\",\"31\");\r\nQuery OK, 1 row affected (0.07 sec)\r\nmysql> insert into customers values(6,\"fuaiai\",\"26\");\r\nQuery OK, 1 row affected (0.06 sec)\r\nmysql> select * from customers;\r\n+----+-----------+-----+\r\n| id | name | age |\r\n+----+-----------+-----+\r\n| 1 | wangbo | 24 |\r\n| 2 | guohui | 22 |\r\n| 3 | zhangheng | 27 |\r\n| 4 | liupeng | 21 |\r\n| 5 | xiaoda | 31 |\r\n| 6 | fuaiai | 26 |\r\n+----+-----------+-----+\r\n6 rows in set (0.00 sec)\r\n<\/pre>\n
\u6b64\u65f6\u8bef\u64cd\u4f5c\uff0c\u5220\u9664\u4e86OPS\u6570\u636e\u5e93<\/strong><\/span><\/div>\n
\r\nmysql> drop database ops;\r\nQuery OK, 1 row affected (0.04 sec)\r\n<\/pre>\n
\u6b64\u65f6\uff0c\u5168\u5907\u4e4b\u540e\u5230\u8bef\u64cd\u4f5c\u65f6\u523b\u4e4b\u95f4\uff0c\u7528\u6237\u5199\u5165\u7684\u6570\u636e\u5728binlog\u4e2d\uff0c\u9700\u8981\u6062\u590d\u51fa\u6765!<\/p>\n
\u67e5\u770b\u5168\u5907\u4e4b\u540e\u65b0\u589e\u7684binlog\u6587\u4ef6<\/strong><\/span><\/div>\n
\r\n[root@vm-002 ~]# cd \/opt\/backup\/\r\n[root@vm-002 backup]# ls\r\nops_2018-07-03.sql.gz\r\n[root@vm-002 backup]# gzip -d ops_2018-07-03.sql.gz \r\n[root@vm-002 backup]# ls\r\nops_2018-07-03.sql\r\n[root@vm-002 backup]# grep CHANGE ops_2018-07-03.sql \r\n-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;\r\n<\/pre>\n
\u8fd9\u662f\u5168\u5907\u65f6\u523b\u7684binlog\u6587\u4ef6\u4f4d\u7f6e,\u5373mysql-bin.000002\u7684106\u884c\uff0c\u56e0\u6b64\u5728\u8be5\u6587\u4ef6\u4e4b\u524d\u7684binlog\u6587\u4ef6\u4e2d\u7684\u6570\u636e\u90fd\u5df2\u7ecf\u5305\u542b\u5728\u8fd9\u4e2a\u5168\u5907\u7684sql\u6587\u4ef6\u4e2d\u4e86<\/p>\n
\u79fb\u52a8binlog\u6587\u4ef6\uff0c\u5e76\u5bfc\u51fa\u4e3asql\u6587\u4ef6\uff0c\u5254\u9664\u5176\u4e2d\u7684drop\u8bed\u53e5<\/strong><\/span><\/div>\n
\u67e5\u770bmysql\u7684\u6570\u636e\u5b58\u653e\u76ee\u5f55\uff0c\u7531\u4e0b\u9762\u53ef\u77e5\u662f\u5728\/var\/lib\/mysql\u4e0b<\/p>\n
\r\n[root@vm-002 backup]# ps -ef|grep mysql\r\nroot 9272 1 0 01:43 pts\/1 00:00:00 \/bin\/sh \/usr\/bin\/mysqld_safe --datadir=\/var\/lib\/mysql --socket=\/var\/lib\/mysql\/mysql.sock --pid-file=\/var\/run\/mysqld\/mysqld.pid --basedir=\/usr --user=mysql\r\nmysql 9377 9272 0 01:43 pts\/1 00:00:00 \/usr\/libexec\/mysqld --basedir=\/usr --datadir=\/var\/lib\/mysql --user=mysql --log-error=\/var\/log\/mysqld.log --pid-file=\/var\/run\/mysqld\/mysqld.pid --socket=\/var\/lib\/mysql\/mysql.sock\r\n[root@vm-002 backup]# cd \/var\/lib\/mysql\/\r\n[root@vm-002 mysql]# ls\r\nibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock test\r\n[root@vm-002 mysql]# cp mysql-bin.000002 \/opt\/backup\/\r\n\u5c06binlog\u6587\u4ef6\u5bfc\u51fasql\u6587\u4ef6\uff0c\u5e76vim\u7f16\u8f91\u5b83\u5220\u9664\u5176\u4e2d\u7684drop\u8bed\u53e5\r\n[root@vm-002 backup]# mysqlbinlog -d ops mysql-bin.000002 >002bin.sql\r\n[root@vm-002 backup]# ls\r\n002bin.sql mysql-bin.000002 ops_2018-07-03.sql\r\n[root@vm-002 backup]# vim 002bin.sql #\u5220\u9664\u91cc\u9762\u7684drop\u8bed\u53e5\r\n<\/pre>\n
\u6ce8\u610f\uff1a\u5728\u6062\u590d\u5168\u5907\u6570\u636e\u4e4b\u524d\u5fc5\u987b\u5c06\u8be5binlog\u6587\u4ef6\u79fb\u51fa\uff0c\u5426\u5219\u6062\u590d\u8fc7\u7a0b\u4e2d\uff0c\u4f1a\u7ee7\u7eed\u5199\u5165\u8bed\u53e5\u5230binlog\uff0c\u6700\u7ec8\u5bfc\u81f4\u589e\u91cf\u6062\u590d\u6570\u636e\u90e8\u5206\u53d8\u5f97\u6bd4\u8f83\u6df7\u4e71<\/p>\n
\u6062\u590d\u6570\u636e<\/strong><\/span><\/div>\n
\r\n[root@vm-002 backup]# mysql -uroot -p < ops_2018-07-03.sql \r\nEnter password: \r\n[root@vm-002 backup]#\r\n<\/pre>\n
\u68c0\u67e5\u201c\u5168\u5907\u201d \u662f\u5426\u6062\u590d\u6210\u529f<\/strong><\/span><\/div>\n
\u67e5\u770b\u6570\u636e\u5e93\uff0c\u770b\u770bops\u5e93\u5728\u4e0d\u5728<\/p>\n
\r\nmysql> show databases;\r\n+--------------------+\r\n| Database |\r\n+--------------------+\r\n| information_schema |\r\n| mysql |\r\n| ops |\r\n| test |\r\n+--------------------+\r\n4 rows in set (0.00 sec)\r\nmysql> use ops;\r\nReading table information for completion of table and column names\r\nYou can turn off this feature to get a quicker startup with -A\r\nDatabase changed\r\nmysql> select * from customers;\r\n+----+-----------+-----+\r\n| id | name | age |\r\n+----+-----------+-----+\r\n| 1 | wangbo | 0 |\r\n| 2 | guohui | 0 |\r\n| 3 | zhangheng | 0 |\r\n+----+-----------+-----+\r\n3 rows in set (0.00 sec)\r\n<\/pre>\n
\u6b64\u65f6\u6062\u590d\u4e86\u5168\u5907\u65f6\u523b\u7684\u6570\u636e;\u63a5\u7740\uff0c\u4f7f\u7528002bin.sql\u6587\u4ef6\u6062\u590d\u5168\u5907\u65f6\u523b\u5230\u5220\u9664\u6570\u636e\u5e93\u4e4b\u95f4\uff0c\u65b0\u589e\u7684\u6570\u636e<\/p>\n
\r\n[root@vm-002 backup]# mysql -uroot -p ops <002bin.sql\r\nEnter password: \r\n[root@vm-002 backup]#\r\n<\/pre>\n
\u518d\u6b21\u67e5\u770b\u6570\u636e\u5e93\uff0c\u53d1\u73b0\u5168\u5907\u4efd\u5230\u5220\u9664\u6570\u636e\u5e93\u4e4b\u95f4\u7684\u90a3\u90e8\u5206\u6570\u636e\u4e5f\u6062\u590d\u4e86\uff01\uff01<\/p>\n
\r\nmysql> select * from customers;\r\n+----+-----------+-----+\r\n| id | name | age |\r\n+----+-----------+-----+\r\n| 1 | wangbo | 24 |\r\n| 2 | guohui | 22 |\r\n| 3 | zhangheng | 27 |\r\n| 4 | liupeng | 21 |\r\n| 5 | xiaoda | 31 |\r\n| 6 | fuaiai | 26 |\r\n+----+-----------+-----+\r\n6 rows in set (0.00 sec)\r\n<\/pre>\n
\u4ee5\u4e0a\u5c31\u662fmysql\u6570\u636e\u5e93\u589e\u91cf\u6570\u636e\u6062\u590d\u7684\u5b9e\u4f8b\u8fc7\u7a0b\uff01<\/p>\n
\u603b\u7ed3\u51e0\u70b9<\/strong><\/div>\n
1\uff09\u672c\u6848\u4f8b\u9002\u7528\u4e8e\u4eba\u4e3aSQL\u8bed\u53e5\u9020\u6210\u7684\u8bef\u64cd\u4f5c\u6216\u8005\u6ca1\u6709\u4e3b\u4ece\u590d\u5236\u7b49\u7684\u70ed\u5907\u60c5\u51b5\u5b95\u673a\u65f6\u7684\u4fee\u590d\u3002<\/p>\n
2\uff09\u6062\u590d\u6761\u4ef6\u4e3amysql\u8981\u5f00\u542fbinlog\u65e5\u5fd7\u529f\u80fd\uff0c\u5e76\u4e14\u8981\u5168\u5907\u548c\u589e\u91cf\u7684\u6240\u6709\u6570\u636e\u3002<\/p>\n
3\uff09\u6062\u590d\u65f6\u5efa\u8bae\u5bf9\u5916\u505c\u6b62\u66f4\u65b0\uff0c\u5373\u7981\u6b62\u66f4\u65b0\u6570\u636e\u5e93\u3002<\/p>\n
4\uff09\u5148\u6062\u590d\u5168\u91cf\uff0c\u7136\u540e\u628a\u5168\u5907\u65f6\u523b\u70b9\u4ee5\u540e\u7684\u589e\u91cf\u65e5\u5fd7\uff0c\u6309\u987a\u5e8f\u6062\u590d\u6210SQL\u6587\u4ef6\uff0c\u7136\u540e\u628a\u6587\u4ef6\u4e2d\u6709\u95ee\u9898\u7684SQL\u8bed\u53e5\u5220\u9664\uff08\u4e5f\u53ef\u901a\u8fc7\u65f6\u95f4\u548c\u4f4d\u7f6e\u70b9\uff09\uff0c\u518d\u6062\u590d\u5230\u6570\u636e\u5e93\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u4e0b\u9762\uff0c\u5c31mysql\u6570\u636e\u5e93\u8bef\u5220\u9664\u540e\u7684\u6062\u590d\u65b9\u6848\u8fdb\u884c\u8bf4\u660e\u3002 1\u3001\u4f7f\u7528Mysql \u6570\u636e\u95ea\u56de\u5de5\u5177\u6062\u590d\u6570\u636e\u7684\u6587\u7ae0\uff1ahttp […]<\/p>\n","protected":false},"author":1479,"featured_media":110423,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[55],"tags":[],"class_list":["post-166869","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-thread"],"acf":[],"_links":{"self":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/166869","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/users\/1479"}],"replies":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/comments?post=166869"}],"version-history":[{"count":5,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/166869\/revisions"}],"predecessor-version":[{"id":167185,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/166869\/revisions\/167185"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media\/110423"}],"wp:attachment":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media?parent=166869"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/categories?post=166869"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/tags?post=166869"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}