[MySQL]: Master-slave replication for a new-setup

June 26, 2015 · by dbversity · in MySQL

Master:

1. Install MySQL as per the standard procedure
2. Edit my.cnf of Master server with below parameters

server-id = 8 # Should be unique id between 2 and 2^32 – 1
log-bin = /home/mysql5/mysql-binlogs/’hostname’binlog # required for replication
log-bin-index = / home/mysql5/mysql-binlogs/’hostname’binindex
set-variable = max_binlog_size= 1GB

3. Restart MySQL after above modifications are done

4. Stop transactions on Master by executing below commands and take master position
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;

5. Take the dump on master with standard MySQL Dump command

Alternavtively for the existing set-up, while taking mysqldump if we use option –master-data, it’ll include binary file name & master position &

6. Unlock the tables once dump is completed.
mysql> UNLOCK TABLES;

7. Grant privileges to Slave(B) to connect to Master to fetch the binary log updates
grant replication slave on *.* to repluser@SlaveIP identified by ‘rep12#$’;
Illustration :-
[root@dbversitymysql]# yum reinstall MySQL
Setting up Reinstall Process
soe-bigdata | 1.2 kB 00:00
Resolving Dependencies
–> Running transaction check
—> Package MySQL-server.x86_64 0:5.5.21-1.linux2.6 will be obsoleted
—> Package MySQL-server.x86_64 0:5.5.21-1.linux2.6 will be reinstalled
–> Finished Dependency Resolution

Dependencies Resolved

============================================================================================================================================================================================================================================
Package Arch Version Repository Size
============================================================================================================================================================================================================================================
Installing:
MySQL-server x86_64 5.5.21-1.linux2.6 soe-bigdata 51 M
replacing MySQL-server.x86_64 5.5.21-1.linux2.6

Transaction Summary
============================================================================================================================================================================================================================================
Install 1 Package(s)

Total download size: 51 M
Is this ok [y/N]: y
Downloading Packages:
MySQL-server-5.5.21-1.linux2.6.x86_64.rpm | 51 MB 00:01
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
Installing : MySQL-server-5.5.21-1.linux2.6.x86_64 1/1
Obsoleted : MySQL-server-5.5.21-1.linux2.6.x86_64 1/1
Giving mysqld 5 seconds to exit nicely

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h dbversitypassword ‘new-password’

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

Verifying : MySQL-server-5.5.21-1.linux2.6.x86_64 1/1
Verifying : MySQL-server-5.5.21-1.linux2.6.x86_64 2/1

Installed:
MySQL-server.x86_64 0:5.5.21-1.linux2.6

Replaced:
MySQL-server.x86_64 0:5.5.21-1.linux2.6

Complete!
[root@dbversitymysql]#
[root@dbversitymysql]#
[root@dbversitymysql]#
[root@dbversitymysql]# cat /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
log-bin=mysql-bin
server-id = 1

key_buffer = 16M
key_buffer_size = 32M
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
max_connections = 600

read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
# InnoDB settings
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_buffer_pool_size = 2G
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

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

skip-show-database=1
log-warnings=2
secure-auth=on
local-infile=0
[root@dbversitymysql]#
[root@dbversitydotcom mysql]# mysql_install_db
Installing MySQL system tables…
OK
Filling help tables…
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h dbversitydotcom password ‘new-password’

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

[root@dbversitydotcom mysql]#
[root@dbversitydotcom mysql]#
[root@dbversitydotcom mysql]#
[root@dbversitydotcom mysql]# ll
total 16
drwx—— 2 root root 4096 Jun 25 05:18 mysql
drwx—— 2 root root 4096 Jun 25 05:18 performance_schema
drwx—— 2 root root 4096 Jun 25 05:18 test
[root@dbversitydotcom mysql]#
[root@dbversitydotcom mysql]#
[root@dbversitydotcom mysql]# chown mysql:mysql /var/lib/mysql/ -R
[root@dbversitydotcom mysql]#
[root@dbversitydotcom mysql]#
[root@dbversitydotcom mysql]# service mysql start
Starting MySQL….. [ OK ]
[root@dbversitydotcom mysql]#
[root@dbversitydotcom mysql]#

[root@dbversitymysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.5.21-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>
mysql>
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

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

mysql>
mysql>
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> grant replication slave on *.* to repluser@xx.xx.xx.37 identified by ‘rep12#$’;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
At slave server :

1. Install MySQL as per the standard procedure

2. Edit my.cnf of Slave (B) with below parameters

server-id = 2 # Should be unique id between 2 and 2^32 – 1
master-host = MasterIP # MUST BE SET
master-user = repluser # MUST BE SET
master-password = rep12#$ # MUST BE SET
master-port = 3306 # optional–defaults to 3306
master-connect-retry = 5 # Slave will try 5 times to connect to master incase of N/W failures

3. Restart MySQL and stop slave
mysql>stop slave;

Or alternatively run below command on Slave server mysql prompt

CHANGE MASTER TO MASTER_HOST=’xx.xx.xx.36′,
MASTER_PORT=3306,
MASTER_USER=’repluser’,
MASTER_PASSWORD=’rep12#$’,
MASTER_LOG_FILE = ‘mysql-bin.000003’,
MASTER_LOG_POS = 107;
4. Import data taken from Master

mysql> mysql change master to
MASTER_LOG_FILE=’mysql-bin.003′,
MASTER_LOG_POS=240;

6. Start slave

mysql>start slave;

7. Verify the replication by creating a DB on Master table.

mysql>show slave status;

Illustration :
[root@12d4-dl585-04 mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.21 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

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

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql>
mysql> CHANGE MASTER TO MASTER_HOST=’xx.xx.xx.36′,
-> MASTER_PORT=3306,
-> MASTER_USER=’repluser’,
-> MASTER_PASSWORD=’rep12#$’,
-> MASTER_LOG_FILE = ‘mysql-bin.000003’,
-> MASTER_LOG_POS = 107;
Query OK, 0 rows affected (0.16 sec)

mysql>
mysql>
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: xx.xx.xx.36
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 107
Relay_Log_File: 12d4-dl585-04-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
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: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 107
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: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)

mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx.xx.xx.36
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 260
Relay_Log_File: 12d4-dl585-04-relay-bin.000002
Relay_Log_Pos: 406
Relay_Master_Log_File: mysql-bin.000003
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: 260
Relay_Log_Space: 570
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

mysql>

Tags: [MySQL]: Master-slave replication for a new-setup

[MySQL]: Master-slave replication for a new-setup

Leave a Reply

Categories

Categories