SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 1999
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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."

  2. #2
    SitePoint Member
    Join Date
    May 2000
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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).]

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 1999
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I don't have shell access. What can I do? I can use telnet and phpMyAdmin.

    Please help me!

  5. #5
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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).]

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 1999
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  7. #7
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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).]

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 1999
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).]

  9. #9
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SitePoint Enthusiast
    Join Date
    Dec 1999
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).]

  11. #11
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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).]

  12. #12
    SitePoint Enthusiast
    Join Date
    Dec 1999
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).]

  13. #13
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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).]


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •