[SOLVED] Error while configuration of mysql replication in centos

jayakumar01 · 04-06-2012, 03:20 AM

Hi
I try to replicte mysql database when database update on master server to get update on slave.I followed the following link for configuration ,but whenever mysql database getting on matser server does int getting replicate on slave .Can anyone help me whether its possible to replicate mysql database from master to slave

http://www.howtoforge.com/mysql_database_replication

Client

Quote:

root@dinesh-desktop:~# scp root@10.200.2.34:/tmp/pommo.sql /tmp
root@10.200.2.34's password:
pommo.sql 100% 16KB 15.8KB/s 00:00
root@dinesh-desktop:~# cd /tmp
root@dinesh-desktop:/tmp# ls
gedit.root.975254361 orbit-gdm pommo.sql ssh-kkDxNe1685

root@dinesh-desktop:/tmp# cd
root@dinesh-desktop:~# mysql -uroot -ppassword
root@dinesh-desktop:~# mysql -uroot -ppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 196
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database pommo;
Query OK, 1 row affected (0.02 sec)

mysql>
Aborted
root@dinesh-desktop:~# mysql -uroot -ppassword pommo < pommo.sql
-bash: pommo.sql: No such file or directory
root@dinesh-desktop:~# mysql -uroot -ppassword pommo </tmp/ pommo.sql
mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1

root@dinesh-desktop:~# mysql -uroot -ppassword pommo </tmp/pommo.sql
root@dinesh-desktop:~# vi /etc/mysql/my.cnf
root@dinesh-desktop:~# vi /etc/mysql/my.cnf
server-id=2
master-host=10.200.2.34
master-user=slave_user
master-password=ecare2@
master-connect-retry=60
replicate-do-db=pommo

vi /etc/mysql/my.cnf
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
server-id=2
master-host=10.200.2.34 ---server ipadress
master-user=slave_user
master-password=password
master-connect-retry=60
replicate-do-db=pommo

Quote:

server side --10.200.2.34

mysql -uroot -ppassword
> Create databases pommo;

mysql> use pommo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
Empty set (0.00 sec)

mysql> show slave status\G;
Empty set (0.00 sec)

ERROR:
No query specified

cat /etc/mysql/my.cnf
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_allowed_packet=1G
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=pommo
server-id=1

TB0ne · 04-06-2012, 11:29 AM

Quote:

Originally Posted by jayakumar01

Hi
I try to replicte mysql database when database update on master server to get update on slave.I followed the following link for configuration ,but whenever mysql database getting on matser server does int getting replicate on slave .Can anyone help me whether its possible to replicate mysql database from master to slave

http://www.howtoforge.com/mysql_database_replication

First, very possible, and it's done all the time. Second, you followed a tutorial from 2006...did you try to look up a CURRENT setup guide, from MySQL?

https://dev.mysql.com/doc/refman/5.1...plication.html

Might also help to tell us what didn't work, what you're seeing in the logs, version/distro of Linux and version of MySQL. Just saying "didn't replicate" tells us little.

jayakumar01 · 04-07-2012, 01:46 AM

Hi

i had followed the below documentation for mysql database replication for mysql 5.0 centos 5.4 distro operating system

http://studioshorts.com/blog/2010/03...n-centos-rhel/

,and but im facing out error in Client side Slave_IO_Running: No.Its delivering mysql connection to master had failed.B.i could able to connect the server from client using graphical interface sqlyog.

Client Side

Server version: 5.1.41-3ubuntu12.10 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Quote:

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.200.2.34
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 452
Relay_Log_File: mysql-relay-bin.000008
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 452
Relay_Log_Space: 261
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2013
Last_IO_Error: error connecting to master 'root@10.200.2.34:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Quote:

tail -f error.log
120407 11:57:36 [ERROR] Slave I/O: error connecting to master 'root@10.200.2.34:3306' - retry-time: 60 retries: 86400, Error_code: 2003
120407 11:58:36 [ERROR] Slave I/O: error connecting to master 'root@10.200.2.34:3306' - retry-time: 60 retries: 86400, Error_code: 2013

jayakumar01 · 04-07-2012, 02:39 AM

Thanks Tbone for ur reply

Grant root@10.200.2.34 master server using % privileges my problem get solved

tail -f /var/log/mysql/error.log
120407 12:24:37 [Note] Plugin 'FEDERATED' is disabled.
120407 12:24:37 InnoDB: Started; log sequence number 0 530754542
SSL error: Unable to get certificate from '/etc/mysql/newcerts/server-cert.pem'
120407 12:24:37 [Warning] Failed to setup SSL
120407 12:24:37 [Warning] SSL error: Unable to get certificate
120407 12:24:37 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000006' at position 452, relay log '/var/lib/mysql/mysql-relay-bin.000008' position: 4
120407 12:24:37 [Note] Event Scheduler: Loaded 0 events
120407 12:24:37 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
120407 12:24:37 [Note] Slave I/O thread: connected to master 'root@10.200.2.34:3306',replication started in log 'mysql-bin.000006' at position 452