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

[jdle]

最近网站并发才 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

[willis]

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

[willis]

你这个日记看的人崩溃

[jdle]

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

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

[shiji]

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

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

[bugmenein]

"独立服务器"

[Cannikin]

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

[newghost]

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

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

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

[KaneLin1217]

2G 内存不推荐跑 innodb

[realpg]

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

[idblife]

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

[qinxi]

1, mysql 监听地址要不就是公网 IP,要不就 0.0.0.0
2, mysql user 授权访问权限
3, iptables 开放 3306
4, php 修改 mysql 连接配置

[ihuotui]

内存不够吧

[yghack]

服务器的配置贴出来

[jdle]

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

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

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

[jdle]

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

[wuxqing]

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

[qinxi]

@jdle 可以.但是仍然需要

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