作者简介
曹杰，中国结算上海分公司经理，从事系统运维管理工作。
MySQL 数据库备份技术和相关方案已经非常成熟，本文不做赘述和展开。在实际场景中，数据库不可能脱离业务单独存在；因此对于备份等运维操作来说，应当在运维平台统一的调度下发起或实施，Ansible 作为近年来流行的自动化运维工具，可以定位于运维平台的核心来使用。
Ansible简介
关于 Ansible 的介绍，公开渠道可以查阅到大量资料，公众号此前也有文章专门介绍，本文不再浪费篇幅铺开。简单来说，Ansible 是一个基于Python语言开发的运维工具，由于没有客户端依赖，在管理成本方面较其他产品有显著优势；Ansible 基于模块工作，通过系统自带、客户编写、和第三方模块，可以满足各种管理任务，本文使用 mysql_db 数据库模块实现了备份功能。
环境准备
FROM debian:latestRUN sed -i 's/deb.debian.org/mirrors.ustc.edu.cn/g' /etc/apt/sources.list \ && apt-get update -y \ && apt-get upgrade -y \ && apt-get install ansible -y \ && apt-get install sshpass -y \ && apt-get install pip -y \ && apt-get install libmysql++-dev -y \ && apt-get install default-mysql-client -y \ && apt-get autocleanRUN pip3 install mysqlclientRUN mkdir -p /etc/ansible/CMD ["/bin/bash"]
mysql_db模块
|
connect_timeout integer |
The connection timeout when connecting to the MySQL server. Default: 30 |
|
dump_extra_args string added in 0.1.0 of community.mysql |
Provide additional arguments for mysqldump. Used when state=dump only, ignored otherwise. |
|
encoding string |
Encoding mode to use, examples include utf8 or latin1_swedish_ci, at creation of database, dump or importation of sql script. Default: “” |
|
force boolean added in 0.1.0 of community.mysql |
Continue dump or import even if we get an SQL error. Used only when state is dump or import. Choices:
|
|
hex_blob boolean added in 0.1.0 of community.mysql |
Dump binary columns using hexadecimal notation. Choices:
|
|
ignore_tables list / elements=string |
A list of table names that will be ignored in the dump of the form database_name.table_name. Default: [] |
|
login_host string |
Host running the database. In some cases for local connections the login_unix_socket=/path/to/mysqld/socket, that is usually /var/run/mysqld/mysqld.sock, needs to be used instead of login_host=localhost. Default: “localhost” |
|
login_password string |
The password used to authenticate with. |
|
login_port integer |
Port of the MySQL server. Requires login_host be defined as other than localhost if login_port is used. Default: 3306 |
|
login_user string |
The username used to authenticate with. |
|
master_data integer added in 0.1.0 of community.mysql |
Option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. 0 to not include master data. 1 to generate a ‘CHANGE MASTER TO’ statement required on the slave to start the replication process. 2 to generate a commented ‘CHANGE MASTER TO’. Can be used when state=dump. Choices:
Default: 0 |
|
name aliases: db list / elements=string / required |
Name of the database to add or remove. name=all may only be provided if state is dump or import. List of databases is provided with state=dump, state=present and state=absent. If name=all it works like –all-databases option for mysqldump (Added in 2.0). |
|
quick boolean |
Option used for dumping large tables. Choices:
|
|
restrict_config_file boolean added in 0.1.0 of community.mysql |
Read only passed config_file. When state is dump or import, by default the module passes config_file parameter using --defaults-extra-file command-line argument to mysql/mysqldump utilities under the hood that read named option file in addition to usual option files. If this behavior is undesirable, use yes to read only named option file. Choices:
|
|
single_transaction boolean |
Execute the dump in a single transaction. Choices:
|
|
skip_lock_tables boolean added in 0.1.0 of community.mysql |
Skip locking tables for read. Used when state=dump, ignored otherwise. Choices:
|
|
state string |
The database state. Choices:
|
|
target path |
Location, on the remote host, of the dump file to read from or write to. Uncompressed SQL files (.sql) as well as bzip2 (.bz2), gzip (.gz) and xz (Added in 2.0) compressed files are supported. |
playbook编排
--- hosts: 127.0.0.1 tasks: name: "mysql dump test" mysql_db: login_host: 192.168.43.51 login_user: root login_password: ****** state: dump name: test target: /tmp/test_{{ ansible_date_time.date }}.gz
PLAYBOOK: bak.yml *********************************************************1 plays in bak.yml
PLAY [127.0.0.1] *********************************************************
TASK [Gathering Facts] *********************************************************task path: /bak.yml:2ok: [127.0.0.1]META: ran handlers
TASK [mysql dump test] *********************************************************task path: /bak.yml:4redirecting (type: modules) ansible.builtin.mysql_db to community.mysql.mysql_db[WARNING]: The value "********" (type int) was converted to "'********'" (type string). If this does not look like what you expect, quote the entire value toensure it does not change.changed: [127.0.0.1] => {"changed": true, "db": "test", "db_list": ["test"], "executed_commands": ["/usr/bin/mysqldump --user=root --password=******** --host=192.168.43.51 --port=3306 test --quick | /bin/gzip > /tmp/test_2022-05-06.gz"], "msg": ""}META: ran handlersMETA: ran handlers
PLAY RECAP *********************************************************127.0.0.1 : ok=2 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
