伺服器架構之性能擴充-第四章（5）第四章、MYSQL主從複制和分區技術

Mysql作為web資料庫，對于越來越大的資料存儲，主從複制、分區技術和SQL語句優化，對于提高通路量有很大的幫助。

主伺服器首先對遠端伺服器建立一個授權使用者，可以在從伺服器上登入主伺服器的資料庫。

Mysql>use mysql

Mysql>select user,password,host from user; //檢視使用者情況

測試：從伺服器登陸，可以看到内容和主伺服器是一樣的

Mysql的bin-log日志是記錄與資料的增删改查有關的資料庫語句。于是bin-log可以作為資料庫恢複的檔案來使用，也是mysql能夠主從複制的基礎。

Vi /etc/my.cnf

Log-bin=mysql-bin //增加這個語句，重新開機mysql即可

Mysql>show variables like ‘%bin%’;

mysql>flush logs; //此時就會多一個新的bin-log日志

mysql>show master status; //檢視目前使用的bin-log日志

mysql>reset master; //清空所有日志

備份資料庫：測試資料庫名為test，表為t。

Mysqldump –uroot –p test –l –F > ‘/tmp/test.sql’

-L鎖定表，-F是重新整理bin-log日志，這樣便可以用重新整理之前的日志進行恢複到備份時刻

Mysql>insert into t values(1); //插入新紀錄

Mysq>flush logs;

Mysql>drop table t; //删除表

恢複資料庫

Mysql test </tmp/test.sql

如果要恢複資料庫備份後那段時間的記錄，就需要恢複bin-log日志

Mysqlbinlog mysql-00002.bin |mysql –uroot –p test

下圖是bin-log的日志内容我們可以看到它是包含position點的。恢複日志也可以指定position點。--stop-position=“193”.

Mysql主從複制的優點：

1.如果主伺服器出現問題，可以快速切換到從伺服器提供服務

2.可以在從伺服器上執行檢視操作，降低主伺服器的通路壓力

3.可以在從伺服器上執行備份，以避免備份期間影響主伺服器的服務

Log-bin=mysql-bin //開啟log-bin功能

Server-id=1 //指定server ID

Service mysql restart

Mysql>show master status; //檢視是否開啟和bin ID

指定主伺服器ip和server id（這裡id要不為1），以及主伺服器的端口和連接配接主伺服器的使用者名和密碼，開啟bin-log，然後重新開機mysql伺服器，同步之前要確定主從伺服器有相同的資料庫名和表結構。

Mysql>show slave status\G

這裡要保證兩個yes選項，第一個slave_io_running 為yes表示可以成功從主伺服器上讀取binlog日志并寫入從服務上，第二個slave_Sql_running為yes表示可以成功讀取本機binlog日志同步資料。可以看到它們也具有相同的bin-log。

如果資料無法同步：

如果是slave_io_running失敗，要檢查一下從資料庫能否登陸主資料，然後檢查從資料庫是否和主資料有一緻的資料庫和表結構。

如果是slave_sql_running失敗，要檢查從服務是否啟動和配置檔案。

如slave stop； set global sql_slave_skip_counter=1;slave start.

當mysql的資料記錄超過千萬或上億條紀錄時，資料的性能會出現下降，這時就要用到分表分區技術或優化sql語句的方法來提高資料庫的使用效率。

分表技術通常有垂直分表和水準分表，垂直分表是對資料庫字段進行分表；水準分表是對資料記錄進行分表。垂直分表技術複雜，不易操作，水準分表更常用。

水準分表可以将一個表分成多個表，但是如果多個表使用不同的表名，則查詢語句要更改，于是引入了分區分表的概念，它不改變表名。

分區分表是在邏輯層進行水準分表，對與應用程式而言，它依然是一張表。Mysql5.1中，它有5鐘分區類型：

Range分區：基于屬于一個給定的連續區間的列值，把比對的多行配置設定給相應分區

List分區：類似range分區，差別在于list分區是基于列值比對一個離散值集合中的某個值來進行選擇

Hash分區：基于使用者定義的表達式的傳回值進行選擇的分區，該表達式使用将要插入到表中的這些行的列值進行計算，這個函數可以包含mysql中有效的，産生非負整數值的任何表達式

Key分區：類似于hash分區，差別在于key分區隻支援計算一列或多列，且mysql伺服器提供其自身的哈希函數

檢視mysql的版本資訊：mysql>status;

檢視是否支援分區技術：mysql>show plugins; //看到partion選項

例1：假定如下的表，該表有20加音像店的職員記錄，這二十家的音像編号從1到20，如果采用range分區為4個小分區，如何實作呢？

Mysql>create table employees(

>id int not null,

>fname varchar(30),

>lname varchar(30),

>hired date not null default ‘1970-01-01’,

>separated date not null default ‘9999-12-31’,

>job_code int not null,

>store_id int not null

<a>>partition by range(store_id)(</a>

<a></a>

>partition p0 values less than(6),

>partition p1 values less than(11),

>partition p2 values less than(16),

>partition p3 values less than(21),

>);

根據store_id（店号）進行分區，less than根據店号ID進行分區。

如果要按離職時間進行分區可以使用：

>partition by range(year（separated）)(

>partition p0 values less than(1991),

>partition p1 values less than(1996),

>partition p2 values less than(2001),

>partition p3 values less than maxvalue,

>); //使用year（）函數轉為整形

List分區類似于range分區，但他是根據離散值來分區。

例3.還是該表儲存有20家音像店，編号從1到20，而這20家音像店分布在四個區域：

>partition by list(store_id)(

>partition pnorth values in (3,5,6,9,17),

>partition peast values in(1,2,10,11,19,20),

>partition pwest values in(4,12,13,14,18),

>partition pcentral values in( 7,8,15,16),

Hash分區主要是用來保護資料在預先确定數目的分區中平均分布。他可以在基于使用者定義的表達式的傳回值來進行選擇分區，該表達式使用将要插入到表中的這些行的列值進行計算

例4：把不同時期的入職時間進行hash

>Partition by hash（year（hired））

>partitions 4

例5、

可以檢視到數庫分為5個分區，從0到4，在加上frm檔案和par檔案，供12個檔案。

不同分區技術對比

建立索引，也是一種提高資料庫效率的方法。

Mysql>Create index idx_id on t2(id);

Mysql>show index from t2;

首先建立一個表，插入幾個資料進行測試，然後使用select into t2 select * from t2 ；進行倍數的增加

再打開一個終端，發現是均勻配置設定資料的。還有一個發現，有時索引比資料更大，造成空間浪費。

插入1000萬行記錄，然後看時間和大小，1000萬條記錄。

檢視資料庫檔案大小

存儲過程：是一種通過建立語句，然後調用資料庫的方法。

通過存儲過程灌入800萬條測試資料庫

Mysql>delimiter //

Mysql>create procedure load_part_tab()

Begin

Declare v <8000000

Insert into part_tab

Values (v,’testing partitions’,adddate(‘2000-01-01’,(rand(v)*36520)mod 3652));

Set v=v+1;

End while;

End

Mysql>delimiter;

分别插入數值和字段“testingpartition“和随機時間。

Mysql>? procedure //檢視存儲幫助

再建立一個兩個表，一個使用分區執行個體，一個不使用分區執行個體，然後調用存儲過程即可完成内容的插入，最後測試。

Mysql分區執行個體：

Partition by range(year(c3))(

Partition p0 values less than(2000),

Partition p1values less than(2001),

Partition p2 values less than(2002),

Partition p3 values less than(2003),

Partition p4 values less than(2004),

Partition p5 values less than(2005),

Partition p6 values less than(2006),

Partition p7 values less than(2006),

Partition p8 values less than(2007),

Partition p9 values less than(2008),

Partition p10 vlues less than maxvalue);

建立未分區執行個體：

Mysql> create table no_part_tab (c1 int default null,c2 varchar(30) default null, c3 date default null ) engine=myisam;

利用存儲過程插入資料：

Mysql>call load_part_tab();

Mysql>inset into no_part_tab select * from part_tab;

測試：SQL性能

Mysql>select count(*) from part_tab where c3>date ‘2000-01-01’ and c3<’2000-12-31’; //大約0.55秒

Mysql> select count(*) from no_part_tab where c3>date ‘2000-01-01’ and c3<’2000-12-31’; //大約5秒

相差10倍，說明分區技術可以有效降低查詢時間。

檢索範圍檢視到，分區表查找範圍明顯很小為79萬。

如果建立索引再檢視結果：

Mysql>create index_c3 on no_part_tab(c3);

Mysql> create index_c3 on part_tab(c3);

測試：

發現建立索引之後測試通路時間發現明顯二者都顯著變小，說明索引也是一種優化查詢的方法。下圖是查找未索引的字段。

Innodb是一種事務型的表存儲引擎。對于innodb表，要區分兩個概念。共享表空間和獨占表空間。

共享表空間：某個資料庫的所有表的資料，存儲在一個資料檔案中，預設的檔案名為ibdata1，初始化為10MB。

獨占表空間：每一個表都将會生成獨立的檔案來存儲資料，每一個表除了一個frm檔案還有一個.ibd檔案。

隻有把innodb設定成獨立表空間以後，才能建立innodb的表分區。

Vi /etc/my.cnf //将innodb_file_per_table =1,重新開機mysql即可

然後建立表，指定引擎和分區情況，檢視資料檔案即可看到分區檔案順利生成。

本文轉自zsaisai 51CTO部落格，原文連結:http://blog.51cto.com/3402313/966578

伺服器架構之性能擴充-第四章（5）第四章、MYSQL主從複制和分區技術

繼續閱讀

淺談企業活動中進行資料分析的重要性

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Ambari介紹和架構原理

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

win10本地scala和spark安裝安裝scala安裝spark

伺服器架構之性能擴充-第四章（5） 第四章、MYSQL主從複制和分區技術

繼續閱讀

伺服器架構之性能擴充-第四章（5）第四章、MYSQL主從複制和分區技術