Replication and Grants nightmare
Hi, I'm running mysql-max 4.0.18 on two servers on separate networks, master is A and slave is B. The problem is that if I don't grant on the master the full range of privileges to the slave the replication doesn't even start. Even worse is the fact that grants seems to be totally managed at random, as you can see: (A and B obviously are fake names). mysql A GRANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER, RELOAD on *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc'; OK mysql A show grants for replica@'B'; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT RELOAD ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' WITH GRANT OPTION | ++ 1 row in set (0.00 sec) - I didn't grant (yet) any option, so why does it say 'WITH GRANT OPTION' ? - where have all the other privileges gone? they vanished mysql A revoke ALL PRIVILEGES on *.* from replica@'B'; # identified by 'RC_rpl!'; Query OK, 0 rows affected (0.00 sec) mysql A flush privileges; Query OK, 0 rows affected (0.00 sec) mysql A show grants for replica@'B'; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' WITH GRANT OPTION | +--+ 1 row in set (0.00 sec) where did it take these rights from? I revoked them all Now the replication part: mysql A GRANT super, reload, replication client, replication slave ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql A flush privileges; Query OK, 0 rows affected (0.00 sec) these are the logs on B: 040430 11:10:34 InnoDB: Started /usr/sbin/mysqld-max: ready for connections. Version: '4.0.18-Max-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 040430 11:10:34 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db-bin.3321' at position 34997604 040430 11:10:34 While trying to obtain the list of slaves from the master 'A:3306', user 'replica' got the following error: 'Access denied. You need the REPLICATION SLAVE privilege for this operation' 040430 11:10:34 Slave I/O thread exiting, read up to log 'db-bin.3321', position 34997604 What is going wrong? P.S. the manual doesn't say that grants super, reload, replication client and replication slave can be used only on *.* and not on DB.*. Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
P.S. the manual doesn't say that grants super, reload, replication client and replication slave can be used only on *.* and not on DB.*. Each of those privileges is listed as an administrative privilege here: http://dev.mysql.com/doc/mysql/en/Privileges_provided.html There are not listed as database or table privileges. I'm not sure what it could mean for them to be database-specific, actually. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
Alle Friday 30 April 2004 15:22, Victoria Reznichenko ha scritto: Hmm.. Your queries worked fine for me: ANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER, RELOAD ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc'; Query OK, 0 rows affected (0.00 sec) mysql show grants for replica@'B'; +-- + | Grants for [EMAIL PROTECTED] || +-- + | GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO | 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' | +-- + 1 row in set (0.01 sec) mysql revoke ALL PRIVILEGES ON *.* FROM replica@'B'; Query OK, 0 rows affected (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql show grants for replica@'B'; +-- -+ | Grants for [EMAIL PROTECTED] | | +-- -+ | GRANT USAGE ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD | '715a443962d324cc' | +-- -+ 1 row in set (0.00 sec) Did you have account for 'replica'@'B' before? neither other users with the same name nor other entries for the same host Are the above queries exact that you used? yes, except the host names Which OS do you use? the master is a Redhat 7.3 with Mysql-max 4.0.18 (binary rpms from www.mysql.com), the slave is a very old Cobalt 6.0 with Mysql-max 4.0.18 compiled from the .src.rpm. Thanks for your help -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
Alle Friday 30 April 2004 15:51, hai scritto:
P.S. the manual doesn't say that grants super, reload, replication client
and replication slave can be used only on *.* and not on DB.*.
Each of those privileges is listed as an administrative privilege here:
http://dev.mysql.com/doc/mysql/en/Privileges_provided.html
There are not listed as database or table privileges.
I'm not sure what it could mean for them to be database-specific, actually.
so I should have columns Repl_client_priv and Repl_slave_priv ?
my mysql db doesn't have them:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ mysql;
USE mysql;
--
-- Table structure for table `columns_priv`
--
CREATE TABLE columns_priv (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Table_name char(64) binary NOT NULL default '',
Column_name char(64) binary NOT NULL default '',
Timestamp timestamp(14) NOT NULL,
Column_priv set('Select','Insert','Update','References') NOT NULL default
'',
PRIMARY KEY (Host,Db,User,Table_name,Column_name)
) TYPE=MyISAM COMMENT='Column privileges';
--
-- Table structure for table `db`
--
CREATE TABLE db (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,Db,User),
KEY User (User)
) TYPE=MyISAM COMMENT='Database privileges';
--
-- Table structure for table `func`
--
CREATE TABLE func (
name char(64) binary NOT NULL default '',
ret tinyint(1) NOT NULL default '0',
dl char(128) NOT NULL default '',
type enum('function','aggregate') NOT NULL default 'function',
PRIMARY KEY (name)
) TYPE=MyISAM COMMENT='User defined functions';
--
-- Table structure for table `host`
--
CREATE TABLE host (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,Db)
) TYPE=MyISAM COMMENT='Host privileges; Merged with database privileges';
--
-- Table structure for table `tables_priv`
--
CREATE TABLE tables_priv (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Table_name char(60) binary NOT NULL default '',
Grantor char(77) NOT NULL default '',
Timestamp timestamp(14) NOT NULL,
Table_priv
set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter')
NOT NULL default '',
Column_priv set('Select','Insert','Update','References') NOT NULL default
'',
PRIMARY KEY (Host,Db,User,Table_name),
KEY Grantor (Grantor)
) TYPE=MyISAM COMMENT='Table privileges';
--
-- Table structure for table `user`
--
CREATE TABLE user (
Host char(60) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Password char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Reload_priv enum('N','Y') NOT NULL default 'N',
Shutdown_priv enum('N','Y') NOT NULL default 'N',
Process_priv enum('N','Y') NOT NULL default 'N',
File_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,User)
) TYPE=MyISAM COMMENT='Users and global privileges';
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
At 16:22 +0200 4/30/04, Nico Sabbi wrote:
Alle Friday 30 April 2004 15:51, hai scritto:
P.S. the manual doesn't say that grants super, reload, replication client
and replication slave can be used only on *.* and not on DB.*.
Each of those privileges is listed as an administrative privilege here:
http://dev.mysql.com/doc/mysql/en/Privileges_provided.html
There are not listed as database or table privileges.
I'm not sure what it could mean for them to be database-specific, actually.
so I should have columns Repl_client_priv and Repl_slave_priv ?
my mysql db doesn't have them:
Indeed you should (in the user table only), and several other columns
as well.
Perhaps that is a clue to what is happening. Did you upgrade from a
release older than 4.0.2 to a release 4.0.2 or newer at some point,
without running the mysql_fix_privilege_tables script? If so, then
please read this:
http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html
--
-- Table structure for table `user`
--
CREATE TABLE user (
Host char(60) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Password char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Reload_priv enum('N','Y') NOT NULL default 'N',
Shutdown_priv enum('N','Y') NOT NULL default 'N',
Process_priv enum('N','Y') NOT NULL default 'N',
File_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,User)
) TYPE=MyISAM COMMENT='Users and global privileges';
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
Alle Friday 30 April 2004 16:44, hai scritto: At 16:22 +0200 4/30/04, Nico Sabbi wrote: Alle Friday 30 April 2004 15:51, hai scritto: P.S. the manual doesn't say that grants super, reload, replication client and replication slave can be used only on *.* and not on DB.*. Each of those privileges is listed as an administrative privilege here: http://dev.mysql.com/doc/mysql/en/Privileges_provided.html There are not listed as database or table privileges. I'm not sure what it could mean for them to be database-specific, actually. so I should have columns Repl_client_priv and Repl_slave_priv ? my mysql db doesn't have them: Indeed you should (in the user table only), and several other columns as well. Perhaps that is a clue to what is happening. Did you upgrade from a release older than 4.0.2 to a release 4.0.2 or newer at some point, without running the mysql_fix_privilege_tables script? If so, then please read this: http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html done, it works correctly now. Thanks very much, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
