We are going to replicate our database to have a single slave for readonly access and a master for our writes. I chose Octopus because it is actively maintained, does sharding (a future project), allows selects to happen on the slave and writes to happen on the master out of the box without me having to touch every single "find" in my code base.
We are going to do this on EC2. Your files might be in different locations than mine are.
Let's start with the MySQL Master:
Edit my.cnf and uncomment:
[mysqld]
...
...
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
This is where the instructions for replication are written by mysql. We also give this mysql server an id of 1.
On your slave, modify the my.cnf and do this:
[mysqld]
...
...
server-id = 2
Now open a mysql client on your master db and do:
mysql> grant replication slave on *.* to 'replication'@XXX.XXX.XXX.XXX identified by 'my_secret_password'
change the above to use an actual ip address after 'replication'@ and also use a real password.
Restart mysql.
Next, do a read lock
mysql> FLUSH TABLES WITH READ LOCK;
Record the file and position on the master. It should give you output like this. Record the file and position to use later.
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 1467771 | | |
+——————+———-+————–+——————+
Export data on master using mysql_dump
mysqldump -uroot -p --all-databases --lock-all-tables > all_databases.sql
mysql> unlock tables;
Restart the slave
More coming soon....
