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

Let's learn what to do when there is an error when mysqldump performs backup data. I believe everyone will benefit a lot after reading it. The text is not very good. I hope that mysqldump will report errors when performing backup data. This short article is what you want.

Surroundings

MySQL version 5.6.28

Question:

The following error occurred when executing mysqldump. When Xiaobai performed mysqldump backup, he reported one error at a time.

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

Solution:

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 errors may be reported:

Mysql > drop table mysql.innodb_index_stats

ERROR 1051 (42S02): Unknown table 'mysql.innodb_index_stats'

Use the deletion method of the following 2 at this time

2. Delete the relevant .frm .ibd files under the database mysql, and restart the database.

[root@test mysql] # rm-rf innodb_index_stats.*

[root@test mysql] # rm-rf innodb_table_stats.*

[root@test mysql] # rm-rf slave_master_info.*

[root@test mysql] # rm-rf slave_relay_log_info*

[root@test mysql] # rm-rf slave_worker_info*

[root@test mysql] # systemctl restart mysqld

3. Log in to the database and recreate the above system table (copy the corresponding code below to create the corresponding table)

CREATE TABLE `innodb_index_ stats` (

`database_ name` varchar (64) COLLATE utf8_bin NOT NULL

`table_ name` varchar (64) COLLATE utf8_bin NOT NULL

`index_ name` varchar (64) COLLATE utf8_bin NOT NULL

`last_ update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

`stat_ name` varchar (64) COLLATE utf8_bin NOT NULL

`stat_ value` bigint (20) unsigned NOT NULL

`sample_ size` bigint (20) unsigned DEFAULT NULL

`stat_ substitution` varchar (1024) 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 (64) COLLATE utf8_bin NOT NULL

`table_ name` varchar (64) COLLATE utf8_bin NOT NULL

`last_ update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

`n _ rows` bigint (20) unsigned NOT NULL

`clustered_index_ size` bigint (20) unsigned NOT NULL

`sum_of_other_index_ sizes` bigint (20) 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 (10) 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 being read from the master.'

`Master_log_ Pos` bigint (20) unsigned NOT NULL COMMENT 'The master log position of the last read event.'

`Host` char (64) 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 (10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.'

`Connect_ retry` int (10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect 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 is employed when connecting to the MySQL server'

`Ignored_server_ IDs`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs'

`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.'

`Retry_ count` bigint (20) 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 will be used to retrieve events from the master.'

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 (10) 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 (20) 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 which the events in the relay log file were read.'

`Master_log_ Pos` bigint (20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.'

`Sql_ delay` int (11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.'

`Number_of_ workers` int (10) unsigned NOT NULL

`Id` int (10) unsigned NOT NULL COMMENT 'Internal Id 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 (10) unsigned NOT NULL

`Relay_log_ name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

`Relay_log_ Pos` bigint (20) unsigned NOT NULL

`Master_log_ name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

`Master_log_ Pos` bigint (20) unsigned NOT NULL

`Checkpoint_relay_log_ name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

`Checkpoint_relay_log_ Pos` bigint (20) unsigned NOT NULL

`Checkpoint_master_log_ name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

`Checkpoint_master_log_ Pos` bigint (20) unsigned NOT NULL

`Checkpoint_ seqno` int (10) unsigned NOT NULL

`Checkpoint_group_ size` int (10) 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 a master-slave master / slave architecture, after the appeal form is restored, Slave starts to report an error. Please ask Baidu for the solution.

.

After reading this article about how to report an error when mysqldump performs backup data, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.