MySQL Master-Slave Replication Configuration Guide

MySQL 5 Master-Slave Replication Setup
1. Installation Package Selection
mysql-mysql-5.0.77

2. Environment Setup
Two Red Hat Enterprise Linux AS release 4 (Nahant Update 5) virtual machines
Master Server: 192.168.126.118
Slave Server: 192.168.126.166

3. Master Server Configuration

Create user backup
GRANT ALL PRIVILEGES ON *.* to [email protected] identified by '123456';

Testing shows minimal privileges also work
grant file on  *.* to [email protected] identified by '123456';

Export the data to be synchronized for use on the slave server
mysqldump -uroot -p123456 diywebsite>/root/diywebsite.sql

Edit configuration file
/etc/my.cnf
server-id = 1
log-bin=mysql-bin
binlog-do-db=diywebsite         # Database names to back up, multiple lines allowed
binlog-ignore-db=mysql        # Database names to ignore, multiple lines allowed

Restart MySQL
service mysqld restart

If the configuration is correct, you can list the following data
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000010 |       98 | diywebsite   | mysql           

4. Slave Server Configuration
Create database
create database diywebsite;

Import data
mysql -uroot -p123456 diywebsite

Edit /etc/my.cnf
server-id=2      
log-bin=mysql-bin
master-host=192.168.126.118
master-user=backup
master-password=123456
master-port=3306
replicate-do-db=diywebsite
replicate-ignore-db=mysql
master-connect-retry=60
#replicate-do-db=test1
# replicate-do-db=test  Database names to replicate
# replicate-ignore-db=mysql Database names to ignore
# master-connect-retry=60 Reconnect interval (in seconds) if the slave detects the master is down

Restart MySQL
service mysqld restart

5. Verify Configuration is Correct:
Log into MySQL and run
slave start;
If the following error appears:
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

This error can be resolved by executing the Change master statement:
change master to master_host='192.168.126.118',master_user='backup',master_password='123456',master_log_file='mysql-bin.000010' ,master_log_pos=98;

Then execute:
mysql> slave start;
Query OK, 0 rows affected (0.01 sec)

mysql>SHOW SLAVE STATUS/G
If the following two items both show Yes, it indicates success:
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

If you see No, synchronization has not succeeded. Check the MySQL log to find the cause of the error
/usr/local/mysql/var/localhost.localdomain.err