What is the method of dealing with errors related to mysql?

This article mainly explains the "mysql-related error problem handling method is what", the content of the explanation is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "mysql-related error problem handling method is what" it!

1. The my.cnf was modified and the configuration did not take effect after reboot.

Reason: there can be multiple directories for my.cnf configuration files in the order of priority: / etc/my.cnf-- > / etc/mysql/my.cnf-- > / usr/local/mysql/etc/my.cnf-- > ~ / .my.cnf

two。 The delay time of master-slave replication is very large, and judging from the results of multiple show slave status, binlog position has been motionless.

Solution:

1.show full processlist command to see what process is running, no abnormal process

two。 Check what binlog is dealing with on master, mysqlbinlog-- base64-output=decode-row xxx | more

You can see the position at the beginning of the binlog from the log, but it is not very readable in this way.

3. In another way, show binlog events in 'binlog-name' from position limit 20

You can know that it is submitted only when the data of a table is deleted, when the data is deleted in batches, and when the whole table cannot be deleted, it needs to be submitted in batches.

3. Failed to add self-growing column

If you want to add a self-growing column to a table as a primary key, an error is reported:

Mysql > ALTER TABLE pre_common_credit_log ADD `logid` mediumint (8) unsigned NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (logid)

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

This is because the number of records in the table has exceeded the maximum value of mediumint, so you need to change mediumint to int. If a table does not have a primary key, you want to change a column to a self-growing column and add it as a primary key

Run directly: the alter table xx change logid logid int auto_increment not null; statement will report an error, first change the column to the primary key, and then change it to self-growth. Or the same sentence as above

Run two statements directly in one command, separated by commas.

Thank you for your reading, the above is the content of "what is the method of dealing with errors related to mysql". After the study of this article, I believe you have a deeper understanding of what is the method of dealing with errors related to mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!