{"id":2843,"date":"2016-09-18T19:01:34","date_gmt":"2016-09-18T11:01:34","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2843"},"modified":"2016-09-18T19:01:34","modified_gmt":"2016-09-18T11:01:34","slug":"mysql%e4%b8%bb%e4%bb%8e%e7%8e%af%e5%a2%83%e6%90%ad%e5%bb%ba","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2843.html","title":{"rendered":"MySQL\u4e3b\u4ece\u73af\u5883\u642d\u5efa"},"content":{"rendered":"

=Start=<\/p>\n

\u7f18\u7531\uff1a<\/h4>\n

\u5de5\u4f5c\u9700\u8981\uff0c\u5b66\u4e60\u9700\u8981\u3002<\/p>\n

\u6b63\u6587\uff1a<\/h4>\n

MySQL\u7684\u4e3b\u4ece\u67b6\u6784\uff1a<\/h6>\n

\u7531\u4e00\u4e2aMySQL\u5b9e\u4f8b\u4f5c\u4e3a\u4e3b\u5e93\uff0c\u63a5\u53d7\u8bfb\u5199\u8bf7\u6c42\uff0c\u53e6\u5916\u4e00\u4e2a\u6216\u591a\u4e2aMySQL\u5b9e\u4f8b\u901a\u8fc7MySQL\u7684\u590d\u5236\u673a\u5236\uff0c\u5c06\u4e3b\u5e93\u7684\u6570\u636e\u540c\u6b65\u5230\u4ece\u5e93\uff0c\u4ece\u5e93\u4f1a\u8bbe\u7f6e\u53ea\u8bfb\uff0c\u9632\u6b62\u4e3b\u4ece\u6570\u636e\u4e0d\u4e00\u81f4\u3002<\/p>\n

\u4e3b\u4ece\u67b6\u6784\u7684\u597d\u5904\uff1a<\/h6>\n
    \n
  1. \u5982\u679c\u4e3b\u670d\u52a1\u5668\u51fa\u73b0\u95ee\u9898\uff0c\u53ef\u4ee5\u5feb\u901f\u5207\u6362\u5230\u4ece\u670d\u52a1\u5668\u63d0\u4f9b\u670d\u52a1\uff1b<\/li>\n
  2. \u53ef\u4ee5\u5728\u4ece\u670d\u52a1\u5668\u4e0a\u6267\u884c\u67e5\u8be2\uff0c\u964d\u4f4e\u4e3b\u670d\u52a1\u5668\u7684\u538b\u529b\uff1b<\/li>\n
  3. \u53ef\u4ee5\u5728\u4ece\u670d\u52a1\u5668\u4e0a\u6267\u884c\u5907\u4efd\uff0c\u4ee5\u907f\u514d\u5907\u4efd\u671f\u95f4\u5f71\u54cd\u4e3b\u670d\u52a1\u5668\u7684\u6027\u80fd\u3002<\/li>\n<\/ol>\n
    \u4e3b\u4ece\u67b6\u6784\u7684\u5c40\u9650\uff1a<\/h6>\n

    \u7531\u4e8eMySQL\u5b9e\u73b0\u7684\u662f\u5f02\u6b65\u590d\u5236\uff0c\u6240\u4ee5\u4e3b\u4ece\u670d\u52a1\u5668\u4e4b\u95f4\u7684\u6570\u636e\u5b58\u5728\u4e00\u5b9a\u5dee\u5f02\uff08\u5ef6\u8fdf\uff09\uff0c\u5bf9\u5b9e\u65f6\u6027\u8981\u6c42\u9ad8\u7684\u6570\u636e\u4ecd\u7136\u9700\u8981\u4ece\u4e3b\u670d\u52a1\u5668\u4e0a\u83b7\u5f97\u3002<\/p>\n

    \u4e3b\u4ece\u642d\u5efa\u6b65\u9aa4\uff1a<\/h5>\n
    \n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n
    \n
    \n
    Step<\/div>\n<\/div>\n<\/th>\n
    		\n
    \n
    \u4e3b\u5e93\uff08Master\uff09<\/div>\n<\/div>\n<\/th>\n
    		\n
    \n
    \u4ece\u5e93\uff08Slave\uff09<\/div>\n<\/div>\n<\/th>\n<\/tr>\n<\/thead>\n
    1<\/td>\n\n

    \u4fee\u6539\u4e3b\u5e93my.cnf\u914d\u7f6e\u6587\u4ef6\uff0c\u7136\u540e\u9700\u8981\u91cd\u542f\u751f\u6548<\/span><\/strong><\/p>\n

    [mysqld]\n\n#\u4f5c\u4e3a\u4e3b\u5e93\n#\u542f\u52a8\u4e8c\u8fdb\u5236\u6587\u4ef6(*)\nlog-bin=mysql-bin\n#\u670d\u52a1\u5668ID(*)\nserver-id=1\n\n#\u9700\u8981\u5907\u4efd\u7684\u6570\u636e\u5e93\uff0c\u591a\u4e2a\u5199\u591a\u884c\nbinlog-do-db=orders\n#\u4e0d\u9700\u8981\u5907\u4efd\u7684\u6570\u636e\u5e93\uff0c\u591a\u4e2a\u5199\u591a\u884c\nbinlog-ignore-db=mysql<\/pre>\n<\/td>\n
    		\n

    \u4fee\u6539\u4ece\u5e93my.cnf\u914d\u7f6e\u6587\u4ef6\uff0c\u7136\u540e\u9700\u8981\u91cd\u542f\u751f\u6548<\/span><\/strong><\/p>\n

    [mysqld]\n\n#\u4f5c\u4e3a\u4ece\u5e93\uff08\u5982\u679c\u6709\u591a\u4e2a\u4ece\u5e93\uff0c\u5728\u5404\u81ea\u7684my.cnf\u4e2d\u7684server-id\u9700\u8981\u5404\u4e0d\u76f8\u540c\uff09\nserver-id=2\n\nreplicate-do-db=bash  #\u53ea\u590d\u5236\u67d0\u4e2a\u5e93\uff0c\u591a\u4e2a\u5199\u591a\u884c\nreplicate-ignore-db=mysql #\u4e0d\u590d\u5236\u67d0\u4e2a\u5e93\uff0c\u591a\u4e2a\u5199\u591a\u884c\nreplicate-ignore-db=test\nreplicate-ignore-db=information_schema<\/pre>\n<\/td>\n<\/tr>\n
    \u00a02<\/td>\n\u521b\u5efa\u4ece\u5e93\u7528\u4e8e\u8fde\u63a5\u4e3b\u5e93\u7684\u8d26\u53f7(repl)<\/p>\n
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip' IDENTIFIED BY 'password';\nmysql> flush privileges;<\/pre>\n<\/td>\n
    		\u4f7f\u7528\u521a\u521b\u5efa\u7684 repl \u8d26\u6237\u5728Slave\u6240\u5728\u4e3b\u673a\u4e0a\u767b\u5f55Master\uff0c\u6d4b\u8bd5\u7528\u4e8e\u590d\u5236\u7684\u8d26\u6237\u662f\u5426\u53ef\u4ee5\u8fde\u63a5\u4e0a
    \nmysql -h master_ip -u repl -p<\/code><\/td>\n<\/tr>\n
    3<\/td>\n\u8bb0\u5f55\u540c\u6b65\u70b9<\/p>\n
    mysql> FLUSH TABLES WITH READ LOCK; \/\/\u82e5\u8981\u540c\u6b65\u7684\u6570\u636e\u5e93\u4e2d\u5df2\u7ecf\u6709\u6570\u636e\uff0c\u8fd8\u9700\u8981\u5148\u7528\u8be5\u8bed\u53e5\u9501\u5b9a\u6570\u636e\u5e93\nmysql> SHOW MASTER STATUS; \/\/\u67e5\u770bMaster\u72b6\u6001\uff0c\u8bb0\u5f55File\u548cPosition\u4e24\u4e2a\u4fe1\u606f<\/pre>\n<\/td>\n
    		<\/td>\n<\/tr>\n
    4<\/td>\n\n

    \u5c06\u4e3b\u5e93\u6570\u636e\u505a\u903b\u8f91\u5907\u4efd\u6216\u7269\u7406\u5907\u4efd<\/p>\n

    mysql> flush tables with read lock;\n\nshell> mysqldump -uroot -p --all-databases --master-data > backup.sql\n\nshell> scp -C backup.sql user@slave_ip:\/tmp\/\n\nmysql> unlock tables;<\/pre>\n<\/td>\n
    		<\/td>\n<\/tr>\n
    5<\/td>\n<\/td>\n\u5728\u4ece\u5e93\u4e0a\u6062\u590d\u6570\u636e<\/p>\n
    shell> mysql -uroot -p <\/tmp\/backup.sql\nshell> rm -rf \/tmp\/backup.sql<\/pre>\n<\/td>\n<\/tr>\n
    6<\/td>\n<\/td>\n\n

    \u4ee5root\u8eab\u4efd\u767b\u5f55\u4ece\u5e93\uff0c\u5e76\u5728\u4ece\u5e93\u4e0a\u8bbe\u7f6e\u4e3b\u5e93\u7684\u76f8\u5173\u4fe1\u606f(host\u3001port\u3001user\u7b49\u7b49)<\/p>\n

    mysql> CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;\n\nmysql> START SLAVE; \/\/\u542f\u52a8\u4e3b\u4ece\u590d\u5236\n\nmysql> SHOW SLAVE STATUS\\G \/\/\u67e5\u770bslave\u72b6\u6001<\/pre>\n<\/td>\n<\/tr>\n
    7<\/td>\n\u4e00\u5207\u987a\u5229\u7684\u8bdd\uff0c\u5230\u8fd9\u91cc\uff0cMaster\u4e0a\u7684\u64cd\u4f5c\u4f1a\u88ab\u540c\u6b65\u5230Slave\u4e0a\u3002<\/p>\n

    \u5728\u4e3b\u5e93\u521b\u5efatest_tb\u8868\uff0c\u6a21\u62df\u6570\u636e\u5e93\u65b0\u589e\u6570\u636e
    \nmysql> create table test_tb(id int,name varchar(30));<\/code><\/td>\n

    		\u5728\u4ece\u5e93\u67e5\u770b\u662f\u5426\u5b58\u5728test_tb\u8868
    \nmysql> show tables;<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n