Conexão remota com a VM do banco não funciona

Olá Flavio, tudo bem? Você poderia disponibilizar todos os arquivos aqui pra gente verificar corretamente, realmente é bem estranho não existir nenhuma resposta na porta 3306.

Fico aguardando retorno!

Arquivo hosts:

[wordpress]
172.17.177.40 ansible_user=vagrant ansible_ssh_private_key_file=.vagrant/machines/wordpress/virtualbox/private_key

[database]
172.17.177.42 ansible_user=vagrant ansible_ssh_private_key_file=.vagrant/machines/mysql/virtualbox/private_key

Arquivo provisioning.yml:

---
- hosts: wordpress
  handlers:
          - name: restart apache
            service:
                    name: apache2
                    state: restarted
            become: yes
  tasks:
          - name: 'Instala pacotes de dependência do sistema operacional'
            apt:
                    name: "{{ item }}"
                    state: latest
            become: yes
            with_items:
                    - php
                    - apache2
                    - libapache2-mod-php
                    - php-gd
                    - libssh2-1
                    - php-ssh2
                    - php-mysql
          - name: 'Baixa o arquivo de instalacao do Wordpress'
            get_url:
                    url: https://wordpress.org/latest.tar.gz'
                    dest: '/tmp/wordpress.tar.gz'
          - name: 'Descompacta o wordpress'
            unarchive:
                    src: '/tmp/wordpress.tar.gz'
                    dest: /var/www/
                    remote_src: yes
            become: yes
          - copy:
                    src: '/var/www/wordpress/wp-config-sample.php'
                    dest: '/var/www/wordpress/wp-config.php'
                    remote_src: yes
            become: yes
          - name: 'Configura o wp-config com as entradas do banco de dados'     
            replace:
                    path: '/var/www/wordpress/wp-config.php'
                    regexp: "{{ item.regex }}"
                    replace: "{{ item.value }}"
                    backup: yes
            with_items:
                    - { regex: 'database_name_here', value: 'wordpress_db'}
                    - { regex: 'username_here', value: 'wordpress_user'}
                    - { regex: 'password_here', value: '12345'}
                    - { regex: 'localhost', value: '171.17.177.42'}
            become: yes
          - name: 'Configura Apache para servir o Wordpress'
            copy:
                    src: 'files/000-default.conf'
                    dest: '/etc/apache2/sites-available/000-default.conf'
            become: yes
            notify:
              - restart apache
- hosts: database
  handlers:
          - name: restart mysql
            service:
                    name: mysql
                    state: restarted
            become: yes
  tasks:
          - name: 'Instala pacotes de dependência do sistema operacional'
            apt:
                    name: "{{ item }}"
                    state: latest
            become: yes
            with_items:
                    - mysql-server
                    - mysql-client 
                    - python-mysqldb
          - name: 'Escreve as credencias de login'
            copy:
                    dest: /home/vagrant/.my.cnf
                    owner: root
                    group: root
                    mode: 0600
                    content: |
                        [client]
                        user=root
                        password=12345
            become: yes
          - name: 'Configura MySQL para aceitar conexões remotas'
            copy:
                    src: 'files/mysqld.cnf'
                    dest: '/etc/mysql/mysql.conf.d/mysqld.cnf'
            become: yes
            notify:
                    - restart mysql
          - name: 'Cria o banco do MySQL'
            mysql_db:
                    name: wordpress_db
                    login_user: root
                    login_password: 12345
                    state: present
          - name: 'Cria o usuário do MySQL'
            mysql_user:
                    login_user: root
                    login_password: 12345
                    name: wordpress_user
                    password: 12345
                    priv: 'wordpress_db.*:ALL'
                    state: present
                    host: "{{ item }}"
            with_items:
                    - 'localhost'
                    - '127.0.0.1'
                    - '172.17.177.40'

Arquivo Vagrantfile:

# -*- mode: ruby -*-
# vi: set ft=ruby :

# All Vagrant configuration is done below. The "2" in Vagrant.configure
# configures the configuration version (we support older styles for
# backwards compatibility). Please don't change it unless you know what
# you're doing.
Vagrant.configure("2") do |config|
  # The most common configuration options are documented and commented below.
  # For a complete reference, please see the online documentation at
  # https://docs.vagrantup.com.

  # Every Vagrant development environment requires a box. You can search for
  # boxes at https://vagrantcloud.com/search.
  config.vm.box = "ubuntu/bionic64"

  # Disable automatic box update checking. If you disable this, then
  # boxes will only be checked for updates when the user runs
  # `vagrant box outdated`. This is not recommended.
  # config.vm.box_check_update = false

  # Create a forwarded port mapping which allows access to a specific port
  # within the machine from a port on the host machine. In the example below,
  # accessing "localhost:8080" will access port 80 on the guest machine.
  # NOTE: This will enable public access to the opened port
  # config.vm.network "forwarded_port", guest: 80, host: 8080

  # Create a forwarded port mapping which allows access to a specific port
  # within the machine from a port on the host machine and only allow access
  # via 127.0.0.1 to disable public access
  # config.vm.network "forwarded_port", guest: 80, host: 8080, host_ip: "127.0.0.1"

  # Create a private network, which allows host-only access to the machine
  # using a specific IP.
  # config.vm.network "private_network", ip: "192.168.33.10"

  # Create a public network, which generally matched to bridged network.
  # Bridged networks make the machine appear as another physical device on
  # your network.
  # config.vm.network "public_network"

  # Share an additional folder to the guest VM. The first argument is
  # the path on the host to the actual folder. The second argument is
  # the path on the guest to mount the folder. And the optional third
  # argument is a set of non-required options.
  # config.vm.synced_folder "../data", "/vagrant_data"

  # Provider-specific configuration so you can fine-tune various
  # backing providers for Vagrant. These expose provider-specific options.
  # Example for VirtualBox:
  #
  # config.vm.provider "virtualbox" do |vb|
  #   # Display the VirtualBox GUI when booting the machine
  #   vb.gui = true
  #
  #   # Customize the amount of memory on the VM:
  #   vb.memory = "1024"
  # end
  #
  # View the documentation for the provider you are using for more
  # information on available options.

  # Enable provisioning with a shell script. Additional provisioners such as
  # Puppet, Chef, Ansible, Salt, and Docker are also available. Please see the
  # documentation for more information about their specific syntax and use.
  # config.vm.provision "shell", inline: <<-SHELL
  #   apt-get update
  #   apt-get install -y apache2
  # SHELL

  config.vm.provider "virtualbox" do |v|
    v.memory = 1024
  end

  config.vm.define "wordpress" do |m|
    m.vm.network "private_network", ip: "172.17.177.40"
  end

  config.vm.define "mysql" do |m|
    m.vm.network "private_network", ip: "172.17.177.42"
  end
end

Arquivo mysqld.cnf:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
lc-messages-dir    = /usr/share/mysql
skip-external-locking
# *
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0
#
# * Fine Tuning
#
key_buffer_size        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_open_cache       = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit    = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log        = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id        = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db        = include_database_name
#binlog_ignore_db    = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem 

Flavio, no caso você está usando o Ubuntu 18.04 correto? E qual a versão do MySQL? Você tentou fixar a versão mysql-server-5.6? Você tentou acessar a máquina diretamente via SSH e verificar se realmente o MySQL está em execução, no caso se o serviço do MySQL está em execução? Você verificou se o usuário wordpress_user foi criado com sucesso? Para isso você poderia usar o cliente do MySQL que sempre é instalado da seguinte forma:

mysql -u wordpress_user -p 

E em seguida informar a senha.

Faz essas verificações e fala pra gente os resultados!

A versão do MySQL é 5.7

mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

O MySQL está rodando:

vagrant@ubuntu-bionic:~$ sudo service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: active (running) since Fri 2020-06-12 10:49:28 UTC; 2min 15s ago
  Process: 1475 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/my
  Process: 1060 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exit
 Main PID: 1477 (mysqld)
    Tasks: 27 (limit: 1152)
   CGroup: /system.slice/mysql.service
           └─1477 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Jun 12 10:49:09 ubuntu-bionic systemd[1]: Starting MySQL Community Server...
Jun 12 10:49:28 ubuntu-bionic systemd[1]: Started MySQL Community Server.

E o usuário do Wordpress existe:

vagrant@ubuntu-bionic:~$ mysql -u wordpress_user -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Realmente está tudo certinho, mas você poderia tentar deixar as duas máquina públicas na sua rede, para isso troque as linhas de configuração da rede e IP no vagrant dessa forma:

config.vm.define "wordpress" do |m|
    m.vm.network "public_network", ip: "172.17.177.40"
  end

  config.vm.define "mysql" do |m|
    m.vm.network "public_network", ip: "172.17.177.42"
  end

E depois tentar acessar novamente a máquina "mysql" pela máquina "wordpress", tenta também fazer o acesso da sua máquina física, e de outro computador ou dispositivo da sua rede, porque isso me parece ser um problema de rede mesmo.

Fico aguardando retorno!

Conforme orientado, troquei os dois m.vm.network para public_network. Ao rodar o ansible-playbook, das 3 interfaces de rede disponíveis (wlp2s0, enp1s0f1 e docker0) escolhi a primeira. O resultado foi este:

PLAY [wordpress] *****************************************************************************************************************************

TASK [Gathering Facts] ***********************************************************************************************************************
fatal: [172.17.177.40]: UNREACHABLE! => {"changed": false, "msg": "Failed to connect to the host via ssh: ssh: connect to host 172.17.177.40 port 22: No route to host\r\n", "unreachable": true}
    to retry, use: --limit @/home/fgsl/wordpress_com_ansible/provisioning.retry

PLAY RECAP ***********************************************************************************************************************************
172.17.177.40              : ok=0    changed=0    unreachable=1    failed=0   

Muito estranho isso, você verificou como está os IPs na sua rede local? Aparentemente não está conseguindo fazer o acesso via SSH, mais isso é bastante estranho, aparentemente não existe um caminho até o host, pode verificar sua rede?

Testei com a interface de rede enp1s0f1:

ansible-playbook provisioning.yml -i hosts

PLAY [wordpress] ***************************************************************

TASK [Gathering Facts] *********************************************************
fatal: [172.17.177.40]: UNREACHABLE! => {"changed": false, "msg": "Failed to connect to the host via ssh: ssh: connect to host 172.17.177.40 port 22: No route to host\r\n", "unreachable": true}
    to retry, use: --limit @/home/fgsl/wordpress_com_ansible/provisioning.retry

PLAY RECAP *********************************************************************
172.17.177.40              : ok=0    changed=0    unreachable=1    failed=0

Testei com a interface de rede docker0:

 ansible-playbook provisioning.yml -i hosts

PLAY [wordpress] ***************************************************************

TASK [Gathering Facts] *********************************************************
fatal: [172.17.177.40]: UNREACHABLE! => {"changed": false, "msg": "Failed to connect to the host via ssh: ssh: connect to host 172.17.177.40 port 22: No route to host\r\n", "unreachable": true}
    to retry, use: --limit @/home/fgsl/wordpress_com_ansible/provisioning.retry

PLAY RECAP *********************************************************************
172.17.177.40              : ok=0    changed=0    unreachable=1    failed=0 

É estranho que a mensagem seja que ele não consiga conectar via SSH uma vez que eu consigo executar o vagrant ssh sem problemas.

O que acontece, é que o Vagrant usa um cliente SSH próprio, e por isso conseguimos sempre fazer a conexão com a máquina virtual usando o Vagrant SSH desde que tenhamos a chave que é criada no momento em que a máquina é criada. Porém quando vamos acessar de fora, ai realmente temos que usar o tradicional SSH e então tem essa pequena diferença. Por isso que eu falei pra verificar a conexão na rede, usando outros dispositivos, pode ser um outro computador, fazendo apenas um Ping para o IP da máquina virtual.

Você poderia tentar usando esse código:

---
- hosts: database
  handlers:
    - name: restart mysql
      service:
        name: mysql
        state: restarted
      become: yes

  tasks:
    - name: 'Instala pacotes de dependencia do sistema operacional'
      apt:
        name:
          - mysql-server-5.6
          - python-mysqldb
        state: latest
      become: yes

    - name: 'Cria o banco MySQL'
      mysql_db:
        name: wordpress_db
        login_user: root
        state: present

    - name: 'Cria o usuário do MySQL'
      mysql_user:
        login_user: root
        name: wordpress_user
        password: '12345'
        priv: 'wordpress_db.*:ALL'
        state: present
        host: "{{ item }}"
      loop:
        - '127.0.0.1'
        - '172.17.177.40'

    - name: 'Configura MySQL para aceitar conexoes remotas'
      copy:
        src: 'files/my.cnf'
        dest: '/etc/mysql/my.cnf'
      become: yes
      notify:
        - restart mysql

- hosts: wordpress
  handlers:
    - name: restart apache
      service:
        name: apache2
        state: restarted
      become: yes

  tasks:
    - name: 'Instala pacotes de dependencia do sistema operacional'
      apt:
        name:
          - php5
          - apache2
          - libapache2-mod-php5
          - php5-gd
          - libssh2-php
          - php5-mcrypt
          - php5-mysql
        state: latest
      become: yes 

    - name: 'Baixa o arquivo de instalacao do Wordpress'
      get_url:
        url: 'https://wordpress.org/wordpress-5.0.tar.gz'
        dest: '/tmp/wordpress.tar.gz'

    - name: 'Descompacta o wordpress'
      unarchive:
        src: '/tmp/wordpress.tar.gz'
        dest: /var/www/
        remote_src: yes
      become: yes

    - copy:
        src: '/var/www/wordpress/wp-config-sample.php'
        dest: '/var/www/wordpress/wp-config.php'
        remote_src: yes
      become: yes

    - name: 'Configura o wp-config com as entradas do banco de dados' 
      replace:
        path: '/var/www/wordpress/wp-config.php'
        regexp: "{{ item.regex }}"
        replace: "{{ item.value }}"
      loop:
        - { regex: 'database_name_here', value: 'wordpress_db' }
        - { regex: 'username_here', value: 'wordpress_user' }
        - { regex: 'password_here', value: '12345' }
        - { regex: 'localhost', value: '172.17.177.42' }
      become: yes

    - name: 'Configura Apache para servir o Wordpress'
      copy:
        src: 'files/000-default.conf'
        dest: '/etc/apache2/sites-available/000-default.conf'
      become: yes
      notify:
        - restart apache

Testei esse provisioning mas o erro persiste.

Fiz um ping para a VM:

172.17.177.40
PING 172.17.177.40 (172.17.177.40) 56(84) bytes of data.
From 172.17.0.1 icmp_seq=1 Destination Host Unreachable
From 172.17.0.1 icmp_seq=2 Destination Host Unreachable
From 172.17.0.1 icmp_seq=3 Destination Host Unreachable
From 172.17.0.1 icmp_seq=4 Destination Host Unreachable
From 172.17.0.1 icmp_seq=5 Destination Host Unreachable
From 172.17.0.1 icmp_seq=6 Destination Host Unreachable
From 172.17.0.1 icmp_seq=7 Destination Host Unreachable
From 172.17.0.1 icmp_seq=8 Destination Host Unreachable
...

Então Flavio, como o erro indica, o "Host de destino inacessível" no caso o ping não chega até a máquina, porém esse ping foi realizado de outra máquina na rede local? Ou foi da máquina física que está executando a máquina virtual?

Esse ping foi realizado da máquina física que está executando a máquina virtual. Não há outra máquina na rede local.

Você pode tentar criar uma máquina virtual sem usar o Vagrant, usando diretamente uma imagem ISO e depois fazer o ping, pra gente verificar se é um problema na máquina em si ou na configuração de alguma coisa no caminho.