How to copy MySQL database from one computer / server on the other?

How to copy MySQL database from one computer / server on the other?

The short answer, you can copy the database from one computer / server to another using ssh or mysql client.

You can run all above 3 teams in a single pass using mysqldump and mysql commands (unsafe way, use only if you use a VPN network or task):
mysqldump $ db-name | mysql-h remote.box.com DB-name

Using ssh, if you do not have direct access to the remote server mysql (safest method):
mysqldump $ db-name | ssh user@remote.box.com mysql database name

You can simply copy the table named foo in the remote database (mysql and remote server remote.box.com) called the bar through the same syntax:
$ mysqldump DB-name foo | ssh user@remote.box.com mysql bar

This will not only save time, but you can impress your friend too;). Almost all commands can be executed using pipes under UNIX / Linux oses.


for easy way, create the batch file, for windows like the following:
cd \ your_mysql_bin_folder
mysqladmin-u root - password = create test_backup
mysqldump u root - password = test | mysql-u root - password = test_back

MySQLDump

How to duplicate/backup data between database both in one server or other server ?
Answer:
- Make sure that you have mysqladmin and mysqldump
- Do the Following script:
#Create the database on the destination server
mysqladmin -u YourUserDBDest -h YourDestServer --password=YourPasswDBDest create YourDBDest
#The Real Job Goes Here
mysqldump -u YourUserDBSource -h YourSourceServer --password=YourPasswDBsource YourSourceDB| mysql -u YourUserDBDest -h YourDestServer --password=YourPassDBDest YourDBDest


Here is an example (I use EasyPHP 2.0b1 for win) that I bundled in batch file:

cd\Program Files\EasyPHP 2.0b1\mysql\bin\
mysqladmin -u root -h Server2 --password=secret2 create db2
mysqldump -u root -h Server1 --password=secret1 db1 | mysql -u root -h Server2 --password=secret2 db2

MySQL Boolean/Integer Result Field

In mysql if we make a query like this:

select 1<0 ->the result would be 0 (incorrect because 1 is not less then 0)
select 1>0 ->the result would be 1 (correct because 1 is not more then 0)

it's mean that : 0=False, 1=True

Getting Up Level

Getting Up Level

Leveling Database structure, is maight be not awkward for many programmer. I got problem when getting the up/upper level of some code. At last I found this way :


Suppose you have a data structure like this:

code varchar(10)
name varchar(100)
parent varchar(10)
level integer

code name parent level
------------------------------------
A1 AOne 1
A10 AOnezero A1 2
A11 AOneOne A1 2
A110 AOneOnezero A11 3


what is the up level 1 code of A110?

answer:
make the sql like this:
select * from table where level=1 and 'A110' like concat(code,'%')