MySQL Master-SLave Replication??

linuxshadow · 06-11-2008, 03:28 AM

I Followed the doc: http://members.cox.net/midian/howto/...eplication.htm for setting Up Master-Slave Replication but stuck with the following step:

Code:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin',MASTER_LOG_POS=/var/log/mysqld.log;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/var/log/mysqld.log' at line 1

What is the correct command for this?

Alternately I tried entering this into /etc/my.cnf on client side:

Code:

server-id=2
master-host=10.14.2.146
master-user=repl
master-password=mysql123
master-connect-retry=60
replicate-do-db=helpcore
master-port = 3306

When I run the following commad at slave side:

Code:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000038 |    14152 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

The same at the Master side are:

Code:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |     2383 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

I followed step by step configuration but its throwing error:

Code:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin',MASTER_LOG_POS=/var/log/mysqld.log;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/var/log/mysqld.log' at line 1

Pls Help !!!

p_s_shah · 06-11-2008, 06:30 AM

***************
Post was wrongly updated. So, removing post to avoid confusion.
One can still refer following links.
***************

Below links also implements the same concept of Replication in some more details.
Refer below links:

http://howtoforge.com/loadbalanced_mysql_cluster_debian
http://howtoforge.com/mysql_master_master_replication
http://howtoforge.com/mysql_database_replication

And other mysql related links on the site.

trickykid · 06-11-2008, 08:47 AM

Actually MASTER_LOG_POS is the actual position in the log file it's currently on and the MASTER_LOG_FILE is the actual file it's currently on which would be mysql-bin.000038

This is what they should look like in your query:

MASTER_LOG_FILE='mysql-bin.000038', MASTER_LOG_POS=2383;

As for the error you are getting, you would have to wrap the /var/log/mysqld around ' ' if it was actually the correct way.

The tutorial is correct that you're following, you're just putting in the wrong values and anything that isn't just a numerical value always has to have ' ' around the value.

linuxshadow · 06-12-2008, 10:49 AM

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-replication.log',MASTER_LOG_POS=/var/log;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/var/log' at line 1
mysql>

lord-fu · 06-12-2008, 10:54 AM

MASTER_LOG_POS needs to be an integer not wrapped in quotes like trickykid said, not a /var/log.

hth

trickykid · 06-12-2008, 05:09 PM

Quote:

Originally Posted by linuxshadow

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-replication.log',MASTER_LOG_POS=/var/log;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/var/log' at line 1
mysql>

Did you even read my reply? I even gave you the correct values from your first post and output.

This is what it should look like:

Code:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000038', MASTER_LOG_POS=2383;

But if you're master is still running, you'll need to rerun the:

Code:

mysql> show master status;

To get the new MASTER_LOG_FILE in use and the MASTER_LOG_POS integer value.

linuxshadow · 06-13-2008, 12:42 AM

Thanks for the reply.I did changed the command but why its not starting the slave

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.0000038',MASTER_LOG_POS=2383;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
mysql> show slave status;
Empty set (0.00 sec)

The my.cnf file entry I made is :

Code:

server-id=2
master-host=10.14.2.146
master-user=repl
master-password=mysql123
master-connect-retry=60
replicate-do-db=helpcore
master-port = 3306

trickykid · 06-13-2008, 10:22 AM

Try running the same query but specifying the host, user, password which seems to help when first initializing the slave even though it's in your config but since you don't specify the LOG_POS and LOG_FILE since you wouldn't want to start from that same position after a reboot as it changes:

Code:

mysql> CHANGE MASTER TO MASTER_HOST='10.14.2.146', MASTER_USER='repl', MASTER_PASSWORD='mysql123', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000038', MASTER_LOG_POS=2383;

mysql> start slave;