MySQL 5.7 backup and restore

Edwardon

Here are some easiest solution for me backup all SQL and restore

Export:

mysqldump -u root -p --all-databases > alldb.sql

Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments:

mysqldump -u root -p --opt --all-databases > alldb.sql
mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql

Import:

mysql -u root -p < alldb.sql

Common error

After upgrading MySQL to 5.7.8, you will get table ‘performance_schema.session_variables’ doesn’t exist

Solution

# mysql_upgrade -u root -p --force
# systemctl restart mysqld

References

  1. https://stackoverflow.com/questions/9497869/export-and-import-all-mysql-databases-at-one-time
  2. https://stackoverflow.com/questions/31967527/table-performance-schema-session-variables-doesnt-exist

Photo by FuYong Hua on Unsplash