CakePHP3でMariaDBに接続できるのかcloud9で試す
CentOS7のデフォルトデータベースはMySQLではなくMariaDBという新しいDBらしいのですね。
MySQLから派生したDBとして最近よくみかけるMariaDB。
MySQLを使っていた人がMariaDBにした場合、CakePHPからの接続は何か特別なことが必要なのか実際にインストールして調べてみたいと思います。
サーバー構築は面倒なので今回もcloud9を利用します。
cloud9はUbuntuなのですが、検索したらすぐにHITしました。
下記サイトがとても参考になりました、というかコマンドそのままいけます。
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,
これで実行してみると
きちんと接続ができている!