Mysql 效能調優 二 1
Mysql 效能調優 二 1
關係型資料庫中，最耗資源的sql操作：
關聯表與排序
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系統引數：
tmp_table_size
狀態引數：
create_tmp_disk_tables
created_tmp_tables
max_tmp_table_size
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系統引數：
sort_buffer_size
innodb_sort_buffer_size
狀態引數：
Sort_merge_passes
Sort_range
Sort_rows
Sort_scan
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系統引數：
log_bin
類似於oracle歸檔日誌，但是log_bin開啟後生成的檔案是二進位制的檔案
使用binlog方法：
1. 啟用binlog
[root@test1 ~]# vi /etc/my.cnf
[mysqld]
log_bin
2. 檢視binlog日誌放置目錄
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| test1-bin.000001 | 120 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
3. 檢視binlog，並生成文字檔案
mysql> create database ss;
Query OK, 1 row affected (0.04 sec)
mysql> use ss;
Database changed
mysql> create table tmp as select * from mysql.user;
Query OK, 4 rows affected (0.48 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| test1-bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.00 sec)
[root@test1 ~]# mysqlbinlog /var/lib/mysql/test1-bin.000001 > binlog.txt
[root@test1 ~]#
[root@test1 ~]# cat binlog.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141217 12:28:03 server id 1 end_log_pos 120 CRC32 0x897a5f31 Start: binlog v 4, server v 5.6.22-log created 141217 12:28:03 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
UwaRVA8BAAAAdAAAAHgAAAABAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABTBpFUEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAATFf
eok=
'/*!*/;
# at 120
#141217 12:31:02 server id 1 end_log_pos 208 CRC32 0xf75dce06 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1418790662/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database ss
/*!*/;
# at 208
#141217 12:31:32 server id 1 end_log_pos 328 CRC32 0x55564536 Query thread_id=1 exec_time=1 error_code=0
use `ss`/*!*/;
SET TIMESTAMP=1418790692/*!*/;
create table tmp as select * from mysql.user
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@test1 ~]#
狀態引數：
binlog_cache_size
max_binlog_size
===================================================================================================
利用mysql的binlog恢復資料
MySQL Binary Log也就是常說的bin-log, ,是mysql執行改動產生的二進位制日誌檔案,其主要作用有兩個:
* 資料回覆
* 主從資料庫。用於slave端執行增刪改，保持與master同步。
1.開啟binary log功能
需要修改mysql的配置檔案，新增一句log_bin = mysql_bin即可
[mysqld]
......
log_bin = mysql_bin
......
log_bin是生成的bin-log的檔名，字尾則是6位數字的編碼，從000001開始，按照上面的配置，生成的檔案則為：
mysql_bin.000001
mysql_bin.000002
......
配置儲存以後重啟mysql的伺服器，用show variables like '%bin%'檢視bin-log是否開啟，如圖：
mysql> show variables like '%bin%';
+-----------------------------------------+--------------------------------+
| Variable_name | Value |
+-----------------------------------------+--------------------------------+
| bind_address | * |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | STATEMENT |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/test1-bin |
| log_bin_index | /var/lib/mysql/test1-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| simplified_binlog_gtid_recovery | OFF |
| sql_log_bin | ON |
| sync_binlog | 0 |
+-----------------------------------------+--------------------------------+
25 rows in set (0.00 sec)
mysql>
2.檢視產生的binary log
bin-log因為是二進位制檔案，不能透過記事本等編輯器直接開啟檢視，mysql提供兩種方式檢視方式，在介紹之前，我們先對資料庫進行一下增刪改的操作，否則log裡邊資料有點空。
create table bin( id int(10) primary key auto_increment,name varchar(255));(測試前我已經建表)
insert into bin(name) values ('orange');
1.在客戶端中使用 show binlog events in 'mysql_bin.000001' 語句進行檢視,為了排序美觀，可以在結尾加\G使結果橫變縱，此時結尾無需加；語句結束符。
eg:
mysql> show binlog events in 'mysql_bin.000001'\G
...............省略...............
*************************** 3. row ***************************
Log_name: mysql_bin.000001
Pos: 174
Event_type: Intvar
Server_id: 1
End_log_pos: 202
Info: INSERT_ID=2
*************************** 4. row ***************************
Log_name: mysql_bin.000001
Pos: 202
Event_type: Query
Server_id: 1
End_log_pos: 304
Info: use `test`; insert into bin(name) values ('orange')
*************************** 5. row ***************************
...............省略...............
Log_name:此條log存在那個檔案中，從上面可以看出這2條log皆存在與mysql_bin.000001檔案中。
Pos:log在bin-log中的開始位置
Event_type:log的型別資訊
Server_id:可以檢視配置中的server_id,表示log是那個伺服器產生
End_log_pos：log在bin-log中的結束位置
Info:log的一些備註資訊，可以直觀的看出進行了什麼操作
2.用mysql自帶的工具mysqlbinlog,這是我們就需要知道bin-log存在硬碟的什麼位置，
mysqlbinlog mysql_bin.000001
3.利用bin_log恢復資料
1. 最常用的就是回覆指定資料端的資料了，可以直接恢復到資料庫中：
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 |mysql -uroot -p123456
2. 亦可匯出為sql檔案，再匯入至資料庫中：
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 >d:\1.sql
source d:\1.sql
3. 指定開始\結束位置，從上面的檢視產生的binary log我們可以知道某個log的開始到結束的位置，我們可以在恢復的過程中指定回覆從A位置到B位置的log.需要用下面兩個引數來指定：
--start-positon="50" //指定從50位置開始
--stop-postion="100"//指定到100位置結束
最後介紹幾個bin_log的操作：（主備機）
1.產看最後一個bin日誌檔案是那個,現在位置
mysql> show mastre status;
2.啟用新的日誌檔案,一般備份完資料庫後執行
mysql> flush logs;
3.清空現有的所用bin-log
mysql> reset master;
關係型資料庫中，最耗資源的sql操作：
關聯表與排序
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系統引數：
tmp_table_size
狀態引數：
create_tmp_disk_tables
created_tmp_tables
max_tmp_table_size
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系統引數：
sort_buffer_size
innodb_sort_buffer_size
狀態引數：
Sort_merge_passes
Sort_range
Sort_rows
Sort_scan
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系統引數：
log_bin
類似於oracle歸檔日誌，但是log_bin開啟後生成的檔案是二進位制的檔案
使用binlog方法：
1. 啟用binlog
[root@test1 ~]# vi /etc/my.cnf
[mysqld]
log_bin
2. 檢視binlog日誌放置目錄
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| test1-bin.000001 | 120 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
3. 檢視binlog，並生成文字檔案
mysql> create database ss;
Query OK, 1 row affected (0.04 sec)
mysql> use ss;
Database changed
mysql> create table tmp as select * from mysql.user;
Query OK, 4 rows affected (0.48 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| test1-bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.00 sec)
[root@test1 ~]# mysqlbinlog /var/lib/mysql/test1-bin.000001 > binlog.txt
[root@test1 ~]#
[root@test1 ~]# cat binlog.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141217 12:28:03 server id 1 end_log_pos 120 CRC32 0x897a5f31 Start: binlog v 4, server v 5.6.22-log created 141217 12:28:03 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
UwaRVA8BAAAAdAAAAHgAAAABAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABTBpFUEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAATFf
eok=
'/*!*/;
# at 120
#141217 12:31:02 server id 1 end_log_pos 208 CRC32 0xf75dce06 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1418790662/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database ss
/*!*/;
# at 208
#141217 12:31:32 server id 1 end_log_pos 328 CRC32 0x55564536 Query thread_id=1 exec_time=1 error_code=0
use `ss`/*!*/;
SET TIMESTAMP=1418790692/*!*/;
create table tmp as select * from mysql.user
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@test1 ~]#
狀態引數：
binlog_cache_size
max_binlog_size
===================================================================================================
利用mysql的binlog恢復資料
MySQL Binary Log也就是常說的bin-log, ,是mysql執行改動產生的二進位制日誌檔案,其主要作用有兩個:
* 資料回覆
* 主從資料庫。用於slave端執行增刪改，保持與master同步。
1.開啟binary log功能
需要修改mysql的配置檔案，新增一句log_bin = mysql_bin即可
[mysqld]
......
log_bin = mysql_bin
......
log_bin是生成的bin-log的檔名，字尾則是6位數字的編碼，從000001開始，按照上面的配置，生成的檔案則為：
mysql_bin.000001
mysql_bin.000002
......
配置儲存以後重啟mysql的伺服器，用show variables like '%bin%'檢視bin-log是否開啟，如圖：
mysql> show variables like '%bin%';
+-----------------------------------------+--------------------------------+
| Variable_name | Value |
+-----------------------------------------+--------------------------------+
| bind_address | * |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | STATEMENT |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/test1-bin |
| log_bin_index | /var/lib/mysql/test1-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| simplified_binlog_gtid_recovery | OFF |
| sql_log_bin | ON |
| sync_binlog | 0 |
+-----------------------------------------+--------------------------------+
25 rows in set (0.00 sec)
mysql>
2.檢視產生的binary log
bin-log因為是二進位制檔案，不能透過記事本等編輯器直接開啟檢視，mysql提供兩種方式檢視方式，在介紹之前，我們先對資料庫進行一下增刪改的操作，否則log裡邊資料有點空。
create table bin( id int(10) primary key auto_increment,name varchar(255));(測試前我已經建表)
insert into bin(name) values ('orange');
1.在客戶端中使用 show binlog events in 'mysql_bin.000001' 語句進行檢視,為了排序美觀，可以在結尾加\G使結果橫變縱，此時結尾無需加；語句結束符。
eg:
mysql> show binlog events in 'mysql_bin.000001'\G
...............省略...............
*************************** 3. row ***************************
Log_name: mysql_bin.000001
Pos: 174
Event_type: Intvar
Server_id: 1
End_log_pos: 202
Info: INSERT_ID=2
*************************** 4. row ***************************
Log_name: mysql_bin.000001
Pos: 202
Event_type: Query
Server_id: 1
End_log_pos: 304
Info: use `test`; insert into bin(name) values ('orange')
*************************** 5. row ***************************
...............省略...............
Log_name:此條log存在那個檔案中，從上面可以看出這2條log皆存在與mysql_bin.000001檔案中。
Pos:log在bin-log中的開始位置
Event_type:log的型別資訊
Server_id:可以檢視配置中的server_id,表示log是那個伺服器產生
End_log_pos：log在bin-log中的結束位置
Info:log的一些備註資訊，可以直觀的看出進行了什麼操作
2.用mysql自帶的工具mysqlbinlog,這是我們就需要知道bin-log存在硬碟的什麼位置，
mysqlbinlog mysql_bin.000001
3.利用bin_log恢復資料
1. 最常用的就是回覆指定資料端的資料了，可以直接恢復到資料庫中：
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 |mysql -uroot -p123456
2. 亦可匯出為sql檔案，再匯入至資料庫中：
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 >d:\1.sql
source d:\1.sql
3. 指定開始\結束位置，從上面的檢視產生的binary log我們可以知道某個log的開始到結束的位置，我們可以在恢復的過程中指定回覆從A位置到B位置的log.需要用下面兩個引數來指定：
--start-positon="50" //指定從50位置開始
--stop-postion="100"//指定到100位置結束
最後介紹幾個bin_log的操作：（主備機）
1.產看最後一個bin日誌檔案是那個,現在位置
mysql> show mastre status;
2.啟用新的日誌檔案,一般備份完資料庫後執行
mysql> flush logs;
3.清空現有的所用bin-log
mysql> reset master;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1379437/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mysql 效能調優 二 2MySql
- Mysql 效能調優 一 1MySql
- MySQL 效能調優技巧MySql
- Mysql 效能調優 一 2MySql
- Mysql 效能調優 一 3MySql
- MySQL效能調優"經驗"MySql
- Spark學習——效能調優（二）Spark
- MySQL入門--效能調優概述MySql
- mysql 效能調優五種方式MySql
- MySQL效能診斷與調優MySql
- 全棧必備——MySQL效能調優全棧MySql
- MySQL 效能調優的10個方法MySql
- oracle效能優化（二）-調整查詢Oracle優化
- MySQL調優效能監控之show profileMySql
- MySQL調優效能監控之performance schemaMySqlORM
- mysql效能的檢查和調優方法MySql
- MySQL效能調優my.cnf詳解MySql
- MySql（七）：MySQL效能調優——鎖定機制與鎖優化分析MySql優化
- 技術更新！10個MySQL效能調優技巧MySql
- Spark 效能調優--資源調優Spark
- Spark 效能調優--Shuffle調優 SortShuffleManagerSpark
- 【效能調優】效能測試、分析與調優基礎
- ElasticSearch效能調優Elasticsearch
- Nginx 效能調優Nginx
- iOS效能調優iOS
- php效能調優PHP
- Java效能調優Java
- Spark效能調優Spark
- oracle效能調優Oracle
- Linux工具效能調優系列二：buffer和cacheLinux
- MySQL調優MySql
- MySQL效能分析和優化-part1MySql優化
- 【MySQL】效能優化之 index merge (1)MySql優化Index
- MySQL管理之道，效能調優，高可用與監控（第二版）pdf下載MySql
- （1）Linux效能調優之Linux程式管理Linux
- 效能調優實戰
- 效能調優 jstackJS
- RedHat 效能調優指南Redhat