Securing MySQL on Mac OSX
I am in the process of installing MySQL and PHP on my Mac following instruction in a book written by a chap called David Powers. I have come to a section that is called 'Securing MySQL on Mac OSX'. Basically it states that MySQL is up and running with a default account of 'root' and it's not password protected and so I need to plug that security gap. I've always used root as my account when working on PC's but having read this in David's book, I am now concerned; does this mean that I am open to attack from a potential hacker if I don't password protect MySQL? It mentions that root in MySQL has nothing to do with the root of Mac OSX, but I need to know if MySQL being unprotected in this way has opened a door for hackers. Thanks
Re: Securing MySQL on Mac OSX
It mentions that root in MySQL has nothing to do with the root of Mac OSX, but I need to know if MySQL being unprotected in this way has opened a door for hackers. it means that they can do whatever they want to the mysql db's without being prompted for a password. This does not mean that your box has open root access to the OS but its not good practice to have mysql as open as that -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqlimport remote host problem
zcat /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz | mysql -h 192.168.0.224 -u root -p alba2 Cheers Claudio 2009/3/11 Rob Wultsch wult...@gmail.com On Tue, Mar 10, 2009 at 7:16 PM, René Fournier m...@renefournier.com wrote: OK, I've managed to do the same thing with just the mysql command line program: mysql -h 192.168.0.224 -u root -p alba2 /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql Works great. However, the sql file is normally gzipped, so Can I ungzip the file on the fly (and without removing the .gzip version) and pipe the contents as I did above? (Yes, I'm UNIX-impaired.) Something like: mysql -h 192.168.0.224 -u root -p alba2 gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz But so that it works... ...Rene Pipe is your friend. You probably want something like: gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz | mysql -h 192.168.0.224 -u root -p alba2
Re: BSD/OS and 5.1
Hi Dave, There is not enough information here to know if your test is failing or your compile is bad. Did the compile complete without errors? What version of BSD/OS are you using there are many versions and varieties. Most of them have a package system with per-built binaries, I would really suggest trying those first. Hope this helps, Ken Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: I mean MySQL 5.1 I compile MySQL 5.1.32 and on tests I got gmake -k test cd unittest gmake test gmake[1]: Entering directory `/usr/src/contrib/mysql/mysql-5.1.32/unittest' perl unit.pl run mytap mysys ../storage/archive ../storage/blackhole ../storage/csv ../storage/example ../storage/federated ../storage/heap ../storage/myisam ../storage/myisammrg ../plugin/daemon_example ../plugin/fulltext Running tests: mytap mysys ../storage/archive ../storage/blackhole ../storage/csv ../storage/example ../storage/federated ../storage/heap ../storage/myisam ../storage/myisammrg ../plugin/daemon_example ../plugin/fulltext mytap/t/basic-t..Useless use of string in void context at -e line 1. mytap/t/basic-t.. No subtests run mysys/bitmap-t...Useless use of string in void context at -e line 1. mysys/bitmap-t... No subtests run mysys/base64-t...Useless use of string in void context at -e line 1. mysys/base64-t... No subtests run mysys/my_atomic-tUseless use of string in void context at -e line 1. mysys/my_atomic-t No subtests run Test Summary Report --- mytap/t/basic-t (Wstat: 0 Tests: 0 Failed: 0) Parse errors: No plan found in TAP output mysys/bitmap-t (Wstat: 0 Tests: 0 Failed: 0) Parse errors: No plan found in TAP output mysys/base64-t (Wstat: 0 Tests: 0 Failed: 0) Parse errors: No plan found in TAP output mysys/my_atomic-t (Wstat: 0 Tests: 0 Failed: 0) Parse errors: No plan found in TAP output Files=4, Tests=0, 1 wallclock secs ( 0.07 usr 0.10 sys + 0.00 cusr 0.10 csys = 0.27 CPU) Result: FAIL Failed 4/4 test programs. 0/0 subtests failed. gmake[1]: *** [test] Error 255 gmake[1]: Leaving directory `/usr/src/contrib/mysql/mysql-5.1.32/unittest' gmake: *** [test-unit] Error 2 cd mysql-test ; \ /usr/bin/perl ./mysql-test-run.pl --mysqld=--binlog-format=mixed Logging: ./mysql-test-run.pl --mysqld=--binlog-format=mixed 090310 10:53:13 [ERROR] Fatal error: Please read Security section of the manual to find out how to run mysqld as root! 090310 10:53:13 [ERROR] Aborting mysql-test-run: *** ERROR: Could not find version of MySQL gmake: *** [test-ns] Error 1 cd mysql-test ; \ /usr/bin/perl ./mysql-test-run.pl --ps-protocol --mysqld=--binlog-format=row Logging: ./mysql-test-run.pl --ps-protocol --mysqld=--binlog-format=row 090310 10:53:14 [ERROR] Fatal error: Please read Security section of the manual to find out how to run mysqld as root! 090310 10:53:14 [ERROR] Aborting mysql-test-run: *** ERROR: Could not find version of MySQL gmake: *** [test-pr] Error 1 gmake: Target `test' not remade because of errors. Is this safe to deploy or are patches going to be needed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: using a subquery/self-join to recursively retrieve a tree
as you say you need recursion to build the tree from the table. if you are interested just in all the employees that are not manager with their manager info. select * from emp e1 inner join emp e2 on e1.uidman=e2.uid; cheers Claudio 2009/3/10 Ali, Saqib docbook@gmail.com Hello, I have following simplistic DB representing a hierarchy: ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | uid| int(10) | NO | PRI | 0 | | | name | char(80) | YES | | NULL| | | mail | char(80) | YES | | NULL| | | manageruid | int(10) | YES | | NULL| | ++--+--+-+-+---+ How can I do some recursion to get the UIDs of all the employees reporting up to a manager, regardless of how deep the tree is. I can do this usindg LDAP and/or PHP, but not sure how to do it as a mysql query. Any thoughts? Thanks saqib http://www.capital-punishment.us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Linking mysqlclient to a windows program
Hello I have configured my VC++ to link my programs with mysqlclient, but now am trying a program with Qt that is compiled using make from the command line, is it enough to add mysqlclient.lib to my makefile library configuration? I mean, is it enough to add the path to the header files and mysqlclient? in *nix I use mysql_config with --libs and --include and the output is inserted during the make process, but in windows I see only the header files and the lib files... Thanks!
MySQL Installation Not Working
Hi, I've tried several times now to get a local installation of MySQL going on my iMac (w/ OS X ver. 10.5.6). I've carefully tried everything I could comprehend to do per the mysql.com resource pages/ manual. I have also tried several procedures I discovered via Google searches that seemed to be promising/authoritative. Throughout this process I have encountered so many obstacles I've lost count. I have deleted and reinstalled the whole MySQL package (mysql-5.1.32-osx10.5- x86.dmg) 4 times while trying to follow the documentation with excruciating attention to the detail and nuances. I think I have encountered every version of 'permission denied', 'unable to open socket', unable to find 'my_print_defaults', 'unable to bind on port 3306', 'unable to access pid file' type of error possible. I am absolutely drowning in barely comprehended documentation, tips and tricks that have taken me down one dead end after another. Is there any way an ordinary person who doesn't walk on water with UNIX and is not able to manufacture a supercomputer with his pocketknife can get a simple 'local' MySQL installation going on his Mac? This is an appeal for help from someone who has wrestled with the official documentation to the point that it's liable to become a textbook case of 'terminal computer- frustration'. I wasn't able to figure out exactly how to comply with your 'MUST use the ./bin/mysqlbug script!' directive so I've copied and pasted the results of running that script below (please accept my apologies if these results were supposed to be transmitted in a different way). SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: DPD To: mysql@lists.mysql.com Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator: Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-5.1.32 (MySQL Community Server (GPL)) C compiler:i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465) C++ compiler: i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465) Environment: machine, os, target, libraries (multiple lines) System: Darwin DPD-Mac.local 9.6.0 Darwin Kernel Version 9.6.0: Mon Nov 24 17:37\ :00 PST 2008; root:xnu-1228.9.59~1/RELEASE_I386 i386 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: i686-apple-darwin9 Configured with: /var/tmp/gcc/gcc-5490~1/src/configure --disable- checking -enabl\ e-werror --prefix=/usr --mandir=/share/man --enable-languages=c,objc,c+ +,obj-c++\ --program-transform-name=/^[cg][^.-]*$/s/$/-4.0/ --with-gxx-include- dir=/includ\ e/c++/4.0.0 --with-slibdir=/usr/lib --build=i686-apple-darwin9 --with- arch=apple\ --with-tune=generic --host=i686-apple-darwin9 --target=i686-apple- darwin9 Thread model: posix gcc version 4.0.1 (Apple Inc. build 5490) ** Regards, Dennis
Re: generic remote command/script for monitoring MySQL instance health
On Mon, 9 Mar 2009, Sven wrote: Hi folks I am searching for a generic command to monitor that MySQL instance is up and running. I don't have any know-how about the schema of the DB. kind regards Sven Aluoor Hi What about 'mysqladmin ping' ? Regards, Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question about LVM snapshots and innodb tables
The book “High Performance MySQL” states the following about using LVM snapshots with innodb tables: “All innodb files (InnoDB tablespace files and InnoDB transaction logs) must be on a single logical volume (partition).” Here is portion of a df command performed on one of our hosts: /dev/mapper/vg01-db 2.5T 2.0T 567G 78% /db /dev/mapper/vg00-innodb 8.0G 2.0G 6.1G 25% /db/innodb /dev/mapper/vg02-binlog 503G 140G 363G 28% /db/binlog /dev/mapper/vg06-data4 755G 652G 103G 87% /db/data /dev/mapper/vgc2-data8 6.2T 644G 5.6T 11% /db/data8 /dev/mapper/vgc3-data9 6.2T 1.8T 4.5T 29% /db/data9 Where /db/innodb contains the innodb logs and the one ibdata file. However, we use innodb_file_per_table so all the /db/datax filesystems have .ibd files (many of the tables in the datadir, /db/data, are sym-linked to /db/data8 and /db/data9 where the data actually resides. We use LVM snapshots to move the data around, since our databases are several terabytes. Does this mean our snapshots are inconsistent? There’s no way we can get all innodb data on a single partition. Thanks, Jim Lyons -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: mysqlimport remote host problem
Thanks. That works great. On 10-Mar-09, at 9:36 PM, Rob Wultsch wrote: On Tue, Mar 10, 2009 at 7:16 PM, René Fournier m...@renefournier.com wrote: OK, I've managed to do the same thing with just the mysql command line program: mysql -h 192.168.0.224 -u root -p alba2 /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql Works great. However, the sql file is normally gzipped, so Can I ungzip the file on the fly (and without removing the .gzip version) and pipe the contents as I did above? (Yes, I'm UNIX-impaired.) Something like: mysql -h 192.168.0.224 -u root -p alba2 gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz But so that it works... ...Rene Pipe is your friend. You probably want something like: gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz | mysql -h 192.168.0.224 -u root -p alba2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: generic remote command/script for monitoring MySQL instance health
On 3/11/09, Thomas Spahni t...@lawbiz.ch wrote: I am searching for a generic command to monitor that MySQL instance is up and running. I don't have any know-how about the schema of the DB. What about 'mysqladmin ping' ? Hi Thomas thank you. That was the command I searched. kind regards Sven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] RE: non-auto increment question
Ashley Sheridan wrote:
On Thu, 2009-02-26 at 11:27 -0500, PJ wrote:
Jerry Schwartz wrote:
Being rather new to all this, I understood from the MySql manual that
the auto_increment is to b e used immediately after an insertion not
intermittently. My application is for administrators (the site owner
designates) to update the database from and administration directory,
accessed by user/password login... so there's really very little
possibility of 2 people accessing at the same time.
By using MAX + 1 I keep the id number in the $idIn and can reuse it in
other INSERTS
[JS] Are you looking for something like LAST_INSERT_ID()? If you
INSERT a
record that has an auto-increment field, you can retrieve the value
that got
inserted with SELECT LAST_INSERT_ID(). It is connection-specific, so
you'll always have your own value. You can then save it to reuse,
either
as a session variable or (more easily) as a hidden field on your form.
Thanks, Jerry,
You hit the nail on the head.:)
To refine my problem (and reduce my ignorance),here's what is happening
on the form page:
There is a series of INSERTs. The first inserts all the columns of
book table except for the id, which I do not specify as it if
auto-insert.
In subsequent tables I have to reference the book.id (for transitional
tables like book_author(refers authors to book) etc.
If I understand it correctly, I must retrieve (SELECT
LAST_INSERT_ID()) after the first INSERT and before the following
insert; and save the id as a string ($id)...e.g. $sql = SELECT
LAST_INSERT_ID() AS $id
I need clarification on the AS $id - should this be simply id(does
this have to be turned into a value into $id or does $id contain the
value? And how do I retrieve it to use the returned value for the next
$sql = INSERT ... - in other words, is the id or $id available for the
next directive or do I have to do something like $id = id?
I'm trying to figure this out with some trials but my insert does not
work from a php file - but it works from command-line... that's another
post.
Here's how I mostly do it (albeit simplified):
$query = INSERT INTO `sometable`(`title`,`content`)
VALUES('$title','$content');
$result = mysql_query($query);
$autoId = mysql_insert_id($result);
$query = INSERT INTO `another_table`(`link_id`,`value`)
VALUES($autoId,'$value');
$result = mysql_query($query);
No need to call another query to retrieve the last inserted id, as it is
tied to the last query executed within this session.
Ash
www.ashleysheridan.co.uk
For some reason or other $autoId = mysql_insert_id($result); just does
not work for me... Yet some of the data is inserted correctly...
I did find that it does not work on tables that are empty... so you
can't start with an empty table. I entered data it still did not work.
I tried on another duplicate database... doesn't work.
I have checked double checked the database, I have added checks to see
what is returned and the returns are 0 or null - as I get different
responses for slightly different functions.
sessions is on
mysql is 5.1.28
php5
here's what is parsed:
else { $sql1 = INSERT INTO book
( title, sub_title, descr, comment, bk_cover,
copyright, ISBN, language, sellers )
VALUES
('$titleIN', '$sub_titleIN', '$descrIN',
'$commentIN', '$bk_coverIN', '$copyrightIN',
'$ISBNIN', '$languageIN', '$sellersIN');
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
$sql2 = INSERT INTO author (first_name, last_name) VALUES
('$first_nameIN', '$last_nameIN');
$result2 = mysql_query($sql2, $db);
$authorID = mysql_insert_id($result2);
$sql2a = INSERT INTO book_author (authID, bookID, ordinal)
VALUES ( '$authorID', '$autoid', '1');
$result2a = mysql_query($sql2a, $db);
$sql2b = INSERT INTO author (first_name, last_name) VALUES
('$first_name2IN', '$last_name2IN');
$result2b = mysql_query($sql2b, $db);
$author2ID = mysql_insert_id($result2b);
$sql2c = INSERT INTO book_author (authID, bookID, ordinal)
VALUES ( '$author2ID', '$autoid', '2');
$result2c = mysql_query($sql2c, $db);
$sql3 = INSERT INTO publishers (publisher) VALUES
('$publisherIN');
$result3 = mysql_query($sql3, $db);
$publisherID = mysql_insert_id($result3);
$sql3a = INSERT INTO book_publisher (bookID, publishers_id)
VALUES ( '$autoid', '$publisherID' );
$result3a = mysql_query($sql3a, $db);
foreach($_POST['categoriesIN'] as $category){
$sql4 = INSERT INTO book_categories (book_id, categories_id)
VALUES ($autoid, $category);
$result4 = mysql_query($sql4,$db);
}
echo $autoid; // shows: blank
echo $authorID; // shows: blank
echo $author2ID; // shows: blank
echo $publisherID; // shows: blank
echo brautoid =
Replica questions
I've got 2 questions about my newly set up master-slave replica. 1 When I run load data from master; I get an error that I do not have RELOAD privileges and then it boots the slave offline. I've run GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO root@'%' IDENTIFIED BY 'password'; flush privileges; but it still does the same thing. 2 Is there a way to get a slave to automatically re-connect as the slave without having to know the masters binlog and position? Each time it reboots I have to manually add it back in with the change master to ... command. TIA -Bryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query from HPM book to limit rows scanned doesn't appear to work as described
Using 5.0.67-0ubuntu6 on Ubuntu 8.10. I'm going through the High Performance MySQL book. I was reading section 4.4.1.8, titled MIN() and MAX(). The point of this is that MySQL doesn't optimize MIN()/MAX() very well, but it showed a supposed workaround for this. The first sample query was: SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE'; As described, this does a table scan, looking at 200 rows. The alternative was this: SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = 'PENELOPE' LIMIT 1; Which supposedly would not do a full table scan, and it seems logical. The explain output for this is the following (tabs replaced with colon): id:select_type:table:type:possible_keys:key:key_len:ref:rows:Extra 1:SIMPLE:actor:ALL:null:null:null:null:200:Using where This explain output is identical to the output for the previous query, so this workaround didn't appear to help any. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
On Wed, Mar 11, 2009 at 4:03 PM, Bryan Irvine sparcta...@gmail.com wrote: I've got 2 questions about my newly set up master-slave replica. 1 When I run load data from master; I get an error that I do not have RELOAD privileges and then it boots the slave offline. I've run GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO root@'%' IDENTIFIED BY 'password'; flush privileges; but it still does the same thing. 2 Is there a way to get a slave to automatically re-connect as the slave without having to know the masters binlog and position? Each time it reboots I have to manually add it back in with the change master to ... command. TIA -Bryan #1. This feature is deprecated. We recommend not using it anymore. It is subject to removal in a future version of MySQL. #2. Replication info should be being recorded on the master.info in your data dir. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
hi, #2. try adding the information of master into my.cnf then restart the server. thanks 2009/3/12 Bryan Irvine sparcta...@gmail.com: I've got 2 questions about my newly set up master-slave replica. 1 When I run load data from master; I get an error that I do not have RELOAD privileges and then it boots the slave offline. I've run GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO root@'%' IDENTIFIED BY 'password'; flush privileges; but it still does the same thing. 2 Is there a way to get a slave to automatically re-connect as the slave without having to know the masters binlog and position? Each time it reboots I have to manually add it back in with the change master to ... command. TIA -Bryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=rancp...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Query from HPM book to limit rows scanned doesn't appear to work as described
Hi! On Wed, Mar 11, 2009 at 8:20 PM, David M. Karr davidmichaelk...@gmail.com wrote: Using 5.0.67-0ubuntu6 on Ubuntu 8.10. I'm going through the High Performance MySQL book. I was reading section 4.4.1.8, titled MIN() and MAX(). The point of this is that MySQL doesn't optimize MIN()/MAX() very well, but it showed a supposed workaround for this. The first sample query was: SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE'; As described, this does a table scan, looking at 200 rows. The alternative was this: SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = 'PENELOPE' LIMIT 1; Which supposedly would not do a full table scan, and it seems logical. The explain output for this is the following (tabs replaced with colon): id:select_type:table:type:possible_keys:key:key_len:ref:rows:Extra 1:SIMPLE:actor:ALL:null:null:null:null:200:Using where This explain output is identical to the output for the previous query, so this workaround didn't appear to help any. But EXPLAIN is only a prediction. If you look at the changes in the Handler status variables, you'll see the second one reads fewer rows. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
That's deprecated too :-) On Wed, Mar 11, 2009 at 9:17 PM, Cui Shijun rancp...@gmail.com wrote: hi, #2. try adding the information of master into my.cnf then restart the server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
