MySQL Replication grant replication error

I have installed MySQL 5 in two windows 2003 servers. I have try to create the SQL replication between two MySQL servers. Configured My.ini file in Master server into
log-bin=mysql-bin
server-id=1
and Slave server into
server-id=2
master-host ='MasterServerIP'
master-port = 3306
master-user = root
master-password = root

After that i execute the command :
GRANT REPLICATION SLAVE ON Test.* TO 'repl@SlaveIPaddress' IDENTIFIED BY 'slavepass';

Both Master and Slave have the DB Name as Test. When i execute the above command it shows the following error message:
 Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

I have follow the instructions from MySQL site. Can any one help me in MySQL replication? If possible. Explain step by step procedure for both Master and Slave configuration.

Thanks,
LVL 5
ncomperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

todd_farmerCommented:
You can't grant privileges for replication slave on a per-database level - it has to be for all databases on the server instance:

RANT REPLICATION SLAVE ON *.* TO 'repl@SlaveIPaddress' IDENTIFIED BY 'slavepass';
0
todd_farmerCommented:
"The FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER  privileges are administrative privileges that can only be granted globally (using ON *.* syntax)."

http://dev.mysql.com/doc/refman/5.0/en/grant.html
0
ncomperAuthor Commented:
Thanks. I have executed using GRANT REPLICATION SLAVE ON *.* TO 'repl@SlaveIPaddress' IDENTIFIED BY 'slavepass'; this command. Can you tell me the solution for below problem

SLAVE SERVER:
master-host = 'MasterIPAddress'
master-port = 3306
master-user = root
master-password = root
master-connect-retry=60
replicate-do-db=Test

mysql> start slave;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    14
Current database: *** NONE ***

Query OK, 0 rows affected, 1 warning (0.39 sec)
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

ncomperAuthor Commented:
GRANT REPLICATION SLAVE ON *.* TO 'repl@SlaveIPaddress' IDENTIFIED BY 'slavepass'; It reasults query ok. 0 rows affected.
0
ygouthamCommented:
http://www.neocodesoftware.com/replication/

check the master configuration for the log file entry.  once done, i guess you need to do a

RESET MASTER

and then do a

START SLAVE

it is crucial to have the latest copy before doing a RESET MASTER.  The slave db should be the SAME as master before start of replication.
0
ygouthamCommented:
more often it is better to give the

log-bin

entry in the master MY.INI as an absolute path than a relative path.  see where the log file sits and try to hard code it in the slave and master
0
ncomperAuthor Commented:
Hi Goutham,

[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306


#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"

#Path to the database root
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
log-bin="C:/Program Files/MySQL/MySQL Server 5.0/Data/mysql-bin.000001"
binlog-do-db=Test
server-id=1

This is the code in my.ini file. when i type the grant replication in Mysqlcommand prompt it show Query ok; 0 rows affected. Show slave hosts; display empty set. It is an very urgent issue for me. Guide me in this issue.
0
ygouthamCommented:
grant is alright.  but have you started the master???

RESET MASTER
0
ygouthamCommented:
i think the parts of

master-user = slave_user_name
master-host = slave.host.ip

etc are missing from the config.  or have you posted only half???
0
ncomperAuthor Commented:
| Waiting for master to send event | MASTERIP | root |  3306 |       60 | mysql-bin.000001 |    98 | services3-relay-bin.000002 | 235 | mysql-bin.000001 | Yes | Yes
This is the details while am executing the SHOW SLAVE STATUS;.
This command executed means replication is done right?

But in every command execution it give the result as "QUERY IS OK; 0 ROWS AFFECTED;" I am new to MySQL replication that's why so many questions.

If am wrong. just give me a steps i have to do in Master and Slave.
0
ygouthamCommented:
your slave is on and running alright.

what you need to ensure are the following steps.

stop master
stop slave

take a mysqldump of the database you are trying to copy from the master and import into the slave.

select count(primary_key_field) from database_name;

should show the same number on master AND slave.  once done

start master
start slave

check by doing a dummy row insertion into the master and QUERY SLAVE to see if it got added into the slave.

change the dummy row details to see that the changes are effected in SLAVE.

delete the dummy row in MASTER and THEN QUERY SLAVE to see if the row is deleted.

if all is well, go home a happier person :o)
0
ygouthamCommented:
the only thing that i noticed is that i do not have the single quotes around the master ip in the my.cnf file at my end

master-host = 10.20.30.40

i did my entire stuff using

http://www.howtoforge.com/mysql_database_replication

I see that you have a windows machine.  replace paths which are given as /var/lib/mysql to D:\mysql\blah\blah

otherwise you need to ensure that the master and slave have the same version of mysql (worse case the slave should have a higher version of mysql than the master).
0
ncomperAuthor Commented:
Thanks Goutham. It works upto final step. In the final step it shows this message.

| Warning | 1287 | 'LOAD DATA FROM MASTER' is deprecated; use 'mysqldump or futu
re BACKUP/RESTORE DATABASE facility' instead |

0
ygouthamCommented:
we need not bother about the load data from master.  the import / export of the databases can be safely handled through the mysqldump route.

are the updates happening???  

did you try the dummy row option???
0
ncomperAuthor Commented:
I have the mysqlbrowser and mysql command prompt. I have installed Mysql in Windows server.

Mysqldump is not working in mysql command prompt. Is that command work in windows machine or only in shell?

I typed this command in Master server. I did the SQL server 2000 replication very easily.
I have created the DB backup from my local machine using phpmyadmin.After that i place that sql code into both servers and create the DB and tables.

This all the steps i have done. If we can use mysqldump command then guide me how to create it..
0
ygouthamCommented:
mysqldump is a command line command.  it DOES NOT work from MYSQL prompt.

get to command prompt. get to the mysql directory and then use

mysqldump -uroot database_name  > backupdump.sql

it takes the root password for mysql first and then this will create a file called backupdump.sql in the same directory (or change the path for the output).  

COPY the file to the SLAVE machine and then

mysql database_name < backupdump.sql

that should take it into the specific database.  keep it the same name with CASE SenSiTiVE options.
0
ygouthamCommented:
from the MASTER mysql command prompt

show create table table_name;

if it ends with "  ) TYPE=MyISAM " at the end, then you can simply copy the files or the entire folder containing the files to the SLAVE machine.  if it is INNODB then you MUST USE mysqldump only.

if it is a large database with toooooo many tables then mysqldump is a better utility.  please ensure that the users are not updating the table when you are dumping and restoring.
0
ncomperAuthor Commented:
Master Database Table structure:

 CREATE TABLE `Test` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

Slave Database Table structure:

CREATE TABLE `Test` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

This is an strucre of DB. This is an test servers.So no one use this DB. Enigne is not INNODB.
Only one table in that database. If i must use mysqldump, how can i use that one?
0
ygouthamCommented:
normally databases are created as folders in mysql.  you can copy the entire folder between the servers and see that it has the same kind of data between the servers.

once done, then start the replication

start master
start slave

play around with the dummy row in master and check the slave for updates...
0
ygouthamCommented:
in master

insert into Test (name) values ('ygoutham');

in slave

select * from Test where name = 'ygoutham';

should ideally return a row.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ncomperAuthor Commented:
Thanks Goutham
0
ygouthamCommented:
U r welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.