mysql 学习笔记之主从复制 Replication

1. 主从复制 Replication

3.1 架构图

3.2 参数配置

3.2.1 创建启闭脚本

login.sh

#!/bin/bashmysql -h227.0.0.1 -uroot -P3306 -p'Yxc@3306'

start.sh

#!/bin/bashmysqld_safe --defaults-file=/data/mysql/3306/my3306.cnf --user=mysql &

shutdown.sh

#!/bin/bash/usr/local/mysql/bin/mysqladmin -uroot -P3306 -p'Yxc@3306' -S /data/mysql/3306/mysql3306.sock shutdown

3.2.2 编辑主节点配置文件

[mysqld]basedir=/usr/local/mysqldatadir=/data/mysql/3306/dataport=3306socket=/data/mysql/3306/mysql3306.sockcharacter-set-server=utf8log-error=/data/mysql/3306/log/error3306.logpid-file=/data/mysql/3306/mysqld3306.pidlog_bin=mysql_binbinlog-format=Rowserver-id=51

3.2.3 创建从节点配置文件

[mysqld]basedir=/usr/local/mysqldatadir=/data/mysql/3306/dataport=3306socket=/data/mysql/3306/mysql3306.sockcharacter-set-server=utf8log-error=/data/mysql/3306/log/error3306.logpid-file=/data/mysql/3306/mysqld3306.pidlog-bin=mysql_binserver-id=52relay-log-index=slave-relay-bin.indexrelay-log=slave-relay-bin

3.3 创建用户

create user 'repl'@'%' identified by 'repl';                  grant replication slave on *.* to 'repl'@'%';

3.4 配置复制

主库上执行命令，获取当前binlog日志及位置

show master status;

获取file，position信息，在两个从库上分别执行如下脚本

CHANGE MASTER TOMASTER_HOST='192.168.56.5',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql_bin.000002',MASTER_LOG_POS=154;

从库上，启动复制，并查看复制状态

start slave;show slave status;

^说明: 如出现如图字样，表示成功。关于slave的命令，可以通过help slave;