How to deal with mysqldump error 23: out of resources when opening file

So earlier today I was doing a mysql dump of a large database. And I got this error:

mysqldump: Got error: 23: "Out of resources when opening file './xxxx/xxxx' (Errcode: 24)" when using LOCK TABLE

A quick google reveals that it’s because the number of files that MySQL is permitted to open has been exceeded.

So I counted how many files our database has:

ls /var/lib/mysql/dbname/ -l|wc -l

The result is 8350 files.

Then checked the limit by executing this in phpmyadmin:

SHOW VARIABLES LIKE 'open%'

It gives me a result of 1024, so I opened /etc/my.cnf and added

[mysqld]
open_files_limit = 10000

Unfortunately this didn’t do the job!

Some further digging landed me on this stackexchange post: http://dba.stackexchange.com/questions/86987/mysql-open-files-limit-cannot-change-this-variable

Looks like the issue is systemd related.

Edit /usr/lib/systemd/system/mysqld.service  and add

LimitNOFILE=10000
LimitMEMLOCK=10000

Then run systemctl daemon-reload  and systemctl restart mysql.service .

Now with all that sorted, finally, the real deal:

mysqldump -u username -p dbname | gzip > ./dbexport.sql.gz

 

WHMCS产品信息无法修改写入的解决办法

网站从美国搬到香港后,这个问题困扰了我好几天,最后整得没办法了,给WHMCS官方发邮件求助,解决办法很简单,去掉MYSQL的STRICT MODE问题就迎刃而解了。
具体方法:
Open your “my.ini” file within the MySQL installation directory, and look for the text “sql-mode”.
Find:

Code:
# Set the SQL mode to strict
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”Replace with:

Code:
# Set the SQL mode to strict
sql-mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”Or, you can run an SQL query within your database management tool, such as phpMyAdmin:

Code:
SET @@global.sql_mode= ”;

MySQL数据库学习笔记

重点: mysql alter 语句用法,添加、修改、删除字段等

您正在看的MySQL教程是:MySQL数据库学习笔记。
MySQL数据库学习笔记
(实验环境:Redhat9.0,MySQL3.23.54)
纲要:
一,连接MySQL
二,MySQL管理与授权
三,数据库简单操作
四, 数据库备份
五,后记
一,连接MySQL
格式:mysql -h 远程主机地址 -u 用户名 -p 回车
输入密码进入:

mysql -u root -p 回车
Enter password: ,输入密码就可以进入
mysql> 进入了
退出命令:>exit 或者ctrl+D

Continue reading MySQL数据库学习笔记

略谈MySQL数据库异常对自增字段AUTO_INCREMENT影响

完整标题:主题帖子数值暴跳的背后——略谈MySQL数据库异常对自增字段AUTO_INCREMENT影响

讨论链接:http://www.phpchina.com/bbs/thread-106637-1-1.html

文章可能有错误,请各位指正。

题录:本文:
A、揭示538976288,8224等这些数值的含义,略谈MySQL数据库异常对自增字段的影响
B、猜测自增字段最大已编号数值(下称:自增字段最大值)的确定方式,猜测不同的异常修复时对自增字段最大值的影响

Continue reading 略谈MySQL数据库异常对自增字段AUTO_INCREMENT影响