{"id":2889,"date":"2022-04-21T15:57:27","date_gmt":"2022-04-21T07:57:27","guid":{"rendered":"https:\/\/www.zeroheart.xyz\/wordpress\/?p=2889"},"modified":"2022-04-21T16:30:16","modified_gmt":"2022-04-21T08:30:16","slug":"mysql%e8%af%bb%e5%86%99%e5%88%86%e7%a6%bb%e7%9a%84%e5%ae%9e%e8%b7%b5%ef%bc%88%e8%bd%ac%e8%bd%bd%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.zeroheart.xyz\/wordpress\/?p=2889","title":{"rendered":"mysql\u8bfb\u5199\u5206\u79bb\u7684\u5b9e\u8df5\uff08\u8f6c\u8f7d\uff09"},"content":{"rendered":"\n<p>\u539f\u6587\uff1acnblogs.com\/muycode\/p\/12603037.html<\/p>\n\n\n\n<h1 class=\"wp-block-heading\"><a href=\"https:\/\/www.cnblogs.com\/muycode\/p\/12603037.html\">SpringBoot \u6574\u5408 MyCat \u5b9e\u73b0\u8bfb\u5199\u5206\u79bb&nbsp;<\/a><\/h1>\n\n\n\n<p>MyCat\u4e00\u4e2a\u5f7b\u5e95\u5f00\u6e90\u7684\uff0c\u9762\u5411\u4f01\u4e1a\u5e94\u7528\u5f00\u53d1\u7684\u5927\u6570\u636e\u5e93\u96c6\u7fa4\u3002\u57fa\u4e8e\u963f\u91cc\u5f00\u6e90\u7684Cobar\u4ea7\u54c1\u800c\u7814\u53d1\u3002\u80fd\u6ee1\u8db3\u6570\u636e\u5e93\u6570\u636e\u5927\u91cf\u5b58\u50a8\uff1b\u63d0\u9ad8\u4e86\u67e5\u8be2\u6027\u80fd\u3002\u6587\u7ae0\u4ecb\u7ecd\u5982\u4f55\u5b9e\u73b0MyCat\u8fde\u63a5MySQL\u5b9e\u73b0\u4e3b\u4ece\u5206\u79bb\uff0c\u5e76\u96c6\u6210SpringBoot\u5b9e\u73b0\u8bfb\u5199\u5206\u79bb\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"mysql\u914d\u7f6e\u4e3b\u4ece\u5173\u7cfb\">MySQL\u914d\u7f6e\u4e3b\u4ece\u5173\u7cfb<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\u8bf4\u660e\">\u8bf4\u660e<\/h3>\n\n\n\n<ul><li>192.168.0.105 Linux \u6570\u636e\u5e93\u4f5c\u4e3a\u4e3bmaster\u6570\u636e\u5e93<\/li><li>127.0.0.1 Window \u4f5c\u4e3a\u4eceslave\u6570\u636e\u5e93<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"master\u4e3b\u6570\u636e\u5e93\u914d\u7f6e\">master\u4e3b\u6570\u636e\u5e93\u914d\u7f6e<\/h3>\n\n\n\n<p>binlog\u662fMysql sever\u5c42\u7ef4\u62a4\u7684\u4e00\u79cd\u4e8c\u8fdb\u5236\u65e5\u5fd7\uff0c\u4e3b\u8981\u662f\u7528\u6765\u8bb0\u5f55\u5bf9Mysql\u6570\u636e\u66f4\u65b0\u6216\u6f5c\u5728\u53d1\u751f\u66f4\u65b0\u7684SQL\u8bed\u53e5\uff0c\u8bb0\u5f55\u4e86\u6240\u6709\u7684\u5199\u8bed\u53e5\uff0c\u5e76\u4ee5\u4e8b\u52a1\u7684\u5f62\u5f0f\u4fdd\u5b58\u5728\u78c1\u76d8\u4e2d\uff0c\u8fd8\u5305\u542b\u8bed\u53e5\u6240\u6267\u884c\u7684\u6d88\u8017\u7684\u65f6\u95f4\uff0cMySQL\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u662f\u4e8b\u52a1\u5b89\u5168\u578b\u7684\u3002<\/p>\n\n\n\n<ul><li>master\u4e0a\u5f00\u542flog_bin<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>$ vim \/etc\/my.cnf\n\n\nlog-bin           = mysql-bin  #&#91;\u5fc5\u987b]\u542f\u7528\u4e8c\u8fdb\u5236\u65e5\u5fd7\nserver-id         = 4          #&#91;\u5fc5\u987b]\u670d\u52a1\u5668\u552f\u4e00ID\uff0c\u9ed8\u8ba4\u662f1\uff0c\u6700\u597d\u53d6ip\u7684\u540e3\u4f4d\nexpire-logs-days  = 7           #\u53ea\u4fdd\u75597\u5929\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\uff0c\u4ee5\u9632\u78c1\u76d8\u88ab\u65e5\u5fd7\u5360\u6ee1\nbinlog-ignore-db  = mysql       #\u4e0d\u5907\u4efd\u7684\u6570\u636e\u5e93\nbinlog-ignore-db  = information_schema\nbinlog-ignore-db  = performation_schema\nbinlog-ignore-db  = sys\nbinlog-do-db=itools_simple #\u9700\u8981\u505a\u590d\u5236\u7684\u6570\u636e\u5e93\u540d\uff0c\u5982\u679c\u6709\u591a\u4e2a\uff0c\u590d\u5236binlog-do-db\u5373\u53ef\n<\/code><\/pre>\n\n\n\n<ul><li>\u767b\u5f55mysql\uff0c\u6d4b\u8bd5log_bin\u662f\u5426\u6210\u529f\u5f00\u542f<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysql -u root -p\n  Root@123\n\n\/\/ log_bin ON\u8868\u793a\u5f00\u542f\u6210\u529f\uff0cOFF\u8868\u793a\u5f00\u542f\u5931\u8d25\nmysql&gt; show variables like '%log_bin%';\n+---------------------------------+--------------------------------+\n| Variable_name                   | Value                          |\n+---------------------------------+--------------------------------+\n| log_bin                         | ON                             |\n| log_bin_basename                | \/var\/lib\/mysql\/mysql-bin       |\n| log_bin_index                   | \/var\/lib\/mysql\/mysql-bin.index |\n| log_bin_trust_function_creators | OFF                            |\n| log_bin_use_v1_row_events       | OFF                            |\n| sql_log_bin                     | ON                             |\n+---------------------------------+--------------------------------+\n6 rows in set (0.01 sec)\n<\/code><\/pre>\n\n\n\n<ul><li>\u65b0\u589e\u5907\u4efd\u8d26\u6237<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; grant replication slave on *.* to 'backup'@'%' identified by 'Root@123';\nmysql&gt; use mysql\nmysql&gt; select user,authentication_string,host from user;\n<\/code><\/pre>\n\n\n\n<ul><li>\u91cd\u542fmysql<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>$ systemctl restart mysqld\n<\/code><\/pre>\n\n\n\n<ul><li>\u767b\u5f55mysql\uff0c\u5e76\u8bbe\u7f6e\u53ea\u8bfb<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysql -u root -p\n  Root@123\n  \nmysql&gt; flush tables with read lock;\n\nmysql&gt; show master status \\G\n<strong>****<\/strong><strong>****<\/strong><strong>****<\/strong><strong>****<\/strong><strong>****<\/strong><strong>****<\/strong><strong>**<em>* 1. row <strong>****<\/strong><strong>****<\/strong><strong>****<\/strong><strong>****<\/strong><strong>****<\/strong><strong>****<\/strong><strong>**<em>*\n             File: mysql-bin.000003\n         Position: 154\n     Binlog_Do_DB: itools_simple\n Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys\nExecuted_Gtid_Set: \n1 row in set (0.01 sec)\n\n\/\/ \u5bfc\u51fa\u6570\u636e\u5e93\uff0c\u8fdb\u5165\u5230\u4e00\u4e2a\u5408\u9002\u7684\u6587\u4ef6\u5939\n$ mysqldump -u root -p itools_simple &gt; itools_simple.txt\n\/\/ \u628a\u751f\u6210\u7684\u6587\u4ef6\u5bfc\u51fa\u5230\u672c\u673a\n$ sz itools_simple.txt\n\n\/\/ \u5728window\u672c\u673a\u6253\u5f00cmd\uff0c\u767b\u5f55slave\u672c\u673amysql\u6570\u636e\u5e93\uff0c\u5bfc\u5165\u6570\u636e\u5e93\n$ .\\mysql -u root -p -hlocalhost mycat_master &lt; .\\itools_simple.txt\n<\/em><\/strong><\/em><\/strong><\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"slave\u4ece\u6570\u636e\u5e93\u914d\u7f6e\">slave\u4ece\u6570\u636e\u5e93\u914d\u7f6e<\/h3>\n\n\n\n<ul><li>\u914d\u7f6e my.ini \u6587\u4ef6<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;mysql]\n#default-character-set=utf8\n\n&#91;mysqld] \nmax_connections=200\ncharacter-set-server=utf8\ndefault-storage-engine=INNODB\n\n# \u6dfb\u52a0\u4e00\u4e0b\u914d\u7f6e\nlog-bin=mysql-bin\nserver-id=223\n<\/code><\/pre>\n\n\n\n<ul><li>\u91cd\u542fslave\u6570\u636e\u5e93<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>net stop mysql\nnet start mysql\n<\/code><\/pre>\n\n\n\n<ul><li>\u767b\u5f55slave\u6570\u636e\u5e93<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysql -u root -p\n  123456\n\n\/\/ \u505c\u6b62 slave\nmysql&gt; stop slave;\n\/\/ \u6839\u636emaster\u4e2d\u7684status\uff0c\u6dfb\u52a0\u4ee5\u4e0b\u914d\u7f6e\nmsyql&gt; change master to master_host='192.168.0.105',master_user='backup',master_password='Root@123',master_log_file='mysql-bin.000003',master_log_pos=154;\n\/\/ \u542f\u7528slave\nmysql&gt; start slave;\n\/\/ \u67e5\u770bslave\u72b6\u6001\nmysql&gt; show slave status \\G\n*************************** 1. row ***************************\n               Slave_IO_State: Waiting for master to send event\n                  Master_Host: 192.168.0.105\n                  Master_User: backup\n                  Master_Port: 3306\n                Connect_Retry: 60\n              Master_Log_File: mysql-bin.000003\n          Read_Master_Log_Pos: 154\n               Relay_Log_File: DESKTOP-OB9O5N7-relay-bin.000002\n                Relay_Log_Pos: 320\n        Relay_Master_Log_File: mysql-bin.000003\n             Slave_IO_Running: Yes\n            Slave_SQL_Running: Yes\n             ..............\n1 row in set (0.00 sec)\n\n\/\/ \u51fa\u73b0\u4e0b\u9762\u4e24\u4e2a\u8bf4\u660e\u914d\u7f6e\u6b63\u786e\n Slave_IO_Running: Yes\nSlave_SQL_Running: Yes\n<\/code><\/pre>\n\n\n\n<ul><li>\u5173\u95edmaster\u6570\u636e\u5e93\u7684\u53ea\u8bfb\u8bbe\u7f6e<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; unlock tables;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\u5b58\u50a8\u5f15\u64ce\">\u5b58\u50a8\u5f15\u64ce<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"innodb\u7279\u6027\">InnoDB\u7279\u6027<\/h4>\n\n\n\n<blockquote class=\"wp-block-quote\"><p>MySQL5.5\u7248\u672c\u540e\uff0cMySQL\u7684\u9ed8\u8ba4\u5185\u7f6e\u5b58\u50a8\u5f15\u64ce\u5df2\u7ecf\u4eceMyISAM\u53d8\u6210InnoDB<\/p><\/blockquote>\n\n\n\n<ul><li>\u652f\u6301\u4e8b\u52a1;<\/li><li>\u884c\u7ea7\u9501\u5b9a(\u66f4\u65b0\u6570\u636e\u65f6\u4e00\u822c\u6307\u9501\u5b9a\u5f53\u524d\u884c)\uff1a\u901a\u8fc7\u7d22\u5f15\u5b9e\u73b0\u3001\u5168\u8868\u626b\u63cf\u5fcd\u8ba9\u65f6\u8868\u9501\u3001\u6ce8\u610f\u95f4\u9699\u6240\u7684\u5f71\u54cd;<\/li><li>\u8bfb\u5199\u963b\u585e\u4e0e\u4e8b\u52a1\u7684\u9694\u79bb\u7ea7\u522b\u76f8\u5173\uff1b<\/li><li>\u5177\u6709\u975e\u5e38\u9ad8\u7684\u7f13\u5b58\u7279\u6027(\u65e2\u80fd\u7f13\u5b58\u7d22\u5f15\u3001\u4e5f\u80fd\u7f13\u5b58\u6570\u636e)\uff1b<\/li><li>\u8fd9\u4e2a\u8868\u548c\u4e3b\u952e\u4ee5\u7ec4(Cluster)\u7684\u65b9\u5f0f\u5b58\u50a8\u3001\u7ec4\u6210\u4e00\u9897\u5e73\u8861\u6811\uff1b<\/li><li>\u6240\u6709\u7684\u8f85\u52a9\u7d22\u5f15(secondary indexes)\u90fd\u4f1a\u4fdd\u5b58\u4e3b\u952e\u4fe1\u606f\uff1b<\/li><li>\u652f\u6301\u5206\u533a\u3001\u8868\u7a7a\u95f4\u7c7b\u4f3c\u4e0eoracle \u6570\u636e\u5e93\uff1b<\/li><li>\u652f\u6301\u5916\u952e\u7ea6\u675f\u3001\u4e0d\u652f\u6301\u5168\u6587\u68c0\u7d22(5.5.5\u4e4b\u524d\u7684MyISAM\u652f\u6301\u5168\u6587\u68c0\u7d22\u30015.5.5\u4e4b\u540e\u5c31\u4e0d\u5728\u652f\u6301);<\/li><li>\u76f8\u5bf9MyISAM\u800c\u8a00\u3001\u5bf9\u786c\u4ef6\u7684\u8981\u6c42\u6bd4\u8f83\u9ad8<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"myisam\u7279\u6027\">MyISAM\u7279\u6027<\/h4>\n\n\n\n<ul><li>\u4e0d\u652f\u6301\u4e8b\u52a1<\/li><li>\u8868\u7ea7\u9501\u5b9a\uff0c\u6570\u636e\u66f4\u65b0\u65f6\u9501\u5b9a\u6574\u4e2a\u8868\uff1a\u5176\u9501\u5b9a\u673a\u5236\u662f\u8868\u7ea7\u9501\u5b9a\uff0c\u8fd9\u867d\u7136\u53ef\u4ee5\u8ba9\u9501\u5b9a\u7684\u5b9e\u73b0\u6210\u672c\u5f88\u5c0f\u4f46\u662f\u4e5f\u540c\u65f6\u5927\u5927\u964d\u4f4e\u4e86\u5176\u5e76\u53d1\u6027\u80fd\u3002<\/li><li>\u8bfb\u5199\u4e92\u76f8\u963b\u585e\uff1a\u4e0d\u4ec5\u4f1a\u5728\u5199\u5165\u7684\u65f6\u5019\u963b\u585e\u8bfb\u53d6\uff0cmyisam\u8fd8\u4f1a\u5728\u8bfb\u53d6\u7684\u65f6\u5019\u963b\u585e\u5199\u5165\uff0c\u4f46\u8bfb\u672c\u8eab\u5e76\u4e0d\u4f1a\u963b\u585e\u53e6\u5916\u7684\u8bfb\u3002<\/li><li>\u53ea\u4f1a\u7f13\u5b58\u7d22\u5f15\uff1aMyISAM\u53ef\u4ee5\u901a\u8fc7key_buffer_size\u7f13\u5b58\u7d22\u5f15\uff0c\u4ee5\u5927\u5927\u63d0\u9ad8\u8bbf\u95ee\u6027\u80fd\uff0c\u51cf\u5c11\u4ea7\u54c1IO\uff0c\u4f46\u662f\u8fd9\u4e2a\u7f13\u5b58\u533a\u53ea\u4f1a\u7f13\u5b58\u7d22\u5f15\uff0c\u800c\u4e0d\u4f1a\u7f13\u5b58\u6570\u636e\u3002<\/li><li>\u8bfb\u53d6\u901f\u5ea6\u8f83\u5feb\uff0c\u5360\u7528\u8d44\u6e90\u76f8\u5bf9\u5c11\u3002<\/li><li>\u4e0d\u652f\u6301\u5916\u952e\u7ea6\u675f\uff0c\u4f46\u652f\u6301\u5168\u6587\u7d22\u5f15\u3002<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"\u4fee\u6539slave\u6570\u636e\u5e93\u5b58\u50a8\u5f15\u64ce\">\u4fee\u6539slave\u6570\u636e\u5e93\u5b58\u50a8\u5f15\u64ce<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; show variables like '%storage_engine%';\n+----------------------------------+--------+\n| Variable_name                    | Value  |\n+----------------------------------+--------+\n| default_storage_engine           | InnoDB |\n| default_tmp_storage_engine       | InnoDB |\n| disabled_storage_engines         |        |\n| internal_tmp_disk_storage_engine | InnoDB |\n+----------------------------------+--------+\n4 rows in set (0.00 sec)\n\n\/\/ \u4fee\u6539\u914d\u7f6e\u6587\u4ef6\n$ vim \/etc\/my.ini\n&#91;mysqld]\ndefault-storage-engine=MyISAM\n\n$ systemctl restart mysqld\n\nmysql&gt; show variables like '%storage_engine%';\n+----------------------------------+--------+\n| Variable_name                    | Value  |\n+----------------------------------+--------+\n| default_storage_engine           | MyISAM |\n| default_tmp_storage_engine       | InnoDB |\n| disabled_storage_engines         |        |\n| internal_tmp_disk_storage_engine | InnoDB |\n+----------------------------------+--------+\n4 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"\u4fee\u6539\u8868\u5b58\u50a8\u5f15\u64ce\">\u4fee\u6539\u8868\u5b58\u50a8\u5f15\u64ce<\/h4>\n\n\n\n<p>\u5982\u679c\u662fslave\u6570\u636e\u5e93\u7684\u8868\u662fMyISAM\uff0cmaster\u6570\u636e\u5e93\u7684\u8868\u662fInnoDB\u3002\u76f4\u63a5\u8986\u76d6master\u6570\u636e\u5e93\u6765\u540c\u6b65\u6570\u636e\u7684\u8bdd\uff0cslave\u6570\u636e\u5e93\u8868\u7684\u5b58\u50a8\u5f15\u64ce\u4e5f\u5c06\u4f1a\u540c\u6b65\u53d8\u6210InnoDB\u3002<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"\u66f4\u6362\u5b58\u50a8\u5f15\u64ce\u9047\u5230\u7684\u95ee\u9898\">\u66f4\u6362\u5b58\u50a8\u5f15\u64ce\u9047\u5230\u7684\u95ee\u9898<\/h4>\n\n\n\n<blockquote class=\"wp-block-quote\"><p>This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you&nbsp;<em>might<\/em>&nbsp;want to use the less safe log_bin_trust_function_creators variable)<\/p><\/blockquote>\n\n\n\n<ul><li>\u51fa\u73b0\u7684\u539f\u56e0<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u5728function\u91cc\u9762\uff0c\u53ea\u6709 DETERMINISTIC, NO SQL \u548c READS SQL DATA \u88ab\u652f\u6301\u3002\n\u5982\u679c\u6211\u4eec\u5f00\u542f\u4e86 bin-log, \u6211\u4eec\u5c31\u5fc5\u987b\u4e3a\u6211\u4eec\u7684function\u6307\u5b9a\u4e00\u4e2a\u53c2\u6570\u3002\n<\/code><\/pre>\n\n\n\n<ul><li>\u89e3\u51b3\u65b9\u6848<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; set global log_bin_trust_function_creators=TRUE;\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\"><p>Specified key was too long; max key length is 1000 bytes<\/p><\/blockquote>\n\n\n\n<ul><li>\u51fa\u73b0\u7684\u539f\u56e0<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>1.DB\u7684 engine \u662f MyISAM\n2.\u5b57\u7b26\u96c6\u662f utf8 \uff0c1\u4e2a utf8=3bytes\n3.(\u7d22\u5f15\u957f\u5ea6\u603b\u548c) * 3 &gt; 1000\u3002\n<\/code><\/pre>\n\n\n\n<ul><li>\u89e3\u51b3\u65b9\u6848<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>1.\u4fee\u6539DB engine \u81f3 innodb\n2.\u66f4\u6539\u5b57\u7b26\u96c6\n3.\u51cf\u5c0f\u5b57\u6bb5\u957f\u5ea6\n<\/code><\/pre>\n\n\n\n<p>\u6ce8\u610f\uff1a\u4e00\u5b9a\u4e0d\u8981\u624b\u52a8\u53bb\u4fee\u6539slave\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\uff0c\u9700\u8981\u7ed9slave\u7684\u7528\u6237\u8bbe\u7f6e\u53ea\u8bfb\u3002<\/p>\n\n\n\n<p>\u81f3\u6b64\uff0cmysql\u7684\u6570\u636e\u5e93\u4e3b\u4ece\u8bbe\u7f6e\u5df2\u7ecf\u914d\u7f6e\u6210\u529f\u3002\u5728master\u4e2d\u4fee\u6539\u6570\u636e\u5e93\uff0c\u4f1a\u540c\u6b65\u5230slave\u4e2d\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"mycat\u57fa\u4e8emysql\u7684\u8bfb\u5199\u5206\u79bb\">Mycat\u57fa\u4e8eMySQL\u7684\u8bfb\u5199\u5206\u79bb<\/h2>\n\n\n\n<p>Mycat\u4e0d\u8d1f\u8d23\u6570\u636e\u7684\u540c\u6b65\uff0c\u6240\u4ee5\u8981\u8fd8\u662f\u8981\u57fa\u4e8eMySQL\u7684\u4e3b\u4ece\u914d\u7f6e\u6765\u5b9e\u73b0\u8bfb\u5199\u5206\u79bb\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\u5b89\u88c5mycat\">\u5b89\u88c5Mycat<\/h3>\n\n\n\n<blockquote class=\"wp-block-quote\"><p>\u7531\u4e8egithub\u9650\u5236\uff0c\u6240\u4ee5\u4ee5\u540e\u65b0\u7248\u672c\u4ece\u4ee5\u4e0b\u5730\u5740\u4e0b\u8f7d&nbsp;<a href=\"http:\/\/dl.mycat.io\/\" target=\"_blank\" rel=\"noreferrer noopener\">http:\/\/dl.mycat.io<\/a><\/p><\/blockquote>\n\n\n\n<ul><li>Linux\u521b\u5efa\u6587\u4ef6\u5939<code>\/usr\/local\/mycat<\/code>\uff0c\u8fdb\u5165\u6587\u4ef6\u5939\uff0c\u4e0b\u8f7d\u5b89\u88c5\u5305<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>$ wget http:\/\/dl.mycat.io\/1.6.7.5\/2020-3-3\/Mycat-server-1.6.7.5-test-20200303154735-linux.tar.gz\n$ tar -zxvf Mycat-server-1.6.7.5-test-20200303154735-linux.tar.gz\n$ cd mycat\n$ useradd mycat\n$ chown -R mycat:mycat \/usr\/local\/mycat\/mycat\n$ passwd mycat\n\n\/\/ \u914d\u7f6ehostname\uff0c\u6dfb\u52a0\u4ee5\u4e0b\u914d\u7f6e\n&#91;root@localhost mycat] vim \/etc\/sysconfig\/network\n    HOSTNAME=localhost\uff08\u4e3b\u673a\u540d\uff09\n\n\/\/ \u67e5\u770b\u662f\u5426\u914d\u7f6e\u4e3b\u673a\n$ vim \/etc\/hosts\n<\/code><\/pre>\n\n\n\n<ul><li>\u5c06Mycat\u914d\u7f6e\u5230\u73af\u5883\u53d8\u91cf\u4e2d<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>$ vim \/etc\/profile\n\/\/ \u5728\u6700\u540e\u6dfb\u52a0\nMYCAT_HOME=\/usr\/local\/mycat\/mycat\nPATH=$MYCAT_HOME\/bin:$PATH\nexport PATH\n\n\/\/ \u4f7f\u914d\u7f6e\u751f\u6548\n$ source \/etc\/profile\n<\/code><\/pre>\n\n\n\n<ul><li>\u5728master\u6570\u636e\u5e93\u4e2d\u6dfb\u52a0user1\uff08\u5199\uff09\u3001user2\uff08\u53ea\u8bfb\uff09\u4e24\u4e2a\u8d26\u6237\uff0c\u5e76\u914d\u7f6e\u6743\u9650\u3002<\/li><li>\u914d\u7f6emycat\u7684<code>schema.xml<\/code><\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>&lt;?xml version=\"1.0\"?&gt;\n&lt;!DOCTYPE mycat:schema SYSTEM \"schema.dtd\"&gt;\n&lt;mycat:schema xmlns:mycat=\"http:\/\/io.mycat\/\"&gt;\n\n    &lt;schema name=\"itools_simple\" checkSQLschema=\"false\" sqlMaxLimit=\"100\" dataNode=\"dn1\"&gt;\n    &lt;\/schema&gt;\n    &lt;dataNode name=\"dn1\" dataHost=\"localhost\" database=\"itools_simple\" \/&gt;\n    &lt;dataHost name=\"localhost\" maxCon=\"1000\" minCon=\"10\" balance=\"0\"\n              writeType=\"0\" dbType=\"mysql\" dbDriver=\"native\" switchType=\"1\"  slaveThreshold=\"100\"&gt;\n        &lt;heartbeat&gt;select user()&lt;\/heartbeat&gt;\n        &lt;writeHost host=\"hostM1\" url=\"192.168.0.105:3306\" user=\"user1\" password=\"Root@123\"&gt;\n            &lt;!-- \u53ef\u4ee5\u914d\u7f6e\u591a\u4e2a\u4ece\u5e93 --&gt;\n            &lt;readHost host=\"hostS2\" url=\"127.0.0.1:3306\" user=\"user2\" password=\"Root@123\" \/&gt;\n        &lt;\/writeHost&gt;\n    &lt;\/dataHost&gt;\n&lt;\/mycat:schema&gt;\n<\/code><\/pre>\n\n\n\n<ul><li>\u914d\u7f6emycat\u7684<code>server.xml<\/code>\uff0c\u589e\u52a0\u4e24\u4e2a\u7528\u6237<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>&lt;user name=\"user1\" defaultAccount=\"true\"&gt;\n\t&lt;property name=\"password\"&gt;Root@123&lt;\/property&gt;\n\t&lt;property name=\"schemas\"&gt;itools_simple&lt;\/property&gt;\n\t&lt;property name=\"defaultSchema\"&gt;itools_simple&lt;\/property&gt;\n&lt;\/user&gt;\n\n&lt;user name=\"user2\"&gt;\n\t&lt;property name=\"password\"&gt;Root@123&lt;\/property&gt;\n\t&lt;property name=\"schemas\"&gt;itools_simple&lt;\/property&gt;\n\t&lt;property name=\"readOnly\"&gt;true&lt;\/property&gt;\n\t&lt;property name=\"defaultSchema\"&gt;itools_simple&lt;\/property&gt;\n&lt;\/user&gt;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\u542f\u52a8mycat\">\u542f\u52a8Mycat<\/h3>\n\n\n\n<ul><li>\u542f\u52a8mycat<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mycat start\nStarting Mycat-server...\n<\/code><\/pre>\n\n\n\n<ul><li>\u67e5\u770b\u542f\u52a8\u65e5\u5fd7<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>$ cat wrapper.log\nMyCAT Server startup successfully. see logs in logs\/mycat.log\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\u4f7f\u7528\u5ba2\u6237\u7aef\u8fde\u63a5mycat\">\u4f7f\u7528\u5ba2\u6237\u7aef\u8fde\u63a5mycat<\/h3>\n\n\n\n<ul><li>\u4f7f\u7528SQLyog\u8fde\u63a5(\u4f7f\u7528\u6b64\u65b9\u5f0f\u8fde\u63a5\uff0c\u4e0d\u80fd\u76f4\u63a5\u901a\u8fc7\u70b9\u51fb\u8868\u67e5\u770b\u6570\u636e)<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"http:\/\/imgs.muycode.com\/sqlyog-connection-mycat.png\" alt=\"sqlyog-connection-mycat\"\/><\/figure>\n\n\n\n<ul><li>\u4f7f\u7528Navicat\u8fde\u63a5<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"http:\/\/imgs.muycode.com\/navicat-connection-mycat.png\" alt=\"navicat-connection-mycat\"\/><\/figure>\n\n\n\n<ul><li>\u53ef\u901a\u8fc7\u5ba2\u6237\u7aef\u76f4\u63a5\u67e5\u770bmaster\u6570\u636e\uff0c\u4e5f\u53ef\u901a\u8fc7\u4fee\u6539mycat\u6570\u636e\uff0c\u67e5\u770bmaster\u548cslave\u7684\u6570\u636e\u662f\u5426\u4f1a\u540c\u6b65<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"http:\/\/imgs.muycode.com\/navicat-show-mycat.png\" alt=\"navicat-show-mycat\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"springboot-\u6574\u5408-mycat-\u5b9e\u73b0\u8bfb\u5199\u5206\u79bb\">SpringBoot \u6574\u5408 MyCat \u5b9e\u73b0\u8bfb\u5199\u5206\u79bb<\/h2>\n\n\n\n<blockquote class=\"wp-block-quote\"><p>\u5176\u5b9e\u6574\u5408MyCat\u4e4b\u540e\uff0c\u5207\u6362\u6570\u636e\u6e90\u7684\u5de5\u4f5c\u53ef\u4ee5\u4ea4\u7ed9MyCat\uff0c\u4e0d\u9700\u8981\u4ee5\u4e0b\u64cd\u4f5c\uff0c\u624b\u52a8\u5207\u6362\u3002\u4ee5\u4e0b\u53ea\u662f\u4ecb\u7ecd\u600e\u4e48\u624b\u52a8\u5207\u6362\u6570\u636e\u6e90<\/p><\/blockquote>\n\n\n\n<ul><li>\u9996\u5148\u9700\u8981\u914d\u7f6e\u597d\u6570\u636e\u5e93\u7684\u4e3b\u4ece\u5173\u7cfb\u3002<\/li><li>\u914d\u7f6e\u597dMyCat\u670d\u52a1\u3002<\/li><li>\u5b9e\u73b0MyCat\u4e0eMySQL\u8bfb\u5199\u5206\u79bb\u3002<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\u6dfb\u52a0\u4f9d\u8d56\">\u6dfb\u52a0\u4f9d\u8d56<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>&lt;dependency&gt;\n    &lt;groupId&gt;org.springframework.boot&lt;\/groupId&gt;\n    &lt;artifactId&gt;spring-boot-starter-aop&lt;\/artifactId&gt;\n&lt;\/dependency&gt;\n\n&lt;dependency&gt;\n    &lt;groupId&gt;com.alibaba&lt;\/groupId&gt;\n    &lt;artifactId&gt;druid&lt;\/artifactId&gt;\n    &lt;version&gt;1.0.23&lt;\/version&gt;\n&lt;\/dependency&gt;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\u521b\u5efa\u6570\u636e\u6e90\">\u521b\u5efa\u6570\u636e\u6e90<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>package com.muycode.itoolsimple.datasource;\n\nimport org.springframework.boot.context.properties.ConfigurationProperties;\nimport org.springframework.boot.jdbc.DataSourceBuilder;\nimport org.springframework.context.annotation.Bean;\nimport org.springframework.context.annotation.Configuration;\n\nimport javax.sql.DataSource;\n\n@Configuration\npublic class DataSourceConfig {\n\n    \/**\n     * \u521b\u5efa\u53ef\u8bfb\u6570\u636e\u6e90\n     *\n     * @return\n     *\/\n    @Bean(name = \"selectDataSource\")\n    @ConfigurationProperties(prefix = \"spring.datasource.select\")\n    public DataSource dataSource1() {\n        return DataSourceBuilder.create().build();\n    }\n\n    \/**\n     * \u521b\u5efa\u53ef\u5199\u6570\u636e\u6e90\n     *\n     * @return\n     *\/\n    @Bean(name = \"updateDataSource\")\n    @ConfigurationProperties(prefix = \"spring.datasource.update\")\n    public DataSource dataSource2() {\n        return DataSourceBuilder.create().build();\n    }\n}\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\u8bbe\u7f6e\u6570\u636e\u6e90\">\u8bbe\u7f6e\u6570\u636e\u6e90<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>package com.muycode.itoolsimple.datasource;\n\nimport org.springframework.context.annotation.Lazy;\nimport org.springframework.stereotype.Component;\n\n@Component\n@Lazy(false)\npublic class DataSourceContextHolder {\n\n    \/**\n     * \u91c7\u7528ThreadLocal \u4fdd\u5b58\u672c\u5730\u591a\u6570\u636e\u6e90\n     *\/\n    private static final ThreadLocal&lt;String&gt; contextHolder = new ThreadLocal&lt;&gt;();\n\n    \/**\n     * \u8bbe\u7f6e\u6570\u636e\u6e90\u7c7b\u578b\n     *\n     * @param dbType\n     *\/\n    public static void setDbType(String dbType) {\n        contextHolder.set(dbType);\n    }\n\n    \/**\n     * \u83b7\u53d6\u6570\u636e\u6e90\u7c7b\u578b\n     *\/\n    public static String getDbType() {\n        return contextHolder.get();\n    }\n\n    public static void clearDbType() {\n        contextHolder.remove();\n    }\n}\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\u8fd4\u56de\u6570\u636e\u6e90\">\u8fd4\u56de\u6570\u636e\u6e90<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>package com.muycode.itoolsimple.datasource;\n\nimport org.springframework.beans.factory.annotation.Autowired;\nimport org.springframework.beans.factory.annotation.Qualifier;\nimport org.springframework.context.annotation.Primary;\nimport org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;\nimport org.springframework.stereotype.Component;\n\nimport javax.sql.DataSource;\nimport java.util.HashMap;\nimport java.util.Map;\n\n@Component\n@Primary\npublic class DynamicDataSource extends AbstractRoutingDataSource {\n\n    @Autowired\n    @Qualifier(\"selectDataSource\")\n    private DataSource selectDataSource;\n\n    @Autowired\n    @Qualifier(\"updateDataSource\")\n    private DataSource updateDataSource;\n\n    \/**\n     * \u8fd4\u56de\u751f\u6548\u7684\u6570\u636e\u6e90\u540d\u79f0\n     *\/\n    @Override\n    protected Object determineCurrentLookupKey() {\n        return DataSourceContextHolder.getDbType();\n    }\n\n    \/**\n     * \u914d\u7f6e\u6570\u636e\u6e90\u4fe1\u606f\n     *\/\n    @Override\n    public void afterPropertiesSet() {\n        Map&lt;Object, Object&gt; map = new HashMap&lt;&gt;(16);\n        map.put(\"selectDataSource\", selectDataSource);\n        map.put(\"updateDataSource\", updateDataSource);\n        setTargetDataSources(map);\n        setDefaultTargetDataSource(updateDataSource);\n        super.afterPropertiesSet();\n    }\n}\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\u521b\u5efa\u5207\u9762\u52a8\u6001\u8bbe\u7f6e\u6570\u636e\u6e90\">\u521b\u5efa\u5207\u9762\uff0c\u52a8\u6001\u8bbe\u7f6e\u6570\u636e\u6e90<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>package com.muycode.itoolsimple.datasource;\n\nimport org.aspectj.lang.JoinPoint;\nimport org.aspectj.lang.annotation.Aspect;\nimport org.aspectj.lang.annotation.Before;\nimport org.springframework.context.annotation.Lazy;\nimport org.springframework.core.annotation.Order;\nimport org.springframework.stereotype.Component;\n\n@Aspect\n@Component\n@Lazy(false)\n@Order(0) \/\/ Order\u8bbe\u5b9aAOP\u6267\u884c\u987a\u5e8f \u4f7f\u4e4b\u5728\u6570\u636e\u5e93\u4e8b\u52a1\u4e0a\u5148\u6267\u884c\npublic class DataSourceOptionAop {\n\n    \/**\n     * \u53ef\u8bfb\u6570\u636e\u6e90\n     *\/\n    private final static String DATASOURCE_TYPE_SELECT = \"selectDataSource\";\n    \/**\n     * \u53ef\u5199\u6570\u636e\u6e90\n     *\/\n    private final static String DATASOURCE_TYPE_UPDATE = \"updateDataSource\";\n\n    \/**\n     * \u521b\u5efa\u5207\u9762\uff0c\u6839\u636e\u65b9\u6cd5\u7c7b\u578b\u9009\u62e9\u4e0d\u540c\u7684\u6570\u636e\u6e90\n     *\n     * @param joinPoint\n     * @author muycode\n     *\/\n    @Before(\"execution(* com.zdys.tcmmes.*.service.*.*(..))\")\n    public void process(JoinPoint joinPoint) {\n        String methodName = joinPoint.getSignature().getName();\n        System.out.print(\"=========== \" + methodName);\n        if (methodName.startsWith(\"save\") || methodName.startsWith(\"update\")\n                || methodName.startsWith(\"del\") || methodName.startsWith(\"add\") || methodName.startsWith(\"login\")\n                || methodName.startsWith(\"logout\") || methodName.startsWith(\"send\")) {\n            DataSourceContextHolder.setDbType(DATASOURCE_TYPE_UPDATE);\n            System.out.println(\"-----------------\u4f7f\u7528updateDataSource\u6570\u636e\u6e90-------------------\");\n        } else {\n            DataSourceContextHolder.setDbType(DATASOURCE_TYPE_SELECT);\n            System.out.println(\"-----------------\u4f7f\u7528selectDataSource\u6570\u636e\u6e90-------------------\");\n        }\n    }\n\n    \/**\n     * \u5728service\u670d\u52a1\u5c42\u4f7f\u7528\u5b8c\u4e4b\u540e\uff0c\u6e05\u7406\u7ed1\u5b9a\u7684\u6570\u636e\u6e90\n     *\n     * @author muycode\n     *\/\n    @After(\"execution(* com.zdys.tcmmes.*.service.*.*(..))\")\n    public void after() {\n        DataSourceContextHolder.clearDbType();\n    }\n}\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"\u8f93\u51fa\u7ed3\u679c\">\u8f93\u51fa\u7ed3\u679c<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>=========== getByUsername-----------------\u4f7f\u7528selectDataSource\u6570\u636e\u6e90-------------------\n=========== getPermissionStringByUserId-----------------\u4f7f\u7528selectDataSource\u6570\u636e\u6e90-------------------\n=========== getPermissionByUserId-----------------\u4f7f\u7528selectDataSource\u6570\u636e\u6e90-------------------\n=========== getRolePermissionLinkByUserId-----------------\u4f7f\u7528selectDataSource\u6570\u636e\u6e90-------------------\n=========== save-----------------\u4f7f\u7528updateDataSource\u6570\u636e\u6e90-------------------\n=========== queryByPage-----------------\u4f7f\u7528selectDataSource\u6570\u636e\u6e90-------------------\n=========== save-----------------\u4f7f\u7528updateDataSource\u6570\u636e\u6e90-------------------\n=========== getPermissionAll-----------------\u4f7f\u7528selectDataSource\u6570\u636e\u6e90-------------------\n=========== save-----------------\u4f7f\u7528updateDataSource\u6570\u636e\u6e90-------------------\n=========== getSysCodeAll-----------------\u4f7f\u7528selectDataSource\u6570\u636e\u6e90-------------------\n=========== save-----------------\u4f7f\u7528updateDataSource\u6570\u636e\u6e90-------------------\n=========== getByRid-----------------\u4f7f\u7528selectDataSource\u6570\u636e\u6e90-------------------\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u539f\u6587\uff1acnblogs.com\/muycode\/p\/12603037.html SpringBoot \u6574\u5408 My [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[202,62,203],"_links":{"self":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2889"}],"collection":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2889"}],"version-history":[{"count":4,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2889\/revisions"}],"predecessor-version":[{"id":2900,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2889\/revisions\/2900"}],"wp:attachment":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2889"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2889"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2889"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}