Re: Safe DB Distribution
In the last episode (Jan 29), Suhas Pharkute said: > Ok, I will explain it again, > > I need a way so that if some body gets the DB files (.MYD and .MYI) > from my distribution, they can put it in their DB engine and can read > my DB. I would like to STOP this stealing. > > Is there any way? If you also give them the php script, then no amount of encryption on the DB files will help, since the user can simply look at the script and see how it decrypts the records. If you write the program in a compiled language like C and encrypt/decrypt the data before passing it to mysql, it would be more difficult to extract the data, possibly difficult enough that none of your users would try. You can't simply use mysql's AES_ENCRYPT/AES_DECRYPT functions, since your user could enable mysql's SQL logging option and read your key from the log. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Safe DB Distribution
Ok, I will explain it again, I need a way so that if some body gets the DB files (.MYD and .MYI) from my distribution, they can put it in their DB engine and can read my DB. I would like to STOP this stealing. Is there any way? Thanks Suhas
Re: Safe DB Distribution
Ok, I will explain it again, I need a way so that if some body gets the DB files (.MYD and .MYI) from my distribution, they can put it in their DB engine and can read my DB. I would like to STOP this stealing. Is there any way? Thanks Suhas On 1/29/07, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote: Have you checked out http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html, you should be able to en/decrypt your data via these functions thereby rendering any data on the disk totally useless unless the process comes through your mysql server. You will have to configure mysql with openssl support though, I'm sure you would be able to find a package somewhere that has that available. You can then use mysql security to only allow access to the webserver user so the php package is the only one that can access this. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Suhas Pharkute [mailto:[EMAIL PROTECTED] Sent: Tuesday, 30 January 2007 1:55 PM To: Ales Zoulek Cc: mysql@lists.mysql.com Subject: Re: Safe DB Distribution Yep, I do understand that, but when I give DB to someone, I do not want them to see the database. I want them to access the database thr' the php application. It is very simple, I don't want any one to see my DB in open way. But they can access it thr' my app. I agree with about file system, and that will work on Linux but unfortunately I am working on Windows(98/NT/XP) any ideas!!! Thx Suhas On 1/29/07, Ales Zoulek <[EMAIL PROTECTED]> wrote: > > What is it exactly that you need to achieve? > > PHP script doesnt read a MySQL data files, MySQL server does. You can > configure your OS and filesystem rights, so that only MySQL server can > read them. > > Ales. > > > > -- Forwarded message -- > > From: "Suhas Pharkute" <[EMAIL PROTECTED]> > > To: mysql@lists.mysql.com > > Date: Mon, 29 Jan 2007 11:48:27 -0700 > > Subject: Safe DB Distribution > > HI, > > > > I want to distribute the MySQL DB to different user with an PHP web app. > My > > biggest problem is MySQL data files. Is there anyway by which I can > > configure the MySQL so that only PHP script can read it and if you > happen to > > copy the Data files and try to use it, that will be useless. > > > > I know, this is been in discussion before, but if I can get any other > idea > > to it, I would appreciate it > > > > Thank you in advance, > > Suhas > > > > > > > > > -- > -- > Ales Zoulek > +420 604 332 515 > ICQ: 82647256 > http://www.al3x.cz/ > -- > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Suhas Pharkute, PhD CEO - Syna Intelligence, LLP V. 208 830 8915 (C) E. [EMAIL PROTECTED],.com W. http://synaintel.com -- Suhas Pharkute, PhD CEO - Syna Intelligence, LLP V. 208 830 8915 (C) E. [EMAIL PROTECTED],.com W. http://synaintel.com
Re: Safe DB Distribution
Ok, I will explain it again, I need a way so that if some body gets the DB files (.MYD and .MYI) from my distribution, they can put it in their DB engine and can read my DB. I would like to STOP this stealing. Is there any way? Thanks Suhas On 1/29/07, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote: Have you checked out http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html, you should be able to en/decrypt your data via these functions thereby rendering any data on the disk totally useless unless the process comes through your mysql server. You will have to configure mysql with openssl support though, I'm sure you would be able to find a package somewhere that has that available. You can then use mysql security to only allow access to the webserver user so the php package is the only one that can access this. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Suhas Pharkute [mailto:[EMAIL PROTECTED] Sent: Tuesday, 30 January 2007 1:55 PM To: Ales Zoulek Cc: mysql@lists.mysql.com Subject: Re: Safe DB Distribution Yep, I do understand that, but when I give DB to someone, I do not want them to see the database. I want them to access the database thr' the php application. It is very simple, I don't want any one to see my DB in open way. But they can access it thr' my app. I agree with about file system, and that will work on Linux but unfortunately I am working on Windows(98/NT/XP) any ideas!!! Thx Suhas On 1/29/07, Ales Zoulek <[EMAIL PROTECTED]> wrote: > > What is it exactly that you need to achieve? > > PHP script doesnt read a MySQL data files, MySQL server does. You can > configure your OS and filesystem rights, so that only MySQL server can > read them. > > Ales. > > > > -- Forwarded message -- > > From: "Suhas Pharkute" <[EMAIL PROTECTED]> > > To: mysql@lists.mysql.com > > Date: Mon, 29 Jan 2007 11:48:27 -0700 > > Subject: Safe DB Distribution > > HI, > > > > I want to distribute the MySQL DB to different user with an PHP web app. > My > > biggest problem is MySQL data files. Is there anyway by which I can > > configure the MySQL so that only PHP script can read it and if you > happen to > > copy the Data files and try to use it, that will be useless. > > > > I know, this is been in discussion before, but if I can get any other > idea > > to it, I would appreciate it > > > > Thank you in advance, > > Suhas > > > > > > > > > -- > -- > Ales Zoulek > +420 604 332 515 > ICQ: 82647256 > http://www.al3x.cz/ > -- > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Suhas Pharkute, PhD CEO - Syna Intelligence, LLP V. 208 830 8915 (C) E. [EMAIL PROTECTED],.com W. http://synaintel.com -- Suhas Pharkute, PhD CEO - Syna Intelligence, LLP V. 208 830 8915 (C) E. [EMAIL PROTECTED],.com W. http://synaintel.com
RE: Safe DB Distribution
Have you checked out http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html, you should be able to en/decrypt your data via these functions thereby rendering any data on the disk totally useless unless the process comes through your mysql server. You will have to configure mysql with openssl support though, I'm sure you would be able to find a package somewhere that has that available. You can then use mysql security to only allow access to the webserver user so the php package is the only one that can access this. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Suhas Pharkute [mailto:[EMAIL PROTECTED] Sent: Tuesday, 30 January 2007 1:55 PM To: Ales Zoulek Cc: mysql@lists.mysql.com Subject: Re: Safe DB Distribution Yep, I do understand that, but when I give DB to someone, I do not want them to see the database. I want them to access the database thr' the php application. It is very simple, I don't want any one to see my DB in open way. But they can access it thr' my app. I agree with about file system, and that will work on Linux but unfortunately I am working on Windows(98/NT/XP) any ideas!!! Thx Suhas On 1/29/07, Ales Zoulek <[EMAIL PROTECTED]> wrote: > > What is it exactly that you need to achieve? > > PHP script doesnt read a MySQL data files, MySQL server does. You can > configure your OS and filesystem rights, so that only MySQL server can > read them. > > Ales. > > > > -- Forwarded message -- > > From: "Suhas Pharkute" <[EMAIL PROTECTED]> > > To: mysql@lists.mysql.com > > Date: Mon, 29 Jan 2007 11:48:27 -0700 > > Subject: Safe DB Distribution > > HI, > > > > I want to distribute the MySQL DB to different user with an PHP web app. > My > > biggest problem is MySQL data files. Is there anyway by which I can > > configure the MySQL so that only PHP script can read it and if you > happen to > > copy the Data files and try to use it, that will be useless. > > > > I know, this is been in discussion before, but if I can get any other > idea > > to it, I would appreciate it > > > > Thank you in advance, > > Suhas > > > > > > > > > -- > -- > Ales Zoulek > +420 604 332 515 > ICQ: 82647256 > http://www.al3x.cz/ > -- > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Suhas Pharkute, PhD CEO - Syna Intelligence, LLP V. 208 830 8915 (C) E. [EMAIL PROTECTED],.com W. http://synaintel.com smime.p7s Description: S/MIME cryptographic signature
Re: Safe DB Distribution
Yep, I do understand that, but when I give DB to someone, I do not want them to see the database. I want them to access the database thr' the php application. It is very simple, I don't want any one to see my DB in open way. But they can access it thr' my app. I agree with about file system, and that will work on Linux but unfortunately I am working on Windows(98/NT/XP) any ideas!!! Thx Suhas On 1/29/07, Ales Zoulek <[EMAIL PROTECTED]> wrote: What is it exactly that you need to achieve? PHP script doesnt read a MySQL data files, MySQL server does. You can configure your OS and filesystem rights, so that only MySQL server can read them. Ales. > -- Forwarded message -- > From: "Suhas Pharkute" <[EMAIL PROTECTED]> > To: mysql@lists.mysql.com > Date: Mon, 29 Jan 2007 11:48:27 -0700 > Subject: Safe DB Distribution > HI, > > I want to distribute the MySQL DB to different user with an PHP web app. My > biggest problem is MySQL data files. Is there anyway by which I can > configure the MySQL so that only PHP script can read it and if you happen to > copy the Data files and try to use it, that will be useless. > > I know, this is been in discussion before, but if I can get any other idea > to it, I would appreciate it > > Thank you in advance, > Suhas > > > -- -- Ales Zoulek +420 604 332 515 ICQ: 82647256 http://www.al3x.cz/ -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Suhas Pharkute, PhD CEO - Syna Intelligence, LLP V. 208 830 8915 (C) E. [EMAIL PROTECTED],.com W. http://synaintel.com
Re: ordering by count
Konstantin Saveljev wrote: Hi, i have a system that has media files ==> users can be fans of media ( so media becomes their favorite ). the table itself: CREATE TABLE media_fans( id int(11) unsigned not null auto_increment, user_id int(11) unsigned not null, media_id int(11) unsigned not null, primary key(id), foreign key(user_id) references users(id) on delete cascade, foreign key(media_id) references media(id) on delete cascade )Engine=InnoDB; let's say we have 1M entries in this table... How to get the Top Favorite media very quickly ? ( let's say You Tube has the sorting possibility and they have a huge amount of users and files and fans of files ) I can see 2 solutions: 1) using this sort of query: SELECT COUNT(id) as 'count' FROM media_fans ORDER BY 'count' DESC LIMIT 100 so if we have 1M rows then the 'EXPLAIN' says that we need to go through all 1M rows and type is 'INDEX' 2) using another table to store the total amount of fans for each media this solution requires another table and some triggers, so when the fan is added to media_fans some trigger works and increments a value for some media ( and then we just select the top favorite media without counting the number of fans every time ) So what i'm interested in: how do huge portals implement that ? do they recount each time ( isn't it slow ? ) or they use precalculated values ? It depends really. If it's something they are going to do all the time, they'd probably use a trigger or work it out in the application and do the update "manually" (eg if they are using mysql 4 which doesn't support triggers). If it's something that they run once a month for their reporting, they'd probably run it "as is". -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General MySQL Question: Ed Reed (CA, United States ofAmerica) Medium
The area by the Santa Clara convention center is pretty dead, but you can take the light rail to downtown San Jose and there is a pretty lively nightlife there, lots of interesting restaurants, The Tech Museum and other things depending on you interests. Also there are lots more interesting places to eat than sizzler within in 5 minute drive of the convention center. Perhaps what is needed is either a nice cheatsheet of what to do and where to go? Or perhaps some of the locals could volunteer to be "ambassadors" and take people with similar interests to local activities, restaurants or points of interest. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General MySQL Question: Ed Reed (CA, United States ofAmerica) Medium
I understand that. I go there for the same thing. My point was that, the conference needs the attendees as much we want the conference. When I go I'm going for the conference stuff but I have a life and they're asking us to give up that life for maybe 6 days. After a the second day things start to get a little monotonous and we need a break. After the third or fourth day the brain is fried. There needs to be some kind of other activity that takes your mind off of the same hotel walls. Let's put it another way. When you go to work do you stay there for 5 days straight? Probably not. You go home, you go out to dinner. You see friends. You do other things to take your mind away from work because you have to recharge your batteries. It's the same thing at the conferences. Unless you're a robot you have to get out and see the sights and be entertained so that you can go back the next day and absorb the next days meetings. I just liked it better when it felt like it was gonna be held at a different location every year. I made this same complaint last year and it never got this much attention. - cheers >>> "Joshua J. Kugler" <[EMAIL PROTECTED]> 1/29/07 4:36 PM >>> That's funny...when I go to a technical conference, I usually go for the conference, and couldn't care less if there is other stuff to do in the area during the off hours. j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com ( http://www.eeinternet.com/ ) PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 >>> "Ed Reed" <[EMAIL PROTECTED]> 1/29/07 1:57 PM >>> I just didn't enjoy the location. When it was in Orlando a few years ago, it was great. There were plenty of things to do and see; different places to eat every night. I had a really good time. Then the following year I went to Santa Clara and there was nothing to do. I went to the movies one night. Had dinner at Sizzler three times. I drove 45 minutes to find a place I could buy a shirt. The rest of the time I stayed in the hotel and watched crappy tv. If you folks want me to be away from my family and friends for five days you've gotta give me a reason to wanna go besides the great technical information. I didn't go to last years, I'm not going to this years and I probably won't go to any future ones held in Santa Clara. When I came home from the last one, my kids asked what I got them from my trip and I had nothing for them. It was just a boring place to go and I don't wanna go back. I'd just like to see it held someplace new every year. Thanks for replying to my comment >>> Lenz Grimmer <[EMAIL PROTECTED]> 1/25/07 2:21 AM >>> -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Ed, thank you for your message! On Thu, 18 Jan 2007, Ed Reed has filled out the MySQL Contact Form with a General MySQL Question: > Can someone tell me you folks are going to hold all future User Conferences > in Santa Clara? I would like to go this year but I didn't enjoy myself at the > last one I went to in Santa Clara in '05 and I don't wanna go back. If you > folks plan to hold all future conferences there I'd like to know so I can > resign myself to the fact that it's never gonna change instead of hoping that > it's gonna be somewhere else every year. To be honest, it's not entirely clear yet if we will change the location for upcoming events. But could you explain why you didn't enjoy yourself at the last one? How can we improve? Bye, LenZ - -- Lenz Grimmer <[EMAIL PROTECTED]> Community Relations Manager, EMEA MySQL GmbH, http://www.mysql.de/, Hamburg, Germany Visit the MySQL Forge at http://forge.mysql.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFFuISeSVDhKrJykfIRAsJxAJ9hLkjhA10B68OHpw97x6QHbSqjLACePQym m9lEE93nxdC+K//cQsL658Q= =W/8N -END PGP SIGNATURE-
[OT] Re: General MySQL Question: Ed Reed (CA, United States of America) Medium
On Monday 29 January 2007 12:57, Ed Reed wrote: > I just didn't enjoy the location. When it was in Orlando a few years ago, > it was great. There were plenty of things to do and see; different places > to eat every night. I had a really good time. Then the following year I > went to Santa Clara and there was nothing to do. I went to the movies one > night. Had dinner at Sizzler three times. I drove 45 minutes to find a > place I could buy a shirt. The rest of the time I stayed in the hotel and > watched crappy tv. If you folks want me to be away from my family and > friends for five days you've gotta give me a reason to wanna go besides the > great technical information. I didn't go to last years, I'm not going to > this years and I probably won't go to any future ones held in Santa Clara. > When I came home from the last one, my kids asked what I got them from my > trip and I had nothing for them. It was just a boring place to go and I > don't wanna go back. I'd just like to see it held someplace new every year. > > Thanks for replying to my comment That's funny...when I go to a technical conference, I usually go for the conference, and couldn't care less if there is other stuff to do in the area during the off hours. j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Safe DB Distribution
What is it exactly that you need to achieve? PHP script doesnt read a MySQL data files, MySQL server does. You can configure your OS and filesystem rights, so that only MySQL server can read them. Ales. -- Forwarded message -- From: "Suhas Pharkute" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Date: Mon, 29 Jan 2007 11:48:27 -0700 Subject: Safe DB Distribution HI, I want to distribute the MySQL DB to different user with an PHP web app. My biggest problem is MySQL data files. Is there anyway by which I can configure the MySQL so that only PHP script can read it and if you happen to copy the Data files and try to use it, that will be useless. I know, this is been in discussion before, but if I can get any other idea to it, I would appreciate it Thank you in advance, Suhas -- -- Ales Zoulek +420 604 332 515 ICQ: 82647256 http://www.al3x.cz/ -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General MySQL Question: Ed Reed (CA, United States of America) Medium
I just didn't enjoy the location. When it was in Orlando a few years ago, it was great. There were plenty of things to do and see; different places to eat every night. I had a really good time. Then the following year I went to Santa Clara and there was nothing to do. I went to the movies one night. Had dinner at Sizzler three times. I drove 45 minutes to find a place I could buy a shirt. The rest of the time I stayed in the hotel and watched crappy tv. If you folks want me to be away from my family and friends for five days you've gotta give me a reason to wanna go besides the great technical information. I didn't go to last years, I'm not going to this years and I probably won't go to any future ones held in Santa Clara. When I came home from the last one, my kids asked what I got them from my trip and I had nothing for them. It was just a boring place to go and I don't wanna go back. I'd just like to see it held someplace new every year. Thanks for replying to my comment >>> Lenz Grimmer <[EMAIL PROTECTED]> 1/25/07 2:21 AM >>> -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Ed, thank you for your message! On Thu, 18 Jan 2007, Ed Reed has filled out the MySQL Contact Form with a General MySQL Question: > Can someone tell me you folks are going to hold all future User Conferences > in Santa Clara? I would like to go this year but I didn't enjoy myself at the > last one I went to in Santa Clara in '05 and I don't wanna go back. If you > folks plan to hold all future conferences there I'd like to know so I can > resign myself to the fact that it's never gonna change instead of hoping that > it's gonna be somewhere else every year. To be honest, it's not entirely clear yet if we will change the location for upcoming events. But could you explain why you didn't enjoy yourself at the last one? How can we improve? Bye, LenZ - -- Lenz Grimmer <[EMAIL PROTECTED]> Community Relations Manager, EMEA MySQL GmbH, http://www.mysql.de/, Hamburg, Germany Visit the MySQL Forge at http://forge.mysql.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFFuISeSVDhKrJykfIRAsJxAJ9hLkjhA10B68OHpw97x6QHbSqjLACePQym m9lEE93nxdC+K//cQsL658Q= =W/8N -END PGP SIGNATURE-
RE: Mysqldump Files
Hi David, Is the space on hard-drive is major concern of yours or abilities to recover from crash is? Backups are usually taking to be able to recover from a crash. Which in its turn means if there is a way to recover faster it is better. Having slave that is constantly updated gives you very quick way of recovering if master goes down. Just point you PHP scripts to slave and be happy. If you need additional back up, do them from "full" slave. Stop it for a while, do backups and then restart slave again. For huge backups in our office we use old, and I mean old, Dells with huge 500GB drives running one of *Nix's Regards, Mikhail Berman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 29, 2007 3:27 PM To: Mikhail Berman Cc: mysql Subject: RE: Mysqldump Files Hi Mikhail, I don't think that would save much space, in terms of file size. The tables that are actively getting inserts are large and growing larger (~750,000 records), and those that have no activity are either currently empty or have less than a hundred records in them. So just dumping the active tables will comprise I'd guess 99% or more of the database size. David -- Hi David, Let me point you in a bit different direction. You are already running replication as it seems from your E-mail So, why not just run chained replication from second to the third server and use "replicate-do-table = [table_name]" in my.cnf of the third server to limit selection of tables to be used by web server. Or do full replication to another server from the first one for full backup? Regards, Mikhail Berman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 29, 2007 2:33 PM To: mysql Subject: Mysqldump Files Howdy Guys and Gals, We are acquiring data on background radiation in a master-slave server environment (RH9.0, MySQL 4.0.24) at the rate of approximately 19,000 records per day. The data are insert-only into about 25 of 31 tables in the database -- no updates are ever applied to the data. Information from the database is used via select statements for graphical display and report generation amongst other uses. A PHP backup script using mysqldump runs as a cron job each night from a third server which also functions as an intranet webserver. After 1 1/2 years of operation, the mysqldump file of the entire database is roughly 760 MB, and this takes under 2 minutes to create. Once bzipped and tarred, the entire file is 31.7 MB in size, and this part of the backup process now takes 46-47 minutes. The rate of acquisition of data will be fairly constant, and up to 3 years of data will be kept on the live master-slave, so simply doubling all these values seems a realistic expectation for a full backup of the database after 3 years. Data older than 3 years would be deleted from the master-slave system. How long it would be reasonable to keep doing a full dump of the database versus using mysqldump with a where clause, i.e., doing a daily incremental backup, say of the last 24 hours. Also, what are the key mysqldump and/or server variables to pay attention to in order to process these large, maybe multi-gigabyte dump files? Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysqldump Files
Hi Mikhail, I don't think that would save much space, in terms of file size. The tables that are actively getting inserts are large and growing larger (~750,000 records), and those that have no activity are either currently empty or have less than a hundred records in them. So just dumping the active tables will comprise I'd guess 99% or more of the database size. David -- Hi David, Let me point you in a bit different direction. You are already running replication as it seems from your E-mail So, why not just run chained replication from second to the third server and use "replicate-do-table = [table_name]" in my.cnf of the third server to limit selection of tables to be used by web server. Or do full replication to another server from the first one for full backup? Regards, Mikhail Berman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 29, 2007 2:33 PM To: mysql Subject: Mysqldump Files Howdy Guys and Gals, We are acquiring data on background radiation in a master-slave server environment (RH9.0, MySQL 4.0.24) at the rate of approximately 19,000 records per day. The data are insert-only into about 25 of 31 tables in the database -- no updates are ever applied to the data. Information from the database is used via select statements for graphical display and report generation amongst other uses. A PHP backup script using mysqldump runs as a cron job each night from a third server which also functions as an intranet webserver. After 1 1/2 years of operation, the mysqldump file of the entire database is roughly 760 MB, and this takes under 2 minutes to create. Once bzipped and tarred, the entire file is 31.7 MB in size, and this part of the backup process now takes 46-47 minutes. The rate of acquisition of data will be fairly constant, and up to 3 years of data will be kept on the live master-slave, so simply doubling all these values seems a realistic expectation for a full backup of the database after 3 years. Data older than 3 years would be deleted from the master-slave system. How long it would be reasonable to keep doing a full dump of the database versus using mysqldump with a where clause, i.e., doing a daily incremental backup, say of the last 24 hours. Also, what are the key mysqldump and/or server variables to pay attention to in order to process these large, maybe multi-gigabyte dump files? Thanks, David
RE: Mysqldump Files
Hi David, Let me point you in a bit different direction. You are already running replication as it seems from your E-mail So, why not just run chained replication from second to the third server and use "replicate-do-table = [table_name]" in my.cnf of the third server to limit selection of tables to be used by web server. Or do full replication to another server from the first one for full backup? Regards, Mikhail Berman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 29, 2007 2:33 PM To: mysql Subject: Mysqldump Files Howdy Guys and Gals, We are acquiring data on background radiation in a master-slave server environment (RH9.0, MySQL 4.0.24) at the rate of approximately 19,000 records per day. The data are insert-only into about 25 of 31 tables in the database -- no updates are ever applied to the data. Information from the database is used via select statements for graphical display and report generation amongst other uses. A PHP backup script using mysqldump runs as a cron job each night from a third server which also functions as an intranet webserver. After 1 1/2 years of operation, the mysqldump file of the entire database is roughly 760 MB, and this takes under 2 minutes to create. Once bzipped and tarred, the entire file is 31.7 MB in size, and this part of the backup process now takes 46-47 minutes. The rate of acquisition of data will be fairly constant, and up to 3 years of data will be kept on the live master-slave, so simply doubling all these values seems a realistic expectation for a full backup of the database after 3 years. Data older than 3 years would be deleted from the master-slave system. How long it would be reasonable to keep doing a full dump of the database versus using mysqldump with a where clause, i.e., doing a daily incremental backup, say of the last 24 hours. Also, what are the key mysqldump and/or server variables to pay attention to in order to process these large, maybe multi-gigabyte dump files? Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqldump Files
Howdy Guys and Gals, We are acquiring data on background radiation in a master-slave server environment (RH9.0, MySQL 4.0.24) at the rate of approximately 19,000 records per day. The data are insert-only into about 25 of 31 tables in the database -- no updates are ever applied to the data. Information from the database is used via select statements for graphical display and report generation amongst other uses. A PHP backup script using mysqldump runs as a cron job each night from a third server which also functions as an intranet webserver. After 1 1/2 years of operation, the mysqldump file of the entire database is roughly 760 MB, and this takes under 2 minutes to create. Once bzipped and tarred, the entire file is 31.7 MB in size, and this part of the backup process now takes 46-47 minutes. The rate of acquisition of data will be fairly constant, and up to 3 years of data will be kept on the live master-slave, so simply doubling all these values seems a realistic expectation for a full backup of the database after 3 years. Data older than 3 years would be deleted from the master-slave system. How long it would be reasonable to keep doing a full dump of the database versus using mysqldump with a where clause, i.e., doing a daily incremental backup, say of the last 24 hours. Also, what are the key mysqldump and/or server variables to pay attention to in order to process these large, maybe multi-gigabyte dump files? Thanks, David
Safe DB Distribution
HI, I want to distribute the MySQL DB to different user with an PHP web app. My biggest problem is MySQL data files. Is there anyway by which I can configure the MySQL so that only PHP script can read it and if you happen to copy the Data files and try to use it, that will be useless. I know, this is been in discussion before, but if I can get any other idea to it, I would appreciate it Thank you in advance, Suhas
RE: simple alternate query
I suggest you use parentheses, by the way, so that it is obvious what you are doing. I never like to depend upon the precedence of operators to make my intentions clear. It's too easy to make mistakes. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Miguel Vaz [mailto:[EMAIL PROTECTED] > Sent: Saturday, January 27, 2007 9:21 PM > To: mysql@lists.mysql.com > Subject: Re: simple alternate query > > > I am dumb, so sorry: > > select * from table1 where id=1 or id >=3 and id <=4 > > Have to go spank myself until i bleed now, thanks. > > > > > > Miguel > > > > > > > > > > At 02:15 28-01-2007, Miguel Vaz wrote: > > > Hi, > > > > I Cant get a simple query to work, heres what i am > looking for: > > > > table1 > > -- > > id name > > 1 peter > > 2 john > > 3 mary > > 4 lisa > > 5 me > > > > I need a select that always retrieves id 1 and an interval > > of my choice: > > > -- > 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]
mysql-bin.index just went poof on Master while Master Running
Hello, I have a master that's been running since the 13th of January. Since the same date I've had a slave running against it, without any errors or serious lag. Suddenly, this morning replication broke with duplicate entry errors. My master claimed to be on mysql-bin.14 with a significant LOG_POS. The slave however, claimed to be back on mysql-bin.01. Trying to skip forward, the slave would skip forward as far as mysql-bin.003, still having duplicate entry errors (assumedly because it had already been this far). I then tried to set it to something much further forward such as mysql-bin.10 at which it gave me this error: 070129 14:00:12 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236) 070129 14:00:12 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log On the master, the file does in fact exist, and the permissions are correct. However, I did note that the mysql-bin.index has not been updated since mysql-bin.01, and the time stamp on the .index file is on the 14th of January. I tried to manually add the missing entries, but to no avail, the slave still couldn't find them. Then I restarted the slave, and still the same problem. Its as if the slave was moving right along, and then suddenly decided to forget where it was and go back to mysql-bin.01. Has anyone seen this before? Its a first for me. BTW, the MySQL on both boxes is 5.0.27-debug on Solaris 10. The binaries are the MySQL-built binaries. Thank you in advance for your help. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple alternate query
Be careful there...
You should probably use '(' ')' around that ...
select * from table1 where id=1 or (id>=3 and id<=4)
On Sunday 28 January 2007 02:21, Miguel Vaz wrote:
> I am dumb, so sorry:
>
> select * from table1 where id=1 or id >=3 and id <=4
>
> Have to go spank myself until i bleed now, thanks.
>
>
>
>
>
> Miguel
>
> At 02:15 28-01-2007, Miguel Vaz wrote:
> > Hi,
> >
> > I Cant get a simple query to work, heres what i am looking for:
> >
> > table1
> > --
> > id name
> > 1 peter
> > 2 john
> > 3 mary
> > 4 lisa
> > 5 me
> >
> > I need a select that always retrieves id 1 and an interval
> > of my choice:
--
Mike Aubury
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL reporting an error with subquery query
Hi, In your SQL statement you have used subquery. The subquery feature is introduced in MySQL version 4.1 and later. Thats why you are getting the Syntax error while running subquery in v4.0.24. Thanks, ViSolve DB Team - Original Message - From: "A Blossom of Paradise" <[EMAIL PROTECTED]> To: Sent: Tuesday, January 23, 2007 7:07 AM Subject: MYSQL reporting an error with subquery query Hello Fellow MySqueelers!, SELECT version() reveals... 4.0.24-nt-max-log I have looked at the following query a hundred times, and cannot find how MYSQL can report a syntax error to me. query string is... SELECT RAND() AS `RAND`, `prod`.`id` FROM `prod` JOIN `pack` ON `prod`.`id` = `pack`.`pid` WHERE `pack`.`did` = 3 AND NOT ( `prod`.`id` IN ( SELECT `pack1`.`pid` FROM `ogrp` AS `ogrp1` JOIN `oitm` AS `oitm1` ON `ogrp1`.`id` = `oitm1`.`gid` JOIN `pack` AS `pack1` ON `oitm1`.`pid` = `pack1`.`id` WHERE `ogrp1`.`raid` = 9 AND `ogrp1`.`wid` = 3 AND `ogrp1`.`rdate` > DATE_SUB( CURDATE(), INTERVAL 3 MONTH ) ) ) ORDER BY `RAND` LIMIT 3 Error number is 1064 Error message is 'You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT `pack1`.`pid` FROM `ogrp` AS `ogrp1` JOIN `oitm` AS `o' any suggestions before i squeel? -wh No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.16.13/634 - Release Date: 1/17/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple alternate query
Hi, You can use select * from table1 where id in (1,3,4,5); Thanks, ViSolve DB Team - Original Message - From: "Miguel Vaz" <[EMAIL PROTECTED]> To: Sent: Sunday, January 28, 2007 7:51 AM Subject: Re: simple alternate query I am dumb, so sorry: select * from table1 where id=1 or id >=3 and id <=4 Have to go spank myself until i bleed now, thanks. Miguel At 02:15 28-01-2007, Miguel Vaz wrote: Hi, I Cant get a simple query to work, heres what i am looking for: table1 -- id name 1 peter 2 john 3 mary 4 lisa 5 me I need a select that always retrieves id 1 and an interval of my choice: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.17.10/651 - Release Date: 1/24/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
