用独立服务器搭建的数据库，怎么链接？

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 2980 天前的主题，其中的信息可能已经有所发展或是发生改变。

最近网站并发才 40 ，数据库就自动关闭了。数据库配置文件也改了很多，还是不能解决，一问阿里云的员工，就是让我买 RDS ，但是看了下价格有点贵了。就想另外买服务器独立搭建数据库，没找到这类的教程，来问问有没有搭建过的伙伴，能不能分享下，我怎么去连接数据库呢？

这是现在网站数据库的错误日志 161025 01:55:22 mysqld_safe Number of processes running now: 0 161025 01:55:22 mysqld_safe 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

willis

2016-10-26 02:57:02 +08:00

[ERROR] InnoDB: Cannot allocate memory for the buffer pool 你这是内存不够，所以起不来
独立服务器跑 mysql,连接方式和你现在连 mysql 是一样的，就是把你现在的数据库地址改成独服的公网

willis

2016-10-26 02:57:19 +08:00

你这个日记看的人崩溃

jdle

2016-10-26 03:22:07 +08:00

@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";这样吗？

zeraba

2016-10-26 06:48:35 +08:00 via Android

数据库的 my.cnf 贴出来看看

shiji

2016-10-26 07:09:50 +08:00 via Android

" 就想另外买服务器独立搭建数据库，没找到这类的教程."

这样的教程不都烂大街了么，怎么会找不到

bugmenein

2016-10-26 08:36:49 +08:00

"独立服务器"

Cannikin

2016-10-26 08:48:38 +08:00

[mysqld] 内添加 skip-name-resolve 参数允许外网访问
将帐号设置为非 localhost 即从"localhost"改称"%"

newghost

2016-10-26 08:51:05 +08:00

我怀疑你是被阿里昏盾之类的进程给杀死的。

首先阿里云的硬盘有 400~500 次每秒的读写速度限制，超过读出来都是空。
再次阿里云对单个进程的 CPU 使用也有限制，比如说瞬间使用率增高，进程就被杀了。 AWS 是限制 CPU 的使用率，阿里的人是直接杀你的进程。

这一点最变态，我有些新布暑应用，一解析大 JSON 就被 kill ，也是找了很久原因。正准备换服务商。

KaneLin1217

2016-10-26 08:52:21 +08:00 via iPhone

2G 内存不推荐跑 innodb

realpg

2016-10-26 09:09:42 +08:00

@KaneLin1217
网站并发 40 ……
512M 内存都够了
一看就是不会配 MYSQL ……
INNODB 也无压力

idblife

2016-10-26 09:45:14 +08:00

你这数据库水平，老老实实买 RDS 吧，要不联系我，给你个优惠价
哈哈

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 连接配置

ihuotui

2016-10-26 09:56:11 +08:00

内存不够吧

yghack

2016-10-26 11:01:10 +08:00

服务器的配置贴出来

jdle

2016-10-26 15:39:08 +08:00

@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

我对数据库不懂

jdle

2016-10-26 15:40:44 +08:00

@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 。

wuxqing

2016-10-26 15:58:40 +08:00

@newghost
@aliyunservice
“阿里云的硬盘有 400~500 次每秒的读写速度限制，超过读出来都是空”，“阿里云对单个进程的 CPU 使用也有限制，比如说瞬间使用率增高，进程就被杀了”。这个有确切的证据？

jdle

2016-10-26 16:00:46 +08:00

@Cannikin
@qinxi
要是买现在服务器同区域的，是不是不用公网 ip ，直接也走内网的？
比如我现在的服务器是在华东 1 区，我另外也在买台华东 1 区的来做数据库？

wuxqing

2016-10-26 16:05:05 +08:00

@jdle 同一个区的可以走内网 IP

qinxi

2016-10-26 16:09:37 +08:00

@jdle 可以.但是仍然需要

1, mysql user 授权访问权限 (主要是授权远程主机访问)
2, iptables 开放 3306
3, php 修改 mysql 连接配置

jdle

2016-10-26 16:12:14 +08:00

@qinxi
访问权限这个就是把 host 项，从” localhost ”改为'%'吗？

qinxi

2016-10-26 16:15:08 +08:00

@jdle 建议是新增一个用户,只给某一个数据库的权限,host 可以是 '%' 或者客户端的 ip

Cannikin

2016-10-26 16:18:56 +08:00

@jdle 不管是不是内网外网，只要不是本机，都要设置配置。

newghost

2016-10-26 18:49:19 +08:00

@wuxqing

400~500 次是我后来到官网上找到的，试验下来也确实如何

CPU 使用高了，杀进程也是实验得下来的，只有最新的机器才这样，同一套系统在老 debian 还有 windows 2003 server 布暑没问题，一换实例解析某些 JSON ，进程就没了。

firefox12

2016-10-26 18:53:08 +08:00 via iPad

@idblife 傻逼一个

idblife

2016-10-27 09:58:43 +08:00

@firefox12 你 Y 有病吧

zeraba

2016-10-27 14:37:04 +08:00 via Android

@jdle 因为不知道你的数据库结构和查询语句单从你的配置文件和日志看貌似你没有配置好 innodb 相关的参数而你的日志都是 imnodb 理论上你应该是在用开源的成熟程序毕竟如果是自己开发的你应该知道怎么解决了所以我建议你调整下你的 my.cnf 可以试试 my.cnf 生成器再不行阿里有镜像安装好数据库的直接用就行