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

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

谢谢大家了！

opengps

2018-07-23 16:20:55 +08:00

迁移可以找迁移工具或者直接导出脚本（注意是：架构+数据）
按理说，你应该没必要单独用数据库服务，因为看你的描述，还不是业务量上来之后的样子，所以完全可以先自己继续用 ecs 折腾着玩。
出现这个问题肯定有某个地方是可以解决的，最笨的办法可以选择备份数据后重装，liunx 我不懂所以帮不了你，win 的话可以先考虑虚拟内存

likuku

2018-07-23 17:27:50 +08:00

@nvhanzhi innodb_buffer_pool_size 你可以当作它是 innodb 自己的内存(把 innodb 想像成一个独立的系统，没错它是 mysql 内的一个数据引擎 /数据插件)，你服务器本身内存太小了，你给 innodb 分了太多内存，结果繁忙时 mysql 整体没有足够内存了。还不行就改 256M 吧，你 wordpress 访问量不大，innodb_buffer_pool_size 那就不需要开那么高。

likuku

2018-07-23 21:35:26 +08:00

@falcon05
@nvhanzhi

改换 myisam 是个好办法(phpmyadmin 可以在线直接切换的)，数据也放系统盘...你这也太省了。

aliyun 不清楚，aws 的 ec2 主机自带盘是不能拿来作数据保存的，因为系统盘都算是临时存储，物理机层面有调度时，重启 vps 后，系统盘都重置的。再怎么样，也得另外买个独立存储盘来存储数据。

都几万商品了，都用它做生意了，数据也算蛮重要了，有些钱是不可以省的。

nvhanzhi

2018-07-23 22:56:07 +08:00

又挂了。。。
Error establishing a database connection
This either means that the username and password information in your wp-config.php file is incorrect or we can ’ t contact the database server at 127.0.0.1. This could mean your host ’ s database server is down.

Are you sure you have the correct username and password?
Are you sure that you have typed the correct hostname?
Are you sure that the database server is running?
If you ’ re unsure what these terms mean you should probably contact your host. If you still need help you can always visit the WordPress Support Forums.