{"id":3247,"date":"2019-05-11T01:31:02","date_gmt":"2019-05-10T17:31:02","guid":{"rendered":"https:\/\/www.ookangzheng.com\/?p=3247"},"modified":"2020-05-24T15:58:21","modified_gmt":"2020-05-24T07:58:21","slug":"mysql-5-7-backup-and-restore","status":"publish","type":"post","link":"https:\/\/www.ookangzheng.com\/mysql-5-7-backup-and-restore\/","title":{"rendered":"MySQL 5.7 backup and restore"},"content":{"rendered":"\n<p>Here are some easiest solution for me backup all SQL and restore<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Export:<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqldump -u root -p --all-databases > alldb.sql<\/code><\/pre>\n\n\n\n<p>Look up the&nbsp;<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/mysqldump.html\">documentation for mysqldump<\/a>. You may want to use some of the options mentioned in comments:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqldump -u root -p --opt --all-databases > alldb.sql\nmysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Import:<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -u root -p &lt; alldb.sql<\/code><\/pre>\n\n\n\n<p><strong>Common error<\/strong><\/p>\n\n\n\n<p>After upgrading MySQL to 5.7.8, you will get table ‘performance_schema.session_variables’ doesn’t exist<\/p>\n\n\n\n<p><strong>Solution<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># mysql_upgrade -u root -p --force\n# systemctl restart mysqld<\/code><\/pre>\n\n\n\n<p>References<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li><a href=\"https:\/\/stackoverflow.com\/questions\/9497869\/export-and-import-all-mysql-databases-at-one-time\">https:\/\/stackoverflow.com\/questions\/9497869\/export-and-import-all-mysql-databases-at-one-time<\/a><\/li><li><a href=\"https:\/\/stackoverflow.com\/questions\/31967527\/table-performance-schema-session-variables-doesnt-exist\">https:\/\/stackoverflow.com\/questions\/31967527\/table-performance-schema-session-variables-doesnt-exist<\/a><\/li><\/ol>\n\n\n\n<p>Photo by&nbsp;<a href=\"https:\/\/unsplash.com\/photos\/tEMU4lzAL0w?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText\">FuYong Hua<\/a>&nbsp;on&nbsp;<a href=\"https:\/\/unsplash.com\/search\/photos\/error?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText\">Unsplash<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Here are some easiest solution for me backup all SQL and restore Export: Look up the&nbsp;documentation for mysqldump. You may&hellip;<\/p>\n","protected":false},"author":1,"featured_media":3248,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37,32],"tags":[],"class_list":["post-3247","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-linux","category-technology"],"_links":{"self":[{"href":"https:\/\/www.ookangzheng.com\/wp-json\/wp\/v2\/posts\/3247","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ookangzheng.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ookangzheng.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ookangzheng.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ookangzheng.com\/wp-json\/wp\/v2\/comments?post=3247"}],"version-history":[{"count":0,"href":"https:\/\/www.ookangzheng.com\/wp-json\/wp\/v2\/posts\/3247\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.ookangzheng.com\/wp-json\/wp\/v2\/media\/3248"}],"wp:attachment":[{"href":"https:\/\/www.ookangzheng.com\/wp-json\/wp\/v2\/media?parent=3247"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ookangzheng.com\/wp-json\/wp\/v2\/categories?post=3247"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ookangzheng.com\/wp-json\/wp\/v2\/tags?post=3247"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}