close x

Howto to make a first approach to database autoscale in AMAZON EC2

What I want to try is have an infrastructure of two data servers behind a load balancer. One master and One slave. They will be replicating data one against another, so in every moment the two servers will have the same data. I have to warn that I have no idea of data servers hehe, I’m doing this, cause I need to do it.

We create the database and the users in MySQL:

CREATE DATABASE trial;
CREATE USER ‘trial_user’@'%’ IDENTIFIED BY ‘PASS’;
GRANT ALL PRIVILEGES ON trial.* TO ‘trial_user’@'%’ WITH GRANT OPTION;

That is the database that we want to replicate. Now we need to create replication user, we do the next in both servers:

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@'%’ IDENTIFIED BY ‘slave_password’;

FLUSH PRIVILEGES; quit;

Now we set up master-master replication in /etc/mysql/my.cnf. The crucial configuration options for master-master replication are auto_increment_increment and auto_increment_offset:

auto_increment_increment controls the increment between successive AUTO_INCREMENT values.
auto_increment_offset determines the starting point for AUTO_INCREMENT column values.
Let’s assume we have N MySQL nodes (N=2 in this example), then auto_increment_increment has the value N on all nodes, and each node must have a different value for auto_increment_offset (1, 2, …, N).

Now let’s configure our two MySQL nodes:

nano /etc/mysql/my.cnf

[...] [mysqld] server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

master-host = ip_server2
master-user = slave_user
master-password = slave_password
master-connect-retry = 60
replicate-do-db = trial

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = trial

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days = 10
max_binlog_size = 500M
[...] and restart the database:

/etc/init.d/mysql restart
We do the same in the second server, just changing the parameter:

server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
master-host = ip_server1
and restart the database.

Now in server1, in the MySQL prompt:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
and it should appear something like that:

+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000009 | 98 | trial | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

Now don’t leave the MySQL shell, because if you leave it, the database lock will be removed, and this is not what we want right now because we must create a database dump now. While the MySQL shell is still open, we open a second command line window where we create the SQL dump snapshot.sql and transfer it to server2 (using scp):

cd /tmp
mysqldump -u root -pyourrootsqlpassword –opt trial > snapshot.sql
scp snapshot.sql root@ip_server2:/tmp
we go back to MySQL prompt and:

UNLOCK TABLES;
quit;
On server2, we can now import the SQL dump snapshot.sql like this:

/usr/bin/mysqladmin –user=root –password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword trial < snapshot.sql
And here is the key, in server2 we need to make the server2 slave of server1:

FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
CHANGE MASTER TO MASTER_HOST=’ip_server1′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’slave_password’, MASTER_LOG_FILE=’mysql-bin.000009′, MASTER_LOG_POS=98;
START SLAVE;
We need to repeat the proccess but in reverse, to make server1 slave od server2.

References:

http://www.howtoforge.com/mysql5_master_master_replication_debian_etch

Cloud Computing Linux0 comments

Leave a Reply