19.6. Replication

过程 19.1. Master 设置步骤

1. 配置 my.cnf 文件

   确保主服务器主机上my.cnf文件的[mysqld]部分包括一个log-bin选项。该部分还应有一个server-id=Master_id选项

   # vim /etc/mysql/my.cnf
   
   server-id               = 1
   log_bin                 = /var/log/mysql/mysql-bin.log
   expire_logs_days        = 10
   max_binlog_size         = 100M
   binlog_do_db            = test
   binlog_ignore_db        = mysql
   					

   bind-address默认是127.0.0.1你必须更改它，否则Slave将无法链接到 Master

   #bind-address		= 127.0.0.1
   bind-address		= 0.0.0.0
   					

   重启服务器

   neo@netkiller:~$ sudo /etc/init.d/mysql reload
    * Reloading MySQL database server mysqld          [ OK ]
   					

   建议使用reload,如果不起作用再用restart

   					
   mysql> SHOW GLOBAL VARIABLES like 'server_id';
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | server_id     | 1     |
   +---------------+-------+
   1 row in set (0.00 sec)
   					
   					

2. 登录slave服务器，测试主库3306工作情况，如果看到下面相关信息表示工作正常。

   					
   # telnet 192.168.1.246 3306
   Trying 192.168.1.246...
   Connected to 192.168.1.246.
   Escape character is '^]'.
   I
   5.1.61-0ubuntu0.11.10.1-log1W<gs/*'#}p<u[J=5//:
   					
   					

3. 创建账户并授予REPLICATION SLAVE权限

   					
   mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'replication'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
   mysql> FLUSH PRIVILEGES;
   					
   					

   创建监控账号monitor（可选项），monitor 使用SHOW MASTER STATUS和SHOW SLAVE STATUS命令但没有复制权限

   GRANT REPLICATION CLIENT ON *.* TO monitor@'192.168.245.131' IDENTIFIED BY 'monitorpass'

4. 锁表禁止写入新数据

   					
   mysql> FLUSH TABLES WITH READ LOCK;
   					
   					

5. 查看Master 工作状态

   					
   mysql> SHOW MASTER STATUS;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000002 |      106 | test         | mysql            |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)
   					
   					

   如果显示下面内容表示，配置不正确

   					
   
   mysql> SHOW MASTER STATUS;
   Empty set (0.02 sec)
   					
   					

   取得快照并记录日志名和偏移量后，可以在主服务器上重新启用写活动

   					
   mysql> UNLOCK TABLES;
   					
   					

过程 19.2. Slave 设置步骤

1. 配置my.cnf

   从服务器的ID必须与主服务器的ID不相同,如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。

   # vim /etc/mysql/my.cnf
   
   [mysqld]
   server-id               = 2
   					

2. # service mysql restart
   mysql start/running, process 22893
   					

3. 指定 master 相关参数

   在从服务器上执行下面的语句，用你的系统的实际值替换选项值

   					
   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;
   	   				
   					

   如果是全新服务器，空数据库可以忽略MASTER_LOG_FILE与MASTER_LOG_POS

   CHANGE MASTER TO MASTER_HOST='192.168.245.129', MASTER_USER='replication', MASTER_PASSWORD='slavepass';
   					

   如果是复制已经存在的数据库需要MASTER_LOG_FILE与MASTER_LOG_POS选项

   首先到Master上运行 show master status 找到File与Position

   					
   mysql> show master status \G
   *************************** 1. row ***************************
               File: mysql-bin.000009
           Position: 3988
       Binlog_Do_DB:
   Binlog_Ignore_DB:
   1 row in set (0.00 sec)
   					
   					

   CHANGE MASTER TO
        MASTER_HOST='192.168.2.1',
        MASTER_USER='replication',
        MASTER_PASSWORD='kJZBTo3BjMx9AnmD9Ryn',
        MASTER_LOG_FILE='mysql-bin.000009',
        MASTER_LOG_POS=3988;
   					

4. 启动从服务器线程

   					
   mysql> START SLAVE;
   Query OK, 0 rows affected (0.00 sec)
   					
   					

5. SLAVE STATUS

   					
   mysql> SHOW SLAVE STATUS\G
   *************************** 1. row ***************************
                Slave_IO_State: Connecting to master
                   Master_Host: 192.168.245.129
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File:
           Read_Master_Log_Pos: 4
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 98
         Relay_Master_Log_File:
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 0
               Relay_Log_Space: 98
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: NULL
   1 row in set (0.00 sec)
   					
   					

1. 登录 master

   复制进程的信息

   SHOW PROCESSLIST语句可以提供在主服务器上和从服务器上发生的关于复制的信息

   mysql> SHOW PROCESSLIST\G
   *************************** 1. row ***************************
        Id: 62
      User: replication
      Host: ken-hx409.local:36465
        db: NULL
   Command: Binlog Dump
      Time: 679
     State: Has sent all binlog to slave; waiting for binlog to be updated
      Info: NULL
   *************************** 2. row ***************************
        Id: 64
      User: root
      Host: localhost
        db: NULL
   Command: Query
      Time: 0
     State: NULL
      Info: SHOW PROCESSLIST
   2 rows in set (0.00 sec)
   
   					

2. 登录从库，查看复制线程

   					
   mysql> SHOW PROCESSLIST\G
   *************************** 1. row ***************************
        Id: 273
      User: root
      Host: localhost
        db: NULL
   Command: Query
      Time: 0
     State: NULL
      Info: SHOW PROCESSLIST
   *************************** 2. row ***************************
        Id: 276
      User: system user
      Host:
        db: NULL
   Command: Connect
      Time: 2
     State: Waiting for master to send event
      Info: NULL
   *************************** 3. row ***************************
        Id: 277
      User: system user
      Host:
        db: NULL
   Command: Connect
      Time: 2
     State: Has read all relay log; waiting for the slave I/O thread to update it
      Info: NULL
   3 rows in set (0.00 sec)
   					
   					

   如果没有复制进程，请使用start slave;启动

   					
   mysql> SHOW PROCESSLIST\G
   *************************** 1. row ***************************
        Id: 273
      User: root
      Host: localhost
        db: NULL
   Command: Query
      Time: 0
     State: NULL
      Info: SHOW PROCESSLIST
   1 row in set (0.02 sec)
   
   mysql> start slave;
   Query OK, 0 rows affected (0.00 sec)
   					
   					

3. 登录 master

   					
   mysql> insert into foo(id,data) values(2,'Hello world!!!');
   Query OK, 1 row affected (0.00 sec)
   					
   					

4. 登录 slave

   					
   mysql> select * from foo;
   					
   					

   在master服务器上插入一条记录，你可以立刻在slave服务器上看到变化。

1. Master 锁表禁止写入新数据

   					
   mysql> FLUSH TABLES WITH READ LOCK;
   					
   					

2. Slave 停止复制进程

   					
   mysql> stop slave;
   					
   					

3. 备份Master数据库

   					
   mysqldump yourdb | gzip > yourdb.sql.gz
   					
   					

4. 恢复数据库

   如果使用mysqldump备份主服务器的数据，将转储文件装载到从服务器

   					
   # zcat yourdb.sql.gz | mysql -u root -p yourdb
   					
   					

5. 启动 Slave 复制进程

   					
   mysql> start slave;
   					
   					

6. 解除 Master 表锁定

   					
   mysql> UNLOCK TABLES;