wercker.com 连接到 MySQL 数据库

Question

背景

我正在尝试使用http://wercker.com为Yii2应用程序构建一些自动化测试。我创建了自己的Docker库https://hub.docker.com/r/consynki/yii2/，提供一个简单的LAMP堆栈。

我将该Docker库作为我的wercker.yml文件中的虚拟机，并使用几个简单的步骤设置我的应用程序、初始化数据库，然后运行我的phpunit测试。

box: consynki/yii2

build:
  steps:

- script:
    name: Update enviroment dependencies
    code: |-
      curl -sS https://getcomposer.org/installer | php -- --install-dir=/usr/bin/ --filename=composer

- script:
    name: Install Composer dependencies
    code: |-
      rm -rf ./vendor
      /usr/bin/composer install --no-interaction --prefer-source

- script:
    name: Apache site install
    code: |-
      sudo chmod -R 755 /var/www
      sudo cp -r ./ /var/www/example.lan/
      sudo chown -R $USER:$USER /var/www/example.lan/
      sudo cp ./config/example.lan.conf /etc/apache2/sites-available/example.lan.conf
      sudo a2ensite example.lan.conf
      sudo cp -fr ./config/hosts /etc/hosts
      sudo service apache2 restart

- script:
    name: Create database
    code: |-
      mysql -uroot -e "SHOW DATABASES;"
      mysql -uroot -e "CREATE DATABASE IF NOT EXISTS test"

- script:
    name: PHPUnit integration tests
    code: |-
      ./vendor/bin/phpunit --configuration phpunit.xml

问题

问题是，当我使用wercker CLI wercker build运行构建时，它无法连接到mysql。在尝试运行“创建数据库”步骤时，我一直收到以下连接错误ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)。

 - script:
    name: Create database
    code: |-
      mysql -uroot -e "SHOW DATABASES;"
      mysql -uroot -e "CREATE DATABASE IF NOT EXISTS test"

我知道Docker容器有一个MySQL连接，因为我可以通过SSH进入它并运行mysql -uroot -e "SHOW DATABASES;"

编辑 我目前正在进行的Wercker LAMP模板可在https://github.com/levi-putna/php-cli-template找到。

请求

我知道wercker.com相对较新，没有很多文档。但我希望有人能够为我提供一个如何在wercker测试运行中使用MySQL的示例。

编辑 - 使用Wercker服务

我还尝试使用Wercker MariaDB服务进行连接，虽然不是真正的MySQL，所以不能完全测试我的生产环境，但应该足以开始测试。

我更新了wercker.yml以添加mariadb服务。简化了步骤，只需让数据库连接工作即可。

box: consynki/yii2

services:
    - id: mariadb

      # your credentials for Docker Hub
      username: $USERNAME
      password: $PASSWORD
      tag: latest

      # set the required environment variable
      env:
        MYSQL_ROOT_PASSWORD: mypassword
        MYSQL_DATABASE: test_database
        MYSQL_USER: admin
        MYSQL_PASSWORD: test123

build:

  steps:

    - script:
        name: Create database
        code: |-
          mysql -h $MARIADB_PORT_3306_TCP_ADDR -P $MARIADB_PORT_3306_TCP_PORT -u $MYSQL_USER -p test123 -v
          mysql -h $MARIADB_PORT_3306_TCP_ADDR -P $MARIADB_PORT_3306_TCP_PORT -u $MYSQL_USER -p test123 -e "SHOW DATABASES;"

我似乎仍然遇到了与我的原始方法相同的错误。ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

在wercker步骤退出后，我也看到一些额外的日志消息。

WARNING Box container has already stopped.
Initializing database
2016-03-01 22:51:00 140429748197312 [Note] /usr/sbin/mysqld (mysqld 10.1.12-MariaDB-1~jessie) starting as process 51 ...
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Memory barrier is not used
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Using Linux native AIO
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Using SSE crc32 instructions
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Completed initialization of buffer pool
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Database physically writes the file full: wait...
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-03-01 22:51:01 140429748197312 [Warning] InnoDB: New log files created, LSN=45883
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Doublewrite buffer not found: creating new
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Doublewrite buffer created
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-01 22:51:01 140429748197312 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Foreign key constraint system tables created
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Creating tablespace and datafile system tables.
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Tablespace and datafile system tables created.
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Waiting for purge to start
2016-03-01 22:51:01 140429748197312 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.28-76.1 started; log sequence number 0
2016-03-01 22:51:01 140428963710720 [Note] InnoDB: Dumping buffer pool(s) not yet started
2016-03-01 22:51:03 140216194230208 [Note] /usr/sbin/mysqld (mysqld 10.1.12-MariaDB-1~jessie) starting as process 80 ...
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Memory barrier is not used
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Using Linux native AIO
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Using SSE crc32 instructions
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Completed initialization of buffer pool
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Highest supported file format is Barracuda.
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Waiting for purge to start
2016-03-01 22:51:03 140216194230208 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.28-76.1 started; log sequence number 1616799
2016-03-01 22:51:04 140215410722560 [Note] InnoDB: Dumping buffer pool(s) not yet started
2016-03-01 22:51:06 140659046377408 [Note] /usr/sbin/mysqld (mysqld 10.1.12-MariaDB-1~jessie) starting as process 109 ...
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Memory barrier is not used
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Using Linux native AIO
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Using SSE crc32 instructions
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Completed initialization of buffer pool
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Highest supported file format is Barracuda.
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Waiting for purge to start
2016-03-01 22:51:06 140659046377408 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.28-76.1 started; log sequence number 1616809
2016-03-01 22:51:06 140658262116096 [Note] InnoDB: Dumping buffer pool(s) not yet started
ERROR: 1049  Unknown database 'test'
2016-03-01 22:51:06 140659046377408 [ERROR] Aborting

- Levi Putna

---

我不确定Wercker在运行Wercker步骤之前是否实际执行Docker文件中的最终CMD。如果我手动运行最终的docker命令，在我的情况下是/run.sh（启动数据库的脚本），我可以在Wercker内连接到mysql。不确定这是否是真正的修复方法，还是只是一种解决方法。 - Levi Putna

Answer 1

您可以使用Wercker准备的现成MySQL服务器，而不是自己设置。这将确保它在调用之间保持存在。

查看此文档。

- Tal

---

这似乎是更好的方法。我坚持使用了示例中的MariaBD，虽然不完全是MySQL，但应该可以用于测试。当我尝试从wercker.ylm内部连接时，似乎仍然出现了相同的错误。我也不确定我应该如何在php内部连接到服务。我是否仍应该使用本地主机端口3306？ - Levi Putna

当使用该服务时，以下环境变量将对您可用：$MARIADB_PORT_3306_TCP_ADDR，$MARIADB_PORT_3306_TCP_PORT。 - Tal

Answer 2

说明

好的，我按照 @Tal 的建议成功地建立了数据库连接。Wercker docks只提供了一个使用MariaDB的示例，幸运的是MySQL有一个docker容器，它几乎与MariaDB版本完全相同。

我将新容器添加为我的wercker.yml文件中的一个服务。

services:
  - id: mysql

    # your credentials for Docker Hub
    username: $USERNAME
    password: $PASSWORD
    tag: latest

    # set the required environment variable
    env:
      MYSQL_ROOT_PASSWORD: mypassword
      MYSQL_DATABASE: test_database
      MYSQL_USER: admin
      MYSQL_PASSWORD: test123

然后我可以使用MySQL服务公开的环境变量，在我的Wercker步骤中连接到数据库。

  - script:
    name: Create database
    code: mysql -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p$MYSQL_ENV_MYSQL_ROOT_PASSWORD -e "CREATE DATABASE IF NOT EXISTS test;"

我可以使用PHP中的环境变量来连接数据库。

class SampleTest extends PHPUnit_Framework_TestCase
{

    public function testSetupWorking()
    {
        $this->assertEquals(true, true);
        $this->assertFalse(false);
    }

    public function testDatabase()
    {

        $hostname = $_SERVER["MYSQL_PORT_3306_TCP_ADDR"];
        $username = 'root';
        $password = $_SERVER["MYSQL_ENV_MYSQL_ROOT_PASSWORD"];
        $database = $_SERVER["MYSQL_ENV_MYSQL_DATABASE"];

        try {
            $dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
            $this->assertFalse(false);

        } catch (PDOException $e) {
            echo $e->getMessage();
            $this->assertFalse(true, $e->getMessage());
        }
    }

}   

我的完整的wercker.yml看起来像这样，您可以在https://github.com/levi-putna/php-cli-template上查看我的完整模板。这只是一个示例，您需要清理步骤和PHP连接数据库的方式，但这是一个很好的起点。

答案 - 完整的wercker.yml

services:
        - id: mysql

          # your credentials for Docker Hub
          username: $USERNAME
          password: $PASSWORD
          tag: latest

          # set the required environment variable
          env:
            MYSQL_ROOT_PASSWORD: mypassword
            MYSQL_DATABASE: test_database
            MYSQL_USER: admin
            MYSQL_PASSWORD: test123

    box: consynki/yii2

    build:

      steps:

        - script:
          name: Show Enviroment
          code: printenv

        - script:
          name: Update enviroment dependencies
          code: |
              sudo DEBIAN_FRONTEND=noninteractive apt-get --no-install-recommends -y install curl php5-cli php5-curl git
              curl -sS https://getcomposer.org/installer | php -- --install-dir=/usr/bin/ --filename=composer
              cd $WERCKER_SOURCE_DIR

        - script:
            name: Install Composer dependencies
            code: |
              rm -rf ./vendor
              /usr/bin/composer install --no-interaction

        - script:
            name: Serve application
            code: php -S localhost:8000 >> /dev/null &

        - script:
            name: Create database
            code: mysql -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p$MYSQL_ENV_MYSQL_ROOT_PASSWORD -e "CREATE DATABASE IF NOT EXISTS test;"

        - script:
            name: PHPUnit integration tests
            code: ./vendor/bin/phpunit --configuration phpunit.xml

- Levi Putna

---

创建数据库步骤假设您的主机上有mysql可用。 - Sash

@sash，我正在将MYSQL添加为Wercker服务步骤的一部分，因此我希望它应该在主机上可用，如果不可用，则“创建数据库”应该失败并导致整个测试运行失败。 - Levi Putna

@LeviPutna 当我添加“创建数据库”脚本时，出现以下错误：mysql: command not found，有什么想法吗？我已经添加了mysql服务。 - MatayoshiMariano

Answer 3

我已成功使用以下配置连接到mysql：

box: ruby:2.1.9
build:
    services:
      - id: mysql
        # your credentials for Docker Hub
        username: $USERNAME
        password: $PASSWORD
        tag: 5.5.55
        # set the required environment variable
        env:
          MYSQL_ROOT_PASSWORD: roo
          MYSQL_USER: testuser
          MYSQL_PASSWORD: testuser
          MYSQL_DATABASE: testdb
    steps:
      - bundle-install
      - rails-database-yml
      - script:
          name: echo ruby information
          code: |
              echo "ruby version $(ruby --version) running!"
              echo "from location $(which ruby)"
              echo -p "gem list: $(gem list)"
      - script:
          name: Set up db
          code: |
              touch db/schema.rb
              bundle exec rake db:migrate RAILS_ENV=test
      - script:
          name: rspec
          code: bundle exec rspec

关于rails-database-yml步骤的信息，可以在这里找到。

- MatayoshiMariano