Copyright note: This article is kept and original by the blogger. Please indicate the source for reprint
original address: https://blog.csdn.net/qq_ 38688267/article/details/117745441

Contents of articles

Scenario Description: find a solution on the network and use ‘mysqldump — all databases’ to back up the’ MySQL ‘library

Finally, the solution is summarized

Scene introduction

   after installing mysql8, use mysqldump command to backup data from mysql5.7, and then synchronize to mysql8. The specific orders are as follows:

mysqldump -uroot -p --all-databases > /root/dump.sql

   then import the data into mysql8 through source command. After the import, when querying the table list, mysql8 reports an error: MySQL E rror:The user specified as a definer (‘mysql.infoschema’@’localhost’) does not exist。

Looking for solutions on the Internet

   a slightly more reliable solution: https://blog.wmsay.com/article/44 . But it still doesn’t solve my problem…

Do it yourself

   after careful thinking, I think that the problem may be in dump. SQL , because I directly specified all databases libraries, which may be MySQL and information_ Schema and other libraries are backed up, and then the data of these libraries in MySQL 8 is covered, resulting in an error.

   in order to confirm my idea, I tried the mysqldump -- all databases command locally and looked at the data in it:

Using mysqldump -- all databases will back up the MySQL library

   will not back up information_ schema、performance_ Schema library
  

Final solution

   the reason is the data coverage of MySQL database data mysql5.7, which is bound to have problems. Simply re install MySQL 8. Then dump the data again and specify the library to dump

mysqldump -uroot -p --databases mydb1 mydb2 > /root/dump.sql

Summary

   when using mysqldump , do not use the -- all databases parameter lazily, but use the -- databases db1 DB2 db3 method to specify. Using mysqldump -- all databases will back up the MySQL library, which is prone to problems!