4,461
edits
MySQL: Difference between revisions
add Moving all of the databases from one server to another
→mysqldump returning Errcode 13: update source to syntaxhighlight |
add Moving all of the databases from one server to another |
||
| Line 1: | Line 1: | ||
== mysqldump returning Errcode 13 == | == mysqldump returning Errcode 13 == | ||
* ''Last tested on Ubuntu 14.04.2 LTS (trusty)'' | * ''Last tested on Ubuntu 14.04.2 LTS (trusty)'' | ||
| Line 15: | Line 16: | ||
Disable apparmor temporary ( <code>service apparmor teardown</code> ) execute the above command and then restart it again ( <code>service apparmor start</code> ). | Disable apparmor temporary ( <code>service apparmor teardown</code> ) execute the above command and then restart it again ( <code>service apparmor start</code> ). | ||
== Moving all of the databases from one server to another == | |||
*''Last tested on Ubuntu 16.04.01 LTS (xenial)'' | |||
Log in as an admin on MySQL Console and lock the database to allow only read operations. | |||
<syntaxhighlight lang="mysql"> | |||
mysql> flush tables with read lock; | |||
mysql> set global read_only = on; | |||
mysql> exit | |||
</syntaxhighlight> | |||
Dump all of the databases into a file. | |||
<syntaxhighlight lang="bash"> | |||
$ mysqldump --lock-all-tables -u root -p --all-databases > dbs.sql | |||
</syntaxhighlight> | |||
Copy the dump to the new server. RSYNC is preferred over SCP, especially if the file is large. | |||
<syntaxhighlight lang="bash"> | |||
$ rsync -tvz --progress dbs.sql mhan@newserver.com:~/files/ | |||
or | |||
$ scp dbs.sql mhan@newserver.com:~/files/ | |||
</syntaxhighlight> | |||
The DB can be (optionally) unlocked. This may or may not be a good thing to do in your case. Do it at your own risk. | |||
<syntaxhighlight lang="mysql"> | |||
mysql> set global read_only = off; | |||
mysql> unlock tables; | |||
mysql> exit | |||
</syntaxhighlight> | |||
On the new server, execute this command to import the new dumped SQL file. | |||
<syntaxhighlight lang="bash"> | |||
$ mysql -u root -p < ~/files/dbs.sql | |||
</syntaxhighlight> | |||
[[Category:System administration]] | [[Category:System administration]] | ||