{"id":385,"date":"2023-11-26T21:50:41","date_gmt":"2023-11-26T13:50:41","guid":{"rendered":"http:\/\/8.141.4.74\/?p=385"},"modified":"2024-03-29T14:39:04","modified_gmt":"2024-03-29T06:39:04","slug":"mysql%e7%9a%84%e5%a4%9a%e7%a7%8d%e4%b8%bb%e4%bb%8e%e5%a4%8d%e5%88%b6%e6%9e%b6%e6%9e%84","status":"publish","type":"post","link":"http:\/\/8.141.4.74\/?p=385","title":{"rendered":"MYSQL\u7684\u591a\u79cd\u4e3b\u4ece\u590d\u5236\u67b6\u6784"},"content":{"rendered":"

1. MySQL\u5f00\u542f\u4e8c\u8fdb\u5236<\/h2>\n
# \u67e5\u770b\u4e8c\u8fdb\u5236\u662f\u5426\u5f00\u542f\r\n(root@localhost) [hellodb]> select @@log_bin;\r\n+-----------+\r\n| @@log_bin |\r\n+-----------+\r\n|         1 |\r\n+-----------+\r\n1 row in set (0.01 sec)\r\n\r\n# \u4e8c\u8fdb\u5236\u65e5\u5fd7\u8bb0\u5f55\u7684\u683c\u5f0f\r\n(root@localhost) [hellodb]> select @@binlog_format;\r\nSTATEMENT:\u6bcf\u4e2a\u4f1a\u66f4\u6539\u6570\u636e\u7684SQL\u8bed\u53e5\u90fd\u4f1a\u88ab\u8bb0\u5f55\u3002\r\nROW:\u5bf9\u4e8e\u6bcf\u4e2a\u66f4\u6539\uff0c\u66f4\u6539\u7684\u884c\u7684\u5185\u5bb9\u90fd\u4f1a\u88ab\u8bb0\u5f55\u3002\r\nMIXED:MySQL\u51b3\u5b9a\u4f55\u65f6\u4f7f\u7528STATEMENT\u683c\u5f0f\uff0c\u4f55\u65f6\u4f7f\u7528ROW\u683c\u5f0f\u3002\r\n\r\n#log_bin\u548csql_log_bin\u7684\u533a\u522b\uff1a\r\nlog_bin:\r\n\u8fd9\u662f\u4e00\u4e2a\u670d\u52a1\u5668\u7cfb\u7edf\u53d8\u91cf\u3002\r\n\u5f53\u4f60\u8bbe\u7f6e\u4e86 log_bin\uff0cMySQL \u670d\u52a1\u5668\u4f1a\u542f\u52a8\u4e8c\u8fdb\u5236\u65e5\u5fd7\u529f\u80fd\uff0c\u8bb0\u5f55\u6240\u6709\u6570\u636e\u5e93\u7684\u66f4\u6539\u3002\r\n\u8fd9\u4e3b\u8981\u7528\u4e8e\u590d\u5236\u548c\u6062\u590d\u7684\u76ee\u7684\u3002\u5982\u679c\u4f60\u5e0c\u671b\u4ece\u4e3b\u670d\u52a1\u5668\u590d\u5236\u6570\u636e\u5230\u4ece\u670d\u52a1\u5668\uff0c\u4f60\u9700\u8981\u5728\u4e3b\u670d\u52a1\u5668\u4e0a\u542f\u7528 log_bin\u3002\r\n\u8be5\u53d8\u91cf\u8fd8\u6307\u5b9a\u4e86\u65e5\u5fd7\u6587\u4ef6\u7684\u57fa\u672c\u540d\u3002\u4f8b\u5982\uff0c\u5982\u679c\u4f60\u8bbe\u7f6e log_bin=\/var\/log\/mysql\/mysql-bin, \u90a3\u4e48\u4f60\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\u53ef\u80fd\u4f1a\u662f mysql-bin.000001, mysql-bin.000002, \u7b49\u7b49\u3002\r\n\r\nsql_log_bin:\r\n\u8fd9\u662f\u4e00\u4e2a\u4f1a\u8bdd\u7ea7\u522b\u7684\u53d8\u91cf\u3002\r\n\u5f53 sql_log_bin \u8bbe\u7f6e\u4e3a TRUE\uff08\u9ed8\u8ba4\u503c\uff09\uff0c\u90a3\u4e48\u5728\u5f53\u524d\u4f1a\u8bdd\u4e2d\u8fdb\u884c\u7684\u66f4\u6539\u5c06\u8bb0\u5f55\u5230\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4e2d\uff08\u524d\u63d0\u662f log_bin \u4e5f\u5df2\u542f\u7528\uff09\u3002\r\n\u5982\u679c\u4f60\u5e0c\u671b\u67d0\u4e2a\u7279\u5b9a\u7684\u4f1a\u8bdd\u4e2d\u7684\u66f4\u6539\u4e0d\u8bb0\u5f55\u5230\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4e2d\uff0c\u53ef\u4ee5\u8bbe\u7f6e SET sql_log_bin = 0; \u4ee5\u7981\u7528\u5b83\u3002\u8fd9\u5bf9\u4e8e\u67d0\u4e9b\u7ba1\u7406\u4efb\u52a1\uff0c\u4f8b\u5982\u5907\u4efd\u6216\u5bfc\u5165\u5927\u91cf\u6570\u636e\uff0c\u53ef\u80fd\u662f\u6709\u7528\u7684\uff0c\u56e0\u4e3a\u8fd9\u6837\u53ef\u4ee5\u9632\u6b62\u5c06\u8fd9\u4e9b\u5927\u91cf\u7684\u66f4\u6539\u590d\u5236\u5230\u4ece\u670d\u52a1\u5668\u4e0a\u3002\r\n\u8bf7\u6ce8\u610f\uff0c\u53ea\u6709\u5177\u6709 SUPER \u6743\u9650\u7684\u7528\u6237\u624d\u80fd\u66f4\u6539 sql_log_bin\u3002\r\n\r\n# \u4e34\u65f6\u5173\u95ed\u4e8c\u8fdb\u5236\u65e5\u5fd7\r\n set sql_log_bin=0;\r\n\r\n# \u67e5\u770b\u5f53\u524d\u62e5\u6709\u7684MYSQL\u4e8c\u8fdb\u5236\u65e5\u65e5\u5fd7\r\n(root@localhost) [hellodb]> show master logs;\r\n+------------------+-----------+\r\n| Log_name         | File_size |\r\n+------------------+-----------+\r\n| mysql-bin.000001 |       328 |\r\n| mysql-bin.000002 |       375 |\r\n| mysql-bin.000003 |       394 |\r\n| mysql-bin.000004 |       351 |\r\n| mysql-bin.000005 |       601 |\r\n| mysql-bin.000006 |       432 |\r\n| mysql-bin.000007 |       385 |\r\n+------------------+-----------+\r\n7 rows in set (0.000 sec)\r\n\r\n(root@localhost) [hellodb]> \r\n\r\n# \u4e5f\u53ef\u4ee5\u67e5\u770b\u4e8c\u8fdb\u5236\u6587\u4ef6\u7684\u4f4d\u7f6e\r\n[root@Rocky9 logbin]# ll \/data\/logbin\/ -h\r\ntotal 32K\r\n-rw-rw---- 1 mysql mysql 328 Sep 20 14:32 mysql-bin.000001\r\n-rw-rw---- 1 mysql mysql 375 Sep 25 11:38 mysql-bin.000002\r\n-rw-rw---- 1 mysql mysql 394 Sep 30 15:32 mysql-bin.000003\r\n-rw-rw---- 1 mysql mysql 351 Oct  2 20:33 mysql-bin.000004\r\n-rw-rw---- 1 mysql mysql 601 Oct  3 00:00 mysql-bin.000005\r\n-rw-rw---- 1 mysql mysql 432 Oct  7 11:14 mysql-bin.000006\r\n-rw-rw---- 1 mysql mysql 385 Oct  7 11:14 mysql-bin.000007\r\n-rw-rw---- 1 mysql mysql 210 Oct  7 11:14 mysql-bin.index\r\n[root@Rocky9 logbin]# \r\n# \u67e5\u770b\u5f53\u524dMYSQL\u4f7f\u7528\u7684\u4e8c\u8fdb\u5236\r\n(root@localhost) [hellodb]> show master status;\r\n+------------------+----------+--------------+------------------+\r\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+------------------+----------+--------------+------------------+\r\n| mysql-bin.000007 |      385 |              |                  |\r\n+------------------+----------+--------------+------------------+\r\n1 row in set (0.000 sec)\r\n(root@localhost) [hellodb]> \r\n# \u67e5\u770b\u4e8c\u8fdb\u5236\u6587\u4ef6\u6700\u5927\u7684\u5bb9\u91cf(\u8fbe\u5230\u8fd9\u4e2a\u6700\u5927\u7684size\u6216\u8005\u91cd\u542f\u6570\u636e\u5e93\u670d\u52a1\u90fd\u4f1a\u91cd\u65b0\u751f\u6210\u4e00\u4e2a\u4e8c\u8fdb\u5236\u6587\u4ef6)\r\n(root@localhost) [hellodb]> select @@max_binlog_size;\r\n+-------------------+\r\n| @@max_binlog_size |\r\n+-------------------+\r\n|        1073741824 |\r\n+-------------------+\r\n1 row in set (0.018 sec)\r\n\r\n(root@localhost) [hellodb]> \r\n# \u5728\u7ebf\u67e5\u770b\u6307\u5b9a\u4e8c\u8fdb\u5236\u6587\u4ef6\u7684\u5185\u5bb9\r\n(root@localhost) [hellodb]> show binlog events in 'mysql-bin.000008';\r\n+------------------+-----+-------------------+-----------+-------------+-----------------------------------------------------------+\r\n| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                                      |\r\n+------------------+-----+-------------------+-----------+-------------+-----------------------------------------------------------+\r\n| mysql-bin.000008 |   4 | Format_desc       |         1 |         256 | Server ver: 10.5.16-MariaDB-log, Binlog ver: 4            |\r\n| mysql-bin.000008 | 256 | Gtid_list         |         1 |         299 | [0-1-1]                                                   |\r\n| mysql-bin.000008 | 299 | Binlog_checkpoint |         1 |         342 | mysql-bin.000008                                          |\r\n| mysql-bin.000008 | 342 | Gtid              |         1 |         384 | BEGIN GTID 0-1-2                                          |\r\n| mysql-bin.000008 | 384 | Intvar            |         1 |         416 | INSERT_ID=10                                              |\r\n| mysql-bin.000008 | 416 | Query             |         1 |         528 | use `hellodb`; insert teachers values(null,'test',22,'F') |\r\n| mysql-bin.000008 | 528 | Xid               |         1 |         559 | COMMIT \/* xid=20 *\/                                       |\r\n+------------------+-----+-------------------+-----------+-------------+-----------------------------------------------------------+\r\n7 rows in set (0.000 sec)\r\n\r\n(root@localhost) [hellodb]> \r\n# \u4e0d\u767b\u5f55\u6570\u636e\u5e93\u67e5\u770b\u4e8c\u8fdb\u5236\u6587\u4ef6\u7684\u5185\u5bb9\uff0c\u4e0d\u6307\u5b9a--start-position\u548c--stop-position\u7684\u8bdd\uff0c\u90a3\u4e48\u5c31\u662f\u67e5\u770b\u5168\u90e8\u6587\u4ef6\u7684\u5185\u5bb9\r\n[root@Rocky9 logbin]# mysqlbinlog \/data\/logbin\/mysql-bin.000008 --start-position=516 --stop-position=528\r\n\/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*\/;\r\n\/*!40019 SET @@session.max_insert_delayed_threads=0*\/;\r\n\/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*\/;\r\nDELIMITER \/*!*\/;\r\n# at 4\r\n#231008 11:50:27 server id 1  end_log_pos 256 CRC32 0x23800a0e  Start: binlog v 4, server v 10.5.16-MariaDB-log created 231008 11:50:27 at startup\r\n# Warning: this binlog is either in use or was not closed properly.\r\nROLLBACK\/*!*\/;\r\nBINLOG '\r\nAyciZQ8BAAAA\/AAAAAABAAABAAQAMTAuNS4xNi1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA\r\nAAAAAAAAAAAAAAAAAAADJyJlEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA\r\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\r\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\r\nAAAAAAAAAAAEEwQADQgICAoKCgEOCoAj\r\n'\/*!*\/;\r\nERROR: Error in Log_event::read_log_event(): 'Event truncated', data_len: 2099353352, event_type: 39\r\nDELIMITER ;\r\n# End of log file\r\nROLLBACK \/* added by mysqlbinlog *\/;\r\n\/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*\/;\r\n\/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*\/;\r\n[root@Rocky9 logbin]# \r\n# \u6e05\u7406\u67d0\u4e2a\u4e8c\u8fdb\u5236\u6587\u4ef6\u4e4b\u524d\u7684\u65e5\u5fd7\u6587\u4ef6\r\n(root@localhost) [hellodb]> purge binary logs to 'mysql-bin.000009';\r\nQuery OK, 0 rows affected (0.021 sec)\r\n(root@localhost) [hellodb]> show master logs;\r\n+------------------+-----------+\r\n| Log_name         | File_size |\r\n+------------------+-----------+\r\n| mysql-bin.000009 |       554 |\r\n| mysql-bin.000010 |      1439 |\r\n+------------------+-----------+\r\n2 rows in set (0.000 sec)\r\n\r\n(root@localhost) [hellodb]> \r\n# \u6e05\u9664\u6240\u6709\u4e8c\u8fdb\u5236\u65e5\u5fd7\r\n(root@localhost) [hellodb]> reset master;\r\nQuery OK, 0 rows affected (0.005 sec)\r\n\r\n(root@localhost) [hellodb]> show master logs;\r\n+------------------+-----------+\r\n| Log_name         | File_size |\r\n+------------------+-----------+\r\n| mysql-bin.000001 |       328 |\r\n+------------------+-----------+\r\n1 row in set (0.000 sec)\r\n\r\n(root@localhost) [hellodb]> \r\n[root@Rocky9 logbin]# ll\r\ntotal 8\r\n-rw-rw---- 1 mysql mysql 328 Oct  8 14:25 mysql-bin.000001\r\n-rw-rw---- 1 mysql mysql  30 Oct  8 14:25 mysql-bin.index\r\n[root@Rocky9 logbin]# cat mysql-bin.index\r\n\/data\/logbin\/mysql-bin.000001\r\n[root@Rocky9 logbin]# \r\n# \u5237\u65b0\u65e5\u5fd7\uff0c\u91cd\u65b0\u751f\u6210\u4e00\u4e2a\u4e8c\u8fdb\u5236\u65e5\u5fd7\r\n(root@localhost) [hellodb]> show master logs;\r\n+------------------+-----------+\r\n| Log_name         | File_size |\r\n+------------------+-----------+\r\n| mysql-bin.000001 |       328 |\r\n+------------------+-----------+\r\n1 row in set (0.000 sec)\r\n\r\n(root@localhost) [hellodb]> flush logs;\r\nQuery OK, 0 rows affected (0.007 sec)\r\n\r\n(root@localhost) [hellodb]> show master logs;\r\n+------------------+-----------+\r\n| Log_name         | File_size |\r\n+------------------+-----------+\r\n| mysql-bin.000001 |       375 |\r\n| mysql-bin.000002 |       371 |\r\n+------------------+-----------+\r\n2 rows in set (0.000 sec)\r\n\r\n(root@localhost) [hellodb]> \r\n# \u6216\u8005\r\nmysqladmin -uroot -pxxxx flush-logs<\/code><\/pre>\n
mysql\u4fee\u6539\u547d\u4ee4\u63d0\u793a\u7b26<\/h3>\n
# \u5f53\u6211\u4eec\u5b89\u88c5\u597dmysql\u670d\u52a1\u5668\u7684\u65f6\u5019\uff0c\u9700\u8981\u4f7f\u7528\u201cmysql\u201d\u8fd9\u4e2a\u5ba2\u6237\u7aef\u547d\u4ee4\u6765\u67e5\u770b\u76f8\u5e94\u7684\u8bbe\u7f6e\uff0c\u4f46\u662f\u63d0\u793a\u7b26\u663e\u793a\u4e0d\u662f\u5f88\u53cb\u597d\u3002\r\neg:\r\nmysql >\r\n\r\n# \u8fdb\u5165mysql\u7684\u914d\u7f6e\u76ee\u5f55\r\ncd \/etc\/my.cnf.d\r\n\r\n# \u5728\u8be5\u76ee\u5f55\u4e0b\u521b\u5efa\u4efb\u4f55\u6587\u4ef6mysql\u90fd\u4f1a\u8bc6\u522b\u5e76\u4e14\u52a0\u8f7d\r\ntouch mysql.cnf\r\n\r\n[mysql]\r\nprompt=(\\\\u@\\\\h mysql\\\\v) [\\\\d]>\\\\_\r\n\r\n# \u89e3\u91ca\uff1a\r\n\u5728MySQL\u7684\u914d\u7f6e\u6587\u4ef6\u6216\u8005\u4ea4\u4e92\u5f0f\u63d0\u793a\u7b26\u4e2d\uff0c\u8fd9\u4e9b\u662f\u7528\u4e8e\u8bbe\u7f6eMySQL\u547d\u4ee4\u884c\u5ba2\u6237\u7aef\u63d0\u793a\u7b26\u7684\u8f6c\u4e49\u5e8f\u5217\u3002\r\n\r\n\\\\u: \u5f53\u524dMySQL\u7528\u6237\u3002\r\n\\\\h: \u8fde\u63a5\u7684MySQL\u670d\u52a1\u5668\u7684\u4e3b\u673a\u540d\u3002\r\n\\\\d: \u5f53\u524d\u9009\u62e9\u7684\u6570\u636e\u5e93\u3002\r\n\\\\_: \u663e\u793a\u4e00\u4e2a'>'\u5b57\u7b26\u3002\r\n\\\\c: \u663e\u793aMySQL\u547d\u4ee4\u8ba1\u6570\u5668\u3002\r\n\\\\l: \u663e\u793a\u5f53\u524ddelimiter\u5b57\u7b26\u3002\r\n\\\\p: \u663e\u793a\u5f53\u524d\u7aef\u53e3\u3002\r\n\\\\P: \u663e\u793a\u5f53\u524dmysqlnd\u5e93\u7aef\u53e3\u3002\r\n\\\\r: \u663e\u793a\u5f53\u524d\u534f\u8bae\u7248\u672c\u3002\r\n\\\\s: \u663e\u793a\u5f53\u524d\u670d\u52a1\u5668\u7248\u672c\u3002\r\n\\\\v: \u663e\u793a\u5f53\u524d\u7248\u672c\u4fe1\u606f\u3002\r\n\\\\n: \u65b0\u884c\u3002\r\n\\\\t: \u5236\u8868\u7b26\r\n\r\n#\u518d\u6b21\u8fdb\u5165\u53d1\u73b0\u63d0\u793a\u7b26\u5df2\u7ecf\u53d8\u5316\u4e86\r\nroot@localhost mysql8.0.32) [(none)]><\/code><\/pre>\n
2. MySQL\u5907\u4efd\u4e0e\u8fd8\u539f<\/h2>\n
# \u5907\u4efd\u7c7b\u578b\r\n1.\u5b8c\u5168\u5907\u4efd\r\n2.\u589e\u91cf\u5907\u4efd\uff1a\u5907\u4efd\u7684\u901f\u5ea6\u7684\u5feb\uff0c\u8fd8\u539f\u9ebb\u70e6\r\n3.\u5dee\u5f02\u5907\u4efd\uff1a\u5907\u4efd\u7684\u8d8a\u6765\u8d8a\u6162\uff0c\u8fd8\u539f\u7b80\u5355(\u53ea\u9700\u8981\u8fd8\u539f\u7b2c\u4e00\u6b21\u7684\u5b8c\u5168\u5907\u4efd\u548c\u5dee\u5f02\u5907\u4efd)\uff0c\u5fc5\u987b\u5148\u505a\u4e00\u6b21\u5b8c\u5168\u5907\u4efd\r\n# \u589e\u91cf\u5907\u4efd\u548c\u5dee\u5f02\u5907\u4efd\u662f\u57fa\u4e8e\u5b8c\u5168\u5907\u4efd\u4e0a\u7684\r\n\r\n#MyISAM\uff1a\u652f\u6301\u6e29\u5907\uff0c\u4e0d\u652f\u6301\u70ed\u5907\uff0c\u56e0\u4e3aMyISAM\u4e0d\u652f\u6301\u4e8b\u52a1\uff0c\u65e0\u6cd5\u9694\u79bb\r\n\r\n# \u4e8b\u52a1(4\u4e2a\u9694\u79bb\u7ea7\u522b)\uff1a\r\n1.\u8bfb\u672a\u63d0\u4ea4(READ UNCOMMITTED)\r\n2.\u8bfb\u5df2\u63d0\u4ea4(READ COMMITTED)\r\n3.\u53ef\u91cd\u590d\u8bfb(REPEATABLE READ)\r\n4.\u4e32\u884c\u3001\u5e8f\u5217\u5316(SERIALIZABLE)\r\n\r\n#\u51b7\u5907\u4efd(Mariadb 10)\r\n# \u7b2c\u4e00\u6b65\uff1a\u5fc5\u987b\u505c\u6b62\u6570\u636e\u5e93\r\nsystemctl stop mysqld\r\n\r\n# \u7b2c\u4e8c\u6b69\uff1a\u4f7f\u7528rsync\u540c\u6b65\u5230\u8fdc\u7a0b\u4e3b\u673a\u4e0a\r\nrsync -a \/var\/lib\/mysql 10.0.0.134:\/data\/\r\n# \u5982\u679c\u914d\u7f6e\u6587\u4ef6\u4fee\u6539\u4e86\uff0c\u4e5f\u9700\u8981\u62f7\u8d1d\r\n# \u4f8b\u5982\uff1ascp \/etc\/my.cnf.d\/mariadb-server.cnf 10.0.0.134:\/etc\/my.cnf.d\/\r\n\r\n# \u7b2c\u4e09\u6b65\uff1a\u5b89\u88c5mysql\uff0c\u5c06\u5907\u4efd\u6587\u4ef6cp\u5230\u5bf9\u5e94\u7684mysql\u76ee\u5f55\u4e0b\r\nyum -y install mysql-server\r\ncp -a \/data\/mysql\/* \/var\/lib\/mysql\r\n\r\n# \u7b2c\u56db\u6b65\uff1a\u542f\u52a8\r\nsystemct enable --now mysqld\r\n\r\n# \u51b7\u5907\u4efd(Mysql 8.0)\r\n# \u53c2\u7167\u4e0a\u8ff0Mariadb\r\n\r\n#\u6e29\u5907\u4efd\r\n# \u4f7f\u7528mysqldump\u547d\u4ee4\u8fdb\u884c\u5907\u4efd\r\n# \u5907\u4efd\u6240\u6709\u6570\u636e\u5e93\r\nmysqldump -uroot -pxxxx -B\/--all-databases > \/data\/xxx.sql\r\n<\/code><\/pre>\n
\u5f00\u542f\u4e8c\u8fdb\u5236\u65e5\u5fd7<\/h3>\n
# mysql8.0\u9ed8\u8ba4\u5f00\u542f\u4e8c\u8fdb\u5236\u65e5\u5fd7\uff0c\u4f46\u662fMariadb 10\u9ed8\u8ba4\u4e0d\u5f00\u542f\u4e8c\u8fdb\u5236\u65e5\u5fd7\uff0c\u9700\u8981\u624b\u52a8\u5f00\u542f\r\nvim \/etc\/my.cnf.d\/mariadb-server.cnf\r\n[mysqld]\r\n#\u5f00\u542f\u4e8c\u8fdb\u5236\u65e5\u5fd7\r\nlog-bin=\/data\/logbin\/mysql-bin\r\n#\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u5b58\u50a8\u65b9\u5f0f\r\nbinlog_format=row\r\n#\u5c06\u4e0a\u9762\u7684\u6587\u4ef6\u5939\u521b\u5efa\u51fa\u6765\r\nmkdir \/data\/logbin\r\n#\u4fee\u6539\u6240\u6709\u8005\u6240\u5c5e\u7ec4\r\nchown mysql.mysql \/data\/logbin\r\n#\u91cd\u542f\u6570\u636e\u5e93\u670d\u52a1\r\nsystemctl restart mysqld<\/code><\/pre>\n
\u5b8c\u5168\u5907\u4efd+\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5907\u4efd<\/h3>\n
# \u5907\u4efd\u5e76\u538b\u7f29\r\n[root@Rocky9 ~]# mysqldump -A --master-data=2 | gzip > \/data\/all.sql.gz\r\n\r\n# \u767b\u5f55\u6570\u636e\u5e93\u505a\u589e\u5220\u64cd\u4f5c\r\n(root@localhost) [hellodb]> delete from teachers where tid=11;\r\n(root@localhost) [hellodb]> insert teachers values (null,'xiaoming',22,'F');\r\nQuery OK, 1 row affected (0.006 sec)\r\n\r\n(root@localhost) [hellodb]> insert teachers values (null,'xiaohong',23,'M');\r\nQuery OK, 1 row affected (0.013 sec)\r\n\r\n(root@localhost) [hellodb]> select * from teachers;\r\n+-----+---------------+-----+--------+\r\n| TID | Name          | Age | Gender |\r\n+-----+---------------+-----+--------+\r\n|   1 | Song Jiang    |  45 | M      |\r\n|   2 | Zhang Sanfeng |  94 | M      |\r\n|   3 | Miejue Shitai |  77 | F      |\r\n|   4 | Lin Chaoying  |  93 | F      |\r\n|  12 | xiaoming      |  22 | F      |\r\n|  13 | xiaohong      |  23 | M      |\r\n+-----+---------------+-----+--------+\r\n6 rows in set (0.001 sec)\r\n\r\n# \u786e\u5b9a\u5f53\u524d\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4f7f\u7528\u7684\u662f\u54ea\u4e2a\r\n(root@localhost) [hellodb]> show master status;\r\n+------------------+----------+--------------+------------------+\r\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+------------------+----------+--------------+------------------+\r\n| mysql-bin.000012 |     1120 |              |                  |\r\n+------------------+----------+--------------+------------------+\r\n1 row in set (0.000 sec)\r\n\r\n# \u67e5\u770b\u4e8c\u8fdb\u5236\u65e5\u5fd7\r\n[root@Rocky9 ~]# ll \/data\/logbin\/\r\ntotal 84\r\n-rw-rw---- 1 mysql mysql   375 Oct  8 14:27 mysql-bin.000001\r\n-rw-rw---- 1 mysql mysql   418 Oct  8 14:29 mysql-bin.000002\r\n-rw-rw---- 1 mysql mysql   925 Oct  9 09:00 mysql-bin.000003\r\n-rw-rw---- 1 mysql mysql   736 Oct  9 09:41 mysql-bin.000004\r\n-rw-rw---- 1 mysql mysql   342 Oct 12 13:45 mysql-bin.000005\r\n-rw-rw---- 1 mysql mysql   365 Oct 16 16:11 mysql-bin.000006\r\n-rw-rw---- 1 mysql mysql   389 Oct 17 09:08 mysql-bin.000007\r\n-rw-rw---- 1 mysql mysql   385 Oct 17 09:08 mysql-bin.000008\r\n-rw-rw---- 1 mysql mysql   389 Oct 19 09:15 mysql-bin.000009\r\n-rw-rw---- 1 mysql mysql   408 Oct 19 09:28 mysql-bin.000010\r\n-rw-rw---- 1 mysql mysql   389 Oct 20 09:57 mysql-bin.000011\r\n-rw-rw---- 1 mysql mysql  1143 Oct 20 11:17 mysql-bin.000012\r\n-rw-rw---- 1 mysql mysql 27411 Oct 20 11:18 mysql-bin.000013\r\n-rw-rw---- 1 mysql mysql   342 Oct 20 11:18 mysql-bin.000014\r\n-rw-rw---- 1 mysql mysql   420 Oct 20 11:18 mysql-bin.index\r\n[root@Rocky9 ~]#\r\n# myql-bin.xxx\u8fd9\u4e2a\u683c\u5f0f\u662f\u5728\/etc\/my.cnf.d\/mariadb-server.cnf\u914d\u7f6e\u6587\u4ef6\u4e2d\u5b9a\u4e49\u597d\u7684\r\nlog-bin=\/data\/logbin\/mysql-bin\r\n\r\n# \u67e5\u770b\u5907\u4efd\u6587\u4ef6\u8bb0\u5f55\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4f4d\u7f6e\r\n# \u5148\u89e3\u538b\u7f29\r\n[root@Rocky9 logbin]# gzip -d \/data\/all.sql.gz\r\n[root@Rocky9 logbin]# grep -i mysql-bin.0000 \/data\/all.sql\r\n-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=385;\r\n[root@Rocky9 logbin]#\r\n# \u8fd9\u4e00\u884c\u4ee3\u8868\u7b2c\u4e00\u6b21\u5b8c\u5168\u5907\u4efd\u5230\u4e86\u4e8c\u8fdb\u5236\u6587\u4ef6mysql-bin.000012\u4e2d\u7684385\u8fd9\u4e2a\u4f4d\u7f6e\uff0c385\u540e\u7eed\u7684\u662f\u6ca1\u6709\u5907\u4efd\u7684\uff0c\u5982\u679c\u60f3\u8fd8\u539f\u7684\u8bdd\uff0c\u9700\u8981\u7b2c\u4e00\u6b21\u5907\u4efd\u7684\u6587\u4ef6\u4ee5\u53ca\u7ed3\u5408\u4e8c\u8fdb\u5236\u6587\u4ef6mysql-bin.000012\u4e2d\u7684385\u540e\u7eed\u7684\u90e8\u5206\u624d\u53ef\u4ee5\u6062\u590d\r\n\r\n# \u5bfc\u5165\u4e8c\u8fdb\u5236\u65e5\u5fd7\r\n[root@Rocky9 logbin]# mysqlbinlog --start-position=385 mysql-bin.000012 > \/data\/binlog.sql\r\n\r\n# \u542f\u52a8\u6570\u636e\u5e93\uff0c\u76f8\u5f53\u4e8e\u65b0\u5b89\u88c5\u6570\u636e\u5e93\r\n[root@Rocky9 logbin]# systemctl restart mariadb.service\r\n# \u8fd8\u539f\u7684\u65f6\u5019\u662f\u4e0d\u9700\u8981\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\uff0c\u53ef\u4ee5\u4e34\u65f6\u7981\u7528\r\n(root@localhost) [(none)]> set sql_log_bin=0;\r\nQuery OK, 0 rows affected (0.000 sec)\r\n\r\n(root@localhost) [(none)]> select @@sql_log_bin;\r\n+---------------+\r\n| @@sql_log_bin |\r\n+---------------+\r\n|             0 |\r\n+---------------+\r\n1 row in set (0.001 sec)\r\n\r\n(root@localhost) [(none)]> source \/data\/all.sql\r\n(root@localhost) [(none)]> source \/data\/logbin.sql\r\n(root@localhost) [(none)]> use hellodb\r\n(root@localhost) [hellodb]> select * from teachers;\r\n+-----+---------------+-----+--------+\r\n| TID | Name          | Age | Gender |\r\n+-----+---------------+-----+--------+\r\n|   1 | Song Jiang    |  45 | M      |\r\n|   2 | Zhang Sanfeng |  94 | M      |\r\n|   3 | Miejue Shitai |  77 | F      |\r\n|   4 | Lin Chaoying  |  93 | F      |\r\n|  12 | xiaoming      |  22 | F      |\r\n|  13 | xiaohong      |  23 | M      |\r\n+-----+---------------+-----+--------+\r\n6 rows in set (0.000 sec)\r\n\r\n(root@localhost) [hellodb]>\r\n# \u6062\u590d\u4e8c\u8fdb\u5236\r\n(root@localhost) [hellodb]> set sql_log_bin=1;\r\nQuery OK, 0 rows affected (0.000 sec)\r\n\r\n# \u8fd8\u539f\u6210\u529f\r\n\r\n# \u8ba1\u5212\u4efb\u52a1\r\n# Example of job definition:\r\n# .---------------- minute (0 - 59)\r\n# |  .------------- hour (0 - 23)\r\n# |  |  .---------- day of month (1 - 31)\r\n# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...\r\n# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat\r\n# |  |  |  |  |\r\n# *  *  *  *  * user-name  command to be executed\r\n\r\n# \u6570\u636e\u5e93\u7684\u9694\u79bb\u7ea7\u522b\u662f\u9694\u79bb\u4e0d\u4f4fDDL\u8bed\u53e5\u7684\uff0c\u4e5f\u5c31\u662falter table\uff0cdrop table\uff0crename table\uff0ctruncate table\u3002\u5e76\u4e14\u8fd9\u4e9b\u64cd\u4f5crollback\u4e5f\u662f\u64a4\u9500\u4e0d\u4e86\u7684\u3002\r\n\r\n# SQL\u8bed\u53e5\u5206\u7c7b\uff1a\r\nDDL\uff1aData Definition Language \u6570\u636e\u5b9a\u4e49\u8bed\u8a00(create,drop,alter)\r\nDML\uff1aData Manipulation Language \u6570\u636e\u64cd\u7eb5\u8bed\u8a00(insert,delete,update)\r\nDQL\uff1aData Query Language \u6570\u636e\u67e5\u8be2\u8bed\u8a00(select)\r\nDCL\uff1aData Crontrol Language \u6570\u636e\u63a7\u5236\u8bed\u8a00(grank,revoke)\r\nTCL\uff1aTransaction Crontrol Language \u4e8b\u52a1\u63a7\u5236\u8bed\u8a00(commit,rollback,savepoint)\r\n\r\n# \u8ba1\u5212\u4efb\u52a1\r\ncrontab\u547d\u4ee4\u7684\u51e0\u4e2a\u5e38\u89c1\u7528\u6cd5\uff1a\r\n\r\ncrontab -e\uff1a\u7f16\u8f91\u5f53\u524d\u7528\u6237\u7684crontab\u6587\u4ef6\u3002\r\ncrontab -l\uff1a\u5217\u51fa\u5f53\u524d\u7528\u6237\u7684crontab\u3002\r\ncrontab -r\uff1a\u5220\u9664\u5f53\u524d\u7528\u6237\u7684crontab\u3002\r\n\r\n# \u683c\u5f0f\r\ncron\u73af\u5883\u8ddfshell\u73af\u5883\u662f\u4e0d\u4e00\u6837\u7684\uff0c\u6700\u597d\u5c06path\u53d8\u91cf\u90fd\u5199\u5728crontab -e\u91cc\u9762\uff0c\u5982\u679c\u60f3\u786e\u5b9a\u76ee\u524dcrontab\u7684\u73af\u5883\u53d8\u91cf\u53ef\u4ee5\u770b\u6587\u4ef6\/etc\/contab\r\nSHELL=\/bin\/bash\r\nPATH=\/sbin:\/bin:\/usr\/sbin:\/usr\/bin #\u548cshell\u4e2d\u7684PATH\u53d8\u91cf\u662f\u4e0d\u540c\u7684\r\nMAILTO=root\r\n\u8fd9\u91cc\u9762\u5b9a\u4e49\u4e86shell\u7c7b\u578b\uff0cpath\u53d8\u91cf\uff0c\u4ee5\u53ca\u90ae\u4ef6\u4eba\r\n\r\ncrontab -e\r\nPATH=\/root\/.local\/bin:\/root\/bin:\/usr\/local\/sbin:\/usr\/local\/bin:\/usr\/sbin:\/usr\/bin\r\n* * * * * \/path\/to\/command\r\n\u8fd9\u4e94\u4e2a*\u5206\u522b\u4ee3\u8868\uff1a\r\n\u5206\u949f (0 - 59)\r\n\u5c0f\u65f6 (0 - 23)\r\n\u4e00\u4e2a\u6708\u4e2d\u7684\u67d0\u5929 (1 - 31)\r\n\u6708\u4efd (1 - 12)\r\n\u4e00\u5468\u4e2d\u7684\u67d0\u5929 (0 - 7) \uff080 \u548c 7 \u90fd\u4ee3\u8868\u661f\u671f\u65e5\uff09<\/code><\/pre>\n
3. \u5229\u7528xtrabackup\u5b8c\u5168\u3001\u589e\u91cf\u5907\u4efd\u53ca\u8fd8\u539f<\/h2>\n
# \u5b89\u88c5xtrabackup\r\nhttps:\/\/www.percona.com\/downloads\r\nyum -y install percona-xtrabackup-80-8.0.34-29.1.el8.x86_64.rpm #Rocky 8\u3001CentOS8\r\nyum -y install percona-xtrabackup-80-8.0.34-29.1.el9.x86_64.rpm #Rocky 9\r\n# \u5b8c\u5168\u5907\u4efd(\u4e0d\u53ef\u4ee5\u56de\u6eda)\r\nmkdir \/backup\r\nxtrabackup -uroot --backup --target-dir=\/backup\/base\r\n\r\n# \u7b2c\u4e00\u6b21\u589e\u91cf\u5907\u4efd(\u4e0d\u53ef\u4ee5\u56de\u6eda\uff0c\u4fdd\u6301\u4e8b\u52a1\u7684\u5b8c\u6574\u6027\uff0c\u589e\u91cf\u5907\u4efd\u4e00\u5b9a\u662f\u5efa\u7acb\u5728\u5b8c\u5168\u5907\u4efd\u7684\u57fa\u7840\u4e0a)\r\nxtrabackup -uroot --backup --target-dir=\/backup\/inc1 --incremental-basedir=\/backup\/base\r\n# \u7b2c\u4e8c\u6b21\u589e\u91cf\u5907\u4efd(\u53ef\u4ee5\u56de\u6eda)\r\nxtrabackup -uroot --backup --target-dir=\/backup\/inc2 --incremental-basedir=\/backup\/inc1\r\n\r\n[root@Rocky8 backup]#ll  \/backup\/ -h\r\ntotal 12K\r\ndrwxr-x--- 6 root root 4.0K Oct 23 22:07 base\r\ndrwxr-x--- 6 root root 4.0K Oct 23 22:56 inc1\r\ndrwxr-x--- 6 root root 4.0K Oct 23 22:58 inc2\r\n[root@Rocky8 backup]#du -sh *\r\n72M base\r\n2.0M    inc1\r\n2.2M    inc2\r\n[root@Rocky8 backup]#\r\n\r\n# \u8fd8\u539f\r\n# \u62f7\u8d1d\u5230\u8fdc\u7a0b\u673a\u5668\r\nscp -r \/backup\/ 10.0.0.10:\/\r\n# \u505c\u6b62\u6570\u636e\u5e93\r\nsystemctl stop mysqld\r\nrm -rf \/var\/lib\/mysql\/*\r\n# \u9884\u51c6\u5907\uff0c\u5c06\u6240\u6709\u5907\u4efd\u6574\u5408\u5230base\u76ee\u5f55\u4e0b(--apply-log-only\u4ee3\u8868\u4e0d\u56de\u6eda)\r\nxtrabackup --prepare --apply-log-only --target-dir=\/backup\/base\r\nxtrabackup --prepare --apply-log-only --target-dir=\/backup\/base --incremental-dir=\/backup\/inc1\r\nxtrabackup --prepare  --target-dir=\/backup\/base --incremental-dir=\/backup\/inc2\r\n# base\u76ee\u5f55\u4e0b\u7684\u6570\u636e\u62f7\u8d1d\u5230\/var\/lib\/mysql\/\u4e0b\r\nxtrabackup --copy-back --target-dir=\/backup\/base\r\n# \u4fee\u6539\u5c5e\u6027\r\nchown -R mysql:mysql \/var\/lib\/mysql\r\nsystemctl start mysqld\r\n<\/code><\/pre>\n
4.\u4e3b\u4ece\u590d\u5236<\/h2>\n
# \u663e\u793a\u4e86\u5f53\u524dMySQL\u670d\u52a1\u5668\u7684\u6d3b\u52a8\u8fdb\u7a0b\r\nmysql> show processlist;\r\n+----+-----------------+-----------+------+---------+-------+------------------------+------------------+\r\n| Id | User            | Host      | db   | Command | Time  | State                  | Info             |\r\n+----+-----------------+-----------+------+---------+-------+------------------------+------------------+\r\n|  5 | event_scheduler | localhost | NULL | Daemon  | 71058 | Waiting on empty queue | NULL             |\r\n| 30 | root            | localhost | NULL | Query   |     0 | init                   | show processlist |\r\n+----+-----------------+-----------+------+---------+-------+------------------------+------------------+\r\n2 rows in set (0.02 sec)\r\n# mysql8.0\u9ed8\u8ba4\u5f00\u542f\u4e86event_scheduler,\u800cmariadb10\u9ed8\u8ba4\u4e0d\u5f00\u542f\r\n\u7b2c\u4e00\u4e2a\u8fdb\u7a0b\uff08Id\u4e3a5\uff09:\r\n\u662f\u7531event_scheduler\u7528\u6237\u8fd0\u884c\u7684\u3002\r\n\u5b83\u5728localhost\u4e0a\u8fd0\u884c\u3002\r\n\u8fd9\u4e2a\u8fdb\u7a0b\u4e0d\u5173\u8054\u4efb\u4f55\u7279\u5b9a\u7684\u6570\u636e\u5e93\u3002\r\n\u5b83\u662f\u4e00\u4e2a\u5b88\u62a4\u8fdb\u7a0b\uff08Daemon\uff09\u3002\r\n\u8fd9\u4e2a\u8fdb\u7a0b\u5df2\u7ecf\u8fd0\u884c\u4e8671058\u79d2\u3002\r\n\u5b83\u5f53\u524d\u6b63\u5728\u7b49\u5f85\u7a7a\u961f\u5217\u4e0a\u7684\u4efb\u52a1\u3002\r\n\u8fd9\u662f\u4e8b\u4ef6\u8c03\u5ea6\u5668\u8fdb\u7a0b\uff0c\u5b83\u7528\u4e8e\u6267\u884c\u9884\u5b9a\u7684\u4e8b\u4ef6\u3002\r\n\u7b2c\u4e8c\u4e2a\u8fdb\u7a0b\uff08Id\u4e3a30\uff09:\r\n\u662f\u7531root\u7528\u6237\u8fd0\u884c\u7684\u3002\r\n\u5b83\u4e5f\u5728localhost\u4e0a\u8fd0\u884c\u3002\r\n\u5b83\u4e0d\u5173\u8054\u4efb\u4f55\u7279\u5b9a\u7684\u6570\u636e\u5e93\u3002\r\n\u5b83\u662f\u4e00\u4e2a\u67e5\u8be2\u8fdb\u7a0b\u3002\r\n\u8fd9\u4e2a\u8fdb\u7a0b\u521a\u5f00\u59cb\u8fd0\u884c\uff08\u8fd0\u884c\u4e860\u79d2\uff09\u3002\r\n\u5b83\u76ee\u524d\u6b63\u5728\u521d\u59cb\u5316\u3002\r\n\u5b83\u6b63\u5728\u6267\u884c\u7684\u67e5\u8be2\u662fshow processlist\uff0c\u8fd9\u5c31\u662f\u4f60\u770b\u5230\u8fd9\u4e2a\u7ed3\u679c\u7684\u539f\u56e0\u3002<\/code><\/pre>\n
server_id<\/h3>\n
# \u67e5\u8be2\u5f53\u524dMySQL\u670d\u52a1\u5668\u7684server_id\u7cfb\u7edf\u53d8\u91cf\u7684\u503c\u3002\r\nmysql> select @@server_id;\r\n+-------------+\r\n| @@server_id |\r\n+-------------+\r\n|           1 |\r\n+-------------+\r\n1 row in set (0.00 sec)\r\n\r\n# server_id\u662f\u4e00\u4e2a\u6574\u6570\uff0c\u5b83\u7528\u4e8e\u552f\u4e00\u6807\u8bc6MySQL\u670d\u52a1\u5668\u5b9e\u4f8b\uff0c\u7279\u522b\u662f\u5728\u590d\u5236\u548c\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4e0a\u4e0b\u6587\u4e2d\u3002\u5728\u4e00\u4e2a\u590d\u5236\u62d3\u6251\u4e2d\uff08\u4f8b\u5982\u4e00\u4e2a\u4e3b-\u4ece\u7ed3\u6784\uff09\uff0c\u6bcf\u4e2a\u670d\u52a1\u5668\u5fc5\u987b\u6709\u4e00\u4e2a\u552f\u4e00\u7684server_id\u3002\r\n\u5177\u4f53\u4f5c\u7528\uff1a\r\n1.\u590d\u5236: \u5728MySQL\u7684\u590d\u5236\u7ed3\u6784\u4e2d\uff0cserver_id\u7528\u4e8e\u786e\u4fdd\u6bcf\u4e2aMySQL\u670d\u52a1\u5668\u8282\u70b9\uff08\u65e0\u8bba\u662f\u4e3b\u8282\u70b9\u8fd8\u662f\u4ece\u8282\u70b9\uff09\u90fd\u6709\u4e00\u4e2a\u552f\u4e00\u6807\u8bc6\u3002\u8fd9\u6837\uff0c\u5f53\u4ece\u8282\u70b9\u8bfb\u53d6\u4e3b\u8282\u70b9\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5e76\u5e94\u7528\u66f4\u6539\u65f6\uff0c\u5b83\u53ef\u4ee5\u907f\u514d\u5e94\u7528\u6765\u81ea\u5176\u81ea\u5df1\u7684\u66f4\u6539\u3002\u5b83\u4e5f\u786e\u4fdd\u4e86\u590d\u5236\u94fe\u4e2d\u7684\u5176\u4ed6\u4ece\u8282\u70b9\u4e0d\u4f1a\u4ece\u53e6\u4e00\u4e2a\u4ece\u8282\u70b9\u63a5\u6536\u66f4\u6539\u3002\r\neg:\u60f3\u8c61\u4e00\u4e0b\uff0c\u6211\u4eec\u6709\u4e00\u4e2a\u4e3bMySQL\u670d\u52a1\u5668\uff08\u6211\u4eec\u79f0\u4e4b\u4e3aM\uff09\u548c\u4e24\u4e2a\u4eceMySQL\u670d\u52a1\u5668\uff08\u79f0\u4e3aS1\u548cS2\uff09\u3002\r\n#\u590d\u5236\u7684\u57fa\u7840:\r\n\u4e3b\u670d\u52a1\u5668M\u5728\u8fdb\u884c\u4efb\u4f55\u5199\u64cd\u4f5c\uff08\u5982INSERT, UPDATE\u7b49\uff09\u65f6\uff0c\u4f1a\u5728\u5176\u4e8c\u8fdb\u5236\u65e5\u5fd7\uff08binary log\uff09\u4e2d\u8bb0\u5f55\u8fd9\u4e9b\u66f4\u6539\u3002\r\n\u4ece\u670d\u52a1\u5668\uff08\u5982S1\u548cS2\uff09\u5c06\u5b9a\u671f\u4ece\u4e3b\u670d\u52a1\u5668M\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4e2d\u8bfb\u53d6\u8fd9\u4e9b\u66f4\u6539\uff0c\u5e76\u5728\u5176\u672c\u5730\u5e94\u7528\u8fd9\u4e9b\u66f4\u6539\u3002\u8fd9\u5c31\u662f\u5b83\u4eec\u5982\u4f55\u4e0e\u4e3b\u670d\u52a1\u5668\u4fdd\u6301\u540c\u6b65\u7684\u3002\r\n#\u4e3a\u4ec0\u4e48\u9700\u8981server_id:\r\n\u5047\u8bbeS1\u6b63\u5728\u4eceM\u8bfb\u53d6\u5e76\u5e94\u7528\u66f4\u6539\u3002\u5728\u8fd9\u4e2a\u8fc7\u7a0b\u4e2d\uff0cS1\u4e5f\u4f1a\u8bb0\u5f55\u8fd9\u4e9b\u66f4\u6539\u5230\u5176\u81ea\u5df1\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4e2d\u3002\r\n\u5982\u679c\u6ca1\u6709\u4e00\u79cd\u673a\u5236\u6765\u8bc6\u522b\u8fd9\u4e9b\u66f4\u6539\u7684\u6765\u6e90\uff0c\u90a3\u4e48S2\u53ef\u80fd\u4f1a\u4eceS1\u8bfb\u53d6\u8fd9\u4e9b\u66f4\u6539\uff0c\u5e76\u518d\u6b21\u5e94\u7528\u5b83\u4eec\uff0c\u5c3d\u7ba1\u5b83\u4eec\u5df2\u7ecf\u76f4\u63a5\u4eceM\u5e94\u7528\u8fc7\u4e86\u3002\u8fd9\u4f1a\u5bfc\u81f4\u6570\u636e\u7684\u4e0d\u4e00\u81f4\u3002\r\n\u8fd9\u5c31\u662fserver_id\u6d3e\u4e0a\u7528\u573a\u7684\u5730\u65b9\u3002\u6bcf\u6b21\u5199\u5165\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u4e8b\u4ef6\u90fd\u4f1a\u5e26\u6709\u4ea7\u751f\u8be5\u4e8b\u4ef6\u7684\u670d\u52a1\u5668\u7684server_id\u3002\r\n\u5f53S1\u4eceM\u8bfb\u53d6\u548c\u5e94\u7528\u66f4\u6539\u65f6\uff0c\u5b83\u77e5\u9053\u8fd9\u4e9b\u66f4\u6539\u6765\u81eaM\uff0c\u56e0\u4e3a\u5b83\u4eec\u5e26\u6709M\u7684server_id\u3002\u56e0\u6b64\uff0c\u5f53S1\u5c06\u8fd9\u4e9b\u66f4\u6539\u5199\u5165\u5176\u81ea\u5df1\u7684\u65e5\u5fd7\u65f6\uff0cS2\u5728\u8bfb\u53d6\u5b83\u4eec\u65f6\u4f1a\u77e5\u9053\u8fd9\u4e9b\u66f4\u6539\u5df2\u7ecf\u4eceM\u5e94\u7528\u8fc7\u4e86\uff0c\u56e0\u6b64\u4e0d\u4f1a\u518d\u6b21\u5e94\u7528\u5b83\u4eec\u3002\r\n#\u94fe\u5f0f\u590d\u5236:\r\n\u5728\u66f4\u590d\u6742\u7684\u8bbe\u7f6e\u4e2d\uff0c\u4f60\u53ef\u80fd\u6709\u591a\u7ea7\u590d\u5236\uff0c\u4f8b\u5982M->S1->S2\u3002\u5728\u8fd9\u79cd\u60c5\u51b5\u4e0b\uff0cS2\u662fS1\u7684\u4ece\u670d\u52a1\u5668\uff0c\u800cS1\u662fM\u7684\u4ece\u670d\u52a1\u5668\u3002\r\n\u5728\u8fd9\u79cd\u8bbe\u7f6e\u4e2d\uff0cS2\u9700\u8981\u4eceS1\u8bfb\u53d6\u548c\u5e94\u7528\u66f4\u6539\uff0c\u4f46\u53ea\u6709\u90a3\u4e9b\u539f\u59cb\u6765\u81eaM\u7684\u66f4\u6539\u3002\u5b83\u9700\u8981\u8df3\u8fc7\u90a3\u4e9b\u5df2\u7ecf\u4eceM\u5230S1\u5e76\u4eceS1\u5230S2\u5e94\u7528\u8fc7\u7684\u66f4\u6539\u3002\u518d\u6b21\uff0cserver_id\u5728\u6b64\u5904\u662f\u5173\u952e\uff0c\u56e0\u4e3a\u5b83\u5141\u8bb8S2\u8bc6\u522b\u548c\u8df3\u8fc7\u91cd\u590d\u7684\u66f4\u6539\u3002\r\n2.\u4e8c\u8fdb\u5236\u65e5\u5fd7: \u5728\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4e8b\u4ef6\u4e2d\uff0cserver_id\u7528\u4e8e\u6807\u8bc6\u751f\u6210\u8be5\u4e8b\u4ef6\u7684MySQL\u670d\u52a1\u5668\u3002\u8fd9\u5728\u5904\u7406\u590d\u5236\u65f6\u975e\u5e38\u6709\u7528\uff0c\u56e0\u4e3a\u4ece\u8282\u70b9\u53ef\u4ee5\u8bc6\u522b\u4e8b\u4ef6\u6765\u6e90\uff0c\u786e\u4fdd\u5b83\u53ea\u5904\u7406\u6765\u81ea\u4e3b\u8282\u70b9\u7684\u4e8b\u4ef6\u3002\r\n3.\u907f\u514d\u51b2\u7a81: \u5728\u591a\u4e3b\u590d\u5236\u73af\u5883\u4e2d\uff0c\u786e\u4fdd\u6bcf\u4e2a\u4e3b\u8282\u70b9\u90fd\u6709\u4e00\u4e2a\u552f\u4e00\u7684server_id\u662f\u5f88\u91cd\u8981\u7684\uff0c\u4ee5\u907f\u514d\u6570\u636e\u51b2\u7a81\u548c\u4e0d\u4e00\u81f4\u3002\r\n\r\n# \u5728MySQL\u7684\u914d\u7f6e\u6587\u4ef6my.cnf\uff08\u6216my.ini\uff09\u4e2d\uff0c\u4f60\u5e94\u8be5\u4f7f\u7528server_id,\u4f7f\u7528\u6a2a\u7ebf\u4e5f\u662f\u53ef\u4ee5\u7684\u3002\r\n[mysqld]\r\nlog-bin=\/data\/mysql\/logbin\/mysql-bin\r\ndatadir=\/var\/lib\/mysql\r\nsocket=\/var\/lib\/mysql\/mysql.sock\r\nlog-error=\/var\/log\/mysql\/mysqld.log\r\npid-file=\/run\/mysqld\/mysqld.pid\r\nserver_id=135\r\n\u5173\u4e8e\u8fd9\u4e24\u8005\u4e4b\u95f4\u7684\u533a\u522b\uff1a\r\nserver-d\u662f\u6b63\u786e\u7684\u914d\u7f6e\u6587\u4ef6\u4e2d\u7684\u53c2\u6570\u540d\u79f0\u3002\r\n@@server_id\u662f\u5728SQL\u67e5\u8be2\u4e2d\u4f7f\u7528\u7684\u7cfb\u7edf\u53d8\u91cf\u540d\u79f0\u3002\u4f8b\u5982\uff0c\u5f53\u4f60\u8fd0\u884cSELECT @@server_id;\u65f6\u3002\r\n\u8fd9\u662fMySQL\u7684\u547d\u540d\u7ea6\u5b9a\uff0c\u7cfb\u7edf\u53d8\u91cf\u4f7f\u7528\u4e0b\u5212\u7ebf(_)\uff0c\u800c\u914d\u7f6e\u6587\u4ef6\u4e2d\u7684\u9009\u9879\u4f7f\u7528\u77ed\u6a2a\u7ebf(-)\u3002\u5728\u5904\u7406\u914d\u7f6e\u6587\u4ef6\u6216\u7f16\u5199SQL\u67e5\u8be2\u65f6\uff0c\u786e\u4fdd\u4f7f\u7528\u6b63\u786e\u7684\u683c\u5f0f\u662f\u5f88\u91cd\u8981\u7684\u3002<\/code><\/pre>\n
\u4e3b\u4ece\u590d\u5236(\u4e3b\u8282\u70b9\u662f\u65b0\u7684)<\/h3>\n
# master\r\n1.\u542f\u7528\u4e8c\u8fdb\u5236,\u540c\u65f6\u8bbe\u7f6e\u4e8c\u8fdb\u5236\u7684\u4f4d\u7f6e\uff0c\u6587\u4ef6\u547d\u540d\u683c\u5f0f\r\nvim \/etc\/my.cnf.d\/mysql-server.cnf\r\n[mysqld]\r\nlog-bin=\/data\/mysql\/logbin\/mysql-bin\r\n2.\u4e3a\u5f53\u524d\u8282\u70b9\u8bbe\u7f6e\u4e00\u4e2a\u5168\u5c40\u552f\u4e00\u7684ID\r\n[mysqld]\r\nserver-id=x\r\nlog-basename=master #\u53ef\u9009\u9879\uff0c\u8bbe\u7f6edatadir\u4e2d\u65e5\u5fd7\u540d\u79f0\uff0c\u786e\u4fdd\u4e0d\u4f9d\u8d56\u4e3b\u673a\u540d\r\n3.\u67e5\u770b\u4ece\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u6587\u4ef6\u548c\u4f4d\u7f6e\u5f00\u59cb\u8fdb\u884c\u590d\u5236\r\nSHOW MASTER STATUS;\r\n+------------------+----------+--------------+------------------+-------------------+\r\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |\r\n+------------------+----------+--------------+------------------+-------------------+\r\n| mysql-bin.000001 |      157 |              |                  |                   |\r\n+------------------+----------+--------------+------------------+-------------------+\r\n1 row in set (0.00 sec)\r\n4.\u521b\u5efa\u6709\u590d\u5236\u6743\u9650\u7684\u7528\u6237\u8d26\u53f7(mysql8.0\u5fc5\u987b\u5206\u5f00)\r\ncreate user repluser@'10.0.0.%' identified by '123456';\r\ngrant replication slave on *.* to repluser@'10.0.0.%';\r\n# GRANT: GRANT\u8bed\u53e5\u5728MySQL\u4e2d\u7528\u4e8e\u8d4b\u4e88\u6743\u9650\u3002\u4f60\u53ef\u4ee5\u4f7f\u7528\u5b83\u4e3a\u7279\u5b9a\u7684\u7528\u6237\u8d4b\u4e88\u7279\u5b9a\u7684\u6743\u9650\uff0c\u4ee5\u6267\u884c\u67d0\u4e9b\u4efb\u52a1\u3002\r\nREPLICATION SLAVE: \u8fd9\u662f\u4f60\u5e0c\u671b\u8d4b\u4e88\u7684\u6743\u9650\u7c7b\u578b\u3002REPLICATION SLAVE\u6743\u9650\u5141\u8bb8\u7528\u6237\u8fde\u63a5\u5230\u4e3b\u670d\u52a1\u5668\uff08master\uff09\u5e76\u8bf7\u6c42\u4e8c\u8fdb\u5236\u65e5\u5fd7\uff08binary log\uff09\u7684\u5185\u5bb9\uff0c\u8fd9\u662fMySQL\u590d\u5236\u6240\u5fc5\u9700\u7684\u3002\u7b80\u800c\u8a00\u4e4b\uff0c\u5177\u6709\u8fd9\u79cd\u6743\u9650\u7684\u7528\u6237\u53ef\u4ee5\u5145\u5f53\u590d\u5236\u7684\u4ece\u670d\u52a1\u5668\uff08slave\uff09\u3002\r\nON *.*: \u8fd9\u8868\u793a\u6743\u9650\u7684\u8303\u56f4\u3002\u5728\u8fd9\u91cc\uff0c*.*\u610f\u5473\u7740\u6743\u9650\u9002\u7528\u4e8e\u6240\u6709\u6570\u636e\u5e93\u548c\u8fd9\u4e9b\u6570\u636e\u5e93\u4e2d\u7684\u6240\u6709\u8868\u3002\u5c3d\u7ba1REPLICATION SLAVE\u6743\u9650\u5b9e\u9645\u4e0a\u662f\u5168\u5c40\u7684\uff08\u56e0\u4e3a\u5b83\u4e0e\u7279\u5b9a\u6570\u636e\u5e93\u6216\u8868\u65e0\u5173\uff09\uff0c\u4f46\u8fd9\u79cd\u8868\u793a\u6cd5\u901a\u5e38\u662f\u6807\u51c6\u7684\u3002\r\nTO repluser@'10.0.0.%': \u8fd9\u5b9a\u4e49\u4e86\u6743\u9650\u7684\u63a5\u6536\u8005\u3002\r\nrepluser: \u8fd9\u662f\u7528\u6237\u540d\u3002\r\n10.0.0.%: \u8fd9\u662f\u5141\u8bb8\u4ece\u4e2d\u8fde\u63a5\u7684\u4e3b\u673a\u5730\u5740\u6a21\u5f0f\u3002%\u662f\u4e00\u4e2a\u901a\u914d\u7b26\uff0c\u610f\u5473\u7740\u8fd9\u4e2a\u7528\u6237\u53ef\u4ee5\u4eceIP\u5730\u5740\u4e3a10.0.0.x\u7684\u4efb\u4f55\u4e3b\u673a\u8fde\u63a5\uff0c\u5176\u4e2dx\u53ef\u4ee5\u662f\u4efb\u4f55\u6570\u5b57\u3002\r\n\u6240\u4ee5\uff0c\u603b\u7ed3\u4e00\u4e0b\uff0c\u8fd9\u4e2a\u547d\u4ee4\u7684\u610f\u601d\u662f\uff1a\u201c\u8d4b\u4e88repluser\u4ece\u4efb\u4f5510.0.0.x\u5730\u5740\u8fde\u63a5\u7684\u7528\u6237\u6743\u9650\uff0c\u5141\u8bb8\u4ed6\u4eec\u5145\u5f53\u590d\u5236\u7684\u4ece\u670d\u52a1\u5668\uff0c\u5e76\u4ece\u4e3b\u670d\u52a1\u5668\u8bf7\u6c42\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u5185\u5bb9\u201d\u3002\r\n# 8.0\u4ee5\u524d\r\nGRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';\r\n\r\n# salve\r\n1.\u4fee\u6539\u914d\u7f6e\u6587\u4ef6\r\n[mysqld]\r\nserver_id=# #\u4e3a\u5f53\u524d\u8282\u70b9\u8bbe\u7f6e\u4e00\u4e2a\u5168\u5c40\u60df\u7684ID\u53f7\r\nlog-bin\r\nread_only=ON #\u8bbe\u7f6e\u6570\u636e\u5e93\u53ea\u8bfb\uff0c\u9488\u5bf9supper user\u65e0\u6548\r\nrelay_log=relay-log #relay log\u7684\u6587\u4ef6\u8def\u5f84\uff0c\u9ed8\u8ba4\u503chostname-relay-bin\r\nrelay_log_index=relay-log.index #\u9ed8\u8ba4\u503chostname-relay-bin.index\r\n2.\u4f7f\u7528\u6709\u590d\u5236\u6743\u9650\u7684\u7528\u6237\u8d26\u53f7\u8fde\u63a5\u81f3\u4e3b\u670d\u52a1\u5668\uff0c\u5e76\u542f\u52a8\u590d\u5236\u7ebf\u7a0b\r\nCHANGE MASTER TO MASTER_HOST='10.0.0.135',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=157;\r\n\r\nCHANGE MASTER TO\u662f\u7528\u4e8e\u8bbe\u7f6e\u6216\u66f4\u6539MySQL\u4ece\u670d\u52a1\u5668\u7684\u590d\u5236\u5750\u6807\u548c\u51ed\u636e\u7684\u547d\u4ee4\u3002\r\nMASTER_HOST='10.0.0.135':\r\n\u8fd9\u662f\u4e3b\u670d\u52a1\u5668\uff08master\uff09\u7684IP\u5730\u5740\u6216\u4e3b\u673a\u540d\uff0c\u4ece\u670d\u52a1\u5668\u5c06\u8fde\u63a5\u5230\u8fd9\u4e2a\u5730\u5740\u4ee5\u63a5\u6536\u590d\u5236\u6570\u636e\u3002\r\nMASTER_USER='repluser':\r\n\u7528\u4e8e\u8fde\u63a5\u5230\u4e3b\u670d\u52a1\u5668\u7684MySQL\u7528\u6237\u540d\u3002\u8fd9\u4e2a\u7528\u6237\u9700\u8981\u6709REPLICATION SLAVE\u6743\u9650\u3002\r\nMASTER_PASSWORD='123456':\r\n\u4e0a\u8ff0\u7528\u6237\u7684\u5bc6\u7801\u3002\r\nMASTER_PORT=3306:\r\n\u4e3b\u670d\u52a1\u5668\u4e0aMySQL\u670d\u52a1\u7684\u7aef\u53e3\u53f7\u3002\r\nMASTER_LOG_FILE='mysql-bin.000010':\r\n\u4ece\u670d\u52a1\u5668\u5f00\u59cb\u8bfb\u53d6\u590d\u5236\u4e8b\u4ef6\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\u540d\u3002\u901a\u5e38\uff0c\u5f53\u4f60\u8bbe\u7f6e\u65b0\u7684\u4ece\u670d\u52a1\u5668\u6216\u9700\u8981\u66f4\u6539\u590d\u5236\u5f00\u59cb\u7684\u4f4d\u7f6e\u65f6\uff0c\u4f60\u4f1a\u8bbe\u7f6e\u8fd9\u4e2a\u503c\u3002\r\nMASTER_LOG_POS=157:\r\n\u5728\u4e0a\u8ff0\u65e5\u5fd7\u6587\u4ef6\u4e2d\uff0c\u4ece\u670d\u52a1\u5668\u5f00\u59cb\u590d\u5236\u7684\u4f4d\u7f6e\uff08\u504f\u79fb\u91cf\uff09\u3002\r\n\u5c06\u6240\u6709\u8fd9\u4e9b\u8bbe\u7f6e\u7ed3\u5408\u8d77\u6765\uff0c\u8fd9\u6761\u547d\u4ee4\u5b9e\u9645\u4e0a\u544a\u8bc9\u4ece\u670d\u52a1\u5668\uff1a\u201c\u8fde\u63a5\u5230IP\u4e3a10.0.0.135\u3001\u7aef\u53e3\u4e3a3306\u7684\u4e3b\u670d\u52a1\u5668\uff0c\u4f7f\u7528repluser\u7528\u6237\u540d\u548c123456\u5bc6\u7801\uff0c\u5e76\u4ecemysql-bin.000001\u65e5\u5fd7\u6587\u4ef6\u7684157\u4f4d\u7f6e\u5f00\u59cb\u63a5\u6536\u590d\u5236\u4e8b\u4ef6\u3002\u201d\r\n\r\n# \u542f\u52a8\u590d\u5236\u8fc7\u7a0b\uff0cslave\u4f1a\u5f00\u542fio\u3001sql\u7ebf\u7a0b\uff0cmaster\u4f1a\u5f00\u542fdump\u7ebf\u7a0b\r\nstart slave\r\n\r\n# \u67e5\u770b\u7ebf\u7a0b\r\nshow processlist;\r\n# \u67e5\u770b\u4ece\u8282\u70b9\u72b6\u6001\r\nshow slave status\\G\r\n*************************** 1. row ***************************\r\n               Slave_IO_State:\r\n                  Master_Host: 10.0.0.135\r\n                  Master_User: repluser\r\n                  Master_Port: 3306\r\n                Connect_Retry: 60\r\n              Master_Log_File: mysql-bin.000010\r\n          Read_Master_Log_Pos: 157\r\n               Relay_Log_File: rocky9-relay-bin.000001\r\n                Relay_Log_Pos: 4\r\n        Relay_Master_Log_File: mysql-bin.000010\r\n             Slave_IO_Running: yes\r\n            Slave_SQL_Running: yes\r\n        Seconds_Behind_Master: NULL\r\n\r\n# IO\u7ebf\u7a0b\uff08\u5728\u4ece\u670d\u52a1\u5668\u4e0a\uff09:\r\n\u8d1f\u8d23\u4ece\u4e3b\u670d\u52a1\u5668\u8bfb\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7\uff08binary log\uff09\u4e8b\u4ef6\u3002\r\nIO\u7ebf\u7a0b\u8fde\u63a5\u5230\u4e3b\u670d\u52a1\u5668\uff0c\u7136\u540e\u8bfb\u53d6\u4e3b\u670d\u52a1\u5668\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\uff0c\u5e76\u5c06\u8fd9\u4e9b\u65e5\u5fd7\u4e8b\u4ef6\u590d\u5236\u5230\u4ece\u670d\u52a1\u5668\u7684\u4e2d\u7ee7\u65e5\u5fd7\uff08relay log\uff09\u3002\r\n\u4e2d\u7ee7\u65e5\u5fd7\u672c\u8d28\u4e0a\u662f\u4ece\u670d\u52a1\u5668\u4e0a\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u4e34\u65f6\u526f\u672c\u3002\r\n\u5f53Slave_IO_Running\u72b6\u6001\u663e\u793a\u4e3aYes\u65f6\uff0c\u8fd9\u610f\u5473\u7740IO\u7ebf\u7a0b\u6b63\u5728\u8fd0\u884c\u5e76\u4e14\u4e0e\u4e3b\u670d\u52a1\u5668\u4fdd\u6301\u8fde\u63a5\u3002\r\n\r\n# SQL\u7ebf\u7a0b\uff08\u5728\u4ece\u670d\u52a1\u5668\u4e0a\uff09:\r\n\u8d1f\u8d23\u8bfb\u53d6\u4e2d\u7ee7\u65e5\u5fd7\u5e76\u6267\u884c\u5176\u4e2d\u7684\u4e8b\u4ef6\uff0c\u4ece\u800c\u4fdd\u6301\u4ece\u670d\u52a1\u5668\u7684\u6570\u636e\u4e0e\u4e3b\u670d\u52a1\u5668\u540c\u6b65\u3002\r\n\u5b83\u5728\u5355\u7ebf\u7a0b\u6a21\u5f0f\u4e0b\u8fd0\u884c\uff0c\u8fd9\u610f\u5473\u7740\u5b83\u4f1a\u4e00\u4e2a\u63a5\u4e00\u4e2a\u5730\u6267\u884c\u65e5\u5fd7\u4e8b\u4ef6\uff0c\u5c3d\u7ba1\u5728\u4e3b\u670d\u52a1\u5668\u4e0a\u8fd9\u4e9b\u4e8b\u4ef6\u53ef\u80fd\u662f\u5e76\u884c\u6267\u884c\u7684\u3002\r\n\u5f53Slave_SQL_Running\u72b6\u6001\u663e\u793a\u4e3aYes\u65f6\uff0c\u8fd9\u610f\u5473\u7740SQL\u7ebf\u7a0b\u6b63\u5728\u8fd0\u884c\u3002\r\n\r\n# Dump\u7ebf\u7a0b\uff08\u5728\u4e3b\u670d\u52a1\u5668\u4e0a\uff09:\r\n\u5f53\u4ece\u670d\u52a1\u5668\u8fde\u63a5\u5230\u4e3b\u670d\u52a1\u5668\u4ee5\u8bf7\u6c42\u4e8c\u8fdb\u5236\u65e5\u5fd7\u65f6\uff0c\u4e3b\u670d\u52a1\u5668\u4e0a\u7684mysqld\u8fdb\u7a0b\u4f1a\u4e3a\u6bcf\u4e2a\u4ece\u670d\u52a1\u5668\u542f\u52a8\u4e00\u4e2adump\u7ebf\u7a0b\u3002\r\nDump\u7ebf\u7a0b\u7684\u4efb\u52a1\u662f\u8bfb\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5e76\u53d1\u9001\u5b83\u7ed9\u8bf7\u6c42\u7684\u4ece\u670d\u52a1\u5668\u7684IO\u7ebf\u7a0b\u3002\r\n\u5982\u679c\u4f60\u6709\u591a\u4e2a\u4ece\u670d\u52a1\u5668\u8fde\u63a5\u5230\u540c\u4e00\u4e2a\u4e3b\u670d\u52a1\u5668\uff0c\u4e3b\u670d\u52a1\u5668\u5c06\u4e3a\u6bcf\u4e2a\u4ece\u670d\u52a1\u5668\u542f\u52a8\u4e00\u4e2a\u5355\u72ec\u7684dump\u7ebf\u7a0b\u3002\r\n\r\n# \u5f53\u4f60\u542f\u52a8\u4ece\u670d\u52a1\u5668\u4e0a\u7684\u590d\u5236\u8fdb\u7a0b\u65f6\uff0c\u8fd9\u4e9b\u7ebf\u7a0b\u662f\u5982\u4f55\u4ea4\u4e92\u5de5\u4f5c\u7684\uff1a\r\nIO\u7ebf\u7a0b\u9996\u5148\u8fde\u63a5\u5230\u4e3b\u670d\u52a1\u5668\u5e76\u8bf7\u6c42\u4ece\u4e00\u4e2a\u6307\u5b9a\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4f4d\u7f6e\u5f00\u59cb\u7684\u65e5\u5fd7\u4e8b\u4ef6\u3002\r\n\u4e3b\u670d\u52a1\u5668\u7684dump\u7ebf\u7a0b\u5f00\u59cb\u8bfb\u53d6\u8fd9\u4e9b\u4e8b\u4ef6\u5e76\u53d1\u9001\u7ed9IO\u7ebf\u7a0b\u3002\r\nIO\u7ebf\u7a0b\u5c06\u8fd9\u4e9b\u4e8b\u4ef6\u5199\u5165\u5176\u4e2d\u7ee7\u65e5\u5fd7\u3002\r\n\u540c\u65f6\uff0cSQL\u7ebf\u7a0b\u5f00\u59cb\u8bfb\u53d6\u4e2d\u7ee7\u65e5\u5fd7\u5e76\u6267\u884c\u65e5\u5fd7\u4e8b\u4ef6\uff0c\u4ece\u800c\u5c06\u4ece\u670d\u52a1\u5668\u4e0a\u7684\u6570\u636e\u4e0e\u4e3b\u670d\u52a1\u5668\u4fdd\u6301\u540c\u6b65\u3002\r\n\r\n# \u505c\u6b62\u4ece\u670d\u52a1\u5668\r\nstop slave\r\n\r\n# \u91cd\u7f6e\u4ece\u670d\u52a1\u5668\u7684\u590d\u5236\u8bbe\u7f6e\r\nreset slave all;<\/code><\/pre>\n
\u4e3b\u4ece\u590d\u5236(\u4e3b\u8282\u70b9\u8fd0\u884c\u5df2\u4e45)<\/h3>\n
# \u5b9e\u9645\u751f\u4ea7\u73af\u5883\u4e2d\uff0c\u4e3b\u8282\u70b9\u4e0d\u4f1a\u662f\u4e00\u4e2a\u65b0\u7684\u6570\u636e\u5e93\uff0c\u800c\u662f\u4e00\u4e2a\u7528\u4e86\u597d\u4e45\u7684\u6570\u636e\u5e93\uff0c\u90a3\u4e48\u5982\u679c\u60f3\u4e3b\u4ece\u590d\u5236\uff0c\u5e76\u4e14\u4ece\u8282\u70b9\u8981\u62ff\u5230\u4e3b\u8282\u70b9\u4e4b\u524d\u7684\u6570\u636e\uff0c\u5982\u4e0b\uff1a\r\n# master\r\n\u9996\u5148\u9700\u8981\u5bf9\u6570\u636e\u5e93\u8fdb\u884c\u5b8c\u5168\u5907\u4efd\r\nmysqldump -A -F --source-data=1 --single-transaction > \/backup\/all.sql\r\n# --master-data\u5728\u8f83\u65b0\u7684\u7248\u672c\u5f53\u4e2d\u5df2\u7ecf\u5f03\u7528\u4e86\uff0c\u800c\u662f\u7528--source-data\u6765\u66ff\u4ee3\r\n\r\n1.\u542f\u7528\u4e8c\u8fdb\u5236,\u540c\u65f6\u8bbe\u7f6e\u4e8c\u8fdb\u5236\u7684\u4f4d\u7f6e\uff0c\u6587\u4ef6\u547d\u540d\u683c\u5f0f\r\nvim \/etc\/my.cnf.d\/mysql-server.cnf\r\n[mysqld]\r\nlog-bin=\/data\/mysql\/logbin\/mysql-bin\r\n2.\u4e3a\u5f53\u524d\u8282\u70b9\u8bbe\u7f6e\u4e00\u4e2a\u5168\u5c40\u552f\u4e00\u7684ID\r\n[mysqld]\r\nserver-id=x\r\nlog-basename=master #\u53ef\u9009\u9879\uff0c\u8bbe\u7f6edatadir\u4e2d\u65e5\u5fd7\u540d\u79f0\uff0c\u786e\u4fdd\u4e0d\u4f9d\u8d56\u4e3b\u673a\u540d\r\n3.\u67e5\u770b\u4ece\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u6587\u4ef6\u548c\u4f4d\u7f6e\u5f00\u59cb\u8fdb\u884c\u590d\u5236\r\nSHOW MASTER STATUS;\r\n+------------------+----------+--------------+------------------+-------------------+\r\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |\r\n+------------------+----------+--------------+------------------+-------------------+\r\n| mysql-bin.000001 |      157 |              |                  |                   |\r\n+------------------+----------+--------------+------------------+-------------------+\r\n1 row in set (0.00 sec)\r\n4.\u521b\u5efa\u6709\u590d\u5236\u6743\u9650\u7684\u7528\u6237\u8d26\u53f7(mysql8.0\u5fc5\u987b\u5206\u5f00)\r\ncreate user repluser@'10.0.0.%' identified by '123456';\r\ngrant replication slave on *.* to repluser@'10.0.0.%';\r\n# REPLICATION SLAVE\uff1a\u8fd9\u662f\u8981\u6388\u4e88\u7684\u7279\u5b9a\u6743\u9650\u3002\u5f53\u4f60\u4e3a\u4e00\u4e2a\u670d\u52a1\u5668\u8bbe\u7f6e\u590d\u5236\uff0c\u5e76\u4e14\u5e0c\u671b\u8fd9\u4e2a\u670d\u52a1\u5668\u4f5c\u4e3a\u4ece\u670d\u52a1\u5668\uff08slave\uff09\uff0c\u8fd9\u4e2a\u6743\u9650\u5c31\u5fc5\u987b\u6388\u4e88\u3002\u8fd9\u6837\uff0c\u4ece\u670d\u52a1\u5668\u624d\u80fd\u8fde\u63a5\u5230\u4e3b\u670d\u52a1\u5668\uff08master\uff09\u5e76\u8bf7\u6c42\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u4e8b\u4ef6\u3002\u7b80\u5355\u5730\u8bf4\uff0c\u5b83\u5141\u8bb8\u4ece\u670d\u52a1\u5668\u4ece\u4e3b\u670d\u52a1\u5668\u62c9\u53d6\u590d\u5236\u6570\u636e\u3002\r\nON .\uff1a\u8fd9\u6307\u7684\u662f\u6743\u9650\u7684\u4f5c\u7528\u8303\u56f4\u3002*.* \u8868\u793a\u6743\u9650\u9002\u7528\u4e8e\u6240\u6709\u6570\u636e\u5e93\u7684\u6240\u6709\u8868\u3002\u5728\u8fd9\u79cd\u60c5\u51b5\u4e0b\uff0c\u8fd9\u662f\u6709\u610f\u4e49\u7684\uff0c\u56e0\u4e3a\u590d\u5236\u6d89\u53ca\u5230\u6240\u6709\u6570\u636e\u5e93\u3002\r\n\r\nTO 'repl'@'%'\uff1a\u8fd9\u6307\u5b9a\u4e86\u5c06\u83b7\u5f97\u6743\u9650\u7684\u7528\u6237\u53ca\u5176\u6765\u6e90\u3002\r\n\r\n# salve\r\n1.\u4fee\u6539\u914d\u7f6e\u6587\u4ef6\r\n[mysqld]\r\nserver_id=# #\u4e3a\u5f53\u524d\u8282\u70b9\u8bbe\u7f6e\u4e00\u4e2a\u5168\u5c40\u60df\u7684ID\u53f7\r\nlog-bin\r\nread_only=ON #\u8bbe\u7f6e\u6570\u636e\u5e93\u53ea\u8bfb\uff0c\u9488\u5bf9supper user\u65e0\u6548\r\nrelay_log=relay-log #relay log\u7684\u6587\u4ef6\u8def\u5f84\uff0c\u9ed8\u8ba4\u503chostname-relay-bin\r\nrelay_log_index=relay-log.index #\u9ed8\u8ba4\u503chostname-relay-bin.index\r\n2.\u5c06\u5728\u4e3b\u8282\u70b9\u7684\u5b8c\u5168\u5907\u4efd\u6587\u4ef6\u62f7\u8d1d\u5230\u4ece\u8282\u70b9\r\nvim  \/backup\/all.sql\r\n\u627e\u5230\u4e0b\u9762\u7684\u8bed\u53e5\r\nCHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=157;\r\n\u4fee\u6539\u6210\r\nCHANGE MASTER TO MASTER_HOST='10.0.0.131',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306,  MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=157;\r\n# \u8fdb\u5165\u5230mysql\u4e2d\r\n# \u4e34\u65f6\u5173\u95ed\u4e8c\u8fdb\u5236\r\nmysql> set sql_log_bin=0;\r\n# \u5bfc\u5165\u5b8c\u5168\u5907\u4efd\u7684sql\u6587\u4ef6\r\nmysql> source \/backup\/all.sql\r\n# \u67e5\u770b\u4ece\u8282\u70b9\u7684\u72b6\u6001\r\nmysql> show slave status\\G\r\n# \u542f\u52a8\u4ece\u8282\u70b9\u7ebf\u7a0b\r\nmysql> start slave\r\n# \u5f00\u542f\u4e8c\u8fdb\u5236\r\nmysql> set sql_log_bin=1;\r\n\r\n\u67e5\u770b\u4ece\u8282\u70b9\u72b6\u6001\uff1a\r\nLast_IO_Error: error connecting to master 'repluser@10.0.0.131:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.\r\n\u6b64\u9519\u8bef\u8868\u793a\u4ece\u670d\u52a1\u5668\u5728\u5c1d\u8bd5\u8fde\u63a5\u5230\u4e3b\u670d\u52a1\u5668\u65f6\u9047\u5230\u4e86\u9274\u6743\u95ee\u9898\u3002\u5177\u4f53\u5730\uff0c\u5b83\u662f\u5173\u4e8ecaching_sha2_password\u9274\u6743\u63d2\u4ef6\u7684\uff0c\u8be5\u63d2\u4ef6\u5728MySQL 8.0\u53ca\u66f4\u9ad8\u7248\u672c\u4e2d\u662f\u9ed8\u8ba4\u7684\u9274\u6743\u63d2\u4ef6\u3002\u6b64\u63d2\u4ef6\u8981\u6c42\u5728\u5ba2\u6237\u7aef\u548c\u670d\u52a1\u5668\u4e4b\u95f4\u4f7f\u7528\u5b89\u5168\u8fde\u63a5\uff0c\u901a\u5e38\u662f\u901a\u8fc7SSL\/TLS\u3002\r\n# \u66f4\u6539\u4e3b\u670d\u52a1\u5668\u4e0a\u590d\u5236\u7528\u6237\u7684\u9274\u6743\u65b9\u6cd5():\r\nALTER USER 'repluser'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' BY '123456';\r\n#\u4e5f\u53ef\u4ee5\u5728\u521b\u5efa\u7684\u65f6\u5019\u66f4\u8be5\r\nCREATE USER 'repluser'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' BY '123456';\r\nflush privileges;\r\n# IDENTIFIED WITH 'mysql_native_password' - \u8fd9\u544a\u8bc9MySQL\uff0c\u5f53\u8fd9\u4e2a\u7528\u6237\u5c1d\u8bd5\u767b\u5f55\u65f6\uff0c\u5e94\u8be5\u4f7f\u7528mysql_native_password\u9274\u6743\u63d2\u4ef6\u3002mysql_native_password \u662fMySQL\u7684\u4e00\u4e2a\u8eab\u4efd\u9a8c\u8bc1\u63d2\u4ef6\u3002\u5728MySQL 8.0\u53ca\u66f4\u9ad8\u7248\u672c\u4e2d\uff0c\u8eab\u4efd\u9a8c\u8bc1\u63d2\u4ef6\u7684\u9ed8\u8ba4\u503c\u662f caching_sha2_password\u3002\u7136\u800c\uff0c\u4e0d\u662f\u6240\u6709\u7684\u5ba2\u6237\u7aef\u5e93\u6216\u8fde\u63a5\u5668\u90fd\u652f\u6301\u65b0\u7684 caching_sha2_password \u63d2\u4ef6\u3002\u56e0\u6b64\uff0c\u5728\u67d0\u4e9b\u60c5\u51b5\u4e0b\uff0c\u53ef\u80fd\u9700\u8981\u521b\u5efa\u4f7f\u7528\u65e7\u7684 mysql_native_password \u63d2\u4ef6\u7684\u7528\u6237\u3002\r\ncaching_sha2_password:\r\n\u8fd9\u662fMySQL 8.0\u53ca\u66f4\u9ad8\u7248\u672c\u4e2d\u7684\u9ed8\u8ba4\u9274\u6743\u63d2\u4ef6\u3002\r\n\u5b83\u4f7f\u7528SHA-256\u54c8\u5e0c\u7b97\u6cd5\u3002\r\n\u8fd9\u4e2a\u63d2\u4ef6\u4e3a\u4e86\u63d0\u9ad8\u6027\u80fd\u548c\u5b89\u5168\u6027\uff0c\u63d0\u4f9b\u4e86\u4e00\u4e2a\u5bc6\u7801\u7f13\u5b58\u673a\u5236\u3002\r\n\u901a\u5e38\uff0c\u5b83\u9700\u8981\u4e00\u4e2a\u5b89\u5168\u7684\u8fde\u63a5\uff08\u5982SSL\/TLS\uff09\uff0c\u4f46\u4e5f\u53ef\u4ee5\u914d\u7f6e\u4e3a\u5141\u8bb8\u975e\u5b89\u5168\u8fde\u63a5\u3002\r\nmysql_native_password:\r\n\u8fd9\u662f\u5728MySQL 8.0\u4e4b\u524d\u7684\u7248\u672c\u4e2d\u7684\u9ed8\u8ba4\u9274\u6743\u63d2\u4ef6\u3002\r\n\u5b83\u4f7f\u7528SHA-1\u54c8\u5e0c\u7b97\u6cd5\u3002\r\n\u5b83\u4e0d\u9700\u8981\u5b89\u5168\u7684\u8fde\u63a5\uff0c\u4f46\u4f7f\u7528SSL\/TLS\u603b\u662f\u4e00\u4e2a\u597d\u7684\u5b9e\u8df5\u3002\r\n\r\n# \u5982\u679c\u662f\u5728\u4ece\u8282\u70b9\u4e0a\u4fee\u6539\u5bfc\u81f4\u6700\u540e\u6570\u636e\u4e0d\u540c\u6b65\uff0c\u90a3\u4e48\u9700\u8981\u518d\u4ece\u8282\u70b9\u8df3\u8fc7\u9519\u8bef\r\nstop slave;\r\nset global sql_slave_skip_counter=1\r\nstart slave;<\/code><\/pre>\n
\u4f7f\u7528GTID\u8fdb\u884c\u4e3b\u4ece\u590d\u5236<\/h2>\n
# \u5229\u7528 GTID\u590d\u5236\u4e0d\u50cf\u4f20\u7edf\u7684\u590d\u5236\u65b9\u5f0f\uff08\u5f02\u6b65\u590d\u5236\u3001\u534a\u540c\u6b65\u590d\u5236\uff09\u9700\u8981\u627e\u5230binlog\u6587\u4ef6\u540d\u548cPOS\u70b9\uff0c\u53ea\u9700\u77e5\u9053master\u7684IP\u3001\u7aef\u53e3\u3001\u8d26\u53f7\u3001\u5bc6\u7801\u5373\u53ef\u3002\u5f00\u542fGTID\u540e\uff0c\u6267\u884cchange master to\r\nmaster_auto_postion=1\u5373\u53ef\uff0c\u5b83\u4f1a\u81ea\u52a8\u5bfb\u627e\u5230\u76f8\u5e94\u7684\u4f4d\u7f6e\u5f00\u59cb\u540c\u6b65\r\n# \u5176\u4f59\u7684\u90fd\u4e00\u6837\uff0c\u53ea\u9700\u8981\u5728\u4e3b\u914d\u7f6e\u6587\u4ef6\u4ee5\u53ca\u4ece\u914d\u7f6e\u6587\u4ef6\u52a0\u5165\u4e0b\u9762\u670d\u52a1\u5668\u9009\u9879\r\nvim \/etc\/my.cnf\r\n[mysqld]\r\ngtid_mode=ON    # gtid\u6a21\u5f0f\r\nenforce_gtid_consistency    # \u4fdd\u8bc1GTID\u5b89\u5168\u7684\u53c2\u6570\r\n\r\n# \u5728mysql8.0.23\u4ee5\u53ca\u66f4\u9ad8\u7684\u7248\u672c\uff0c\u5df2\u7ecf\u9010\u6e10\u5f03\u7528\u4e86change master\u8fd9\u79cd\u5199\u6cd5\u4f7f\u7528\u4e0b\u9762\uff1a\r\n# \u65e7\u7248\u672c\u5199\u6cd5\uff1a\r\nCHANGE MASTER TO\r\nMASTER_HOST='10.0.0.131',\r\nMASTER_USER='repluser',\r\nMASTER_PASSWORD='123456',\r\nMASTER_PORT=3306,\r\nMASTER_AUTO_POSITION=1;\r\n\r\n# \u65b0\u7248\u672c\u5199\u6cd5\uff1a\r\nCHANGE REPLICATION SOURCE TO\r\nSOURCE_HOST='10.0.0.131',\r\nSOURCE_USER='repluser',\r\nSOURCE_PASSWORD='123456',\r\nSOURCE_PORT=3306,\r\nSOURCE_AUTO_POSITION=1;\r\nSOURCE_LOG_FILE \u548c SOURCE_LOG_POS\uff08\u4ece MySQL 8.0.23 \u5f00\u59cb\uff09\r\n\r\n# \u6e05\u7a7aslave\u7684\u65b0\u7248\u672c\u5199\u6cd5\uff1a\r\n RESET REPLICA ALL;\r\n \u4ece MySQL 8.0.22 \u5f00\u59cb\uff0c\u4f7f\u7528 START REPLICA \u4ee3\u66ff START SLAVE\uff0c\r\n STOP REPLICA=stop slave\r\n START REPLIC=start slave\r\n SHOW REPLICA STATUS\\G=show slave status\\G<\/code><\/pre>\n
5. \u7ea7\u8054\u590d\u5236<\/h2>\n
\u7ea7\u8054\u590d\u5236\u662f\u6307\u4ece\u670d\u52a1\u5668\u4e0d\u4ec5\u4ece\u4e3b\u670d\u52a1\u5668\u63a5\u6536\u66f4\u65b0\uff0c\u8fd8\u5c06\u8fd9\u4e9b\u66f4\u65b0\u4f20\u9012\u7ed9\u5176\u4ed6\u7684\u4ece\u670d\u52a1\u5668\u3002\u4e5f\u5c31\u662f\u8bf4\uff0c\u4f60\u53ef\u4ee5\u6709\u5982\u4e0b\u7684\u94fe\u5f0f\u7ed3\u6784\uff1a\u4e3b -> \u4ece1 -> \u4ece2\u3002<\/p><\/blockquote>\n
\u914d\u7f6e\u65b9\u6cd5<\/strong>\uff1a<\/p>\n
    \n
  1. \u9996\u5148\uff0c\u786e\u4fdd\u4e3b\u670d\u52a1\u5668\u542f\u7528\u4e86\u4e8c\u8fdb\u5236\u65e5\u5fd7(binlog<\/code>)\u3002<\/li>\n
  2. \u5728\u4ece1\u670d\u52a1\u5668\u4e0a\uff0c\u50cf\u666e\u901a\u7684\u4e3b\u4ece\u590d\u5236\u90a3\u6837\u914d\u7f6e\u590d\u5236\uff0c\u4f7f\u5176\u4ece\u4e3b\u670d\u52a1\u5668\u63a5\u6536\u66f4\u65b0\u3002<\/li>\n
  3. \u5728\u4ece1\u4e0a\u8bbe\u7f6e log_slave_updates=1<\/code>\uff0c\u8fd9\u6837\u4ece1\u53ef\u4ee5\u5c06\u4ece\u4e3b\u670d\u52a1\u5668\u63a5\u6536\u5230\u7684\u66f4\u65b0\u5199\u5165\u81ea\u5df1\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u3002<\/li>\n
  4. \u5728\u4ece2\u670d\u52a1\u5668\u4e0a\uff0c\u5c06\u4ece1\u914d\u7f6e\u4e3a\u5176\u4e3b\u670d\u52a1\u5668\uff0c\u4f7f\u4ece2\u4ece\u4ece1\u63a5\u6536\u66f4\u65b0\u3002<\/li>\n<\/ol>\n
    # \u51c6\u5907\u4e3b\u670d\u52a1\u5668\uff1a\r\n1.\u5f00\u542f\u4e8c\u8fdb\u5236\uff0c\u4ee5\u53ca\u8bbe\u7f6eserver_id\r\nvim \/etc\/my.cnf\r\n[mysqld]\r\nserver-id=135\r\nlog-bin=\/data\/mysql\/logbin\/mysql-bin\r\n2.\u91cd\u542fmysql\u670d\u52a1\r\nsystemctl restart mysqld\r\n3.\u521b\u5efa\u7528\u4e8e\u590d\u5236\u7684\u7528\u6237\u5e76\u8d4b\u4e88\u6743\u9650\uff1a\r\nCREATE USER 'repluser'@'%' IDENTIFIED WITH mysql_native_password BY '123456';\r\nGRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';\r\n4.\u8bb0\u5f55\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u4f4d\u7f6e\uff1a\r\nshow master status;\r\n\r\n# \u51c6\u5907\u7b2c\u4e00\u4e2a\u4ece\u670d\u52a1\u5668\uff08\u4ece1\uff09\uff1a\r\n1.\u5728 my.cnf \u6216 my.ini \u914d\u7f6e\u6587\u4ef6\u4e2d\u8bbe\u7f6e\uff1a\r\n[mysqld]\r\nlog-bin=\/data\/mysql\/logbin\/mysql-bin\r\nserver-id=131\r\nrelay-log=relay-bin\r\nlog_slave_updates=1  # \u8ba9\u4ece\u670d\u52a1\u5668\u8bb0\u5f55\u590d\u5236\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\r\n2.\u91cd\u542fMySQL\u670d\u52a1\u3002\r\n3.\u4f7f\u7528\u4ee5\u4e0b\u547d\u4ee4\u914d\u7f6e\u4ece1\u4ee5\u4ece\u4e3b\u670d\u52a1\u5668\u63a5\u6536\u590d\u5236\uff1a\r\nCHANGE MASTER TO\r\n  MASTER_HOST='master_host_ip',\r\n  MASTER_USER='repluser',\r\n  MASTER_PASSWORD='123456',\r\n  MASTER_LOG_FILE='mysql-bin.000001',\r\n  MASTER_LOG_POS=157;\r\n4.\u542f\u52a8\u4ece1\u7684\u590d\u5236\u7ebf\u7a0b\uff1a\r\nstart slave;\r\n\r\n# \u51c6\u5907\u7b2c\u4e8c\u4e2a\u4ece\u670d\u52a1\u5668\uff08\u4ece2\uff09\uff1a\r\n1.\u5728 my.cnf \u6216 my.ini \u914d\u7f6e\u6587\u4ef6\u4e2d\u8bbe\u7f6e\uff1a\r\n[mysqld]\r\nlog-bin=\/data\/mysql\/logbin\/mysql-bin\r\nserver-id=134\r\nrelay-log=relay-bin\r\n2.\u91cd\u542fMySQL\u670d\u52a1\u3002\r\n3.\u4f7f\u7528\u4ee5\u4e0b\u547d\u4ee4\u914d\u7f6e\u4ece2\u4ee5\u4ece\u4ece1\u670d\u52a1\u5668\u63a5\u6536\u590d\u5236\uff1a\r\nCHANGE MASTER TO\r\n  MASTER_HOST='slave1_host_ip',\r\n  MASTER_USER='repluser',\r\n  MASTER_PASSWORD='123456',\r\n  MASTER_LOG_FILE='mysql-bin.000001',\r\n  MASTER_LOG_POS=157;\r\n4.\u542f\u52a8\u4ece2\u7684\u590d\u5236\u7ebf\u7a0b\uff1a\r\nstart slave;<\/code><\/pre>\n
    6. \u4e3b\u4e3b\u590d\u5236<\/h2>\n
    \u4e3b\u4e3b\u590d\u5236\u6307\u7684\u662f\u4e24\u4e2a\u670d\u52a1\u5668\u90fd\u53ef\u4ee5\u63a5\u53d7\u5199\u64cd\u4f5c\uff0c\u5e76\u5c06\u8fd9\u4e9b\u64cd\u4f5c\u590d\u5236\u5230\u53e6\u4e00\u4e2a\u670d\u52a1\u5668\u3002\u8fd9\u5b9e\u9645\u4e0a\u662f\u4e24\u4e2a\u4e3b\u4ece\u590d\u5236\u7684\u7ec4\u5408<\/p><\/blockquote>\n
    \u914d\u7f6e\u65b9\u6cd5<\/strong>\uff1a<\/p>\n
      \n
    1. \u914d\u7f6e\u670d\u52a1\u5668A\u548c\u670d\u52a1\u5668B\u7684server-id<\/code>\uff0c\u786e\u4fdd\u5b83\u4eec\u662f\u552f\u4e00\u7684\u3002<\/li>\n
    2. \u542f\u7528\u4e24\u4e2a\u670d\u52a1\u5668\u4e0a\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u3002<\/li>\n
    3. \u5728\u670d\u52a1\u5668A\u4e0a\uff0c\u914d\u7f6e\u4ece\u670d\u52a1\u5668\uff0c\u4f7f\u5176\u4ece\u670d\u52a1\u5668B\u63a5\u6536\u66f4\u65b0\u3002<\/li>\n
    4. \u5728\u670d\u52a1\u5668B\u4e0a\uff0c\u914d\u7f6e\u4ece\u670d\u52a1\u5668\uff0c\u4f7f\u5176\u4ece\u670d\u52a1\u5668A\u63a5\u6536\u66f4\u65b0\u3002<\/li>\n
    5. \u4e3a\u4e86\u907f\u514d\u4e3b\u952e\u51b2\u7a81\uff0c\u6709\u4e9b\u573a\u666f\u53ef\u80fd\u9700\u8981\u4e3a\u6bcf\u4e2a\u670d\u52a1\u5668\u8bbe\u7f6e\u4e0d\u540c\u7684\u81ea\u589e\u95f4\u9694\uff08auto_increment_increment<\/code>\uff09\u548c\u8d77\u59cb\u503c\uff08auto_increment_offset<\/code>\uff09\u3002<\/li>\n<\/ol>\n
      \u6ce8\u610f<\/strong>\uff1a<\/p>\n