系统配置：centos7
mysql5.7
ansible 2.9.1
1、安装ansible
yum install epel-release-y
yum install ansible
2、编写inventory文件 hosts.ini
[mysql_servers]
192.168.235.145 ansible_ssh_user="root" ansible_ssh_pass="xxxxxxx" mysql_role=master server_id=1
192.168.235.146 ansible_ssh_user="root" ansible_ssh_pass="xxxxxxx" mysql_role=slave server_id=2
192.168.235.147 ansible_ssh_user="root" ansible_ssh_pass="xxxxxxx" mysql_role=slave server_id=3
[mysql_servers:vars]
master_ip=192.168.235.145
mysql_version=5.7.28
mysql_root_password=sy#M*tFE(11O
mysqld_error_log=/var/log/mysqld.log
mysql_repl_user=repl
mysql_repl_password=repl
3、编写tasks任务文件 main.yml
--- - name: stop firewalld shell: systemctl stop firewalld
register: command_result
failed_when: "'FAILED' in command_result.stderr"
- name: disable firewalld
shell: systemctl disable firewalld
register: command_result
failed_when: "'FAILED' in command_result.stderr"
- name: install repository
yum :
name: https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
state: present
- name: copy global my.cnf
template:
src: templates/my.cnf.j2
dest: /etc/my.cnf
- name: install mysql '{{ mysql_version }}'
yum:
name: 'mysql-community-server-{{ mysql_version }}'
state: present
enablerepo: mysql57-community
disablerepo: mysql80-community
- name: start mysql
service:
name: mysqld
state: started
- name: Find temporary password
shell: >
echo `grep 'temporary.*root@localhost' "{{ mysqld_error_log }}" | sed 's/.*root@localhost: //'`
register: mysql_root_password_temp
- name: Set new password for mysql root user
shell: >
mysql -NBe "alter user USER() identified by '{{ mysql_root_password }}';"
--connect-expired-password -uroot -p'{{ mysql_root_password_temp.stdout }}'
- name: uninstall validate_password plugin
shell: >
mysql -NBe "uninstall plugin validate_password" -uroot -p'{{ mysql_root_password }}'
- name: install MySQL-python
yum:
name: MySQL-python
state: latest
- name: create replication user
mysql_user:
name: "{{ mysql_repl_user }}"
host: "%"
password: "{{ mysql_repl_password }}"
priv: "*.*:REPLICATION SLAVE"
state: present
login_user: root
login_password: "{{ mysql_root_password }}"
when: mysql_role == "master"
tags:
- create-repl-user
- name: change master to
mysql_replication:
mode: changemaster
master_host: "{{ master_ip }}"
master_user: repl
master_password: repl
master_auto_position: yes
login_user: root
login_password: "{{ mysql_root_password }}"
when: mysql_role == 'slave'
- name: start slave
mysql_replication:
mode: startslave
login_user: root
login_password: "{{ mysql_root_password }}"
when: mysql_role == "slave"
4、mysql配置文件模板 my.cnf.j2
[mysqld]
datadir = /var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=/var/lib/mysql/mysql-bin
server-id = {{ server_id }}
character_set_server='utf8'
skip-name-resolve
## InnoDB specific parameters
innodb_buffer_pool_size = 2G
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_log_file_size=128M
innodb_flush_method=O_DIRECT
## Transaction behavior
autocommit=ON
transaction-isolation=READ-COMMITTED
#slow_query
log_queries_not_using_indexes=1
long_query_time=1
slow_query_log=1
## Memory sizes
join_buffer_size = 8M
sort_buffer_size = 8M
read_rnd_buffer_size = 16M
tmp_table_size = 128M
max_heap_table_size = 128M
## Miscellaneous parameters
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
default_storage_engine=InnoDB
# max binlog keeps days
expire_logs_days = 7
max_connections=300
log_timestamps=system
slave_net_timeout=3600
## Enables binary logging
max_relay_log_size=512M
binlog_format=ROW
max_binlog_size=512M
master_info_repository = table
relay_log_info_repository = table
sync_binlog=0
gtid_mode=ON
log_slave_updates=ON
enforce_gtid_consistency=ON
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
## Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
{% if mysql_role == "slave" %}
read_only=ON
{% endif %}
执行：
ansible-playbook -i hosts.ini start_deploy.yml
完整代码链接：