Basic Master Slave setup on MySQL 5.5

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
...

---