Difference between revisions of "MySQL Replication"
Line 3: | Line 3: | ||
=== Create User for Replication === | === Create User for Replication === | ||
− | Execute these commands on your master | + | Execute these commands on your MySQL master while logged in as root. Replace '''slaveuser''' and '''slavepass''' with the user and password that youwant to create. For increased security replace '''%''' with the IP of your MySQL slave. |
<source lang="mysql">CREATE USER 'slaveuser'@'%' IDENTIFIED BY 'slavepass'; | <source lang="mysql">CREATE USER 'slaveuser'@'%' IDENTIFIED BY 'slavepass'; | ||
GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%';</source> | GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%';</source> | ||
=== MySQL Master Configuration === | === MySQL Master Configuration === | ||
− | Add the following to your master | + | Add the following to your MySQL master's my.ini or my.cnf under '''[mysqld]'''. Then restart the MySQL service. |
<source lang="ini">log-bin=mysql-bin | <source lang="ini">log-bin=mysql-bin | ||
server-id=1 | server-id=1 | ||
Line 14: | Line 14: | ||
=== Obtain the Master Binary Log Coordinates === | === Obtain the Master Binary Log Coordinates === | ||
− | Log in to your master | + | Log in to your MySQL master as root and execute the following commands. '''DO NOT CLOSE THIS MYSQL SESSION YET.''' These commands lock your database to prevent writing. This is required to guarantee data integrity while the data is being transferred to the MySQL slave. Your TCAdmin control panel might not work correctly while the tables are locked. |
<source lang="mysql">FLUSH TABLES WITH READ LOCK; | <source lang="mysql">FLUSH TABLES WITH READ LOCK; | ||
SHOW MASTER STATUS;</source> | SHOW MASTER STATUS;</source> |
Revision as of 17:09, 19 February 2012
Contents
How to Set Up Replication
For detailed instructions read the MySQL reference manual: http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html The following instructions are provided for your convenience. We do not provide support for configuring replication on your MySQL server.
Create User for Replication
Execute these commands on your MySQL master while logged in as root. Replace slaveuser and slavepass with the user and password that youwant to create. For increased security replace % with the IP of your MySQL slave.
CREATE USER 'slaveuser'@'%' IDENTIFIED BY 'slavepass'; GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%';
MySQL Master Configuration
Add the following to your MySQL master's my.ini or my.cnf under [mysqld]. Then restart the MySQL service.
log-bin=mysql-bin server-id=1 sync_binlog=1
Obtain the Master Binary Log Coordinates
Log in to your MySQL master as root and execute the following commands. DO NOT CLOSE THIS MYSQL SESSION YET. These commands lock your database to prevent writing. This is required to guarantee data integrity while the data is being transferred to the MySQL slave. Your TCAdmin control panel might not work correctly while the tables are locked.
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
You should see output similar to the following:
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 609384 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Transfer the Current Database to the MySQL Slave
On the MySQL slave, Create an empty database and user. The database should be named the same as your TCAdmin database on the MySQL master.
MySQL Slave Configuration
Add the following to your my.ini or my.cnf under [mysqld]. Replace the values as needed. Then restart the MySQL service.
#each slave should have a unique id server-id=2 #enter the name of the database that will be replicated replicate-do-db=tcadmin2 #make the database read only to prevent accidental writing on the slave read-only