據我分析,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/