Topic awaiting preservation: Exporting Database with MYSQL Administrator

Im using mysql administrator and i want to know how export my database so it could be uploaded on a server. i tried the backup procedure but that just gave me a sql file that looks like a test file with an sql extention on it. is that right or is there another way to do it. basically how do i get it online in the right format.

you dont have access to php myadmin do you? i'm assuming mysql administrator is different?

anyhow, an sql file is the format you'd usually export an sql database in.

I thought he was trying to export the sql from an existing database? And then load it to a database on another server?

Yeah - .sql is still what you want for that.

I don't know what mysql administrator is, but I would have to assume it is at least similar to phpmyadmin. You will need to make sure that you specify the right options when exporting - include the data and not just the structure.

If in doubt as to whether it saved the right info.....upload it as a new database and take a look

i am using http://www.mysql.com/products/tools/administrator/ and when i choose the backup option which i assume is to export the database it spits out what looks like a plain onld text file with an sql extension. when i open it it opens in the plain old text editor. is that suppose to be that way or am i making a mistake. thanx for the help

Hustluz is talking about mysqladmin, a stanadard program that comes with MySQL. There is a man page for it on Linux. Just type 'man mysqladmin' to find out how to use it.

To transfer the database from one server to another, you want to use 'mysqdump'. As DL-44 said, look at 'man mysqldump' to figure out which options to use to get a dump of your database.

.



-- not necessarily stoned... just beautiful.

quote:

---

Hustluz said:

it spits out what looks like a plain onld text file with an sql extension. when i open it it opens in the plain old text editor.

---

Well, until you put it into cotnext, that's all it is - plain old text, like any other file format.

I can only reiterate - if in doubt, uploade the file as a new DB and verify that it worked correctly. There's no way to know for sure short of that.

Well you can just look at the sql file in a text editor and you should be able to tell if it worked even before you upload it

quote:

---

Well you can just look at the sql file in a text editor and you should be able to tell if it worked even before you upload it

---

That's easy if you know SQL... If you don't it's a lot of goobledigook...

Mysqldump, "export" in phpmyadmin, backup in mysql administrator should basically do the same thing.
Create a textfile with content somewhat like:

CREATE TABLE....
INSERT...

This textfile now holds a lot of SQL queries that you can run in a new database on any mysql server, the same way as you run a simple SELECT * FROM persons WHERE name LIKE "Dan".

The difference is that this backup-sql-file will actually create the tables for you in the new database and then insert the old content into these tables. Thus restoring the database and it's tables and contents on a new server.

So all in all, to backup:
go to MySQL Administrator, connect to your server.
Click backup, create new project, name it, select database, check all tables, go to advanced, check Normal backup, Backup shema completely, Add drop table, complete inserts.

Click Start Backup.
Choose where to save it, and go.
Done.

To restore:
go to MySQL Administrator, connect to your server.
Click restore
Choose file (take the sql file you created in the backup step)
Check create schemas if necessary
Click restore backup
Done...

Not rocket science exactly, but tricky the first time if you don't know how sql works.

Cheers/Dan

{cell 260} {Blog}
-{"Theories without facts are just religions...?}-

quote:

---

H][RO said:

Well you can just look at the sql file in a text editor and you should be able to tell if it worked even before you upload it

---

Even if, as DMS mentioned, you are ery familiar with SQL, it seems it would be quicker and easier, and it would *definitely* be more reliable, to simply upload it again and look at the results rather than scrolling through a big garbled text file.

Obviously this is not a necessary step in a daily back-up routine or anything of that nature, but if you are trying to figure out how to back up and restore your database, and you're not sure if you're doing it right, I'd go with the safe bet of trying it out

Well if you don't know sql then yeh its easier, scrolling through the sql file can be quicker before you upload it to make sure it worked, then just do a final check check of the size/row count of each table and make sure they are the same.


Each to their own i guess


I would hope if you are dealing with databases at all you at least know what sql looks like.