Several issues encountered in backing up and importing the MySQL database

First, how to export and backup

1, the common method, the use of tools or commands to export SQL script directly, take navicat as an example, directly select the database, dump the SQL file

Problem: Bad handling when there is a view or function execution fails

2. Separate export of view function and table data

Take Navicat as an example:

① Create a backup that includes views and tables: Click the backup directly below the database, and then create a new backup.

② after the completion of the production, directly select the extraction of SQL, extract can choose to be a table, view, or function, so that the table and the view foot off the

Second, after generating the export and backup of the SQL file is imported, here to solve the import process encountered several problems:

1. Error:mysql server has gone away

There are many reasons to report this mistake, the most probable one:

Your SQL query file is too large, you need to modify the MySQL configuration, directly under the MYSQLD node of the My.ini file to modify: max_allowed_packet=16777216 just fine.

2. error:the user specified as a definer (' root ' @ '% ') does not exist

This is due to insufficient permissions for the current user to execute some SQL, giving the current user all the permissions directly:

Grant all privileges on * * to [e-mail protected] "%" identified by ".";

Flush privileges;

Note: There is a possibility that multiple database dependencies and multiple views depend on each other during the import process, which adjusts the order of execution to complete the import correctly.

Several issues encountered in backing up and importing the MySQL database