Replikacja mysql

  • Zainstaluj serwer mysql na dwoch maszynach.
  • Na serwerze SW1 (192.168.1.131) skonfigurujemy bazę master
  • Na serwerze SW2 (192.168.1.132) skonfigurujemy bazę slave
  • Na wszystkich instancjach trzeba wlaczyc nasluchiwanie na IP 0.0.0.0 (bind-address)

Ustawienie serwera Maser

W pliku konfiguracyjnym /etc/mysql/my.cnf serwera MASTER dodaj w sekcji [mysqld]:
log-bin                 = mysql-bin
server-id               = 1
binlog-do-db            = asl24
root@asl244:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
root@asl244:~#
tworzymy uzytkownika:
root@asl244:~# mysql -u root -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.49-3-log (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> CREATE USER 'urepl'@'%' IDENTIFIED BY 'qwerty';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'urepl'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>
Sprawdzamy działanie MASTER:
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      106 | asl24        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
	
mysql>
W pliku konfiguracyjnym /etc/mysql/my.cnf serwera SLAVE dodaj w sekcji [mysqld]:
server-id               = 2
master-host             = 192.168.1.131    # tutaj podajemy adres serwera master
master-user             = urepl
master-password         = qwerty
master-port             = 3306
root@asl244-client1:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
root@asl244-client1:~#
Na serwerze MASTER wykonujemy:
root@asl244:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.1.49-3-log (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> use asl24
ERROR 1049 (42000): Unknown database 'asl24'
mysql> CREATE DATABASE asl24;
Query OK, 1 row affected (0.00 sec)

mysql> use asl24;
Database changed
mysql> create table asl24 (id int, nazwa varchar(20));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into asl24 values ('1','ala ma kota');
Query OK, 1 row affected (0.00 sec)

mysql>
Na serwerze SLAVE sprawdzamy:
iroot@asl244-client1:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.1.49-3 (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> use asl24;
Database changed
mysql> select * from asl24;
+------+-------------+
| id   | nazwa       |
+------+-------------+
|    1 | ala ma kota |
+------+-------------+
1 row in set (0.00 sec)

mysql> 
Sprawdzamy status serwera SLAVE (mozna uzyc SHIFT-PGUP do przewijania ekranu):
mysql> SHOW SLAVE STATUS\G; 
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.129
Master_User: urepl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000018
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
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: 106
Relay_Log_Space: 552
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:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
Na serwerze MASTER i SLAVE wykonaj z poziomu uzytkownika root:
mysql> CREATE USER 'asl24'@'%' IDENTIFIED BY 'qwerty';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON asl24.* TO 'asl24'@'%';
Query OK, 0 rows affected (0.00 sec)

Konfiguracja keepalived:
virtual_server 192.168.229.128 3306 {
	delay_loop 20
	lb_algo rr
	lb_kind NAT
	#persistence_timeout 50
	protocol TCP
	real_server 192.168.1.131 3306 {
		weight 1
		TCP_CHECK {
		connect_timeout 3
		}
	}
	real_server 192.168.1.132 3306 {
		weight 1
		TCP_CHECK {
			connect_timeout 3
		}
	}
}

W przypadku gdy SLAVE się nie połączył można spróbować:
STOP SLAVE;

CHANGE MASTER TO
->     MASTER_HOST='192.168.1.131',
->     MASTER_USER='urepl',
->     MASTER_PASSWORD='qwerty';

START SLAVE