MySQL Backup

[clizon]

Hi there,

What is the easiest way to back up you entire MySQL database?

Say I wanted to replicate my site on my local pc. How do I download the mySQL ?

Everything else is easy.. that part I'm not sure about.

Thanks,

[Fred Drouven]

I use the export tables function from MySQL-Front (http://mysqlfront.venturemedia.de/) (See the download url in the rightupper corner of this page)

I backup all the data to my local database. You can setup two connections at the same time (1 your powweb database, 2 your local database) and than export from one db to the other. You can also upload tables with data this way.

[robin_ge]

Originally posted by Fred Drouven
I use the export tables function from MySQL-Front (http://mysqlfront.venturemedia.de/) (See the download url in the rightupper corner of this page)

I backup all the data to my local database. You can setup two connections at the same time (1 your powweb database, 2 your local database) and than export from one db to the other. You can also upload tables with data this way.

Hi! Fred, I'm using MySQL-Front and I don't know how to setup a local database. Could you give me a hand? Thanks!

[Fred Drouven]

Make a connection to localhost / user root (no password) and then use create database (the icon right to the printer icon)

If this is the firsttime you use your mysql database make sure that you create user(s) with passwords and rights.

btw you can't do that on powweb. If you want a database on powweb you'll have to do that in ops and pay the $10.-

[clizon]

Can you transfer the database from one host to another that way as well??


thanks,

[KimmiKat]

clizon,

The easiest way to transfer your database using the mySQL-front proggy is saving it as a Gzip file and reuploading the same file to your new host. I did that and it was pretty much painless.

[robin_ge]

Originally posted by Fred Drouven
Make a connection to localhost / user root (no password) and then use create database (the icon right to the printer icon)

If this is the firsttime you use your mysql database make sure that you create user(s) with passwords and rights.

btw you can't do that on powweb. If you want a database on powweb you'll have to do that in ops and pay the $10.-

Do I need anything extra on my machine? I mean when I connected using the way you said and it gives me an error says can't connect to MySQL server on 'localhost'. So do I need some SQL software to support this connection? Thanks a lot!

[robin_ge]

Originally posted by clizon
Can you transfer the database from one host to another that way as well??


thanks,

I saw an option on MySQL-Front that just does that.

[robin_ge]

Originally posted by KimmiKat
clizon,

The easiest way to transfer your database using the mySQL-front proggy is saving it as a Gzip file and reuploading the same file to your new host. I did that and it was pretty much painless.

Hi! KimmiKat! Can you do that when you just want to import the ".sql" database file to the same database?(i.e. For example, my vBulletin database is broken and I want to use my ".sql" backup file to recover the database in vBulletin) Thanks!

[HalfaBee]

You can't use 'localhost' in mysql-front.
Use your mysqlserver.powweb.com .

To restore your db using mysql-front
click on thye Query tab and press the Load SQL from file button.

Not sure if this works with big sql files.

The other way is to upload your SQL to the server and use mysql

/usr/local/bin/mysql -hhostname -uusername -ppassword db < backup.sql

You can execute this in a little php script.

<? system( '/usr/local/bin/mysql -hhostname -uusername -ppassword db < backup.sql' ) ?>

Just upload it as restore.php and run from the browser.

HalfaBee

[KimmiKat]

robin_ge,

You are correct, it is .sql. My brain must've hit it's "max_questions" quota, which is 1....I need have coffee first before posting anything.:D

[robin_ge]

Originally posted by HalfaBee
You can't use 'localhost' in mysql-front.
Use your mysqlserver.powweb.com .

To restore your db using mysql-front
click on thye Query tab and press the Load SQL from file button.

Not sure if this works with big sql files.

The other way is to upload your SQL to the server and use mysql

/usr/local/bin/mysql -hhostname -uusername -ppassword db < backup.sql

You can execute this in a little php script.

<? system( '/usr/local/bin/mysql -hhostname -uusername -ppassword db < backup.sql' ) ?>

Just upload it as restore.php and run from the browser.

HalfaBee

Thank you! HalfaBee! Just one thing that I want to confirm on, where do I upload my ".sql" to the server? The ftp server or the sql database. If it is ftp, which directory and if it is sql database, how do I do that? Thanks a lot!!!! Really appreciated!

[robin_ge]

Originally posted by KimmiKat
robin_ge,

You are correct, it is .sql. My brain must've hit it's "max_questions" quota, which is 1....I need have coffee first before posting anything.:D

:p :p

[HalfaBee]

Upload the SQL using FTP to the same dir. you upload the PHP script to.

If you want to zip the sql using winzip you can.
Make the script look like this.

<?
echo "<pre>";
unlink( 'backup.sql' ); // deletes old backup.sql file
system( '/usr/local/bin/unzip backup.zip' ); // extracts new backup.sql file
system( '/usr/local/bin/mysql -hhostname -uusername -ppassword db < backup.sql' );
?>




If you want to dump the DB on the server use

<?
system( '/usr/local/bin/mysqldump --opt -hhostname -uusername -ppassword -r backup.sql db' );
?>

HalfaBee

[danpadams]

How will this method of importing via the command line affect the number of queries allowed, I am assuming that you guys have implemented this tool before?

<? system( '/usr/local/bin/mysql -hhostname -uusername -ppassword db < backup.sql' ) ?>

I am partially concerned because I am a potential Powweb customer and I looked into the .sql file that is produced by mysqlfront and it was full of sql statements. My database that I am interested in using is 6599 records large in 13 tables. I tired expierementing with the mySQL Front database export to another database feature and it seemed to use many different sql statements to import the data, how is this method different?

Dan

[KimmiKat]

robin_ge,

Databases are fun! I had to increase my brain qouta to 4 so I could disect a database backup. :D

When I downloaded my database, I needed to seperate the 3 users into seperate files. I had 3 mboards (each used a different user name running off the same database)and one was for a Homeowner's Association which was temporary located on my domain. I was able to extract the tables for thier mboard and upload just their tables (not all the tables in the database) to their database on their own server. :)

[HalfaBee]

Originally posted by danpadams
How will this method of importing via the command line affect the number of queries allowed, I am assuming that you guys have implemented this tool before?

<? system( '/usr/local/bin/mysql -hhostname -uusername -ppassword db < backup.sql' ) ?>

I am partially concerned because I am a potential Powweb customer and I looked into the .sql file that is produced by mysqlfront and it was full of sql statements. My database that I am interested in using is 6599 records large in 13 tables. I tired expierementing with the mySQL Front database export to another database feature and it seemed to use many different sql statements to import the data, how is this method different?

Dan

This does not affect the quota as all the statements to restore the db are inserts and these are not counted.
You will use 1 connection :)

I am not sure about the mysqldump, I dumped my little DB about 1MB of SQL and it worked fine.


HalfaBee

[danpadams]

Thanks Halfabee,

I wasn't sure of what type of queries are concerned. Do you know if there is a list of the applicable types that count?

Insert?
Create?
Update?
Select?

I am just curious partially because I would be encouraged if I could get a lot of traffic to my db which will be related to church information. If anyone wants, and possibly has a suggestion it is at http://www.infoChi.com and click on concordance. You can e-mail me from that site.

Thanks for the help, (once again). I may also end up referring other people to use powweb.com's services.

Dan

[robin_ge]

Originally posted by HalfaBee
Upload the SQL using FTP to the same dir. you upload the PHP script to.

If you want to zip the sql using winzip you can.
Make the script look like this.

<?
echo "<pre>";
unlink( 'backup.sql' ); // deletes old backup.sql file
system( '/usr/local/bin/unzip backup.zip' ); // extracts new backup.sql file
system( '/usr/local/bin/mysql -hhostname -uusername -ppassword db < backup.sql' );
?>




If you want to dump the DB on the server use

<?
system( '/usr/local/bin/mysqldump --opt -hhostname -uusername -ppassword -r backup.sql db' );
?>

HalfaBee

Thanks so much! HalfaBee!

[robin_ge]

Originally posted by KimmiKat
robin_ge,

Databases are fun! I had to increase my brain qouta to 4 so I could disect a database backup. :D

When I downloaded my database, I needed to seperate the 3 users into seperate files. I had 3 mboards (each used a different user name running off the same database)and one was for a Homeowner's Association which was temporary located on my domain. I was able to extract the tables for thier mboard and upload just their tables (not all the tables in the database) to their database on their own server. :)

Cool! I didn't know you can use the same database for differrent discussion boards.

[KimmiKat]

robin_ge

I didn't know either til I read a post on InvisionBoard help forum. One user mentioned that he had 6 mboards running off one database using 6 users (He had a dedicated server). So I know InvisionBoard allows that, not sure about the others.

When I installed the mboard each time, it asked if I wanted to use a prefix, and I did for each board, something ibf1, ibf2, etc. You can use anything. It made it easier to extract the tables using mySQL-front. So the 3 mboards I set up now each run on their own server. One for the HOA and one each for 2 of my domains.