Motivation
Install MySQL on the Machines
There are two ways to do this. Use the one that looks less scary to you.
SSH to the MySQL machine. Run the following commands to make XFS file system, register to
Move Databases to the New Volume
This step is basically moving MySQL data directories to the new Volume, and tell MySQL about it. One thing that I noted that some of the installations have different location for log files and data directories than mentioned here. Also, I found that
Lets move to setting up replication.
Create or Edit my.cfg
If you have a
Edit Master Node's Config file
- To elliminate the uncertainity of EC2 instances going down for unknown reasons leading loss of our data.
- To have almost unconstrained storage space.
- To have a robust system that can cope with application scaling.
- At least two running EC2 machines (one master, other slave)
- Ability to SSH to the MySQL machines.
- If you decide to use EC2 API CLI, it must be on your machine where you are running EC2 API commands from, and configured.
Install MySQL on the Machines
yum install -y mysql mysql-serverCreate and Attach EBS Volume to Your MySQL Machines
There are two ways to do this. Use the one that looks less scary to you.
- Via EC2-API CLI
ec2-create-volume -z us-east-1b -s 10
Here-z
isAvailability Zone
and-s
is size of the volume inGiB
. Please note that it's important to have the volume in same Availability Zone as the MySQL machine you are trying to attach to.
Note down thevolume id
this command returns, assume it'svol-XXXXX
. Now attach it to a running intance, say, withinstance id
asi-YYYYYY
ec2-attach-volume -d /dev/sdf -i i-YYYYYY vol-XXXXX
here-d
is device name. It's not necessary to use/dev/sdf
you may use/dev/sdh
or anything else.
- Use AWS Console, Go to
EC2 > Elastic Block Store > Volumes
ClickCreate Volume
, type in specifications
Select the Volume, clickMore > Attach Volume
SSH to the MySQL machine. Run the following commands to make XFS file system, register to
fstab
, and mount to /vol
sudo mkfs.xfs /dev/sdf echo "/dev/sdf /vol xfs noatime 0 0" | sudo tee -a /etc/fstab sudo mkdir -m 000 /vol sudo mount /vol
Move Databases to the New Volume
This step is basically moving MySQL data directories to the new Volume, and tell MySQL about it. One thing that I noted that some of the installations have different location for log files and data directories than mentioned here. Also, I found that
/etc/mysql
directory was non existant. You may look for their location in /etc/my.cnf
. If you find there is no /etc/my.cnf
, you should create it. (I will tell you later how.)# stop MySQL sudo /etc/init.d/mysql stop # create appropriate directories in volume sudo mkdir /vol/etc /vol/lib /vol/log # move from currently installed location sudo mv /etc/mysql /vol/etc/ sudo mv /var/lib/mysql /vol/lib/ sudo mv /var/log/mysql /vol/log/ # replace the moved directories with a placeholder sudo mkdir /etc/mysql sudo mkdir /var/lib/mysql sudo mkdir /var/log/mysql # mount them to refer to the ones in volume echo "/vol/etc/mysql /etc/mysql none bind" | sudo tee -a /etc/fstab sudo mount /etc/mysql echo "/vol/lib/mysql /var/lib/mysql none bind" | sudo tee -a /etc/fstab sudo mount /var/lib/mysql echo "/vol/log/mysql /var/log/mysql none bind" | sudo tee -a /etc/fstab sudo mount /var/log/mysql #start MySQL sudo /etc/init.d/mysql startYou may, now, try creating a database and look into
/vol/lib/mysql
. Lets move to setting up replication.
Create or Edit my.cfg
If you have a
my.cnf
file, back it up. We are going to edit it. This file is going to have details about master and slave.mv /etc/my.cnf /etc/my.cnf.old vi /etc/my.cnfIf not just create one by
touch /etc/my.cnf
Edit Master Node's Config file
On master machine, give it a
Create Replication User on Master Node
Also, lets find starting position of binary log. This information will be required to configure the slave.
Edit or create slave machine
Now you may create/delete a database, a table, insert/remove data and see on slave machine happening the same.
Summary
Here are the things that I did:
Refer
server-id
and enable binary logging. Add these two lines server-id = 1 log-bin = mysql-binThe file looks like this:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # master id server-id=1 # binary logging for replication) log-bin=mysql-bin # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 [mysqld_safe] log-error=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pidRestart MySQL
Create Replication User on Master Node
GRANT REPLICATION SLAVE ON *.* TO 'replicator' IDENTIFIED BY 'replicatorPassword'; FLUSH PRIVILEGES;
Also, lets find starting position of binary log. This information will be required to configure the slave.
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 | 1013 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)Configure Slave Node
Edit or create slave machine
/etc/my.cnf
to have server-id
, you may enable binary logging but not required.server-id = 2 log-bin = mysql-binLets connect the two machines. We will require the private DNS/IP of the master machine and some of the informations that we have got earlier. Let's say private DNS is
ip-12-34-567-890.ec2.internal
CHANGE MASTER TO MASTER_HOST='ip-12-34-567-890.ec2.internal', MASTER_USER='replicator', MASTER_PASSWORD='replicatorPassword', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=1013; START SLAVE;
Now you may create/delete a database, a table, insert/remove data and see on slave machine happening the same.
Summary
Here are the things that I did:
- Installed MySQL on my EC2 instances.
- Created, attached, formatted, mounted a Volume to these machines. Added the details to
fstab
. - Stopped MySQL. Moved MySQL data and log directories to the volume.
- Recreated placeholder directories in the same place as previous ones, and binded it to the new location. Added to
fstab
and mounted. - Restarted MySQL and ran a sanity test.
- Stopped MySQL, and edited/created
my.cnf
on both machines to assign them aserver-id
and enable binary logging. Started MySQL. - Created a replication user. Noted down master node's status.
- Connected slave with master using
CHANGE MASTER ...
command. - ran
START SLAVE
command to start replication. Performed a sanity test.
Refer