Solved

MySQL Replication grant replication error

Posted on 2007-03-28
22
918 Views
Last Modified: 2008-01-09
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,
0
Comment
Question by:ncomper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
  • 2
22 Comments
 
LVL 30

Expert Comment

by:todd_farmer
ID: 18806424
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
 
LVL 30

Expert Comment

by:todd_farmer
ID: 18806428
"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
 
LVL 5

Author Comment

by:ncomper
ID: 18806555
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 5

Author Comment

by:ncomper
ID: 18806697
GRANT REPLICATION SLAVE ON *.* TO 'repl@SlaveIPaddress' IDENTIFIED BY 'slavepass'; It reasults query ok. 0 rows affected.
0
 
LVL 14

Expert Comment

by:ygoutham
ID: 18806741
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
 
LVL 14

Expert Comment

by:ygoutham
ID: 18806749
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
 
LVL 5

Author Comment

by:ncomper
ID: 18806983
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
 
LVL 14

Expert Comment

by:ygoutham
ID: 18807318
grant is alright.  but have you started the master???

RESET MASTER
0
 
LVL 14

Expert Comment

by:ygoutham
ID: 18807388
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
 
LVL 5

Author Comment

by:ncomper
ID: 18807732
| 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
 
LVL 14

Expert Comment

by:ygoutham
ID: 18807908
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
 
LVL 14

Expert Comment

by:ygoutham
ID: 18808006
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
 
LVL 5

Author Comment

by:ncomper
ID: 18814470
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
 
LVL 14

Expert Comment

by:ygoutham
ID: 18814533
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
 
LVL 5

Author Comment

by:ncomper
ID: 18814632
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
 
LVL 14

Expert Comment

by:ygoutham
ID: 18814680
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
 
LVL 14

Expert Comment

by:ygoutham
ID: 18814697
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
 
LVL 5

Author Comment

by:ncomper
ID: 18814823
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
 
LVL 14

Expert Comment

by:ygoutham
ID: 18814871
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
 
LVL 14

Accepted Solution

by:
ygoutham earned 500 total points
ID: 18814880
in master

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

in slave

select * from Test where name = 'ygoutham';

should ideally return a row.
0
 
LVL 5

Author Comment

by:ncomper
ID: 18815483
Thanks Goutham
0
 
LVL 14

Expert Comment

by:ygoutham
ID: 18815568
U r welcome!
0

Featured Post

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question