Join the community today
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 Premium Member Premium Member

    4,071
    427
    83
    May 31, 2014
    Ratings:
    +833
    Local Time:
    12:21 AM
    Nginx-1.25.x
    MariaDB 10.3.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. rdan

    rdan Well-Known Member

    5,439
    1,399
    113
    May 25, 2014
    Ratings:
    +2,188
    Local Time:
    6:21 AM
    Mainline
    10.2
    Your site is pretty big I guess.
     
  3. pamamolf

    pamamolf Premium Member Premium Member

    4,071
    427
    83
    May 31, 2014
    Ratings:
    +833
    Local Time:
    12:21 AM
    Nginx-1.25.x
    MariaDB 10.3.x
    Is there any other way to do a safe backup of my database than mysqldump ?
     
  4. Matt

    Matt Well-Known Member

    929
    415
    63
    May 25, 2014
    Rotherham, UK
    Ratings:
    +671
    Local Time:
    10:21 PM
    1.5.15
    MariaDB 10.2
    What is the full command you are using with MySQLdump?
     
  5. pamamolf

    pamamolf Premium Member Premium Member

    4,071
    427
    83
    May 31, 2014
    Ratings:
    +833
    Local Time:
    12:21 AM
    Nginx-1.25.x
    MariaDB 10.3.x
    mysqldump without any parameter only username database and export to .sql without compression :)
     
  6. Matt

    Matt Well-Known Member

    929
    415
    63
    May 25, 2014
    Rotherham, UK
    Ratings:
    +671
    Local Time:
    10:21 PM
    1.5.15
    MariaDB 10.2
    add this to it
    Code:
    --opt --single-transaction
     
  7. pamamolf

    pamamolf Premium Member Premium Member

    4,071
    427
    83
    May 31, 2014
    Ratings:
    +833
    Local Time:
    12:21 AM
    Nginx-1.25.x
    MariaDB 10.3.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 Premium Member Premium Member

    4,071
    427
    83
    May 31, 2014
    Ratings:
    +833
    Local Time:
    12:21 AM
    Nginx-1.25.x
    MariaDB 10.3.x
    Double post ops...
     
  9. Matt

    Matt Well-Known Member

    929
    415
    63
    May 25, 2014
    Rotherham, UK
    Ratings:
    +671
    Local Time:
    10:21 PM
    1.5.15
    MariaDB 10.2
  10. pamamolf

    pamamolf Premium Member Premium Member

    4,071
    427
    83
    May 31, 2014
    Ratings:
    +833
    Local Time:
    12:21 AM
    Nginx-1.25.x
    MariaDB 10.3.x
    What's the difference with this parameter?

    --lock-tables=false

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

    Matt Well-Known Member

    929
    415
    63
    May 25, 2014
    Rotherham, UK
    Ratings:
    +671
    Local Time:
    10:21 PM
    1.5.15
    MariaDB 10.2
    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 Well-Known Member

    929
    415
    63
    May 25, 2014
    Rotherham, UK
    Ratings:
    +671
    Local Time:
    10:21 PM
    1.5.15
    MariaDB 10.2
    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 Premium Member Premium Member

    4,071
    427
    83
    May 31, 2014
    Ratings:
    +833
    Local Time:
    12:21 AM
    Nginx-1.25.x
    MariaDB 10.3.x
    Great thanks !!!!
     
  14. pamamolf

    pamamolf Premium Member Premium Member

    4,071
    427
    83
    May 31, 2014
    Ratings:
    +833
    Local Time:
    12:21 AM
    Nginx-1.25.x
    MariaDB 10.3.x
    For Myisam which way do you recommend me to use for the backup?