•  

Announcement

Announcement Module
Collapse
No announcement yet.

Open file issue while taking backup

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Open file issue while taking backup

    #1
    Hi everybody,

    I am getting some problem while taking backup of mysql database. My database size is near about 20 GB. While taking backup the Opened_files and Open_files limit get increased suddendly.

    There is any other method so that the limit will not be cross.

    I am taking the backup with command.

    mysqldump --all-databases --lock-all-tables > /mnt/db.sql

    According to me , while taking backup nobody access my DB then how it can be possible that the Open_files limit gets increase. It means while mysqldump runs it opens the files also.

    Kindly help me out of the situation.

    Thanks

    -Vaneet Gupta
    Tags: None

  • #2
    Your backup command do _not_ _block_ new connections.
    It only blocks access to writing to the tables.

    So during backup all writes will hang until the lock is lifted and due to performance degradation during backup you will probably get more connections overall.

    These will probably still consume file descriptors for the .frm files even though they aren't allowed to manipulate the data in the tables.

    But the question is, what is your actually problem? That OS reports to many open descriptors or that MySQL does or?
    Can't you increase the level to handle a backup?

    But if your database is large enough you could consider some other backup method to avoid locking. For example you can run a secondary server with replication slave on where you run the backups. That way you don't disturb production during backups at all.

    Comment


    • #3
      Do you have a lot of unused MyISAM tables that get opened for making a backup?

      > Your backup command do _not_ _block_ new connections.
      > It only blocks access to writing to the tables.
      For InnoDB, really?

      Comment


      • #4
        Hi,

        Thanks for the reply , I don't what exactly the issue. I am taking the backup of mysql at 1 am morning when nobody access the database or website. But issue is that while taking the backup of mysql database , it suddendly increases the DB connection to 700 something like that which I am capturing data using netstat and some scripts.

        According to me , when we are taking the backup of mysql , it does read lock , but nobody is accessing the website and database then how is it possible that the file descriptor limits increases or connection increases.

        Can you please help out or explain? I am unable to dignose the problem.

        Comment


        • #5
          hi,

          thanks for the reply, I agreed to your point , mysql does blocks writting to the tables.

          Yes, I have lot of unused tables in the database, might be 100 of tables are unused. But I don't know , these are open or not. How can I check the unused tables are opened.

          Even I have restarted the mysql server also.

          That is sure nobody was access the DB or website when the backup is taking.

          Comment

          Working...
          X