Replicating One Database
|
|
For this example, you're going to create a new database, with a table, and replicate this onto another server. You need to have two servers running MySQL (ideally the same version), and the two servers need to be able to see each other to test this example.
First, on the master create a database called replication_db, a table called replication_ table, and add some data to this table, as follows:
mysql> CREATE DATABASE replication_db; Query OK, 1 row affected (0.01 sec) mysql> USE replication_db; Database changed mysql> CREATE TABLE replication_table(f1 INT,f2 VARCHAR(20)); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO replication_table (f1,f2) VALUES(1,'first'); Query OK, 1 row affected (0.03 sec)
Now grant permission to a slave to replicate. The slave user will be replication_user, with a password of replication_pwd:
mysql> GRANT REPLICATION SLAVE ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';
On the slave, shut the server down, and add the code below to the configuration file (my.cfg or my.ini). Replace the master-host setting with the IP of your slave server. The server_id can be any number, as long as it's not the same as the server_id on the master:
master-host = 192.168.4.100 master-user = replication_user master_password = replication_pwd server-id = 3 replicate-do-db = replication_db
On the slave create the replication_db database, and copy the replication_table data from the master to the slave (being MyISAM tables, the data will be in the replication_db directory). See Chapter 11, "Database Backups," if you are not sure how to do this. Make sure when you copy the files to the slave that the permissions are correct (in Unix, chown mysql.mysql *, chmod 700 *). Also, be aware that if your master server has already been using binary logging, you'll need to reset the binary log with RESET MASTER so that the slave can start updating from the beginning of the first binary log. Now start the slave server and connect. Once you've connected, check the slave status to see if replication has begun properly:
mysql> SHOW SLAVE STATUS; +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | 192.168.4.100 | replication_user | 3306 | 60 | g-bin.001 | 79 | s-bin.002 | 124 | g-bin.001 | Yes | Yes | replication_db | | 0 | | 0 | 79 | 132 | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ 1 row in set (0.00 sec) mysql> INSERT INTO replication_table (f1,f2) VALUES(2,'second'); Query OK, 1 row affected (0.06 sec) mysql> SELECT * FROM replication_table; +------+--------+ | f1 | f2 | +------+--------+ | 1 | first | | 2 | second | +------+--------+ 2 rows in set (0.00 sec) mysql> SHOW SLAVE STATUS; +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | 192.168.4.100 | replication_user | 3306 | 60 | g-bin.001 | 180 | s-bin.002 | 225 | g-bin.001 | Yes | Yes | replication_db | | 0 | | 0 | 180 | 233 | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ 1 row in set (0.00 sec)
Back on the master you can run DELETE and UPDATE statements, and these will be mirrored on the slave. For example:
mysql> DELETE FROM replication_table WHERE f1=1; Query OK, 1 row affected (0.34 sec) mysql> UPDATE replication_table SET f1=1; Query OK, 1 row affected (0.05 sec)
Checking on the slave, you'll see the following:
mysql> SELECT * FROM replication_table; +------+--------+ | f1 | f2 | +------+--------+ | 1 | second | +------+--------+ 1 row in set (0.01 sec)
The slave does not have to stay connected to the master at all times to remain in sync as long as the binary logs are correct, as the next example demonstrates. First, shut down the slave:
% /usr/local/mysql/bin/mysqladmin -uroot -pg00r002b shutdown 020821 17:25:37 mysqld ended
Then add another record to the master:
mysql> INSERT INTO replication_table (f1,f2) VALUES(3,'third'); Query OK, 1 row affected (0.03 sec)
Back on the slave, restart the server, connect to the replication_db database, and you'll see the new record has been added:
% bin/mysqld_safe & [1] 1989 % /usr/local/mysql/bin/mysql -uroot -pg00r002b mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.0.2-alpha-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT * FROM replication_table; +------+--------+ | f1 | f2 | +------+--------+ | 3 | third | | 1 | second | +------+--------+ 2 rows in set (0.02 sec)
The master could go down as well, and the slave would keep trying to reconnect (every master-connect-retry seconds, which has a default of 60) until the master was up again.
Be careful when making changes to the binary logs, though, because this is the only thing the slave has to go on. The next example shows an example where data can get lost. First, shut down the slave:
% /usr/local/mysql/bin/mysqladmin -uroot -pg00r002b shutdown 020821 17:25:37 mysqld ended
As before, add another record to the master, but this time run the RESET MASTER statement afterward (this removes all old binary logs and starts again with binary log 1):
mysql> INSERT INTO replication_table (f1,f2) VALUES(4,'fourth'); Query OK, 1 row affected (0.01 sec) mysql> RESET MASTER; Query OK, 0 rows affected (0.03 sec)
Now, when you restart the slave, it will not pick up the change:
% bin/mysqld_safe & [1] 1989 % /usr/local/mysql/bin/mysql -uroot -pg00r002b mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.0.2-alpha-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT * FROM replication_table; +------+--------+ | f1 | f2 | +------+--------+ | 3 | third | | 1 | second | +------+--------+ 2 rows in set (0.00 sec)
You can see why by looking at the slave status:
mysql> SHOW SLAVE STATUS; +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | 192.168.4.100 | replication_user | 3306 | 60 | g-bin.001 | 443 | s-bin.004 | 4 | g-bin.001 | Yes | Yes | replication_db | | 0 | | 0 | 443 | 500 | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ 1 row in set (0.00 sec)
The master log is supposed to be at position 443—compare this to what the master binary log is actually at, on the master:
mysql> SHOW MASTER STATUS; +-----------+----------+--------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +-----------+----------+--------------+------------------+ | g-bin.001 | 79 | | | +-----------+----------+--------------+------------------+ 1 row in set (0.00 sec)
You can bring them back into sync by resetting the slave, as follows:
mysql> RESET SLAVE; Query OK, 0 rows affected (0.01 sec)
The slave status has now changed and will again be looking at the beginning of binary log 1, or position 79:
mysql> SHOW SLAVE STATUS; +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | 192.168.4.100 | replication_user | 3306 | 60 | g-bin.001 | 79 | s-bin.002 | 124 | g-bin.001 | Yes | Yes | replication_db | | 0 | | 0 | 79 | 132 | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ 1 row in set (0.00 sec)
Now back on the master, add the record again, and take a look at the master status, where the binary log has moved to position 180:
mysql> INSERT INTO replication_table (f1,f2) VALUES(4,'fourth'); Query OK, 1 row affected (0.00 sec) mysql> SHOW MASTER STATUS; +-----------+----------+--------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +-----------+----------+--------------+------------------+ | g-bin.001 | 180 | | | +-----------+----------+--------------+------------------+ 1 row in set (0.00 sec)
And the slave has once again picked up the INSERT:
mysql> SELECT * FROM replication_table; +------+--------+ | f1 | f2 | +------+--------+ | 3 | third | | 1 | second | | 4 | fourth | +------+--------+ 3 rows in set (0.00 sec)
If you're having an astute day, you may have noticed that the record has been added twice on the master:
mysql> SELECT * FROM replication_table; +------+--------+ | f1 | f2 | +------+--------+ | 3 | third | | 1 | second | | 4 | fourth | | 4 | fourth | +------+--------+ 4 rows in set (0.00 sec)
This example serves as a warning that just because replication is working, there is no guarantee that the data on both servers is identical. With good design (such as adding a primary key to the table), you could have avoided this problem; but it pays to carefully monitor your slave and master statuses and be careful when working with the binary logs.
|
|
