这是一个创建于 3349 天前的主题，其中的信息可能已经有所发展或是发生改变。
最近网站并发才 40 ，数据库就自动关闭了。 数据库配置文件也改了很多，还是不能解决，一问阿里云的员工，就是让我买 RDS ，但是看了下价格有点贵了。 就想另外买服务器独立搭建数据库，没找到这类的教程，来问问有没有搭建过的伙伴，能不能分享下，我怎么去连接数据库呢？
这是现在网站数据库的错误日志 161025 01:55:22 mysqld_safe Number of processes running now: 0 161025 01:55:22 mysqld_sfe mysqld restarted 2016-10-25 01:55:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2016-10-25 01:55:30 17682 [Note] Plugin 'FEDERATED' is disabled. 2016-10-25 01:55:31 17682 [Note] InnoDB: Using atomics to ref count buffer pool pages 2016-10-25 01:55:31 17682 [Note] InnoDB: The InnoDB memory heap is disabled 2016-10-25 01:55:31 17682 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-10-25 01:55:31 17682 [Note] InnoDB: Memory barrier is not used 2016-10-25 01:55:31 17682 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-10-25 01:55:31 17682 [Note] InnoDB: Using Linux native AIO 2016-10-25 01:55:31 17682 [Note] InnoDB: Using CPU crc32 instructions 2016-10-25 01:55:32 17682 [Note] InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 2016-10-25 01:55:32 17682 [ERROR] InnoDB: Cannot allocate memory for the buffer pool 2016-10-25 01:55:32 17682 [ERROR] Plugin 'InnoDB' init function returned error. 2016-10-25 01:55:32 17682 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2016-10-25 01:55:32 17682 [ERROR] Unknown/unsupported storage engine: InnoDB 2016-10-25 01:55:32 17682 [ERROR] Aborting 2016-10-25 01:55:32 17682 [Note] Binlog end 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'partition' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'BLACKHOLE' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_SYS_FIELDS' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_SYS_INDEXES' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_SYS_TABLES' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_FT_CONFIG' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_FT_DELETED' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_METRICS' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_CMPMEM' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_CMP_RESET' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_CMP' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_LOCK_WAITS' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_LOCKS' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'INNODB_TRX' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'ARCHIVE' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'MyISAM' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'CSV' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'MRG_MYISAM' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'MEMORY' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'sha256_password' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'mysql_old_password' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'mysql_native_password' 2016-10-25 01:55:32 17682 [Note] Shutting down plugin 'binlog' 2016-10-25 01:55:32 17682 [Note] /alidata/server/mysql/bin/mysqld: Shutdown complete 161025 01:55:32 mysqld_safe mysqld from pid file /alidata/server/mysql/data/AY140716132949Z.pid ended
27 条回复 2016-10-27 14:37:04 +08:00
 | 1 willis 2016-10-26 02:57:02 +08:00 [ERROR] InnoDB: Cannot allocate memory for the buffer pool 你这是内存不够，所以起不来 独立服务器跑 mysql,连接方式和你现在连 mysql 是一样的，就是把你现在的数据库地址改成独服的公网 |
| 2 willis 2016-10-26 02:57:19 +08:00 你这个日记看的人崩溃 |
 | 3 jdle OP @willis total used free shared buffers cached Mem: 1875 1804 71 0 78 57 -/+ buffers/cache: 1667 207 Swap: 0 0 0 刚才用命令看了下使用情况。 日志粘贴出来，就乱了，也没看见编辑器那里可以修改。 就是现在网站数据库配置文件里面的： $db_host = "localhost:3306"; 改成 $db_host = "独立服务器 ip";这样吗？ |
 | 4 zeraba 2016-10-26 06:48:35 +08:00 via Android 数据库的 my.cnf 贴出来看看 |
 | 5 shiji 2016-10-26 07:09:50 +08:00 via Android " 就想另外买服务器独立搭建数据库，没找到这类的教程." 这样的教程不都烂大街了么，怎么会找不到 |
 | 6 bugmenein 2016-10-26 08:36:49 +08:00 "独立服务器" |
 | 7 Cannikin 2016-10-26 08:48:38 +08:00 [mysqld] 内添加 skip-name-resolve 参数允许外网访问 将帐号设置为非 localhost 即从"localhost"改称"%" |
 | 8 newghost 2016-10-26 08:51:05 +08:00 我怀疑你是被阿里昏盾之类的进程给杀死的。 首先阿里云的硬盘有 400~500 次每秒的读写速度限制，超过读出来都是空。 再次阿里云对单个进程的 CPU 使用也有限制，比如说瞬间使用率增高，进程就被杀了。 AWS 是限制 CPU 的使用率，阿里的人是直接杀你的进程。 这一点最变态，我有些新布暑应用，一解析大 JSON 就被 kill ，也是找了很久原因。正准备换服务商。 |
 | 9 KaneLin1217 2016-10-26 08:52:21 +08:00 via iPhone 2G 内存不推荐跑 innodb |
 | 10 realpg PRO |
 | 11 idblife 2016-10-26 09:45:14 +08:00  1 你这数据库水平，老老实实买 RDS 吧，要不联系我，给你个优惠价 哈哈 |
 | 12 qinxi 2016-10-26 09:53:03 +08:00 1, mysql 监听地址要不就是公网 IP,要不就 0.0.0.0 2, mysql user 授权访问权限 3, iptables 开放 3306 4, php 修改 mysql 连接配置 |
 | 13 ihuotui 2016-10-26 09:56:11 +08:00 内存不够吧 |
 | 14 yghack 2016-10-26 11:01:10 +08:00 服务器的配置贴出来 |
| 15 jdle OP @zeraba [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 1400M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 2M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 这是 etc 目录下的 [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking log-error=/alidata/log/mysql/error.log key_buffer_size = 32M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 8M log-bin=mysql-bin binlog_format=mixed server-id = 1 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 10M write_buffer = 10M [mysqlhotcopy] interactive-timeout expire_logs_days = 5 max_binlog_size = 1000M 我对数据库不懂 |
| 16 jdle OP yghack @realpg @newghost 这是 my.cnf 的配置 [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 1400M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 2M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 这是 etc 目录下的 [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking log-error=/alidata/log/mysql/error.log key_buffer_size = 32M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 8M log-bin=mysql-bin binlog_format=mixed server-id = 1 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 10M write_buffer = 10M [mysqlhotcopy] interactive-timeout expire_logs_days = 5 max_binlog_size = 1000M 是的，我对数据库不懂，服务器的物理内存是 2G 。 |
 | 17 wuxqing 2016-10-26 15:58:40 +08:00 @newghost @aliyunservice “阿里云的硬盘有 400~500 次每秒的读写速度限制，超过读出来都是空”，“阿里云对单个进程的 CPU 使用也有限制，比如说瞬间使用率增高，进程就被杀了”。这个有确切的证据？ |
| 18 jdle OP |
| 20 qinxi 2016-10-26 16:09:37 +08:00 |
