Slave MySQL server

From ISPWiki

Jump to: navigation, search

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 configuring 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"

где XXX - пароль root, а 10.0.0.4 - это IP-адрес основного MySQL-сервера.

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 the 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 variables you got above.


Note The mechanism enabling to switch from the master MySQL-server to the backup one has not yet been implemented. If the master MySQL-server fails, you need to change the IP-address into a backup address manually.

Was this helpful? Yes | No
Personal tools