BiTel

# mysqldump bgbilling -p > bgbilling.sql
Enter password:
mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'log\_error\_1\_201007'': Out of resources when opening file '/usr/local/tmp_mysql/#sql_dc1_0.MYD' (Errcode: 24) (23)

# cat my.cnf
# A generic my.cnf file based on /usr/share/mysql/my-medium.cnf
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#

# The following options will be passed to all MySQL clients
[client]
user            = root
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
max_allowed_packet = 500M
myisam_data_pointer_size = 6
max_connections = 100000
server-id=1
log-bin = /mnt/advanced/mysql_bin/mysql_bin
user            = mysql
datadir         = /var/lib/mysql
port            = 3306
socket          = /var/lib/mysql/mysql.sock
pid-file        = /var/run/mysqld/mysqld.pid
skip-locking
key_buffer = 16M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
collation_server = utf8_unicode_ci
character_set_server = utf8

# plugins
plugin_dir=/usr/lib64/mysql/plugin
#plugin-load=ha_archive.so;ha_blackhole.so;ha_innodb.so;ha_revision.so;ha_sphinx.so;libpbxt.so

# acivate the federated storage engine
#federated

# Default to using old password format for compatibility with old and
# shorter password hash.
# Reference: http://dev.mysql.com/doc/mysql/en/Password_hashing.html
old_passwords

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking


# Point the following paths to different dedicated disks
tmpdir          = /usr/local/tmp_mysql
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

[mysqldump]
quick
max_allowed_packet = 640M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set = utf8

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
#open-files-limit=320000
err-log=/var/log/mysqld/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# MySQL Instance Manager options section
[manager]
user=mysql
default-mysqld-path=/usr/sbin/mysqld
socket=/var/lib/mysql/mysqlmanager.sock
pid-file=/var/run/mysqld/mysqlmanager.pid
password-file=/etc/mysqlmanager.passwd
run-as-service
monitoring-interval=20
port=2273

max_allowed_packet = 500M
max_connections = 100000
max_allowed_packet = 640M
#open-files-limit=320000 ----- вначале он небыл коменчен и был 32000

strace -f mysqldump bgbilling -p > bgbilling.sql 2> strace.log

sh-3.2$ perror 24
OS error code  24:  Too many open files

lsof | grep mysql | wc -l

# lsof | grep mysql | wc -l
lsof: WARNING: can't stat() fuse.gvfs-fuse-daemon file system /home/skyb/.gvfs
      Output information may be incomplete.
273

sysctl fs.file-max

# sysctl fs.file-max
fs.file-max = 747755

mysqldump -p bgbilling table1 table2 table3 > tables_1-3.sql

mysqldump --skip-lock-tables

show triggers;

mysql> show triggers;
Empty set (1.65 sec)

mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'log\_error\_1\_201007'': Out of resources when opening file

-- Retrieving table structure for table log_server_1_201009...
-- Sending SELECT query...
-- Retrieving rows...
mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'log\_server\_1\_201009'': Out of resources when opening file '/usr/local/tmp_mysql/#sql_1308_0.MYI' (Errcode: 24) (23)

Reply-Message=33\n\nTrace:\nLogin found.\n');
/*!40000 ALTER TABLE `log_server_1_201009` ENABLE KEYS */;
UNLOCK TABLES;

grep tmpdir /etc/mysql/my.cnf | grep -v '#' | awk '{ print $3 }' | xargs -I[] df -i []

# grep tmpdir /etc/my.cnf | grep -v '#' | awk '{ print $3 }' | xargs -I[] df -i []
Файловая система      Инодов   Испол   Своб  Исп % смонтирована на
/dev/md3             51806208    3329 51802879    1% /usr/local

# lsof | wc -l

# /sbin/sysctl fs.file-max

# ulimit

mysql> show variables like 'open_files_limit';

ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 20
file size               (blocks, -f) unlimited
pending signals                 (-i) 73728
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 73728
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

 show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+

mysqldump -p --single-transaction  bgbilling > bgbilling.sql

mysqldump -p --single-transaction  bgbilling > bgbilling.sql

mysqldump -p --single-transaction  bgbilling > bgbilling.sql