Environment
MySQL version 5.6.28
Problem:
Execution mysqldump The following error occurred, small white when performing mysqldump backup, is to perform a single error
1, Mysqldump:got error:1146:table ' MySQL. innodb_index_stats' doesn ' t exist when using LOCK TABLES
2, Mysqldump:got error:1146:table ' MySQL. innodb_table_stats' doesn ' t exist when using LOCK TABLES
3, Mysqldump:got error:1146:table ' MySQL. slave_master_info' doesn ' t exist when using LOCK TABLES
4, Mysqldump:got error:1146:table ' MySQL. slave_relay_log_info' doesn ' t exist when using LOCK TABLES
5, Mysqldump:got error:1146:table ' MySQL. slave_worker_info' doesn ' t exist when using LOCK TABLES
How to resolve:
1, delete the above system table,
mysql> drop table mysql.innodb_index_stats;
mysql> drop table mysql.innodb_table_stats;
mysql> drop table Mysql.slave_master_info;
mysql> drop table Mysql.slave_relay_log_info;
mysql> drop table Mysql.slave_worker_info;
The following error may be reported:
mysql> drop table mysql.innodb_index_stats;
ERROR 1051 (42S02): Unknown table ' Mysql.innodb_index_stats '
Use the following 2 delete method
2. Delete the associated. frm. ibd file under MySQL database and restart the database
[Email protected] mysql]# RM-RF innodb_index_stats.*
[Email protected] mysql]# RM-RF innodb_table_stats.*
[Email protected] mysql]# RM-RF slave_master_info.*
[Email protected] mysql]# RM-RF slave_relay_log_info*
[Email protected] mysql]# RM-RF slave_worker_info*
[Email protected] mysql]# systemctl restart mysqld
3, log in the database, re-create the above system table (copy the corresponding code below to create the corresponding table)
CREATE TABLE ' Innodb_index_stats ' (
' database_name ' varchar (COLLATE) Utf8_bin not NULL,
' table_name ' varchar (COLLATE) Utf8_bin not NULL,
' index_name ' varchar (COLLATE) Utf8_bin not NULL,
' Last_update ' timestamp not NULL the DEFAULT current_timestamp on update current_timestamp,
' Stat_name ' varchar (COLLATE) Utf8_bin not NULL,
' Stat_value ' bigint () unsigned not NULL,
' Sample_size ' bigint () unsigned DEFAULT NULL,
' stat_description ' varchar (1024x768) COLLATE Utf8_bin not NULL,
PRIMARY KEY (' database_name ', ' table_name ', ' index_name ', ' stat_name ')
) Engine=innodb DEFAULT Charset=utf8 collate=utf8_bin stats_persistent=0;
CREATE TABLE ' Innodb_table_stats ' (
' database_name ' varchar (COLLATE) Utf8_bin not NULL,
' table_name ' varchar (COLLATE) Utf8_bin not NULL,
' Last_update ' timestamp not NULL the DEFAULT current_timestamp on update current_timestamp,
' N_rows ' bigint () unsigned not NULL,
' Clustered_index_size ' bigint () unsigned not NULL,
' Sum_of_other_index_sizes ' bigint () unsigned not NULL,
PRIMARY KEY (' database_name ', ' table_name ')
) Engine=innodb DEFAULT Charset=utf8 collate=utf8_bin stats_persistent=0;
CREATE TABLE ' Slave_master_info ' (
' Number_of_lines ' int (ten) unsigned not NULL COMMENT ' number of lines in the file. ',
' Master_log_name ' text CHARACTER SET UTF8 COLLATE utf8_bin not NULL COMMENT ' The name of the Master binary log currently B Eing read from the master. ',
' Master_log_pos ' bigint () unsigned not NULL COMMENT ' the Master log position of the last read event. ',
' Host ' char CHARACTER SET UTF8 COLLATE utf8_bin not NULL DEFAULT ' ' COMMENT ' the Host name of the master. ',
' user_name ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' The User name used to connect to the master. ',
' User_password ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' The password used to connect to the master. ',
' Port ' int (unsigned) is not NULL COMMENT ' the network Port used to connect to the master. ',
' Connect_retry ' int (ten) unsigned not NULL COMMENT ' the period (in seconds) that the slave would wait before trying to recon Nect to the master. ',
' Enabled_ssl ' tinyint (1) Not NULL COMMENT ' indicates whether the server supports SSL connections. ',
' Ssl_ca ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' The file used for the Certificate Authority (CA) Certificate. ',
' Ssl_capath ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' The path to the Certificate authority (CA) certificates. ',
' Ssl_cert ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' The name of the SSL certificate file. ',
' Ssl_cipher ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' The name of the cipher in use for the SSL connection. ',
' Ssl_key ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' The name of the SSL key file. ',
' Ssl_verify_server_cert ' tinyint (1) Not NULL COMMENT ' Whether to verify the server certificate. ',
' Heartbeat ' float not NULL,
' Bind ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' displays which interface was employed when connecting to the MySQL Server ',
' Ignored_server_ids ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' The number of server IDs to being ignored, followed by The actual server IDs ',
' uuid ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' the master server Uuid. ',
' Retry_count ' bigint () unsigned not NULL COMMENT ' number of reconnect attempts, to the master, before giving up. ',
' ssl_crl ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' The file used for the Certificate revocation List (CRL) ',
' Ssl_crlpath ' text CHARACTER SET UTF8 COLLATE utf8_bin COMMENT ' The path used for Certificate revocation List (CRL) files ' ,
' Enabled_auto_position ' tinyint (1) Not NULL COMMENT ' indicates whether Gtids would be used to retrieve events from the mast Er. ',
PRIMARY KEY (' Host ', ' Port ')
) Engine=innodb DEFAULT Charset=utf8 stats_persistent=0 comment= ' Master information ';
CREATE TABLE ' Slave_relay_log_info ' (
' Number_of_lines ' int (ten) unsigned not NULL COMMENT ' number of lines in the file or rows in the table. Used to version table definitions. ',
' Relay_log_name ' text CHARACTER SET UTF8 COLLATE utf8_bin not NULL COMMENT ' The name of the current Relay log file. '
' Relay_log_pos ' bigint () unsigned not NULL COMMENT ' the Relay log position of the last executed event. ',
' Master_log_name ' text CHARACTER SET UTF8 COLLATE utf8_bin not NULL COMMENT ' The name of the Master binary log file from W Hich the events in the relay log file were read. ',
' Master_log_pos ' bigint () unsigned not NULL COMMENT ' the Master log position of the last executed event. ',
' Sql_delay ' int (one) not NULL COMMENT ' The number of seconds that the slave must lag behind the master. ',
' Number_of_workers ' int (ten) unsigned not NULL,
' id ' int (ten) unsigned not NULL COMMENT ' Internal Id of that uniquely identifies this record. ',
PRIMARY KEY (' Id ')
) Engine=innodb DEFAULT Charset=utf8 stats_persistent=0 comment= ' Relay Log information ';
CREATE TABLE ' Slave_worker_info ' (
' Id ' int (ten) unsigned not NULL,
' Relay_log_name ' text CHARACTER SET UTF8 COLLATE utf8_bin not NULL,
' Relay_log_pos ' bigint () unsigned not NULL,
' Master_log_name ' text CHARACTER SET UTF8 COLLATE utf8_bin not NULL,
' Master_log_pos ' bigint () unsigned not NULL,
' Checkpoint_relay_log_name ' text CHARACTER SET UTF8 COLLATE utf8_bin not NULL,
' Checkpoint_relay_log_pos ' bigint () unsigned not NULL,
' Checkpoint_master_log_name ' text CHARACTER SET UTF8 COLLATE utf8_bin not NULL,
' Checkpoint_master_log_pos ' bigint () unsigned not NULL,
' Checkpoint_seqno ' int (ten) unsigned not NULL,
' Checkpoint_group_size ' int (ten) unsigned not NULL,
' Checkpoint_group_bitmap ' blob not NULL,
PRIMARY KEY (' Id ')
) Engine=innodb DEFAULT Charset=utf8 stats_persistent=0 comment= ' Worker information ';
4, if the database has Master-slave Master architecture, restore the appeal form, slave start error, solution please Baidu
......
Perform mysqldump backup data times wrong