Open Source Database Magazine - Issue One Released
I just uploaded the pdf of the summer issue of Open Source Database Magazine. Included in this issue: * A great article on the new features of Postgresql 8.4 by Robert Treat * Part one of a two part article on Perconas new XtraBackup backup program Also the news, the book shelf and Peter Brawleys Coding Corner. Downloads are available at http://www.osdbzine.net. thanks, Keith Murphy Editor Open Source Database Magazine http://www.osdbzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
dev.mysql.com is down. EOM
How do you show ALL grants for a username?
(r...@localhost) [(none)] SELECT CONCAT('SHOW GRANTS FOR \'', user
,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants;
+-+
| mygrants|
+-+
| SHOW GRANTS FOR ''@'localhost'; |
| SHOW GRANTS FOR ''@'pse01'; |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'madc'@'%'; |
| SHOW GRANTS FOR 'madc'@'10.10.10.%';|
| SHOW GRANTS FOR 'madc'@'10.10.10.42'; |
| SHOW GRANTS FOR 'madc'@'127.0.0.1'; |
| SHOW GRANTS FOR 'madc'@'localhost'; |
| SHOW GRANTS FOR 'root'@'127.0.0.1'; |
| SHOW GRANTS FOR 'root'@'localhost'; |
| SHOW GRANTS FOR 'slave'@'10.10.10.%'; |
| SHOW GRANTS FOR 'slave_user'@'%'; |
+-+
But how do I see all the grants that madc has? I would have expected the %
wildcard to work, but mysql uses it as a literal!?
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'%';
+---
-+
| Grants for m...@%
|
+---
-+
| GRANT ALL PRIVILEGES ON *.* TO 'madc'@'%' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `misc`.* TO 'madc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `elog`.* TO 'madc'@'%' WITH GRANT OPTION
|
+---
-+
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc';
+---
-+
| Grants for m...@%
|
+---
-+
| GRANT ALL PRIVILEGES ON *.* TO 'madc'@'%' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `misc`.* TO 'madc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `elog`.* TO 'madc'@'%' WITH GRANT OPTION
|
+---
-+
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@;
ERROR 1141 (42000): There is no such grant defined for user 'madc' on host
''
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump output
#mysqldump -u username -p -h mysqld_host databasename database_name.sql username is the mysqld server login name mysqld_host is the mysqld server address or hostname databasename is the database which you should dump You can use man mysqldump or mysqldump --help to get more infomation 2009/7/21 zhu dingze mysql.li...@gmail.com we need more information, such as your client and server version, the command that exactly your input etc. 2009/7/14 JingTian jingtian.seu...@gmail.com hi all, i use mysqldump to backup my database, the command line is; mysqldump -p -u -h database_name database_name.sql i find in the database_name.sql, there is a line: Not dumping tablespaces as no INFORMATION_SCHEMA.FILES table on this server can anyone tell me what does it mean? thanks very much, -- Tianjing -- Dingze Zhu We are running the best Chinese MySQL and Solaris Community in China. Welcome to visit http://www.mysqlsystems.com
Where does mysqld write a core file?
I've been trying to make mysqld write a coredump following a crash, and this morning I finally succeeded - according to mysqld.log anyway. It clearly says Writing a core file - but where to? I've checked the datadir /var/lib/mysql, whereelse might it be? /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How do you remove a user from the grant table?!!
How the F do you remove a user from the grant table?!!
The mysql.com site is down too by the way...
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc';
ERROR 1141 (42000): There is no such grant defined for user 'madc' on host
'%'
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@;
ERROR 1141 (42000): There is no such grant defined for user 'madc' on host
''
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'madc' on host
'%'
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'localhost';
+--+
| Grants for m...@localhost|
+--+
| GRANT USAGE ON *.* TO 'madc'@'localhost' |
| GRANT ALL PRIVILEGES ON `panasonic_elog`.* TO 'madc'@'localhost' |
+--+
2 rows in set (0.00 sec)
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES, GRANT OPTION FROM
'madc'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES, GRANT OPTION FROM
'madc'@'localhost';
Query OK, 0 rows affected (0.00 sec)
(r...@localhost) [(none)] GRANT ALL PRIVILEGES ON `panasonic_elog`.* TO
'madc'@'127.0.0.1' IDENTIFIED BY 'madc';
Query OK, 0 rows affected (0.00 sec)
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'127.0.0.1';
+---
--+
| Grants for m...@127.0.0.1
|
+---
--+
| GRANT USAGE ON *.* TO 'madc'@'127.0.0.1' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' |
| GRANT ALL PRIVILEGES ON `panasonic_elog`.* TO 'madc'@'127.0.0.1'
|
+---
--+
2 rows in set (0.00 sec)
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES, GRANT OPTION FROM
'madc'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
(r...@localhost) [(none)] SELECT CONCAT('SHOW GRANTS FOR \'', user
,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants;
+-+
| mygrants|
+-+
| SHOW GRANTS FOR ''@'localhost'; |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'madc'@'127.0.0.1'; |
| SHOW GRANTS FOR 'madc'@'localhost'; |
| SHOW GRANTS FOR 'root'@'127.0.0.1'; |
| SHOW GRANTS FOR 'root'@'localhost'; |
| SHOW GRANTS FOR 'slave'@'10.10.10.%'; |
| SHOW GRANTS FOR 'slave_user'@'%'; |
+-+
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES, GRANT OPTION FROM
'madc'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec)
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES, GRANT OPTION FROM
'madc'@'localhost';
Query OK, 0 rows affected (0.01 sec)
(r...@localhost) [(none)] SELECT CONCAT('SHOW GRANTS FOR \'', user
,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants;
+-+
| mygrants|
+-+
| SHOW GRANTS FOR ''@'localhost'; |
| SHOW GRANTS FOR ''@'pse05'; |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'madc'@'127.0.0.1'; |
| SHOW GRANTS FOR 'madc'@'localhost'; |
| SHOW GRANTS FOR 'root'@'127.0.0.1'; |
| SHOW GRANTS FOR 'root'@'localhost'; |
| SHOW GRANTS FOR 'slave'@'10.10.10.%'; |
| SHOW GRANTS FOR 'slave_user'@'%'; |
+-+
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'127.0.0.1';
+---
--+
| Grants for m...@127.0.0.1
|
+---
--+
| GRANT USAGE ON *.* TO 'madc'@'127.0.0.1' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' |
+---
--+
1 row in set (0.00 sec)
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES FROM 'madc'@'127.0.0.1';
ERROR 1064 (42000): 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 'FROM 'madc'@'127.0.0.1'' at line 1
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES ON 'madc'@'127.0.0.1';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your
Re: How do you show ALL grants for a username?
Use information_schema!
select * from information_schema.user_privileges where grantee like
'madc'@%;
Should get you what you need.
John Daisley
Email: john.dais...@butterflysystems.co.uk
Mobile: +44 (0)7812 451238
MySQL Certified Database Administrator (CMDBA)
MySQL Certified Developer (CMDEV)
MySQL Certified Associate (CMA)
Comptia A+ Certified Professional IT Technician
---
Life's journey is not to arrive at the grave safely in a well preserved
body, but rather to slide in sideways, thoroughly used up, totally worn
out and screaming Wow! what a ride!
On Wed, 2009-07-22 at 12:58 -0700, Daevid Vincent wrote:
(r...@localhost) [(none)] SELECT CONCAT('SHOW GRANTS FOR \'', user
,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants;
+-+
| mygrants|
+-+
| SHOW GRANTS FOR ''@'localhost'; |
| SHOW GRANTS FOR ''@'pse01'; |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'madc'@'%'; |
| SHOW GRANTS FOR 'madc'@'10.10.10.%';|
| SHOW GRANTS FOR 'madc'@'10.10.10.42'; |
| SHOW GRANTS FOR 'madc'@'127.0.0.1'; |
| SHOW GRANTS FOR 'madc'@'localhost'; |
| SHOW GRANTS FOR 'root'@'127.0.0.1'; |
| SHOW GRANTS FOR 'root'@'localhost'; |
| SHOW GRANTS FOR 'slave'@'10.10.10.%'; |
| SHOW GRANTS FOR 'slave_user'@'%'; |
+-+
But how do I see all the grants that madc has? I would have expected the %
wildcard to work, but mysql uses it as a literal!?
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'%';
+---
-+
| Grants for m...@%
|
+---
-+
| GRANT ALL PRIVILEGES ON *.* TO 'madc'@'%' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `misc`.* TO 'madc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `elog`.* TO 'madc'@'%' WITH GRANT OPTION
|
+---
-+
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc';
+---
-+
| Grants for m...@%
|
+---
-+
| GRANT ALL PRIVILEGES ON *.* TO 'madc'@'%' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `misc`.* TO 'madc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `elog`.* TO 'madc'@'%' WITH GRANT OPTION
|
+---
-+
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@;
ERROR 1141 (42000): There is no such grant defined for user 'madc' on host
''
PHP MYSQL Admin question
Trying the latest phpMYAdmin on Mac OS X. Everything is set up correct but I get #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured) And I am telling config.php where the mysql.sock file is located. Help. -- 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 MYSQL Admin question
Are you sure MySQL is running? Can you connect with any other MySQL client? On Wed, Jul 22, 2009 at 1:55 PM, Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem r...@doctor.nl2k.ab.ca wrote: Trying the latest phpMYAdmin on Mac OS X. Everything is set up correct but I get #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured) And I am telling config.php where the mysql.sock file is located. Help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Index selection problem
Maybe I'm wrong :) On Tuesday, July 21, 2009, John Daisley john.dais...@mypostoffice.co.uk wrote: On Tue, 2009-07-21 at 19:42 +0200, Morten Primdahl wrote: On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote: MySQL is unable to use your index when you use IN and/or OR on yoru column. Is this really true? No its not true! Try running OPTIMIZE TABLE on the affected table, then run the query again and see if the other index is used! I'm reading High Performance MySQL 2nd ed. these days and specifically got the impression that using IN will allow usage of the index. The below quote is from the book, and the multiple equality condition refers to an IN (...) expression. ... we draw a distinction between ranges of values and multiple equality conditions.The second query is a multiple equality condition, in our terminology. We’re not just being picky: these two kinds of index accesses perform differently. The range condition makes MySQL ignore any further columns in the index, but the multiple equality condition doesn’t have that limitation. John Daisley Email: john.dais...@butterflysystems.co.uk Mobile: +44 (0)7812 451238 MySQL Certified Database Administrator (CMDBA) MySQL Certified Developer (CMDEV) MySQL Certified Associate (CMA) Comptia A+ Certified Professional IT Technician --- Life's journey is not to arrive at the grave safely in a well preserved body, but rather to slide in sideways, thoroughly used up, totally worn out and screaming Wow! what a ride! -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- 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 MYSQL Admin question
On Thu, Jul 23, 2009 at 08:47:41AM -0500, Johnny Withers wrote: Are you sure MySQL is running? Can you connect with any other MySQL client? Turns out the php.ini was not set properly. On Wed, Jul 22, 2009 at 1:55 PM, Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem r...@doctor.nl2k.ab.ca wrote: Trying the latest phpMYAdmin on Mac OS X. Everything is set up correct but I get #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured) And I am telling config.php where the mysql.sock file is located. Help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Member - Liberal International This is doc...@nl2k.ab.ca Ici doc...@nl2k.ab.ca God, Queen and country! Beware Anti-Christ rising! Never Satan President Republic! The fool says in his heart, There is no God. They are corrupt, and their ways are vile; there is no one who does good. - Ps 53:1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqldump and access rights
Hi list, i use mysqldump --tab to create database dumps. this will produce txt and sql files. the resulting sql files is owned by the user but the resulting datafile is owned by mysql.mysql is there any way to change that ? re. wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump and access rights
# chown mysql.mysql datafile 2009/7/23 walter harms wha...@bfs.de Hi list, i use mysqldump --tab to create database dumps. this will produce txt and sql files. the resulting sql files is owned by the user but the resulting datafile is owned by mysql.mysql is there any way to change that ? re. wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=xwei...@gmail.com
RE: How do you remove a user from the grant table?!!
*mysql create user 'test'@'localhost' identified by 'pass';* Query OK, 0 rows affected (0.00 sec) *mysql GRANT CREATE, DELETE ON *.* TO 'test'@'localhost';* Query OK, 0 rows affected (0.00 sec) *mysql select * from information_schema.user_privileges where grantee like 'test'@'localhost';* ++---++--+ | GRANTEE| TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | ++---++--+ | 'test'@'localhost' | NULL | DELETE | NO | | 'test'@'localhost' | NULL | CREATE | NO | ++---++--+ 2 rows in set (0.00 sec) *mysql REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'test'@'localhost';* Query OK, 0 rows affected (0.00 sec) *mysql select * from information_schema.user_privileges where grantee like 'test'@'localhost';* ++---++--+ | GRANTEE| TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | ++---++--+ | 'test'@'localhost' | NULL | USAGE | NO | ++---++--+ * http://dev.mysql.com/doc/refman/5.0/en/revoke.htmlREVOKEhttp://dev.mysql.com/doc/refman/5.0/en/revoke.htmlremoves privileges, but does not drop mysql.user table entries. To remove a user account entirely, use DROP USERhttp://dev.mysql.com/doc/refman/5.0/en/drop-user.html mysql drop user 'test'@'localhost; mysql select * from information_schema.user_privileges where grantee like 'test'@'localhost'; Empty set (0.00 sec) *
