Submitted by (Contact Author) (Forums) on Sat, 2006-07-01 14:41. :: MySQL

This How To explain the replication in MySQL on the same machine.

You have large hits, with database driven site and performance, redundancy, security now comes in mind. DBA is now input by many fingers to seek some good solution to make happy faces. So here comes words like Cluster, Replication, Backup, Fail Over and others. So what these are? We currently discuss Replication in terms of a web based database driven site for large hits.

What Replication is and why?

Replication allows DBA to clone the database of master to another database server with same database. That include the master and slave identities. Slave make itself the exact copy of the master database server and or its databases. There may be one-one, one-many, many-one relation among master(s) and slave(s). Slave continously reads the binary log at master(binlog store the queries written at master database server) and get input to its slave database server.

What for Replication is not:

consider the solution for backup, performance, security and redundancy. There are other techniques for it.

We currently see the Replication of MySQL server at same machine, i.e master and slave running on same machine. We will discuss some issue with replication too.

Pre-requisite:

MySQL server 4.1.12 or above in source format. Download it them from http://mysql.com in source format.
You can for ease, download mysql client from http://mysql.com or put the mysql in the environmental variable. Other wise mysql would have to be called from the respective master/slave directories.
some Linux distribution. (I used Fedora Core 2).
remove dependencies for the mysql server

MySQL Master Installation and Configuration:

Download mysql-4.1.12 Source at /misc folder

tar xzvf mysql-4.1.12.tar.gz

cd /misc/mysql-4.1.12

./configure --prefix=/usr/local/mysql-master

make

make install

cd /usr/local/mysql-master/bin

./mysql_install_db

(it will create a var folder )

cd ../var

cp /misc/mysql-4.1.12/support-files/my-medium.cnf my.cnf

cd ..

groupadd mysql

useradd -g mysql mysql

chown -R root .

chown -R mysql var

chgrp -R mysql .

[mysqld]

port = 3306


socket = /usr/local/mysql-master/mysql.sock



#skip-networking // we have skip this in our case as we are doing both master and slave on same machine.



# Replication Master Server (default)

# binary logging is required for replication

log-bin



# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set


# but will not function as a master if omitted

server-id = 1

configure other settings according to your requirement or remain same, it will work!! :)
Now starts the mysql server by:

cd /usr/local/mysql-master/bin

./mysqld_safe --defaults-file=/usr/local/mysql-master/var/my.cnf &;

MySQL Slave:

Now extract mysql-4.1.12.tar.gz at different dir,

cd /opt/mysql-4.1.12

./configure --prefix=/usr/local/mysql-slave

make

make install

cd /usr/local/mysql-slave

cd bin

./mysql_install_db

(it will create a var folder )

cd ../var

cp /opt/mysql-4.1.12/support-files/my-medium.cnf my.cnf

cd ..

groupadd mysql

useradd -g mysql mysql

chown -R root .

chown -R mysql var

chgrp -R mysql .

Edit my.cnf in the var folder

[mysqld]

port  = 3307

socket  = /usr/local/mysql-slave/var/mysql.sock



#skip-networking



server-id = 2




# The replication master for this slave - required

master-host = localhost

master-user = slavedb

master-password = q1w2e3r4t5

master-port = 3306

Now starts the mysql server by:

cd /usr/local/mysql-slave/bin

./mysqld_safe --defaults-file=/usr/local/mysql-slave/var/my.cnf &

Configure Replication:

connect to mysql master by:

mysql --sock=/usr/local/mysql-master/mysql.sock

Create account at master for slave:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slavedb@"192.168.1.27/255.255.255.0" identified by 'q1w2e3r4t5'; Query OK, 0 rows affected (0.28 sec)

connect to mysql slave by:

mysql --sock=/usr/local/mysql-slave/mysql.sock

mysql> slave start;

Query OK, 0 rows affected, 1 warning (0.04 sec)

Testing:

mysql connect at master:

mysql> show master status\G;

*************************** 1. row ***************************

 File: adam-bin.000001

 Position: 227

 Binlog_Do_DB:

Binlog_Ignore_DB:


1 row in set (0.01 sec)



ERROR:

No query specified

connect mysql at slave

mysql> show slave status\G;

*************************** 1. row *************************** Slave_IO_State: Connecting to master
Master_Host: localhost
Master_User: slavedb
Master_Port: 3306
Connect_Retry: 60

Master_Log_File: adam-bin.000001

Read_Master_Log_Pos: 4
Relay_Log_File: adam-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: adam-bin.000001

Slave_IO_Running: Yes

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: 4
Relay_Log_Space: 4
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
1 row in set (0.00 sec)

ERROR:
No query specified

binlog file at masterand its position must be same in mysql slave by show slave status\G;

If every things fine, you are running a working copy of one-one master and slave relation on same system.

Some Issues:

Replication may be fail because of number of issues. I share some of my experience with replication.

issue: replication fails, master got down.

Master could be down due to many of the reasons. Do CHECK the FILE LIMITS IN DATABASE, SQL QUERIES AND DISK USAGE. If any of the reason shows, fix that, restart mysqld and check master status:

mysql> show master status\G;

*************************** 1. row ***************************

adam-bin.000003

Position: 227

Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec)

now check slave: by show slave status:

mysql> show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: localhost
Master_User: slavedb
Master_Port: 3306
Connect_Retry: 60

Master_Log_File: adam-bin.000001

Read_Master_Log_Pos: 4

Relay_Log_File: adam-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: adam-bin.000001

Slave_IO_Running: No

Slave_SQL_Running: Yes

mysql>; stop slave;

mysql> change master to master_log_file='adam-bin.000003', master_log_pos=227;

mysql> start slave;

Now check with show slave status\G; its working fine.

Issue 2: At Slaves if duplicate error key appear

i.e
At slave

Mysql> show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.152
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: adam-bin.000048
Read_Master_Log_Pos: 317714810
Relay_Log_File: db4-relay-bin.000001
Relay_Log_Pos: 290512385
Relay_Master_Log_File: adam-bin.000048
Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062

Last_Error: Error 'Duplicate entry 'dekq5g820avnfdmar5qi9dkhv3' for key 1' on query. Default database:'session_sql'. Query: 'INSERT INTO sessi ons5 VALUES ('dekq5g820avnfdmar5qi9dkhv3', UNIX_TIMESTAMP(NOW()) + 18000, 'redir ect|i:1;')'

Skip_Counter:0
Exec_Master_Log_Pos: 290512419
Relay_Log_Space: 317714776
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
1 row in set (0.00 sec)

That means that by unavailablilty of master, slave was read, or unavailability of slave, master was updated, so a query got two primary keys found for one entry which is a mess up.

So fixing it is by:
At slave:

Mysql> set global sql_slave_skip_counter=1;

Mysql> start slave;

Mysql> show slave status\G;

This will show the synchronized value with master. Fixed.

How To Set Up Database Replication In MySQL - Page 2

Submitted by falko (Contact Author) (Forums) on Tue, 2005-11-29 23:55. ::

2 Configure The Slave

On the slave we first have to create the database exampledb:

mysql -u root -p
Enter password:
CREATE DATABASE exampledb;
quit;

If you have made an SQL dump of exampledb on the master and have transferred it to the slave, then it is time now to import the SQL dump into our newly created exampledb on the slave:

mysql -u root -p<password> exampledb < /path/to/exampledb.sql (Replace <password> with the real password for the MySQL user root! Important: There is no space between -p and <password>!)

If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do right now.

Now we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.100, and that the master database to watch is exampledb. Therefore we add the following lines to /etc/mysql/my.cnf:

server-id=2
master-host=192.168.0.100
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=exampledb

Then we restart MySQL:

/etc/init.d/mysql restart

If you have not imported the master exampledb with the help of an SQL dump, but want to go the LOAD DATA FROM MASTER; way, then it is time for you now to get the data from the master exampledb:

mysql -u root -p
Enter password:
LOAD DATA FROM MASTER;
quit;

If you have phpMyAdmin installed on the slave you can now check if all tables/data from the master exampledb is also available on the slave exampledb.

Finally, we must do this:

mysql -u root -p
Enter password:
SLAVE STOP;

In the next command (still on the MySQL shell) you have to replace the values appropriately:

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
MASTER_USER is the user we granted replication privileges on the master.
MASTER_PASSWORD is the password of MASTER_USER on the master.
MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

Now all that is left to do is start the slave. Still on the MySQL shell we run

START SLAVE;
quit;

That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!

Links

MySQL: http://www.mysql.com

MySQL Replication On The Same Machine