搭建mysql的主從復制和讀寫分離-創新互聯

搭建mysql的主從復制和讀寫分離 +--------+ (write) +--------+ | client | +---------------------+| master | +--------+| | +--------+| | | | | | | | +--------+ (read) | (read) +--------+| amoeba |+---------------------|-----------+ (write) | +--------+| | | | | | | | (read)| | | +--------+| | +--------+ | +--------+ | client | +-| slave1 |+---------+----------+| slave2 | +--------+ +--------+ (replication) +--------+ master mysql:172.17.0.4 slave1 mysql:172.17.0.5 slave2 mysql:172.17.0.6 mysql-proxy(amoeba):172.17.0.8 Test host:172.17.0.7 主從復制： master mysql ：安裝mysqlserver和mysql yum install mysql-server mysql -y 修改配置文件 vim /etc/my.cnf.d/server.cnf [mysqld] server-id = 1 log-bin = master-bin 啟動mysql /etc/init.d/mysql start 修改root密碼 mysqladmin -uroot -p password mysql 登陸mysql測試并查看master狀態 mysql -uroot -pmysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 22449 Server version: 5.5.47-MariaDB-log MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 3648 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.03 sec) 創建主從同步賬號 MariaDB [(none)]> grant replication slave on *.* to 'mysqlmaster'@'172.17.0.%' identified by '123456'; slave1 mysql ：安裝mysqlserver和mysql yum install mysql-server mysql -y 修改配置文件 vim /etc/my.cnf.d/server.cnf [mysqld] server-id = 2 #id必須唯一 log-bin = slave-bin 啟動mysql /etc/init.d/mysql start 修改root密碼 mysqladmin -uroot -p password mysql 創建同步文件 MariaDB [(none)]> change master to master_host='172.17.0.4',master_user='mysqlmaster',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=3648; MariaDB [(none)]> start slave; 查看是否成功，確保下面兩項為Yes MariaDB [(none)]> show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes master mysql ：在master mysql上創建數據查看slave1 mysql是否同步 MariaDB [(none)]> create database new1; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> create database new2; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | new1 | | new2 | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) slave1 mysql : MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | new1 | | new2 | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) 我們生產環境中會碰到這種情況：備份主機數據，或者添加一臺主機。進行雙主機的結構。另外就是在線上中途添加更多的從機。而我們知道，從機上設置 slave 時要指定 master_log_file 和 master_log_pos, 即指定binlog文件和偏移值。這也就是說，從機是可以從任意位置的 binlog 文件中進行數據的同步。比如：我們將 binlog 文件備份到其它某處放置，某天，數據庫出問題了，需要對某些數據進行數據恢復，這時候從該文件中進行恢復。添加一個新的從機，可以有兩種方式：從 master 機器復制; 另一種是直接從 slave 復制. mysql-主從結構添加新的slave 兩種解決辦法 1.copy master master mysql ：鎖定數據庫 MariaDB [(none)]> flush tables with read lock; Query OK, 0 rows affected (0.04 sec) 查看主機狀態，幾下file position參數 MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 3974 | | | +------------------+----------+--------------+------------------+ 備份所有數據庫 mysqldump --all-databases -uroot -pmysql > backup.sql 拷貝到準備新加的slave主機 scp backup.sql [email protected]:/root slave2 mysql : 安裝mysqlserver和mysql yum install mysql-server mysql -y 修改配置文件 vim /etc/my.cnf.d/server.cnf [mysqld] server-id = 3 #id必須唯一 log-bin = slave2-bin 啟動mysql /etc/init.d/mysql start 修改root密碼 mysqladmin -uroot -p password mysql 導入主服務器scp過來的數據庫 mysql -uroot -pmysql < backup.sql 創建同步文件 MariaDB [(none)]> change master to master_host='172.17.0.4',master_user='mysqlmaster',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=3974; MariaDB [(none)]> start slave; 查看是否成功，確保下面兩項為Yes MariaDB [(none)]> show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes master mysql ：創建數據驗證是否添加成功 MariaDB [(none)]> create database new3; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | new1 | | new2 | | new3 | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) slave2 mysql : MariaDB [(none)]> create database new3; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | new1 | | new2 | | new3 | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) 2 copy slave 復制從庫要步驟： ==================== 可以看到，從主庫復制會有段時間鎖表，這段時間會影響主庫的使用。如果我們能直接從從庫進行復制，就不會對主庫產生影響了。但是，從從庫復制要保證的是復制過程中從庫上的數據不會發生變化，所以要先停掉從庫。 1.停止從庫: mysql> stop slave; 2.看當前從庫的狀態。和前面的看主庫狀態一樣。但現在是從從庫復制，所以查看從庫狀態：mysql> show slave status; 記下 Relay_Master_Log_file 和 Exec_Master_Log_Pos, 用處和前面一樣. 3.備份從庫數據.用 mysqldump 4.在新的從庫上還原數據 5.設置新從庫的 slave 參數.change master to master_host = '192.168.3.119',master_port = 3306,master_user = 'repl_user',master_password='root',master_log_file='master-bin.000005',master_log_pos=194244; 可以看到，雖然新從庫是從從庫復制的數據，但實際上 binlog 的 master 還是指向的主庫。另外，這里將 master_log_file 和 master_log_pos 設置成第 2 步中的 Relay_Master_Log_file 和 Exec_Master_Log_Pos start slave; mysql的主從復制+讀寫分離 +--------+ (write) +--------+ | client | +---------------------+| master | +--------+| | +--------+| | | | | | | | +--------+ (read) | (read) +--------+| amoeba |+---------------------|-----------+ (write) | +--------+| | | | | | | | (read)| | | +--------+| | +--------+ | +--------+ | client | +-| slave1 |+---------+----------+| slave2 | +--------+ +--------+ (replication) +--------+ 讀寫分離 Amoeba(變形蟲)項目，專注分布式數據庫 proxy 開發。座落與Client、DB Server(s)之間。對客戶端透明。具有負載均衡、高可用性、sql過濾、讀寫分離、可路由相關的query到目標數據庫、可并發請求多臺數據庫合并結果。再看下上面的架構圖。因為amoeba是java編寫的，所以需要先安裝java框架 Amoeba ： java安裝下載java wget http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz 創建java目錄，并解壓 mkdir -p /usr/jdk tar -xzvf jdk-7u79-linux-x64.tar.gz -C /usr/jdk 配置環境變量，在/etc/profile文件最后添加如下配置 vim /etc/profile export JAVA_HOME=/usr/jdk/ export CLASSPATH=${JAVA_HOME}/lib export PATH=${JAVA_HOME}/bin:$PATH 使配置文件生效 source /etc/profile 測試 java -version java version "1.7.0_79" Java(TM) SE Runtime Environment (build 1.7.0_79-b15) Java HotSpot(TM) 64-Bit Server VM (build 24.79-b02, mixed mode) 證明已經安裝完成 amoeba安裝配置下載解壓 wget http://ufpr.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip unzip amoeba-mysql-3.0.5-RC-distribution.zip mv amoeba-mysql-3.0.5-RC /usr/local/ 配置 vim amoeba-mysql-3.0.5-RC/conf/amoeba.xml （前段連接文件） <service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService">  <property name="port">8066</property> ///////amoeba監聽端口/////////   <property name="connectionFactory"> <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory"> <property name="sendBufferSize">128</property> <property name="receiveBufferSize">64</property> </bean> </property> <property name="authenticateProvider"> <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"> <property name="user">root</property> ///Amoeba代理用戶名///// <property name="password">mysql</property> ///Amoeba代理用戶密碼///// <property name="filter"> <bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController"> <property name="ipFile">${amoeba.home}/conf/access_list.conf</property> </bean> </property> </bean> </property> </service> ....................... ....................... <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter"> <property name="ruleLoader"> <bean class="com.meidusa.amoeba.route.TableRuleFileLoader"> <property name="ruleFile">${amoeba.home}/conf/rule.xml</property> <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property> </bean> </property> <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property> <property name="LRUMapSize">1500</property> <property name="defaultPool">master</property> ///////默認地址池//////// <property name="writePool">master</property> //////寫地址池/////////// <property name="readPool">vipdb</property> //////讀地址池//////// <property name="needParse">true</property> </queryRouter> vim amoeba-mysql-3.0.5-RC/conf/dbServers.xml （后端數據庫參數文件） <dbServer name="abstractServer" abstractive="true"> <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory"> <property name="connectionManager">${defaultManager}</property> <property name="sendBufferSize">64</property> <property name="receiveBufferSize">128</property>  <property name="port">3306</property> /////數據庫連接端口///////  <property name="schema">test</property> ////默認數據庫////  <property name="user">amoeba</property> ///主從數據庫默認連接用戶//// <property name="password">mysql</property> ////主從數據庫默認連接用戶密碼//// </factoryConfig> ............................ ............................ </dbServer> <dbServer name="master" parent="abstractServer"> +---+ <factoryConfig> |  | <property name="ipAddress">172.17.0.4</property> | </factoryConfig> | </dbServer> | | <dbServer name="slave1" parent="abstractServer"> | <factoryConfig> |  |+-------//////定義后端數據庫，dbServer可以隨意命名，但自己必須清楚哪個是主，那個是從，而且主服務器命名要和amoeba.xml中writePool相對應，從服務器對應下面 <property name="ipAddress">172.17.0.5</property> |virturl dbServer中的poolNames ip對應各個db/////// </factoryConfig> | </dbServer> | <dbServer name="slave2" parent="abstractServer"> | <factoryConfig> |  | <property name="ipAddress">172.17.0.6</property> | </factoryConfig> +---| </dbServer> <dbServer name="vipdb" virtual="true"> +---+ <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool"> |  | <property name="loadbalance">1</property> | |+-------///第一行的dbserver name命名一定和amoeba.xml的readPool相對應，property name配置從服務器的集合，可以是一個，可以是多個！  | <property name="poolNames">slave1,slave2</property> | </poolConfig> | </dbServer> +---+ vim amoeba-mysql-3.0.5-RC/jvm.properties （java虛擬機配置）把原來的這一句JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m" 改成JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16m -XX:MaxPermSize=96m" 在master，slave1，slave2分別授權dbServer.xml中定義的用戶名和密碼 MariaDB [(none)]> grant all on *.* to 'amoeba'@'%' identified by "mysql"; MariaDB [(none)]> grant all on *.* to 'amoeba'@'localhost' identified by "mysql"; 啟動amoeba /usr/local/amoeba-mysql-3.0.5-RC/bin/launcher& 查看進程(如果啟動成功，會看到如下進程) ps -ef root 467 337 0 12:51 pts/0 00:00:00 /bin/bash amoeba-mysql-3.0.5-RC/bin/launcher root 472 467 0 12:51 pts/0 00:00:05 /usr/local/jdk1.7.0_67/bin/java -server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16m root 473 467 0 12:51 pts/0 00:00:00 tail -f /usr/local/amoeba-mysql-3.0.5-RC/logs/console.log 查看監聽端口 netstat -anpl tcp 0 0 :::8066 :::* LISTEN 472/java tcp 0 0 ::ffff:172.17.0.8:8066 ::ffff:172.17.0.7:39978 ESTABLISHED 472/java tcp 0 0 ::ffff:172.17.0.8:46624 ::ffff:172.17.0.5:3306 ESTABLISHED 472/java tcp 0 0 ::ffff:172.17.0.8:41286 ::ffff:172.17.0.4:3306 ESTABLISHED 472/java Test host測試測試是拿amoeba.xml中定義的用戶名密碼去測試，千萬別被這地方繞進去 [root@7898596a875b ~]# mysql -uroot -p -h272.17.0.8 -P8066 Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1265630343 Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | new1 | | new2 | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) 測試讀寫分離 master mysql : 在master上建表 MariaDB [(none)]> use new1; MariaDB [new1]> create table student( id int(4) not null AUTO_INCREMENT, name char(20) not null, age tinyint(2) NOT NULL default '0', dept varchar(16) default NULL, primary key(id), KEY index_name (name) ); slave1,slave2 : slave1 停掉slave MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.02 sec) MariaDB [new1]> insert into new1.student(id,name) values(2,'slave'); Query OK, 1 row affected (0.03 sec) MariaDB [new1]> select * from new1.student; +----+-------+-----+------+ | id | name | age | dept | +----+-------+-----+------+ | 2 | slave | 0 | NULL | +----+-------+-----+------+ 1 row in set (0.00 sec) slave2 MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> insert into new1.student(id,name) values(3,'slave'); Query OK, 1 row affected (0.04 sec) MariaDB [(none)]> select * from new1.student; +----+-------+-----+------+ | id | name | age | dept | +----+-------+-----+------+ | 3 | slave | 0 | NULL | +----+-------+-----+------+ 1 row in set (0.00 sec) master上也插入一條數據 MariaDB [new1]> insert into new1.student(id,name) values(1,'slave'); Query OK, 1 row affected (0.02 sec) 查詢插入 MariaDB [new1]> select * from new1.student; +----+-------+-----+------+ | id | name | age | dept | +----+-------+-----+------+ | 1 | slave | 0 | NULL | +----+-------+-----+------+ 1 row in set (0.00 sec) Test host測試 MySQL [new1]> select * from new1.student; +----+-------+-----+------+ | id | name | age | dept | +----+-------+-----+------+ | 3 | slave | 0 | NULL | +----+-------+-----+------+ 1 row in set (0.00 sec) 第一次查詢發現只查詢到slave的數據，以為另一個slave讀寫分離沒有做成功，再次查詢發現 amoeba是輪詢著從從數據庫池里讀取數據 MySQL [new1]> select * from new1.student; +----+-------+-----+------+ | id | name | age | dept | +----+-------+-----+------+ | 2 | slave | 0 | NULL | +----+-------+-----+------+ 1 row in set (0.00 sec) 再插入一條數據，發現查詢不到插入的，還是只能查詢到slave上數據 MySQL [new1]> insert into student(id,name) values(4,'yufyang'); Query OK, 1 row affected (0.02 sec) MySQL [new1]> select * from new1.student; +----+-------+-----+------+ | id | name | age | dept | +----+-------+-----+------+ | 3 | slave | 0 | NULL | +----+-------+-----+------+ 1 row in set (0.00 sec) MySQL [new1]> select * from new1.student; +----+-------+-----+------+ | id | name | age | dept | +----+-------+-----+------+ | 2 | slave | 0 | NULL | +----+-------+-----+------+ 1 row in set (0.01 sec) master mysql ： MariaDB [new1]> select * from new1.student; +----+---------+-----+------+ | id | name | age | dept | +----+---------+-----+------+ | 1 | slave | 0 | NULL | | 4 | yufyang | 0 | NULL | +----+---------+-----+------+ 2 rows in set (0.00 sec) 發現剛才在測試機上通過amoeba插入的數據已經出現在主數據庫的表中開啟slave 再次查詢slave上的數據 MariaDB [new1]> select * from new1.student; +----+---------+-----+------+ | id | name | age | dept | +----+---------+-----+------+ | 1 | slave | 0 | NULL | | 2 | slave | 0 | NULL | | 4 | yufyang | 0 | NULL | +----+---------+-----+------+ 發現已經同步master，amoeba測試機上的數據了

成都創新互聯公司專注于企業全網營銷推廣、網站重做改版、遂川網站定制設計、自適應品牌網站建設、H5網站設計、購物商城網站建設、集團公司官網建設、外貿網站建設、高端網站制作、響應式網頁設計等建站業務，價格優惠性價比高，為遂川等各大城市提供網站開發制作服務。

另外有需要云服務器可以了解下創新互聯cdcxhl.cn，海內外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業上云的綜合解決方案，具有“安全穩定、簡單易用、服務可用性高、性價比高”等特點與優勢，專為企業上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。

本文名稱：搭建mysql的主從復制和讀寫分離-創新互聯
分享URL：http://m.2m8n56k.cn/article10/docgdo.html

成都網站建設公司_創新互聯，為您提供網站設計、定制網站、搜索引擎優化、企業建站、面包屑導航、軟件開發

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：[email protected]。內容未經允許不得轉載，或轉載時需注明來源：創新互聯

猜你還喜歡下面的內容

中文字幕第五页-中文字幕第页-中文字幕韩国-中文字幕最新-国产尤物二区三区在线观看-国产尤物福利视频一区二区

搭建mysql的主從復制和讀寫分離-創新互聯