Learn about Centmin Mod LEMP Stack today
Register Now

MariaDB MYSQL server down :-(

Discussion in 'MariaDB & General MySQL news & discussions' started by RoldanLT, Jun 15, 2015.

  1. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    10:18 AM
    1.11
    10.2
  2. eva2000

    eva2000 Administrator Staff Member

    30,194
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    12:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    First standard response so everyone else can read - Centmin Mod is provide as is, so short of scripted related bugs or issues, so troubleshooting is left to the Centmin Mod user to deal with. So I do not provide any free support for such.

    However, Centmin Mod users are free to help each other out and ask questions or give answers on this community forum. My hopes are that this community forum evolves so that more veteran long time Centmin Mod users help new Centmin Mod users out :)

    and quick check of your log - you ran out of disk space during innodb table recovery - make sure you have enough disk free space = at least 5x times amount of free disk space as your largest innodb table size
    Code:
    150615 20:18:52 [ERROR] mysqld: Error writing file '/var/lib/mysql/server64.phcorner.net.pid' (Errcode: 28 "No space left on device")
    150615 20:18:52 [ERROR] Can't start server: can't create PID file: No space left on device
    
     
  3. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    10:18 AM
    1.11
    10.2
    Aw, Sorry I have only 49.09 GB.
     
  4. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    10:18 AM
    1.11
    10.2
    How can I delete some temp file? to free up space? :(
     
  5. eva2000

    eva2000 Administrator Staff Member

    30,194
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    12:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    depends on what you have on server
    Code:
    du -h --max-depth=1 /
    du -h --max-depth=1 /home
    du -h --max-depth=1 /usr
    du -h --max-depth=1 /var
    You can empty out /svr-setup and empty ccache cache (via ccache -c) as most files get re-downloaded when needed from centmin.sh runs and ccache gets rebuilt when used. Can also empty yum caches too
    Code:
    df -h
    rm -rf /svr-setup/*
    ccache -c
    yum clean all
    rm -rf /var/cache/yum/*
    df -h
    Code:
    ccache -h
    Usage:
        ccache [options]
        ccache compiler [compiler options]
        compiler [compiler options]          (via symbolic link)
    
    Options:
        -c, --cleanup         delete old files and recalculate size counters
                              (normally not needed as this is done automatically)
        -C, --clear           clear the cache completely (except configuration)
        -F, --max-files=N     set maximum number of files in cache to N (use 0 for
                              no limit)
        -M, --max-size=SIZE   set maximum size of cache to SIZE (use 0 for no
                              limit); available suffixes: k, M, G, T (decimal) and
                              Ki, Mi, Gi, Ti (binary); default suffix: G
        -o, --set-config=K=V  set configuration key K to value V
        -p, --print-config    print current configuration options
        -s, --show-stats      show statistics summary
        -z, --zero-stats      zero statistics counters
    
        -h, --help            print this help text
        -V, --version         print version and copyright information
    
    See also <http://ccache.samba.org>.
    innodb tables use much more memory and disk space than myisam so you need to account for that
     
  6. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    10:18 AM
    1.11
    10.2
    So it's 36G /var
     
  7. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    10:18 AM
    1.11
    10.2
    # du -h --max-depth=1 /usr
    4.0K /usr/games
    7.2G /usr/local
    98M /usr/src
    33M /usr/libexec
    4.0K /usr/etc
    231M /usr/bin
    43M /usr/sbin
    514M /usr/share
    53M /usr/include
    551M /usr/lib64
    277M /usr/lib
    9.0G /usr
     
  8. eva2000

    eva2000 Administrator Staff Member

    30,194
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    12:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    what about

    Code:
    df -h
     
  9. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    10:18 AM
    1.11
    10.2
    Code:
    # df -h
    Filesystem            Size  Used Avail Use% Mounted on
    /dev/mapper/vg_server64-lv_root
                           50G   45G  1.8G  97% /
    tmpfs                  32G     0   32G   0% /dev/shm
    /dev/sda1             477M   87M  365M  20% /boot
    /dev/mapper/vg_server64-lv_home
                          745G   81G  626G  12% /home
    tmpfs                  32G  612K   32G   1% /tmp
    
     
  10. eva2000

    eva2000 Administrator Staff Member

    30,194
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    12:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    non-optimal disk partitioning there ! / which /var resides on only has 50GB partition with 1.8GB available

    /home has 626GB so you can move entire mysql data directory to /home/mysql instead

    commands to run to move to /home/mysql - obviously mysqladmin command might not work as your mysql server is already down heh but put it in as reference for basic datadir moves
    Code:
    service nginx stop; echo "prevent front side errors showing"
    mysqladmin flush-tables; sleep 60; echo "wait for 60 seconds"
    service mysql stop
    mkdir -p /home/mysql
    cp -a /etc/my.cnf /etc/my.cnf-varlibmysql.bak
    sed -i 's|datadir=\/var\/lib\/mysql|datadir=\/home\/mysql|' /etc/my.cnf
    sed -i 's|slow_query_log_file=\/var\/lib\/mysql|slow_query_log_file=\/home\/mysql|' /etc/my.cnf
    
    make sure datadir variable etc in /etc/my.cnf is changed first
    Code:
    grep '\/var\/lib\/mysql' /etc/my.cnf
    grep '\/home\/mysql' /etc/my.cnf
    
    i.e. defaults
    Code:
    grep '\/var\/lib\/mysql' /etc/my.cnf
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    slow_query_log_file=/var/lib/mysql/slowq.log
    socket=/var/lib/mysql/mysql.sock
    if changed proceed
    Code:
    \cp -Rpf /var/lib/mysql/* /home/mysql/
    chown mysql:mysql /home/mysql
    service mysql start; echo "do not reboot server or restart mysql as innodb might be doing recovery - check mysql error log for details"
    service nginx start
    
    rest would be up to you as I said I don't provide free troubleshooting and technical support for stuff like this :)
     
    Last edited: Jun 15, 2015
    • Like Like x 1
  11. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    10:18 AM
    1.11
    10.2
    Thanks a lot Eva, now I got this:
    Code:
    # service mysql start; echo "do not reboot server or restart mysql as innodb might be doing recovery - check mysql error log for details"
    Starting MySQL...... ERROR!
    do not reboot server or restart mysql as innodb might be doing recovery - check mysql error log for details
    
     
  12. eva2000

    eva2000 Administrator Staff Member

    30,194
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    12:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    check mysql error log - if it's related to mysql.sock might need to edit /etc/my.cnf and change it back for the 2 instances of socket=/var/lib/mysql/mysql.sock
     
  13. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    10:18 AM
    1.11
    10.2
    here:
    Code:
    150615 21:07:14 mysqld_safe mysqld from pid file /var/lib/mysql/server64.phcorner.net.pid ended
    150615 21:09:11 mysqld_safe Starting mysqld daemon with databases from /home/mysql
    150615 21:09:11 [Note] /usr/sbin/mysqld (mysqld 10.0.19-MariaDB) starting as process 26905 ...
    150615 21:09:11 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    150615 21:09:11 [Note] InnoDB: The InnoDB memory heap is disabled
    150615 21:09:11 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    150615 21:09:11 [Note] InnoDB: Memory barrier is not used
    150615 21:09:11 [Note] InnoDB: Compressed tables use zlib 1.2.3
    150615 21:09:11 [Note] InnoDB: Using Linux native AIO
    150615 21:09:11 [Note] InnoDB: Using CPU crc32 instructions
    150615 21:09:11 [Note] InnoDB: Initializing buffer pool, size = 4.0G
    150615 21:09:13 [Note] InnoDB: Completed initialization of buffer pool
    150615 21:09:13 [Note] InnoDB: Highest supported file format is Barracuda.
    150615 21:09:14 [Note] InnoDB: 128 rollback segment(s) are active.
    150615 21:09:14 [Note] InnoDB: Waiting for purge to start
    150615 21:09:14 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.23-72.1 started; log sequence number 78641392207
    2015-06-15 21:09:14 7f5b955f8700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool
    150615 21:09:14 [Note] Plugin 'FEDERATED' is disabled.
    150615 21:09:14 [Note] Plugin 'ARCHIVE' is disabled.
    150615 21:09:14 [Note] Plugin 'FEEDBACK' is disabled.
    150615 21:09:14 [Note] Server socket created on IP: '::'.
    150615 21:09:14 [ERROR] Can't start server : Bind on unix socket: Permission denied
    150615 21:09:14 [ERROR] Do you already have another mysqld server running on socket: /home/mysql/mysql.sock ?
    150615 21:09:14 [ERROR] Aborting
    
    150615 21:09:14 [Note] InnoDB: FTS optimize thread exiting.
    150615 21:09:14 [Note] InnoDB: Starting shutdown...
    2015-06-15 21:09:14 7f5b955f8700 InnoDB: Buffer pool(s) load completed at 150615 21:09:14
    2015-06-15 21:09:14 7f5b955f8700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool
    2015-06-15 21:09:14 7f5b955f8700 InnoDB: Cannot open './/ib_buffer_pool.incomplete' for writing: Permission denied
    150615 21:09:16 [Note] InnoDB: Shutdown completed; log sequence number 78641395893
    150615 21:09:16 [Note] /usr/sbin/mysqld: Shutdown complete
    
    150615 21:09:16 mysqld_safe mysqld from pid file /home/mysql/server64.phcorner.net.pid ended
    
     
  14. eva2000

    eva2000 Administrator Staff Member

    30,194
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    12:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    ah forgot
    Code:
    chown mysql:mysql /home/mysql
    also comment out in /etc/my.cnf these 2 values for now
    Code:
    innodb_buffer_pool_dump_at_shutdown=1
    innodb_buffer_pool_load_at_startup=1
     
    • Like Like x 1
  15. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    10:18 AM
    1.11
    10.2
    Is this normal?
    Still not yet finished up until now.
     
  16. eva2000

    eva2000 Administrator Staff Member

    30,194
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    12:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    again always check mysql error log ;)
     
  17. eva2000

    eva2000 Administrator Staff Member

    30,194
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    12:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    might apply ;)
     
    • Like Like x 1
  18. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    10:18 AM
    1.11
    10.2
    That fix it instantly :D
     
  19. eva2000

    eva2000 Administrator Staff Member

    30,194
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    12:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    yeah i corrected my sed replacements above to
    Code:
    sed -i 's|datadir=\/var\/lib\/mysql|datadir=\/home\/mysql|' /etc/my.cnf
    sed -i 's|slow_query_log_file=\/var\/lib\/mysql|slow_query_log_file=\/home\/mysql|' /etc/my.cnf
     
  20. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    10:18 AM
    1.11
    10.2
    This is correct?
    slow_query_log_file=/home/mysql/slowq.log

    Mysql is restarting fine now, but I can't login to phpmyadmin.
    With this errors:
    session_start(): Write of lock failed
    session_start(): Unable to clear session lock record