1# test code for state dump and import for mysql_db module 2# (c) 2014, Wayne Rosario <wrosario@ansible.com> 3 4# This file is part of Ansible 5# 6# Ansible is free software: you can redistribute it and/or modify 7# it under the terms of the GNU General Public License as published by 8# the Free Software Foundation, either version 3 of the License, or 9# (at your option) any later version. 10# 11# Ansible is distributed in the hope that it will be useful, 12# but WITHOUT ANY WARRANTY; without even the implied warranty of 13# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 14# GNU General Public License for more details. 15# 16# You should have received a copy of the GNU General Public License 17# along with Ansible. If not, see <http://www.gnu.org/licenses/>. 18 19# ============================================================ 20- set_fact: 21 db_file_name: "{{ tmp_dir }}/{{ file }}" 22 wrong_sql_file: "{{ tmp_dir }}/wrong.sql" 23 dump_file1: "{{ tmp_dir }}/{{ file2 }}" 24 dump_file2: "{{ tmp_dir }}/{{ file3 }}" 25 db_user: "test" 26 db_user_unsafe_password: "pass!word" 27 config_file: "/root/.my.cnf" 28 29- name: create custom config file 30 shell: 'echo "[client]" > {{ config_file }}' 31 32- name: create user for test unsafe_login_password parameter 33 mysql_user: 34 login_user: '{{ mysql_user }}' 35 login_password: '{{ mysql_password }}' 36 login_host: 127.0.0.1 37 login_port: '{{ mysql_primary_port }}' 38 name: '{{ db_user }}' 39 password: '{{ db_user_unsafe_password }}' 40 priv: '*.*:ALL' 41 state: present 42 43- name: state dump/import - create database 44 mysql_db: 45 login_user: '{{ mysql_user }}' 46 login_password: '{{ mysql_password }}' 47 login_host: 127.0.0.1 48 login_port: '{{ mysql_primary_port }}' 49 name: '{{ db_name }}' 50 state: present 51 check_implicit_admin: yes 52 53- name: create database 54 mysql_db: 55 login_user: '{{ mysql_user }}' 56 login_password: '{{ mysql_password }}' 57 login_host: 127.0.0.1 58 login_port: '{{ mysql_primary_port }}' 59 name: '{{ db_name2 }}' 60 state: present 61 check_implicit_admin: no 62 63- name: state dump/import - create table department 64 command: "{{ mysql_command }} {{ db_name }} \"-e create table department(id int, name varchar(100))\"" 65 66- name: state dump/import - create table employee 67 command: "{{ mysql_command }} {{ db_name }} \"-e create table employee(id int, name varchar(100))\"" 68 69- name: state dump/import - insert data into table employee 70 command: "{{ mysql_command }} {{ db_name }} \"-e insert into employee value(47,'Joe Smith')\"" 71 72- name: state dump/import - insert data into table department 73 command: "{{ mysql_command }} {{ db_name }} \"-e insert into department value(2,'Engineering')\"" 74 75- name: state dump/import - file name should not exist 76 file: 77 name: '{{ db_file_name }}' 78 state: absent 79 80- name: database dump file1 should not exist 81 file: 82 name: '{{ dump_file1 }}' 83 state: absent 84 85- name: database dump file2 should not exist 86 file: 87 name: '{{ dump_file2 }}' 88 state: absent 89 90- name: state dump without department table. 91 mysql_db: 92 login_user: '{{ db_user }}' 93 login_password: '{{ db_user_unsafe_password }}' 94 login_host: 127.0.0.1 95 login_port: '{{ mysql_primary_port }}' 96 unsafe_login_password: yes 97 name: '{{ db_name }}' 98 state: dump 99 target: '{{ db_file_name }}' 100 ignore_tables: 101 - "{{ db_name }}.department" 102 force: yes 103 master_data: 1 104 skip_lock_tables: yes 105 dump_extra_args: --skip-triggers 106 config_file: '{{ config_file }}' 107 restrict_config_file: yes 108 check_implicit_admin: no 109 register: result 110 111- name: assert successful completion of dump operation 112 assert: 113 that: 114 - result is changed 115 - result.executed_commands[0] is search("mysqldump --defaults-file={{ config_file }} --user={{ db_user }} --password=\*\*\*\*\*\*\*\* --force --host=127.0.0.1 --port={{ mysql_primary_port }} {{ db_name }} --skip-lock-tables --quick --ignore-table={{ db_name }}.department --master-data=1 --skip-triggers") 116 117- name: state dump/import - file name should exist 118 file: 119 name: '{{ db_file_name }}' 120 state: file 121 122- name: state dump with multiple databases in comma separated form. 123 mysql_db: 124 login_user: '{{ mysql_user }}' 125 login_password: '{{ mysql_password }}' 126 login_host: 127.0.0.1 127 login_port: '{{ mysql_primary_port }}' 128 name: "{{ db_name }},{{ db_name2 }}" 129 state: dump 130 target: '{{ dump_file1 }}' 131 check_implicit_admin: yes 132 register: dump_result1 133 134- name: assert successful completion of dump operation (with multiple databases in comma separated form) 135 assert: 136 that: 137 - dump_result1 is changed 138 - dump_result1.executed_commands[0] is search(" --user=root --password=\*\*\*\*\*\*\*\*") 139 140- name: state dump - dump file1 should exist 141 file: 142 name: '{{ dump_file1 }}' 143 state: file 144 145- name: state dump with multiple databases in list form via check_mode 146 mysql_db: 147 login_user: '{{ mysql_user }}' 148 login_password: '{{ mysql_password }}' 149 login_host: 127.0.0.1 150 login_port: '{{ mysql_primary_port }}' 151 name: 152 - '{{ db_name }}' 153 - '{{ db_name2 }}' 154 state: dump 155 target: '{{ dump_file2 }}' 156 register: dump_result 157 check_mode: yes 158 159- name: assert successful completion of dump operation (with multiple databases in list form) via check mode 160 assert: 161 that: 162 - "dump_result.changed == true" 163 164- name: database dump file2 should not exist 165 stat: 166 path: '{{ dump_file2 }}' 167 register: stat_result 168 169- name: assert that check_mode does not create dump file for databases 170 assert: 171 that: 172 - stat_result.stat.exists is defined and not stat_result.stat.exists 173 174- name: state dump with multiple databases in list form. 175 mysql_db: 176 login_user: '{{ mysql_user }}' 177 login_password: '{{ mysql_password }}' 178 login_host: 127.0.0.1 179 login_port: '{{ mysql_primary_port }}' 180 name: 181 - '{{ db_name }}' 182 - '{{ db_name2 }}' 183 state: dump 184 target: '{{ dump_file2 }}' 185 register: dump_result2 186 187- name: assert successful completion of dump operation (with multiple databases in list form) 188 assert: 189 that: 190 - "dump_result2.changed == true" 191 192- name: state dump - dump file2 should exist 193 file: 194 name: '{{ dump_file2 }}' 195 state: file 196 197- name: state dump/import - remove database 198 mysql_db: 199 login_user: '{{ mysql_user }}' 200 login_password: '{{ mysql_password }}' 201 login_host: 127.0.0.1 202 login_port: '{{ mysql_primary_port }}' 203 name: '{{ db_name }}' 204 state: absent 205 206- name: remove database 207 mysql_db: 208 login_user: '{{ mysql_user }}' 209 login_password: '{{ mysql_password }}' 210 login_host: 127.0.0.1 211 login_port: '{{ mysql_primary_port }}' 212 name: '{{ db_name2 }}' 213 state: absent 214 215- name: test state=import to restore the database of type {{ format_type }} (expect changed=true) 216 mysql_db: 217 login_user: '{{ db_user }}' 218 login_password: '{{ db_user_unsafe_password }}' 219 login_host: 127.0.0.1 220 login_port: '{{ mysql_primary_port }}' 221 unsafe_login_password: yes 222 name: '{{ db_name }}' 223 state: import 224 target: '{{ db_file_name }}' 225 use_shell: yes 226 register: result 227 228- name: show the tables 229 command: "{{ mysql_command }} {{ db_name }} \"-e show tables\"" 230 register: result 231 232- name: assert that the department table is absent. 233 assert: 234 that: 235 - "'department' not in result.stdout" 236 237- name: test state=import to restore a database from multiple database dumped file1 238 mysql_db: 239 login_user: '{{ mysql_user }}' 240 login_password: '{{ mysql_password }}' 241 login_host: 127.0.0.1 242 login_port: '{{ mysql_primary_port }}' 243 name: '{{ db_name2 }}' 244 state: import 245 target: '{{ dump_file1 }}' 246 use_shell: no 247 register: import_result 248 249- name: assert output message restored a database from dump file1 250 assert: 251 that: 252 - "import_result.changed == true" 253 254- name: remove database 255 mysql_db: 256 login_user: '{{ mysql_user }}' 257 login_password: '{{ mysql_password }}' 258 login_host: 127.0.0.1 259 login_port: '{{ mysql_primary_port }}' 260 name: '{{ db_name2 }}' 261 state: absent 262 263- name: run command to list databases 264 command: "{{ mysql_command }} \"-e show databases like 'data%'\"" 265 register: mysql_result 266 267- name: assert that db_name2 database does not exist 268 assert: 269 that: 270 - "'{{ db_name2 }}' not in mysql_result.stdout" 271 272- name: test state=import to restore a database from dumped file2 (check mode) 273 mysql_db: 274 login_user: '{{ mysql_user }}' 275 login_password: '{{ mysql_password }}' 276 login_host: 127.0.0.1 277 login_port: '{{ mysql_primary_port }}' 278 name: '{{ db_name2 }}' 279 state: import 280 target: '{{ dump_file2 }}' 281 register: check_import_result 282 check_mode: yes 283 284- name: assert output message restored a database from dump file2 (check mode) 285 assert: 286 that: 287 - "check_import_result.changed == true" 288 289- name: run command to list databases 290 command: "{{ mysql_command }} \"-e show databases like 'data%'\"" 291 register: mysql_result 292 293- name: assert that db_name2 database does not exist (check mode) 294 assert: 295 that: 296 - "'{{ db_name2 }}' not in mysql_result.stdout" 297 298- name: test state=import to restore a database from multiple database dumped file2 299 mysql_db: 300 login_user: '{{ mysql_user }}' 301 login_password: '{{ mysql_password }}' 302 login_host: 127.0.0.1 303 login_port: '{{ mysql_primary_port }}' 304 name: '{{ db_name2 }}' 305 state: import 306 target: '{{ dump_file2 }}' 307 register: import_result2 308 309- name: assert output message restored a database from dump file2 310 assert: 311 that: 312 - import_result2.changed == true 313 - import_result2.db_list == ['{{ db_name2 }}'] 314 315- name: run command to list databases 316 command: "{{ mysql_command }} \"-e show databases like 'data%'\"" 317 register: mysql_result 318 319- name: assert that db_name2 database does exist after import 320 assert: 321 that: 322 - "'{{ db_name2 }}' in mysql_result.stdout" 323 324- name: test state=dump to backup the database of type {{ format_type }} (expect changed=true) 325 mysql_db: 326 login_user: '{{ mysql_user }}' 327 login_password: '{{ mysql_password }}' 328 login_host: 127.0.0.1 329 login_port: '{{ mysql_primary_port }}' 330 name: '{{ db_name }}' 331 state: dump 332 target: '{{ db_file_name }}' 333 register: result 334 335- name: assert output message backup the database 336 assert: 337 that: 338 - "result.changed == true" 339 - "result.db =='{{ db_name }}'" 340 341# - name: assert database was backed up successfully 342# command: "file {{ db_file_name }}" 343# register: result 344# 345# - name: assert file format type 346# assert: 347# that: 348# - "'{{ format_msg_type }}' in result.stdout" 349 350- name: update database table employee 351 command: "{{ mysql_command }} {{ db_name }} \"-e update employee set name='John Doe' where id=47\"" 352 353- name: test state=import to restore the database of type {{ format_type }} (expect changed=true) 354 mysql_db: 355 login_user: '{{ mysql_user }}' 356 login_password: '{{ mysql_password }}' 357 login_host: 127.0.0.1 358 login_port: '{{ mysql_primary_port }}' 359 name: '{{ db_name }}' 360 state: import 361 target: '{{ db_file_name }}' 362 register: result 363 364- name: assert output message restore the database 365 assert: 366 that: 367 - "result.changed == true" 368 369- name: select data from table employee 370 command: "{{ mysql_command }} {{ db_name }} \"-e select * from employee\"" 371 register: result 372 373- name: assert data in database is from the restore database 374 assert: 375 that: 376 - "'47' in result.stdout" 377 - "'Joe Smith' in result.stdout" 378 379########################## 380# Test ``force`` parameter 381########################## 382 383- name: create wrong sql file 384 shell: echo 'CREATE TABLE hello (id int); CREATE ELBAT ehlo (int id);' >> '{{ wrong_sql_file }}' 385 386- name: try to import without force parameter, must fail 387 mysql_db: 388 login_user: '{{ mysql_user }}' 389 login_password: '{{ mysql_password }}' 390 login_host: 127.0.0.1 391 login_port: '{{ mysql_primary_port }}' 392 name: '{{ db_name }}' 393 state: import 394 target: '{{ wrong_sql_file }}' 395 force: no 396 register: result 397 ignore_errors: yes 398 399- assert: 400 that: 401 - result.failed == true 402 403- name: try to import with force parameter 404 mysql_db: 405 login_user: '{{ mysql_user }}' 406 login_password: '{{ mysql_password }}' 407 login_host: 127.0.0.1 408 login_port: '{{ mysql_primary_port }}' 409 name: '{{ db_name }}' 410 state: import 411 target: '{{ wrong_sql_file }}' 412 force: yes 413 register: result 414 415- assert: 416 that: 417 - result is changed 418 419########## 420# Clean up 421########## 422 423- name: remove database name 424 mysql_db: 425 login_user: '{{ mysql_user }}' 426 login_password: '{{ mysql_password }}' 427 login_host: 127.0.0.1 428 login_port: '{{ mysql_primary_port }}' 429 name: '{{ db_name }}' 430 state: absent 431 432- name: remove database 433 mysql_db: 434 login_user: '{{ mysql_user }}' 435 login_password: '{{ mysql_password }}' 436 login_host: 127.0.0.1 437 login_port: '{{ mysql_primary_port }}' 438 name: '{{ db_name2 }}' 439 state: absent 440 441- name: remove file name 442 file: 443 name: '{{ db_file_name }}' 444 state: absent 445 446- name: remove file name 447 file: 448 name: '{{ wrong_sql_file }}' 449 state: absent 450 451- name: remove dump file1 452 file: 453 name: '{{ dump_file1 }}' 454 state: absent 455 456- name: remove dump file2 457 file: 458 name: '{{ dump_file2 }}' 459 state: absent 460