自建主從復制-mycat · 服務器學習

[TOC] 參考: https://www.cnblogs.com/YingYue/p/7677814.html?tdsourcetag=s_pctim_aiomsg # （一）主庫和從庫前期準備目的：保證主庫和從庫的連接配置是正確的！ ## 1、my.ini配置 >注：windows是my.ini，Linux是my.conf ### 主庫配置 ``` server-id = 1 #[必須]服務器唯一ID，默認是1 log-bin = mysql-bin #[必須]啟用二進制日志 binlog-do-db = test_db #指定對名稱為test_db的數據庫記錄二進制日志 # binlog-ignore-db = mysql #指定不對名稱為mysql的數據庫記錄二進制日志 # binlog-ignore-db = information_schema #指定不對名稱為information_schema的數據庫記錄二進制日志 # binlog_format = mixed #binlog日志格式，mysql默認采用，如果從服務器slave有別的slave要復制那么該slave也需要這一項# expire_logs_days = 7 #超過7天的binlog刪除### skip-external-locking #MySQL選項以避免外部鎖定。該選項默認開啟 default-storage-engine = InnoDB #默認存儲引擎 lower_case_table_names = 1 #忽略表大小寫 ``` 重啟 `service mysqld restart` ### 從庫配置 ``` server-id = 2 #[必須]服務器唯一ID，默認是1 replicate-do-db = test_db #復制名稱為test_db的數據庫 replicate-ignore-db = mysql #不需要（忽略）復制名稱為mysql的數據庫 # replicate-ignore-db = information_schema #不需要（忽略）復制名稱為information_schema的數據庫 skip-external-locking #MySQL選項以避免外部鎖定。該選項默認開啟 default-storage-engine = InnoDB #默認存儲引擎 lower_case_table_names = 1 #忽略表大小寫 ``` 重啟 `service mysqld restart` ## 2、訪問權限（這一步很關鍵，多數都死在這個地方） > 目的：保證主從庫通信正常，即：主庫可以訪問從庫地址，從庫可以訪問主庫。即： mysql -h IP1 -u root -p，mysql -h IP2 -u root -p，可以正常訪問。鑒于有些人不知道，每個數據庫服務器SQL做以下代碼 ``` mysql -u root use mysql; update user set host = '%' where user = 'root'; select host, user from user; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; FLUSH PRIVILEGES ``` > 例如1：防火墻都關閉，如ECS把安全組建立連接！ > 例如2：將數據庫通信正常，授權如：grant all privileges命令！相關百度即可！ > 注意1：主庫創建數據庫，從庫不需要創建！主從庫做好后，從庫數據庫會自動創建！ > 注意2：主庫用戶為：root,從庫用戶也為：root ### a.主庫訪問用戶及數據庫 ``` create database test_db; //創建數據庫 grant all privileges on test_db.* to 'root'@'%' identified by ''; //授權，密碼為空 flush privileges; grant replication slave on *.* to 'root'@'%'; //給ip地址為所有（%表示任何ip）MySQL服務器上的盈月授權對該master的復制權限 flush privileges; ``` ### b.從庫訪問用戶及數據庫從庫只需要創建數據庫 ``` create database test_db; //創建數據庫 ``` # （二）從庫運行先運行主庫，再看從庫狀態！ ## 1、主從庫狀態 ``` show master status; /*!查看主master的狀態，如果沒有數據，需要在my.cnf 中配置*/ ``` 顯示： ``` mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 154 Binlog_Do_DB: test_db Binlog_Ignore_DB: mysql,information_schema Executed_Gtid_Set: 1 row in set (0.00 sec) ``` ## 1、從庫連接主庫并啟動 ``` show slave status; //查看該slave的狀態 stop slave; //停止從服務器 reset slave; //重置從服務器 change master to master_user='root', master_password='', master_host='192.168.100.101',master_port=3306, master_log_file='mysql-bin.000001',master_log_pos=154; //注：mysql-bin.000002是上邊的file,這個2071是上邊的Position start slave; //開啟從服務器 show slave status\G //查看從服務器連接狀態 ``` 保證結果中有兩個狀態是正常的，如下（**如果不是Yes，說明通信肯定有問題，繼續找問題**）： ``` Slave_IO_Running: Yes Slave_SQL_Running: Yes ``` >啟動后，從庫的數據庫test_db會自動生成！ # （三）測試運行 ## 1、創建增刪改（主庫） ``` create table country(id int not null primary key auto_increment, name varchar(100)); insert into country values(1, '南非'),(2,'瑞典'); delete from country where id = 1; update country set name='中國' where id =2; ``` ## 2、查（從庫） ``` select * from country where id = 2; ``` # （四）數據庫代理-mycat運行官網： http://www.mycat.io/ >注間：Linux需要安裝 java 環境，詳見《CentOS安裝JDK的四種方法，推薦yum安裝》 ## 1、配置server.xml（連接地址） >注：這里指的是連接的用戶名和密碼！ ``` <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="useSqlStat">1</property>  </system> <user name="root"> <property name="password">123456</property> <property name="schemas">TESTDB</property> </user> <user name="yingyue"> <property name="password">123456</property> <property name="schemas">TESTDB</property> </user> </mycat:server> ``` ## 2、配置schema.xml（讀寫庫地址） ``` <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema> <dataNode name="dn1" dataHost="localhost1" database="test_db" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat>  <writeHost host="hostM1" url="192.168.100.101:3306" user="root" password="">  <readHost host="hostS2" url="192.168.100.103:3306" user="yingyue" password="123456" /> </writeHost> </dataHost> </mycat:schema> ``` ## 3、啟動與連接 ``` mycat install mycat start mysql -h 192.168.100.101 -uroot -p123456 -P8066 -DTESTDB //可以正常連接mycath中間件了 (或)mysql -h 192.168.100.101 -uyingyue -p123456 -P8066 -DTESTDB ``` # （五）PHP連接mycat地址 ``` $TESTDB = [ // 數據庫類型 'type' => 'mysql', // 服務器地址 'hostname' => '192.168.100.101', // 數據庫名 'database' => 'TESTDB', // 用戶名 'username' => 'root', // 密碼 'password' => '123456', // 端口 'hostport' => '8066', }; return $TESTDB; ``` # 錯誤解決入處理 ## 1、Last_IO_Error: error connecting to master 'root@1.1.1.1:3306' - retry-time: 60 retries: 12 答：缺少這一步操作： `grant replication slave on *.* to 'root'@'%'; ` ## 2、所有操作都操作完了，Slave_IO_Running: No 依然為No 答：我居然是主從庫的配置錯了，暈死我了！ ## 3、從庫數據中斷怎么辦？從庫數據中斷就必須得先從主庫把數據恢復，再進行同步操作！