Modify MySQL Storage engine backup database error and solution

Backup Database error

Cause: The monitoring server has a relatively high CPU load recently. (Cpu4 core, load 2.7 or so) is very strange, because the other server MySQL occupies not a lot of resources, so I first optimized the database configuration file. The CPU dropped a little bit, but there was no particularly noticeable change.

So, from the MySQL storage engine and log considerations, the result is that the default engine is MyISAM. All right. Switch to InnoDB, (because I didn't back up in advance, I changed the engine in the config file because the log was not turned on except the binary.) So I didn't move it. Restart the database.

Ok

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M02/7E/9E/wKioL1cF1tyTp3zbAABQBRmfhQY914.jpg "title=" 3.jpg " alt= "Wkiol1cf1tytp3zbaabqbrmfhqy914.jpg"/> Long heard these two engines, the difference, it seems that the performance difference is really big AH!

All right. Back up the database now!

When you use back up all databases

Mysqldump:got error:23:out of resourceswhen opening file './mysql/jchigr32. MYD ' (errcode:24) when using LOCK TABLES

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/7E/A1/wKiom1cF1GXh_EzaAACYnTCrFic442.jpg "title=" 1.jpg " alt= "Wkiom1cf1gxh_ezaaacyntcrfic442.jpg"/>

Reason

appear outof Resources when opening file './xxx. MYD ' (errcode:23) error is

because the number of open files exceeds the my.cnf of the --open-files-limit . open-files-limit Options

Unable to MySQL The command line is modified directly and must be my.cnf set, the maximum value is 65536 .

First Use Ulimit-n command to view the number of open files under limit:

default is 1024x768

Solve

Vim/etc/my.cnf

open_files_limit=65536 (maximum)

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/7E/A1/wKiom1cF1IHgJ4qnAADP7McdZYE282.jpg "title=" 2.jpg " alt= "Wkiom1cf1ihgj4qnaadp7mcdzye282.jpg"/>

Restart MySQL OK

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M00/7E/A1/wKiom1cF1JfR59WVAABnf1rNUno369.jpg "title=" 3.jpg " alt= "Wkiom1cf1jfr59wvaabnf1rnuno369.jpg"/>

Check

Mysqlcheck-uroot-pxxx MySQL

Error:youcan ' t use locks with log tables.

Error:table ' mysql.tempmix ' doesn ' t exist

Status:operation failed

Mysql.tempmix1

Error:table ' mysql.tempmix1 ' doesn ' t exist

Status:operation failed

Mysql.tempmix4

Error:table ' mysql.tempmix4 ' doesn ' t exist

Status:operation failed

All right! Ask Google big God

Mysqldump table does not exist when lock tables is used

Symptoms

MySQL database dump failed with error:

Mysqldump:goterror:1146:table ' <TABLENAME> ' doesn ' t exist when using LOCK TABLES

Reason

1. Maybe someone deleted and recreated the InnoDB data file but forgot to remove the corresponding. frm file from the database directory for the InnoDB table, or someone moved the. frm file to another database. Please contact show tables

2. Incorrect permissions and ownership on table file MySQL data directory

3. Table is corrupted

4. Create uppercase in Table

Degree of resolution

1. Please contact show tables if the move is not listed in the table. frm from the database directory file

2. Permissions and ownership of files in the MySQL Data Catalog table are incorrect, the ideal owner should be the MySQL user and the permissions are set to 660.

3. Repair table

4. Set Lower_case_table_names

If the above is a bit OK then try using the--skip-lock-tables parameter with mysqldump.

According to the above ideas, quickly solve!

If this error occurs, it is recommended that you back up the database separately.

This article comes from "? Only! "Blog, be sure to keep this provenance http://renzhiyuan.blog.51cto.com/10433137/1761241

Modify MySQL Storage engine backup database error and solution