ncomper
asked on
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,
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,
"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
http://dev.mysql.com/doc/refman/5.0/en/grant.html
ASKER
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)
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)
ASKER
GRANT REPLICATION SLAVE ON *.* TO 'repl@SlaveIPaddress' IDENTIFIED BY 'slavepass'; It reasults query ok. 0 rows affected.
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.
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.
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
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
ASKER
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.
[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.
grant is alright. but have you started the master???
RESET MASTER
RESET MASTER
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???
master-user = slave_user_name
master-host = slave.host.ip
etc are missing from the config. or have you posted only half???
ASKER
| Waiting for master to send event | MASTERIP | root | 3306 | 60 | mysql-bin.000001 | 98 | services3-relay-bin.000002
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.
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.
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)
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)
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).
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).
ASKER
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 |
| Warning | 1287 | 'LOAD DATA FROM MASTER' is deprecated; use 'mysqldump or futu
re BACKUP/RESTORE DATABASE facility' instead |
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???
are the updates happening???
did you try the dummy row option???
ASKER
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..
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..
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.
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.
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.
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.
ASKER
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?
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?
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...
once done, then start the replication
start master
start slave
play around with the dummy row in master and check the slave for updates...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Goutham
U r welcome!
RANT REPLICATION SLAVE ON *.* TO 'repl@SlaveIPaddress' IDENTIFIED BY 'slavepass';