mysql主备复制关系_mysql主备复制模式搭建

在主备节点执行以下操作：

准备工作目录

# MySQL工作目录

mkdir -p $MySQLPath/mysql

# MySQL配置目录

mkdir -p $MySQLPath/mysql/etc

# MySQL数据目录

mkdir -p $MySQLPath/mysql/data

# MySQL日志目录

mkdir -p $MySQLPath/mysql/log

touch $MySQLPath/errorLog/mysqld.log

# MySQL临时目录

mkdir -p $MySQLPath/mysql/tmp

修改工作目录属主

chown -R mysql:mysql $MySQLPath

安装MySQL

tar -zxf *mysql*.tar.gz

加入本地命令及init服务

mkdir -p /usr/local/bin

ln -s $MySQLPath/install/bin/* /usr/local/bin/

cp $MySQLPath/install/support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

配置my.cnf文件

主MySQL节点

[mysqld]

datadir=$MySQLPath/data

socket=$MySQLPath/data/mysql.sock

user=mysql

symbolic-links=0

log_bin_trust_function_creators=1

log-bin=mysql-bin

binlog_format=mixed

server-id = 1

init_connect='SET NAMES utf8'

innodb_file_format=BARRACUDA

innodb_large_prefix=ON

character-set-server=utf8

[client]

socket=$MySQLPath/data/mysql.sock

[mysql]

socket=$MySQLPath/data/mysql.sock

[mysqld_safe]

socket=$MySQLPath/data/mysql.sock

log-error=$MySQLPath/errorLog/mysqld.log

pid-file=$MySQLPath/data/mysqld.pid

replicate-do-db =all

备mysql节点

[mysqld]

datadir=$MySQLPath/data

socket=$MySQLPathl/data/mysql.sock

user=mysql

symbolic-links=0

log_bin_trust_function_creators=1

log-bin=mysql-bin

binlog_format=mixed

server-id = 2

init_connect='SET NAMES utf8'

innodb_file_format=BARRACUDA

innodb_large_prefix=ON

character-set-server=utf8

[client]

socket=$MySQLPath/data/mysql.sock

[mysql]

socket=$MySQLPath/data/mysql.sock

[mysqld_safe]

socket=$MySQLPath/data/mysql.sock

log-error=$MySQLPath/errorLog/mysqld.log

pid-file=$MySQLPath/data/mysqld.pid

replicate-do-db =all

初始化MySQL

mysqld --initialize-insecure --user=mysql --basedir=$MySQLPath/install --datadir=$MySQLPath/data

启动MySQL

service mysqld start

修改密码

mysqladmin -u root password {{db_password}}

创建软连接

ln -s $MySQLPath/data/mysql.sock /var/lib/mysql

cp .my.cnf /root/.my.cnf

在主MySQL执行命令

tasks:

- name: create mysqluser replication

mysql_user:

login_user: "{{db_user}}"

login_password: '{{db_password}}'

name: replication

host: '%'

password: '{{db_password}}'

priv: '*.*:REPLICATION SLAVE'

state: present

- name: Get master binlog file name and binlog position

mysql_replication:

login_user: "{{db_user}}"

login_password: '{{db_password}}'

mode: getmaster

register: mysql_replication

- name: stop slave

mysql_replication:

login_user: "{{db_user}}"

login_password: '{{db_password}}'

login_host: '{{db_slave}}'

mode: stopslave

- name: change master

mysql_replication:

login_user: "{{db_user}}"

login_password: '{{db_password}}'

login_host: '{{db_slave}}'

mode: changemaster

master_host: '{{db_master}}'

master_user: replication

master_password: '{{db_password}}'

master_log_file: '{{mysql_replication.File}}'

master_log_pos: '{{mysql_replication.Position}}'

- name: start slave

mysql_replication:

login_user: "{{db_user}}"

login_password: '{{db_password}}'

login_host: '{{db_slave}}'

mode: startslave

- name: get slave status

mysql_replication:

login_user: "{{db_user}}"

login_password: '{{db_password}}'

login_host: '{{db_slave}}'

mode: getslave

点赞

收藏

分享

文章举报

建康

发布了9 篇原创文章 · 获赞 0 · 访问量 183

私信

关注