How do I upload/download a populated MySQL database

[audiopro]

I have seen MySQLAdmin for local operations but I now want to put the whole database onto a remote server.
What admin tools should I use?


Keith

http://www.studiosoft.co.uk

 

[KarveR]

when you say put it on a remote server, sychronised, or just move it ?

for removal, see mysqldump --complete-insert

to then load, from a mysql prompt type ?

you should use \. '/path/to/dumped_file.sql'

To keep synchronised, check out replication on

http://www.mysql.com

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[audiopro]

I have set up the database on my local machine and now want to copy it onto a remote server. I am new to this so please bear with me. I could create a new version of the database on the remote server, transfer the data across via a text file and the job would be done but I am sure there must be a more elegant approach. The same applies to backing up the Database to our other servers. None of these backups need to be synchronised so should be a straight forward job.
What are the various approaches or is there an excellent tool for the task?


Keith

http://www.studiosoft.co.uk

 

[vacunita]

If they don't need to be synchronized, then mysqldump, should suffice. You a get a nice portable text file, that you can take anywhere. When used appropriately it will recreate the database, and the tables, with all there contents and relationships, in one sigle step. What's more elegant than that?

Besides you can keep a backup copy on a CD or DVD, for emergency use, you can also maintain several states of the DB, say by date, or by size. and they can be reinsterted into the db by issuing a single command.

Very helpfull in case of disaster.






----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[audiopro]

Thanks for that.
I will look into MySQLDump .


Keith

http://www.studiosoft.co.uk

 

[audiopro]

Can anyone point me in the direction of a good tutorial which deals with useage on a remote server. The tutorials I have read all assume that you have SQL command line access.

Keith

http://www.studiosoft.co.uk

 

[KarveR]

What access do you have, if you don't have command line?

phpMyAdmin can take a mysql dump but its sloooooow.



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[audiopro]

I have a command line on my local server but only FTP access to the remote, hosted server.


Keith

http://www.studiosoft.co.uk

 

[KarveR]

How do your hosts envisage you managing mysql via only FTP ? do they not provide any tools?

Can you set up mysqladmin to use your remote server?

What OS/s do you have at your disposal?

Is php available on the remote server?

.....

more info really, anything and everything that may be useful.

If you FTP the dump file, and you can get a remote mysql session (linux : mysql -h remote_host -u user -p)
you should have no problems but without knowing exactly what you have to use, answers are not forthcoming.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[vacunita]

FTP, access will only help you upload the file to the server, but you need some kind of acces where you can interact with the DB. Usually hosts provide web based DB managers from the control Panel of the site, to access and maipulate the db.

You can use that to pass the dumped file, or as KarveR pointed out, if you can connect to the remote db using MYSQLAdmin, you can the pass the dumped file through there.

PHP might also work, but will take some coding. Not too much mind you but some.

However the best would be if you can ask for telnet access to the server, so you can issue the command directly.

So what access do you have? I'm leaning towards the web based db admin scenario, sionce its the most widely available in most hosts.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[audiopro]

hanks for all that. I am setting this up for a third party on their remote server so communication is long winded and painful. I have a very rough DB creation / backup program which I wrote to reinstall the DB and tables on my local server when my early experiments went mammaries up, I am leaning towards developing that (not a lot of additional work) to allow me to manipulate the DB. I thought there would be a specific tool for the purpose but having enquired with a few providers, it seems they each offer a different admin tool. My current project is likely to be installed on numerous remote servers so I think writing a universal handling tool now, will save me some considerable time in the future.
There are also a few other issues with the present ISP and the site may be moved so I think it is time to dive in and do it.
Out of interest, I assume that MySQL is a cut down version of SQL Server, what has been omitted?


Keith

http://www.studiosoft.co.uk

 

[KarveR]

mySQL is its own server, and as such has a unique set of commands and abilities. For the best answer see

http://www.mysql.com.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TonyGroves]

MySQL is totally unrelated to SQL Server. Just because SQL Server is produced by Microsoft doesn't mean it's the product all the others look up to! There were plenty of relational databases around before Microsoft got in on the act.

 

[vacunita]

If its likely to need installation in several remote servers, your best bet then is to create a server side app, most likely in PHP, that takes in the dumped file from your server, and parses it an runs all the commands. it should not be to hard to code since every sql command needed is already in the dumped file. and its plain text.

As for your other question, Karver, and Tony, have answered clearly the issue. Mysql has nothing to do with Microsft's SQL. ITs its own product developed by a totally different company, that has nothing to do with Microsoft, and I think is in most respects leaps ahead of Microsofts SQL server. but that's just me.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[lgarner]

The only things that MySQL lacks which have been apparent to me are stored procedures and triggers. Also a job scheduler. The current beta version addresses some of these if you need those features.

We use MySQL in a production environmet, but I wouldn't equate it with MS SQL Server, Oracle or DB2. For price and simplicity MySQL is very good. For something that bridges the gap between it and commercial databases, you could look at Postgresql.

 

[audiopro]

Thanks guys.
I guess I am just seeking the light with regard to web databases in general. I didn't want to waste time writng an interface only to find out there is a comprehensive method out there.
I have been using Foxpro for many years and that is great for machine or network based apps. but doesn't web very well.
I have a number of apps. which interface between Foxpro on the local machine and Perl on the server and this works very well indeed. I have written numerous apps. using flat files as the amount of data involved is small, can be loaded into arrays and manipulated simply.
My latest projects require the chosen medium to work with large amounts of data( well large compared with what I have been used to ).
I have been impressed with the simplicity of SQL queries within MySQL but wondered if I am using the best tool for the job. The one slight reservation I have about MySQL is its ability to manipulate dates. I feel I am missing some vital information regarding the MySQL DATETIME fields and searches for examples have proved inconclusive. I thought there would be a set of inbuilt functions for this purpose as it is 'bread and butter' DB manipulation. I have long used epoch seconds as a reliable method of working with dates but this too has its drawbacks. Comparisons are very simple but converting to an actual date can get a bit messy. I have written functions to take care of some of the common tasks but some of these are horrendously long winded.
I have been ridiculed (all very friendly) in various forums regarding my insistence on using the epoch method but until I find a better method...............



Keith

http://www.studiosoft.co.uk

 

[KarveR]

just use real dates, they're far more flexible.

MySQL date and time functions

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.