據我分析,Mysql的 InnoDB存儲引擎是支持事務的,事務開啟后沒有被主動Commit。導致該資源被長期占用,其他事務在搶占該資源時,因上一個事務的鎖而導致搶占失敗!
## 文章引言
## 實例講解
進入數據庫
```bash
mysql -uroot -p123456 -P23306
```
創建數據庫
```bash
create database hmsc DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
```
授權訪問
```bash
GRANT ALL PRIVILEGES ON hmsc.* TO 'hmsc'@'%' IDENTIFIED BY 'hmsc2020' WITH GRANT OPTION;
```
變更刷新
```bash
flush privileges;
```
打開my.cnf配置
```bash
vim /etc/my.cnf
```
修改端口為23306
```bash
[client]
port = 23306
socket = /tmp/mysql.sock
[mysql]
prompt="MySQL [\d]> "
no-auto-rehash
[mysqld]
port = 23306
socket = /tmp/mysql.sock
```
重啟mysql服務
```bash
systemctl restart mysql
```
## 問題收集
### Mysql錯誤: ERROR 1205: Lock wait timeout exceeded解決辦法
我的方法:打開/etc/my.cnf設置MySQL鎖等待超時
```
innodb_lock_wait_timeout=50
autocommit=on
```
**該類問題導致原因**
據我分析,Mysql的 InnoDB存儲引擎是支持事務的,事務開啟后沒有被主動Commit。導致該資源被長期占用,其他事務在搶占該資源時,因上一個事務的鎖而導致搶占失敗!因此出現 Lock wait timeout exceeded
## 常用命令匯總
```bash
# 查看數據庫版本
mysql -V
# 創建用戶:
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
# 移除權限:
REVOKE privilege ON test.* FROM 'test'@'%';
# 查看所有數據庫:
show databases;
```
## 高可用方案
MySQL 數據庫的高可用性分析
https://www.cnblogs.com/qq1148932219/p/11692298.html
## 提升寫入速度
配置這幾個參數,提高mysql寫入速度
當mysql大批量插入數據的時候就會變的非常慢, mysql提高insert into 插入速度的方法有三種:
**第一種插入提速方法:**
如果數據庫中的數據已經很多(幾百萬條), 那么可以 加大mysql配置中的 bulk_insert_buffer_size,這個參數默認為8M
`bulk_insert_buffer_size=100M`
**第二種mysql插入提速方法:**
改寫所有 insert into 語句為 insert delayed into
這個insert delayed不同之處在于:立即返回結果,后臺進行處理插入。
**第三個方法: 一次插入多條數據:**
insert中插入多條數據,舉例:
insert into table values('11','11'),('22','22'),('33','33')...;
### innodb_buffer_pool_size
如 果用Innodb,那么這是一個重要變量。相對于MyISAM來說,Innodb對于buffer size更敏感。MySIAM可能對于大數據量使用默認的key_buffer_size也還好,但Innodb在大數據量時用默認值就感覺在爬了。 Innodb的緩沖池會緩存數據和索引,所以不需要給系統的緩存留空間,如果只用Innodb,可以把這個值設為內存的70%-80%。和 key_buffer相同,如果數據量比較小也不怎么增加,那么不要把這個值設太高也可以提高內存的使用率。
### innodb_additional_pool_size
這個的效果不是很明顯,至少是當操作系統能合理分配內存時。但你可能仍需要設成20M或更多一點以看Innodb會分配多少內存做其他用途。
### innodb_log_file_size
對于寫很多尤其是大數據量時非常重要。要注意,大的文件提供更高的性能,但數據庫恢復時會用更多的時間。我一般用64M-512M,具體取決于服務器的空間。
### innodb_log_buffer_size
默認值對于多數中等寫操作和事務短的運用都是可以的。如 果經常做更新或者使用了很多blob數據,應該增大這個值。但太大了也是浪費內存,因為1秒鐘總會 flush(這個詞的中文怎么說呢?)一次,所以不需要設到超過1秒的需求。8M-16M一般應該夠了。小的運用可以設更小一點。
### innodb_flush_log_at_trx_commit (這個很管用)
抱怨Innodb比MyISAM慢 100倍?那么你大概是忘了調整這個值。默認值1的意思是每一次事務提交或事務外的指令都需要把日志寫入(flush)硬盤,這是很費時的。特別是使用電 池供電緩存(Battery backed up cache)時。設成2對于很多運用,特別是從MyISAM表轉過來的是可以的,它的意思是不寫入硬盤而是寫入系統緩存。日志仍然會每秒flush到硬 盤,所以你一般不會丟失超過1-2秒的更新。設成0會更快一點,但安全方面比較差,即使MySQL掛了也可能會丟失事務的數據。而值2只會在整個操作系統 掛了時才可能丟數據。
## 配置文件優化
打開配置文件
```
vim /etc/my.cnf
```
### mysql 8.0版本數據庫的配置
```bash
[client]
port = 23306
socket = /var/run/mysqld8/mysql.sock
host = localhost
user = mysqlcheck
password = mysqlcheck@123
[mysqladmin]
port = 23306
socket = /var/run/mysqld8/mysql.sock
host = localhost
user = mysqlcheck
password = mysqlcheck@123
[mysql]
no-auto-rehash
prompt = "\u@\h:\d \r:\m:\s> "
socket = /var/run/mysqld8/mysql.sock
port = 23306
host = localhost
user = mysqlcheck
password = mysqlcheck@123
[mysqld]
server-id = 51
port = 23306
mysqlx_port = 33060
datadir = /block/mysql_data
socket = /var/run/mysqld8/mysql.sock
mysqlx_socket = /var/run/mysqld8/mysqlx.sock
pid-file = /var/run/mysqld8/mysqld.pid
default-authentication-plugin = mysql_native_password
lower_case_table_names = 1
max_connections = 10000
open_files_limit = 100000
# 服務日志
log-error = /var/log/mysql8/mysqld.log
# 啟用所有日志
# general-log-file = /var/log/mysql8/general.log
# 啟用慢查詢日志
# slow-query-log-file = /var/log/mysql8/query.log
# 默認時間10秒
# long-query-time = 5
# 記錄沒有使用索引查詢的sql命令
# log-queries-not-using-indexes
gtid-mode = on
enforce-gtid-consistency = on
log-bin = binlog
binlog_format = "mixed"
# # master 只允許同步的庫,只不允許同步的庫
# binlog_do_db = dbname1,dbname2
# binlog_ignore_db = dbname1,dbname2
# # slave 級聯復制,只同步的庫,只不同步的庫
# log_slave_updates
# replicate_do_db = dbname1,dbname2
# replicate_ignore_db = dbname1,dbname2
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 100000
innodb_buffer_pool_size = 8192M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 16
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
# innodb_data_file_path=ibdata1:1G:autoextend
innodb_data_file_path=ibdata1:10M:autoextend
bulk_insert_buffer_size = 100M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
```
### mysql 5.7版本數據庫的配置
```bash
[client]
port = 3306
socket = /tmp/mysql.sock
[mysql]
prompt="MySQL [\d]> "
no-auto-rehash
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
skip-name-resolve
#skip-networking
back_log = 300
max_connections = 612
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 256
max_allowed_packet = 500M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 16M
thread_cache_size = 16
query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 2M
ft_min_word_len = 4
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 7
log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
skip-external-locking
default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 128M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 16M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
[mysqldump]
quick
max_allowed_packet = 500M
[myisamchk]
key_buffer_size = 16M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
```
## 關于我們
為程序員提供優質博文、實戰筆記、開發資源、學習資料內容包括:區塊鏈、架構、Golang、Vuejs、Python、Nodejs、C/C++函數庫等等。并不定期奉送各種福利。

> 歡迎訂閱:極客筆記Geeknr
>
> 我的名言:夢想還是要有的,萬一實現了呢?
>
> 原文鏈接:http://blog.geeknr.com/index.php/archives/22/
- 關于本書
- 引言
- 準備工作
- 安裝 Go語言開發環境
- 開始使用Go
- 創建一個Go模塊
- 第一章 手把手系列
- 1.1 教你搭建Nginx教程
- 1.2 教你搭建Jupyter教程
- 1.3 教你搭建Node教程
- 1.4 教你搭建Fabric教程
- 1.5 教你搭建Ethereum教程
- 1.6 教你搭建Bitcoin教程
- 1.7 教你搭建Systemd教程
- 第二章 架構師之路
- 2.1 微服務開發筆記
- 2.2 Docker開發筆記
- 2.3 ElasticSearch開發筆記
- 2.4 Linux開發筆記
- 2.5 Mysql開發筆記
- 2.6 Nginx開發筆記
- 2.7 Redis開發筆記
- 第三章 區塊鏈教程
- 3.1 Bitcoin開發筆記
- 3.2 Ethereum開發筆記
- 3.3 USDT開發筆記
- 第四章 網絡知識庫
- 4.1 比特幣白皮書
- 4.2 以太坊白皮書
- 第五章 技術博客園
- 5.1 Fabric架構詳解
- 5.2 技術開發指南
- 5.3 共識機制詳解
- 第六章 項目管理
- 6.1 項目運行環境
- 6.2 項目經理的角色
- 6.3 第6、7、8章框架
- 第七章 公務員考公
- 7.1 程序員成功上岸經歷
- 7.2 程序員備考的最佳實踐
- 7.3 程序員備考過程中會遇到哪些問題?
- 7.4 公考公平嗎,35歲再去考可以么?
- 7.5 資料、工具推薦和擴展閱讀
- 結論
- 附錄