MySQL Replication Setup

On the Master we first flush the tables in the database setting a read lock:

USE phpMyWebcam;FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS;

The last command will show something like this

+——————+———-+————–+——————+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+——————+———-+————–+——————+
| mysql-bin.015586 | 364167 | phpMyWebcam | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

We write down this information, as we will need it later on the slave!

We leave the MySQL shell and execute:
mysqldump -u root -p –opt phpMyWebcam > phpMyWebcam.sql (Replace with the real password for the MySQL user root! Important: There is no space between -p and !)
This will create an SQL dump of phpMyWebcam in the file phpMyWebcam.sql. Transfer this file to your slave server!

Finally we have to unlock the tables in phpMyWebcam:

mysql -u root -pEnter password:

mysql> UNLOCK TABLES;
quit;

Now the configuration on the master is finished. On to the slave…
If you have made an SQL dump of phpMyWebcam on the master and have transferred it to the slave, then it is time now to import the SQL dump into our newly created phpMyWebcam on the slave:

mysql -u root -p phpMyWebcam < /path/to/phpMyWebcam.sql (Replace with the real password for the MySQL user root! Important: There is no space between -p and !)

Finally, we must do this:
mysql -u root -pEnter password:SLAVE STOP;

In the next command (still on the MySQL shell) you have to replace the values appropriately:
CHANGE MASTER TO MASTER_HOST=‘localhost’, MASTER_PORT=13306, MASTER_USER=’replicant’, MASTER_PASSWORD='’, MASTER_LOG_FILE=’mysql-bin.015586′, MASTER_LOG_POS=364167;

  • MASTER_HOST is the IP address or hostname of the master (in this example it is localhost).
  • MASTER_PORT is the port to which the sql master listens (in this example it is 13306).
  • MASTER_USER is the user we granted replication privileges on the master.
  • MASTER_PASSWORD is the password of MASTER_USERon the master.
  • MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS;on the master.
  • MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

Now all that is left to do is start the slave. Still on the MySQL shell we run
START SLAVE;quit;
That’s it! Now whenever phpMyWebcam is updated on the master, all changes will be replicated to phpMyWebcam on the slave. Test it!

To see the status of your slave you can every time run this command on the slave:

SHOW SLAVE STATUS;

Related Posts

Postgres: Copy a database

simple

November 17, 2012

Databases, Postgres

No Comment

For backup and development reasons a copy of a postgres database on the same system is often used. This is not a big problem and can be done directly as a SQL statement: [crayon-5bc7b4cae39bf278001552/] in your favorite console (psql, pgadmin, etc). There is one thing to remember, if you do a template copy this can […]

Read More

Oracle: Timeout idle or dead sessions

Lately in a project we had a lot of problems with idle database sessions. It turns out that the DBA did not configure any timeout for the oracle database for idle or dead sessions. This is done with the PMON process, and via SQL*Net, by the sqlnet_expire_time parameter. SQL*Net In your sqlnet.ora file add the […]

Read More

Leave a Reply