Slave MySQL server

From ISPWiki

Prerequisite

If the second network interface is not present on the server, jump this step.

Open /etc/network/interfaces

 # nano /etc/network/interfaces

to add the following lines

 auto ethX
 iface ethX inet static
     address 10.0.0.5
     netmask 255.0.0.0

where X is the network interface number.

Run the command below to apply the changes:

 # /etc/init.d/networking restart

Installing and customizing the MySQL server

Install the MySQL server with the following command:

 # apt-get install mysql-server

Get the status of master MySQL server binary logs: Получите статус бинарных логов основного MySQL-сервера:

 # ssh root@10.0.0.4 "echo \"SET GLOBAL read_only = ON; SHOW MASTER STATUS;\" | mysql -uroot -pXXX"

where XXX is a root password, 10.0.0.4 is the Master MySQL server IP-address.

Keep the Запишите значения полученных из ответа переменных LogFile and LogPosition variables as you will need them later. , так как они потребуются позже.

Stop the master MySQL server:

 # ssh root@10.0.0.4 "/etc/init.d/mysql stop"

Copy the /etc/mysql/my.cfg file from the master MySQL server:

 # scp root@10.0.0.4:/etc/mysql/my.cfg /etc/mysql/

Open /etc/mysql/my.cfg

 # nano /etc/mysql/my.cfg

to change the server-id option in the mysqld section

 ...
 [mysqld]
 ...
 server-id 2
 ...

Copy the /var/lib/mysql directory from the master MySQL server:

 # scp -r root@10.0.0.4:/var/lib/mysql/* /var/lib/mysql/

Run the master MySQL server:

 # ssh root@10.0.0.4 "/etc/init.d/mysql start"

Restart the local MySQL server with the command:

 # /etc/init.d/mysql restart

Start database replication on the MySQL server:

 # echo "CHANGE MASTER TO MASTER_HOST = '10.0.0.4', MASTER_USER = 'replication', MASTER_PASSWORD = 'YYY', \
   MASTER_PORT = 3306, MASTER_CONNECT_RETRY = 10, MASTER_LOG_FILE = 'LogFile', MASTER_LOG_POS = LogPosition; \
   START SLAVE;" | mysql -uroot -pXXX"

where XXX is a root password, YYY is a replication user password, 10.0.0.4 is the master MySQL server IP-address, LogFile and LogPosition are vriable you got above.

Register the MySQL server in ISPmanager Cluster:

 # ssh root@10.0.0.6 "/usr/local/ispmgr/sbin/mgrctl -m ispmgr clusternodes.new ip=10.0.0.5 sok=ok"
 # ssh root@10.0.0.6 "/usr/local/ispmgr/sbin/mgrctl -m ispmgr clusternodes.roles.edit plid=10.0.0.5 \
   name=MySQL port=3306 enable=on priority=100 sok=ok"

where 10.0.0.6 is the master node IP-address.