编程知识 cdmana.com

Fault analysis | mysql5.7 using mysqldump

author : Wang Xiang
Love can be born DBA Team members , In charge of the company DMP Operation and maintenance of products and customers MySQL How to deal with the problem . Good at database fault handling . Database technology and python Have a strong interest in .
In this paper, the source : Original contribution
* Produced by aikesheng open source community , Original content is not allowed to be used without authorization , For reprint, please contact the editor and indicate the source .

background

I'm dealing with clients at one time MySQL Problems encountered by customers MySQL Of sys The library doesn't work and throws an error :

mysql> SELECT * FROM sys.processlist; 
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

First , This problem is not hard to solve , But the phenomenon caused by this problem is quite interesting .

Troubleshooting common problems

Start with a few common questions :

  1. Not enough permissions ;
  2. sys library functions and procedures The loss of ;
  3. mysqldump Cross Version recovery after full backup 【 There will be problems 2 The phenomenon of 】;
  4. mysql The upgrade was not performed mysql_upgrade【 There will be problems 2 The phenomenon of 】;

First of all, check whether you have permission .

mysql> SHOW GRANTS FOR root@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)  

Obviously not , Then check to see if it's sys Library related functions and procedures lost ?

mysql> SELECT * FROM mysql.proc;
Empty set (0.00 sec)

mysql> SHOW PROCEDURE STATUS WHERE Db = 'sys';
Empty set (0.00 sec)

mysql>  SHOW FUNCTION STATUS WHERE Db = 'sys';
Empty set (0.00 sec)

sys library functions and procedures lost , That's the problem 3 It's the problem 4, Give to mysqldump Full backup and upgrade are not performed mysql_upgrade.

Start the long investigation process with doubt . After the root shaving problem of customers , It was found that none of the above happened . Users are used to backup everything (-A), And they all appear after the backup recovery sys library ERROR 1356, Check users MySQL There are several major versions of the environment MySQL 5.7.13,5.7.25,5.7.28. So the problem is fixed on mysqldump On the backup of .

First backup restore a look

I strongly believe that it is caused by the cross version of customers , Give the client some evidence . First verify a wave of the same version MySQL Use mysqldump After full recovery , Will there be sys library ERROR 1356.

Check for a wave before backing up sys library , Make sure it's completely OK After opening the whole .

mysql> SELECT * FROM sys.version;
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 1.5.2       | 5.7.31-log    |
+-------------+---------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM sys.processlist; 
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

mysql> SELECT COUNT(*) FROM mysql.proc;
+----------+
| COUNT(*) |
+----------+
|       48 |
+----------+
1 row in set (0.00 sec)

Back up all the libraries with the same command we often use .

mysqldump --all-databases --set-gtid-purged=OFF \
--master-data=2 --single-transaction --routines \
--events --triggers  --max_allowed_packet=256M  > all.sql

After the backup, we start to recover the data .

mysql -uroot -S /tmp/mysql.sock < all.sql

After recovery , Detect a wave of sys library .

mysql> SELECT * FROM sys.processlist; 
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

mysql> SELECT COUNT(*) FROM mysql.proc;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SHOW PROCEDURE STATUS WHERE Db = 'sys';
Empty set (0.00 sec)

mysql>  SHOW FUNCTION STATUS WHERE Db = 'sys';
Empty set (0.00 sec)

Slap slap face , Even the same version will appear ? What is the problem ?

Look at the other versions

Yes MySQL 5.7.13,5.7.21,5.7.25,5.7.28,5.7.31 Wait for several versions to test the full lying gun . Strange phenomenon is that their only common feature is that no matter how to backup or restore, as long as you use --all-databases (-A) Report on ERROR 1356. This can not help but let the author sink into meditation .

Looking for a breakthrough

Since the general law can only be used --all-databases (-A) Meeting ERROR 1356, Let's see what he backed up . So I called on my colleagues less Looked at the , I looked up and down . Suddenly found that :

  1. Backup SQL In the document DROP It fell off mysql.proc;
  2. after CREATE A new one mysql.proc;
  3. LOCK TABLES and UNLOCK TABLES There is no backup in the middle CREATE ROUTINE Any data ?

This is not equivalent to each import full backup to me without any sys schema routines Brand new mysql.proc surface ? That's not an extraordinary embarrassment ?

--
-- Table structure for table `proc`
--

DROP TABLE IF EXISTS `proc`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `proc` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `type` enum('FUNCTION','PROCEDURE') NOT NULL,
  `specific_name` char(64) NOT NULL DEFAULT '',
  `language` enum('SQL') NOT NULL DEFAULT 'SQL',
  `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
  `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
  `param_list` blob NOT NULL,
  `returns` longblob NOT NULL,
  `body` longblob NOT NULL,
  `definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `proc`
--

LOCK TABLES `proc` WRITE;
/*!40000 ALTER TABLE `proc` DISABLE KEYS */;
/*!40000 ALTER TABLE `proc` ENABLE KEYS */;
UNLOCK TABLES;

The truth

In official documents 【sys-schema-usage】

https://dev.mysql.com/doc/ref...

There's a passage on the page ( The official text is quoted here ):

However, those statements display the definitions in relatively unformatted form. To view object definitions with more readable formatting, access the individual .sql files found under the scripts/sys_schema in MySQL source distributions. Prior to MySQL 5.7.28, the sources are maintained in a separate distribution available from the sys schema development website at https://github.com/mysql/mysq...

Neither mysqldump nor mysqlpump dump the sys schema by default. To generate a dump file, name the sys schema explicitly on the command line using either of these commands:

mysqldump --databases --routines sys > sys_dump.sql
mysqlpump sys > sys_dump.sql

To reinstall the schema from the dump file, use this command:

mysql < sys_dump.sql

Official documents clearly tell us that we won't back it up sys library . But in the use of mysqldump In execution --all-databases It will be emptied mysql.proc Lead to sys Not working properly ; This is a BUG, And only exists in MySQL 5.7.x !

BUG Connect :

Solutions and usage scenarios

Aiming at this BUG Tidy up 4 There are several solutions for reference , Select and use according to the actual environment .

1、mysql_upgrade install or upgrade sys schema

This scheme is applicable to sys Kuo has been because of mysqldump Use in case of import and damage .

#  Delete  sys schema (An error occurs if a sys schema exists but has no version view)
mysql> DROP DATABASE sys;

#  This is the time  sys schema  Should not exist 
mysql> SHOW DATABASES;

#  Last , perform  mysql_upgrade sys schema  To return to normal 
mysql_upgrade --upgrade-system-tables --skip-verbose --force

mysql> SHOW DATABASES;
mysql> SELECT COUNT(*) FROM mysql.proc;
Be careful :mysql_upgrade Repairing sys At the same time , And fix mysql Library and user library table ( Lock during the period and the speed is average ), There is a very small possibility of accidental injury ; Use mysql_upgrade When you add --upgrade-system-tables, Otherwise, it will scan the user database table .

2、 Backup at the same time when full backup sys library

This scheme is applicable to the database that needs to be restored ,sys The library is not used normally ; Make an extra backup after full backup sys The library is used to repair .

mysqldump -A --set-gtid-purged=OFF --master-data=2 --single-transaction --routines --events --triggers  > all.sql
mysqldump --databases --routines sys > sys_dump_`mysql -V|awk '{print $5}'|cut -b 1-6`.sql

Be careful : It doesn't apply to master-slave .

3、 Use databases be completely ready

This scheme is suitable for all scenarios ,100% Security .

select_databases="                                                                 
    SELECT
        GROUP_CONCAT(schema_name SEPARATOR ' ') 
    FROM 
        information_schema.schemata 
    WHERE 
        schema_name NOT IN ('performance_schema','information_schema');"

databases=`mysql -NBe "$select_databases"`
mysqldump --set-gtid-purged=OFF --master-data=2 \
--single-transaction --routines --events --triggers \
--max_allowed_packet=256M  --databases > all.sql

4、 Use mysql-sys Open source code

If your database sys I got it all , It's the production library again . Then you can only use this method ;

【mysql-sys】

https://github.com/mysql/mysq...

Recorded in sys The create statement of the library will download the file to the local , And then according to the database version , Just execute the following command .

#  Before installation , The content is disabled  sql_log_bin, Don't log it .
mysql> source before_setup.sql

#  establish  sys  library , It will actually call... In other folders  sql  sentence 
#  To make a table 、 View 、 stored procedure 、 Trigger creation 
mysql> source sys_57.sql

#  Operation after installation , The content is to  sql_log_bin  Return to the state before operation 
mysql> source after_setup.sql

【 snacks 1】 try MySQL 8

test MySQL 8.0.0 to MySQL 8.0.20 The whole series is not affected by , The specific reason is from MySQL 8.0.0 It's been removed since mysql.proc This table . Please refer to the official documents for details :

  1. data-dictionary-usage-differences
  2. news-8-0-0

Previously, tables in the mysql system database were visible to DML and DDL statements. As of MySQL 8.0, data dictionary tables are invisible and cannot be modified or queried directly. However, in most cases there are corresponding INFORMATION_SCHEMA tables that can be queried instead. This enables the underlying data dictionary tables to be changed as server development proceeds, while maintaining a stable INFORMATION_SCHEMA interface for application use.

【 snacks 2】 If there is any doubt ?

Take a look at it mysqldump The source code. ( This source code design is also very interesting , I'm going to put it in the following article ), Take a look at this variable first .

/**
  First mysql version supporting the information schema.
*/
#define FIRST_INFORMATION_SCHEMA_VERSION 50003
/**
  Name of the information schema database.
*/
#define INFORMATION_SCHEMA_DB_NAME "information_schema"
/**
  First mysql version supporting the performance schema.
*/
#define FIRST_PERFORMANCE_SCHEMA_VERSION 50503
/**
  Name of the performance schema database.
*/
#define PERFORMANCE_SCHEMA_DB_NAME "performance_schema"

/**
  First mysql version supporting the sys schema.
*/
#define FIRST_SYS_SCHEMA_VERSION 50707  /*  First appeared sys schema Of MySQL edition  5.7.7 */

/**
  Name of the sys schema database.
*/
#define SYS_SCHEMA_DB_NAME "sys"

dump All library tables (--all-databases) Source code :

.........
/*  perform dump_all_databases Conditions  */
if (opt_alldbs)
  {
    if (!opt_alltspcs && !opt_notspcs)
      dump_all_tablespaces();
    dump_all_databases();
  }
.........
    
/* dump_all_databases */
static int dump_all_databases()
{
  MYSQL_ROW row;
  MYSQL_RES *tableres;
  int result=0

  /*  Get all databases :SHOW DATABASES */
  if (mysql_query_with_error_report(mysql, &tableres, "SHOW DATABASES"))
    return 1;
  while ((row= mysql_fetch_row(tableres)))
  {
      
    /*  exclude information_schema */
    if (mysql_get_server_version(mysql) >= FIRST_INFORMATION_SCHEMA_VERSION &&
        !my_strcasecmp(&my_charset_latin1, row[0], INFORMATION_SCHEMA_DB_NAME))
      continue;
      
    /*  exclude performance_schema */
    if (mysql_get_server_version(mysql) >= FIRST_PERFORMANCE_SCHEMA_VERSION &&
        !my_strcasecmp(&my_charset_latin1, row[0], PERFORMANCE_SCHEMA_DB_NAME))
      continue;
    
    /*  exclude sys */
    /*  Check current MySQL Is the version of  >=  The earliest support SYS_SCHEMA Version number of . && row[0]  by  SYS_SCHEMA_DB_NAME  Just skip. , No backup */
    if (mysql_get_server_version(mysql) >= FIRST_SYS_SCHEMA_VERSION &&
        !my_strcasecmp(&my_charset_latin1, row[0], SYS_SCHEMA_DB_NAME))
      continue;

    if (is_ndbinfo(mysql, row[0]))
      continue;
    
    /* dump All the tables in the library  */
    /*  one by one dump Each table  dump_all_tables_in_db */
    if (dump_all_tables_in_db(row[0]))
      result=1;
  }
.........

Backup functions and procedures Source code :

 /**  here  --all-databases sys  Libraries don't pass in  dump_routines_for_db  This function .
  So the backup process in the function is skipped sys library , That's why .sql In the document mysql.proc No, CREATE ROUTINE sys The phenomenon of the library  */
static uint dump_routines_for_db(char *db) 
{
........
  /* 0, retrieve and dump functions, 1, procedures */
  for (i= 0; i <= 1; i++)
  {
    /*  perform SHOW FUNCTION/PROCEDURE STATUS WHERE Db = xx, Get all functions and procedures */
    my_snprintf(query_buff, sizeof(query_buff),
                "SHOW %s STATUS WHERE Db = '%s'",
                routine_type[i], db_name_buff);

    if (mysql_query_with_error_report(mysql, &routine_list_res, query_buff))
      DBUG_RETURN(1);

    if (mysql_num_rows(routine_list_res))
    {

      while ((routine_list_row= mysql_fetch_row(routine_list_res)))
      {
        routine_name= quote_name(routine_list_row[1], name_buff, 0);
        DBUG_PRINT("info", ("retrieving CREATE %s for %s", routine_type[i],
                            name_buff));
        /*  perform SHOW CREATE FUNCTION/PROCEDURE xxx, Get all functions、procedures Create statement  */
        my_snprintf(query_buff, sizeof(query_buff), "SHOW CREATE %s %s",
                    routine_type[i], routine_name);
........    

版权声明
本文为[Aikesheng cloud database]所创,转载请带上原文链接,感谢

Scroll to Top