Ansible操作MySQL常用的几个模块 - qidao123.com ToB IT社区-企服评测·应用市场

- name: 创建MySQL数据库用户--user_test
mysql_user:
# ----- 登陆数据库
login_host: "localhost"
login_port: 3306
login_user: root
login_password: "{{ root_password }}"
## 添加login_unix_socket，否则报错:FAILED! => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": false, "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (2002, "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)\
login_unix_socket: "/你sock文件的路径/mysql.sock"
# ----- 创建的用户的信息
name: "user_test"
host: "%"
password: "{{ user_test_password }}"
priv: "{{ item }}"
# -----
state: present
with_items:
- "*.*:REPLICATION SLAVE,REPLICATION CLIENT"
- "*.*:PROCESS"
- "performance_schema.*:select"
### 权限之间不要有空格，否则报错--例如:(item=*.*:REPLICATION SLAVE, REPLICATION CLIENT) => {"ansible_loop_var": "item", "changed": false, "item": "*.*:REPLICATION SLAVE, REPLICATION CLIENT", "msg": "invalid privileges string: Invalid privileges specified: frozenset([' REPLICATION CLIENT'])"}

复制代码

- hosts: db_servers
tasks:
- name: Query MySQL table
mysql_query:
login_host: localhost
login_user: db_user
login_password: db_pass
db: db_name
query: SELECT * FROM table_name WHERE column_name = 'value';
register: result
- name: Print query result
debug:
var: result.stdout_lines

复制代码

# 从库，执行Change Master
# -- 只有是从库的时候，才需要执行这一步
- name: "从库：Change Master"
mysql_replication:
mode: changemaster
# ------------
# 登陆MySQL
login_user: root
login_password: "{{ mysql_database_user_root_password }}"
# ------------
# Change Master
master_host: "{{ mysql_replication_master_host }}"
master_port: "{{ mysql_replication_master_port }}"
master_user: "{{ mysql_replication_user_name }}"
master_password: "{{ mysql_replication_user_password }}"
master_log_file: "{{ mysql_replication_master_status['File'] }}"
master_log_pos: "{{ mysql_replication_master_status['Position'] }}"
when: db_role == "slave"
# 从库：Start Slave
# -- 只有是从库的时候，才需要执行这一步
- name: 从库：Start Slave
mysql_replication:
mode: startslave
# ------------
# 登陆MySQL
login_user: root
login_password: "{{ mysql_database_user_root_password }}"
# ------------
when: db_role == "slave"
# 从库：获取从库信息
- name: 从库：获取从库信息
mysql_replication:
mode: getslave
# ------------
# 登陆MySQL
login_user: root
login_password: "{{ mysql_database_user_root_password }}"
# ------------
register: mysql_replication_slave_status
when: db_role == "slave"
# 输出从库信息
- name: "从库：Slave Status"
debug:
msg: "Slave_IO_Running【{{ mysql_replication_slave_status['Slave_IO_Running'] }}】 / Slave_SQL_Running【{{ mysql_replication_slave_status['Slave_SQL_Running'] }}】 / Slave_SQL_Running_State【{{ mysql_replication_slave_status['Slave_SQL_Running_State'] }}】 / Seconds_Behind_Master【{{ mysql_replication_slave_status['Seconds_Behind_Master'] }}】"
when: db_role == "slave"

复制代码

# 启动MySQL服务
- name: 启动MySQL服务
service:
name: mysqld
state: started
enabled: yes

复制代码

mysqladmin -h localhost -u root -p'在path_mysql_error_log中查找的 A temporary password' password '新密码'

复制代码

mysqladmin -u root password "newpassword"

复制代码

# 通过得到的临时口令，修改数据库的口令为变量文件中设置的口令
- name: 修改MySQL的临时口令
shell: "mysqladmin -h localhost -u root password '{{ new_root_password }}'"
ignore_errors: yes

复制代码