Solved

MySQL Replication grant replication error

Posted on 2007-03-28
22
911 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysql Left Join Case 10 105
Upgrade MySQL 5.5 to MySQL 5.6 on Windows 13 82
database connection error mysql stops 7 31
WordPress  Failed to Import Media 6 11
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 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