网站数据库经常挂，小白求助！

2 核 4G 的阿里云服务器，2M 带宽
/etc/my.cnf 的配置

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[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 = 1895
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 1024
max_allowed_packet = 500M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 128M

read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 256M

thread_cache_size = 64

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

ft_min_word_len = 4

#log_bin = mysql-bin #不让 mysql 的操作日志文件生成
binlog_format = mixed
expire_logs_days = 7 #日志保存 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
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 2048M
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 = 64M
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 = 256M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

谢谢大家了！

lhy360121

2018-07-23 15:16:03 +08:00

经常挂的报错是什么？

要看日志看日志看日志

nvhanzhi

2018-07-23 15:19:41 +08:00

@lhy360121 日志里啥都没有啊，我把操作日志文件删除了，把 localhost 改成 127.0.0.1 了也没有作用

lhy360121

2018-07-23 15:22:25 +08:00

怎么可能没有，系统日志，mysql 日志

nvhanzhi

2018-07-23 15:23:09 +08:00

@lhy360121

2018-07-23 14:56:20 11266 [Note] InnoDB: Restoring possible half-written data pages
2018-07-23 14:56:20 11266 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 123228603010
2018-07-23 14:56:20 11266 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 84149106, file name mysql-bin.000004
2018-07-23 14:56:21 11266 [Note] InnoDB: 128 rollback segment(s) are active.
2018-07-23 14:56:21 11266 [Note] InnoDB: Waiting for purge to start
2018-07-23 14:56:21 11266 [Note] InnoDB: 5.6.36 started; log sequence number 123228603010
2018-07-23 14:56:21 11266 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2018-07-23 14:56:21 11266 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2018-07-23 14:56:21 11266 [Note] Server socket created on IP: '0.0.0.0'.
2018-07-23 14:56:21 11266 [Note] Event Scheduler: Loaded 0 events
2018-07-23 14:56:21 11266 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.36-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
2018-07-23 14:57:10 11308 [Warning] You need to use --log-bin to make --binlog-format work.
2018-07-23 14:57:10 11308 [Note] Plugin 'FEDERATED' is disabled.
2018-07-23 14:57:10 11308 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-07-23 14:57:10 11308 [Note] InnoDB: The InnoDB memory heap is disabled
2018-07-23 14:57:10 11308 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-23 14:57:10 11308 [Note] InnoDB: Memory barrier is not used
2018-07-23 14:57:10 11308 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-07-23 14:57:10 11308 [Note] InnoDB: Using Linux native AIO
2018-07-23 14:57:10 11308 [Note] InnoDB: Using CPU crc32 instructions
2018-07-23 14:57:10 11308 [Note] InnoDB: Initializing buffer pool, size = 2.0G
2018-07-23 14:57:10 11308 [Note] InnoDB: Completed initialization of buffer pool
2018-07-23 14:57:10 11308 [Note] InnoDB: Highest supported file format is Barracuda.
2018-07-23 14:57:10 11308 [Note] InnoDB: Log scan progressed past the checkpoint lsn 123229690053
2018-07-23 14:57:10 11308 [Note] InnoDB: Database was not shutdown normally!
2018-07-23 14:57:10 11308 [Note] InnoDB: Starting crash recovery.
2018-07-23 14:57:10 11308 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-07-23 14:57:10 11308 [Note] InnoDB: Restoring possible half-written data pages
2018-07-23 14:57:10 11308 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 123229980451
2018-07-23 14:57:11 11308 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 84149106, file name mysql-bin.000004
2018-07-23 14:57:11 11308 [Note] InnoDB: 128 rollback segment(s) are active.
2018-07-23 14:57:11 11308 [Note] InnoDB: Waiting for purge to start
2018-07-23 14:57:11 11308 [Note] InnoDB: 5.6.36 started; log sequence number 123229980451
2018-07-23 14:57:11 11308 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2018-07-23 14:57:11 11308 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2018-07-23 14:57:11 11308 [Note] Server socket created on IP: '0.0.0.0'.
2018-07-23 14:57:11 11308 [Note] Event Scheduler: Loaded 0 events
2018-07-23 14:57:11 11308 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.36-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)

torment5524

2018-07-23 15:24:00 +08:00

其实你要是自己数据库不会排查的话，建议你直接上阿里云的 rds 数据库。。。价格也差不多。

根据你目前的情况的话，看日志找找慢 sql，阿里云的监控里面看看内存缓存和 cpu 负载等指标

nvhanzhi

2018-07-23 15:24:34 +08:00

2018-07-23 14:48:03 9667 [Note] InnoDB: 128 rollback segment(s) are active.
2018-07-23 14:48:03 9667 [Note] InnoDB: Waiting for purge to start
2018-07-23 14:48:03 9667 [Note] InnoDB: 5.6.36 started; log sequence number 123224907401
2018-07-23 14:48:03 9667 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2018-07-23 14:48:03 9667 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2018-07-23 14:48:03 9667 [Note] Server socket created on IP: '0.0.0.0'.
2018-07-23 14:48:04 9667 [Note] Event Scheduler: Loaded 0 events
2018-07-23 14:48:04 9667 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.36-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
2018-07-23 14:48:18 9733 [Warning] You need to use --log-bin to make --binlog-format work.
2018-07-23 14:48:18 9733 [Note] Plugin 'FEDERATED' is disabled.
2018-07-23 14:48:18 9733 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-07-23 14:48:18 9733 [Note] InnoDB: The InnoDB memory heap is disabled
2018-07-23 14:48:18 9733 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-23 14:48:18 9733 [Note] InnoDB: Memory barrier is not used
2018-07-23 14:48:18 9733 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-07-23 14:48:18 9733 [Note] InnoDB: Using Linux native AIO
2018-07-23 14:48:18 9733 [Note] InnoDB: Using CPU crc32 instructions
2018-07-23 14:48:18 9733 [Note] InnoDB: Initializing buffer pool, size = 2.0G
2018-07-23 14:48:18 9733 [Note] InnoDB: Completed initialization of buffer pool
2018-07-23 14:48:18 9733 [Note] InnoDB: Highest supported file format is Barracuda.
2018-07-23 14:48:18 9733 [Note] InnoDB: Log scan progressed past the checkpoint lsn 123225057677
2018-07-23 14:48:18 9733 [Note] InnoDB: Database was not shutdown normally!
2018-07-23 14:48:18 9733 [Note] InnoDB: Starting crash recovery.
2018-07-23 14:48:18 9733 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-07-23 14:48:18 9733 [Note] InnoDB: Restoring possible half-written data pages
2018-07-23 14:48:18 9733 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 123225090259
2018-07-23 14:48:19 9733 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 84149106, file name mysql-bin.000004
2018-07-23 14:48:19 9733 [Note] InnoDB: 128 rollback segment(s) are active.
2018-07-23 14:48:19 9733 [Note] InnoDB: Waiting for purge to start
2018-07-23 14:48:19 9733 [Note] InnoDB: 5.6.36 started; log sequence number 123225090259
2018-07-23 14:48:19 9733 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2018-07-23 14:48:19 9733 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2018-07-23 14:48:19 9733 [Note] Server socket created on IP: '0.0.0.0'.
2018-07-23 14:48:20 9733 [Note] Event Scheduler: Loaded 0 events
2018-07-23 14:48:20 9733 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.36-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
2018-07-23 14:48:26 9777 [Warning] You need to use --log-bin to make --binlog-format work.
2018-07-23 14:48:26 9777 [Note] Plugin 'FEDERATED' is disabled.
2018-07-23 14:48:26 9777 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-07-23 14:48:26 9777 [Note] InnoDB: The InnoDB memory heap is disabled
2018-07-23 14:48:26 9777 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-23 14:48:26 9777 [Note] InnoDB: Memory barrier is not used
2018-07-23 14:48:26 9777 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-07-23 14:48:26 9777 [Note] InnoDB: Using Linux native AIO
2018-07-23 14:48:26 9777 [Note] InnoDB: Using CPU crc32 instructions
2018-07-23 14:48:26 9777 [Note] InnoDB: Initializing buffer pool, size = 2.0G
InnoDB: mmap(274726912 bytes) failed; errno 12
2018-07-23 14:48:26 9777 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2018-07-23 14:48:26 9777 [ERROR] Plugin 'InnoDB' init function returned error.
2018-07-23 14:48:26 9777 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-07-23 14:48:26 9777 [ERROR] Unknown/unsupported storage engine: InnoDB
2018-07-23 14:48:26 9777 [ERROR] Aborting