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 rdan, Dec 14, 2015.

  1. rdan

    rdan Premium Member Premium Member

    5,112
    1,267
    113
    May 25, 2014
    Ratings:
    +1,936
    Local Time:
    8:02 PM
    Mainline
    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. rdan

    rdan Premium Member Premium Member

    5,112
    1,267
    113
    May 25, 2014
    Ratings:
    +1,936
    Local Time:
    8:02 PM
    Mainline
    10.2
    Fixed now.
     
  3. eva2000

    eva2000 Administrator Staff Member

    47,551
    10,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,774
    Local Time:
    10:02 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    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
     
  4. rdan

    rdan Premium Member Premium Member

    5,112
    1,267
    113
    May 25, 2014
    Ratings:
    +1,936
    Local Time:
    8:02 PM
    Mainline
    10.2
    Now my question is, how to properly move MYSQL raw data on home folder?
    Seems /dev/sda2/ is almost full now :/.
     
  5. rdan

    rdan Premium Member Premium Member

    5,112
    1,267
    113
    May 25, 2014
    Ratings:
    +1,936
    Local Time:
    8:02 PM
    Mainline
    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

    47,551
    10,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,774
    Local Time:
    10:02 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
  7. rdan

    rdan Premium Member Premium Member

    5,112
    1,267
    113
    May 25, 2014
    Ratings:
    +1,936
    Local Time:
    8:02 PM
    Mainline
    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

    47,551
    10,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,774
    Local Time:
    10:02 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
  9. rdan

    rdan Premium Member Premium Member

    5,112
    1,267
    113
    May 25, 2014
    Ratings:
    +1,936
    Local Time:
    8:02 PM
    Mainline
    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. rdan

    rdan Premium Member Premium Member

    5,112
    1,267
    113
    May 25, 2014
    Ratings:
    +1,936
    Local Time:
    8:02 PM
    Mainline
    10.2
    Is it safe to delete this folder now?
    /var/lib/mysql (40GB now)

    Thanks!
     
  11. eva2000

    eva2000 Administrator Staff Member

    47,551
    10,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,774
    Local Time:
    10:02 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    yeah that would work

    i generally backup and download a copy before deleting just to be 100% safe
     
  12. rdan

    rdan Premium Member Premium Member

    5,112
    1,267
    113
    May 25, 2014
    Ratings:
    +1,936
    Local Time:
    8:02 PM
    Mainline
    10.2
    I thought it's the best option? :D
    You message seems saying it's not :|.
     
  13. eva2000

    eva2000 Administrator Staff Member

    47,551
    10,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,774
    Local Time:
    10:02 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    literally it is what i said = that it would work :)
     
  14. rdan

    rdan Premium Member Premium Member

    5,112
    1,267
    113
    May 25, 2014
    Ratings:
    +1,936
    Local Time:
    8:02 PM
    Mainline
    10.2