首页 注册 登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
jdle
V2EX  ›  数据库

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

  •   
  •   jdle · 2016-10-26 02:42:01 +08:00 · 2445 次点击
    这是一个创建于 2949 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

  • 2016-10-25
  • 01:55:32
  • plugin
  • Shutting
    27 条回复  •  2016-10-27 14:37:04 +08:00
    willis
        1
    willis  
       2016-10-26 02:57:02 +08:00
    [ERROR] InnoDB: Cannot allocate memory for the buffer pool 你这是内存不够，所以起不来
    独立服务器跑 mysql,连接方式和你现在连 mysql 是一样的，就是把你现在的数据库地址改成独服的公网
    willis
        2
    willis  
       2016-10-26 02:57:19 +08:00
    你这个日记看的人崩溃
    jdle
        3
    jdle  
    OP
       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
        4
    zeraba  
       2016-10-26 06:48:35 +08:00 via Android
    数据库的 my.cnf 贴出来看看
    shiji
        5
    shiji  
       2016-10-26 07:09:50 +08:00 via Android
    " 就想另外买服务器独立搭建数据库，没找到这类的教程."

    这样的教程不都烂大街了么，怎么会找不到
    bugmenein
        6
    bugmenein  
       2016-10-26 08:36:49 +08:00
    "独立服务器"
    Cannikin
        7
    Cannikin  
       2016-10-26 08:48:38 +08:00
    [mysqld] 内添加 skip-name-resolve 参数允许外网访问
    将帐号设置为非 localhost 即从"localhost"改称"%"
    newghost
        8
    newghost  
       2016-10-26 08:51:05 +08:00
    我怀疑你是被阿里昏盾之类的进程给杀死的。

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

    这一点最变态，我有些新布暑应用，一解析大 JSON 就被 kill ，也是找了很久原因。正准备换服务商。
    KaneLin1217
        9
    KaneLin1217  
       2016-10-26 08:52:21 +08:00 via iPhone
    2G 内存不推荐跑 innodb
    realpg
        10
    realpg  
       2016-10-26 09:09:42 +08:00
    @KaneLin1217
    网站并发 40 ……
    512M 内存都够了
    一看就是不会配 MYSQL ……
    INNODB 也无压力
    idblife
        11
    idblife  
       2016-10-26 09:45:14 +08:00   ❤️ 1
    你这数据库水平，老老实实买 RDS 吧，要不联系我，给你个优惠价
    哈哈
    qinxi
        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 连接配置
    ihuotui
        13
    ihuotui  
       2016-10-26 09:56:11 +08:00
    内存不够吧
    yghack
        14
    yghack  
       2016-10-26 11:01:10 +08:00
    服务器的配置贴出来
    jdle
        15
    jdle  
    OP
       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
        16
    jdle  
    OP
       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
        17
    wuxqing  
       2016-10-26 15:58:40 +08:00
    @newghost
    @aliyunservice
    “阿里云的硬盘有 400~500 次每秒的读写速度限制，超过读出来都是空”，“阿里云对单个进程的 CPU 使用也有限制，比如说瞬间使用率增高，进程就被杀了”。这个有确切的证据？
    jdle
        18
    jdle  
    OP
       2016-10-26 16:00:46 +08:00
    @Cannikin
    @qinxi
    要是买现在服务器同区域的，是不是不用公网 ip ，直接也走内网的？
    比如我现在的服务器是在华东 1 区，我另外也在买台华东 1 区的来做数据库？
    wuxqing
        19
    wuxqing  
       2016-10-26 16:05:05 +08:00
    @jdle 同一个区的可以走内网 IP
    qinxi
        20
    qinxi  
       2016-10-26 16:09:37 +08:00
    @jdle 可以.但是仍然需要

    1, mysql user 授权访问权限 (主要是授权远程主机访问)
    2, iptables 开放 3306
    3, php 修改 mysql 连接配置
    jdle
        21
    jdle  
    OP
       2016-10-26 16:12:14 +08:00
    @qinxi
    访问权限这个就是把 host 项，从” localhost ”改为'%'吗？
    qinxi
        22
    qinxi  
       2016-10-26 16:15:08 +08:00
    @jdle 建议是新增一个用户,只给某一个数据库的权限,host 可以是 '%' 或者 客户端的 ip
    Cannikin
        23
    Cannikin  
       2016-10-26 16:18:56 +08:00
    @jdle 不管是不是内网外网，只要不是本机，都要设置配置。
    newghost
        24
    newghost  
       2016-10-26 18:49:19 +08:00
    @wuxqing

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

    CPU 使用高了，杀进程也是实验得下来的，只有最新的机器才这样，同一套系统在老 debian 还有 windows 2003 server 布暑没问题，一换实例解析某些 JSON ，进程就没了。
    firefox12
        25
    firefox12  
       2016-10-26 18:53:08 +08:00 via iPad
    @idblife 傻逼一个
    idblife
        26
    idblife  
       2016-10-27 09:58:43 +08:00
    @firefox12 你 Y 有病吧
    zeraba
        27
    zeraba  
       2016-10-27 14:37:04 +08:00 via Android
    @jdle 因为不知道你的数据库结构和查询语句 单从你的配置文件和日志看 貌似你没有配置好 innodb 相关的参数 而你的日志都是 imnodb 理论上你应该是在用开源的成熟程序 毕竟如果是自己开发的 你应该知道怎么解决了 所以我建议你调整下你的 my.cnf 可以试试 my.cnf 生成器 再不行 阿里有镜像安装好数据库的 直接用就行
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   994 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 20:21 · PVG 04:21 · LAX 12:21 · JFK 15:21
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.