Mysql主从复制配置

1.环境

系统版本：Centos5.8  x86_64

               Mysql版本：Mysql5.6.16

安装方式：源码编译

服务器规划：

                               Master:10.0.0.171

                               Slave1:10.0.0.172

                               Slave2:10.0.0173

2.安装

2.1安装依赖

[root@zookeeper1 ~]# yum  install  –y  gcc  gcc-c++  cmake  ncurses ncurses-devel

2.2编译并安装

[root@zookeeper1 ~]# cd  mysql-5.6.16

[root@zookeeper1 mysql-5.6.16]# cmake .   && make  &&  make install                

注意：若cmake出现错误，解决后需删除CMakeCache.txt再重新cmake

3.初始化配置

[root@zookeeper1 ~]# groupadd  mysql

[root@zookeeper1 ~]# useradd  -r -M  -g  mysql mysql

[root@zookeeper1 ~]# cd /usr/local/mysql

[root@zookeeper1 mysql]# chown   -R mysql.mysql  /usr/local/mysql

[root@zookeeper1mysql]#./scripts/mysql_install_db  --user=mysql

[root@zookeeper1mysql]# chown  -R  root /usr/local/mysql

[root@zookeeper1mysql]# chown  -R  mysql /usr/local/mysql/data

[root@zookeeper1mysql]# cp  support-files/mysql.server  /etc/init.d/mysqld  

3. 主从复制配置

3.1 Master 所需操作

§启用binlog

§修改server-id

§创建主从复制账户

[root@zookeeper1 mysql]# vim my.cnf

[mysqld]

log_bin=mysql-master-bin.log                                   //启动binlog

sync_binlog=1                                  

innodb_flush_log_at_trx_commit=1      

//以上两条为防止服务器宕机但数据未保存到硬盘造成数据丢失

datadir =/usr/local/mysql/data

//数据存储位置

server_id = 1

//server-id 必须不同

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[root@zookeeper1 mysql]# services mysqld start            //启动mysql服务

[root@zookeeper1 mysql]# mysql -u root -p                      //登录mysql，密码为challenger

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 12

Server version: 5.6.16-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or itsaffiliates. All rights reserved.

Oracle is a registered trademark of OracleCorporation and/or its

affiliates. Other names may be trademarks oftheir respective

owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ toclear the current input statement.

mysql> grant replication slave on *.* to  ‘repl’@10.0.0.172 identified by ‘challenger‘ ;

mysql> grant replication slave on *.* to  ‘repl’@10.0.0.173 identified by ‘challenger‘ ;

//建立复制账号，只能10.0.0.172，和10.0.0173使用

mysql>  flush privileges;

//刷新权限

mysql> show master status\G;

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

           File: mysql-master-bin.000004

       Position: 755

   Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

//记下File名称和Position位置，配置从服务器需要。File为二进制日志文件名，Position是二进制日志的记录位置

3.2 Slavs所需操作

注意：每台Slave的配置操作除server-id不同外，其它配置均相同

§修改server-id

§配置master参数

§启动Slave线程

[root@zookeeper2 mysql]#  vim  my.cnf

[mysqld]

sync_binlog=1

innodb_flush_log_at_trx_commit=1

//同Master配置作用相同

read_only = 1                                    //关闭Slave服务器的写操作

datadir =/usr/local/mysql/data

server_id = 11                                   //server-id,必须不同

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[root@zookeeper2 mysql]#  service mysqld start             //启动mysql服务

[root@zookeeper2 mysql]# mysql -u root -p                      //登录mysql，密码为challenger

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.16 Source distribution

Copyright (c) 2000, 2011, Oracle and/or itsaffiliates. All rights reserved.

Oracle is a registered trademark of OracleCorporation and/or its

affiliates. Other names may be trademarks oftheir respective

owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ toclear the current input statement.

mysql> change master to

       -> master_host=‘10.0.0.171’,master_user=‘repl‘,master_password=’challenger’,

       -> master_log_file=’mysql-master-bin.000004‘,master_log_pos=755;

//设置master参数，主机名，用户，密码，二进制文件名，同步位置

mysql> start slave;                          //启动复制线程

mysql> show slave status\G;      

//查看slave状态，若出现

//            Slave_IO_Running: Yes

//          Slave_SQL_Running: Yes

//则表明主从复制已正常启动

MySQL主从复制,古老的榕树,5-wow.com