如何配置高效的數據庫以及MySQL的代碼及插件開發 · 2016學習日記

#MySQL安裝和基本配置 ##Linux 下的 MySQL 安裝注意之一 ? Linux Generic版 ? 下載社區版 ? 配置好 /etc/my.cnf 也可以指定(用于啟動多個端口) ? 注意在 mysql 目錄下 scripts/mysql_install_db ? 注意 data 目錄的權限 ? 一旦出錯,從日志中尋找原因 ##MySQL 怎么編譯? ? 在源碼目錄下 cmake CMakeLists.txt ? 生成 BUILD 目錄 ? 進入 BUILD 目錄, cmake /var/software/mysql-5.6.22 再進入 /var/software/mysql-5.6.22 ? make ##安裝,默認庫介紹配置文件選擇 my-huge.cnf my-innodb-heavy-4G.cnf my-large.cnf my-medium.cnf my-small.cnf ##基本配置 1. Socket 位置確定 2. 開啟數據庫的帳號和權限 1. grant ALL PRIVILEGES on quanzhan.* to ‘quanzhan’@‘101.251.196.%' identified by 'xxxxx' with grant option; 3. 訪問配置:端口開啟,限定IP,禁止DNS解析等 1. skip-name-resolve ##mysql啟動項 1. mysqld_safe與mysqld 2. 終端指定配置選項 1. bind-address 3. root密碼忘記怎么辦 ![](https://box.kancloud.cn/2016-05-05_572afeeb391bc.png) ##存儲引擎的區別和選擇 **存儲引擎是mysql提供的文件訪問層的一個抽象接口來定制一種文件訪問機制。** **mysql的存儲引擎包括**:MyISAM、InnoDB、BDB、MEMORY、MERGE、 EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等, **MyISAM:**不支持事務,支持全文索引,表級鎖。適合處理讀頻率遠大于寫頻率 **InnoDB:**支持事務,5.6以后支持全文索引,行級鎖。適用于高并發讀寫 #MySQL體系結構與存儲引擎 ? Server Initialization Module ? 命令行、配置文件解析、內存分配 ? Connection Manager ? 協議監聽和協議轉發 ? Thread Manager ? 新建線程處理請求 ? Connection Thread ? 新建新的,或者從線程緩存中去取 ? User Authentication Module ? 驗證用戶身份 ? Access Control Module ? Parser ? 接收請求,解析進入命令分發或者進入查詢 ? Command Dispatcher ? 解析器分發給命令分發器 ? Query Cache Module ? 查詢緩存檢查(SELECT、DELETE、UPDATE) ? Optimizer ? 查詢優化器 ? Table Manager ? 打開表,獲取鎖 ? Table Modification Modules ? 表更新 ? Table Maintenance Module ? 表維護 ? Status Reporting Module ? 狀態報告 ? Abstracted Storage Engine Interface (Table Handler) ? 抽象引擎接口 ? Storage Engine Implementations (MyISAM, InnoDB...) ? 存儲引擎實現 ? Logging Module ? 日志記錄 ? Replication Master Module ? 復制主模塊 ? Replication Slave Module ? 復制從模塊 ? Client/Server Protocol API ? 客戶端、服務器協議 API ? Low-Level Network I/O API ? 底層網絡 I/O API ? Core API ? 核心 API ![](https://box.kancloud.cn/2016-05-05_572afeeb99496.png) ![](https://box.kancloud.cn/2016-05-05_572afeebc9b50.png) ###MySQL 源代碼目錄 ? BUILD 編譯腳本 ? client 命令行工具代碼(mysql,mysqladmin) ? cmd-line-utils 增強命令行的第三方庫 (libedit 如 readline). ? dbug 調試庫 ? libevent 由于 5.6 支持某個插件的庫 ? plugin 插件所在的庫 ? libmysql MySQL 的庫,其他客戶端,經如C/PHP 訪問 MySQL需要引用這個目錄的庫 ? mysys 核心可移植性或者工具API ? regex 正則表達式庫 ? scripts 腳本庫,如 mysqld_safe 所在 ? sql MySQL 的核心所在,用C++所寫 ? sql-bench 測評腳本目錄 ? sql-common 客戶端、服務器能用代碼 ? strings 字符串庫 ? storage 存儲引擎所在的庫 ? vio 底層網絡I/O操作庫 ? zlib 庫 #MySQL的配置參數和優化驗證 ##連接數(connection)配置 max_connections 1. 連接數相關配置 2. 連接數過低演示 ##查詢緩存(query_cache)配置 query_cache 1. 相同SQL語句的查詢會有緩存 2. 查詢緩存使用條件 3. 查詢配置優化 4. query_cache_size, query_cache_limit, query_cache_type ##查詢緩存(query_cache)效果 1:緩存效果計算: 命中率 ≈ qcache_hits / (qcache_hits + com_select) 緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks 緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size 2:如何提高緩存命中率 3:命中率演示 ##打開表緩存(table_cache)配置 table_cache 1. 設置表高速緩存的數目,更快速地訪問表 2. MyISAM和InnoDB的table_cache參數區別 3. 調整和效果演示 ##臨時表緩存(tmp_table_size)配置 tmp_table_size 1. 復雜語句查詢時會自動創建的臨時表 2. 效果監測和優化 3. 相關配置max_heap_table_size ##其它緩沖區配置 1. sort_buffer_size 增加索引解決 2. key_buffer_size MyISAM 3. innodb_buffer_pool_size 物理內存的 60%-70% 4. read_buffer_size ##binlog配置 1. 二進制日志配置log-bin 2. 性能和安全的取舍 sync_binlog ![](https://box.kancloud.cn/2016-05-05_572afeec02014.png) #MySQL運行狀態報告 ##MySQL 管理工具集 1. show status 太爛了 2. 稍微人性化一點的工具 mysqlreport ##MySQL 管理工具集- percona-toolkit 1. 重復鍵檢測 pt-duplicate-key-checker --host='101.251.196.91' -- user='root' --password='xxx' 2、 pt-mysql-summary --host='101.251.196.91' --user='root' --password='xxx' #MySQL 插件的開發與了解 ? 我們熟悉的大部分功能由插件構成, show plugins ? 在 MySQL 運行時加載外部庫 ? 熱加載,熱生效 ? 使用 C/C++ 開發 ? 簡單的直接 GCC 編譯,復雜的就跟軟件差不多 ? 注意: /usr/local/mysql/bin/mysql_config --cflags ? 插件的安裝和卸載 ? install plugin、uninstall plugin ? 插件的原理 ? 常規結構 ? monitor_plugin_init ? monitor_plugin_deinit ? mysql_declare_plugin(monitor_plugin) ? mysql_declare_plugin_end ? 單線程的開發 ? 多線程比較常見,用 pthread 創建處理程序 ? 插件的類型 ? Storage engines ? Full-text parsers ? Daemons ? INFORMATION_SCHEMA tables ? 這是啥東東? ? Semisynchronous replication ? Auditing ? Authentication ? 打包編譯 ? 建立 Makefile.am ? 建立 configure.ac ? libtoolize --force ? aclocal ? autoheader ? touch NEWS; touch README; touch AUTHORS; touch ChangeLog ? automake --force-missing --add-missing ? autoconf ? UDF ? CREATE AGGREGATE FUNCTION udf_floatsum RETURNS REAL SONAME 'udf_floatsum.so'; ? CREATE FUNCTION udf_intexample RETURNS INTEGER SONAME 'udf_intexample.so'; ? SELECT udf_intexample(2543); ##MySQL 兩個簡單的插件開發 ? 代碼編寫 ? HelloWorld ? 編譯HelloWorld ? gcc -ohello_world.so hello_world.c `/usr/local/mysql/bin/mysql_config --cflags` -shared -fPIC - DMYSQL_DYNAMIC_PLUGIN ? 兩個監控插件,一個帶端口,一個不帶端口 ? g++ -o newmonitor.so newmonitor.c `/usr/local/mysql/bin/mysql_config --cflags` -shared -fPIC - DMYSQL_DYNAMIC_PLUGIN -I/var/software/mysql-5.6.20/sql/ - L/usr/local/mysql/lib/ -I/var/software/mysql-5.6.20/include/ - fpermissive ? 編譯安裝 ? ./configure --with-mysql-source=../mysql-5.6.20 -- with-mysql-bindir=/usr/local/mysql/bin/ ? 使用 ? 代碼構成了解 ? 評測(通過PHP 模塊來操作,對比直接插入SQL、與使用HandlerSocket 插入) ? 端口讀寫分離 ![](https://box.kancloud.cn/2016-05-05_572afeec39d2b.png) ![](https://box.kancloud.cn/2016-05-05_572afeec6f2a4.png) ##MySQL Innodb-Memcached Plugin 安裝 ? source /usr/local/mysql/share/innodb_memcached_config.sql ? 注意要把已有的 memcached 關掉 ? install plugin daemon_memcached soname "libmemcached.so"; 評測 ? memslap ? memaslap ? Plugin 評測 ? Memcached Cache Get ? Time: 5983989 Gets/Sec: 16711.260665753 ? Memcached Cache Set ? Time: 92655677 Gets/Sec: 1079.26468445 ##安裝監控工具MRTG