MySQL Replication Document@CA

As Root User on Server 1 (192.168.210.116)
==========================================

cd /var/lib/mysql
mv auto.cnf auto_.cnf
mkdir -p /Data/mysqldata
chown -R mysql:mysql /mysqldata
chmod -R 777 /mysqldata
cp mysql -r /Data/mysqldata
service mysql stop
<<<change /etc/my.cnf file and edit data_dir>>
cd /var/lib/
cd /Data/mysqldata
tar -zcvf mysql.tgz mysql
scp -P6381 mysql.tgz dbaadmin@192.168.210.117:/Data/mysqldata/

My.cnf File
———–
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
datadir=/Data/mysqldata/mysql
socket=/var/lib/mysql/mysql.sock

join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
max_connections = 300
bind-address=0.0.0.0
key_buffer = 32M
key_buffer_size=256M
myisam_sort_buffer_size = 128M
bulk_insert_buffer_size=1G
#join_buffer_size = 1M
#read_buffer_size = 1M
#sort_buffer_size = 2M
#table_cache = 5120
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_log_file_size=256M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50
innodb_flush_method=O_DIRECT
transaction-isolation=READ-COMMITTED
wait_timeout = 1800
connect_timeout=15
max_allowed_packet = 24M
max_connect_errors = 10
query_cache_limit = 10M
query_cache_size = 32M
query_cache_type = 1
max_connect_errors = 1844674407370954751
thread_cache_size = 256
skip-name-resolve
tmp_table_size=524288000
max_heap_table_size=524288000

# —————
server_id=001
log-bin=/Data/mysqldata/BinaryLogs/ad1/master1
log-bin-index=/Data/mysqldata/BinaryLogs/ad1/bin-log.index
relay-log=/Data/mysqldata/BinaryLogs/ad1/relay-log
relay-log-index=/Data/mysqldata/BinaryLogs/ad1/relay-log.index
relay-log-space-limit = 4G
auto_increment_increment = 10
auto_increment_offset = 1

binlog-do-db= ctrdb
binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-format=MIXED

#master-host = 192.168.210.49
#master-user = replication
#master-password = slave
#master-port = 3306

# ——————————————————————————–

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Login to MySQL Terminal :

grant replication slave on *.* to ‘replication’@192.168.210.116 identified by ‘slave’;
grant replication slave on *.* to ‘replication’@192.168.210.117 identified by ‘slave’;

show master status; (note the position and log).
stop slave;

on server 1:(192.168.210.116)
change master to master_host= ‘192.168.210.117’, master_port=3306, master_user=’replication’, master_password=’slave’, master_log_file=’master2.000002′, master_log_pos=120, master_connect_retry=10 ;

start slave;
show slave status \G;
show master status;

============================================
As Root User on Server 2 (192.168.210.117)
============================================

mkdir /Data/mysqldata
cd /Data
chmod -R 777 mysqldata/
chown -R mysql:mysql mysqldata/

as dbadmin user

sudo tar -zxvf mysql.tgz
service mysql stop
cd /Data/mysqldata
tar -xvf mysql.tgz
<<<change /etc/my.cnf file and edit data_dir>>>
service mysql stop

MY.CNF FILE
————–
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
datadir=/Data/mysqldata/mysql
socket=/var/lib/mysql/mysql.sock

join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
max_connections = 300
bind-address=0.0.0.0
key_buffer = 32M
key_buffer_size=256M
myisam_sort_buffer_size = 128M
bulk_insert_buffer_size=1G
#join_buffer_size = 1M
#read_buffer_size = 1M
#sort_buffer_size = 2M
#table_cache = 5120
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_log_file_size=256M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50
innodb_flush_method=O_DIRECT
transaction-isolation=READ-COMMITTED
wait_timeout = 1800
connect_timeout=15
max_allowed_packet = 24M
max_connect_errors = 10
query_cache_limit = 10M
query_cache_size = 32M
query_cache_type = 1
max_connect_errors = 1844674407370954751
thread_cache_size = 256
skip-name-resolve
tmp_table_size=524288000
max_heap_table_size=524288000

# —————
server_id=002
log-bin=/Data/mysqldata/BinaryLogs/ad2/master2
log-bin-index=/Data/mysqldata/BinaryLogs/ad2/bin-log.index
relay-log=/Data/mysqldata/BinaryLogs/ad2/relay-log
relay-log-index=/Data/mysqldata/BinaryLogs/ad2/relay-log.index
relay-log-space-limit = 4G
auto_increment_increment = 10
auto_increment_offset = 1

binlog-do-db= ctrassetdb
binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-format=MIXED

#master-host = 192.168.210.49
#master-user = replication
#master-password = slave
#master-port = 3306

# ——————————————————————————–

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open Terminal of MySQL:
———————–

STOP SLAVE;

On Server 2:(192.168.210.117)

change master to master_host= ‘192.168.210.116’, master_port=3306, master_user=’replication’, master_password=’slave’, master_log_file=’master1.000002′,master_log_pos=120, master_connect_retry=10 ;

START SLAVE;
SHOW SLAVE STATUS \G;
show master status;

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: