MySQL Replication - Master - Slave ( One Way Replication)

How to Setup MySQL Replication

Configure the Master Database :

First you will need to configure the my.cnf file.  The my.cnf file is not located in the same place on all servers, so you should use the ps command to list how mysql was started.

[root@hostname repl_setup]$ ps -ef | grep mysql

mysql 6290 1 0 May18 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/apps/mysql/my.cnf --pid-file=/apps/mysql/mysql.pid

mysql 6318 6290 0 May18 ? 00:48:49 /usr/sbin/mysqld --defaults-file=/apps/mysql/my.cnf --basedir=/usr --datadir=/apps/mysql --pid-file=/apps/mysql/mysql.pid --skip-external-locking --port=3300 --socket=/apps/mysql/mysql.sock

mysql 6473 1 0 May18 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/apps2/mysql/my.cnf --pid-file=/apps2/mysql/mysql.pid

mysql 6501 6473 0 May18 ? 00:19:18 /usr/sbin/mysqld --defaults-file=/apps2/mysql/my.cnf --basedir=/usr --datadir=/apps2/mysql --pid-file=/apps2/mysql/mysql.pid --skip-external-locking --port=3302 --socket=/apps2/mysql/mysql.sock

root 28389 25247 0 15:51 pts/0 00:00:00 grep mysql

In the above example, there are two instances of mysql running on this server, and thus 2 different my.cnf files.  You will need to edit both files.  In this example I will focus on the databases for the /apps/mysql/my.cnf file.

cd /apps/mysql

vi my.cnf

[mysqld]

basedir=/usr

datadir=/apps/mysql

socket=/apps/mysql/mysql.sock

port=3300

# InnoDB settings

default-storage-engine=INNODB

innodb_data_home_dir = /apps/mysql/

innodb_data_file_path = ibdata1:100M:autoextend

innodb_buffer_pool_size=100M

innodb_additional_mem_pool_size=10M

innodb_flush_log_at_trx_commit=1

init_connect='SET collation_connection = utf8_general_ci'

init_connect='SET NAMES utf8'

default-character-set=utf8

character-set-server=utf8

collation-server=utf8_general_ci

max_allowed_packet=32M

innodb_log_file_size=100M

innodb_log_buffer_size=8M

max_binlog_size=100M

log-bin=mysql-bin

server-id=30

[mysql]

socket=/apps/mysql/mysql.sock

port=3300

default-character-set=utf8

init_connect='SET collation_connection = utf8_general_ci'

init_connect='SET NAMES utf8'

character-set-server=utf8

collation-server=utf8_general_ci

Set the max_binlog_size to 100M (the default is 1GB and may be too big for most servers), turn binary logging on with log-bin=mysql-bin, and set the server_id to a unique value.

Then we restart MySQL:

/etc/init.d/mysql restart

Then we log into the MySQL database as “root” and create a user with replication privileges:

mysql -u root -p

Enter password:

Now we are on the MySQL shell.

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '<some_password>';

GRANT * ON *.* TO 'repl'@'%' IDENTIFIED BY '<some_password>';

(Replace <some_password> with a real password!)

FLUSH PRIVILEGES;

Get a list of Databases, for each database you want to replicate:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| CD52 |

| consolidata |

| globallink |

| globallink42 |

| globallink421 |

| globallink421bc |

| gwapp |

| gwts |

| mysql |

| somedb |

| teamsite |

| ts_events |

| ts_reports |

| webdb |

| workdb |

+--------------------+

16 rows in set (0.03 sec)

Next (still on the MySQL shell) do this for each database you want to replicate:

USE CD52;

FLUSH TABLES WITH READ LOCK;

RESET MASTER;

START MASTER;

SHOW MASTER STATUS;

The last command will show something like this:

mysql> show master status;

+------------------+-----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+-----------+--------------+------------------+

| mysql-bin.000361 | 210425592 | | |

+------------------+-----------+--------------+------------------+

1 row in set (0.01 sec)

Write down this information, we will need it later on the slave! Then leave the MySQL shell:

quit;

Configure the Slave

We have to tell MySQL on the slave that it is the slave, that the master is 10.70.30.161, and that the master database to watch is CD52. Therefore we add the following lines to/apps/mysql/my.cnf:

cd /apps/mysql

vi my.cnf

server-id=31

Then we restart MySQL:

/etc/init.d/mysql restart

Then load the Databases from the Masters:

mysql -u root –p –socket=/apps/mysql/mysql.sock

Enter password:

RESET SLAVE;

LOAD DATA FROM MASTER;

quit;

Finally, we must do this:

mysql -u root –p –socket=/apps/mysql/mysql.sock

Enter password:

SLAVE STOP;

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

RESET SLAVE;

CHANGE MASTER TO MASTER_HOST='ibm_master.ibm.com', MASTER_USER='repl', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.000361', MASTER_LOG_POS=210425592;

• MASTER_HOST is the IP address or hostname of the master (in this example it is 10.70.30.161).

• 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 the following command from mysql:

START SLAVE;

quit;

Unlock Master Tables

mysql -u root -p

Enter password:

USE CD52;

UNLOCK TABLES;

quit;