环境：master: 192.168.0.3

        Slave: 192.168.0.4

Mysql版本为5.0.67（编译安装）

database: eric

1.       Master服务器启动mysql，

a)         #mysql –uroot –proot

b)        创建一个有复制权限的用户，只限slave远程连接访问.

                         i.              mysql>grant replication slave on *.* to replication@192.168.0.4 identified by ‘password’;

                       ii.              mysql>flush privileges;

c)         mysql>flush tables with read lock; #锁定master服务器所有表的写入。

d)        重新打开一终端，备份要复制的数据库。

                         i.              Var]#tar zcvf eric.tar.gz eric/    //eric所在路径/opt/mysql/var/eric/即一个库。

                       ii.              ]#scp eric.tar.gz 192.168.0.4:/opt/mysql/var/    //将主服务器的库传到slave相应路径下。

e)         返回上一终端。

                         i.              Mysql>show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000014 |       98 | eric         |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.01 sec)

其中mysql-bin.000014和98二个值将是slave与master的同步点。

f)          给数据库解锁（当备份完成后）　mysql>unlock tables;

g)         编辑mysql的配置文件。Vim /etc/my.cnf 设置这三个参数，没有的添加，有的直接更改即可。

log-bin=mysql-bin

server-id  = 1

binlog-do-db=eric

                   保存退出。

2.       Slave服务器配置

a)         将从master中备份的库解压到相应路径下（退库的导入）

                         i.              Var]#tar zxvf eric.tar.gz ./

b)        修改my.cnf

server-id=2

master-host=192.168.0.3

master-user= replication

master-password= password

log-bin=

3.       重启master, slave的mysql服务

a)         注意顺序，先重启master-à 然后是slave.

b)        Slave服务器重启后，登录mysql

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to

-> master_host='192.168.0.3',

-> master_user='replication',

-> master_password='password',

-> master_log_file='mysql-bin.000014',

-> master_log_pos=98;

Query OK, 0 rows affected (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\\G;

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

             Master_Host: 192.168.0.3

             Master_User: replication

             Master_Port: 3306

             Connect_Retry: 60

            Master_Log_File: mysql-bin.000014

        Read_Master_Log_Pos: 98

            Relay_Log_File: alan-relay-bin.000002

            Relay_Log_Pos: 235

      Relay_Master_Log_File: mysql-bin.000014

            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: 98

            Relay_Log_Space: 235

            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.01 sec)

ERROR: No query specified

当这个参数都为yes时，证明主从复制成功

     Slave_IO_Running: Yes   Slave_SQL_Running: Yes

设置只读：

Alter database dbname set READ_ONLY

恢复读写：

Alter DATABASE db_name SET READ_WRITE(责任编辑：admin)下载本文