{"id":63,"date":"2021-03-13T12:23:13","date_gmt":"2021-03-13T04:23:13","guid":{"rendered":"http:\/\/daishen.ltd\/?p=63"},"modified":"2021-03-13T12:23:20","modified_gmt":"2021-03-13T04:23:20","slug":"mysql%e5%a4%87%e4%bb%bd%e4%b8%8e%e6%81%a2%e5%a4%8d","status":"publish","type":"post","link":"https:\/\/daishen.ltd\/?p=63","title":{"rendered":"mysql\u5907\u4efd\u4e0e\u6062\u590d"},"content":{"rendered":"<p><meta charset=\"UTF-8\"><meta name=\"viewport\" content=\"width=device-width initial-scale=1\"><br \/>\n<title>mysql\u5907\u4efd\u4e0e\u6062\u590d<\/title><\/p>\n<h1>1. \u8fd0\u7ef4\u5728\u6570\u636e\u5e93\u5907\u4efd\u6062\u590d\u65b9\u9762\u7684\u804c\u8d23<\/h1>\n<h2>1.1 \u8bbe\u8ba1\u5907\u4efd\u7b56\u7565<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\n\u5907\u4efd\u5468\u671f:\u6839\u636e\u6570\u636e\u91cf\uff0c\n\u5907\u4efd\u5de5\u5177: mysq1dump (MDP) \uff0cXBK (PBK) percona Xtrabackup,\nMEB (MySQL Enterprise BACKUP MEB ) mysqlbinlog\n\u5907\u4efd\u65b9\u5f0f:\n\u903b\u8f91\u5907\u4efd\uff1a\n\t\u5168\u5907\uff1amysqldump\n\t\u589e\u91cf\uff1abinlog\uff08flush logs\uff0ccp\uff09\n\u7269\u7406\u5907\u4efd\uff1a\n\t\u5168\u5907\uff1aXBK\n\t\u589e\u91cf\uff1aXBK\n<\/code><\/pre>\n<h2>1.2 \u65e5\u5e38\u5907\u4efd\u68c0\u67e5<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">crontab -l\t\t\u67e5\u770b\u662f\u5426\u5b58\u5728\u5907\u4efd\u811a\u672c\n\u5907\u4efd\u811a\u672c\n\u5907\u4efd\u8def\u5f84\n\u770b\u5907\u4efd\u65e5\u5fd7\uff0c\u68c0\u67e5\u5907\u4efd\u6587\u4ef6\uff08\u5927\u5c0f\uff0c\u5185\u5bb9\uff09\n\n\n\u5907\u4efd\u5b58\u5728\u6027\n\u5907\u4efd\u7a7a\u95f4\u591f\u7528\u5426\n<\/code><\/pre>\n<h2>1.3 \u5b9a\u671f\u6062\u590d\u6f14\u7ec3(\u6d4b\u8bd5\u5e93)<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u4e00\u5b63\u5ea6 \u6216\u8005 \u534a\u5e74\n<\/code><\/pre>\n<h2>1.4 \u6545\u969c\u6062\u590d<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u901a\u8fc7\u73b0\u6709\u5b8c\u6574\u7684\u5907\u4efd\u5b8c\u6574\u7684\u65e5\u5fd7,\u80fd\u591f\u5c06\u6570\u636e\u5e93\u6062\u590d\u5230\u6545\u969c\u4e4b\u524d\u7684\u65f6\u95f4\u70b9.\uff08\u5feb\u901f\uff09       \n<\/code><\/pre>\n<h2>1.5 \u8fc1\u79fb<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">mysql -&gt; mysql\n\u5176\u4ed6 -&gt; mysql\nmysql -&gt; \u5176\u4ed6\n\u4e0d\u540c\u64cd\u4f5c\u7cfb\u7edf\u8fc1\u79fb\n\n1. \u505c\u673a\u65f6\u95f4\n2. \u56de\u9000\u65b9\u6848\n<\/code><\/pre>\n<h2>1.6\u5bfc\u51fa\u6570\u636e\u5e93\u6570\u636e<\/h2>\n<pre><code class=\"language-mysql\" lang=\"mysql\">\/etc\/my.cnf\n#\u5bfc\u51fa\u6570\u636e\u7684\u9650\u5236\nsecure-file-priv=\n\n\nselect concat (\"alter table \",table_schema, \" \", table_name,\" discard tablespace;\") from information_schema.tables where table_schema='ocp' into outfile '\/tmp\/discard.sql';\n\nselect * from user into outfile '\/tmp\/user.csv';\n<\/code><\/pre>\n<p>&nbsp;<\/p>\n<h1>2. \u5907\u4efd\u7c7b\u578b<\/h1>\n<h2>2.1 \u70ed\u5907<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u5728\u6570\u636e\u5e93\u6b63\u5e38\u4e1a\u52a1\u65f6,\u5907\u4efd\u6570\u636e,\u5e76\u4e14\u80fd\u591f\u4e00\u81f4\u6027\u6062\u590d\uff08\u53ea\u80fd\u662finnodb\uff09\n\u5bf9\u4e1a\u52a1\u5f71\u54cd\u975e\u5e38\u5c0f\n<\/code><\/pre>\n<h2>2.2 \u6e29\u5907<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u9501\u8868\u5907\u4efd,\u53ea\u80fd\u67e5\u8be2\u4e0d\u80fd\u4fee\u6539\uff08myisam\uff09\n\u5f71\u54cd\u5230\u5199\u5165\u64cd\u4f5c\n<\/code><\/pre>\n<h2>2.3 \u51b7\u5907<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u5173\u95ed\u6570\u636e\u5e93\u4e1a\u52a1,\u6570\u636e\u5e93\u6ca1\u6709\u4efb\u4f55\u53d8\u66f4\u7684\u60c5\u51b5\u4e0b,\u8fdb\u884c\u5907\u4efd\u6570\u636e.\n\u4e1a\u52a1\u505c\u6b62\n<\/code><\/pre>\n<h1>3. \u5907\u4efd\u65b9\u5f0f\u53ca\u5de5\u5177\u4ecb\u7ecd<\/h1>\n<h2>3.1 \u903b\u8f91\u5907\u4efd\u5de5\u5177<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u57fa\u4e8eSQL\u8bed\u53e5\u8fdb\u884c\u5907\u4efd\nmysqldump       *****\nmysqlbinlog     *****\n<\/code><\/pre>\n<h2>3.2 \u7269\u7406\u5907\u4efd\u5de5\u5177<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u57fa\u4e8e\u78c1\u76d8\u6570\u636e\u6587\u4ef6\u5907\u4efd\nxtrabackup(XBK) \uff1apercona \u7b2c\u4e09\u65b9   *****\nMySQL Enterprise Backup\uff08MEB\uff09\n<\/code><\/pre>\n<h1>4.  \u903b\u8f91\u5907\u4efd\u548c\u7269\u7406\u5907\u4efd\u7684\u6bd4\u8f83<\/h1>\n<h2>4.1 mysqldump (MDP)<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u4f18\u70b9\uff1a\n1.\u4e0d\u9700\u8981\u4e0b\u8f7d\u5b89\u88c5\n2.\u5907\u4efd\u51fa\u6765\u7684\u662fSQL\uff0c\u6587\u672c\u683c\u5f0f\uff0c\u53ef\u8bfb\u6027\u9ad8,\u4fbf\u4e8e\u5907\u4efd\u5904\u7406\n3.\u538b\u7f29\u6bd4\u8f83\u9ad8\uff0c\u8282\u7701\u5907\u4efd\u7684\u78c1\u76d8\u7a7a\u95f4\n\n\u7f3a\u70b9\uff1a\n4.\u4f9d\u8d56\u4e8e\u6570\u636e\u5e93\u5f15\u64ce\uff0c\u9700\u8981\u4ece\u78c1\u76d8\u628a\u6570\u636e\u8bfb\u51fa\n\u7136\u540e\u8f6c\u6362\u6210SQL\u8fdb\u884c\u8f6c\u50a8\uff0c\u6bd4\u8f83\u8017\u8d39\u8d44\u6e90\uff0c\u6570\u636e\u91cf\u5927\u7684\u8bdd\u6548\u7387\u8f83\u4f4e\n\u5efa\u8bae\uff1a\n100G\u4ee5\u5185\u7684\u6570\u636e\u91cf\u7ea7\uff0c\u53ef\u4ee5\u4f7f\u7528mysqldump\n\u8d85\u8fc7TB\u4ee5\u4e0a\uff0c\u6211\u4eec\u4e5f\u53ef\u80fd\u9009\u62e9\u7684\u662fmysqldump\uff0c\u914d\u5408\u5206\u5e03\u5f0f\u7684\u7cfb\u7edf\n1EB  =1024 PB =1000000 TB\n<\/code><\/pre>\n<h2>4.2 xtrabackup(XBK)<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u4f18\u70b9\uff1a\n1.\u7c7b\u4f3c\u4e8e\u76f4\u63a5cp\u6570\u636e\u6587\u4ef6\uff0c\u4e0d\u9700\u8981\u7ba1\u903b\u8f91\u7ed3\u6784\uff0c\u76f8\u5bf9\u6765\u8bf4\u6027\u80fd\u8f83\u9ad8\n\u7f3a\u70b9\uff1a\n2.\u53ef\u8bfb\u6027\u5dee\n3.\u538b\u7f29\u6bd4\u4f4e\uff0c\u9700\u8981\u66f4\u591a\u78c1\u76d8\u7a7a\u95f4\n\u5efa\u8bae\uff1a\n&gt;100G&lt;TB\n<\/code><\/pre>\n<h1>5.\u5907\u4efd\u7b56\u7565<\/h1>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u5907\u4efd\u65b9\u5f0f\uff1a\n\u5168\u5907:\u5168\u5e93\u5907\u4efd\uff0c\u5907\u4efd\u6240\u6709\u6570\u636e\n\u589e\u91cf:\u5907\u4efd\u53d8\u5316\u7684\u6570\u636e\n\u903b\u8f91\u5907\u4efd=mysqldump+mysqlbinlog\n\u7269\u7406\u5907\u4efd=xtrabackup_full+xtrabackup_incr+binlog\u6216\u8005xtrabackup_full+binlog\n\u5907\u4efd\u5468\u671f:\n\u6839\u636e\u6570\u636e\u91cf\u8bbe\u8ba1\u5907\u4efd\u5468\u671f\n\u6bd4\u5982\uff1a\u5468\u65e5\u5168\u5907\uff0c\u54681-\u54686\u589e\u91cf\n<\/code><\/pre>\n<h1>6.\u5907\u4efd\u5de5\u5177\u4f7f\u7528-mysqldump<\/h1>\n<h2>6.1 mysqldump (\u903b\u8f91\u5907\u4efd\u7684\u5ba2\u6237\u7aef\u5de5\u5177)<\/h2>\n<h3>6.1.1 \u5ba2\u6237\u7aef\u901a\u7528\u53c2\u6570<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">-u  -p   -S   -h  -P    \n\u672c\u5730\u5907\u4efd:\nmysqldump -uroot -p  -S \/tmp\/mysql.sock\n\u8fdc\u7a0b\u5907\u4efd:\nmysqldump -uroot -p  -h 10.0.0.51 -P3306\n<\/code><\/pre>\n<h3>6.1.2 \u5907\u4efd\u4e13\u7528\u57fa\u672c\u53c2\u6570<\/h3>\n<h4>-A   \u5168\u5907\u53c2\u6570<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-swift\" lang=\"swift\">\u4f8b\u5b501:\n[root@db01 ~]# mkdir -p \/data\/backup\nmysqldump -uroot -p1 -A &gt; \/backup\/full.sql\n# \u8865\u5145:\n# 1.\u5e38\u89c4\u5907\u4efd\u662f\u8981\u52a0 --set-gtid-purged=OFF,\u89e3\u51b3\u5907\u4efd\u65f6\u7684\u8b66\u544a\n# [root@db01 ~]# mysqldump -uroot -p123 -A  --set-gtid-purged=OFF  &gt;\/backup\/full.sql\n# 2.\u6784\u5efa\u4e3b\u4ece\u65f6,\u505a\u7684\u5907\u4efd,\u4e0d\u9700\u8981\u52a0\u8fd9\u4e2a\u53c2\u6570\n# [root@db01 ~]# mysqldump -uroot -p123 -A    --set-gtid-purged=ON &gt;\/backup\/full.sql\n<\/code><\/pre>\n<h4>-B db1  db2  db3  \u5907\u4efd\u591a\u4e2a\u5355\u5e93<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-kotlin\" lang=\"kotlin\">\u8bf4\u660e\uff1a\u751f\u4ea7\u4e2d\u9700\u8981\u5907\u4efd\uff0c\u751f\u4ea7\u76f8\u5173\u7684\u5e93\u548cMySQL\u5e93\n\u4f8b\u5b502 :\n mysqldump -uroot -p1 -B ocp cuoni school  &gt; \/backup\/db.sql \n<\/code><\/pre>\n<h4>\u5907\u4efd\u5355\u4e2a\u6216\u591a\u4e2a\u8868<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">\u4f8b\u5b503 world\u6570\u636e\u5e93\u4e0b\u7684city,country\u8868\nmysqldump -uroot -p1 world city country &gt;\/backup\/tab.sql\n\u4ee5\u4e0a\u5907\u4efd\u6062\u590d\u65f6:\u5fc5\u987b\u5e93\u4e8b\u5148\u5b58\u5728,\u5e76\u4e14ues\u624d\u80fdsource\u6062\u590d\n<\/code><\/pre>\n<h3>6.1.3 \u9ad8\u7ea7\u53c2\u6570\u5e94\u7528<\/h3>\n<h4>\u7279\u6b8a\u53c2\u65701\u4f7f\u7528\uff08\u5fc5\u987b\u8981\u52a0\uff09<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">-R            \u5907\u4efd\u5b58\u50a8\u8fc7\u7a0b\u53ca\u51fd\u6570\n--triggers  \u5907\u4efd\u89e6\u53d1\u5668\n-E             \u5907\u4efd\u4e8b\u4ef6\n\n\u4f8b\u5b504:\n[root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers &gt;\/data\/backup\/full.sql\n(5) \u7279\u6b8a\u53c2\u65702\u4f7f\u7528\n<\/code><\/pre>\n<h4>-F  \u5728\u5907\u4efd\u5f00\u59cb\u65f6,\u5237\u65b0\u4e00\u4e2a\u65b0binlog\u65e5\u5fd7<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-jsx\" lang=\"jsx\">\u4f8b\u5b505:\nmysqldump -uroot -p  -A  -R --triggers -F &gt;\/bak\/full.sql\n<\/code><\/pre>\n<h4>&#8211;master-data=2<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-kotlin\" lang=\"kotlin\">\uff081\uff09\u4ee5\u6ce8\u91ca\u7684\u5f62\u5f0f,\u4fdd\u5b58\u5907\u4efd\u5f00\u59cb\u65f6\u95f4\u70b9\u7684binlog\u7684\u72b6\u6001\u4fe1\u606f\n\uff082\uff09\u81ea\u52a8\u5f00\u542f\u9501\u8868\u529f\u80fd\n\nmysqldump -uroot -p  -A  -R --triggers --master-data=2   &gt;\/back\/world.sql\n[root@db01 ~]# grep 'CHANGE' \/backup\/world.sql \n-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;\n\n\u529f\u80fd\uff1a\n\uff081\uff09\u5728\u5907\u4efd\u65f6\uff0c\u4f1a\u81ea\u52a8\u8bb0\u5f55\uff0c\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\u540d\u548c\u4f4d\u7f6e\u53f7\n0 \u9ed8\u8ba4\u503c\n1  \u4ee5change master to\u547d\u4ee4\u5f62\u5f0f\uff0c\u53ef\u4ee5\u7528\u4f5c\u4e3b\u4ece\u590d\u5236\n2  \u4ee5\u6ce8\u91ca\u7684\u5f62\u5f0f\u8bb0\u5f55\uff0c\u5907\u4efd\u65f6\u523b\u7684\u6587\u4ef6\u540d+postion\u53f7\n\uff082\uff09 \u81ea\u52a8\u9501\u8868\n\uff083\uff09\u5982\u679c\u914d\u5408--single-transaction\uff0c\u53ea\u5bf9\u975eInnoDB\u8868\u8fdb\u884c\u9501\u8868\u5907\u4efd\uff0cInnoDB\u8868\u8fdb\u884c\u201c\u70ed\u201c\u201d\u5907\uff0c\u5b9e\u9645\u4e0a\u662f\u5b9e\u73b0\u5feb\u7167\u5907\u4efd\u3002\n<\/code><\/pre>\n<h4>&#8211;single-transaction<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-kotlin\" lang=\"kotlin\">innodb \u5b58\u50a8\u5f15\u64ce\u5f00\u542f\u70ed\u5907(\u5feb\u7167\u5907\u4efd)\u529f\u80fd       \nmaster-data\u53ef\u4ee5\u81ea\u52a8\u52a0\u9501\n\uff081\uff09\u5728\u4e0d\u52a0--single-transaction \uff0c\u542f\u52a8\u6240\u6709\u8868\u7684\u6e29\u5907\u4efd\uff0c\u6240\u6709\u8868\u90fd\u9501\u5b9a\n\uff081\uff09\u52a0\u4e0a--single-transaction ,\u5bf9innodb\u8fdb\u884c\u4e00\u81f4\u6027\u5feb\u7167\u5907\u4efd\uff0c\u4e0d\u9501\u8868,\u5bf9\u975einnodb\u8868\u53ef\u4ee5\u5b9e\u73b0\u81ea\u52a8\u9501\u8868\u529f\u80fd\n\u4f8b\u5b506: \u5907\u4efd\u5fc5\u52a0\u53c2\u6570\nmysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF &gt;\/data\/backup\/full.sql\n<\/code><\/pre>\n<h4>&#8211;set-gtid-purged=auto<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-kotlin\" lang=\"kotlin\">\u9ed8\u8ba4\u662fauto\n\u4e3b\u4ece\u590d\u5236,\u5ffd\u7565\u6b64\u53c2\u6570\n\u666e\u901a\u5907\u4efd\uff0c\u53ef\u4ee5OFF\n\n\u4f7f\u7528\u573a\u666f:\n1. --set-gtid-purged=OFF,\u53ef\u4ee5\u4f7f\u7528\u5728\u65e5\u5e38\u5907\u4efd\u53c2\u6570\u4e2d.\nmysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF &gt;\/data\/backup\/full.sql\n2. auto , on:\u5728\u6784\u5efa\u4e3b\u4ece\u590d\u5236\u73af\u5883\u65f6\u9700\u8981\u7684\u53c2\u6570\u914d\u7f6e\nmysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=ON &gt;\/data\/backup\/full.sql\n<\/code><\/pre>\n<h4>&#8211;max-allowed-packet=#<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M &gt;\/data\/backup\/full.sql\n\n --max-allowed-packet=# \nThe maximum packet length to send to or receive from server.\n<\/code><\/pre>\n<p>&nbsp;<\/p>\n<h4>6.2 \u5c0f\u7ec3\u4e60\uff1a<\/h4>\n<p>6.2.1. \u5b9e\u73b0\u6240\u6709\u8868\u7684\u5355\u72ec\u5907\u4efd<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">\u63d0\u793a\uff1a\ninformation_schema.tables\nmysqldump -uroot -p123 world city &gt;\/backup\/world_city.sql\n\nselect concat(\"mysqldump -uroot -p123 \",table_schema,\" \",table_name,\" --master-data=2 --single-transaction --set-gtid-purged=0  -R -E --triggers&gt;\/backup\/\",table_schema,\"_\",table_name,\".sql\") from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema');\n<\/code><\/pre>\n<h4>6.2.2.\u6a21\u62df\u6545\u969c\u6848\u4f8b\u5e76\u6062\u590d<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">\uff081\uff09\u6bcf\u5929\u5168\u5907\n\uff082\uff09binlog\u65e5\u5fd7\u662f\u5b8c\u6574\n\uff083\uff09\u6a21\u62df\u767d\u5929\u7684\u6570\u636e\u53d8\u5316\n\uff084\uff09\u6a21\u62df\u4e0b\u5348\u4e24\u70b9\u8bef\u5220\u9664\u6570\u636e\u5e93\n\n\u9700\u6c42\uff1a \u5229\u7528\u5168\u5907+binlog\u56de\u590d\u6570\u636e\u5e93\u8bef\u5220\u9664\u4e4b\u524d\u3002\n\u6545\u969c\u6a21\u62df\u53ca\u6062\u590d\uff1a\n1. \u6a21\u62df\u5468\u4e0023:00\u7684\u5168\u5907\nmysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF &gt;\/data\/backup\/full.sql\n2. \u6a21\u62df\u767d\u5929\u7684\u6570\u636e\u53d8\u5316\nMaster [(none)]&gt;create database day1 charset utf8;\nMaster [(none)]&gt;use day1\nMaster [day1]&gt;create table t1(id int);\nMaster [day1]&gt;insert into t1 values(1),(2),(3);\nMaster [day1]&gt;commit;\nMaster [world]&gt;update city set countrycode='CHN';\nMaster [world]&gt;commit;\n\u6a21\u62df\u78c1\u76d8\u635f\u574f\uff1a\n[root@db01 data]# \\rm -rf \/data\/mysql\/data\/*\n3. \u6062\u590d\u6545\u969c\n[root@db01 data]# pkill mysqld\n[root@db01 data]# \\rm -rf \/data\/mysql\/data\/*\n4. \u6062\u590d\u601d\u8def\n1.\u68c0\u67e5\u5907\u4efd\u53ef\u7528\u6027\n2.\u4ece\u5907\u4efd\u4e2d\u83b7\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4f4d\u7f6e\n3.\u6839\u636e\u65e5\u5fd7\u4f4d\u7f6e\u622a\u53d6\u9700\u8981\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\n4.\u521d\u59cb\u5316\u6570\u636e\u5e93,\u5e76\u542f\u52a8\n5.\u6062\u590d\u5168\u5907\n6.\u6062\u590d\u4e8c\u8fdb\u5236\u65e5\u5fd7\n<\/code><\/pre>\n<h2>6.3. \u538b\u7f29\u5907\u4efd\u5e76\u6dfb\u52a0\u65f6\u95f4\u6233<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-jsx\" lang=\"jsx\">\u4f8b\u5b50\uff1a\nmysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip &gt; \/backup\/full_$(date +%F).sql.gz\nmysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip &gt; \/backup\/full_$(date +%F-%T).sql.gz\n\nmysqldump\u5907\u4efd\u7684\u6062\u590d\u65b9\u5f0f\uff08\u5728\u751f\u4ea7\u4e2d\u6062\u590d\u8981\u8c28\u614e\uff0c\u6062\u590d\u4f1a\u5220\u9664\u91cd\u590d\u7684\u8868\uff09\nset sql_log_bin=0;\nsource \/backup\/full_2018-06-28.sql\n\n\u6ce8\u610f\uff1a\n1\u3001mysqldump\u5728\u5907\u4efd\u548c\u6062\u590d\u65f6\u90fd\u9700\u8981mysql\u5b9e\u4f8b\u542f\u52a8\u4e3a\u524d\u63d0\u3002\n2\u3001\u4e00\u822c\u6570\u636e\u91cf\u7ea7100G\u4ee5\u5185\uff0c\u5927\u7ea615-45\u5206\u949f\u53ef\u4ee5\u6062\u590d\uff0c\u6570\u636e\u91cf\u7ea7\u5f88\u5927\u5f88\u5927\u7684\u65f6\u5019\uff08PB\u3001EB\uff09\n3\u3001mysqldump\u662f\u8986\u76d6\u5f62\u5f0f\u6062\u590d\u7684\u65b9\u6cd5\u3002\n\n\u4e00\u822c\u6211\u4eec\u8ba4\u4e3a\uff0c\u5728\u540c\u6570\u636e\u91cf\u7ea7\uff0c\u7269\u7406\u5907\u4efd\u8981\u6bd4\u903b\u8f91\u5907\u4efd\u901f\u5ea6\u5feb.\n\u903b\u8f91\u5907\u4efd\u7684\u4f18\u52bf:\n1\u3001\u53ef\u8bfb\u6027\u5f3a\n2\u3001\u538b\u7f29\u6bd4\u5f88\u9ad8\n<\/code><\/pre>\n<h1>7\u3001\u4f01\u4e1a\u6545\u969c\u6062\u590d\u6848\u4f8b<\/h1>\n<h2>7.1 \u80cc\u666f\u73af\u5883\uff1a<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">\u6b63\u5728\u8fd0\u884c\u7684\u7f51\u7ad9\u7cfb\u7edf\uff0cmysql-5.7.20 \u6570\u636e\u5e93\uff0c\u6570\u636e\u91cf50G\uff0c\u65e5\u4e1a\u52a1\u589e\u91cf1-5M\u3002\n<\/code><\/pre>\n<h2>7.2 \u5907\u4efd\u7b56\u7565\uff1a<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">\u6bcf\u592923:00\u70b9\uff0c\u8ba1\u5212\u4efb\u52a1\u8c03\u7528mysqldump\u6267\u884c\u5168\u5907\u811a\u672c\n<\/code><\/pre>\n<h2>7.3 \u6545\u969c\u65f6\u95f4\u70b9\uff1a<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u5e74\u5e95\u6545\u969c\u6f14\u7ec3:\u6a21\u62df\u5468\u4e09\u4e0a\u534810\u70b9\u8bef\u5220\u9664\u6570\u636e\u5e93\uff0c\u5e76\u8fdb\u884c\u6062\u590d.\n<\/code><\/pre>\n<h2>7.4 \u601d\u8def\uff1a<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">1\u3001\u505c\u4e1a\u52a1\uff0c\u6302\u7ef4\u62a4\u9875\u9762\uff0c\u907f\u514d\u6570\u636e\u7684\u4e8c\u6b21\u4f24\u5bb3\n2\u3001\u627e\u4e00\u4e2a\u4e34\u65f6\u5e93\uff0c\u6062\u590d\u5468\u4e0923\uff1a00\u5168\u5907\n3\u3001\u622a\u53d6\u5468\u4e8c23\uff1a00  --- \u5468\u4e8c10\u70b9\u8bef\u5220\u9664\u4e4b\u95f4\u7684binlog\uff0c\u6062\u590d\u5230\u4e34\u65f6\u5e93\n4\u3001\u6d4b\u8bd5\u53ef\u7528\u6027\u548c\u5b8c\u6574\u6027\n5\u3001 \n    5.1 \u65b9\u6cd5\u4e00\uff1a\u76f4\u63a5\u4f7f\u7528\u4e34\u65f6\u5e93\u9876\u66ff\u539f\u751f\u4ea7\u5e93\uff0c\u524d\u7aef\u5e94\u7528\u5272\u63a5\u5230\u65b0\u5e93\n    5.2 \u65b9\u6cd5\u4e8c\uff1a\u5c06\u8bef\u5220\u9664\u7684\u8868\u5bfc\u51fa\uff0c\u5bfc\u5165\u5230\u539f\u751f\u4ea7\u5e93\n6\u3001\u5f00\u542f\u4e1a\u52a1\n\u5904\u7406\u7ed3\u679c\uff1a\u7ecf\u8fc720\u5206\u949f\u7684\u5904\u7406\uff0c\u6700\u7ec8\u4e1a\u52a1\u6062\u590d\u6b63\u5e38\n<\/code><\/pre>\n<h2>7.5 \u6545\u969c\u6a21\u62df\u6f14\u7ec3<\/h2>\n<h3>7.5.1 \u51c6\u5907\u6570\u636e<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">create database backup;\nuse backup\ncreate table t1 (id int);\ninsert into t1 values(1),(2),(3);\ncommit;\nrm -rf \/backup\/*\n<\/code><\/pre>\n<h3>7.5.2 \u5468\u4e8c 23\uff1a00\u5168\u5907<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-tsx\" lang=\"tsx\">mysqldump -uroot -p1 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip &gt; \/backup\/full_$(date +%F).sql.gz\n<\/code><\/pre>\n<h3>7.5.3 \u6a21\u62df\u5468\u4e8c 23\uff1a00\u5230\u5468\u4e09 10\u70b9\u4e4b\u95f4\u6570\u636e\u53d8\u5316<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">use backup\ninsert into t1 values(11),(22),(33);\ncommit;\ncreate table t2 (id int);\ninsert into t2 values(11),(22),(33);\n<\/code><\/pre>\n<h3>7.5.4 \u6a21\u62df\u6545\u969c,\u5220\u9664\u8868(\u53ea\u662f\u6a21\u62df\uff0c\u4e0d\u4ee3\u8868\u751f\u4ea7\u64cd\u4f5c)<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-rust\" lang=\"rust\">drop database backup;\n<\/code><\/pre>\n<h2>7.6 \u6062\u590d\u8fc7\u7a0b<\/h2>\n<h3>7.6.1 \u51c6\u5907\u4e34\u65f6\u6570\u636e\u5e93\uff08\u591a\u5b9e\u4f8b3307\uff09<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">systemctl start mysqld3307\n<\/code><\/pre>\n<h3>7.6.2 \u51c6\u5907\u5907\u4efd<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">\uff081\uff09\u51c6\u5907\u5168\u5907\uff1a\ncd \/backup\ngunzip full_2018-10-17.sql.gz \n\uff082\uff09\u622a\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7\n-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=793;\n\nshow binlog events in 'mysql-bin.000003';\n793\n1559\n\nmysqlbinlog  --skip-gtids --start-position=793 --stop-position=1559   \/data\/binlog\/mysql-bin.000003&gt;\/backup\/bin.sql\n<\/code><\/pre>\n<h3>7.6.3 \u6062\u590d\u5907\u4efd\u5230\u4e34\u65f6\u5e93<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">mysql -S \/data\/3307\/mysql.sock\nset sql_log_bin=0;\nsource \/backup\/full_2020-11-26.sql\nsource \/backup\/bin.sql\nset sql_log_bin=1;\n<\/code><\/pre>\n<h3>7.6.4 \u5c06\u6545\u969c\u8868\u5bfc\u51fa\u5e76\u6062\u590d\u5230\u751f\u4ea7<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">mysqldump   -S \/data\/3307\/mysql.sock -B backup &gt;\/backup\/t1.sql\nmysql -uroot -p1 \nset sql_log_bin=0;\nsource \/backup\/t1.sql;\nset sql_log_bin=1;\n<\/code><\/pre>\n<h1>8. \u8bfe\u4e0b\u4f5c\u4e1a\uff1a<\/h1>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u7ec3\u4e60\uff1a\n1\u3001\u521b\u5efa\u4e00\u4e2a\u6570\u636e\u5e93 oldboy\n2\u3001\u5728oldboy\u4e0b\u521b\u5efa\u4e00\u5f20\u8868t1\n3\u3001\u63d2\u51655\u884c\u4efb\u610f\u6570\u636e\n4\u3001\u5168\u5907\n5\u3001\u63d2\u5165\u4e24\u884c\u6570\u636e\uff0c\u4efb\u610f\u4fee\u65393\u884c\u6570\u636e\uff0c\u5220\u96641\u884c\u6570\u636e\n6\u3001\u5220\u9664\u6240\u6709\u6570\u636e\n7\u3001\u518dt1\u4e2d\u53c8\u63d2\u51655\u884c\u65b0\u6570\u636e\uff0c\u4fee\u65393\u884c\u6570\u636e\n\u9700\u6c42\uff0c\u8df3\u8fc7\u7b2c\u516d\u6b65\u6062\u590d\u8868\u6570\u636e\n\u5199\u5907\u4efd\u811a\u672c\u548c\u7b56\u7565\n<\/code><\/pre>\n<h1>9. \u5907\u4efd\u65f6\u4f18\u5316\u53c2\u6570:<\/h1>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-tsx\" lang=\"tsx\">(1) max_allowed_packet   \u63a7\u5236\u7684\u662f\u5907\u4efd\u65f6\u4f20\u8f93\u6570\u636e\u5305\u7684\u5927\u5c0f.\n\nmysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2 max_allowed_packet=128M  --single-transaction|gzip &gt; \/backup\/full_$(date +%F).sql.gz\n\n(2) \u589e\u52a0key_buffer_size    (\u4e34\u65f6\u8868\u6709\u5173)\n(3) \u5206\u5e93\u5206\u8868\u5e76\u53d1\u5907\u4efd       (\u4f5c\u4e1a)\n(4) \u67b6\u6784\u5206\u79bb,\u5206\u522b\u5907\u4efd      (\u67b6\u6784\u62c6\u5206,\u5206\u5e03\u5f0f\u5907\u4efd)\n(5) --set-gtid-purged=AUTO\/ON \u5728\u6784\u5efa\u4e3b\u4ece\u65f6,\u4f7f\u7528AUTO\/ON\n(6) --set-gtid-purged=OFF \u4ec5\u662f\u505a\u666e\u901a\u7684\u672c\u673a\u5907\u4efd\u6062\u590d\u65f6,\u53ef\u4ee5\u6dfb\u52a0\n\n<\/code><\/pre>\n<h1>10. MySQL\u7269\u7406\u5907\u4efd\u5de5\u5177-xtrabackup(XBK\u3001Xbackup)<\/h1>\n<h2>10.1\u5b89\u88c5<\/h2>\n<h3>10.1.1 \u5b89\u88c5\u4f9d\u8d56\u5305\uff1a<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-cpp\" lang=\"cpp\">wget -O \/etc\/yum.repos.d\/epel.repo http:\/\/mirrors.aliyun.com\/repo\/epel-7.repo\nyum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev\n<\/code><\/pre>\n<h3>10.1.2 \u4e0b\u8f7d\u8f6f\u4ef6\u5e76\u5b89\u88c5<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">wget https:\/\/www.percona.com\/downloads\/XtraBackup\/Percona-XtraBackup-2.4.12\/binary\/redhat\/7\/x86_64\/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm\n\nhttps:\/\/www.percona.com\/downloads\/XtraBackup\/Percona-XtraBackup-2.4.4\/binary\/redhat\/6\/x86_64\/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm\n\nyum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm\n<\/code><\/pre>\n<h2>10.2\u3001\u5907\u4efd\u547d\u4ee4\u4ecb\u7ecd:<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">xtrabackup\ninnobackupex    ******\n<\/code><\/pre>\n<h2>10.3 \u5907\u4efd\u65b9\u5f0f\u2014\u2014\u7269\u7406\u5907\u4efd<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">\uff081\uff09\u5bf9\u4e8e\u975eInnodb\u8868\uff08\u6bd4\u5982 myisam\uff09\u65f6\uff0c\u9501\u8868cp\u6240\u6709\u975einnoDB\u8868\u6570\u636e\u6587\u4ef6\uff0c\u5c5e\u4e8e\u4e00\u79cd\u6e29\u5907\u4efd\u3002\n\uff082\uff09\u5bf9\u4e8eInnodb\u7684\u8868\uff08\u652f\u6301\u4e8b\u52a1\u7684\uff09\uff0c\u7acb\u5373\u89e6\u53d1checkpoint\uff0c\u7acb\u5373\u89e6\u53d1CKPT,copy\u6240\u6709InnoDB\u8868\u76f8\u5173\u7684\u6587\u4ef6(ibdata1,ibd,frm)\uff0c\u5e76\u4e14\u5c06\u5907\u4efd\u8fc7\u7a0b\u4e2d\u4ea7\u751f,\u65b0\u7684\u6570\u636e\u53d8\u5316\u7684\u90e8\u5206redo\u4e00\u8d77\u5907\u4efd\u8d70\uff0c\u5c5e\u4e8e\u70ed\u5907\u65b9\u5f0f\u3002\n\uff083\uff09\u5728\u6062\u590d\u65f6,xbk\u4f1a\u8c03\u7528InnoDB\u5f15\u64ce\u7684CSR\u8fc7\u7a0b,\u5c06\u6570\u636e\u548credo\u7684LSN\u8ffd\u5e73,\u7136\u540e\u8fdb\u884c\u4e00\u81f4\u6027\u6062\u590d.\n<\/code><\/pre>\n<h2>\u9762\u8bd5\u9898\uff1a xbk \u5728innodb\u8868\u5907\u4efd\u6062\u590d\u7684\u6d41\u7a0b<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">  0\u3001xbk\u5907\u4efd\u6267\u884c\u7684\u77ac\u95f4,\u7acb\u5373\u89e6\u53d1checkpoint,\u5df2\u63d0\u4ea4\u7684\u6570\u636e\u810f\u9875,\u4ece\u5185\u5b58\u5237\u5199\u5230\u78c1\u76d8,\u5e76\u8bb0\u5f55\u6b64\u65f6\u7684LSN\u53f7\n  1\u3001\u5907\u4efd\u65f6\uff0c\u62f7\u8d1d\u78c1\u76d8\u6570\u636e\u9875\uff0c\u5e76\u4e14\u8bb0\u5f55\u5907\u4efd\u8fc7\u7a0b\u4e2d\u4ea7\u751f\u7684redo\u548cundo\u4e00\u8d77\u62f7\u8d1d\u8d70,\u4e5f\u5c31\u662fcheckpoint LSN\u4e4b\u540e\u7684\u65e5\u5fd7\n  2\u3001\u5728\u6062\u590d\u4e4b\u524d\uff0c\u6a21\u62dfInnodb\u201c\u81ea\u52a8\u6545\u969c\u6062\u590d\u201d\u7684\u8fc7\u7a0b\uff0c\u5c06redo\uff08\u524d\u6eda\uff09\u4e0eundo\uff08\u56de\u6eda\uff09\u8fdb\u884c\u5e94\u7528\n  3\u3001\u6062\u590d\u8fc7\u7a0b\u662fcp \u5907\u4efd\u5230\u539f\u6765\u6570\u636e\u76ee\u5f55\u4e0b\n\n1.\u5907\u4efd\u7684\u8fc7\u7a0b\n(1)\u975eInnoDB\u8868,\u8fdb\u884c\u77ed\u6682\u7684\u9501\u8868,\u7136\u540eCopy\u6570\u636e\u6587\u4ef6\n(2) \u5bf9\u4e8eInnoDB\u8868,\u7acb\u5373\u51fa\u53d1checkpoint,\u4f1a\u7acb\u5373\u8bb0\u5f55\u4e00\u4e2aLSN, COPY\u6570\u636e\u6587\u4ef6.\n(3)\u5c06\u5907\u4efd\u8fc7\u7a0b\u4e2d\u4ea7\u751f\u7684redo\u8fdb\u884c\u622a\u53d6\u548c\u4fdd\u5b58,\u5e76\u8bb0\u5f55\u6b64\u65f6\u6700\u65b0\u7684LSN\n2.\u6062\u590d\u8fc7\u7a0b\n\u6a21\u62df\u4e86CSR\u7684\u5168\u8fc7\u7a0b,\u5728\u6062\u590d\u4e4b\u3002\u524d,\u5c06\u6570\u636e\u7684LSN\u53f7\u548credoLSN\u53f7\u8ffd\u5e73\n\u6062\u590d\u65b9\u6cd5\u5c31\u662f\u76f4\u63a5cp\u56de\u53bb\u5373\u53ef\n\n<\/code><\/pre>\n<h2>10.4\u3001innobackupex\u4f7f\u7528<\/h2>\n<h3>10.4.1 \u5168\u5907<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">[root@db01 backup]# innobackupex --host=127.0.0.1  --user=root --password=1  \/backup\/full\n\n<\/code><\/pre>\n<h3>\u81ea\u4e3b\u5b9a\u5236\u5907\u4efd\u8def\u5f84\u540d<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">[root@db01 backup]# innobackupex --host=127.0.0.1  --user=root --password=1 --no-timestamp \/backup\/full\n<\/code><\/pre>\n<h3>\u5907\u4efd\u96c6\u4e2d\u591a\u51fa\u6765\u7684\u6587\u4ef6\uff1a<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">-rw-r----- 1 root root       24 Jun 29 09:59 xtrabackup_binlog_info\n-rw-r----- 1 root root      119 Jun 29 09:59 xtrabackup_checkpoints\n-rw-r----- 1 root root      489 Jun 29 09:59 xtrabackup_info\n-rw-r----- 1 root root     2560 Jun 29 09:59 xtrabackup_logfile\n\nxtrabackup_binlog_info \uff1a\uff08\u5907\u4efd\u65f6\u523b\u7684binlog\u4f4d\u7f6e\uff09\n[root@db01 full]# cat xtrabackup_binlog_info \nmysql-bin.000003    536749\n79de40d3-5ff3-11e9-804a-000c2928f5dd:1-7\n\u8bb0\u5f55\u7684\u662f\u5907\u4efd\u65f6\u523b\uff0cbinlog\u7684\u6587\u4ef6\u540d\u5b57\u548c\u5f53\u65f6\u7684\u7ed3\u675f\u7684position\uff0c\u53ef\u4ee5\u7528\u6765\u4f5c\u4e3a\u622a\u53d6binlog\u65f6\u7684\u8d77\u70b9\u3002\n\nxtrabackup_checkpoints \uff1a\nbackup_type = full-backuped\nfrom_lsn = 0            \u4e0a\u6b21\u6240\u5230\u8fbe\u7684LSN\u53f7(\u5bf9\u4e8e\u5168\u5907\u5c31\u662f\u4ece0\u5f00\u59cb,\u5bf9\u4e8e\u589e\u91cf\u662f\u4e0a\u6b21\u5907\u4efd\u7ed3\u675f\u7684\u4f4d\u7f6e)\nto_lsn = 160683027      \u5907\u4efd\u5f00\u59cb\u65f6\u95f4(ckpt)\u70b9\u6570\u636e\u9875\u7684LSN    \nlast_lsn = 160683036    \u5907\u4efd\u7ed3\u675f\u540e\uff0credo\u65e5\u5fd7\u6700\u7ec8\u7684LSN\ncompact = 0\nrecover_binlog_info = 0\n\uff081\uff09\u5907\u4efd\u65f6\u523b\uff0c\u7acb\u5373\u5c06\u5df2\u7ecfcommit\u8fc7\u7684\uff0c\u5185\u5b58\u4e2d\u7684\u6570\u636e\u9875\u5237\u65b0\u5230\u78c1\u76d8(CKPT).\u5f00\u59cb\u5907\u4efd\u6570\u636e\uff0c\u6570\u636e\u6587\u4ef6\u7684LSN\u4f1a\u505c\u7559\u5728to_lsn\u4f4d\u7f6e\u3002\n\uff082\uff09\u5907\u4efd\u65f6\u523b\u6709\u53ef\u80fd\u4f1a\u6709\u5176\u4ed6\u7684\u6570\u636e\u5199\u5165\uff0c\u5df2\u5907\u8d70\u7684\u6570\u636e\u6587\u4ef6\u5c31\u4e0d\u4f1a\u518d\u53d1\u751f\u53d8\u5316\u4e86\u3002\n\uff083\uff09\u5728\u5907\u4efd\u8fc7\u7a0b\u4e2d\uff0c\u5907\u4efd\u8f6f\u4ef6\u4f1a\u4e00\u76f4\u76d1\u63a7\u7740redo\u7684undo\uff0c\u5982\u679c\u4e00\u65e6\u6709\u53d8\u5316\u4f1a\u5c06\u65e5\u5fd7\u4e5f\u4e00\u5e76\u5907\u8d70\uff0c\u5e76\u8bb0\u5f55LSN\u5230last_lsn\u3002\n\u4eceto_lsn  ----\u300blast_lsn \u5c31\u662f\uff0c\u5907\u4efd\u8fc7\u7a0b\u4e2d\u4ea7\u751f\u7684\u6570\u636e\u53d8\u5316.\n<\/code><\/pre>\n<h3>10.4.2 \u5168\u5907\u7684\u6062\u590d<\/h3>\n<h4>\u51c6\u5907\u5907\u4efd\uff08Prepared\uff09<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">\u5c06redo\u8fdb\u884c\u91cd\u505a\uff0c\u5df2\u63d0\u4ea4\u7684\u5199\u5230\u6570\u636e\u6587\u4ef6\uff0c\u672a\u63d0\u4ea4\u7684\u4f7f\u7528undo\u56de\u6eda\u6389\u3002\u6a21\u62df\u4e86CSR\u7684\u8fc7\u7a0b\n[root@db01 ~]# innobackupex --apply-log  \/backup\/full\n<\/code><\/pre>\n<h4>\u6062\u590d\u5907\u4efd<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u524d\u63d0\uff1a\n1\u3001\u88ab\u6062\u590d\u7684\u76ee\u5f55\u662f\u7a7a\n2\u3001\u88ab\u6062\u590d\u7684\u6570\u636e\u5e93\u7684\u5b9e\u4f8b\u662f\u5173\u95ed\nsystemctl stop mysqld\n<\/code><\/pre>\n<p>\u521b\u5efa\u65b0\u76ee\u5f55<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">[root@db01 backup]# mkdir \/data\/mysql1\n<\/code><\/pre>\n<h4>\u6570\u636e\u6388\u6743<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-kotlin\" lang=\"kotlin\">chown -R mysql.mysql \/data\/mysql1\n<\/code><\/pre>\n<h4>\u6062\u590d\u5907\u4efd<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">[root@db01 full]# cp -a \/backup\/full\/* \/data\/mysql1\/\n<\/code><\/pre>\n<h4>\u542f\u52a8\u6570\u636e\u5e93<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-kotlin\" lang=\"kotlin\">vim \/etc\/my.cnf\ndatadir=\/data\/mysql1\n[root@db01 mysql1]# chown -R mysql.mysql \/data\/mysql1\nsystemctl start mysqld\n<\/code><\/pre>\n<h3>10.4.3 innobackupex \u589e\u91cf\u5907\u4efd(incremental)<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\uff081\uff09\u589e\u91cf\u5907\u4efd\u7684\u65b9\u5f0f\uff0c\u662f\u57fa\u4e8e\u4e0a\u4e00\u6b21\u5907\u4efd\u8fdb\u884c\u589e\u91cf\u3002\n\uff082\uff09\u589e\u91cf\u5907\u4efd\u65e0\u6cd5\u5355\u72ec\u6062\u590d\u3002\u5fc5\u987b\u57fa\u4e8e\u5168\u5907\u8fdb\u884c\u6062\u590d\u3002\n\uff083\uff09\u6240\u6709\u589e\u91cf\u5fc5\u987b\u8981\u6309\u987a\u5e8f\u5408\u5e76\u5230\u5168\u5907\u4e2d\u3002\n<\/code><\/pre>\n<h4>\u589e\u91cf\u5907\u4efd\u547d\u4ee4<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">\uff081\uff09\u5220\u6389\u539f\u6765\u5907\u4efd\n rm -rf \/backup\/*\n \n\uff082\uff09\u5168\u5907\uff08\u5468\u65e5\uff09\n[root@db01 backup]# innobackupex --host=127.0.0.1  --user=root --password=1 --no-timestamp \/backup\/full\n\uff083\uff09\u6a21\u62df\u5468\u4e00\u6570\u636e\u53d8\u5316\ndb01 [(none)]&gt;create database inc1 charset utf8mb4;\ndb01 [(none)]&gt;use inc1\nmysql&gt;create table t1 (id int);\nmysql&gt;insert into t1 values(1),(2),(3);\nmysql&gt;commit;\n\n\uff084\uff09\u7b2c\u4e00\u6b21\u589e\u91cf\u5907\u4efd\uff08\u5468\u4e00\uff09\ninnobackupex --host=127.0.0.1  --user=root --password=1 --no-timestamp --incremental  --incremental-basedir=\/backup\/full \/backup\/inc1\n\n--inccremental\t\u589e\u91cf\u5907\u4efd\u5f00\u5173\n--inccremental-basedir=\/backup\/full   \u57fa\u4e8e\u90a3\u4e2a\u5907\u4efd\u8fdb\u884c\u589e\u91cf\n\/backup\/inc1\t\u589e\u91cf\u5907\u4efd\u4f4d\u7f6e\n    \n\uff085\uff09\u68c0\u67e5\u589e\u91cf\u5907\u4efd\n[root@db01 inc1 ]$ cat \/backup\/full\/xtrabackup_checkpoints\nbackup_type = full-backuped\nfrom_lsn = 0\nto_lsn = 2842887\nlast_lsn = 2842896\ncompact = 0\nrecover_binlog_info = 0\n[root@db01 inc1 ]$ cat \/backup\/inc1\/xtrabackup_checkpoints\nbackup_type = incremental\nfrom_lsn = 2842887\nto_lsn = 2848802\nlast_lsn = 2848811\ncompact = 0\nrecover_binlog_info = 0\n\n \u4e0a\u4e00\u6b21\u5907\u4efdlast_lsn = 2842896 \u51cf\u53bb 9 \u7b49\u4e8e \u672c\u6b21\u5907\u4efdfrom_lsn = 2842887\n    \n\uff086\uff09\u6a21\u62df\u5468\u4e8c\u6570\u636e\nmysql&gt;create database inc2 charset utf8mb4;\nmysql&gt;create table t2 (id int);\nmysql&gt;insert into t2 values(1),(2),(3);\nmysql&gt;commit;\n\n\uff087\uff09\u5468\u4e8c\u589e\u91cf\n innobackupex --host=127.0.0.1  --user=root --password=1 --no-timestamp --incremental  --incremental-basedir=\/backup\/inc1 \/backup\/inc2\n    \n\uff088\uff09\u68c0\u67e5\u589e\u91cf\u5907\u4efd\n[root@db01 \/backup ]$ cat \/backup\/inc1\/xtrabackup_checkpoints\nbackup_type = incremental\nfrom_lsn = 2842887\nto_lsn = 2848802\nlast_lsn = 2848811\ncompact = 0\nrecover_binlog_info = 0\n[root@db01 \/backup ]$ cat \/backup\/inc2\/xtrabackup_checkpoints\nbackup_type = incremental\nfrom_lsn = 2848802\nto_lsn = 2854690\nlast_lsn = 2854699\ncompact = 0\nrecover_binlog_info = 0\n\n\u4e0a\u4e00\u6b21\u5907\u4efdlast_lsn = 2848811 \u51cf\u53bb 9 \u7b49\u4e8e \u672c\u6b21\u5907\u4efd from_lsn = 2848802\n\n\uff089\uff09\u6a21\u62df\u5468\u4e09\u6570\u636e\u53d8\u5316\nmysql&gt;create database inc3 charset utf8mb4;\nmysql&gt;create table t3 (id int);\nmysql&gt;insert into t3 values(1),(2),(3);\nmysql&gt;commit;\n\n(10)\u6a21\u62df\u4e0a\u534810\u70b9\u6570\u636e\u5e93\u5d29\u6e83\npkill mysqld\n\\rm -rf \/data\/mysql\/data\/*\n<\/code><\/pre>\n<h4>\u6062\u590d\u5230\u5468\u4e09\u8befdrop\u4e4b\u524d\u7684\u6570\u636e\u72b6\u6001<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u6062\u590d\u601d\u8def\uff1a\n1.\u505c\u4e1a\u52a1,\u6302\u7ef4\u62a4\u9875\n2.\u67e5\u627e\u53ef\u7528\u5907\u4efd\u5e76\u5904\u7406\u5907\u4efd:full+inc1+inc2\n3. binlog: inc2 \u5230\u6545\u969c\u65f6\u95f4\u70b9\u7684binlog\n4.\u6062\u590d\u5168\u5907+\u589e\u91cf+binlog\n5.\u9a8c\u8bc1\u6570\u636e\n6.\u8d77\u4e1a\u52a1,\u64a4\u7ef4\u62a4\u9875\n<\/code><\/pre>\n<h4>\u6062\u590d\u8fc7\u7a0b<\/h4>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">\u6062\u590d\u524d\u7684\u51c6\u5907\n(1) \u6574\u7406full\ninnobackupex --apply-log --redo-only \/backup\/full\n\n--redo-only\t\u5f53\u4f5c\u4e3a\u589e\u91cf\u5907\u4efd\u57fa\u7840\u7684\u65f6\u5019\u52a0\u4e0a\uff0c\u9664\u4e86\u6700\u540e\u4e00\u6b21\u589e\u91cf\u5907\u4efd\n\n(2)\u5408\u5e76inc1\u5230full,\u5e76\u6574\u7406\u5907\u4efd\ninnobackupex --apply-log --redo-only --incremental-dir=\/backup\/inc1  \/backup\/full \n\n[root@db01 ~ ]$ cat \/backup\/full\/xtrabackup_checkpoints\nbackup_type = log-applied\nfrom_lsn = 0\nto_lsn = 2848802\nlast_lsn = 2848811\ncompact = 0\nrecover_binlog_info = 0\n[root@db01 ~ ]$ cat \/backup\/inc1\/xtrabackup_checkpoints\nbackup_type = incremental\nfrom_lsn = 2842887\nto_lsn = 2848802\nlast_lsn = 2848811\ncompact = 0\nrecover_binlog_info = 0\n\n\u5408\u5e76\u4e4b\u540e\uff0cfull\u5907\u4efdlast_lsn = 2848811 \u7b49\u4e8e\u589e\u91cf\u5907\u4efd last_lsn = 2848811\n\n(3)\u5408\u5e76inc2\u5230full,\u5e76\u6574\u7406\u5907\u4efd\ninnobackupex --apply-log  --incremental-dir=\/backup\/inc2 \/backup\/full\n\n[root@db01 ~ ]$ cat \/backup\/full\/xtrabackup_checkpoints\nbackup_type = full-prepared\nfrom_lsn = 0\nto_lsn = 2854690\nlast_lsn = 2854699\ncompact = 0\nrecover_binlog_info = 0\n[root@db01 ~ ]$ cat \/backup\/inc2\/xtrabackup_checkpoints                              \nbackup_type = incremental\nfrom_lsn = 2848802\nto_lsn = 2854690\nlast_lsn = 2854699\ncompact = 0\nrecover_binlog_info = 0\n\n\u5408\u5e76\u4e4b\u540e\uff0cfull\u5907\u4efdlast_lsn = 2854699 \u7b49\u4e8e\u589e\u91cf\u5907\u4efd last_lsn = 2854699\n\n(4) \u6700\u540e\u4e00\u6b21\u6574\u7406full\ninnobackupex --apply-log \/backup\/full\n\n\u622a\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7\n\u8d77\u70b9\uff1a[root@db01 inc2 ]$ cat xtrabackup_binlog_info  \nmysql-bin.000004\t1997\t2fa25f12-271a-11eb-9b2f-000c2943bd92:1-20\n\u7ec8\u70b9\uff1a[root@db01 inc2 ]$ mysqlbinlog \/data\/binlog\/mysql-bin.000004 |grep 'SET' \n\nSET @@SESSION.GTID_NEXT= '2fa25f12-271a-11eb-9b2f-000c2943bd92:23'\/*!*\/;\n\n\u622a\u53d6\u65e5\u5fd7\uff1a[root@db01 inc2 ]$ mysqlbinlog --skip-gtids --include-gtids='2fa25f12-271a-11eb-9b2f-000c2943bd92:21-23' \/data\/binlog\/mysql-bin.000004 &gt;\/tmp\/binlog.sql\n\n\n\u8fdb\u884c\u6062\u590d\n[root@db01 backup]# mkdir \/data\/mysql\/data -p\n[root@db01 full]# cp -a * \/data\/mysql\/data\n[root@db01 backup]# chown -R mysql.mysql  \/data\/*\n[root@db01 backup]# service mysqld start\nMaster [(none)]&gt;set sql_log_bin=0;\nMaster [(none)]&gt;source \/data\/backup\/binlog.sql\n\n\u9a8c\u8bc1\u6570\u636e\nmysql&gt; select *from inc1.t1;\nmysql&gt; select *from inc2.t1;\nmysql&gt; select *from inc3.t1;\n<\/code><\/pre>\n<h1>11.\u8fc1\u79fb\uff085.6.44&#8211;&gt;5.7.26\uff09<\/h1>\n<h3>11.1\u642d\u5efa5.6\u7684\u6d4b\u8bd5\u73af\u5883<\/h3>\n<p>(1)\u521b\u5efa\u5fc5\u987b\u7684\u76ee\u5f55<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">[root@cuoni ~]# mkdir \/data\/mysql\/data -p\n[root@cuoni ~]# mkdir \/application\/ -p\n[root@cuoni ~]# mkdir \/data\/binlog -p\n<\/code><\/pre>\n<p>\u4e0a\u4f20\u8f6f\u4ef6\u81f3\/application \u4e0b<br \/>\n(2)\u5efa\u7528\u6237,\u6539\u6743\u9650<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">[root@cuoni ~]# useradd mysql\n[root@cuoni ~]# chown -R mysql. \/data\/application\/\n<\/code><\/pre>\n<p>\uff083\uff09\u4fee\u6539\u73af\u5883\u53d8\u91cf<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">vim \/etc\/profile \n\nexport PATH=\/application\/mysql\/bin:$PATH;\n\nsource \/etc\/profile \n<\/code><\/pre>\n<p>\uff084\uff09\u6570\u636e\u521d\u59cb\u5316<\/p>\n<p>\u5148\u5378\u8f7d\u81ea\u5e26\u7684mairadb<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">\/application\/mysql\/scripts\/mysql_install_db --user=mysql --basedir=\/application\/mysql --datadir=\/data\/mysql\/data\n\n<\/code><\/pre>\n<p>(5)\u51c6\u5907\u914d\u7f6e\u6587\u4ef6\u548c\u542f\u52a8\u811a\u672c<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">vim \/etc\/my.cnf\n\n[mysqld]\nuser=mysql\nbasedir=\/application\/mysql\ndatadir=\/data\/mysql\/data\nsocket=\/tmp\/mysql.sock\nserver_id=6\nport=3306\nautocommit=0\nlog_error=\/tmp\/mysql3306.log\nlog_bin=\/data\/binlog\/mysql-bin\nbinlog_format=row\ngtid-mode=on\nenforce-gtid-consistency=true\n#\u6162\u65e5\u5fd7\u5f00\u5173:\nslow_query_log=1\n#\u6587\u4ef6\u4f4d\u7f6e\u53ca\u540d\u5b57\nslow_query_log_file=\/data\/mysql\/slow.log\n#\u8bbe\u5b9a\u6162\u67e5\u8be2\u65f6\u95f4:\nlong_query_time=0.1\n#\u6ca1\u8d70\u7d22\u5f15\u7684\u8bed\u53e5\u4e5f\u8bb0\u5f55:\nlog_queries_not_using_indexes\n#\u5bfc\u51fa\u6570\u636e\u7684\u9650\u5236\nsecure-file-priv=\n\n[mysql]\nsocket=\/tmp\/mysql.sock\n\n\ncp \/application\/mysql\/support-files\/mysql.server \/etc\/init.d\/mysqld\n\n<\/code><\/pre>\n<p>(6)\u542f\u52a8\u6570\u636e\u5e93<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">\/etc\/init.d\/mysqld start\n\nmysql\n\nmysqladmin -uroot -p password 1\n\n<\/code><\/pre>\n<h3>11.2\u8fc1\u79fb5.6\u6570\u636e\u52305.7<\/h3>\n<p>\uff081\uff09\u5907\u4efd5.6\u6570\u636e<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">mysqldump -uroot -p123 -A - -master-data=2 --single-t ransaction -R -E --triggers &gt; \/tmp\/full.sql \n\n<\/code><\/pre>\n<p>(2)\u51c6\u59075.7\u6570\u636e\u5e93<\/p>\n<p>\u4f20\u8f935.6\u5907\u4efd\u6570\u636e\u52305.7\u670d\u52a1\u5668<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">scp \/tmp\/full root@10.0.0.51:\/tmp\n<\/code><\/pre>\n<p>\u542f\u52a8\u6570\u636e\u5e93\u5e76\u767b\u9646<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">mysql -uroot -1\n<\/code><\/pre>\n<p>\u5bfc\u5165\u6570\u636e\u5e76\u66f4\u65b0<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">mysql&gt;source \/tmp\/full.sql \n\n[root@ db01 \/] # mysql_upgrade -uroot -p1 -S \/data\/3308\/mysq1.sock\n\n<\/code><\/pre>\n<p>(3)binlog\u6301\u7eed\u8ffd\u52a0<\/p>\n<p>(4)\u505c\u4e1a\u52a1\uff0c\u6062\u590d\u5269\u4f59binlog<\/p>\n<p>(5)\u4e1a\u52a1\u5272\u63a5<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>mysql\u5907\u4efd\u4e0e\u6062\u590d 1. \u8fd0\u7ef4\u5728\u6570\u636e\u5e93\u5907\u4efd\u6062\u590d\u65b9\u9762\u7684\u804c\u8d23 1.1 \u8bbe\u8ba1\u5907\u4efd\u7b56\u7565 &nbsp; \u5907\u4efd\u5468\u671f:\u6839\u636e [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-63","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/63","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=63"}],"version-history":[{"count":1,"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/63\/revisions"}],"predecessor-version":[{"id":64,"href":"https:\/\/daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/63\/revisions\/64"}],"wp:attachment":[{"href":"https:\/\/daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=63"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=63"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=63"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}