How to backup and restore mySQL database?

[usbworkshop]

Hi, I know we can administer a mySQL database via a PHP3 program called phpmyAdmin. However, I can backup those database but I am having problems RESTORING them on another host.

When I tried to paste a query, I get an error like this.

MySQL said: You have an error in your SQL syntax near 's PhotoGenie image enhancement technology, and are instantly ready to print out,' at line 1

The paragraph inside the query is like this:
"The ePhoto CL30 digital camera is a high-performing, easy-to-use mega-pixel digital camera that expands the range of camera choices by offering high-end features, including a USB (Universal Serial Bus) interface. With the CL30, your images have a maximum resolution of 1440 x 1080 pixels with Agfa's PhotoGenie image enhancement technology, and are instantly ready to print out, send through e-mail or simply view on your television using the supplied video cable. Today's digital photography is extremely user-friendly, immediate and very versatile?and unlike conventional photography, only the pictures you want are stored and/or printed. With the CL30's QuickReview feature, you have the option of sharing or deleting your images in record mode before they are stored on the memory card."

[Kevin Yank]

There's a much easier way to backup and restore a MySQL database than doing it through PHP. Assuming you have shell access on the host that runs the MySQL server, you should be able to do the following:

mysqldump your_db > dump.sql

The mysqldump program will create a text file called dump.sql containing all the SQL statements required to create the tables and insert the data that is in the database you specify ('your_db').

Then to import the dump.sql file on the other host, you just need to do the following:

mysqladmin create your_db
mysql your_db < dump.sql

That's all there is to it. Alternatively, if both machines are on the same network, you can directly copy the database by issuing the following commands from the source host:

mysqladmin -h the.target.machine create your_db

mysqldump your_db | mysql -h the.target.machine your_db

You'll probably need to specify your MySQL username and password for each of the above commands with the -u and -p commandline switches. Type the commands with the --help argument for more information.

These tricks come from Chapter 11 of "MySQL" from New Riders publising, a review of which is currently featured on http://www.WebMasterbase.com/.

Let me know if you have any questions!

------------------
-Kevin Yank.
http://www.SitePoint.com/
Helping Small Business Grow Online!


[This message has been edited by kyank (edited May 19, 2000).]

[insomniac]

usbworkshop,

I'm far from an expert with MySQL, but it looks like your problem is with the ' character in the text. As far as I know, PhpmyAdmin does not filter unusual characters like ' or < > etc. I'm not sure if you can get around this in phpmyAdmin, but I also use phpmyAdmin and what I did was create a script which will insert or modify a database field from my input. The thing to remember is MySQL doesn't like weird characters, so a code such as:

$homepage = addslashes($homepage);

must be used around the begining of the script before adding something to the database . This code will check the variable $homepage, and see if there are any weird characters (such as '). If there are, it will replace them with a slash (/), and finaly return them back to normal when viewed from the database.

Andrew.

[usbworkshop]

Sorry, I don't have shell access. What can I do? I can use telnet and phpMyAdmin.

Please help me!

[Kevin Yank]

usb,

Telnet access is shell access...

However, now that I think of it, you don't need shell access. You can download the package of client programs for Windows (including mysqladmin, mysqldump, and mysql that I used above) from http://www.mysql.com/ and just run them on your local machine. You'll just have to use the "-h hostname" argument when invoking the programs to tell them which host to connect to to perform the operations.

The exact file you need to download is: http://www.mysql.com/Downloads/Win32...ts-3.23.14.zip

So repeating the commands in my previous post, adjusted so that you can run them on your local machine:

To backup a database:

mysqldump -h source.host.name -u user -p your_db > dump.sql


To restore a database:

mysqladmin -h target.host.name -u user -p create your_db

mysql -h target.host.name -u user -p < dump.sql


To perform a direct copy:

mysqladmin -h target.host.name -u user -p create your_db

mysqldump -h source.host.name -u user -p your_db | mysql -h target.host.name -u user -p your_db


Looks intimidating, but it should work just fine.

------------------
-Kevin Yank.
http://www.SitePoint.com/
Helping Small Business Grow Online!

[This message has been edited by kyank (edited May 19, 2000).]

[This message has been edited by kyank (edited May 19, 2000).]

[usbworkshop]

Thanks! Can you tell where to put in the "servername" , "host" and "login"?

Actually I don't really know how to set up the MySqlManager.exe

Thanks for everything...

[Kevin Yank]

You don't need to set up anything. Having downloaded the programs, you can run them right away from the MS DOS Prompt with the commands I gave you above. Just slot in the hostname and your MySQL username where I've indicated, and provide your MySQL password when prompted.


------------------
-Kevin Yank.
http://www.SitePoint.com/
Helping Small Business Grow Online!

[This message has been edited by kyank (edited May 20, 2000).]

[usbworkshop]

Hi, I typed in the folowing but doesn't work.

mysqldump -h 209.xx.xxx.xx -u us19582a -p db19582a > dump.sql

us19582a is my database username
db19582a is my database name

The program didn't ask me for the password.

[This message has been edited by usbworkshop (edited May 20, 2000).]

[Karl]

Hi,

you have got a space between the -p and your password, there should not be a space.

hope this helps you.

------------------
Karl Austin
KDA Web Services

[usbworkshop]

Hi thanks. But it still doesn't work.
When I typed in mysqldump -h 209.xx.xxx.xxx -u us19582a -pid1678 > dump.sql

209.xxxxxx is my webpage IP address, right?

I got the following in my new dump.sql file.

mysqldump Ver 8.2 Distrib 3.23.14-alpha, for Win95/Win98 (i586)
By Igor Romanenko, Monty, Jani & Sinisa. This software is in public Domain
This software comes with ABSOLUTELY NO WARRANTY

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]

-A, --all-databases Dump all the databases. This will be same as
--databases with all databases selected.
-a, --all Include all MySQL specific create options.
-#, --debug=... Output debug log. Often this is 'd:t ,filename`.
-?, --help Display this help message and exit.
-B, --databases To dump several databases. Note the difference in
usage; In this case no tables are given. All name
arguments are regarded as databasenames.
'USE db_name;' will be included in the output
-c, --complete-insert Use complete insert statements.
-C, --compress Use compression in server/client protocol.
-e, --extended-insert Allows utilization of the new, much faster
INSERT syntax.
--add-drop-table Add a 'drop table' before each create.
--add-locks Add locks around insert statements.
--allow-keywords Allow creation of column names that are keywords.
--delayed-insert Insert rows with INSERT DELAYED.
-F --flush-logs Flush logs file in server before starting dump.
-f, --force Continue even if we get an sql-error.
-h, --host=... Connect to host.
-l, --lock-tables Lock all tables for read.
-n, --no-create-db 'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;'
will not be put in the output. The above line will
be added otherwise, if --databases or
--all-databases option was given.
-t, --no-create-info Don't write table creation info.

[This message has been edited by usbworkshop (edited May 20, 2000).]

[Kevin Yank]

usb,

You forgot to put the name of the database you want to dump (normall you'd only dump one database at a time):

mysqldump -h source.host.name -u user -ppassword your_db > dump.sql

If you want to dump all the databases stored on the server, use the -A option:

mysqldump -h source.host.name -u user -ppassword -A > dump.sql


------------------
-Kevin Yank.
http://www.SitePoint.com/
Helping Small Business Grow Online!

[This message has been edited by kyank (edited May 21, 2000).]

[This message has been edited by kyank (edited May 21, 2000).]

[usbworkshop]

YAHOO! I GOT THIS TO WORK! Thank you so much!

What I type in is to restore my database:
mysql -h localhost -u <user> -p<password> <databasename> < dump.sql

The problem I have before is that my new host keeps directing my new site IP address to usbworkshop.com which resides in my current hosting company. So, I figured out I can use localhost because I am already in the server (telnet).

It's good to know there are people like you around.

Now, I can backup and restore my mySQL database without a problem.

BTW, I have asked so many people on the web on backup and restoring mySQL database but people keep telling me to use the query function in phpMyAdmin. Running queries in PHP is slower than dumping mySQL data into the database. And special characters get in the way every time. I am so frustrated until I saw your reply.

Maybe your site should have a article about mySQL backup and restore. That would help a lot of people.

Thank you!

[This message has been edited by usbworkshop (edited May 21, 2000).]

[This message has been edited by usbworkshop (edited May 21, 2000).]

[Kevin Yank]

Careful what you wish for -- one of the articles in the series on PHP and MySQL starting THIS WEEK will be on MySQL database maintenance.

One more thing: if you plan on using mysqldump to keep archives of your database, it would be a good idea to zip up the .sql files produced, since they are just plain text and will compress well.

------------------
-Kevin Yank.
http://www.SitePoint.com/
Helping Small Business Grow Online!

[This message has been edited by kyank (edited May 21, 2000).]