Solved

MySQL Replication grant replication error

Posted on 2007-03-28
22
896 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
  • 12
  • 8
  • 2
22 Comments
 
LVL 30

Expert Comment

by:todd_farmer
Comment Utility
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
Comment Utility
"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
Comment Utility
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
 
LVL 5

Author Comment

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

Expert Comment

by:ygoutham
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
grant is alright.  but have you started the master???

RESET MASTER
0
 
LVL 14

Expert Comment

by:ygoutham
Comment Utility
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
Comment Utility
| 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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Expert Comment

by:ygoutham
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Goutham
0
 
LVL 14

Expert Comment

by:ygoutham
Comment Utility
U r welcome!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now