MySQL Dump!

[liljim]

Anyone know of a function (if one exists), which shows the contents of a database (including all of the "CREATE TABLE" syntax)? We're switching servers soon, and I'd like to:

a;) Backup all the data (not through telnet or any existing web-based methods).

b;) Write a script to back up contents of a database regularly. I think I'll probably resort to using the gzip functions to dump the contents of the database somewhere.

To extend this question, anyone ever used "SELECT INTO OUTFILE list_of_columns FROM list_of_tables" (I think that's the correct statement)? I always get a connection-related error. Maybe on the server we're currently using, we don't have the correct write permissions.

[aladinsane]

At 7/5/01 07:11 PM, liljim wrote: Anyone know of a function (if one exists), which shows the contents of a database (including all of the "CREATE TABLE" syntax)? We're switching servers soon, and I'd like to:

a;) Backup all the data (not through telnet or any existing web-based methods).

b;) Write a script to back up contents of a database regularly. I think I'll probably resort to using the gzip functions to dump the contents of the database somewhere.

To extend this question, anyone ever used "SELECT INTO OUTFILE list_of_columns FROM list_of_tables" (I think that's the correct statement)? I always get a connection-related error. Maybe on the server we're currently using, we don't have the correct write permissions.

Do you use PHPMyAdmin? It includes plenty of features for dumping the contents of databases, including all sql statements, and is also invaluable for day-to-day db management.

[Pecos]

At 7/5/01 07:11 PM, liljim wrote: Anyone know of a function (if one exists), which shows the contents of a database (including all of the "CREATE TABLE" syntax)? We're switching servers soon, and I'd like to:

a;) Backup all the data (not through telnet or any existing web-based methods).

b;) Write a script to back up contents of a database regularly. I think I'll probably resort to using the gzip functions to dump the contents of the database somewhere.

That's the problem I've been faced with a lot - I can't find a way to just display all the tables that are in a database. Let me know if you find out.

To extend this question, anyone ever used "SELECT INTO OUTFILE list_of_columns FROM list_of_tables" (I think that's the correct statement)? I always get a connection-related error. Maybe on the server we're currently using, we don't have the correct write permissions.

What you can do is write a script that just outputs the returned data to a system prompt. And if you're at a unix-based command prompt when running the script (script.sh or something), you can do:
prompt> ./script.sh > output_file

And then all your output would be in the output_file
I doubt it if this helps, but hopefully.

[liljim]

At 7/6/01 04:43 AM, aladinsane wrote: Do you use PHPMyAdmin? It includes plenty of features for dumping the contents of databases, including all sql statements, and is also invaluable for day-to-day db management.

Yeah, I use PHPMyAdmin quite regularly, and am aware of the table dump facilities. I'd be a lot happier if I could write something that would back up the data without me even having to think about it though...

[RichL]

At 7/6/01 09:41 AM, liljim wrote:

At 7/6/01 04:43 AM, aladinsane wrote: Do you use PHPMyAdmin? It includes plenty of features for dumping the contents of databases, including all sql statements, and is also invaluable for day-to-day db management.

Yeah, I use PHPMyAdmin quite regularly, and am aware of the table dump facilities. I'd be a lot happier if I could write something that would back up the data without me even having to think about it though...

I know you all hate using scripts from Hotscripts (premade ones) but i would suggest looking there, they have many "auto-backup" of MySQL DB scripts, you could look at the source of them and use parts of it to write your own..im uploading "mambo" to F2S so if anyone wants to see it in action, its at www.testing12345.f2s.com, why am i uploading it? i have no idea

[Pecos]

At 7/6/01 09:41 AM, liljim wrote:

At 7/6/01 04:43 AM, aladinsane wrote: Do you use PHPMyAdmin? It includes plenty of features for dumping the contents of databases, including all sql statements, and is also invaluable for day-to-day db management.

Yeah, I use PHPMyAdmin quite regularly, and am aware of the table dump facilities. I'd be a lot happier if I could write something that would back up the data without me even having to think about it though...

Add your script as a cron job - that way you can schedule it to run every night or something like that.

[liljim]

At 7/6/01 11:17 AM, MassiveBit wrote: Add your script as a cron job - that way you can schedule it to run every night or something like that.

1/. Don't have access to cron jobs until we switch servers.

2/. phpMyAdmin just displays the contents of the database, it doesn't explicitly save it anywhere.

;)

[Pecos]

At 7/6/01 11:32 AM, liljim wrote:

At 7/6/01 11:17 AM, MassiveBit wrote: Add your script as a cron job - that way you can schedule it to run every night or something like that.

1/. Don't have access to cron jobs until we switch servers.

Why not? Doesn't it just come with the system? Anyway, you can just have your script run as a process in the background and execute whatever when it's a certain time.

2/. phpMyAdmin just displays the contents of the database, it doesn't explicitly save it anywhere.

;)

Do you have the source to phpMyAdmin? If so, edit it to dump data out. If not, you're screwed! :o)

[liljim]

At 7/6/01 02:28 PM, MassiveBit wrote:

1/. Don't have access to cron jobs until we switch servers.

Why not? Doesn't it just come with the system?

It comes with the system, but we're currently on a co-located server, and the admin don't trust us with using it.... Which is partly why we're about to lease a RAQ.

Anyway, you can just have your script run as a process in the background and execute whatever when it's a certain time.

I could if I knew what you meant....

Do you have the source to phpMyAdmin? If so, edit it to dump data out. If not, you're screwed! :o)

I can get details from the tables, together with the table types. I just wondered whether or not there's an easy way around it ;)

[Pecos]

At 7/6/01 02:50 PM, liljim wrote:

Anyway, you can just have your script run as a process in the background and execute whatever when it's a certain time.

I could if I knew what you meant....

Well, I'm guessing that the script would need root access, and you don't have root access anyway.. so nevermind. :(
I'm of no help today.

BTW, on a completely different topic..
I used arrays in Java for the first time today... not willingly of course.... because request.getParameterValues() returns an array. How gay is that!

[Big-Charge]

At 7/6/01 04:46 PM, MassiveBit wrote:

At 7/6/01 02:50 PM, liljim wrote:

Anyway, you can just have your script run as a process in the background and execute whatever when it's a certain time.

I could if I knew what you meant....

Well, I'm guessing that the script would need root access, and you don't have root access anyway.. so nevermind. :(
I'm of no help today.

BTW, on a completely different topic..
I used arrays in Java for the first time today... not willingly of course.... because request.getParameterValues() returns an array. How gay is that!

Stop promoting Java man! You're gonna give the Java people and idea and they will make a new version of Java that will burn out CGI and PHP and then the Internet will laggggggggg ass!

[thisisnotmike]

At 7/6/01 11:58 PM, Big_Charge wrote:

At 7/6/01 04:46 PM, MassiveBit wrote:

At 7/6/01 02:50 PM, liljim wrote:

Anyway, you can just have your script run as a process in the background and execute whatever when it's a certain time.

I could if I knew what you meant....

Well, I'm guessing that the script would need root access, and you don't have root access anyway.. so nevermind. :(
I'm of no help today.

BTW, on a completely different topic..
I used arrays in Java for the first time today... not willingly of course.... because request.getParameterValues() returns an array. How gay is that!

Stop promoting Java man! You're gonna give the Java people and idea and they will make a new version of Java that will burn out CGI and PHP and then the Internet will laggggggggg ass!

Java Servlet Pages (Or whatever they're called) don't lag anymore than php or asp does.

[Pecos]

At 7/6/01 11:58 PM, Big_Charge wrote: Stop promoting Java man! You're gonna give the Java people and idea and they will make a new version of Java that will burn out CGI and PHP and then the Internet will laggggggggg ass!

Java is way superior and it does NOT lag.