RE: RAID or not?
-Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Jon Drukman Sent: Thursday, August 21, 2003 9:24 PM To: [EMAIL PROTECTED] Subject: Re: RAID or not? Lefevre, Steven wrote: I say go with RAID 5, on a controller card. Mirroring just gives you backup, and you lose half your diskspace. It offers no performance benefit, and actually the computer might have to work harder to make sure the drives are in sync. that is not true. mirroring gives you double the read speed and half the write speed. RAID5 gives you less than half the write speed. - OK, I see how it can give you double the read speed, but how can it give you have the write speed? Does it split the data between disks and then sync them later? that's why i said if your database app is mostly selects go for mirroring. the OP said his app is about 50% select, so i say mirroring is a good choice. and hey, what's wrong with having a backup? the computer doesn't work any harder, it's all handled through the RAID controller card anyway. - Of course, there's nothing wrong with having a backup, in fact he should. But I was under the mistaken impression that that was all disk mirroing did for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID or not?
I say go with RAID 5, on a controller card. Mirroring just gives you backup, and you lose half your diskspace. It offers no performance benefit, and actually the computer might have to work harder to make sure the drives are in sync. Disk striping makes things *fast*, BUT THERE IS NO PROTECTION. If you lose a drive, you are screwed; hope you have a backup. Raid 5 spreads data out over all the disks, and keeps one for checksums or whatever. You lose only one drive to the checksum. You get better performance than mirroring or regular drive, because the data is spread out over your drives. It's not as good as disk striping, though. You get great redudancy, because if you lose one disk, the RAID still operates (in 'degraded mode') -- it's slower, but the server is still up. When you get your replacement drive in, you just hook it up, and the RAID rebuilds itself. So, all in all, RAID 5 gives fault tolerance and better performance. You can have the OS do the RAID, but that puts a lot of burden on the processor and OS. I recommend getting a RAID card, and not a cheap one, either. Plan on spending ~$500. -Original Message- From: Jackson Miller [mailto:[EMAIL PROTECTED] Sent: Thursday, August 21, 2003 11:56 AM To: Jon Drukman Cc: [EMAIL PROTECTED] Subject: Re: RAID or not? On Thursday 21 August 2003 2:23, Jon Drukman wrote: if you're mostly running SELECTs then i would recommend a mirrored configuration. I would say I am running about %50 SELECTS, 30% UPDATE, 20% INSERT. However I don't know how to find that out for sure. Would that affect how I set up the RAID? -Jackson jackson miller cold feet creative 615.321.3300 / 800.595.4401 [EMAIL PROTECTED] cold feet presents Emma the world's easiest email marketing Learn more @ http://www.myemma.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Secure Database Design Part II
Hey folks - Thanks to everyone who gave input to my concerns. Of course, we don't intend to have the mysql port open to the world. We will have Apache/PHP connect on a unix socket, or to another machine with a cross-cable on non-routeable IPs. But now I have another question. We are working on a web database to allow our suppliers to log on and submit information that they would otherwise fax, email, or phone to us. It would reduce work in our office, and reduce errors in duplication of our information. But, we are very concerned about security! We aren't worried so much about outside hackers as we are about legit users trying to gain access to information they shouldn't. Some of our suppliers are overseas and we think they have no qualms about trying to hack the system, knowing the stunts they have pulled in the past. It would be extremely difficult to pursue any problems legally, since it would be international, and the damage would be already done. So, here's my question. Good database design dictates that I normalize my tables. So, in this simplified example, we have a table of supplier quotes: supplier_id part_id quote_price quote_date All of our suppliers would be drawing from the same table, via php. I'm worried that good database design might be more susceptible to information 'spilling over' -- what if I make a simple mistake and put the wrong supplier_id with a new user's logon? That new user would see all the parts that belong to whatever company I mistakenly associate them with. I'm not so worried about, say, suppliers seeing sales data. All the php pages will be protected by Unix filesystem permissions, so I can be reasonably certain that only those belonging to the suppliers group will be able to execute supplier_*.php. Even if they do load some sales_*.php page, then the MySQL user permissions will stop them from actually seeing any data on the page. So there are two layers of security between sales and suppliers, for example. I would have to make two mistakes for them to have access to sales data. But, when all suppliers are accessing the same pages, it's up to my careful hands to make sure they are pulling only their records out of the table. If I make a mistake in a query, it might pull up other records, or even all records! Of course if I design it completely perfectly the first time, I don't have to worry about anything. But I'm not perfect and I don't make perfect things. So, I'm thinking I should violate good design principles, and setup identical tables for each supplier, salesperson, customer, etc. That way, since they share the same PHP pages, they aren't all pulling data from the same table. If there is any mixup in the query, the user doesn't have the MySQL permission to pull data from another suppliers table. Does this make sense? Steve Lefevre Network Administrator IMI International, Inc. 614.839.2500 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MD5-read permission?
Hey folks - While contemplating the design of a secure web database, an idea struck me. I'm thinking of submitting it as a feature request, so please critique it. I'm having php handle user logon with it's .htaccess emulation. I'm storing usernames and password hashes in a table. The problem is that php needs to open MySQL with *some user* with *some permission*, just to read the user table and check the password. So, It seems that I have to store the password plaintext somewhere in some php file. (I asked the list about this earlier and several others had great suggestions on how to hide this plain-text password -- Thanks Rob! -- but, can we make it better?) So if some wily hacker were to get the contents of this php file, s/he would get a username and password for the database. Now of course, I'm only going to give this user permission to read the user database, and all the passwords are hashed... but : I propose a new permission that I will call MD5read. It's like select, only it just returns hashes. So, say you do something like: SELECT password FROM user; 49726b60ccbf03d6c619632e1db6 f8ec2c9d79b5f969a96be968e7152bbd SELECT username, password FROM user; 24424b444b80831b677594a238f81dd9 | 4549625d8275b97b9b4f9662f1c550fa 1e5143d05b327f7d3cce15f9e3e44ad2 | fe3b4b388a69ceed38d6a0066e6a221b SELECT username+password+somethingelse FROM user; 49726b60ccbf03d6c619632e1db6 f8ec2c9d79b5f969a96be968e7152bbd So that way, if someone gets the username/password for this user, they can't get any data off of the database. One thing you have to watch is that you don't use the md5 function for a user that has only md5read permission, because that would double-hash it, and whatever you're checking would fail. I know you can do SELECT md5(username), md5(password) FROM user (or whatever the syntax is), but the user doing that has to have read permission already. So if a hacker gets that username and password, they are probably not going to hash data they are trying to get out of the database. I would feel safe storing a user's name and password in a plain text php script if they had only this permission. Is this useful? Are there any flaws in my reasoning? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Designing a secure database?
I'm working on a website/database deal, and right now I'm designing the security model. It's using LAMP(HP), so all the tricks are going to be through PHP. First off, this will be behind a firewall with SSL encryption. We may also setup some kind of VPN tunneling.I'll be ignoring other security details that don't apply to the problem at hand, but please feel free to suggest. I'm going to have users enter a username and password, with PHP emulating the htaccess dialogue boxes. I would like to check the entered values in a permissions table. This table looks like: username | passwordhash | ipaddress | permission All users will have a static IP, so they have to match the username-password-ipaddress combination. I'm storing the passwords as an MD5 hash in case someone breaks in and reads the table. So what MySQL user does PHP log onto as the database as, in order to read the permissions table? (After that, we just go with the logged user's permission). If I make a special user that just has permission to read the permission table, do I have to store that user's password plaintext in a php script somewhere, thus adding a security risk if someone were to get a hold of that password? Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subselect workaround help?
Hey folks- 'nother question. I'm not an SQL expert, and I think I need a subselect, which means I need a workaround on MySQL 3.23 or whatever version it is. Here's the tables I have, with the relevant columns: Students - Name - StudentID (PK) - ClassID Classes - ClassID (PK) - Name Each Student belongs to one class. So, I have the StudentID. I want to get the Classes.Name... Final result should look like Student | Class -- Steve Lefevre | Math101 Stacy Adams | Intro to SQL Something like SELECT Student.Name, Classes.Name FROM Students, Classes WHERE Students.StudentID = . $ID . AND Classes.Name IN ( SELECT Classes.Name FROM Classes WHERE ClassID = Students.ClassID ) I know that probably won't work as a subselect query, but that's my starting point. Any help? Thanks! Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Weird WHERE clause possible?
I'm developing a web-site database. There is a form on one page where a user can search for a student by typing in a last name and submitting it. Sometimes there can be more than one student with the same last name, so I want the user to be able to type in Smith, J (where normally they type 'Smith'). The problem (as I see it) is that I'm storing the last name and the first name in two seperate fields. I can make an SQL statement like Select * from Students Where LastName Like Smith%;, but can I make something like SELECT * FROM Students WHERE (LastName, , , FirstName) AS Name LIKE Smith, J%; ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Backups mechanism
Can you post this script? (Minus the passwords, etc.) - Original Message - From: Simon Green [EMAIL PROTECTED] To: 'Jonas Widarsson' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 11:18 AM Subject: RE: Backups mechanism What we have done it right a small script: It logs on to the MySQL server. It then locks the databases and tables we have put in a config file. Simply copies the data files to a new directory. Unlocks the tables. Once this is done (copy if fast) you can tar or zip up the copied files. Simon -Original Message- From: Jonas Widarsson [mailto:[EMAIL PROTECTED]] Sent: 07 January 2003 15:07 To: [EMAIL PROTECTED] Subject: Backups mechanism Hello world! Every attempt I have made to find a decent way of backing up a database ends up with tons of reading to show hundreds of ways to do database backups. I want to know which way to do complete backups is most commonly used by professional users. (Windows and Linux) I have pleasant experience from Microsoft SQL Server 2000 where backung up is very confident, easy to understand and just a matter of a mouse click. Is there any similarily convenient way to do this with mysql, for example: * a shell script (windows / Linux) that works as expected without days of configuration? * scheduled backups, complete or differential? * GUI-solution ??? (mysql control center does not even mention the word backup) Backup is such an important issue. Why does it seem like it is something that the developers don't care about? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Weird WHERE clause possible?
- Original Message - From: Ryan Fox [EMAIL PROTECTED] To: Lefevre, Steven [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 11:33 AM Subject: Re: Weird WHERE clause possible? Backing up, is the problem that you need to uniquely identify the student's name after returning the results? If so, a simple, efficient way is to add an auto_increment column to your students table, so each name is given a unique ID. Then, when results are returned, you can create a link for each student that includes the student's ID. Well, I do already have a unique ID on the table. If this isn't the case, and you really want to do a statement like that above, how about: SELECT * FROM Students WHERE LastName='Smith' and FirstName LIKE 'J%'; or worse, SELECT * FROM Students WHERE concat(LastName, , , FirstName) LIKE 'Smith, J%'; Do you say 'worse', because the query is slower? I'm migrating users from a desktop FileMaker app to an Apache/Mysql/PHP thingy. The opening page of the FM app has a list of all the students, in alphabetical order. It displays rather quickly. My webpage takes a while to load all the students -- it's like 2 MBs of data over a dial-up line! They are used to having a type ahead field on the main page of the Filemaker app, where they type the first letter of the student. Then Filemaker shows only, say, students begining with G. Then, they type the next letter, and see only Ga. By this time, they have usually found their student. So, in my web app, instead of sending a whole table of all the student names, I just have a form field where they type in the last name, or the first few letters of the last name. Problem is with names like Smith, there are some 40 Smiths. The user would then want to enter Smith, Su into the form field. That breaks the form field because I was only searching on the last name. I think your 'worse' query solves the problem, but why is it worse? If I were using your first Idea, I'd worry more about improperly parsing the user entry into the SQL, e. g. Smith, Su - SELECT * FROM Students WHERE LastName='Smith' and FirstName LIKE 'Su%'; On a hunch, it seems more likely breakable b/c of user input. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't find hosts.frm -- *directory* permissions now okay?
mysql still doesn't own the mysql directory. OK, I changed that for /usr/libexec [root@server /]# ls -la /usr/libexec/ total 4008 drwxr-xr-x7 root root 4096 Dec 11 15:28 . drwxr-xr-x 16 root root 4096 Nov 27 09:32 .. ... drwxr-xr-x3 mysqlmysql4096 Dec 11 15:30 mysql -rwxr-xr-x1 root root 3771937 Sep 4 13:42 mysqld ... (BTW I also changed the ownership of mysqld in /usr/libexec from root to mysql, but no luck. Tried it both ways.) The /var/lib directory was already this way: [root@server /]# ls -la /var/lib/ total 60 drwxr-xr-x 13 root root 4096 Dec 6 10:07 . drwxr-xr-x 19 root root 4096 Nov 27 09:21 .. drwxr-xr-x2 root root 4096 Nov 27 09:22 alternatives ... drwxr-xr-x4 mysqlmysql4096 Dec 12 16:38 mysql ... and I still get the same error: 021212 16:38:14 mysqld started 021212 16:38:14 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021212 16:38:14 mysqld ended Any ideas? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can't find file: './mysql/host.frm'
OK, I'm making progess on my MySQL problem. The log file says this: 021211 15:32:05 mysqld started 021211 15:32:05 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021211 15:32:05 mysqld ended I've run the mysql_install_db script when I first installed mysql. I ran it again just today. I copied the directory from /var/lib/mysql to /usr/libexec/mysql (mysqld is in /usr/libexec), yet it says it still can't find the host.frm. [root@server test]# less /usr/libexec/mysql/ total 116 drwxr-xr-x3 root root 4096 Dec 11 15:30 ./ drwxr-xr-x7 root root 4096 Dec 11 15:28 ../ ... -rw-r-1 root root 8958 Dec 11 15:29 host.frm So now host.frm is in three places, but I get the same error. [root@server libexec]# locate host.frm /usr/libexec/mysql/host.frm /var/lib/mysql/mysql/host.frm /var/lib/mysql/host.frm Where the heck does it think it should be? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unexpected ending
Hey folks - Linux newbie here. I have RH 8.0 installed on a machine. I did an RPM installation of MySQL 3.23 (or whatever the current 3 series is). When I try to start the safe daemon, I get this: [root@server ]# /usr/bin/safe_mysqld Starting mysqld daemon with databases from /var/lib/mysql 021210 13:26:51 mysqld ended [root@server ]# What's the deal? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Unexpected ending
Before I get 1000 RTFMs, I found on google references to an error file. I did a search (after rebuilding the 'locate' database) and found no error file! :( Also, no results returned on the mysql mailing list archive. - Original Message - From: Lefevre, Steven [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 10, 2002 1:28 PM Subject: Unexpected ending Hey folks - Linux newbie here. I have RH 8.0 installed on a machine. I did an RPM installation of MySQL 3.23 (or whatever the current 3 series is). When I try to start the safe daemon, I get this: [root@server ]# /usr/bin/safe_mysqld Starting mysqld daemon with databases from /var/lib/mysql 021210 13:26:51 mysqld ended [root@server ]# What's the deal? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
