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