使用ansible脚本用pt-archiver归档数据
【背景说明】
需要使用ansible来管理删除数据任务,数据量较大,使用pt-archiver工具
【环境说明】
MySQL主从架构+pt-archiver 3.5.5
【剧本说明】
tree roles/archive_mysql/ roles/archive_mysql/ ├── files │ └── percona-toolkit-3.5.5-1.el7.x86_64.rpm pt工具 ├── tasks │ ├── archive_mysql.yml 执行剧本逻辑 │ └── main.yml 调用剧本 └── vars └── main.yml 环境变量
ansble的hosts配置,建议定义一下角色
[mysql] xx.x.x.xx1 ansible_user=root ansible_ssh_pass=xxxx cluster_role=master xx.x.x.xx2 ansible_user=root ansible_ssh_pass=xxxx cluster_role=slave
tasks目录脚本
指定需要执行的节点信息，注意标识为master节点
cat roles/archive_mysql/tasks/main.yml --- - name: exec mysql archive include: archive_mysql.yml when: cluster_role == "master" tags: [archive_mysql]
取MySQL主从的主IP，然后使用pt工具执行删除数据操作 cat roles/archive_mysql/tasks/archive_mysql.yml --- - name: copy percona-toolkit rpm package copy: src: ../files/{{ item }} dest: '/tmp/{{ item }}' mode: 0774 with_items: ['percona-toolkit-3.5.5-1.el7.x86_64.rpm'] - name: install percona-toolkit rpm yum: name: '/tmp/{{ item }}' state: present with_items: - percona-toolkit-3.5.5-1.el7.x86_64.rpm - name: kubectl MySQL IP shell: kubectl describe service mysql -n production | grep 'Endpoints:' | awk -F ':' '{print $2}' | tr -d '[:space:]' register: mysql_service_ip - name: print target_database debug: var: mysql_service_ip.stdout - name: Get dbname shell: "/usr/local/mysql/bin/mysql -u {{ mysql_user }} -p{{ mysql_password }} -h {{ mysql_service_ip.stdout }} -P {{ mysql_port }} -e 'SHOW DATABASES LIKE \"{{ data_center }}%_eip\";'" register: query_result changed_when: false - name: Get dbname set_fact: target_database: "{{ item }}" with_items: "{{ query_result.stdout_lines }}" when: item is match('^[a-zA-Z0-9_]+$') - name: print target_database debug: var: target_database - name: Delete data using pt-archiver shell: | pt-archiver --source h={{ mysql_service_ip.stdout }},D={{ target_database }},t={{ item.table_name }},u={{ mysql_user }},p={{ mysql_password }},P={{ mysql_port }} --where="{{ item.where_clause }}" --charset=utf8 --progress 100 --limit=1000 --txn-size {{ batch_size }} --statistics --bulk-delete --purge loop: "{{ tables_to_archive }}" register: delete_output - name: Print several lines of text vars: msg: | {% for item in delete_output.results %} cmd: {{ item.cmd }} archive_results: {% for end_item in item.stdout_lines %} {{ end_item }} {% endfor %} {% endfor %} debug: msg: "{{ msg.split('\n') }}"
环境变量定义
添加自己数据库的变量信息，如果是固定数据库IP，自己定义一个数据库IP即可，这里是使用k8s的service方式取到主节点IP
tables_to_archive:这个变量注意定义表名跟条件
cat roles/archive_mysql/vars/main.yml
---
mysql_user: read_write
mysql_password: 'xxxxxx'
mysql_port: 3306
data_center: test1
batch_size: 10000
tables_to_archive:
- table_name: sbtest1
where_clause: "id<40090"
- table_name: sbtest2
where_clause: "id<50900"
执行结果
ansible-playbook archive_mysql.yaml
PLAY [mysql_ms] ***************************************************************************************************************************************************************************************************************************
TASK [Gathering Facts] ********************************************************************************************************************************************************************************************************************
ok: [xx.x.x.xx1]
ok: [xx.x.x.xx2]
TASK [archive_mysql : copy percona-toolkit rpm package] ***************************************************************************************************************************************************************************
skipping: [xx.x.x.xx2] => (item=percona-toolkit-3.5.5-1.el7.x86_64.rpm)
ok: [xx.x.x.xx1] => (item=percona-toolkit-3.5.5-1.el7.x86_64.rpm)
TASK [archive_mysql : install percona-toolkit rpm] *********************************************************************************************************************************************************************************
skipping: [xx.x.x.xx2] => (item=[])
ok: [xx.x.x.xx1] => (item=[u'/tmp/percona-toolkit-3.5.5-1.el7.x86_64.rpm'])
TASK [archive_mysql : kubectl MySQL IP] ********************************************************************************************************************************************************************************************
skipping: [xx.x.x.xx2]
changed: [xx.x.x.xx1]
TASK [archive_mysql : print target_database] ***************************************************************************************************************************************************************************************
ok: [xx.x.x.xx1] => {
"mysql_service_ip.stdout": "xx.x.x.xx1"
}
skipping: [xx.x.x.xx2]
TASK [archive_mysql : Get dbname] **************************************************************************************************************************************************************************************************
skipping: [xx.x.x.xx2]
ok: [xx.x.x.xx1]
TASK [archive_mysql : Get dbname] **************************************************************************************************************************************************************************************************
skipping: [xx.x.x.xx1] => (item=Database (test1%_eip))
ok: [xx.x.x.xx1] => (item=test1_eip)
skipping: [xx.x.x.xx2]
TASK [archive_mysql : print target_database] ***************************************************************************************************************************************************************************************
ok: [xx.x.x.xx1] => {
"target_database": "test1_eip"
}
skipping: [xx.x.x.xx2]
TASK [archive_mysql : Delete data using pt-archiver] *******************************************************************************************************************************************************************************
skipping: [xx.x.x.xx2] => (item={u'table_name': u'sbtest1', u'where_clause': u'id<40090'})
skipping: [xx.x.x.xx2] => (item={u'table_name': u'sbtest2', u'where_clause': u'id<50900'})
changed: [xx.x.x.xx1] => (item={u'table_name': u'sbtest1', u'where_clause': u'id<40090'})
changed: [xx.x.x.xx1] => (item={u'table_name': u'sbtest2', u'where_clause': u'id<50900'})
TASK [archive_mysql : Print several lines of text] *********************************************************************************************************************************************************************************
ok: [xx.x.x.xx1] => {
"msg": [
"cmd: pt-archiver --source h=xx.x.x.xx1,D=test1_eip,t=sbtest1,u=read_write,p=xxxxxx,P=3306 --where=\"id<40090\" --charset=utf8 --progress 100 --limit=1000 --txn-size 10000 --statistics --bulk-delete --purge",
"",
"archive_results:",
" TIME ELAPSED COUNT",
" 2023-12-21T17:26:37 0 0",
" 2023-12-21T17:26:37 0 100",
......省略
" 2023-12-21T17:26:38 1 29500",
" 2023-12-21T17:26:38 1 29507",
" Started at 2023-12-21T17:26:37, ended at 2023-12-21T17:26:38",
" Source: A=utf8,D=test1_eip,P=3306,h=xx.x.x.xx1,p=...,t=sbtest1,u=read_write",
" SELECT 29507",
" INSERT 0",
" DELETE 29507",
" Action Count Time Pct",
" bulk_deleting 30 0.7110 47.36",
" select 31 0.0785 5.23",
" commit 3 0.0576 3.83",
" other 0 0.6542 43.58",
" ",
"cmd: pt-archiver --source h=xx.x.x.xx1,D=test1_eip,t=sbtest2,u=read_write,p=xxxxxx,P=3306 --where=\"id<50900\" --charset=utf8 --progress 100 --limit=1000 --txn-size 10000 --statistics --bulk-delete --purge",
"",
"archive_results:",
" TIME ELAPSED COUNT",
" 2023-12-21T17:26:39 0 0",
" 2023-12-21T17:26:39 0 100",
" 2023-12-21T17:26:39 0 200",
" 2023-12-21T17:26:39 0 300",
......省略
" 2023-12-21T17:26:41 1 37607",
" Started at 2023-12-21T17:26:39, ended at 2023-12-21T17:26:41",
" Source: A=utf8,D=test1_eip,P=3306,h=xx.x.x.xx1,p=...,t=sbtest2,u=read_write",
" SELECT 37607",
" INSERT 0",
" DELETE 37607",
" Action Count Time Pct",
" bulk_deleting 38 0.8458 46.27",
" commit 4 0.1391 7.61",
" select 39 0.0874 4.78",
" other 0 0.7557 41.34",
" ",
""
]
}
skipping: [xx.x.x.xx2]
PLAY RECAP ********************************************************************************************************************************************************************************************************************************
xx.x.x.xx1 : ok=10 changed=2 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
xx.x.x.xx2 : ok=1 changed=0 unreachable=0 failed=0 skipped=9 rescued=0 ignored=0
