<转>mysql 主从服务器配置

mysql主从(master-slave)复制安装配置
Posted by 机器人 on 6th 一月 2010 in mysql

一. 环境准备

准备两台服务器，IP分别为
192.168.1.119 (主数据库服务器）
192.168.1.120 (从数据库服务器）

分别装好mysql数据库服务器

二. 安装配置

1. 在主数据库服务器上设置一个复制使用的账户，并授予replication slave权限，这里创建一个复制用户rep1，可以从IP为192.168.1.120的主机进行连接.

mysql > grant replication slave on *.* to 'rep'@'192.168.1.120' identified by '123';

2. 修改主数据库服务器的配置文件my.cnf，开启binlog，并设置server-id的值。

[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log

数据库重启后生效

3. 在主服务器上，设置读锁有效，这个操作是为了确保没有数据库操作，以便获得一个一致性的快照。

mysql > flush tables with read lock;

4. 然后得到主服务器上当前的二进制日志名的偏移量值。这个操作的目的是为了在从数据库启动以后，从这个点开始进行数据的恢复。

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     2122 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5. 建立测试数据库test1

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

6. 备份一份数据库文件，将其恢复到从服务器上，可以通过mysqldump或者直接拷贝文件的方式。这里采用直接copy文件的方式。

[~@master]# cd /var/lib/mysql
[~@master]# tar cvf test1.tar test1
test1/
test1/db.opt
[~@master]# scp test1.tar root@192.168.1.120:/var/lib/mysql/
...
[~@slave]# tar xvf test1.tar


7. 备份完毕后，主数据库恢复被写操作

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

8. 修改从数据库的配置文件my.cnf,添加server-id参数，注意这里的server-id参数必须唯一，不能和主数据库的配置相同.复制使用的用户、主数据库服务器的IP、端口以及连接丢失时，重试的时间

[mysqld]
server-id=2
master-host=192.168.1.119
master-user=rep1
master-password=123456
master-connect-retry=60

9. 在从服务器上，使用–skip-slave-start 选项启动从数据库，这样就不会立即启动从数据库上的复制进程，方便对数据库的服务进程进行进一步的配置

[~@slave]# /usr/bin/mysqld_safe --skip-slave-start &
[~@slave]# Starting mysqld daemon with databases from /var/lib/mysql

10. 指定开始执行复制的日志文件和位置

mysql> change master to
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=2207;
Query OK, 0 rows affected (0.01 sec)

这里的log_file和log_pos是通过主服务器中执行

mysql > show master stauts;

得到

11. 在从服务器上,启动slave进程

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

12. 这时在slave上执行show processlist 命令将显示类似如下进程

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

这表明slave已经连接上master,并开始接受并执行日志.

13.测试复制服务器的正确性,在主数据库上执行一个更新操作,观察是否在从数据库上同步.

mysql> use test1;
Database changed
mysql> create table rep(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into rep values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

15. 在从数据库上检查新表是否被创建,数据是否被同步.

mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| rep             |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from rep;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

可以看到数据可以正确同步到从数据库上,复制服务配置成功完成.

三 复制启动选项

这些选项可以在启动时加入,也可以直接写在my.cnf里

1. log-slave-updates
这个参数用来配置从服务器上的更新操作是否写进进制日志,默认不打开,但是,如果这个从服务器同时也要作为其它服务器的主服务器时,就需要启动.
这个参数需要和log-bin一起使用.

2. master-connect-retry
这个参数用来设置在和主服务器连接丢失的时候,重度的时间间隔.

3. read-only
这个参数用来设置从服务器只能接受超级用户的更新操作,从而限制应用程序错误的对从服务器的更新操作.

4. 指定复制的数据库或者表
可以使用replicate-do-db、replicate-do-table、replicate-ignore-db、replicate- ignore-table或replicate-wild-do-table来指定从主数据库复制到从从数据库的数据库或者表。

四　日常管理维护

1. 查看从服务器状态

mysql> show slave status \G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.1.119
                Master_User: rep1
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 2400
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 428
      Relay_Master_Log_File: mysql-bin.000001
           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: 2400
            Relay_Log_Space: 428
            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: 0
1 row in set (0.00 sec)

ERROR:
No query specified

主要关心”Slave_IO_Running”和“Slave_SQL_Running”这两个进程的状态是否为”yes”。只要其中一个进程状态为no，则表示复制进程停止，错误原因可以从“Last_Errno”字段中的值看到。

2. 主从服务器手工同步
从服务器由于各种原因导致更新速度较慢，从而是主从服务器之间的数据差距越来越大，最终对某些应用产生影响，这种情况下，就需要定期地进行主从服务器的数据同步，使得主从服务器差距能够减到最小。常用方法是：在负载较低的时候暂时阻塞主数据库的更新，强制主从数据库更新同步。

阻塞更新

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     2400 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

在从服务器上，执行下面语句，其中master_pos_wait()函数的参数是前面步骤中得到的复制坐标值。

mysql> select master_pos_wait('mysql-bin.000001','2400');
+--------------------------------------------+
| master_pos_wait('mysql-bin.000001','2400') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)

这个语句会阻塞直到从服务器达到指定的日志文件和偏移量后，返回０，如果返回-1。

在主服务器上，执行下面语句允许主服务器重新开始处理更新

mysql>  unlock tables;
Query OK, 0 rows affected (0.00 sec)

3. log event entry exceeded max_allowed_packet的处理

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.01 sec)
mysql> set @@global.max_allowed_packet=16777216

同时在my.cnf里设置max_allowed_packet=16M，保证下次数据库重新启动后参数继续有效。

好了，暂时总结这么多.