SWB Auction Manager – MySQL Replication
SWB offers database replication of all your live auction data to a local server at your location in real time. All that is needed on your end is a server, ideally Linux Red Hat, or a similar system, we will apply the following steps:
1. MASTER CONFIGURATION
- In my.cnf on the master server, add the following under [mysqld]
log-bin=mysql-bin
server-id=1
2. SLAVE CONFIGURATION
On the slave server add the following under [mysqld]
server-id=2
replicate-do-db=db_name
If you want to setup multiple slaves, each needs to have a unique server id
db_name is the name of the database you want to replicate on the master
3. OBTAIN MASTER COORDINATES
a. Start a session on the master by typing mysql -u admin -p and hitting enter. Type in the admin pw and hit enter. Execute the command:
FLUSH TABLES WITH READ LOCK;
Leave the client running so the lock remains in place
b. Open a new session in a different shell. In a different session on the master, use the SHOW MASTER STATUS statement to determine the current binary log file name and offset. Something like this:
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 655366 | | |
+——————+———-+————–+——————+
Note down File and Position values. You will need them later.
4. CREATE MASTER DUMP
On the master server in the secondary shell create a data dump, example:
[shell] mysqldump -u user-p database >dbdump.db
…where user is the db user and database is the database name
On the client where you posted FLUSH TABLES WITH READ LOCK; it is now time to release the lock:
mysql> UNLOCK TABLES;
exit that shell
5. DATA IMPORT
a. (Stop the slave if it’s running.) Start the slave, using the –skip-slave-start option so that replication does not start.
b. Import the dump file:
shell> mysql -u username -p database < dbdump.db
...where username is the username and database is the database name and dbdump.db is the master dump
6. SUPPLY REPLICATION CONFIGURATION INFO TO SLAVE
On the slave database, execute the following statement, replacing the variables as needed:
mysql> CHANGE MASTER TO
-> MASTER_HOST=’master_host_name’,
-> MASTER_USER=’replication_user_name’,
-> MASTER_PASSWORD=’replication_password’,
-> MASTER_LOG_FILE=’recorded_log_file_name’,
-> MASTER_LOG_POS=recorded_log_position;
7. START REPLICATION
On the live server, make sure that:
a. The user used for replication has the privilege “Repl_slave_priv” set to Y
b. The host for that user in the user and db table is %, not localhost or anything else
c. The live server firewall allows remote access to the mysql port
Start the slave threads:
mysql> START SLAVE;
After you have performed this procedure, the slave should connect to the master and catch up on any updates that have occurred since the snapshot was taken.
check the message in /var/log/mysqld.log. It should be something like this:
100219 17:30:00 [Note] Slave I/O thread: connected to master ‘user@77.777.777.77:3306′, replication started in log ‘mysql-bin.000001′ at position 655366
8. ENJOY
Lean back and watch the data replicate … 
Related posts:
0 Responses to “SWB Auction Manager – MySQL Replication”