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