{"id":120,"date":"2016-04-26T10:22:18","date_gmt":"2016-04-26T02:22:18","guid":{"rendered":"https:\/\/blog.espnlol.com\/?p=120"},"modified":"2018-07-05T16:17:38","modified_gmt":"2018-07-05T08:17:38","slug":"mariadb%e5%ae%89%e8%a3%85%e5%8f%8a%e4%b8%bb%e4%bb%8e","status":"publish","type":"post","link":"https:\/\/blog.espnlol.com\/?p=120","title":{"rendered":"Mariadb\u5b89\u88c5\u53ca\u4e3b\u4ece"},"content":{"rendered":"
\n

1. \u00a0Mysql\u5b89\u88c5\u914d\u7f6e\u6587\u6863<\/strong><\/p>\n<\/div>\n

1.1 \u8f6f\u4ef6\u7248\u672c<\/strong><\/h2>\n

\u91c7\u7528Mysql5.6.26(Mysql5.6\u7248\u672c\u4e2d\u6700\u540e\u7248\u672c)<\/p>\n

1.2 \u8f6f\u4ef6\u5b89\u88c5<\/strong><\/h2>\n

1.2.1 \u5b89\u88c5<\/strong><\/h3>\n

\u91c7\u7528\u6e90\u7801\u7f16\u8bd1\u5b89\u88c5\uff0c\u5177\u4f53\u5982\u4e0b<\/p>\n

cd \/usr\/local\/src\/\r\nwget&nbsp;http:\/\/cdn.mysql.com\/Downloads\/MySQL-5.6\/mysql-5.6.26.tar.gz\r\nyum install -y gcc gcc-c++ make cmake ncurses-devel libtool zilib-devel bison bison-devel\r\ngroupadd -g 27 mysql&nbsp; #linux\u9ed8\u8ba4\u4f7f\u752827\u4f5c\u4e3amysql\u7528\u6237\u7684gid\u548cuid\uff0c\u5efa\u8bae\u6240\u6709\u670d\u52a1\u5668\u7edf\u4e00\u3002\r\nuseradd -g 27 -u 27 -r -s \/sbin\/nologin -M mysql\r\nmkdir -p \/data\/mysql3306\/{data,log,tmp}\r\nchown mysql.mysql \/data\/mysql3306\/\r\ntar xf mysql-5.6.26.tar.gz\r\ncd mysql-5.6.26\r\ncmake . -DCMAKE_INSTALL_PREFIX=\/usr\/local\/mysql-5.6.26 \\\r\n-DMYSQL_DATADIR=\/data\/mysql3306\/data\/ \\\r\n-DSYSCONFDIR=\/etc \\\r\n-DWITH_MYISAM_STORAGE_ENGINE=1 \\\r\n-DWITH_INNOBASE_STORAGE_ENGINE=1 \\\r\n-DWITH_ARCHIVE_STORAGE_ENGINE=1 \\\r\n-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \\\r\n-DWITH_READLINE=1 \\\r\n-DWITH_SSL=system \\\r\n-DWITH_ZLIB=system \\\r\n-DWITH_LIBWRAP=0 \\\r\n-DDEFAULT_CHARSET=utf8 \\\r\n-DMYSQL_UNIX_ADDR=\/data\/mysql3306\/tmp\/mysql.sock \\\r\n-DDEFAULT_COLLATION=utf8_general_ci\r\nmake && make install\r\nln -s \/usr\/local\/mysql-5.6.26\/ \/usr\/local\/mysql<\/pre>\n
 <\/p>\n
1.2.2 \u914d\u7f6eMysql\u73af\u5883\u53d8\u91cf<\/strong><\/h3>\n
export PATH=\/usr\/local\/mysql\/bin:$PATH\r\nexport MYSQL_PS1=\"(\\u@\\h:\\p) [\\d]>\"\r\n#MYSQL_PS1: mysql\u5ba2\u6237\u7aef\u63d0\u793a\u7b26(user@host:port\/sock)\u00a0 [databasename]<\/pre>\n
1.2.3 \u914d\u7f6eMysqlServer\u7ba1\u7406<\/strong><\/h3>\n
Mysql\u7ba1\u7406\u65b9\u5f0f\u5206\u547d\u4ee4\u884c\u4e0e\u7cfb\u7edf\u7ba1\u7406\u811a\u672c\u4e24\u79cd\uff1a\u63a8\u8350\u4f7f\u7528\u7cfb\u7edf\u7ba1\u7406\u811a\u672c<\/p>\n
\u7b2c\u4e00\uff0c\u00a0 \u547d\u4ee4\u884c\u65b9\u5f0f\uff1a<\/p>\n
#\/usr\/local\/mysql\/bin\/mysqld_safe --defaults-file=\/data\/mysql3306\/my.cnf &<\/pre>\n
\u7b2c\u4e8c\uff0c\u00a0 \u7cfb\u7edf\u7ba1\u7406\u811a\u672c\uff1a\u9ed8\u8ba4\u914d\u7f6e\u6587\u4ef6\u8def\u5f84\u4e3a\/etc\/my.cnf<\/p>\n
cp -a \/usr\/local\/mysql\/support-files\/mysql.server \/etc\/init.d\/mysqld\r\n\/etc\/init.d\/mysqld start|stop|restart|reload|force-reload|status\r\nchkconfig mysqld on<\/pre>\n
1.3 \u6570\u636e\u5e93\u521d\u59cb\u5316<\/strong><\/h2>\n
\u5b89\u88c5\u5b8c\u6bd5\uff0c\u63a5\u4e0b\u6765\u9700\u8981\u914d\u7f6emy.cnf\u5e76\u5bf9\u6570\u636e\u5e93\u8fdb\u884c\u521d\u59cb\u5316\u3002<\/p>\n
1.3.1 \u914d\u7f6e\u6587\u4ef6my.cnf<\/h3>\n
\u62f7\u8d1d\u5982\u4e0b\u5185\u5bb9\u5230\/etc\/my.cnf\u6587\u4ef6\u4e2d\uff0c\u5e76\u6839\u636e\u63d0\u793a\u4ee5\u53ca\u5b9e\u9645\u60c5\u51b5\u5982\u5185\u5b58\uff0c\u5e76\u53d1\u8bbf\u95ee\u8fde\u63a5\u6570\u7b49\u5bf9\u90e8\u5206\u53c2\u6570\u8fdb\u884c\u8c03\u6574\u3002<\/p>\n
\u6ce8\u610f\uff1amy.cnf\u6709\u4e00\u4e2a\u53c2\u6570\u8986\u76d6\u539f\u5219\uff1a<\/p>\n
#mysql --help |grep my.cnf\r\norder of preference, my.cnf, $MYSQL_TCP_PORT,\r\n\/etc\/mysql\/my.cnf \u00a0\/etc\/my.cnf \u00a0~\/.my.cnf<\/pre>\n
\u5982\u679c\u91c7\u7528\/etc\/init.d\/mysqld\u65b9\u5f0f\u6765\u7ba1\u7406Mysql Server\uff0c\u5219Mysql Server\u4f1a\u6309\u7167\u8fd9\u4e2a\u987a\u5e8f\u6765\u5bfb\u627emy.cnf\u6587\u4ef6\uff0c\u540e\u9762\u7684my.cnf\u6587\u4ef6\u4e2d\u5b9a\u4e49\u7684\u53c2\u6570\u503c\u4f1a\u8986\u76d6\u524d\u9762\u7684my.cnf\u4e2d<\/p>\n
\u76f8\u540c\u53c2\u6570\u7684\u914d\u7f6e\u3002\u6240\u4ee5\uff0c\u8bf7\u5148\u6267\u884c\u5982\u4e0b\u547d\u4ee4\uff0c\u786e\u5b9a\u5f53\u524d\u73af\u5883\u4e2dmysql\u914d\u7f6e\u6587\u4ef6\u60c5\u51b5\uff1a<\/p>\n
#find \/ -name my.cnf ;ls ~\/.my.cnf<\/pre>\n
\u786e\u8ba4\u662f\u5426\u5b58\u5728\u4e0a\u9762\u5217\u4e3e\u8def\u5f84\u4e2d\u7684my.cnf\uff0c\u5982\u6709\u5219\u5220\u9664\u6216\u8005\u6539\u540d\uff0c\u4ee5\u514d\u5f71\u54cd\u9884\u8ba1\u914d\u7f6e\u53c2\u6570\u3002<\/p>\n
-----------------------------------------my.cnf-------------------------------------------\r\n[client]\r\nport\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 3306\r\nsocket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/tmp\/mysql.sock\r\ndefault_character_set\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 utf8\r\n\r\n[mysqld]\r\nport\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 3306\r\nbind_address\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 0.0.0.0\r\nbasedir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/usr\/local\/mysql-5.6.26\/\r\ndatadir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/data\r\nuser\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 mysql\r\ntmpdir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/tmp\r\npid-file\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/tmp\/mysql.pid\r\nsocket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/tmp\/mysql.sock\r\n\r\nskip_external_locking\r\nskip_name_resolve\r\ncharacter_set_server =\u00a0\u00a0\u00a0\u00a0 utf8\r\nlower_case_table_names =\u00a0\u00a0\u00a0\u00a0 1\r\ndefault_storage_engine\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 INNODB\r\n#wait_timeout\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 100\r\n#interactive_timeout =\u00a0\u00a0\u00a0\u00a0 100\r\n\r\nback_log\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1024\u00a0 #limited by \/proc\/sys\/net\/core\/somaxconn(default 128),\u6240\u4ee5\u8bbe\u7f6e\u518d\u5927\u4e5f\u6ca1\u6709\u610f\u4e49,\u8981\u5148\u8c03somaxconn.\r\nmyisam_recover_options\r\nmyisam_repair_threads\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\r\nevent_scheduler\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 ON\r\nexplicit_defaults_for_timestamp\u00a0\u00a0 = true #http:\/\/www.williamsang.com\/archives\/818.html\r\n\r\n###########slow log###########\r\nslow_query_log\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\r\nslow_query_log_file\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/slow.log\r\nlong_query_time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\r\nlog_queries_not_using_indexes =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\nlog_throttle_queries_not_using_indexes = 10\r\n\r\n########error log#############\r\nlog_error\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/error.log\r\n\r\n###########bin log############\r\nlog_bin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/mysql-bin\r\nlog_bin_index\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/mysql-bin.index\r\nbinlog_format\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 row\r\nsync_binlog\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 #\u4e3a\u907f\u514dmster\u5b95\u673a\u5bfc\u81f4binlog\u4e22\u5931\u4e8b\u52a1,master\u5efa\u8bae\u8bbe\u7f6e\u4e3a1,mysql5.5\u4e2d\u6709bug\u6b64\u53c2\u6570=1\u914d\u5408innodb_flush_log_at_trx_commit=1\u4f1a\u5bfc\u81f4\u7ec4\u63d0\u4ea4\u5931\u6548,\u5bfc\u81f4\u6027\u80fd\u6025\u5267\u4e0b\u964d,\u9700\u8981\u5c06innodb_flush_log_at_trx_commit\u8bbe\u4e3a2\u6765\u907f\u514d\u6b64\u95ee\u9898.\r\nbinlog_cache_size\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 2M\u00a0 #\u8fd9\u4e2a\u53ef\u4ee5\u5728\u540e\u7eed\u6839\u636e\u60c5\u51b5\u8fdb\u884c\u8c03\u6574\r\nmax_binlog_cache_size\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 4M\r\nexpire_logs_days\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 10\r\n\r\n#########replication##########\r\nserver_id\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 102223306\u00a0\u00a0\u00a0 #\u4e3a4\u5b57\u8282\u6574\u6570:0-2\u768432\u6b21\u65b9-1(0-4294967295),\u547d\u540d:ip3,4\u6bb5+\u7aef\u53e3\u53f7,\u82e5\u4e09\u6bb5\u4e3a0,\u5219\u524d\u9762\u52a01.\r\n#read_only\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\r\n#slave_net_timeout\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 10\r\n#relay_log\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/relay-bin\r\n#relay_log_index\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/relay-bin.index\r\n#master_info_file\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/master.info\r\n#relay_log_info_file\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/relay.info\r\n#sync_master_info\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1 #\u8fd9\u4e24\u4e2async\u503c\u9ed8\u8ba4\u4e3a10000,\u662f\u7f13\u5b58\u5199\u7684,\u610f\u5473\u7740\u5982\u679cslave\u5b95\u673a\u91cd\u542f,\u8fd9\u4fe9\u6587\u4ef6\u7f13\u5b58\u7684\u4fe1\u606f\u4f1a\u6e05\u7a7a,relay_log_recovery\u4f1a\u8ba9slave\u91cd\u65b0\u53bbmaster\u4e0a\u83b7\u53d6relay_log,\u4ece\u800c\u5bfc\u81f4\u4e3b\u4ece\u4e0d\u4e00\u81f4.\u662f\u5426\u5f00\u542f\u8981\u770bslave\u78c1\u76d8IOPS\u662f\u5426\u662f\u4e3b\u4ece\u5ef6\u65f6\u7684\u539f\u56e0.\r\n#sync_relay_log_info =\u00a0\u00a0\u00a0\u00a0 1\r\n#relay_log_recovery\u00a0 =\u00a0\u00a0\u00a0\u00a0 1 #slave\u5b95\u673a\u5219\u6e05\u9664\u672c\u5730relay_log\u5e76\u91cd\u65b0\u4ecemaster\u83b7\u53d6relay_log\r\n#skip_slave_start\r\n#log_slave_updates\r\n#replicate_wild_ignore_table=mysql.%\r\n#replicate_wild_ignore_table=test.%\r\n#replicate_wild_do_table=xxxx.%\u00a0 #\u4f7f\u7528\u8be5\u53c2\u6570\u663e\u5f0f\u6307\u5b9a\u4e86\u67d0\u4e2a\u5e93\u8981\u540c\u6b65,\u5219\u522b\u7684\u9700\u8981\u540c\u6b65\u7684\u5e93\u90fd\u9700\u8981\u663e\u5f0f\u6307\u5b9a,\u5426\u5219,\u4e0d\u4f1a\u540c\u6b65\r\n\r\n#######per_thread_buffer#######\r\nmax_connections\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1024\r\nmax_connect_errors\u00a0 =\u00a0\u00a0\u00a0\u00a0 1000000\r\nmax_user_connections\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1024\r\nopen_files_limit\u00a0 =\u00a0\u00a0\u00a0\u00a0 65535\r\nkey_buffer_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 64M\r\nmax_allowed_packet =\u00a0\u00a0\u00a0\u00a0 128M\r\ntable_open_cache\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 2048\u00a0 #http:\/\/www.cnblogs.com\/suredandan\/p\/4010931.html\r\ntable_definition_cache\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 600\r\nread_buffer_size =\u00a0\u00a0\u00a0\u00a0 32M\u00a0 #\u8fd9\u91cc\u8981\u6ce8\u610f,\u8fd94\u4e2a\u53c2\u6570\u662fper_thread\u7684,\u9700\u8981\u6839\u636e\u5185\u5b58\u548c\u5e76\u53d1\u6570\u7684\u5b9e\u9645\u60c5\u51b5\u6765\u8c03\u6574.\u6211\u8fd9\u91cc\u662f32-24=8G\r\nread_rnd_buffer_size =\u00a0\u00a0\u00a0\u00a0 32M\r\njoin_buffer_size\u00a0 =\u00a0\u00a0\u00a0\u00a0 32M\r\nsort_buffer_size\u00a0 =\u00a0\u00a0\u00a0\u00a0 64M\r\ntmp_table_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 256M\u00a0 #\u8fd9\u4e24\u4e2a\u4e5f\u662fper_thread\u7684,\u9700\u8981\u6839\u636e\u60c5\u51b5\u6765\u5b9a.\u5982\u679c\u8d85\u8fc7\u4ed6\u4eec\u9650\u5236,\u5c31\u4f1a\u5199myisam\u8868\u5230\u786c\u76d8\/tmp\r\nmax_heap_table_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 256M\r\n#query_cache_limit\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 32M\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 #\u7f13\u5b58select\u7ed3\u679c\u96c6,write\u591a,\u6570\u636e\u7ecf\u5e38\u53d8\u5316,\u4e0d\u5efa\u8bae\u6253\u5f00;\u4ee5\u8bfb\u4e3a\u4e3b,\u5199\u64cd\u4f5c\u5c11\u7684\u573a\u666f,\u5efa\u8bae\u6253\u5f00.\r\n#query_cache_min_res_unit = \u00a0\u00a0\u00a0\u00a0 3k\r\n#query_cache_size\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 256M\r\nquery_cache_type\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 0\r\nquery_cache_size\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 0\r\nbulk_insert_buffer_size\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 8M\r\nthread_cache_size\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 128\r\n#thread_concurrency\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 12\u00a0 #setting for only Solaris 8 and earlier.\u8fd9\u4e2a\u53c2\u6570\u8981\u8c28\u614e\u8bbe\u7f6e,\u5b98\u65b9\u63a8\u8350\u8bbe\u7f6e\u4e3acpu\u76842\u500d,\u4f46\u662f\u57285.7\u7248\u672c\u5df2\u7ecf\u5c06\u6b64\u53c2\u6570\u79fb\u9664,\u8fd9\u91cc\u5efa\u8bae\u4fdd\u7559\u9ed8\u8ba4\u503c.\u4e0d\u505a\u66f4\u6539\r\nthread_stack\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 256k\u00a0 #\u9ed8\u8ba4\u503c\u5c31\u662f256k\r\n\r\n###########innodb############\r\ninnodb_data_file_path\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0\u00a0ibdata1:2G:autoextend\r\ninnodb_log_file_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1024M\r\ninnodb_log_files_in_group\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 3\r\ninnodb_log_buffer_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 256M\r\ninnodb_buffer_pool_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 24G\u00a0 #\u5b98\u7f51\u63a8\u8350\u5185\u5b58\u768475%-80%\r\ninnodb_buffer_pool_instances\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 8\u00a0 #\u7f13\u51b2\u6c60\u6570\u91cf.\r\ninnodb_flush_log_at_trx_commit\u00a0 =\u00a0\u00a0\u00a0\u00a0 1 #\u4e3b\u5e93\u4e0a\u5efa\u8bae1, \u4ece\u5e93\u5efa\u8bae2.Mysql5.5\u6ce8\u610f\u8bbe\u4e3a2.\r\ninnodb_support_xa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\r\ninnodb_flush_method\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 'O_DIRECT'\r\ninnodb_lock_wait_timeout\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 100 #\u9ed8\u8ba4\u503c\u662f50,\u6839\u636e\u4e1a\u52a1\u573a\u666f\u6765\u8c03\u6574.\r\ninnodb_max_dirty_pages_pct =\u00a0\u00a0\u00a0\u00a0 90\r\nlog_bin_trust_function_creators =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\ninnodb_file_per_table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\r\ninnodb_file_format\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 barracuda\r\ninnodb_file_format_max\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 barracuda\r\ninnodb_strict_mode\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\r\n#innodb_thread_concurrency\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 16 #\u9ed8\u8ba40,\u4e0d\u9650\u5236\r\ninnodb_read_io_threads\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 4 #\u6839\u636e\u8bfb\u5199\u5bc6\u96c6\u5ea6\u6765\u8c03\u6574\r\ninnodb_write_io_threads\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 8\r\ninnodb_io_capacity\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 400 #\u6839\u636e\u786c\u76d8IOPS\u60c5\u51b5\u6765\u8bbe\u5b9a\uff0c\u4e0d\u5b9c\u8fc7\u5927\r\ninnodb_purge_threads\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\r\ntransaction_isolation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 READ-COMMITTED\r\n\r\n[mysqldump]\r\nquick\r\nmax_allowed_packet = 256M\r\n\r\n---------------------------------------my.cnf---------------------------------------------<\/pre>\n
1.3.2 \u6570\u636e\u5e93\u521d\u59cb\u5316<\/h3>\n
cd \/usr\/local\/mysql\r\nscripts\/mysql_install_db --defaults-file=\/data\/mysql3306\/my.cnf\r\nchown mysql.mysql \/data\/mysql3306\/ -R\r\n\r\n<\/pre>\n
1.4 \u6570\u636e\u5e93\u6388\u6743<\/strong><\/p>\n
1.4.1 \u6388\u6743\u51c6\u5219<\/h3>\n
\u5220\u9664\u6240\u6709\u7528\u6237\u540d\u4e3a\u7a7a\u7684\u7528\u6237<\/p>\n
\u4e0d\u5141\u8bb8\u5bc6\u7801\u4e3a\u7a7a\u7684\u7528\u6237\u5b58\u5728<\/p>\n
\u7ba1\u7406\u5458\u7528\u6237\u53ef\u4ee5\u6709\u6240\u6709\u5e93\u6743\u9650\uff0c\u5e76\u6709\u6388\u6743\u6743\u9650<\/p>\n
\u5f00\u53d1\u5e94\u7528\u53ea\u9700\u7ed9\u76f8\u5e94\u5e93\u7684\u6743\u9650<\/p>\n
 <\/p>\n
1.4.2 \u6e05\u7406\u6240\u6709\u7a7a\u7528\u6237\u548c\u7a7a\u5bc6\u7801\u7528\u6237<\/h3>\n
mysql>select user,host,password from mysql.user;\r\nmysql>drop user ' '@'localhost';\r\nmysql>drop user ' '@'your hostname';\r\nmysql>drop user 'root'@'localhost';\r\nmysql>drop user 'root'@'127.0.0.1';\r\nmysql>drop user 'root'@'::1';\r\nmysql>drop user 'root'@'your hostname';<\/pre>\n
1.4.3 \u7ed9root\u4ee5\u53ca\u5e94\u7528\u7528\u6237\u6388\u6743<\/strong><\/h3>\n
mysql>grant all privileges on *.* to 'root'@'localhost' identified by 'your password' with grant option;\r\nmysql>grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'you password' with grant option;\r\nmysql>flush privileges;\r\nmysql>grant select on *.* to 'test1'@'your ipaddress' identified by 'your password';\r\nmysql>grant select,insert,update,delete,index,create routine,alter routine,execute,trigger on test.* to 'test'@'%' identified by 'your password';\r\nmysql>flush privileges;<\/pre>\n
1.4.3.1 \u5fae\u7968\u6d4b\u8bd5\u6570\u636e\u5e93\u6dfb\u52a0\u6388\u6743\uff1a\u540e\u7eed\u65b0\u589e\u7684\u6570\u636e\u5e93\u9700\u8981\u5355\u72ec\u6388\u6743<\/h4>\n
MariaDB [gjdyzjb]> grant all privileges on *.* to 'root'@'%' identified by 'your password' with grant option;\r\nMariaDB [gjdyzjb]> grant select on *.* to 'wepiao_reader'@'%' identified by 'your password';\r\nMariaDB [gjdyzjb]> flush privileges;<\/pre>\n
\u9488\u5bf9\u540e\u7eed\u6dfb\u52a0\u7684\u6570\u636e\u5e93\uff0cwepiao_reader\u8d26\u53f7\u4e5f\u53ea\u6709select\u6743\u9650\uff0c\u5982\u679c\u9700\u8981\u6dfb\u52a0\u5176\u4ed6\u6743\u9650\uff0c\u53ef\u4f7f\u7528\u5982\u4e0b\u8bed\u53e5\uff1a<\/p>\n
mysql>grant all on your_database_name.* to 'wepiao_reader'@'%' identified by 'your password';<\/pre>\n
1.4.4 \u4fee\u6539\u7528\u6237\u5bc6\u7801<\/h3>\n
mysql>update mysql.user set password=password('123456') where user='root';\r\nmysql>flush privileges;<\/pre>\n
1.4.5 \u67e5\u770b\u7528\u6237\u6743\u9650<\/h3>\n
mysql>show grants for 'root'@'localhost';\r\nmysql>show grants for 'test'@'%';\r\nmysql>show grants for 'test1'@'your ipaddress';<\/pre>\n
1.4.6 \u6743\u9650\u7684\u56de\u6536\u4ee5\u53ca\u7528\u6237\u7684\u5220\u9664<\/h3>\n
\u56de\u6536\u6743\u9650\u4f7f\u7528revoke\uff0c\u5220\u9664\u7528\u6237\u4f7f\u7528drop\uff0c\u4e0d\u63a8\u8350\u4f7f\u7528delete\u547d\u4ee4\u76f4\u63a5\u5bf9mysql.user\u8868\u8fdb\u884c\u64cd\u4f5c\u3002<\/strong><\/p>\n
\u56de\u6536\u6743\u9650\uff1a\u6ce8\u610f\uff0crevoke\u53ea\u56de\u6536\u6743\u9650\uff0c\u5e76\u4e0d\u5220\u9664\u7528\u6237<\/p>\n
mysql>revoke select on *.* from 'test1'@'your ipaddress';\r\nmysql>revoke all on test.* from 'test'@'%';<\/pre>\n
\u5220\u9664\u7528\u6237\uff1a<\/p>\n
mysql>drop user 'test'@'%';\r\nmysql>drop user 'test1'@'your password';<\/pre>\n
1.5 \u6570\u636e\u5bfc\u5165\u5bfc\u51fa\uff1amysqldump<\/strong><\/h2>\n
1.5.1 \u57fa\u4e8e\u4e3b\u4ece\u9700\u6c42\u7684\u5bfc\u51fa\u5bfc\u5165<\/h3>\n
\u5bfc\u51fa<\/strong><\/p>\n
\/usr\/local\/mysql\/bin\/mysqldump -urepldata -p -h10.251.80.80 --single-transaction (--add-drop-database) --master-data=2 --databases results_professional wepiao_professional_v2 gjdyzjb wepiao_base_info wepiao_movie > professional_db.`date +%F`.sql<\/pre>\n
\u89e3\u6790\uff1a<\/p>\n
–single-transaction<\/strong>: \u521b\u5efa\u4e00\u81f4\u6027\u5feb\u7167\uff08\u53ea\u9002\u7528\u4e8eInnodb\u5b58\u50a8\u5f15\u64ce\uff09\uff0c\u907f\u514ddump\u8fc7\u7a0b\u4e2d\u9501\u8868\u3002\u540c\u65f6\uff0cdump\u8fc7\u7a0b\u4e2d\u4e0d\u80fd\u5b58\u5728\u5982\u4e0b\u64cd\u4f5c\uff1aALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE\u3002<\/p>\n
–add-drop-database<\/strong>: \u5728dump\u51fa\u6765\u7684sql\u4e2d\u6dfb\u52a0create database\u8bed\u53e5\uff08\u5982\u679cdump\u7684\u5bf9\u8c61\u53ea\u6709\u4e00\u4e2a\u6570\u636e\u5e93\uff0c\u8be5\u8bed\u53e5\u65e0\u6548\uff09\u3002\u4f46\u662f\u6839\u636e\u5b9e\u9645\u4f7f\u7528\u60c5\u51b5\u6765\u770b\uff0c\u65b0\u589e\u7684create database\u8bed\u53e5\u4e0d\u4f1a\u6307\u5b9a\u5b57\u7b26\u96c6\u3002\u6240\u4ee5\uff0c\u8fd9\u4e2a\u9009\u9879\u4e0d\u5efa\u8bae\u4f7f\u7528<\/strong>\u3002\u5efa\u8bae\u5bfc\u5165\u524d\u624b\u52a8\u5efa\u7acb\u6570\u636e\u5e93\u3002<\/p>\n
–master-data:\u00a0<\/strong>\u4f1a\u5728dump\u51fa\u6765\u7684sql\u4e2d\u589e\u52a0master\u7684binlog File\u548cPosition\u4fe1\u606f\u3002\u8bbe\u5b9a\u4e3a1\uff0c\u6b64\u65b0\u589e\u7684\u4fe1\u606f\u4e0d\u4f1a\u88ab\u6ce8\u91ca\uff1b\u8bbe\u5b9a\u4e3a2\uff0c\u6b64\u65b0\u589e\u7684\u4fe1\u606f\u4f1a\u88ab\u6ce8\u91ca\u3002\u5982\u679c\u672a\u542f\u7528GTID\u6765\u5b9e\u73b0\u590d\u5236\u7684\u8bdd\uff0c\u5efa\u8bae\u8bbe\u5b9a\u4e3a2<\/strong>\u3002<\/p>\n
–databases:\u00a0<\/strong>\u53eadump\u6307\u5b9a\u7684\u6570\u636e\u5e93<\/p>\n
\u5bfc\u5165<\/strong><\/p>\n
\u5728\u65b0mysql\u5b9e\u4f8b\u4e0a\u65b0\u5efa\u6240\u6709\u88ab\u5bfc\u51fa\u7684\u6570\u636e\u5e93\uff0c\u518d\u5bfc\u5165\uff0c\u5177\u4f53\u53c2\u8003\u5982\u4e0b\u547d\u4ee4\uff1a<\/p>\n
CREATE DATABASE IF NOT EXISTS my_testDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;\r\n\u2026\r\nmysql \u2013uroot \u2013p < dumpfilename.sql<\/pre>\n
1.5.2 \u5bfc\u51fa\u6570\u636e\u5e93\u3001\u51fd\u6570\u3001\u5b58\u50a8\u8fc7\u7a0b\u4ee5\u53ca\u89e6\u53d1\u5668<\/h3>\n
mysqldump --single-transcation --master-data=2 -A --triggers -R >all.sql<\/pre>\n
1.5.3 \u53ea\u5bfc\u51fa\u6570\u636e\u7ed3\u6784<\/h3>\n
mysqldump \u2013single-transaction --master-data=2 -A --no-data >all.sql \u00a0 #\u5bfc\u51fa\u5168\u5e93\u8868\u7ed3\u6784\r\nmysqldump \u2013single-transaction --master-data=2 --databases database_name --no-data<\/pre>\n
>database_name.sql \u00a0 #\u5bfc\u51fa\u6307\u5b9a\u5e93\u8868\u7ed3\u6784<\/p>\n
1.5.4 \u5bfc\u51fa\u5e76\u538b\u7f29<\/h3>\n
\u5bfc\u51fa\u5e76\u538b\u7f29\uff1amysqldump \u2013single-transaction --master-data=2 |gzip > all.sql.gz\r\n\u89e3\u538b\u5e76\u5bfc\u5165\uff1agunzip -f < all.sql.gz | mysql -u root -p<\/pre>\n
1.5.5 \u5bfc\u51fa\u5355\u8868\u5e76\u538b\u7f29<\/h3>\n
\/usr\/local\/mysql\/bin\/mysqldump -uroot -p -h10.104.9.129 --single-transaction wepiao_movie movie_schedule_seat_minutes |gzip > \/data\/backup\/10.104.17.197_6_tables_20150925\/wepiao_movie.movie_schedule_seat_minutes.sql.gz\r\ngunzip < wepiao_movie.movie_schedule_seat_minutes.sql.gz\u00a0|\/usr\/local\/mysql\/bin\/mysql -uroot -p -h10.104.9.129 wxmovie_basic_backup<\/pre>\n
1.6 Mysql\u591a\u5b9e\u4f8b\u914d\u7f6e<\/strong><\/h2>\n
1.6.1 \u65b0\u5b9e\u4f8b\u521d\u59cb\u5316<\/h3>\n
\u5173\u95ed\u76d1\u542c3306\u7aef\u53e3\u7684\u5b9e\u4f8b\uff0c\u7136\u540e\u57fa\u4e8e\u6211\u4eec\u524d\u9762\u5df2\u7ecf\u521b\u5efa\u4e86\u4e00\u4e2a3306\u7aef\u53e3\u7684\u5b9e\u4f8b\uff0c\u62f7\u8d1d\u521d\u59cb\u5316\u914d\u7f6e\u6587\u4ef6\u5e76\u6839\u636e\u5b9e\u9645\u60c5\u51b5\u505a\u76f8\u5e94\u4fee\u6539\uff0c\u521d\u59cb\u5316\u65b0\u6570\u636e\u5e93\u5b9e\u4f8b\u3002<\/p>\n
mkdir -p \/data\/mysql3307\/{data,log,tmp}\r\nchown mysql.mysql mysql3307\/ -R\r\ncp \u2013a \/etc\/my.cnf \/data\/mysql3307\/\r\nsed -i 's\/3306\/3307\/g' \/data\/mysql3307\/my.cnf\r\nscripts\/mysql_install_db --defaults-file=\/data\/mysql3307\/my.cnf\r\nchown mysql.mysql mysql3307\/ -R<\/pre>\n
\u5355\u72ec\u542f\u52a8\u6b64\u5b9e\u4f8b\uff0c\u4fee\u6539\u6b64\u5b9e\u4f8broot\u7528\u6237\u5bc6\u7801\u4fe1\u606f\uff08\u6ce8\u610f\uff1a\u540c\u4e00\u6570\u636e\u5e93\u670d\u52a1\u5668\u4e0a\u7684\u6240\u6709\u6570\u636e\u5e93\u5b9e\u4f8b\u7684\u5bc6\u7801\u9700\u4e00\u81f4\u3002\uff09\uff1a<\/p>\n
mysql -uroot -p -P3307 -h127.0.0.1\r\n(root@127.0.0.1:3307) [(none)]>drop user 'root'@'localhost';\r\n(root@127.0.0.1:3307) [(none)]>drop user 'root'@'127.0.0.1';\r\n(root@127.0.0.1:3307) [(none)]>drop user 'root'@'::1';\r\n(root@127.0.0.1:3307) [(none)]>drop user 'root'@'your hostname';\r\n(root@127.0.0.1:3307) [(none)]>drop user ' '@'localhost';\r\n(root@127.0.0.1:3307) [(none)]>drop user ' '@'your hostname';\r\n(root@127.0.0.1:3307) [(none)]>grant all on *.* to 'root'@'localhost' identified by 'your password' with grant option;\r\n(root@127.0.0.1:3307) [(none)]>grant all on *.* to 'root'@'127.0.0.1' identified by 'your password' with grant option;\r\n(root@127.0.0.1:3307) [(none)]>flush privileges;<\/pre>\n
 <\/p>\n
\u7136\u540e\u5173\u95ed3307\u7aef\u53e3\u6570\u636e\u5e93\uff1a<\/p>\n
mysqladmin -uroot -p -h127.0.0.1 -P3307 shutdown<\/pre>\n
1.6.2 \u4fee\u6539\/etc\/my.cnf<\/h3>\n
\u591a\u5b9e\u4f8b\u652f\u6301\u5982\u4e0b\u914d\u7f6e\u5757:<\/p>\n
———————————mysqld-multi\u7b80\u4ecb————————————-<\/p>\n
[mysql] #client\u914d\u7f6e\u5757<\/p>\n
[mysqld_multi] #\u591a\u5b9e\u4f8b\u63a7\u5236\u914d\u7f6e\u5757<\/p>\n
[mysqld] #\u591a\u5b9e\u4f8b\u76f8\u540c\u53c2\u6570\u914d\u7f6e\u5757<\/p>\n
[mysqld-5.5] #mysql5.5\u7279\u6709\u53c2\u6570\u914d\u7f6e\u5757<\/p>\n
[mysqld-5.6] #mysql5.6\u7279\u6709\u53c2\u6570\u914d\u7f6e\u5757<\/p>\n
[mysqld-5.7] #mysql5.7\u7279\u6709\u53c2\u6570\u914d\u7f6e\u5757<\/p>\n
[mysqld1] #\u5b9e\u4f8b1\u7279\u522b\u914d\u7f6e\u5757<\/p>\n
[mysqld2] #\u5b9e\u4f8b2 \u7279\u522b\u914d\u7f6e\u5757<\/p>\n
[mysqld3] #\u5b9e\u4f8b3 \u7279\u522b\u914d\u7f6e\u5757<\/p>\n
\u3002\u3002\u3002\u3002\u3002\u3002<\/p>\n
[mysqldump] #mysqldump\u53c2\u6570\u914d\u7f6e\u5757<\/p>\n
\u3002\u3002\u3002\u3002\u3002\u3002<\/p>\n
———————————mysqld-multi\u7b80\u4ecb————————————–<\/p>\n
mysqld-multi\u793a\u4f8b\u914d\u7f6e\u6587\u4ef6\uff1a \/etc\/my.cnf<\/p>\n
————————————-mysqld-multi—————————————<\/p>\n
[client]\r\n\r\nuser\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 root\r\n\r\npassword\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 123456\r\n\r\n#port\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 3306\r\n\r\n#socket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/tmp\/mysql.sock\r\n\r\ndefault_character_set\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 utf8\r\n\r\n\r\n\r\n[mysqld_multi]\r\n\r\nmysqld\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/usr\/local\/mysql\/bin\/mysqld_safe\r\n\r\nmysqladmin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/usr\/local\/mysql\/bin\/mysqladmin\r\n\r\nlog\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/mysqld_multi.log\r\n\r\n\r\n\r\n[mysqld]\r\n\r\n#port\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 3306\r\n\r\n#bind_address\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 0.0.0.0\r\n\r\n#basedir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/usr\/local\/mysql-5.6.26\/\r\n\r\n#datadir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/data\r\n\r\n#user\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 mysql\r\n\r\n#tmpdir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/tmp\r\n\r\n#pid-file\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/tmp\/mysql.pid\r\n\r\n#socket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/tmp\/mysql.sock\r\n\r\n\u3002\u3002\u3002\u3002\u3002\u3002\r\n\r\n\r\n\r\n[mysqld1]\r\n\r\nport\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 3306\r\n\r\nbind_address\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 0.0.0.0\r\n\r\nbasedir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/usr\/local\/mysql-5.6.26\/\r\n\r\ndatadir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/data\r\n\r\nuser\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 mysql\r\n\r\ntmpdir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/tmp\r\n\r\npid-file\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/tmp\/mysql.pid\r\n\r\nsocket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/tmp\/mysql.sock\r\n\r\n\r\n\r\nslow_query_log_file\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/slow.log\r\n\r\nlog_error\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/error.log\r\n\r\nlog_bin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/mysql-bin\r\n\r\nlog_bin_index\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3306\/log\/mysql-bin.index\r\n\r\nserver_id\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 102223306\u00a0\u00a0\u00a0 #\u4e3a4\u5b57\u8282\u6574\u6570:0-2\u768432\u6b21\u65b9-1(0-4294967295),\u547d\u540d:ip3,4\u6bb5+\u7aef\u53e3\u53f7,\u82e5\u4e09\u6bb5\u4e3a0,\u5219\u524d\u9762\u52a01.\r\n\r\n\r\n\r\n[mysqld2]\r\n\r\nport\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 3307\r\n\r\nbind_address\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 0.0.0.0\r\n\r\nbasedir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/usr\/local\/mysql-5.6.26\/\r\n\r\ndatadir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/data\r\n\r\nuser\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 mysql\r\n\r\ntmpdir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/tmp\r\n\r\npid-file\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/tmp\/mysql.pid\r\n\r\nsocket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/tmp\/mysql.sock\r\n\r\n\r\n\r\nslow_query_log_file\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/log\/slow.log\r\n\r\nlog_error\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/log\/error.log\r\n\r\nlog_bin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/log\/mysql-bin\r\n\r\nlog_bin_index\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/log\/mysql-bin.index\r\n\r\nserver_id\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 102223307\u00a0\u00a0\u00a0 #\u4e3a4\u5b57\u8282\u6574\u6570:0-2\u768432\u6b21\u65b9-1(0-4294967295),\u547d\u540d:ip3,4\u6bb5+\u7aef\u53e3\u53f7,\u82e5\u4e09\u6bb5\u4e3a0,\u5219\u524d\u9762\u52a01.\r\n\r\n\r\n\r\n[mysqldump]\r\n\r\nquick\r\n\r\nmax_allowed_packet = 256M<\/pre>\n
————————————-mysqld-multi—————————————<\/p>\n
1.6.3 \u542f\u52a8mysqld_multi\r\n#mv \/etc\/init.d\/mysqld \/etc\/init.d\/mysqld.server\r\n\r\n#chmod \u2013x \/etc\/init.d\/mysqld.server\r\n\r\n#cp -a \/usr\/local\/mysql\/support-files\/mysqld_multi.server \/etc\/init.d\/mysqld_multi\r\n\r\n\r\n\r\n#\/etc\/init.d\/mysqld_multi\u4f7f\u7528\u65b9\u6cd5\uff1a\r\n\r\n#\/etc\/init.d\/mysqld_multi start\u00a0 #\u542f\u52a8\u6240\u6709\u5b9e\u4f8b\r\n\r\n#\/etc\/init.d\/mysqld_multi stop\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 #\u505c\u6b62\u6240\u6709\u5b9e\u4f8b\r\n\r\n#\/etc\/init.d\/mysqld_multi start 1\u00a0 #\u542f\u52a8\u7f16\u53f7\u4e3a1\u7684\u5b9e\u4f8b\r\n\r\n#\/etc\/init.d\/mysqld_multi start 2\u00a0\u00a0\u00a0 #\u542f\u52a8\u7f16\u53f7\u4e3a2\u7684\u5b9e\u4f8b\r\n\r\n#\/etc\/init.d\/mysqld_multi stop 1\u00a0 #\u505c\u6b62\u7f16\u53f7\u4e3a1\u7684\u5b9e\u4f8b\r\n\r\n#\/etc\/init.d\/mysqld_multi stop 2\u00a0 #\u505c\u6b62\u7f16\u53f7\u4e3a2\u7684\u5b9e\u4f8b\r\n\r\n#\/etc\/init.d\/mysqld_multi restart 1 #\u91cd\u542f\u7f16\u53f7\u4e3a1\u7684\u5b9e\u4f8b\r\n\r\n#\/etc\/init.d\/mysqld_multi restart 2 #\u91cd\u542f\u7f16\u53f7\u4e3a2\u7684\u5b9e\u4f8b\r\n\r\n#\/etc\/init.d\/mysqld_multi report\u00a0 #\u67e5\u770b\u6240\u6709\u5b9e\u4f8b\u8fd0\u884c\u72b6\u6001\r\n\r\n#\/etc\/init.d\/mysqld_multi report 1 #\u67e5\u770b\u7f16\u53f7\u4e3a1\u7684\u5b9e\u4f8b\u7684\u8fd0\u884c\u72b6\u6001\r\n\r\n#\/etc\/init.d\/mysqld_multi report 2 #\u67e5\u770b\u7f16\u53f7\u4e3a2\u7684\u5b9e\u4f8b\u7684\u8fd0\u884c\u72b6\u6001<\/pre>\n
1.7 \u5907\u4efd\u4e0e\u6062\u590d<\/strong><\/h2>\n
\u5907\u4efd\u4e0e\u6062\u590d\u8fd9\u90e8\u5206\u76ee\u524d\u53ef\u9009\u5de5\u5177\u67092\u4e2a\uff0c\u5206\u522b\u4e3aMysqldump\u4e0eXtrabackup\uff1a\u76ee\u524d\u6682\u65f6\u7528Mysqldump\u5de5\u5177\u6765\u5b9e\u65bd\u6570\u636e\u5e93\u7684\u5907\u4efd\u65b9\u6848\u3002<\/p>\n
1.7.1 Mysqldump\uff1a\u8be6\u60c5\u8bf7\u89c1\u6570\u636e\u5e93\u5bfc\u5165\u548c\u5bfc\u51fa\u3002<\/h3>\n
1.7.2 Xtrabackup<\/h3>\n
1.7.2.1 \u5b89\u88c5<\/strong><\/h4>\n
#yum install\u00a0http:\/\/www.percona.com\/downloads\/percona-release\/redhat\/0.1-3\/percona-release-0.1-3.noarch.rpm<\/a><\/p>\n
#yum install percona-xtrabackup<\/a><\/p>\n
1.7.2.2 \u5907\u4efd\u811a\u672c<\/strong><\/h4>\n
\u6b64\u811a\u672c\u57fa\u4e8e\u524d\u4e00\u4e2a\u5907\u4efd(\u53ef\u80fd\u662f\u5168\u5907\uff0c\u4e5f\u53ef\u80fd\u662f\u589e\u5907)\u6765\u5224\u65ad\u63a5\u4e0b\u6765\u8be5\u6267\u884c\u54ea\u4e00\u4e2a\u5907\u4efd\uff0c7\u5929\u4e00\u8f6e(\u4e00\u4e2a\u5168\u5907\uff0c6\u4e2a\u589e\u5907)\uff0c\u6570\u636e\u4e8b\u5b9e\u4e0a\u4fdd\u755914\u5929<\/strong><\/p>\n
—————–xtrabackup.sh—————————<\/p>\n
cat \/opt\/xtrabackup\/xtrabackup.sh\r\n#!\/bin\/bash\r\nBEGINTIME=`date +\"%Y-%m-%d %H:%M:%S\"` #begining time\r\nformat_time=`date +\"%Y-%m-%d_%H:%M:%S\"`\r\n#oldDate=$(date -d \"-7 days\" \"+%Y-%m-%d_%H:%M:%S\")\r\noldDate=$(date -d \"-7 days\" \"+%Y-%m-%d_%H\")\r\nweek=`date +%w` #week number\r\nbackdir=\/data\/Xtrabackup #backup folder\r\n#file_cnf=\/etc\/my.cnf\r\nfile_cnf=\/data\/mysql3306\/my.cnf\r\nuser_name=root\r\npassword=123456\r\nout_log=$backdir\/xtrabackup_log_$format_time\r\ntime_cost=$backdir\/xtrabackup_time.txt\r\nif [ -d \"$backdir\/incr6\" ];then\r\nrm -rf $backdir-${oldDate}*\r\nmv $backdir $backdir-$format_time\r\nmkdir $backdir\r\nelse\r\nmkdir -p $backdir\r\nfi<\/pre>\n
###### Start backup now, Monday full backup other days is incremental backup ######<\/p>\n
if [ ! -d \"$backdir\/full\" ];then\r\necho \"#####start monday full backup at $BEGINTIME to directory full\" >>$time_cost\r\ninnobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password $backdir\/full 1> $out_log 2>&1\r\necho \"#####finash monday full backup at `date +\"%Y-%m-%d %H:%M:%S\"` to directory full\" >>$time_cost\r\nexit 1\r\nfi\r\nif [ ! -d \"$backdir\/incr1\" ];then\r\necho \"#####start incremental 1 backup at $BEGINTIME to directory incr1\" >>$time_cost\r\ninnobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental-basedir=$backdir\/full --no-timestamp --incremental $backdir\/incr1 1> $out_log 2>&1\r\nexit 1\r\nfi\r\nif [ ! -d \"$backdir\/incr2\" ];then\r\necho \"#####start incremental 2 backup at $BEGINTIME to directory incr2\" >>$time_cost\r\ninnobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental --incremental-basedir=$backdir\/incr1 $backdir\/incr2 1> $out_log 2>&1\r\nexit 1\r\nfi\r\nif [ ! -d \"$backdir\/incr3\" ];then\r\necho \"#####start incremental 3 backup at $BEGINTIME to directory incr3\" >>$time_cost\r\ninnobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental --incremental-basedir=$backdir\/incr2 $backdir\/incr3 1> $out_log 2>&1\r\nexit 1\r\nfi\r\nif [ ! -d \"$backdir\/incr4\" ];then\r\necho \"#####start incremental 4 backup at $BEGINTIME to directory incr4\" >>$time_cost\r\ninnobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental --incremental-basedir=$backdir\/incr3 $backdir\/incr4 1> $out_log 2>&1\r\nexit 1\r\nfi\r\nif [ ! -d \"$backdir\/incr5\" ];then\r\necho \"#####start incremental 5 backup at $BEGINTIME to directory incr5\" >>$time_cost\r\ninnobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental --incremental-basedir=$backdir\/incr4 $backdir\/incr5 1> $out_log 2>&1\r\nexit 1\r\nfi\r\nif [ ! -d \"$backdir\/incr6\" ];then\r\necho \"#####start incremental 6 backup at $BEGINTIME to directory incr6\" >>$time_cost\r\ninnobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental --incremental-basedir=$backdir\/incr5 $backdir\/incr6 1> $out_log 2>&1\r\nexit 1\r\nfi### END weekly backup ###<\/pre>\n
—————–xtrabackup.sh—————————<\/p>\n
1.7.2.3 \u6062\u590d\u811a\u672c<\/h4>\n
Xtrabackup\u5907\u4efd\u51fa\u6765\u7684\u6587\u4ef6\uff0c\u5728\u8fdb\u884c\u6062\u590d\u65f6\uff0c\u8bf7\u6ce8\u610f\u4ee5\u4e0b\u51e0\u70b9\uff1a<\/p>\n
1). \u6709\u51e0\u4e2a\u5907\u4efd\u6587\u4ef6\uff0c\u5c31\u4f7f\u7528\u54ea\u51e0\u6761\u6062\u590d\u8bed\u53e5\uff0c\u987a\u5e8f\u6267\u884c\uff0c\u591a\u4f59\u7684\u8bed\u53e5\u6ce8\u91ca\u6389<\/p>\n
2). \u5982\u679c\u53ea\u6709\u4e00\u4e2a\u5168\u5907\uff0c\u65e0\u589e\u91cf\u5907\u4efd\uff0c\u5728\u6062\u590d\u65f6\u53ef\u5c06\u5168\u5907\u6062\u590d\u547d\u4ee4\u4e2d\u7684–redo-only\u914d\u7f6e\u9879\u53bb\u6389\uff0c\u4ee5\u5728\u6062\u590d\u7684\u65f6\u5019\u5373\u751f\u6210ib_logfile<\/p>\n
3). \u5982\u679c\u5b58\u5728\u589e\u5907\uff0c\u5728\u6062\u590d\u65f6\u5b8c\u6240\u6709\u589e\u91cf\u5907\u4efd\u540e\uff0c\u53ef\u9009\u62e9\u5bf9\u6062\u590d\u540e\u7684\u5168\u91cf\u518d\u6267\u884c\u4e00\u6b21apply log\uff0c\u4ee5\u751f\u6210ib_logfile\u3002<\/p>\n
4).\u5982\u679c\u4e0d\u6309 \u7b2c2\u548c\u7b2c3\u6761\u65b9\u5f0f\u6267\u884c\uff0c\u6062\u590d\u540e\u7684full\u76ee\u5f55\u4e2d\u662f\u6ca1\u6709ib_logfile\u6587\u4ef6\u7684\uff0c\u4f1a\u5728\u542f\u52a8\u6570\u636e\u5e93\u7684\u65f6\u5019\u6839\u636e\u6307\u5b9a\u7684\u914d\u7f6e\u6587\u4ef6\u6765\u91cd\u65b0\u751f\u6210\u3002<\/p>\n
—————–xtrarecover.sh—————————<\/p>\n
#!\/bin\/bash\r\n\r\n#backdir=\/databackup\/db_backup\/Xtrabackup\r\nbackdir=\/data\/Xtrabackup\r\n\r\ninnobackupex --apply-log --redo-only --use-memory=4G $backdir\/full\/ 1>$backdir\/huifu.log 2>&1\r\ninnobackupex --apply-log --redo-only --use-memory=4G $backdir\/full\/ --incremental-dir=$backdir\/incr1\/ 1>$backdir\/huifu.log1 2>&1\r\ninnobackupex --apply-log --redo-only --use-memory=4G $backdir\/full\/ --incremental-dir=$backdir\/incr2\/ 1>$backdir\/huifu.log2 2>&1\r\ninnobackupex --apply-log --redo-only --use-memory=4G $backdir\/full\/ --incremental-dir=$backdir\/incr3\/ 1>$backdir\/huifu.log3 2>&1\r\ninnobackupex --apply-log --redo-only --use-memory=4G $backdir\/full\/ --incremental-dir=$backdir\/incr4\/ 1>$backdir\/huifu.log4 2>&1\r\ninnobackupex --apply-log --redo-only --use-memory=4G $backdir\/full\/ --incremental-dir=$backdir\/incr5\/ 1>$backdir\/huifu.log 2>&1\r\ninnobackupex --apply-log --use-memory=4G $backdir\/full\/ --incremental-dir=$backdir\/incr6\/ 1>$backdir\/huifu.log 2>&1\r\n\r\ninnobackupex --apply-log --redo-only --use-memory=4G $backdir\/full\/ 1>$backdir\/huifu.log 2>&1 \u00a0#\u53ef\u9009\r\ninnobackupex --defaults-file=\/data\/mysql3307\/my.cnf --user=root --copy-back $backdir\/full\/\r\nchown mysql.mysql \/data\/mysql3307\/data\/ -R\r\ncd \/usr\/local\/mysql\r\nbin\/mysqld_safe --defaults-file=\/data\/mysql3307\/my.cnf &<\/pre>\n
—————–xtrarecover.sh—————————<\/p>\n
1.7.2.4 Xtrabackup\u6ce8\u610f\u4e8b\u9879<\/h4>\n
1). Xtrabackup\u5907\u4efd\u51fa\u6765\u7684\u6587\u4ef6\u53ea\u80fd\u6062\u590d\u5230\u540c\u7248\u672c\u4f1a\u66f4\u9ad8\u7248\u672c\u7684\u6570\u636e\u5e93\u5b9e\u4f8b\u4e2d\u53bb\uff0c\u80fd\u5411\u4e0a\u517c\u5bb9\uff0c\u4e0d\u80fd\u5411\u4e0b\u517c\u5bb9\u3002\u6bd4\u5982\uff0cMysql5.6\u7684\u5b9e\u4f8b\u5907\u4efd\u51fa\u6765\u7684\u6587\u4ef6\uff0c\u65e0\u6cd5\u5bfc\u5165Mysql5.5\u7248\u672c\u7684\u5b9e\u4f8b\u3002Mariadb\u4e5f\u4e00\u6837\u3002<\/p>\n
2). Mysql\u5b9e\u4f8b\u5907\u4efd\u51fa\u6765\u7684\u6587\u4ef6\u4e5f\u65e0\u6cd5\u5bfc\u5165\u5230Mariadb\u7684\u5b9e\u4f8b\u4e2d\uff0c\u4e0d\u517c\u5bb9\u3002<\/p>\n
1.8 Mysql\u4e3b\u4ece\u914d\u7f6e(Replication)<\/strong><\/h2>\n
\u57fa\u4e8eMysqldump\u5de5\u5177\u6765\u63d0\u4f9b\u5907\u4efd\u6587\u4ef6\u4ee5\u53caMaster\u7684\u76f8\u5173\u4fe1\u606f\u3002<\/p>\n
1.8.1 \u73af\u5883\u51c6\u5907<\/h3>\n
Master: 10.10.0.222 3306<\/p>\n
Slave:\u00a0 10.10.0.222 3307<\/p>\n
\u9700\u8981\u540c\u6b65\u7684\u6570\u636e\u5e93: test1,test2<\/p>\n
1.8.2 \u51c6\u5907\u5907\u4efd\u6587\u4ef6<\/h3>\n
#mysqldump -uroot -p -h127.0.0.1 -P3306 --single-transaction --master-data=2 --databases test1 test2 |gzip > test1_2.2.sql.gz<\/pre>\n
1.8.3 \u4e3b\u5e93\u4e0a\u6dfb\u52a0\u6388\u6743<\/h3>\n
(root@127.0.0.1:3306) [(none)]>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'127.0.0.1'\u00a0 IDENTIFIED BY\u00a0 'repl';\r\n\r\n(root@127.0.0.1:3306) [(none)]>flush privileges;<\/pre>\n
1.8.4 \u4fee\u6539\u4ece\u5e93\u914d\u7f6e\u6587\u4ef6\uff0c\u589e\u52a0\u4ece\u5e93\u53c2\u6570<\/h3>\n
——————————————-slave.cnf————————————–<\/p>\n
read_only\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\u00a0 #\u8fd9\u4e2a\u53c2\u6570\u6839\u636e\u662f\u5426\u5728\u540c\u6b65\u5916\u8fd8\u9700\u8981\u521b\u5efa\u522b\u7684\u6570\u636e\u5e93\u6765\u5b9a\r\n\r\nslave_net_timeout\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 10\r\n\r\nrelay_log\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/log\/relay-bin\r\n\r\nrelay_log_index\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/log\/relay-bin.index\r\n\r\nmaster_info_file\u00a0 =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/log\/master.info\r\n\r\nrelay_log_info_file\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/data\/mysql3307\/log\/relay.info\r\n\r\nsync_master_info\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 #\u8fd9\u4e24\u4e2async\u503c\u9ed8\u8ba4\u4e3a10000,\u662f\u7f13\u5b58\u5199\u7684,\u610f\u5473\u7740\u5982\u679cslave\u5b95\u673a\u91cd\u542f,\u8fd9\u4fe9\u6587\u4ef6\u7f13\u5b58\u7684\u4fe1\u606f\u4f1a\u6e05\u7a7a,relay_log_recovery\u4f1a\u8ba9slave\u91cd\u65b0\u53bbmaster\u4e0a\u83b7\u53d6relay_log,\u4ece\u800c\u5bfc\u81f4\u4e3b\u4ece\u4e0d\u4e00\u81f4.\u662f\u5426\u5f00\u542f\u8981\u770bslave\u78c1\u76d8IOPS\u662f\u5426\u662f\u4e3b\u4ece\u5ef6\u65f6\u7684\u539f\u56e0.\r\n\r\nsync_relay_log_info\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n\r\nrelay_log_recovery\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 #slave\u5b95\u673a\u5219\u6e05\u9664\u672c\u5730relay_log\u5e76\u91cd\u65b0\u4ecemaster\u83b7\u53d6relay_log\r\n\r\nskip_slave_start\r\n\r\nlog_slave_updates\r\n\r\n\r\n\r\n#replicate_wild_ignore_table=mysql.%\r\n\r\n#replicate_wild_ignore_table=test.%\r\n\r\nreplicate_wild_do_table=test1.% #\u4f7f\u7528\u8be5\u53c2\u6570\u663e\u5f0f\u6307\u5b9a\u4e86\u67d0\u4e2a\u5e93\u8981\u540c\u6b65,\u5219\u522b\u7684\u9700\u8981\u540c\u6b65\u7684\u5e93\u90fd\u9700\u8981\u663e\u5f0f\u6307\u5b9a,\u5426\u5219,\u4e0d\u4f1a\u540c\u6b65\r\n\r\nreplicate_wild_do_table=test2.%<\/pre>\n
——————————————-slave.cnf————————————–<\/p>\n
 <\/p>\n
1.8.5 \u4ece\u5e93\u91cd\u542f\uff0c\u5bfc\u5165\u5907\u4efd\u5e76\u914d\u7f6e\u540c\u6b65<\/h3>\n
\/etc\/init.d\/mysqld_multi restart 2\r\n\r\n(root@127.0.0.1:3307) [(none)]>set sql_log_bin=0;\r\n\r\ngunzip -f < test1_2.2.sql.gz |mysql -uroot -p -h127.0.0.1 -P3307\r\n\r\n(root@127.0.0.1:3307) [(none)]>set sql_log_bin=1;\r\n\r\n(root@127.0.0.1:3307) [(none)]>change master to master_host='127.0.0.1',master_port=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000017',master_log_pos=120;\r\n\r\n(root@127.0.0.1:3307) [(none)]>start slave;\r\n\r\n(root@127.0.0.1:3307) [(none)]>show slave status \\G;\r\n\r\n<\/pre>\n
1.8.6 \u4e3b\u4ece\u540c\u6b65\u7684\u4e00\u81f4\u6027\u95ee\u9898<\/h3>\n
\u7531\u4e8e\u76ee\u524d\u6211\u4eec\u91c7\u7528\u7684\u662f\u5f02\u6b65\u590d\u5236\u6a21\u5f0f\uff0c\u6240\u4ee5\u5b58\u5728\u5982\u4e0bcrash safe\u95ee\u9898\uff1a<\/p>\n
\u7b2c\u4e00\uff0c<\/strong>Master crash safe<\/strong><\/p>\n
\u9488\u5bf9\u8fd9\u4e2a\u95ee\u9898\uff0c\u6211\u4eec\u53ef\u4ee5\u901a\u8fc7\u4fee\u6539\u5982\u4e0b\u53c2\u6570\u6765\u5c3d\u6700\u5927\u53ef\u80fd\u7684\u51cf\u5c11Mysql\u8fdb\u7a0b\u5b95\u673a\u6216\u8005Mysql Server\u670d\u52a1\u5668\u5b95\u673a\u5e26\u6765\u7684crash\u95ee\u9898\u3002\u5bf9\u4e3b\u5e93\u7684\u914d\u7f6e\u6587\u4ef6\u5b9a\u4e49\u5982\u4e0b\u53c2\u6570\uff1a<\/p>\n
innodb_flush_log_at_trx_commit\u00a0 =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/p>\n
sync_binlog\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/p>\n
innodb-xa-support\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1<\/p>\n
innodb_flush_log_at_trx_commit<\/strong>\uff1a<\/strong><\/p>\n
\u8bbe\u4e3a1\uff0cMysql\u5728\u6bcf\u4e00\u6b21\u4e8b\u52a1\u63d0\u4ea4\u90fd\u4f1a\u628ainnodb log buffer\u4e2d\u7684\u6570\u636e\u5199\u5165ib_logfile\u5e76\u5237\u5165\u5230\u78c1\u76d8\uff0c\u53ea\u5728DB\u670d\u52a1\u5668\u5b95\u673a\u7684\u60c5\u51b5\u4e0b\u624d\u4f1a\u4e22\u5931\u4e00\u4e2a\u4e8b\u52a1\u3002<\/p>\n
\u8bbe\u4e3a0\uff0cmysql\u4f1a\u6bcf\u79d2\u4e00\u6b21\u5199\u5165ib_logfile\uff0c\u7136\u540e\u7531OS\u6765\u51b3\u5b9a\u5237\u5165\u78c1\u76d8\u7684\u7b56\u7565\u3002<\/p>\n
\u8bbe\u4e3a2\uff0cmysql\u4f1a\u6bcf\u6b21\u4e8b\u52a1\u63d0\u4ea4\u5199\u5165ib_logfile\uff0c\u7136\u540e\u6bcf\u79d2\u4e00\u6b21\u5237\u5165\u78c1\u76d8\uff0c\u4f46\u662f\u8fd9\u4e2a\u6bcf\u79d2\u4e00\u6b21\u5e76\u4e0d\u662f100%\u4fdd\u8bc1\u3002<\/p>\n
sync_binlog:<\/strong><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/strong>\u8bbe\u4e3aN\uff08N>0\uff09\uff0cMysql\u4f1a\u5728\u6bcf\u5199N\u6b21binlog\u65f6\uff0c\u5c06binlog_cache\u4e2d\u7684\u6570\u636e\u5237\u5230\u78c1\u76d8\uff0c\u4ee5\u4fdd\u8bc1\u6570\u636e\u5e93\u4e2d\u6570\u636e\u4e0ebinlog\u7684\u4e00\u81f4\u6027\u3002<\/p>\n
\u8bbe\u4e3a0\uff0cMysql\u4e0d\u5237binlog\u5230\u78c1\u76d8\uff0c\u800c\u4f9d\u8d56OS\u7684\u7b56\u7565\u6765\u5237\u78c1\u76d8\u3002<\/p>\n
innodb-xa-support<\/strong><\/p>\n
\u8bbe\u4e3a1\uff0c\u652f\u6301binlog\u4e0einnodb redo log\u4e4b\u95f4\u7684\u6570\u636e\u4e00\u81f4\u6027\u3002<\/p>\n
\u4e0d\u5229\u5f71\u54cd\uff1a<\/strong><\/p>\n
\u589e\u52a0\u4e86\u78c1\u76d8IO\u538b\u529b\uff0c\u90e8\u5206\u964d\u4f4e\u6027\u80fd\uff0c\u81f3\u4e8e\u964d\u4f4e\u7684\u767e\u5206\u6bd4\uff0c\u9700\u8981\u6839\u636e\u5177\u4f53\u73af\u5883\u5177\u4f53\u6d4b\u8bd5\u624d\u80fd\u5f97\u51fa\u3002\u53e6\u5916\uff0cMysql5.5\u4e0a\u5b58\u5728bug\uff0c\u5982\u679c\u8bbe\u5b9ainnodb_flush_log_at_trx_commit\u548csync_binlog\u4e3a1\uff0c\u4f1a\u5bfc\u81f4group commit\u5931\u6548\uff0c\u5bfc\u81f4\u6027\u80fd\u5927\u5e45\u4e0b\u964d\uff0c\u57285.6\u7248\u672c\u4e2d\uff0c\u8be5bug\u5df2\u89e3\u51b3\u3002<\/p>\n
\u7b2c\u4e8c\uff0c<\/strong>Slave crash safe<\/strong><\/p>\n
sync_master_info = 1<\/strong>\uff0csync_relay_log_info = 1<\/strong><\/p>\n
\u8fd9\u4e24\u4e2async\u503c\u9ed8\u8ba4\u4e3a10000\uff0c\u8fd9\u4e2a10000\u662f\u5199\u5728\u7f13\u5b58\u91cc\u7684\uff0c\u610f\u5473\u7740\u5982\u679cslave\u5b95\u673a\u91cd\u542f\uff0c\u8fd9\u4fe9\u6587\u4ef6\u7f13\u5b58\u7684\u4fe1\u606f\u5728flush\u5230\u78c1\u76d8\u4e4b\u524d\u88ab\u6e05\u7a7a\uff0c \u800crelay_log_recovery=1\u4f1a\u8ba9slave\u91cd\u65b0\u53bbmaster\u4e0a\u83b7\u53d6relay_log\uff0c\u4ece\u800c\u5bfc\u81f4\u4ece\u5e93\u91cd\u590d\u5199(\u51fa\u73b0\u59821062\u9519\u8bef\u7b49)\uff0c\u7ee7\u800c\u5bfc\u81f4\u4e3b\u4ece\u4e0d\u4e00\u81f4\u3002\u662f\u5426\u5f00\u542f\u8981\u770bslave\u673a\u5668\u78c1\u76d8IOPS\u662f\u5426\u662f\u5bfc\u81f4\u4e3b\u4ece\u5ef6\u65f6\u7684\u539f\u56e0\u3002<\/p>\n
relay_log_recovery = 1<\/strong><\/p>\n
slave\u5b95\u673a\u5219\u6e05\u9664\u672c\u5730\u5df2\u7ecf\u63a5\u6536\u7684relay_log\u5e76\u91cd\u65b0\u4ecemaster\u83b7\u53d6relay_log\u3002\u8fd9\u4e2a\u91cd\u65b0\u62c9\u53d6\u7684\u70b9\uff0c\u53d6\u51b3\u4e8erelay_log_info\u6587\u4ef6\u4e2d\u7684\u8bb0\u5f55\u3002<\/p>\n
\u4f46\u662f\u5728\u4ece\u5e93\u4e0a\uff0c\u5373\u4f7f\u8bbe\u5b9a\u4e86\u5982\u4e0a\u4e09\u4e2a\u53c2\u6570\uff0c\u5982\u679c\u4ece\u5e93\u5b95\u673a\uff0c\u4e5f\u662f\u4f1a\u4e22\u5931\u81f3\u5c11\u4e00\u4e2a\u4e8b\u52a1\u7684\u3002\u6240\u4ee5\u5176\u5b9e\u8fd8\u6709\u53e6\u4e00\u79cd\u914d\u7f6e\u65b9\u6848\uff0crelay-log.info\u7684\u4fe1\u606f\u4fdd\u5b58\u5728innodb\u7684\u4e8b\u52a1\u8868\u4e2d\uff0c\u5982\u679c\u51fa\u73b0slave crash\uff0c\u5219\u4f1a\u56de\u6eda\uff1a<\/p>\n
relay_log_info_repository = TABLE<\/p>\n
master_info_repository=TABLE<\/p>\n
\u4e0d\u8fc7\u8fd9\u79cd\u65b9\u6848\u6211\u8fd8\u9700\u8981\u518d\u9a8c\u8bc1\u6d4b\u8bd5\uff0c\u76ee\u524d\u6682\u672a\u91c7\u7528\u3002<\/p>\n
\u6392\u9664\u94fe\u5f0f\u590d\u5236\u7684\u73af\u5883\uff0c\u4ece\u5e93\u4e0a\u53ef\u4ee5\u8003\u8651\u5c06innodb_flush_log_at_trx_commit\u8bbe\u4e3a2\uff0c\u5173\u95edbinlog\u3002<\/strong><\/p>\n
\u4e3b\u4ece\u4e00\u81f4\u6027\u68c0\u6d4b\u4e0e\u4fee\u590d\u5de5\u5177\uff1a\u6709\u5f85\u8c03\u7814\u548c\u6d4b\u8bd5<\/strong><\/p>\n
\u2022 pt-table-checksum<\/p>\n
\u2022 pt-table-sync<\/p>\n
2. MariaDB\u5b89\u88c5\u914d\u7f6e\u6587\u6863<\/strong><\/h1>\n
2.1 \u8f6f\u4ef6\u7248\u672c<\/strong><\/h2>\n
\u91c7\u7528MariaDB 10.0.20\u7248\u672c<\/p>\n
2.2 \u8f6f\u4ef6\u5b89\u88c5<\/strong><\/h2>\n
\u91c7\u7528\u4e8c\u8fdb\u5236\u65b9\u5f0f\u5b89\u88c5<\/strong><\/p>\n
#yum install zlib-devel openssl-devel gcc-c++ gcc bison cmake ncurses-devel -y\r\n\r\n#wget\u00a0https:\/\/downloads.mariadb.org\/interstitial\/mariadb-10.0.20\/bintar-linux-x86_64\/mariadb-10.0.20-linux-x86_64.tar.gz\/from\/http%3A\/\/mirrors.accretive-networks.net\/mariadb\r\n\r\n#groupadd -g 27 mysql\u00a0 #linux\u9ed8\u8ba4\u4f7f\u752827\u4f5c\u4e3amysql\u7528\u6237\u7684gid\u548cuid\uff0c\u5efa\u8bae\u6240\u6709\u670d\u52a1\u5668\u7edf\u4e00\u3002\r\n\r\n#useradd -g 27 -u 27 -r -s \/sbin\/nologin -M mysql\r\n\r\n#tar \u2013zxvf mariadb-10.0.20-linux-x86_64.tar.gz\u00a0 -C\u3000\/usr\/local\/\r\n\r\n#mv \/usr\/local\/ mariadb-10.0.20-linux-x86_64\u00a0 \/usr\/local\/mariadb10.0.20\r\n\r\n#mkdir -p \/data\/mariadb3308\/{data,log,tmp}\r\n\r\n#chown mysql.mysql \/data\/mariadb3308\/ -R\r\n\r\n#sed -i 's\/mysql3307\/mariadb3308\/g' my.cnf\r\n\r\n#sed -i '\/^basedir\/s\/.*\/basedir\\ =\\ \\\/usr\\\/local\\\/mariadb10.0.20\/g' my.cnf<\/pre>\n
\u521d\u59cb\u5316\u6570\u636e\u5e93\u5b9e\u4f8b<\/strong><\/p>\n
#cd \/usr\/local\/mariadb10.0.20\/\r\n\r\n#scripts\/mysql_install_db --defaults-file=\/data\/mariadb3308\/my.cnf\r\n\r\n\u670d\u52a1\u63a7\u5236\uff0c\u6388\u6743\uff0c\u591a\u5b9e\u4f8b\u7b49\u8bf7\u53c2\u8003mysql\u7684\u914d\u7f6e\uff0c\u5728\u6b64\u6211\u4e3b\u8981\u4f7f\u7528mysqld_safe\u76f4\u63a5\u542f\u52a8\u670d\u52a1\u6765\u6d4b\u8bd5\u591a\u4e3b\u4e00\u4ece\uff0c\u5177\u4f53\u5b89\u88c5\u914d\u7f6e\u7684\u8bdd\u5c31\u4e0d\u8be6\u8ff0\u4e86\u3002\r\n\r\n\r\n\r\n#\/usr\/local\/mariadb10.0.20\/bin\/mysqld_safe --defaults-file=\/data\/mariadb3308\/\r\n\r\nmy.cnf &\r\n\r\n\u62a5\u4e86\u4e24\u4e2aWarning\u5982\u4e0b\uff0c\u5ffd\u7565\uff1a\r\n\r\n[Warning] 'explicit-defaults-for-timestamp' is MySQL 5.6 compatible option. To be implemented in later versions.\r\n\r\n[Warning] 'log-throttle-queries-not-using-indexes' is MySQL 5.6 compatible option. To be implemented in later versions.<\/pre>\n
2.3 Mariadb\u591a\u4e3b\u4e00\u4ece\u914d\u7f6e<\/strong><\/h2>\n
2.3.1 \u73af\u5883\u4ecb\u7ecd<\/h3>\n
Mariadb<\/strong>\u591a\u4e3b\u4e00\u4ece\u6709\u4e00\u4e2a\u524d\u63d0\uff1a\u5404\u4e2a\u4e3bMysql\u670d\u52a1\u5668\u7684\u6570\u636e\u5e93(Schema)\u8981\u552f\u4e00<\/strong><\/p>\n
Master1: 10.10.0.222 3306\u00a0 test1,test2<\/p>\n
Master2: 10.10.0.222 3307 \u00a0test3,test4 (\u4e0e3306\u7684\u4e3b\u4ece\u5173\u7cfb\u5728\u6b64\u6e05\u9664\u6389)<\/p>\n
Slave:\u00a0\u00a0\u00a0 10.10.0.222 3308<\/p>\n
2.3.2 \u6388\u6743\u4e0e\u5907\u4efd<\/h3>\n
\u5728\u672c\u673a\u76843306\u548c3307\u8fd9\u4e24\u4e2a\u5b9e\u4f8b\u4e0a\u5206\u522b\u6388\u6743\uff1a\r\n\r\nmysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'127.0.0.1'\u00a0 IDENTIFIED BY\u00a0 'repl';\r\n\r\nmysql>flush privileges;\r\n\r\n\u5206\u522b\u5907\u4efd3306\u5b9e\u4f8b\u7684test1,test2\u548c3307\u5b9e\u4f8b\u7684test3,test4\r\n\r\n#mysqldump -uroot -p -h127.0.0.1 -P3306 --single-transaction --master-data=2 --databases test1 test2 |gzip > test1test2.sql.gz\r\n\r\n#mysqldump -uroot -p -h127.0.0.1 -P3307 --single-transaction --master-data=2 --databases test3 test4 |gzip > test3test4.sql.gz<\/pre>\n
2.3.3 \u4fee\u6539\u4ece\u5e93\u914d\u7f6e\uff0c\u5bfc\u5165\u4ece\u5e93<\/h3>\n
\u4fee\u6539\u914d\u7f6e\uff1a<\/strong><\/p>\n
———————————–slave my.cnf——————————————-<\/p>\n
server_id\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 102223308\u00a0\u00a0\u00a0 #\u4e3a4\u5b57\u8282\u6574\u6570:0-2\u768432\u6b21\u65b9-1(0-4294967295),\u547d\u540d:ip3,4\u6bb5+\u7aef\u53e3\u53f7,\u82e5\u4e09\u6bb5\u4e3a0,\u5219\u524d\u9762\u52a01.\r\n\r\nread_only\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\r\n\r\nslave_net_timeout\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 10\r\n\r\nrelay_log\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mariadb3308\/log\/relay-bin\r\n\r\nrelay_log_index\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mariadb3308\/log\/relay-bin.index\r\n\r\nmaster_info_file\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mariadb3308\/log\/master.info\r\n\r\nrelay_log_info_file\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 \/data\/mariadb3308\/log\/relay.info\r\n\r\nsync_master_info\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1 #\u8fd9\u4e24\u4e2async\u503c\u9ed8\u8ba4\u4e3a10000,\u662f\u7f13\u5b58\u5199\u7684,\u610f\u5473\u7740\u5982\u679cslave\u5b95\u673a\u91cd\u542f,\u8fd9\u4fe9\u6587\u4ef6\u7f13\u5b58\u7684\u4fe1\u606f\u4f1a\u6e05\u7a7a,relay_log_recovery\u4f1a\u8ba9slave\u91cd\u65b0\u53bbmaster\u4e0a\u83b7\u53d6relay_log,\u4ece\u800c\u5bfc\u81f4\u4e3b\u4ece\u4e0d\u4e00\u81f4.\u662f\u5426\u5f00\u542f\u8981\u770bslave\u78c1\u76d8IOPS\u662f\u5426\u662f\u4e3b\u4ece\u5ef6\u65f6\u7684\u539f\u56e0.\r\n\r\nsync_relay_log_info\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1\r\n\r\nrelay_log_recovery\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 1 #slave\u5b95\u673a\u5219\u6e05\u9664\u672c\u5730relay_log\u5e76\u91cd\u65b0\u4ecemaster\u83b7\u53d6relay_log\r\n\r\nskip_slave_start\r\n\r\nlog_slave_updates\r\n\r\n\r\n\r\n#replicate_wild_ignore_table=mysql.%\r\n\r\n#replicate_wild_ignore_table=test.%\r\n\r\nreplicate_wild_do_table=test1.%\r\n\r\nreplicate_wild_do_table=test2.%\r\n\r\nreplicate_wild_do_table=test3.%\r\n\r\nreplicate_wild_do_table=test4.%<\/pre>\n
———————————–slave my.cnf——————————————-<\/p>\n
\u5bfc\u5165\u4ece\u5e93\uff1a<\/strong><\/p>\n
(root@127.0.0.1:3308) [(none)]>CREATE DATABASE IF NOT EXISTS test1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;\r\n\r\n(root@127.0.0.1:3308) [(none)]>CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;\r\n\r\n(root@127.0.0.1:3308) [(none)]>CREATE DATABASE IF NOT EXISTS test3 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;\r\n\r\n(root@127.0.0.1:3308) [(none)]>CREATE DATABASE IF NOT EXISTS test4 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;\r\n\r\ngunzip -f < test1test2.sql.gz |mysql -uroot -p -h127.0.0.1 -P3308\r\n\r\ngunzip -f < test3test4.sql.gz |mysql -uroot -p -h127.0.0.1 -P3308<\/pre>\n
2.3.4 \u914d\u7f6e\u591a\u4e3b\u4e00\u4ece<\/h3>\n
(root@127.0.0.1:3308) [(none)]>change master '3306' to master_host='127.0.0.1',MASTER_PORT=3306,master_user='repl', master_password='repl',master_log_file='mysql-bin.000019', master_log_pos=1691;\r\n\r\n(root@127.0.0.1:3308) [(none)]>change master '3307' to master_host='127.0.0.1',MASTER_PORT=3307,master_user='repl', master_password='repl',master_log_file='mysql-bin.000011', master_log_pos=3506;\r\n\r\n\u591a\u4e2a\u901a\u9053\u64cd\u4f5c\uff1a\r\n\r\n(root@127.0.0.1:3308) [(none)]>start all slaves;\r\n\r\n(root@127.0.0.1:3308) [(none)]>show all slaves status \\G;\r\n\r\n\u5355\u4e2a\u901a\u9053\u64cd\u4f5c\uff1a\r\n\r\n(root@127.0.0.1:3308) [(none)]>stop slave '3306';\r\n\r\n(root@127.0.0.1:3308) [(none)]>start slave '3306';\u2019\r\n\r\n(root@127.0.0.1:3308) [(none)]>show slave '3306' status \\G;\r\n\r\n\u6e05\u7406slave\u4fe1\u606f\uff1a\r\n\r\nMysql>reset slave 'connect_name' all;\u00a0 \u5982reset slave '3306' all;\r\n\r\n\u5355\u901a\u9053\u9519\u8bef\u5904\u7406\uff1a\r\n\r\n(root@localhost:mysql.sock) [gjdyzjb]>stop slave 'zzb'; \u00a0#\u505c\u6389\u5355\u901a\u9053\r\n\r\n(root@localhost:mysql.sock) [gjdyzjb]>set @@default_master_connection='zzb'; \u00a0 \u00a0#\u6307\u5b9a\u9ed8\u8ba4\u7684\u901a\u9053\r\n\r\n(root@localhost:mysql.sock) [gjdyzjb]>select @@default_master_connection; \u00a0#\u786e\u8ba4\u6307\u5b9a\u7684\u9ed8\u8ba4\u7684\u901a\u9053\r\n\r\n(root@localhost:mysql.sock) [gjdyzjb]>SET GLOBAL sql_slave_skip_counter =1; \u00a0#\u8df3\u8fc7\u9519\u8bef\r\n\r\n(root@localhost:mysql.sock) [gjdyzjb]>start slave 'zzb'; \u00a0#\u5f00\u542f\u5355\u901a\u9053<\/pre>\n
Binlog\u6e05\u7406\u4e0e\u8c03\u6574<\/p>\n
show binary logs;\r\n\r\nshow variables like '%expire%';\r\n\r\nset global expire_logs_days=3;\r\n\r\nPURGE MASTER LOGS TO 'mysql-bin.001989';\r\n\r\nPURGE\u00a0MASTER\u00a0LOGS\u00a0BEFORE\u00a0'2003-04-02\u00a022:46:26';<\/pre>\n","protected":false},"excerpt":{"rendered":"
1. \u00a0Mysql\u5b89\u88c5\u914d\u7f6e\u6587\u6863 1.1 \u8f6f\u4ef6\u7248\u672c \u91c7\u7528Mysql5.6.26(M … \u7ee7\u7eed\u9605\u8bfb →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[],"class_list":["post-120","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts\/120","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=120"}],"version-history":[{"count":3,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts\/120\/revisions"}],"predecessor-version":[{"id":126,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts\/120\/revisions\/126"}],"wp:attachment":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}