Move a database from a linux to another linux

mysql> show databases;
| Database           |
| information_schema |
| geo                |
| mysql              |
| performance_schema |
| sys                |
5 rows in set (0.00 sec)

mysql> select * from country;
| id | country |
|  1 | France  |
|  2 | Japan   |
2 rows in set (0.00 sec)

mysql> select * from city;
| id | city  |
|  1 | Paris |
|  2 | Tokyo |
2 rows in set (0.00 sec)

The code above is a copy from the shell of linux(xubuntu) web-server which I installed, some days ago, apache, PHP, and mySQL one by one.

I like to move the database “geo” including the 2 tables and their records to another same server which is installed apache, PHP, and mySQL, one by one on linux(xubuntu).

In order to move it, I’ll use a USB.or a portable hard drive.

Can I move it from a server to another same environment server with your help?

(This matter is immigration or backup of data)

I am using Postgresql, but there must be a “msqldump” that you dump to a file and restore with “mysqlrestore” to another database.

After reading the link above that sibertius guides me
I did the below.

It produces warning like the above.
How can I fix it to back up from the command line?

If it is only a single database then try:

  1. install PhpMyAdmin on both computers
  2. select source database
  3. select source, Export and save Zipped source
  4. select destination, Import save Zipped file

You have two tables with 4 rows total. Running some queries to recreate that on the new machine takes less time than making a backup, getting it on the other machine, and importing it.

Just saying.


Thank you for the suggestion.

I like to install PhpMyAdmin after I succeed in moving data to another computer without phpMyAdmin.

Sorry to say, I don’t understand the above quote…
Would you rephrase it, please?

I imagine rpkamp is saying that you have so little data in that database that it will be less work to just type it in on the new machine rather than copy it across.

I presumed that you were asking about the principle of how data is migrated from one server to another, and just using this database as an example.

My first exposure to MySQL was running data conversions into a system based on it, and I distinctly recall that moving a database from one machine to another just involved copying the folder of database-name from one server to the other. I don’t see any reference to that method, though. Last time I tried it my exporting each table manually, I got all sorts of errors about “table already exists” on the destination system, even though it was a new system and the table definitely did not exist. That’s one of the things that puts me off updating my system, but that’s another topic.

What I’m saying is, typing this:

CREATE TABLE country(id INT, country VARCHAR(255), PRIMARY KEY(id));
INSERT INTO country (id, country) VALUES (1, 'France'), (2, 'Japan');

INSERT INTO city (id, city) VALUES (1, 'Paris'), (2, 'Tokio');

on the new server is faster than figuring out how to make a backup, then make a backup, then copy the backup to the new machine, and then import the backup on the new machine.

Yes, I am asking about the principle of how data is migrated from one server to another.

In order to make my question clear since my English is not enough, I made the database “geo” and two tables in geo, ie, country, a example data.

I am afraid that “CREATE DATABASE city” is typo of “CREATE TABLE city”…

For my question, I tried to changed the database name “city” to “geo” from the quote above.

When I create a database ‘geo’ like the above, I think “geo” will be created as a directory in file system of my linux, ie xubuntu…
I like to find the path or location of the directory “geo”.

The root directory of my file system has 20 directoies like the below.

What I want to know is the path or location of the database “geo” I created?
Is it inside /bin, /boor, or some other directory among directories which is listed above?
Can I confirm where is the database “geo” with your help?

Isn’t there a tool in Linux that would allow you to “find” a directory within a file system? I haven’t used it for a long time, but it strikes me as something that would be useful. You could just find geo (presuming find is the command, though it’s probably something way less logical), and if that gives too many results, create a database with a really off-the-wall name and look for that.

In my Windows-based WAMP installation, my databases are stored in directories inside the C:\wamp\bin\mysql\mysql5.6.17\data directory.

You should not copy the files from one server to another, but use mysqldump instead, as already suggested in this thread.

That is correct, that should have been CREATE TABLE.

As you said like the above,
I tried to use mysqldump.
And the below comes from the shell emulator of my xubuntu.

dotker@dotker:~$ ls -lh geo2.sql
-rw-rw-r-- 1 dotker dotker 24  9월 20 18:56 geo2.sql
dotker@dotker:~$ mysql -u root -h localhost -p geo < geo2.sql
Enter password: 
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'Terminal close' at line 1

In order to restore geo2.sql as geo, I type “~$ mysql -u root -h localhost -p geo < geo2.sql”
but it produces ERROR 1604.
What is wrong in the code above?

Apparently your file geo2.sql contains the text Terminal close instead of actual SQL …

What does “the text Terminal close” mean?

I made geo2.php with the shell script below.

$ mysqldump -u root -h localhost -p geo > geo2.sql

What’s wrong with the shell script above?
How can I make “actual SQL”?

That seems correct. Is the output correct when you remove > geo2.sql ?

Thread continues here: Move a database from a linux to another linux 2