11
tedsmith
Re: How to restore .sql backup files?
  • 2005/4/23 13:21

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


Hi again guys.

Thanks for your replies. I'm at home now, but like a saddo am trying to work out what the problmes are.

I think I may have made some good headway. I've established that what may have happended perhaps is related to the character encoding? I'm currently using the 'MySQL Administrator' application at home which has a Backup and Restore functionality, and I have just tried restoring a backup made today of my own personnal website using phpMyAdmin. I get the same problem restoring that as I do with the backup referred to above.

Apparently, MySql Administrator created backups using the UTF-8 character set. When you select the 'Auto-Detect Character Set' it says that it 'Could not detect the character set of this file'. I'm assuming this is why it's unable to restore either of them.

So,

a) What character set does phpMyAdmin (and the XOOPS Backup module) export SQL files as by default?

If I can find this out I can select the appropriate CS from the list and hopefully it will then restore.

BTW - there are not tables in the current database I am trying to restore to - it's just a blank new database with the same name as the one I am trying to restore.

Keep it coming dudes!

12
jdseymour
Re: How to restore .sql backup files?

I believe I see the problem here is a sample table dump of mine:

-- phpMyAdmin SQL Dump
-- version 2.6.1-pl2
-- http://www.phpmyadmin.net
-- 
-- 
Host209.xx.xxx.xx
-- Generation TimeApr 222005 at 12:33 PM
-- Server version4.0.23
-- PHP Version4.3.10
-- 
-- 
Database: `xxxxxxxx_xoops`
-- 

-- --------------------------------------------------------

-- 
-- 
Table structure for table `xxxxps_shortcuts`
-- 

DROP TABLE IF EXISTS `xxxxps_shortcuts`;
CREATE TABLE `xxxxxps_shortcuts` (
  `
shortcutidmediumint(8unsigned NOT NULL auto_increment,
  `
uidmediumint(8unsigned NOT NULL default '0',
  `
dateint(10unsigned NOT NULL default '0',
  `
titlevarchar(255NOT NULL default '',
  `
urlvarchar(255NOT NULL default '',
  `
hitsint(8unsigned NOT NULL default '0',
  `
ratingtinyint(4NOT NULL default '0',
  
PRIMARY KEY  (`shortcutid`),
  
KEY `uid` (`uid`),
  
KEY `title` (`title`)
TYPE=MyISAM AUTO_INCREMENT=;

-- 
-- 
Dumping data for table `xxxxps_shortcuts`
-- 

INSERT INTO `xxxxps_shortcutsVALUES (211105559467'Site Statistics Referer''/modules/statistics/referdetail.php'3810);
INSERT INTO `xxxxps_shortcutsVALUES (3471108942374'fgfdfds''/modules/xhld1/'010);
INSERT INTO `xxxxps_shortcutsVALUES (421109216043'Discussion Board''/modules/newbb/'10);
INSERT INTO `xxxxps_shortcutsVALUES (51991112886087': Technology : Security : and''/search.php'00);


Notice the difference in this one to yours? In this one notice the single quotes, in your example no single quotes exist. Ignore the ADD DROP Table is exist, that is just if the table exist already and I want to replace the info in the existing table.

Check with the single quotes as above and see if you still get errors.

13
Dave_L
Re: How to restore .sql backup files?
  • 2005/4/23 14:25

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


The backticks (``) around table and column names are optional, and are only needed if the names contain embedded spaces (or possibly other special characters).

14
tedsmith
Re: How to restore .sql backup files?
  • 2005/4/23 15:47

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


Thanks Dave_L, but I've got it sorted (I hope) and I came back hear to tell everyone and in the meantime you had posted more stuff.

Turns out that the phpMyAdmin was in latin1 character encoding. MySQL Administrator restores (by default) using character code UTF-8. When I told it to use latin1 it resotred the backups, in both instances (my work one and my personnal one).

So I now have a folder full of a database which I assume I can now take to work on Monday, copy to the c:\mysql\data\cms folder (cms is the name of the database) and it will presumebly detect it? If I then launch my browser and navigate to our Intranet page it should work in theory (providing all the permissions etc are OK)? Either way, I'm nearly there!

15
tedsmith
Re: How to restore .sql backup files?
  • 2005/4/25 11:23

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


IT WORKED!!!!!!!!!!

I copied the content of the restore to the database folder in the mysql\data folder, launched IE, and hey presto.]#
Thanks 4 the help

Login

Who's Online

432 user(s) are online (140 user(s) are browsing Support Forums)


Members: 0


Guests: 432


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits