When you need to migrate a MySQL server instance to a new server for any reason, eg upgrade of hardware, distri upgrade,… you can easily do the following:<\/p>\n
Migrating the databases<\/strong> Copy your sql dump file with eg rsync<\/p>\n On the new server, import your dumps:<\/p>\n Migrating the users<\/strong><\/p>\n If you are migrating to the same<\/em> MySQL version you can export all users and settings by dumping the mysql database itself.<\/p>\n and just copy & import of that file on the new server<\/p>\n However, when doing a MySQL version upgrade, we need to export all the Users and their privileges.<\/p>\n Here’s an easy bash script to do this.<\/p>\n Copy the lines between the ———————-<\/p>\n Login on your new server, go to the mysql shell via the admin user<\/p>\n And paste the output from our previous script.<\/p>\n Et Voila, your databases and users are migrated!<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" When you need to migrate a MySQL server instance to a new server for any reason, eg upgrade of hardware, distri upgrade,… you can easily do the following: Migrating the databases On the “old” mysql server, you need to export all databases by: mysqldump -uroot -p –all-databases > \/tmp\/dumpofalldb.sql Copy your sql dump file with […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[4,3,6],"tags":[16,17,12],"class_list":["post-62","post","type-post","status-publish","format-standard","hentry","category-debian","category-mysql","category-ubuntu","tag-dump","tag-migrate","tag-mysql-2"],"_links":{"self":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts\/62","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=62"}],"version-history":[{"count":4,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts\/62\/revisions"}],"predecessor-version":[{"id":65,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts\/62\/revisions\/65"}],"wp:attachment":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=62"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=62"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=62"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nOn the “old” mysql server, you need to export all databases by:<\/p>\nmysqldump -uroot -p --all-databases > \/tmp\/dumpofalldb.sql<\/pre>\n
rsync -avh \/tmp\/dumpofalldb.sql username@192.168.10.11:\/tmp<\/pre>\n
mysql -uroot -p < \/tmp\/dumpofalldb.sql<\/pre>\n
mysqldump -uroot -p mysql > \/tmp\/dumpmysqldb.sql<\/pre>\n
#!\/bin\/bash\r\n# addapted from http:\/\/serverfault.com\/questions\/105612\/how-to-copy-user-priviledges-with-mysql\r\nread -p \"Enter your MySQL admin user: \" User\r\nread -sp \"Enter password\" Password\r\necho \"\"\r\necho \"-----------------------------------------------------------------------------------\"\r\n( mysql -u$User -p$Password --batch --skip-column-names -e \"SELECT user, host FROM user\" mysql ) | while read user host\r\ndo\r\n CreateUser=`mysql -u$User -p$Password --batch --skip-column-names -e\"SHOW GRANTS FOR '$user'@'$host'\"`\r\n echo \"$CreateUser;\"\r\n echo \"flush privileges;\"\r\ndone\r\necho \"-----------------------------------------------------------------------------------\"<\/pre>\n
mysql -uroot -p<\/pre>\n