Mysql的master,slave的配置

MYSQL的master,slave环境的搭建

试验环境：

Linux5.5(32bit)

撰写人：hx10

日期:2010/7/3  hangzhou

Blog:http://hi.baidu.com/hx10

试验环境：

Master:10.80.11.203

Slave1:10.80.11.204

Slave2:10.80.11.205

 

安装mysql

 

cd /usr/local

tar -zxvf mysql-5.1.48-linux-i686-glibc23.tar.gz

mv mysql-5.1.48-linux-i686-glibc23  mysql

groupadd mysql

useradd -g mysql mysql

cd /usr/local/mysql

chown -R root /usr/local/mysql

chgrp -R mysql /usr/local/mysql

chown -R mysql /usr/local/mysql/data

scripts/mysql_install_db --user=mysql

chown -R root .

chown -R mysql data

chgrp -R mysql .

cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf

cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld

chkconfig --add mysqld

 

增加环境变量

echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile

source /etc/profile

 

主节点：     

首先在master上建立2个数据库和表

#service mysqld start

#mysql

mysql>create database www;

mysql>use www;

mysql>create table www(id int);

mysql>insert into www values(1);

mysql> select * from www;

+------+

| id   |

+------+

|    1 |

+------+

1 row in set (0.01 sec)

 

 

mysql>create database blog;

mysql>use blog;

mysql>create table blog(id int);

mysql>insert into blog values(1);

mysql> select * from blog;

+------+

| id   |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

 

 

1.vim /etc/my.cnf

注意一下2个参数,这一段在[mysqld]节点中添加

log-bin=mysql-bin       //日志为2进制，不需要更改

server-id    =1         //为1就是Master,不需要更改

binlog-do-db=blog        //要同步的库

binlog-do-db=www         //要同步的库

binlog-ignore-db=mysql,test,information_schema   //是不要记录日志的数据库名，多个数据库中间用逗号(,)隔开

 

 

然后把innodb前面的#去掉，结果如下

innodb_data_home_dir = /usr/local/mysql/data/      //innodb的表空间位置

innodb_data_file_path = ibdata1:50M:autoextend     //表空间的名字，开始50M

innodb_log_group_home_dir = /usr/local/mysql/data/ 

innodb_buffer_pool_size = 256M                     //为系统内存的50-80%

innodb_additional_mem_pool_size = 20M

innodb_log_file_size = 64M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

 

 

 

然后启动数据库,让配置文件生效

#service mysqld restart

 

2.从master服务器添加要从slave服务器访问master服务器的有权限的账号

 

#mysql -u root -p

mysql>grant replication slave on *.* to repluser@'10.80.11.204' identified by '123456';

mysql>grant replication slave on *.* to repluser@'10.80.11.205' identified by '123456';

mysql>flush privileges;

 

 

格式:GRANT REPLICATION SLAVE ON *.* TO '账号'@'从服务器IP或主机名' IDENTIFIED BY '密码';

 

3.备份master数据库数据

mysql> flush tables with read lock;  //不要退出这个终端，否则这个锁就不生效了。从服务器的数据库建好后。在主服务器执行解锁

 

同时要记录下mysql-bin.000003和1271

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB              |

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

| mysql-bin.000002 |      106 | blog,www     | mysql,test,information_schema |

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

1 row in set (0.01 sec)

取得快照并记录日志名和偏移量

 

开启另一个终端对主服务器数据目录做快照。

#cd /usr/local/mysql/data

#tar -zcvf backup.tar.gz www blog

 

此时在主库解开table的锁定

mysql> unlock tables;

 

 

从节点：

 

安装mysql方法同主节点

1. vim /etc/my.cnf   增加以下几项

server-id  = 2

master-host     =   10.80.11.203

master-user     =   repluser

master-password =   123456

master-port     =  3306

master-connect-retry=60

replicate-do-db=www   //告诉slave只做www数据库的更新 

replicate-do-db=blog  //告诉slave只做blog数据库的更新 

log-slave-updates

 

3.把从主数据库服务器备份出来的数据库导入到从服务器中

先用scp把主服务器上的backup.tar.gz拷贝过来,解压到/usr/local/mysql/data目录

#cd /usr/local/mysq/data

#tar -zxvf backup.tar.gz

 

 

 

启动从库服务器

#service mysqld start

停止slave服务，设置主服务器的各种参数

#mysql

mysql>slave stop;

然后敲入以下代码,一行一行的复制

change master to

MASTER_HOST='10.80.11.203',

MASTER_USER='repluser',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=106;

mysql> slave start;

 

然后在master上插入记录，会发现在slave上也会出现

 

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.80.11.203

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000003

          Read_Master_Log_Pos: 1271

               Relay_Log_File: squid2-relay-bin.000004

                Relay_Log_Pos: 617

        Relay_Master_Log_File: mysql-bin.000003

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: www,blog

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

              Relay_Log_Space: 773

              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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

1 row in set (0.01 sec)

 

从节点2的配置跟从节点1的配置一样，只是server-id  改成3

 

 

最重要是以下三点：

 

Slave_IO_Running：

是否要从 Master Server 复製 Binary Log 资料，必须为 Yes。

 

Slave_SQL_Running：

是否要执行从 Master Server 复製过来的 Binary Log 资料，必须为 Yes。

 

Seconds_Behind_Master：

Slave 的资料落后了 Master 多少秒，执行一段时间后应该会是零。

然后在master上更新数据，会发现在2台slave上都能查询到数据，本实验测试成功。

本文出自 “无云安全技术站” 博客，请务必保留此出处http://hx100.blog.51cto.com/44326/343390