Get the most out of your Centmin Mod LEMP stack
Become a Member

MariaDB Any advice on how to properly save MYSQL/MariaDB data from crash down?

Discussion in 'Centmin Mod Insights' started by RoldanLT, Dec 14, 2015.

  1. RoldanLT

    RoldanLT Well-Known Member

    3,899
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:12 PM
    1.11
    10.2
    One of my server has MariaDB 10.0.22 just crash, and unable to start :(.
    What adjustment need to do on my.cnf to help this start properly?

    Posting error log later.
     
  2. RoldanLT

    RoldanLT Well-Known Member

    3,899
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:12 PM
    1.11
    10.2
    Fixed now.
     
  3. eva2000

    eva2000 Administrator Staff Member

    30,151
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,133
    Local Time:
    1:12 AM
    Nginx 1.13.x
    MariaDB 5.5
    Guess it was something simple ?

    Depends simplest method is to restore databases from a frequently backed up clean copy. However, depending on whether crash = corruption and whether recovery is needed, it can be very extensive work involved so best to hire someone to do it if you don't know
     
    • Like Like x 1
  4. RoldanLT

    RoldanLT Well-Known Member

    3,899
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:12 PM
    1.11
    10.2
    Now my question is, how to properly move MYSQL raw data on home folder?
    Seems /dev/sda2/ is almost full now :/.
     
  5. RoldanLT

    RoldanLT Well-Known Member

    3,899
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:12 PM
    1.11
    10.2
    I have recovered it now successfully Eva :|.
    But /dev/sda2/ is almost full, so next thing to do is move MYSQL data on /home/ folder.

    I remember changing some config on my.cnf and manually copying the data.
     
  6. eva2000

    eva2000 Administrator Staff Member

    30,151
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,133
    Local Time:
    1:12 AM
    Nginx 1.13.x
    MariaDB 5.5
  7. RoldanLT

    RoldanLT Well-Known Member

    3,899
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:12 PM
    1.11
    10.2
    This is hosting a VB4 Forum by the way. With 15GB of Database.
    I partition /var/ with 200GB but still almost full right now :/.
     
  8. eva2000

    eva2000 Administrator Staff Member

    30,151
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,133
    Local Time:
    1:12 AM
    Nginx 1.13.x
    MariaDB 5.5
  9. RoldanLT

    RoldanLT Well-Known Member

    3,899
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:12 PM
    1.11
    10.2
    Learned from you before :).
    Anything I miss on this Eva?

    Code:
    npstop
    mysqladmin flush-tables
    service mysql stop
    mkdir -p /home/mysql
    cp -a /etc/my.cnf /etc/my.cnf-december2015.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
    \cp -Rpf /var/lib/mysql/* /home/mysql/
    chown mysql:mysql /home/mysql
    service mysql start
    npstart
    mysqlrestart
    
     
  10. RoldanLT

    RoldanLT Well-Known Member

    3,899
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:12 PM
    1.11
    10.2
    Is it safe to delete this folder now?
    /var/lib/mysql (40GB now)

    Thanks!
     
  11. eva2000

    eva2000 Administrator Staff Member

    30,151
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,133
    Local Time:
    1:12 AM
    Nginx 1.13.x
    MariaDB 5.5
    yeah that would work

    i generally backup and download a copy before deleting just to be 100% safe
     
    • Like Like x 1
  12. RoldanLT

    RoldanLT Well-Known Member

    3,899
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:12 PM
    1.11
    10.2
    I thought it's the best option? :D
    You message seems saying it's not :|.
     
    • Funny Funny x 1
  13. eva2000

    eva2000 Administrator Staff Member

    30,151
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,133
    Local Time:
    1:12 AM
    Nginx 1.13.x
    MariaDB 5.5
    literally it is what i said = that it would work :)
     
    • Informative Informative x 1
  14. RoldanLT

    RoldanLT Well-Known Member

    3,899
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:12 PM
    1.11
    10.2