【WordPressカスタマイズ相談】現役プログラマが解決策を教えます

WordPress(ワードプレス)のお悩み、うまくいかなくてお困りなこと、不具合調査、新規制作依頼まで、ウェブアプリケーションエンジニアがあなたをサポートします。

CakePHP3でMariaDBに接続できるのかcloud9で試す

CentOS7のデフォルトデータベースはMySQLではなくMariaDBという新しいDBらしいのですね。
MySQLから派生したDBとして最近よくみかけるMariaDB
MySQLを使っていた人がMariaDBにした場合、CakePHPからの接続は何か特別なことが必要なのか実際にインストールして調べてみたいと思います。


サーバー構築は面倒なので今回もcloud9を利用します。


cloud9はUbuntuなのですが、検索したらすぐにHITしました。
下記サイトがとても参考になりました、というかコマンドそのままいけます。


qiita.com




software-properties-commonをインストール
cloud9:~/workspace $ sudo apt-get install software-properties-common
Reading package lists... Done
Building dependency tree       
Reading state information... Done
software-properties-common is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 82 not upgraded.
cloud9:~/workspace $ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --homedir /tmp/tmp.TGnTO9cQIc --no-auto-check-trustdb --trust-model always --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --keyring /etc/apt/trusted.gpg.d/fkrull-deadsnakes.gpg --keyring /etc/apt/trusted.gpg.d/git-core-ppa.gpg --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
gpg: requesting key 1BB943DB from hkp server keyserver.ubuntu.com
gpg: key 1BB943DB: public key "MariaDB Package Signing Key <package-signing-key@mariadb.org>" imported
gpg: Total number processed: 1
gpg:               imported: 1
リポジトリを変更
cloud9:~/workspace $ sudo add-apt-repository 'deb http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.1/ubuntu trusty main'
パッケージ更新
cloud9:~/workspace $ sudo apt-get update
Get:1 http://ftp.yz.yamagata-u.ac.jp trusty InRelease [3234 B]
Get:2 http://ftp.yz.yamagata-u.ac.jp trusty/main amd64 Packages [5568 B]       
Get:3 http://ftp.yz.yamagata-u.ac.jp trusty/main i386 Packages [5567 B]        
Ign http://downloads-distro.mongodb.org dist InRelease                         
Hit http://ppa.launchpad.net trusty InRelease                                  
Hit http://security.ubuntu.com trusty-security InRelease                       
Ign http://asia-east1.gce.clouds.archive.ubuntu.com trusty InRelease           
Hit http://ppa.launchpad.net trusty InRelease                                  
Hit http://downloads-distro.mongodb.org dist Release.gpg                       
Hit http://security.ubuntu.com trusty-security/main Sources                    
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates InRelease   
Ign http://toolbelt.heroku.com ./ InRelease                                    
Hit http://ppa.launchpad.net trusty/main amd64 Packages                        
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports InRelease 
Hit http://security.ubuntu.com trusty-security/universe Sources                
Hit http://downloads-distro.mongodb.org dist Release                           
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty Release.gpg         
Hit http://ppa.launchpad.net trusty/main i386 Packages                         
Hit http://toolbelt.heroku.com ./ Release.gpg                                  
Hit http://security.ubuntu.com trusty-security/main amd64 Packages             
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/main Sources
Hit http://downloads-distro.mongodb.org dist/10gen amd64 Packages              
Hit http://ppa.launchpad.net trusty/main amd64 Packages                        
Hit http://security.ubuntu.com trusty-security/universe amd64 Packages         
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/restricted Sources
Hit http://toolbelt.heroku.com ./ Release                                      
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/universe Sources
Hit http://ppa.launchpad.net trusty/main i386 Packages                         
Hit http://security.ubuntu.com trusty-security/main i386 Packages              
Hit http://downloads-distro.mongodb.org dist/10gen i386 Packages               
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/multiverse Sources
Hit http://toolbelt.heroku.com ./ Packages                                     
Hit http://security.ubuntu.com trusty-security/universe i386 Packages 
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/main amd64 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/restricted amd64 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/universe amd64 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/multiverse amd64 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/main i386 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/restricted i386 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/universe i386 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-updates/multiverse i386 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/main Sources
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/restricted Sources
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/universe Sources
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/multiverse Sources
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/main amd64 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/restricted amd64 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/universe amd64 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/multiverse amd64 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/main i386 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/restricted i386 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/universe i386 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty-backports/multiverse i386 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty Release             
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/main Sources        
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/restricted Sources  
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/universe Sources    
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/multiverse Sources  
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/main amd64 Packages 
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/restricted amd64 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/universe amd64 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/multiverse amd64 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/main i386 Packages  
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/restricted i386 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/universe i386 Packages
Hit http://asia-east1.gce.clouds.archive.ubuntu.com trusty/multiverse i386 Packages
Fetched 14.4 kB in 9s (1510 B/s)                                               
Reading package lists... Done
mariadb-serverをインストール
cloud9:~/workspace $ sudo apt-get install mariadb-server
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following extra packages will be installed:
  galera-3 iproute libmariadbclient18 libmysqlclient18 libreadline5
  mariadb-client-10.1 mariadb-client-core-10.1 mariadb-common
  mariadb-server-10.1 mariadb-server-core-10.1 mysql-common socat
Suggested packages:
  mariadb-test tinyca
The following packages will be REMOVED:
  libmysqlclient-dev mysql-client-5.5 mysql-client-core-5.5 mysql-server
  mysql-server-5.5 mysql-server-core-5.5
The following NEW packages will be installed:
  galera-3 iproute libmariadbclient18 libreadline5 mariadb-client-10.1
  mariadb-client-core-10.1 mariadb-common mariadb-server mariadb-server-10.1
  mariadb-server-core-10.1 socat
The following packages will be upgraded:
  libmysqlclient18 mysql-common
2 upgraded, 11 newly installed, 6 to remove and 74 not upgraded.
Need to get 20.5 MB of archives.
After this operation, 76.5 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.1/ubuntu/ trusty/main mysql-common all 10.1.17+maria-1~trusty [8442 B]
Get:2 http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.1/ubuntu/ trusty/main mariadb-common all 10.1.17+maria-1~trusty [3202 B]
Get:3 http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.1/ubuntu/ trusty/main galera-3 amd64 25.3.17-trusty [6977 kB]
Get:4 http://asia-east1.gce.clouds.archive.ubuntu.com/ubuntu/ trusty/main libreadline5 amd64 5.2+dfsg-2 [130 kB]
Get:5 http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.1/ubuntu/ trusty/main libmysqlclient18 amd64 10.1.17+maria-1~trusty [2888 B]
Get:6 http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.1/ubuntu/ trusty/main libmariadbclient18 amd64 10.1.17+maria-1~trusty [578 kB]
Get:7 http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.1/ubuntu/ trusty/main mariadb-client-core-10.1 amd64 10.1.17+maria-1~trusty [687 kB]
Get:8 http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.1/ubuntu/ trusty/main mariadb-client-10.1 amd64 10.1.17+maria-1~trusty [1059 kB]
Get:9 http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.1/ubuntu/ trusty/main mariadb-server-core-10.1 amd64 10.1.17+maria-1~trusty [4455 kB]
Get:10 http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.1/ubuntu/ trusty/main mariadb-server-10.1 amd64 10.1.17+maria-1~trusty [6272 kB]
Get:11 http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.1/ubuntu/ trusty/main mariadb-server all 10.1.17+maria-1~trusty [2918 B]
Get:12 http://asia-east1.gce.clouds.archive.ubuntu.com/ubuntu/ trusty-updates/main iproute all 1:3.12.0-2ubuntu1 [2392 B]
Get:13 http://asia-east1.gce.clouds.archive.ubuntu.com/ubuntu/ trusty/universe socat amd64 1.7.2.3-1 [302 kB]
Fetched 20.5 MB in 2s (8381 kB/s)
debconf: unable to initialize frontend: Dialog
debconf: (Dialog frontend requires a screen at least 13 lines tall and 31 columns wide.)
debconf: falling back to frontend: Readline
Preconfiguring packages ...
Configuring mariadb-server-10.1
-------------------------------

While not mandatory, it is highly recommended that you set a password for the MariaDB administrative "root" user.

If this field is left blank, the password will not be changed.

New password for the MariaDB "root" user: 



Repeat password for the MariaDB "root" user: 


(Reading database ... 75375 files and directories currently installed.)
Removing libmysqlclient-dev (5.5.50-0ubuntu0.14.04.1) ...
Removing mysql-server (5.5.50-0ubuntu0.14.04.1) ...
Removing mysql-server-5.5 (5.5.50-0ubuntu0.14.04.1) ...
 * Stopping MySQL database server mysqld
   ...done.
Removing mysql-client-5.5 (5.5.50-0ubuntu0.14.04.1) ...
Removing mysql-client-core-5.5 (5.5.50-0ubuntu0.14.04.1) ...
Removing mysql-server-core-5.5 (5.5.50-0ubuntu0.14.04.1) ...
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
Selecting previously unselected package libreadline5:amd64.
(Reading database ... 75083 files and directories currently installed.)
Preparing to unpack .../libreadline5_5.2+dfsg-2_amd64.deb ...
Unpacking libreadline5:amd64 (5.2+dfsg-2) ...
Preparing to unpack .../mysql-common_10.1.17+maria-1~trusty_all.deb ...
Unpacking mysql-common (10.1.17+maria-1~trusty) over (5.5.50-0ubuntu0.14.04.1) ...
Selecting previously unselected package mariadb-common.
Preparing to unpack .../mariadb-common_10.1.17+maria-1~trusty_all.deb ...
Unpacking mariadb-common (10.1.17+maria-1~trusty) ...
Selecting previously unselected package galera-3.
Preparing to unpack .../galera-3_25.3.17-trusty_amd64.deb ...
Unpacking galera-3 (25.3.17-trusty) ...
Selecting previously unselected package iproute.
Preparing to unpack .../iproute_1%3a3.12.0-2ubuntu1_all.deb ...
Unpacking iproute (1:3.12.0-2ubuntu1) ...
Preparing to unpack .../libmysqlclient18_10.1.17+maria-1~trusty_amd64.deb ...
Unpacking libmysqlclient18 (10.1.17+maria-1~trusty) over (5.5.50-0ubuntu0.14.04.1) ...
Selecting previously unselected package libmariadbclient18.
Preparing to unpack .../libmariadbclient18_10.1.17+maria-1~trusty_amd64.deb ...
Unpacking libmariadbclient18 (10.1.17+maria-1~trusty) ...
Selecting previously unselected package mariadb-client-core-10.1.
Preparing to unpack .../mariadb-client-core-10.1_10.1.17+maria-1~trusty_amd64.deb ...
Unpacking mariadb-client-core-10.1 (10.1.17+maria-1~trusty) ...
Selecting previously unselected package mariadb-client-10.1.
Preparing to unpack .../mariadb-client-10.1_10.1.17+maria-1~trusty_amd64.deb ...
Unpacking mariadb-client-10.1 (10.1.17+maria-1~trusty) ...
Selecting previously unselected package mariadb-server-core-10.1.
Preparing to unpack .../mariadb-server-core-10.1_10.1.17+maria-1~trusty_amd64.deb ...
Unpacking mariadb-server-core-10.1 (10.1.17+maria-1~trusty) ...
Selecting previously unselected package socat.
Preparing to unpack .../socat_1.7.2.3-1_amd64.deb ...
Unpacking socat (1.7.2.3-1) ...
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
Setting up mysql-common (10.1.17+maria-1~trusty) ...
Installing new version of config file /etc/mysql/my.cnf ...
Setting up mariadb-common (10.1.17+maria-1~trusty) ...
Selecting previously unselected package mariadb-server-10.1.
(Reading database ... 75286 files and directories currently installed.)
Preparing to unpack .../mariadb-server-10.1_10.1.17+maria-1~trusty_amd64.deb ...
Unpacking mariadb-server-10.1 (10.1.17+maria-1~trusty) ...
Selecting previously unselected package mariadb-server.
Preparing to unpack .../mariadb-server_10.1.17+maria-1~trusty_all.deb ...
Unpacking mariadb-server (10.1.17+maria-1~trusty) ...
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
Processing triggers for ureadahead (0.100.0-16) ...
Setting up libreadline5:amd64 (5.2+dfsg-2) ...
Setting up galera-3 (25.3.17-trusty) ...
Setting up iproute (1:3.12.0-2ubuntu1) ...
Setting up socat (1.7.2.3-1) ...
Setting up libmysqlclient18 (10.1.17+maria-1~trusty) ...
Setting up libmariadbclient18 (10.1.17+maria-1~trusty) ...
Setting up mariadb-client-core-10.1 (10.1.17+maria-1~trusty) ...
Setting up mariadb-client-10.1 (10.1.17+maria-1~trusty) ...
Setting up mariadb-server-core-10.1 (10.1.17+maria-1~trusty) ...
Setting up mariadb-server-10.1 (10.1.17+maria-1~trusty) ...
Installing new version of config file /etc/apparmor.d/usr.sbin.mysqld ...
Installing new version of config file /etc/init.d/mysql ...
Installing new version of config file /etc/logrotate.d/mysql-server ...
Installing new version of config file /etc/mysql/debian-start ...
Installing new version of config file /etc/mysql/conf.d/mysqld_safe_syslog.cnf ...
 * Stopping MariaDB database server mysqld
   ...done.
2016-09-26 10:32:08 140397257996224 [Note] /usr/sbin/mysqld (mysqld 10.1.17-MariaDB-1~trusty) starting as process 3663 ...
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: The InnoDB memory heap is disabled
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Using SSE crc32 instructions
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Completed initialization of buffer pool
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Highest supported file format is Barracuda.
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: The log sequence numbers 1595675 and 1595675 in ibdata files do not match the log sequence number 1595685 in the ib_logfiles!
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Database was not shutdown normally!
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Starting crash recovery.
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Restoring possible half-written data pages 
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: from the doublewrite buffer...
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Creating tablespace and datafile system tables.
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Tablespace and datafile system tables created.
2016-09-26 10:32:08 140397257996224 [Warning] InnoDB: Resizing redo log from 2*320 to 2*3072 pages, LSN=1600094
2016-09-26 10:32:08 140397257996224 [Warning] InnoDB: Starting to delete and rewrite log files.
2016-09-26 10:32:08 140397257996224 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2016-09-26 10:32:09 140397257996224 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2016-09-26 10:32:09 140397257996224 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-09-26 10:32:09 140397257996224 [Warning] InnoDB: New log files created, LSN=1600524
2016-09-26 10:32:09 140397257996224 [Note] InnoDB: 128 rollback segment(s) are active.
2016-09-26 10:32:09 140397257996224 [Note] InnoDB: Waiting for purge to start
2016-09-26 10:32:09 140397257996224 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.31-77.0 started; log sequence number 1595685
2016-09-26 10:32:09 140396332029696 [Note] InnoDB: Dumping buffer pool(s) not yet started
2016-09-26 10:32:09 140397257996224 [Note] Plugin 'FEEDBACK' is disabled.
2016-09-26 10:32:09 140397257681664 [ERROR] mysqld: Table './mysql/user' is marked as crashed and should be repaired
2016-09-26 10:32:09 140397257681664 [ERROR] mysqld: Table 'user' is marked as crashed and should be repaired
2016-09-26 10:32:09 140397257681664 [Warning] Checking table:   './mysql/user'
2016-09-26 10:32:09 140397257681664 [ERROR] mysql.user: 1 client is using or hasn't closed the table properly
2016-09-26 10:32:09 140397257681664 [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired
2016-09-26 10:32:09 140397257681664 [ERROR] mysqld: Table 'db' is marked as crashed and should be repaired
2016-09-26 10:32:09 140397257681664 [Warning] Checking table:   './mysql/db'
2016-09-26 10:32:09 140397257681664 [ERROR] mysql.db: 1 client is using or hasn't closed the table properly
2016-09-26 10:32:09 140397257681664 [ERROR] Missing system table mysql.roles_mapping; please run mysql_upgrade to create it
 * Starting MariaDB database server mysqld
   ...done.
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
Processing triggers for ureadahead (0.100.0-16) ...
Setting up mariadb-server (10.1.17+maria-1~trusty) ...
Processing triggers for libc-bin (2.19-0ubuntu6.6) ...


いくつかERRORが出ていますが、mysqldのエラーとmysql.dbのエラーみたい。。。
ためしにmysqlコマンドでログインできるかチェック

cloud9:~/workspace $ mysql -u ユーザー -p
Enter password: パスワードを入力
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 57
Server version: 10.1.17-MariaDB-1~trusty mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>


ログインできたので問題ありません。
続いてCakePHP3をインストール。
soudan.hatenablog.jp


CakePHP3のDB設定は config/app.php に記述する。
「Datasources」という項目

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'ホスト名',
        /**
         * CakePHP will use the default DB port based on the driver selected
         * MySQL on MAMP uses port 8889, MAMP users will want to uncomment
         * the following line and set the port accordingly
         */
        //'port' => 'non_standard_port_number',
        'username' => 'ユーザー',
        'password' => 'パスワード',
        'database' => 'maria_db',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => true,
        'log' => false,

これで実行してみると

f:id:jsaz:20160926231528p:plain

きちんと接続ができている!

MySQLmariaDB関係なくCakePHP3はDB接続が可能である*