Shulou(Shulou.com)06/01 Report--

The following are some errors encountered in the process of using MySQL at work, which are specially recorded.

Error 1: the following error is prompted when executing mysql:

ERROR 1580 (HY000) at line 1: You cannot 'DROP' a log table if logging is enabled "

Solution: execute set global slow_query_log=off, and then run mysql again

Error 2: the following error occurred in the execution of the innobackupex Times:

Innobackupex: Error: Failed to connect to MySQL server: DBI connect ('; mysql_read_default_file=/mvnobss/users/mysusr01/mysql/bin/my.cnf;mysql_read_default_group=xtrabackup;port=39301

Mysql_socket=/mvnobss/users/mysusr01/mysql/mydata/mysqld-hdh-vocrmdb1-39301. Sockyards, such as rootballs,...) Failed: Can't connect to local MySQL server through socket'/ mvnobss/users/mysusr01/mysql/mydata/mysqld-hdh-vocrmdb1-39301.sock'

Solution: users performing innobackupex backups need to have read access to the MySQL data directory and read and write access to socket files

Error 3:java App reports a connection exception. The error message is as follows:

Cause: com.binary.jdbc.exception.DataSourceException: Cannot create PoolableConnectionFactory (null, message from server: "Host '10.123.121.252' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'")

Solution: mysqladmin-uroot-p flush-hosts

Error 4: execute mysqldump to report the following error:

Mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `res_card_storage_ detail` at row: 510319

Solution: increase the value of the parameter net_write_timeout (solution for online search)

After the modification, the problem remains the same. By observing the system log, we find the following important information:

Jul 6 17:29:25 yt-votst1 kernel: Out of memory: Kill process 16398 (mysqld) score 44 or sacrifice child

Jul 6 17:29:25 yt-votst1 kernel: Killed process 16398, UID 312, (mysqld) total-vm:5289288kB, anon-rss:1352100kB, file-rss:768kB

Then check the system memory settings:

Free-m

Total used free shared buffers cached

Mem: 29970 29830 139 0 169 224

-/ + buffers/cache: 29436

Swap: 0 0 0

Conclusion: so the real reason for the mysqldump error here is that the memory overflow of the mysqld process is caused by not setting swap, and the mysqldump runs normally after setting swap.

Error 5:mysql.user table data was emptied

Solution:

1. Add skip-grant-tables to my.cnf and restart myqld

two。 Insert data into the myql.user table

Mysql > insert into user (host,user,password) values ('localhost','root',password (' 123'))

ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

3. The error above is because there is a STRICT_TRANS_TABLES option in sql_mode. Remove this option and restart mysqld

4. Execute the following sql

Mysql > insert into user (host,user,password) values ('localhost','root',password (' 123'))

Mysql > update user set select_priv='y', insert_priv='y',update_priv='y',Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y' Create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y' where user='root'

Mysql > commit

5. Remove the skip-grant-tables parameter and restart mysqld

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.