Want more timely Centmin Mod News Updates?
Become a Member

MariaDB When i use mysqldump the site is not responding

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by pamamolf, Nov 22, 2014.

  1. pamamolf

    pamamolf Well-Known Member

    2,498
    229
    63
    May 31, 2014
    Ratings:
    +390
    Local Time:
    11:32 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Hi

    When i use mysqldump the site is not responding for 3 minutes until is finish :(

    What i should adjust to avoid this?

    Thanks
     
  2. RoldanLT

    RoldanLT Well-Known Member

    3,829
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    4:32 AM
    1.11
    10.2
    Your site is pretty big I guess.
     
    • Like Like x 1
  3. pamamolf

    pamamolf Well-Known Member

    2,498
    229
    63
    May 31, 2014
    Ratings:
    +390
    Local Time:
    11:32 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Is there any other way to do a safe backup of my database than mysqldump ?
     
  4. Matt

    Matt Moderator Staff Member

    680
    314
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +433
    Local Time:
    9:32 PM
    1.7.1
    MariaDB 10
    What is the full command you are using with MySQLdump?
     
  5. pamamolf

    pamamolf Well-Known Member

    2,498
    229
    63
    May 31, 2014
    Ratings:
    +390
    Local Time:
    11:32 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    mysqldump without any parameter only username database and export to .sql without compression :)
     
  6. Matt

    Matt Moderator Staff Member

    680
    314
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +433
    Local Time:
    9:32 PM
    1.7.1
    MariaDB 10
    add this to it
    Code:
    --opt --single-transaction
     
  7. pamamolf

    pamamolf Well-Known Member

    2,498
    229
    63
    May 31, 2014
    Ratings:
    +390
    Local Time:
    11:32 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    ok thanks :)

    I can imagine that this will delay a bit the backup :)

    Now it takes about 3 minutes to finish.... and i hope to not need more than 10 with this :)
     
  8. pamamolf

    pamamolf Well-Known Member

    2,498
    229
    63
    May 31, 2014
    Ratings:
    +390
    Local Time:
    11:32 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Double post ops...
     
  9. Matt

    Matt Moderator Staff Member

    680
    314
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +433
    Local Time:
    9:32 PM
    1.7.1
    MariaDB 10
    • Like Like x 1
  10. pamamolf

    pamamolf Well-Known Member

    2,498
    229
    63
    May 31, 2014
    Ratings:
    +390
    Local Time:
    11:32 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    What's the difference with this parameter?

    --lock-tables=false

    And are the backups now 100% safe using your option?
     
  11. Matt

    Matt Moderator Staff Member

    680
    314
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +433
    Local Time:
    9:32 PM
    1.7.1
    MariaDB 10
    Yes, they are safe because you get a consistent export. That is why the MyISAM tables are locked during export. InnoDB can be exported with the single transaction because of the row level locking.

    If you add the lock-tables=false your MyISAM tables could be incomplete or corrupted.
     
  12. Matt

    Matt Moderator Staff Member

    680
    314
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +433
    Local Time:
    9:32 PM
    1.7.1
    MariaDB 10
    and just to add, I always use the single-transaction option, and have exported and imported hundreds of times with zero issues.
     
  13. pamamolf

    pamamolf Well-Known Member

    2,498
    229
    63
    May 31, 2014
    Ratings:
    +390
    Local Time:
    11:32 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Great thanks !!!!
     
  14. pamamolf

    pamamolf Well-Known Member

    2,498
    229
    63
    May 31, 2014
    Ratings:
    +390
    Local Time:
    11:32 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    For Myisam which way do you recommend me to use for the backup?