OpenShiftでPHP Gearと一緒にMySQLを使えるようにする

PHP動いているんだったらやっぱりDBも使いたいよね。ってことでMySQLを動かしてみようと。

公式のDocumentにはこんな感じで書いてあった。

If you want to add a database to an existing application the command is similar:

$ rhc cartridge-add <dbcartridge> --app <myappname>

This command will also spin up your database server and insert the environment variables.

前回のHerokuよりも無料枠が良さげなOpenShiftでPHPをデプロイするでmyphpappというアプリ名で作ったので、これで使えるようにしてみる。
まずは、使えるカートリッジを確認する。

$ rhc cartridge list RSA 1024 bit CA certificates are loaded due to old openssl compatibility jbossas-7                JBoss Application Server 7              web jboss-dv-6.1.0 (!)       JBoss Data Virtualization 6             web jbosseap-6 (*)           JBoss Enterprise Application Platform 6 web jboss-unified-push-1 (!) JBoss Unified Push Server 1.0.0.Beta1   web jboss-unified-push-2 (!) JBoss Unified Push Server 1.0.0.Beta2   web jenkins-1                Jenkins Server                          web nodejs-0.10              Node.js 0.10                            web perl-5.10                Perl 5.10                               web php-5.3                  PHP 5.3                                 web php-5.4                  PHP 5.4                                 web zend-6.1                 PHP 5.4 with Zend Server 6.1            web python-2.6               Python 2.6                              web python-2.7               Python 2.7                              web python-3.3               Python 3.3                              web ruby-1.8                 Ruby 1.8                                web ruby-1.9                 Ruby 1.9                                web ruby-2.0                 Ruby 2.0                                web jbossews-1.0             Tomcat 6 (JBoss EWS 1.0)                web jbossews-2.0             Tomcat 7 (JBoss EWS 2.0)                web jboss-vertx-2.1 (!)      Vert.x 2.1                              web jboss-wildfly-10 (!)     WildFly Application Server 10           web jboss-wildfly-8 (!)      WildFly Application Server 8.2.1.Final  web jboss-wildfly-9 (!)      WildFly Application Server 9            web diy-0.1                  Do-It-Yourself 0.1                      web cron-1.4                 Cron 1.4                                addon jenkins-client-1         Jenkins Client                          addon mongodb-2.4              MongoDB 2.4                             addon mysql-5.1                MySQL 5.1                               addon mysql-5.5                MySQL 5.5                               addon phpmyadmin-4             phpMyAdmin 4.0                          addon postgresql-8.4           PostgreSQL 8.4                          addon postgresql-9.2           PostgreSQL 9.2                          addon rockmongo-1.1            RockMongo 1.1                           addon switchyard-0             SwitchYard 0.8.0                        addon haproxy-1.4              Web Load Balancer                       addon   Note: Web cartridges can only be added to new applications.   (*) denotes a cartridge with additional usage costs.   (!) denotes a cartridge that will not receive automatic security updates.

mysql-5.5というのがあったので、これが使えそうだ。
なので、作成してみる。

$ rhc cartridge-add mysql-5.5 --app myphpapp RSA 1024 bit CA certificates are loaded due to old openssl compatibility Adding mysql-5.5 to application 'myphpapp' ... done   mysql-5.5 (MySQL 5.5) ---------------------   Gears:          Located with php-5.4   Connection URL: mysql://$OPENSHIFT_MYSQL_DB_HOST:$OPENSHIFT_MYSQL_DB_PORT/   Database Name:  myphpapp   Password:       ***********   Username:       ***********   MySQL 5.5 database added.  Please make note of these credentials:          Root User: ***********    Root Password: ***********    Database Name: myphpapp   Connection URL: mysql://$OPENSHIFT_MYSQL_DB_HOST:$OPENSHIFT_MYSQL_DB_PORT/   You can manage your new MySQL database by also embedding phpmyadmin. The phpmyadmin username and password will be the same as the MySQL credentials above.

MySQLへの接続は環境変数に入っているようだ。myphpappにSSHで接続して、envで確認してみる。

$ rhc ssh myphpapp   \> env | grep OPENSHIFT_MYSQL - OPENSHIFT_MYSQL_DIR=xxxxxx OPENSHIFT_MYSQL_DB_PORT=xxxxxx OPENSHIFT_MYSQL_DB_HOST=xxxxxx OPENSHIFT_MYSQL_DB_PASSWORD=xxxxxx OPENSHIFT_MYSQL_IDENT=xxxxxx OPENSHIFT_MYSQL_DB_USERNAME=xxxxxx OPENSHIFT_MYSQL_DB_SOCKET=xxxxxx OPENSHIFT_MYSQL_DB_URL=xxxxxx OPENSHIFT_MYSQL_VERSION=5.5 OPENSHIFT_MYSQL_DB_LOG_DIR=xxxxxx OPENSHIFT_MYSQL_LD_LIBRARY_PATH_ELEMENT=xxxxxx

接続に必要な環境変数がいっぱい出てきたので、これを元にSSHでMySQLへログインできるのかやってみる。

\> mysql -u ($OPENSHIFT_MYSQL_DB_USERNAME) -p -h ($OPENSHIFT_MYSQL_DB_HOST) Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.45 MySQL Community Server (GPL)   Copyright (c) 2000, 2015, 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>

普通にデータベースとか見えるのかな。

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | myphpapp           | | mysql              | | performance_schema | +--------------------+ 4 rows in set (0.01 sec)   mysql> use myphpapp; Database changed mysql> show tables; Empty set (0.00 sec)

結構普通に使えて超便利！

MySQL 5.7.9とPHP5.6.15をインストールした時のお話

昨日、MySQLをインストールしたので、今日はPHPをインストールした。

ちなみにこの辺はいつもやってるので、慣れてるし、手順もconfigureオプションも確立している...はずなのにはまった。。

このようなオプションでPHPをconfigureしようとするとエラーが出て全く進まない。

# ./configure --enable-mbstring=all --enable-mbregex --with-mysql=/usr/local/mysql --with-apxs2=/usr/local/apache2/bin/apxs --with-openssl --with-zlib --with-pdo-mysql=/usr/local/mysql

checking for specified location of the MySQL UNIX socket... no
configure: error: Cannot find libmysqlclient_r under /usr/local/mysql/.
Note that the MySQL client library is not bundled anymore!

いろいろ調べてやってみた。

...けど結局は同じエラーメッセージが出て解決しない。

• /etc/ld.so.confに/usr/local/mysql/libを追加してldconfig
• configureオプションに--with-libdir=lib64を追加して/usr/local/mysql/lib64を/usr/local/mysql/libのシンボリックリンクとして作成
• 関係ないと思うのにconfigureオプションに--with-mysqli=/usr/local/mysql/bin/mysql_configを追加した
• configureオプションにおまけで--with-mysql-sockを追加した
• やぶれかぶれに環境変数にLD_LIBRARYPATHに/usr/local/mysql/libを追加

しかし、/usr/local/mysql/libを見るとlibmysqlclient_rらしきものが本当にない。
libmysqlclient.soがあるので問題ないかと思ったけど、どうやらconnectorがないのではないかと思い、C Driver for MySQL (Connector/C)をインストールしてみた。

# wget http://dev.mysql.com/get/Downloads/Connector-C/mysql-connector-c-6.1.6-src.tar.gz # tar -xvzf mysql-connector-c-6.1.6-src.tar.gz # cd mysql-connector-c-6.1.6-src # ccmake . # make # make install

なんと上手くいった！ちなみに上記の箇条書きで試した方法は全て削除している。
こんなことしてたかなぁ。。。

MySQL5.7.9を入れてみた

MySQL 5.7.9をインストールしてみました。

ソースからコンパイルしてインストールする時の5.6系から変更点がいくつかあったのでとりあえず書いときます。

▪️cmakeの際にboostが無いって怒られた

---

-- Could not find (the correct version of) boost.
-- MySQL currently requires boost_1_59_0

CMake Error at cmake/boost.cmake:76 (MESSAGE):
You can download it with -DDOWNLOAD_BOOST=1 -DWITH_BOOST=

This CMake script will look for boost in . If it is not there,
it will download and unpack it (in that directory) for you.

If you are inside a firewall, you may need to use an http proxy:

export http_proxy=http://example.com:80

Call Stack (most recent call first):
cmake/boost.cmake:228 (COULD_NOT_FIND_BOOST)
CMakeLists.txt:435 (INCLUDE)


-- Configuring incomplete, errors occurred!

cmakeのオプションで言われた通りつけてあげましょう。

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/src/boost

後のmake、make installはいつも通り。

▪️DB初期化のためのコマンドがmysql_install_dbからmysqld --initializeに置き換わった

---

mysql_install_dbはまだ使えるっぽいけど

# /usr/local/mysql/bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data

とすると、

2015-11-07 18:55:49 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize

と言われる。

今後のためにも慣れておいたほうが良さそうなので、mysqld --initializeを使うことにした。ただし、--initializeはrootユーザのパスワードをランダム生成するので、後で自分で設定するためには--initialize-insecureを使用しないといけないみたい。

一応、コマンドを使った際のログを残しておく。

# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/usr/local/mysql/data --log-error-verbosity=3 2015-11-07T10:07:21.400486Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. 2015-11-07T10:07:21.400558Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set. 2015-11-07T10:07:21.400584Z 0 [Note] Ignoring --secure-file-priv value as server is running with --initialize(-insecure) or --bootstrap. 2015-11-07T10:07:21.400613Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.9) starting as process 2514 ... 2015-11-07T10:07:21.402084Z 0 [Note] Creating the data directory /usr/local/mysql/data/ 2015-11-07T10:07:21.403910Z 0 [Note] InnoDB: PUNCH HOLE support available 2015-11-07T10:07:21.403936Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2015-11-07T10:07:21.403941Z 0 [Note] InnoDB: Uses event mutexes 2015-11-07T10:07:21.403945Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2015-11-07T10:07:21.403949Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3 2015-11-07T10:07:21.404183Z 0 [Note] InnoDB: Number of pools: 1 2015-11-07T10:07:21.404314Z 0 [Note] InnoDB: Using CPU crc32 instructions 2015-11-07T10:07:21.410576Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2015-11-07T10:07:21.416844Z 0 [Note] InnoDB: Completed initialization of buffer pool 2015-11-07T10:07:21.418882Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2015-11-07T10:07:21.429619Z 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created! 2015-11-07T10:07:21.430027Z 0 [Note] InnoDB: Setting file './ibdata1' size to 12 MB. Physically writing the file full; Please wait ... 2015-11-07T10:07:21.447162Z 0 [Note] InnoDB: File './ibdata1' size is now 12 MB. 2015-11-07T10:07:21.447502Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 2015-11-07T10:07:21.524477Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2015-11-07T10:07:21.598728Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2015-11-07T10:07:21.598856Z 0 [Warning] InnoDB: New log files created, LSN=45790 2015-11-07T10:07:21.598882Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2015-11-07T10:07:21.598987Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2015-11-07T10:07:21.619434Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2015-11-07T10:07:21.619641Z 0 [Note] InnoDB: Doublewrite buffer not found: creating new 2015-11-07T10:07:21.626953Z 0 [Note] InnoDB: Doublewrite buffer created 2015-11-07T10:07:21.632331Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2015-11-07T10:07:21.632360Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2015-11-07T10:07:21.633023Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2015-11-07T10:07:21.638561Z 0 [Note] InnoDB: Foreign key constraint system tables created 2015-11-07T10:07:21.638618Z 0 [Note] InnoDB: Creating tablespace and datafile system tables. 2015-11-07T10:07:21.639340Z 0 [Note] InnoDB: Tablespace and datafile system tables created. 2015-11-07T10:07:21.639374Z 0 [Note] InnoDB: Creating sys_virtual system tables. 2015-11-07T10:07:21.639991Z 0 [Note] InnoDB: sys_virtual table created 2015-11-07T10:07:21.640373Z 0 [Note] InnoDB: Waiting for purge to start 2015-11-07T10:07:21.690554Z 0 [Note] InnoDB: 5.7.9 started; log sequence number 0 2015-11-07T10:07:21.691056Z 0 [Note] InnoDB: not started 2015-11-07T10:07:21.691257Z 0 [Note] Plugin 'FEDERATED' is disabled. 2015-11-07T10:07:21.691823Z 0 [Note] Salting uuid generator variables, current_pid: 2514, server_start_time: 1446890841, bytes_sent: 0, 2015-11-07T10:07:21.691909Z 0 [Note] Generated uuid: '55fba61b-8537-11e5-8f4c-000c292cb85b', server_start_time: 707628092897482539, bytes_sent: 72185296 2015-11-07T10:07:21.691926Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 55fba61b-8537-11e5-8f4c-000c292cb85b. 2015-11-07T10:07:21.694302Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2015-11-07T10:07:21.694833Z 1 [Note] Creating the system database 2015-11-07T10:07:21.694852Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 2015-11-07T10:07:21.695095Z 1 [Note] Creating the system tables 2015-11-07T10:07:22.060945Z 1 [Note] Filling in the system tables, part 1 2015-11-07T10:07:22.061871Z 1 [Note] Filling in the system tables, part 2 2015-11-07T10:07:22.061882Z 1 [Note] Filling in the mysql.help table 2015-11-07T10:07:22.191693Z 1 [Note] Creating the sys schema 2015-11-07T10:07:22.511545Z 1 [Note] Bootstrapping complete 2015-11-07T10:07:22.512002Z 0 [Note] Giving 0 client threads a chance to die gracefully 2015-11-07T10:07:22.512025Z 0 [Note] Shutting down slave threads 2015-11-07T10:07:22.512032Z 0 [Note] Forcefully disconnecting 0 remaining clients 2015-11-07T10:07:22.512220Z 0 [Note] Binlog end 2015-11-07T10:07:22.512960Z 0 [Note] InnoDB: FTS optimize thread exiting. 2015-11-07T10:07:22.513059Z 0 [Note] InnoDB: Starting shutdown... 2015-11-07T10:07:22.615835Z 0 [Note] InnoDB: Dumping buffer pool(s) to /usr/local/mysql/data/ib_buffer_pool 2015-11-07T10:07:22.616224Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 151107 19:07:22 2015-11-07T10:07:23.832562Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2471242 2015-11-07T10:07:23.834259Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

ここからはそのまま。

# /usr/local/mysql/support-files/mysql.server start Starting MySQL.. SUCCESS! # /usr/local/mysql/bin/mysqladmin -u root password New password: Confirm new password: Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. # /usr/local/mysql/bin/mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.9 Source distribution   Copyright (c) 2000, 2015, 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>

一旦、インストールはこれで終了

今日の作業

---

これで１日終わった（´д` ;）