There are more than enough master slave mysql tutorials, and I'm adding another one to the pile for my own personal reference.
Setup:
Two micro ec2 instances on amazon with mysql55 installed. Make sure port 3306 is open between servers.
Step 1: Edit /etc/my.cnf on master and slave
On master:
[mysqld]
log-bin=mysql-bin
server-id=1
On slave
[mysqld]
server-id=2
Side Note: you can't put master-host settings here as they've been deprecated since 5.1.17. MySQL will save the values internally and will automatically reference them when a reboot is done.
Restart Both Servers
service mysqld restart
Step 2: Create Slave User On Master
On Master run the mysql command:
CREATE USER 'slave2'@'[SLAVE IP OR ADDRESS]' IDENTIFIED BY '[Fancy Password]';
GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'[SLAVE IP OR ADDRESS]';
Side Note: mydbname.* will not work, must use *.*
Step 3: Finish current commands and lock the tables
On Master:
flush tables with read lock
This will finish all commands and stop new ones from happening. To release the locks we need to run unlock tables, but not till later.
show master status
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 1859 | | |
+------------------+----------+--------------+------------------+
We'll have to keep track of the file and position if we use
--lock-all-tables instead of --master-data. I'm using the latter
Don't close your mysql console as this will unlock the tables. For the next step create a new terminal on the master server.
Step 4: Create a dump of the data on the master server
On master run:
mysqldump -u root -p --all-databases --master-data > moodle.sql
and copy the file to the slave server.
Step 5: Setup the slave server with the master login info.
On slave go to mysql command prompt
stop slave;
CHANGE MASTER TO
MASTER_HOST='[MASTER IP OR ADDRESS]',
MASTER_USER='slave2',
MASTER_PASSWORD='[Fancy Password]';
# MASTER_LOG_FILE='mysql-bin.000002',
# MASTER_LOG_POS=1859;
On the mysql doc site, these two lines are not needed as they are included in the dump file when use used --master-data
Step 6: Import the dump sql file on the slave server.
Use this to import the dump file. (As mentioned this will add the master log file pos too.)
mysql -u root -p < moodle.sql
Step 7: Start the replication
We still have that mysql command prompt on master so we are now ready to release the lock.
unlock tables;
Then on slave we can begin the replication
start slave;
Step 8: Confirm the slave is working
show slave status\G
and you should see
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
---
