Let me show you some examples of error analysis and solutions.

1.ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/data/mysql/mysql.sock’

  • Fault Analysis: The database is not started or the port is prohibited by the firewall.
  • Solution: Start the database or open the database listening port on the firewall.

2.ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

  • Fault Analysis: The password is incorrect or the user has no access permission.
  • Solutions:

1) Modify the my.cnf master configuration file, add skip-grant-tables under [mysqld], restart the database. Run the following command to change the password:

mysql> use mysql; mysql> update user set password=password("123456") where user="root";Copy the code

Delete the skip-grant-tables parameter, restart the database, and log in with the new password.

2) Reauthorize:

mysql> grant all on *.* to 'root'@'mysql-server' identified by '123456';
Copy the code

3. The client reports Too many connections

  • The number of connections exceeds the maximum connection limit of Mysql.
  • Solutions:
  • 1, increase the number of connections in my. CNF configuration file and restart MySQL service. max_connections = 10000
  • 2. Temporarily modifying the maximum number of connections does not take effect after the restart. You need to modify the configuration file in my. CNF. The modification takes effect after the next restart.

    set GLOBAL max_connections=10000;

4.Warning: World-writable config file ‘/etc/my.cnf’ is ignored ERROR! MySQL is running but PID file could not be found

  • The MySQL configuration file /etc/my.cnf has incorrect permissions.
  • Solutions:

    chmod 644 /et/my.cnf

5.InnoDB: Error: page 14178 log sequence number 29455369832 InnoDB: is in the future! Current system log sequence number 29455369832

  • Problem analysis: InnoDB data file is damaged.
  • Innodb_force_recovery =4 in [mysqld], start database backup data file, then remove this parameter, use backup file to restore data.

6. Slave_IO_Running of the slave library is NO

  • Problem Analysis: The server ID of the primary and secondary libraries is the same.
  • Solution: Change the value of the server id of the secondary library to be different from that of the primary library and lower than that of the primary library. After modification, restart, and then synchronize!

7. Slave_IO_Running of the slave library is NO problem

  • Problem analysis: There are many reasons for the slave library thread to be NO. The main reason is that the primary key conflicts or the master library deletes or updates the data. The records cannot be found from the library and the data is modified. The status code error can be 1007, 1032, 1062, or 1452.
  • Solution 1:

    mysql> stop slave; mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> start slave;

  • Solution 2: Set the user permission and secondary library read-only permission

    set global read_only=true;

8.Error initializing relay log position: I/O error reading the header from the binary log

  • Analysis problem: The relay log relay-bin of the secondary library is damaged.
  • Solution: Fix it manually, find the binlog and POS points of synchronization again, and then resynchronize.

    mysql> CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.xxx’,MASTER_LOG_POS=xxx;

Any operations or DBA who has maintained MySQL knows that some of the error messages you often encounter include code similar to 10XX.

Replicate_Wild_Ignore_Table:         Last_Errno: 1032         Last_Error: Could not execute Update_rows event on table xuanzhi.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000004, end_log_pos 3704
Copy the code

However, if you don’t look into it or have encountered it before, it’s not really clear what exactly this code means. This also caused certain obstacles for us to make mistakes.

Therefore, today migrant elder brother on the master from the synchronization process of some common error code, its specific explanation to you sorted out.

MySQL > insert into MySQL

  • 130: The file format is incorrect.

  • 145: The file cannot be opened

  • 1005: Failed to create table
  • 1006: Failed to create the database
  • 1007: Failed to create the database because the database already exists
  • 1008: Failed to delete database because the database does not exist
  • 1009: Failed to delete the database because the database file cannot be deleted
  • 1010: Failed to delete the database because the data directory cannot be deleted
  • 1011: Failed to delete database files
  • 1012: Cannot read records in system tables
  • 1020: The record has been modified by another user
  • 1021: The remaining disk space is insufficient. Expand the available disk space
  • 1022: Failed to change record because the keyword is repeated
  • 1023: An error occurred while closing
  • 1024: File reading error
  • 1025: Error while changing name
  • 1026: Write file error
  • 1032: The record does not exist
  • 1036: The data table is read-only and cannot be modified
  • 1037: The system memory is insufficient. Please restart the database or the server
  • 1038: Insufficient memory for sorting, please increase the sort buffer
  • 1040: Maximum number of connections reached to the database. Please increase the number of available connections to the database
  • 1041: System memory is insufficient
  • 1042: Invalid host name
  • 1043: Invalid connection
  • 1044: The current user does not have permission to access the database
  • 1045: Failed to connect to the database because the user name or password is incorrect
  • 1048: The field cannot be empty
  • 1049: Database does not exist
  • 1050: The data table already exists
  • 1051: Data table does not exist
  • 1054: The field does not exist
  • 1065: Invalid SQL statement. SQL statement is null
  • 1081: Failed to establish the Socket connection
  • 1114: The data table is full and cannot hold any records
  • 1116: Too many open tables
  • 1129: The database is abnormal. Please restart the database
  • 1130: Failed to connect to the database because you have no permission to connect to the database
  • 1133: The database user does not exist
  • 1141: The current user has no access to the database
  • 1142: The current user does not have access to the data table
  • 1143: The current user does not have access to fields in the data table
  • 1146: Data table does not exist
  • 1147: User access to the table is not defined
  • 1149: SQL statement syntax error
  • 1158: Network error, read error, please check the network connection
  • 1159: Network error, read timeout, please check the network connection status
  • 1160: Network error. Write error occurred. Please check the network connection
  • 1161: Network error, write timeout, please check network connection status
  • 1062: Failed to enter the database because the field value is repeated
  • 1169: Failed to update record because the field value is duplicate
  • 1177: Failed to open the table
  • 1180: Failed to commit transaction
  • 1181: Rollback transaction failed
  • 1203: The number of connections between the current user and the database has reached the maximum number of database connections. Please increase the number of available database connections or restart the database
  • 1205: Lock timeout
  • 1211: The current user does not have the permission to create users
  • 1216: Foreign key constraint check failed, update subtable record failed
  • 1217: Foreign key constraint check failed, delete or modify primary table record failed
  • 1226: The resources used by the current user exceed the allowed resources. Please restart the database or the server
  • 1227: Insufficient permission, you are not authorized to perform this operation
  • 1235: The MySQL version is too early to support this function
  • 1250: The client does not support the authentication protocol required by the server. Please consider upgrading the client.
  • 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL clientQuote:
  • 1267: Invalid mixed character set.
  • 2002: The server port is incorrect.
  • 2003: The MySQL service is not started. Please start it.
  • 2008: MySQL client ran out of memory error The reason for this error is simply that the customer does not have enough memory to store all the results.
  • 2013: Remote connection to the database is sometimes a problem caused by the MySQL server losing the connection while executing a SQL statement.
  • 10048: It is recommended to change the maximum number of connections in the my.ini file by changing the mysql_connect() method to mysql_pconnect(). Mysql_pconnect () : pconnect = 0; Pconnect = 0; pconnect = 0; Pconnect =0; Pconnect = 1; Enable anti-refresh, do not refresh too fast.
  • 10055: No cache space available, check if your drive C space is full, remove any files that are not used. Can be in the background of the “forum core Settings “,” core function Settings” in the “process optimization” on,”GZIP compressed output “off. Look up 10055 (no cache space available)

  • If the MySQL service fails to start on this machine, there must be an error in your my.ini file, the MySQL service cannot start properly. After you delete it, MySQL will run as its default configuration, and there will be no problem

** recommend several MySQL articles, worth learning!! **

As soon as I joined, I encountered MySQL so big Bug! Almost took the blame and left…

Great! Wall crack recommended a MySQL automated operation and maintenance tool!

Remember! Never use UTF-8 in MySQL

Why not deploy the database in a Docker container?

Wow!!!! 7000 words learning notes, MySQL from start to quit

MySQL Router high availability principle and practice

The above is today to you sort out the common error code description, I hope to help you with your daily work or learning, we can point to see and forward to share a wave of support, your support is the biggest writing power of migrant elder brother.