Photo-blog
Alle getoonde foto's zijn (c) MHE Vos, Nederland.
All shown photo's are (c) MHE Vos, Netherlands.
Below blog is created with the Blogspot.stack using the Categories-filter.
Quickly transfer MySQL databases to a new server
Fri, Aug 08 2014 Database, Linux, Mac OS X, MySQL, Unix PermalinkAgain I needed to transfer all data from one server to another. I knew I documented the transfer of MySQL databases somewhere (it's deep inside in the Replication-how-to) and decided to post them again here, so they're quicker to find.
One can transfer MySQL databases in various ways:
$ cd /var/mysql/ (or /var/lib/mysql/)
$ sudo zip -r ~/[database].zip [database]
Do this for each database that you want to copy. Then send all zip's per FTP to the new server.
Start an SSH session with the remote server and enter the following commands:
$ cd /var/mysql (or /var/lib/mysql/)
$ sudo unzip ~/[database].zip
$ sudo chown -R _mysql:admin [database]
For the above chown, check first with ls -l if _mysql:admin are the right owners. Then do this for each unzipped database.
Next, start Navicat and now you should see your databases in the connection of the new server. If not, you probably forgot to either do a Refresh Connection or the chown-command.
If you can access the tables and view data, good! If not, right click the table and choose Maintain->Repair Table->Quick or ->Extended and then try again.
One can transfer MySQL databases in various ways:
- Using mysqldump and zip + ftp
- Zip the database itself + ftp (you might need to repair the tables after unzipping)
- Use Navicat's Data Transfer module (not always good for tables with millions of records or blob data)
$ cd /var/mysql/ (or /var/lib/mysql/)
$ sudo zip -r ~/[database].zip [database]
Do this for each database that you want to copy. Then send all zip's per FTP to the new server.
Start an SSH session with the remote server and enter the following commands:
$ cd /var/mysql (or /var/lib/mysql/)
$ sudo unzip ~/[database].zip
$ sudo chown -R _mysql:admin [database]
For the above chown, check first with ls -l if _mysql:admin are the right owners. Then do this for each unzipped database.
Next, start Navicat and now you should see your databases in the connection of the new server. If not, you probably forgot to either do a Refresh Connection or the chown-command.
If you can access the tables and view data, good! If not, right click the table and choose Maintain->Repair Table->Quick or ->Extended and then try again.
Alle getoonde foto's zijn (c) MHE Vos, Nederland.
All shown photo's are (c) MHE Vos, Netherlands.