replication by dbmMp7v

VIEWS: 3 PAGES: 5

									General Instructions:
IT IS RECCOMENDED TO USE A FOLDER REPLICATION SOFTWARE FOR THE OPEN DENTAL A TO
Z FOLDERS WHEN USING REPLICATION. THIS DOCUMENT ASSUMES THAT YOU ARE A FIRST TIME
USER OF OPEN DENTAL.
THE REPLICATED DATABASE MUST BE EXACTLY THE SAME ON ALL REPLICATION SERVERS BEFORE
REPLICATION BEGINS
THE FIRST TIME.

0. If replication is currently setup and you are trying to re-create the replication
setup,
you must first stop the existing slave service by running the "SLAVE STOP;" while
the
MYSQL service is started.

1. STOP THE MYSQL SERVICE. ("net stop mysql"). On some operating systems, such
as Windows Vista, this
will require you go into the system services in Control Panel->Administrative
Tools->Services and stop
the MYSQL service manually.
*WIPE OUT ANY PREVIOUSLY EXISTING FILES IN THE MYSQL DATA DIRECTORY WHICH DO NOT
RESIDE IN A SUBFOLDER.
**Make certain that there is an identical database copy on all servers

2. ENABLE REPLICATION BY SETTING UP THE MY.INI FILE ON ALL COMPUTERS INVOLDED BEFORE
GOING TO THE NEXT STEP.
        Add in the following lines to the "my.ini" file:
                log-bin=mysql-bin
                server-id=1
                log-slave-updates
                master-connect-retry=30
                replicate-do-db=opendental
                relay-log=server-relay-bin

        Alter the "server-id" and "replicate-do-db" as needed.
        The "server-id" should be sequential (1, 2, 3, etc.), increment the
sequence for each computer in the chain.
        The "replicate-do-db" should be the name of the database.
        Replace 'server' within the "relay-log" value to the name of the server.
ex) relay-log=maincomputer-relay-bin

3. START THE MYSQL SERVICE ON ALL THE REPLICATION SERVERS. ("net start mysql").
On some operating systems, such as Windows Vista, this
will require you go into the system services in Control Panel->Administrative
Tools->Services and start
the MYSQL service manually.

4. Set the local replication server's master computer by running a
command similar to the following (RUN THE FOLLOWING Mysql QUERIES):
Go the the command line and change the directory to "C:\Program Files\MySQL\MySQL Server
5.0\bin"
and type the following: "mysql -u root opendental" (where opendental is the name
of the database).

//Run on SERVER_1
CHANGE MASTER TO
MASTER_HOST = ‘SERVER_2’,
MASTER_USER = 'repl',
 MASTER_PASSWORD = 'od1234';

 //Run on SERVER_2:
 CHANGE MASTER TO
 MASTER_HOST = ‘SERVER_1’,
 MASTER_USER = 'repl',
 MASTER_PASSWORD = 'od1234';

          The "MASTER_HOST" can be either an IP address, or a DNS name.

 5. #Grant permissions for open dental to perform data merges.
 GRANT CREATE TEMPORARY TABLES ON opendental.* TO
 'repl' IDENTIFIED BY 'od1234';
 GRANT SUPER ON *.* TO
 'repl' IDENTIFIED BY 'od1234';
 GRANT REPLICATION SLAVE ON *.* TO
 'repl' IDENTIFIED BY 'od1234';

         The "opendental.*" should be the name of your mysql database, and the ".*"
 means everything within the specified database.

 6. Run the "START SLAVE;" in the mysql command interface for each computer in
 the chain.

 7. Run the "SHOW SLAVE STATUS;" in the mysql command interface for each computer
 in the chain.
 Make sure that thge Slave IO Running and Slave SQL Running columns both say 'YES'.

 8. Test replication in both directions.
 a. From server1, run the following SQL to create a junk temporary table: CREATE
 TABLE tmp123 (Col1 VARCHAR(1));
 b. From server2, verify that table tmp123 was created by running the following
 SQL: SELECT * FROM tmp123;
 c. From server2, run the following SQL to create a junk temporary table: CREATE
 TABLE tmp456 (Col1 VARCHAR(1));
 d. From server1, verify that table tmp456 was created by running the following
 SQL: SELECT * FROM tmp456;
 e. From server1, delete table tmp456 by running the following SQL: DROP TABLE
 tmp456;
 f. From server2, verify that table tmp456 was dropped by running the following
 SQL and verifying that tmp456 does NOT show up: SHOW TABLES LIKE 'tmp456%';
 g. From server2, delete table tmp123 by running the following SQL: DROP TABLE
 tmp123;
 h. From server1, verify that table tmp123 was dropped by running the following
 SQL and verifying that tmp123 does NOT show up: SHOW TABLES LIKE 'tmp123%';



                        Server_1 IP 192.168.0.1          Server_2 IP 192.168.0.2
                         database=databasename           database=databasename
1. STOP THE MYSQL       C:\>net stop mysql               C:\>net stop mysql
SERVICE. (*WIPE OUT ANY The MySQL service is stopping.   The MySQL service is stopping.
PREVIOUSLY EXISTING     The MySQL service was stopped    The MySQL service was stopped
FILES IN THE MYSQL DATA successfully.                    successfully.
DIRECTORY WHICH DO NOT
RESIDE IN A SUBFOLDER.)
2. ENABLE REPLICATION Contents of "C:\Program                          Contents of "C:\Program
BY SETTING UP THE     Files\MySQL\MySQL Server                         Files\MySQL\MySQL Server
MY.INI FILE.          5.0\my.ini"                                      5.0\my.ini"

                                 [mysqld]                              [mysqld]
                                 basedir="C:/Program                   basedir="C:/Program
                                 Files/MySQL/MySQL Server 5.0/"        Files/MySQL/MySQL Server 5.0/"
                                 datadir="C:/mysql/data/"              datadir="C:/mysql/data/"
                                 skip-innodb                           skip-innodb
                                 log-bin=mysql-bin                     log-bin=mysql-bin
                                 server-id=1                           server-id=2
                                 log-slave-updates                     log-slave-updates
                                 master-connect-retry=30               master-connect-retry=30
                                 replicate-do-db=databasename          replicate-do-db=databasename

3. START THE MYSQL     C:\>net start mysql                         C:\>net start mysql
SERVICE.               The MySQL service is starting.              The MySQL service is starting.
                       The MySQL service was started               The MySQL service was started
                       successfully.                               successfully.
4. Set the local       C:\Program Files\MySQL\MySQL C:\Program Files\MySQL\MySQL
replication server's Server 5.0\bin>mysql -u root                  Server 5.0\bin>mysql -u root
master computer by     Welcome to the MySQL monitor.               Welcome to the MySQL monitor.
running a              Commands end with ; or \g.                  Commands end with ; or \g.
command similar to the Your MySQL connection id is 1 to Your MySQL connection id is 1 to
following (RUN THE     server version:                             server version:
FOLLOWING Mysql        5.0.22-community-nt-log                     5.0.22-community-nt-log
QUERIES):
                       Type 'help;' or '\h' for help. Type '\c' to Type 'help;' or '\h' for help. Type '\c' to
                       clear the buffer.                           clear the buffer.


                                 mysql> change master to              mysql> change master to
                                    -> MASTER_HOST =                     -> MASTER_HOST =
                                 ‘192.168.0.2’,                       ‘192.168.0.1’
                                    -> MASTER_USER = 'repl',             -> MASTER_USER = 'repl',
                                    -> MASTER_PASSWORD =                 -> MASTER_PASSWORD =
                                 'od1234';                            'od1234';
                                 Query OK, 0 rows affected (0.17 sec) Query OK, 0 rows affected (0.17 sec)

                                 mysql> GRANT CREATE                  mysql> GRANT CREATE
                                 TEMPORARY TABLES ON                  TEMPORARY TABLES ON
                                 opendental.* TO                      opendental.* TO
                                    -> 'repl' IDENTIFIED BY              -> 'repl' IDENTIFIED BY
                                 'od1234';                            'od1234';
                                 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)

                                 mysql> GRANT SUPER ON *.* TO mysql> GRANT SUPER ON *.* TO
                                    -> 'repl' IDENTIFIED BY              -> 'repl' IDENTIFIED BY
                                 'od1234';                            'od1234';
                                 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)

                                 mysql> GRANT REPLICATION             mysql> GRANT REPLICATION
                                 SLAVE ON *.* TO                      SLAVE ON *.* TO
                                    -> 'repl' IDENTIFIED BY              -> 'repl' IDENTIFIED BY
                                 'od1234';                            'od1234';
                                 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
                            mysql>                     mysql>
6. START SLAVE;             mysql> start slave;        mysql> start slave;
                            Query OK, 0 rows affected, Query OK, 0 rows affected,
                            1 warning (0.00 sec)       1 warning (0.00 sec)


7. SHOW SLAVE STATUS mysql> show slave status; mysql> show slave status;
(look for the “YES” “YES” ) ----------------+------------- ----------------+-------------
                            ---------------+                   ---------------+
                            | Slave_IO_State                 | | Slave_IO_State                 |
                            Master_Host    | Master_User |     Master_Host    | Master_User |
                            Master_Port |                      Master_Port |
                             Connect_Retry | Master_Log_File Connect_Retry | Master_Log_File
                            | Read_Master_Log_Pos |            | Read_Master_Log_Pos |
                            Relay_Log_File                     Relay_Log_File
                            | Relay_Log_Pos |                  | Relay_Log_Pos |
                            Relay_Master_Log_File |            Relay_Master_Log_File |
                            Slave_IO_Running |                 Slave_IO_Running |
                            Slave_SQL_Running |                Slave_SQL_Running |
                             Replicate_Do_DB |                  Replicate_Do_DB |
                            Replicate_Ignore_DB |              Replicate_Ignore_DB |
                            Replicate_Do_Table |               Replicate_Do_Table |
                            Replicate_Ignore_T                 Replicate_Ignore_T
                            able | Replicate_Wild_Do_Table | able | Replicate_Wild_Do_Table |
                            Replicate_Wild_Ignore_Table |      Replicate_Wild_Ignore_Table |
                            Last_Errno | Last                  Last_Errno | Last
                            _Error | Skip_Counter |            _Error | Skip_Counter |
                            Exec_Master_Log_Pos |              Exec_Master_Log_Pos |
                            Relay_Log_Space |                  Relay_Log_Space |
                            Until_Condition                    Until_Condition
                            | Until_Log_File | Until_Log_Pos | Until_Log_File | Until_Log_Pos
                            | Master_SSL_Allowed |             | Master_SSL_Allowed |
                            Master_SSL_CA_File | Mas           Master_SSL_CA_File | Mas
                            ter_SSL_CA_Path |                  ter_SSL_CA_Path |
                            Master_SSL_Cert |                  Master_SSL_Cert |
                            Master_SSL_Cipher |                Master_SSL_Cipher |
                            Master_SSL_Key | Seconds           Master_SSL_Key | Seconds
                            _Behind_Master |                   _Behind_Master |
                            ----------------+--------          ----------------+--------
                            ---------------+                   ---------------+
                            | Waiting for master to send event | Waiting for master to send event
                            | 192.168.1.3 | repl     |    3306 | 192.168.1.2 | repl     |    3306
                            |                                  |
                                         30 |                               30 |
                            mysql-bin.000002 |              98 mysql-bin.000002 |              98
                            | open-relay-bin.000005            | open-relay-bin.000005
                            |          235 | mysql-bin.000002 |           235 | mysql-bin.000002
                            |Yes                            | | Yes                            |
                            Yes                 |             Yes                  |
                             replicate     |                 | replicate      |                |
                            |                                  |
                                 |                           |      |                          |
                            |          0 |                     |          0 |
                                   |        0|              98        |        0|             98
                            |             235 | None           |             235 | None
                            |                 |              0|                  |             0
                            | No            |                | | No            |               |
                                             |               |                  |              |
                            |                 |                |                 |
                                         0 |                                0 |
                            ---------------+                   ---------------+
                            1 row in set (0.00 sec)            1 row in set (0.00 sec)

8. Check the log files at   C:\mysql\data>dir                  C:\mysql\data>dir
                            02/22/2008 01:25 PM             57 02/22/2008 01:25 PM            57
C:\mysql\data               relay-log.info                     relay-log.info
02/22/2008 09:12 PM          235 02/22/2008 09:12 PM          235
server-relay-bin.000043          server-relay-bin.000043
02/22/2008 09:12 PM           26 02/22/2008 09:12 PM           26
server-relay-bin.index           server-relay-bin.index
02/22/2008 01:22 PM       17,642 02/22/2008 01:22 PM       17,642
server.err                       server.err
02/22/2008 01:22 PM            5 02/22/2008 01:22 PM           5
server.pid                       server.pid

								
To top