Increase in mysql Open_files_limit

  • Good afternoon everyone ... as the open thread says, some of you have been forced to increase this parameter to make a dump of a very large database ....



    Server version: 5.5.54-0 + deb8u1 (Debian)



    It would be good any help, to be able to skip this limitation .. I have already tried all the forums that I have read and changed ... but I did not manage to increase the paramenter to copy a database

  • Can you post the output for


    SQL
    1. SHOW VARIABLES LIKE 'open_files_limit';

    to check your current open files limit ?
    What have you changed so far ?

  • Good morning Bene ... thank you very much for your interest, sorry the delay was outside, enjoying a little summer with the family


    Sorry for my english ... I only use a google translate.


    I have tried several things reading some forums but none of them have been effective in order to expand the 2000 that I need
    I have changed values


    Ulimit -n 10000


    I have changed values in


    /etc/security/limits.conf


    I have modified values on mysql in your configuration file forcing the new values on the parameter


    Open-files-limit = 2000


    But I have not been able to get all the data at once .., so far I have worked with workbench to get sql ...
    But with the script that makes our backup in the panel does not leave the whole copy,
    More or less is a small summary


    output --- >


    mysql> SHOW GLOBAL STATUS LIKE 'Open_files';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | Open_files | 442 |
    +---------------+-------+
    1 row in set (0.00 sec)



    mysql> SHOW VARIABLES LIKE 'Open_files_limit';
    +------------------+-------+
    | Variable_name | Value |
    +------------------+-------+
    | open_files_limit | 1024 |
    +------------------+-------+
    1 row in set (0.00 sec)

  • For mariadb there is an extra systemd file ... I have no idea whether mysql uses a similar script in /lib/systemd/system/

    Code
    1. nano /lib/systemd/system/mariadb.service
    2. change:
    3. LimitNOFILE=1024
    4. service mysql restart
  • For mariadb there is an extra systemd file ... I have no idea whether mysql uses a similar script in /lib/systemd/system/

    You MUST never edit a file under that directory. you MUST

    • cp /lib/systemd/system/mariadb.service /etc/systemd/system/mariadb.service
    • edit /etc/systemd/system/mariadb.service

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206