moving database to new server
Hello, list -- Quick (and simple?) question: I've been backing my databases up with a perl tool mysqlhotcopy. It seems to leave me a bunch of files in a folder I specified. What is the best way to move this database function to a different machine? There are no constraints. I can take it all down, whatever. Copy the files from the database, or use the 'hot' copies? Any takers? Cheers, -- Michael Higgins [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
basic architecture review?
Hello, list -- No problem, yet. ;-) Wondering if anyone would have a suggestion to ensure better performance, or could point out any likely errors in the database outlined below. Basically, I have digital pictures, scanned text/forms and emails that all relate to information indexed in a separate DB with shipment_id. I don't have any real experience with DB design, so any suggestions or things to consider would be appreciated. My thinking is that I create an overview with an id and store that id in the other tables so I can get all related documents. (My next question will be how to query the lot in a single statement...) All the tables are ENGINE=MyISAM and DEFAULT CHARSET=utf8. I don't know squat about configuration parameters but an error in putting up the images led me to change this line in my.cnf: max_allowed_packet = 4M ... because I don't know how to put up a binary in chunks, I guess. (I'm using DBD::mysql and CGI in perl and inserting the uploaded file with a placeholder in my SQL...) Thanks in advance for any helpful suggestions, corrections or clarifications. ;-) Cheers, Michael Higgins # db info ### +--+ | Tables_in_claims | +--+ | carrdocs | | claimsubs| | emails | | overview | | pictures | +--+ mysql describe carrdocs; +---++--+-+-++ | Field | Type | Null | Key | Default | Extra | +---++--+-+-++ | cardoc_id | int(11)| NO | PRI | NULL| auto_increment | | claim_id | int(11)| NO | | || | carr_doc | mediumblob | YES | | NULL|| | carr_doctype | tinytext | YES | | NULL|| | carr_mimetype | tinytext | YES | | NULL|| +---++--+-+-++ 5 rows in set (0.13 sec) mysql describe claimsubs; +--++--+-+-++ | Field| Type | Null | Key | Default | Extra | +--++--+-+-++ | claimsub_id | int(11)| NO | PRI | NULL| auto_increment | | claim_id | int(11)| NO | | || | claim_doc| mediumblob | YES | | NULL|| | clm_doctype | tinytext | YES | | NULL|| | clm_mimetype | tinytext | YES | | NULL|| | clmdoc_name | tinytext | YES | | NULL|| +--++--+-+-++ 6 rows in set (0.01 sec) mysql describe emails; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | email_id | int(11) | NO | PRI | NULL| auto_increment | | claim_id | int(11) | NO | | || | email| text| YES | | NULL|| +--+-+--+-+-++ 3 rows in set (0.00 sec) mysql describe overview; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | claim_id| int(11) | NO | PRI | NULL| auto_increment | | shipment_id | int(11) | NO | UNI | || | claimant| varchar(60) | YES | | NULL|| | clmnt_email | varchar(60) | YES | | NULL|| | claim_rep | varchar(60) | YES | | NULL|| | rep_email | varchar(60) | YES | | NULL|| | carr_clm_no | varchar(30) | YES | | NULL|| | pro_number | varchar(30) | YES | | NULL|| | carrier | varchar(60) | YES | | NULL|| | claim_amt | varchar(10) | YES | | NULL|| | claim_notes | text| YES | | NULL|| +-+-+--+-+-++ 11 rows in set (0.00 sec) mysql describe pictures; +--++--+-+-++ | Field| Type | Null | Key | Default | Extra | +--++--+-+-++ | image_id | int(11)| NO | PRI | NULL| auto_increment | | claim_id | int(11)| NO | | || | image_note | text | YES | | NULL|| | image| mediumblob | YES | | NULL|| | img_mimetype | tinytext | YES | | NULL
RE: basic architecture review?
-Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Michael, this looks pretty decent overall. I'm a big fan of fully descriptive table and column names, [8] (All good suggestions, thanks.) One performance suggestion: add an index on each table for the claim_id column. This will greatly speed retrieval of material related to a given claim/overview. ALTER TABLE x ADD INDEX claim_id_idx (claim_id) Okay, this was exactly the kind of question I had. So, if I do that, then mysql will just access that INDEX information internally? IOW, I don't actually query on that field, or ever have to think about it again, right? When you say query the lot, what do you mean? Get all related stuff in a single SQL statement? Possible, but maybe a bit messy, and not as easy to maintain as a handful of routines that each get documents, emails, pictures. As you add more tables holding related material the SQL would become unwieldy and you'd likely break it down later anyway. Yeah, I get that... but what I'm looking for is to select (all non-blob fields) from (all the tables) where claim_id = x . [what do I do here? some kind of a 'join'?] This way, I'd be able to get access to each record associated with that claim_id from one, say, webpage. Like, having retrieved an image_id from the monolithic query, I could then retrieve the associated image blob with another query. Anyway, I'm sure this will all become clearer to me eventually... ;-) Thanks a bunch, Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: advice for blob tables?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] I don't feel the implementation direction this article takes is good. It uses single row binary storage, which anyone who has had to deal with large files knows is a definate issue. Just wanted to thank you all for your input thus far. I'm proceeding with caution, but with heightened confidence that I'll be moving forward with a bit of a clue. Cheers, -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
advice for blob tables?
Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue Thanks! -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
