Skip to content

Instantly share code, notes, and snippets.

@nakunaru

nakunaru/gist:11026951

Last active August 29, 2015 14:00
Show Gist options
  • Save nakunaru/11026951 to your computer and use it in GitHub Desktop.
Save nakunaru/11026951 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL install
Raw
gistfile1.txt
1. デフォルトで存在しているmysqlユーザのパスワードを変更
[root@canvaslinux ~]# sudo passwd mysql
ユーザー mysql のパスワードを変更。
新しいパスワード: <- 今回は「mysql」とした
よくないパスワード: 短かすぎます
よくないパスワード: 簡単すぎます
新しいパスワードを再入力してください: <- 今回は「mysql」とした
passwd: 全ての認証トークンが正しく更新できました。
2. MySQL用のyumリポジトリのインストール
参照 http://dev.mysql.com/doc/mysql-repo-excerpt/5.6/en/linux-installation-yum-repo.html
http://qiita.com/kmats@github/items/48457b7e5046f3cdf4d8
[root@canvaslinux ~]# sudo yum install -y http://repo.mysql.com/mysql-community-release-el6-4.noarch.rpm
Loaded plugins: refresh-packagekit, security
ol6_UEK_latest | 1.2 kB 00:00
ol6_UEK_latest/primary | 13 MB 00:04
ol6_UEK_latest 295/295
ol6_latest | 1.4 kB 00:00
Setting up Install Process
mysql-community-release-el6-4.noarch.rpm | 5.0 kB 00:00
Examining /var/tmp/yum-root-y74Kne/mysql-community-release-el6-4.noarch.rpm: mysql-community-release-el6-4.noarch
Marking /var/tmp/yum-root-y74Kne/mysql-community-release-el6-4.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-release.noarch 0:el6-4 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=================================================================================================================================================================
Package Arch Version Repository Size
=================================================================================================================================================================
Installing:
mysql-community-release noarch el6-4 /mysql-community-release-el6-4.noarch 2.5 k
Transaction Summary
=================================================================================================================================================================
Install 1 Package(s)
Total size: 2.5 k
Installed size: 2.5 k
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : mysql-community-release-el6-4.noarch 1/1
Verifying : mysql-community-release-el6-4.noarch 1/1
Installed:
mysql-community-release.noarch 0:el6-4
Complete!
[root@canvaslinux ~]# sudo yum list | grep mysql-community
mysql-community-release.noarch el6-4 @/mysql-community-release-el6-4.noarch
mysql-community-client.x86_64 5.6.14-3.el6 mysql-community
mysql-community-common.i686 5.6.14-3.el6 mysql-community
mysql-community-common.x86_64 5.6.14-3.el6 mysql-community
mysql-community-devel.i686 5.6.14-3.el6 mysql-community
mysql-community-devel.x86_64 5.6.14-3.el6 mysql-community
mysql-community-embedded.i686 5.6.14-3.el6 mysql-community
mysql-community-embedded.x86_64 5.6.14-3.el6 mysql-community
mysql-community-embedded-devel.i686 5.6.14-3.el6 mysql-community
mysql-community-embedded-devel.x86_64 5.6.14-3.el6 mysql-community
mysql-community-libs.i686 5.6.14-3.el6 mysql-community
mysql-community-libs.x86_64 5.6.14-3.el6 mysql-community
mysql-community-libs-compat.i686 5.6.14-3.el6 mysql-community
mysql-community-libs-compat.x86_64 5.6.14-3.el6 mysql-community
mysql-community-release.noarch el6-5 mysql-community
mysql-community-server.x86_64 5.6.14-3.el6 mysql-community
mysql-community-test.x86_64 5.6.14-3.el6 mysql-community
mysql-connector-odbc.x86_64 5.2.6-1.el6 mysql-community
mysql-workbench-community.x86_64 6.0.8-1.el6 mysql-community
--> 現時点では5.6がインストールされるようだ
3. MySQLのインストール
[root@canvaslinux ~]# sudo yum install mysql-common mysql-server
Loaded plugins: refresh-packagekit, security
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-common.x86_64 0:5.6.14-3.el6 will be installed
---> Package mysql-community-server.x86_64 0:5.6.14-3.el6 will be obsoleting
--> Processing Dependency: mysql-community-client(x86-64) = 5.6.14-3.el6 for package: mysql-community-server-5.6.14-3.el6.x86_64
---> Package mysql-server.x86_64 0:5.1.66-2.el6_3 will be obsoleted
--> Running transaction check
---> Package mysql.x86_64 0:5.1.66-2.el6_3 will be obsoleted
--> Processing Dependency: mysql = 5.1.66-2.el6_3 for package: mysql-devel-5.1.66-2.el6_3.x86_64
---> Package mysql-community-client.x86_64 0:5.6.14-3.el6 will be obsoleting
--> Processing Dependency: mysql-community-libs(x86-64) = 5.6.14-3.el6 for package: mysql-community-client-5.6.14-3.el6.x86_64
--> Running transaction check
---> Package mysql-community-devel.x86_64 0:5.6.14-3.el6 will be obsoleting
---> Package mysql-community-libs.x86_64 0:5.6.14-3.el6 will be obsoleting
---> Package mysql-devel.x86_64 0:5.1.66-2.el6_3 will be obsoleted
---> Package mysql-libs.x86_64 0:5.1.66-2.el6_3 will be obsoleted
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: 2:postfix-2.6.6-2.2.el6_1.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: redland-1.0.7-11.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: 2:postfix-2.6.6-2.2.el6_1.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: redland-1.0.7-11.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient_r.so.16()(64bit) for package: 1:qt-mysql-4.6.2-25.el6.x86_64
--> Processing Dependency: libmysqlclient_r.so.16(libmysqlclient_16)(64bit) for package: 1:qt-mysql-4.6.2-25.el6.x86_64
--> Running transaction check
---> Package mysql-libs.x86_64 0:5.1.66-2.el6_3 will be updated
---> Package mysql-libs.x86_64 0:5.1.73-3.el6_5 will be an update
--> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: mysql-libs-5.1.73-3.el6_5.x86_64
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: mysql-libs-5.1.73-3.el6_5.x86_64
---> Package postfix.x86_64 2:2.6.6-2.2.el6_1 will be updated
---> Package postfix.x86_64 2:2.6.6-6.el6_5 will be an update
---> Package qt-mysql.x86_64 1:4.6.2-25.el6 will be updated
---> Package qt-mysql.x86_64 1:4.6.2-28.el6_5 will be an update
--> Processing Dependency: qt(x86-64) = 1:4.6.2-28.el6_5 for package: 1:qt-mysql-4.6.2-28.el6_5.x86_64
--> Running transaction check
---> Package openssl.x86_64 0:1.0.0-27.el6 will be updated
--> Processing Dependency: openssl = 1.0.0-27.el6 for package: openssl-devel-1.0.0-27.el6.x86_64
---> Package openssl.x86_64 0:1.0.1e-16.el6_5.7 will be an update
---> Package qt.x86_64 1:4.6.2-25.el6 will be updated
--> Processing Dependency: qt(x86-64) = 1:4.6.2-25.el6 for package: 1:qt-x11-4.6.2-25.el6.x86_64
--> Processing Dependency: qt(x86-64) = 1:4.6.2-25.el6 for package: 1:qt-devel-4.6.2-25.el6.x86_64
--> Processing Dependency: qt(x86-64) = 1:4.6.2-25.el6 for package: 1:qt-sqlite-4.6.2-25.el6.x86_64
---> Package qt.x86_64 1:4.6.2-28.el6_5 will be an update
--> Running transaction check
---> Package openssl-devel.x86_64 0:1.0.0-27.el6 will be updated
---> Package openssl-devel.x86_64 0:1.0.1e-16.el6_5.7 will be an update
---> Package qt-devel.x86_64 1:4.6.2-25.el6 will be updated
---> Package qt-devel.x86_64 1:4.6.2-28.el6_5 will be an update
---> Package qt-sqlite.x86_64 1:4.6.2-25.el6 will be updated
---> Package qt-sqlite.x86_64 1:4.6.2-28.el6_5 will be an update
---> Package qt-x11.x86_64 1:4.6.2-25.el6 will be updated
---> Package qt-x11.x86_64 1:4.6.2-28.el6_5 will be an update
--> Restarting Dependency Resolution with new changes.
--> Running transaction check
---> Package mysql-libs.x86_64 0:5.1.73-3.el6_5 will be an update
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: 2:postfix-2.6.6-6.el6_5.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: redland-1.0.7-11.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient_r.so.16()(64bit) for package: 1:qt-mysql-4.6.2-28.el6_5.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: 2:postfix-2.6.6-6.el6_5.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: redland-1.0.7-11.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient_r.so.16(libmysqlclient_16)(64bit) for package: 1:qt-mysql-4.6.2-28.el6_5.x86_64
--> Running transaction check
---> Package mysql-community-libs-compat.x86_64 0:5.6.14-3.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================================================================================================================================
Package Arch Version Repository Size
====================================================================================================================================================================================================================
Installing:
mysql-community-client x86_64 5.6.14-3.el6 mysql-community 18 M
replacing mysql.x86_64 5.1.66-2.el6_3
mysql-community-common x86_64 5.6.14-3.el6 mysql-community 296 k
mysql-community-devel x86_64 5.6.14-3.el6 mysql-community 3.2 M
replacing mysql-devel.x86_64 5.1.66-2.el6_3
mysql-community-libs x86_64 5.6.14-3.el6 mysql-community 1.8 M
replacing mysql-libs.x86_64 5.1.66-2.el6_3
mysql-community-server x86_64 5.6.14-3.el6 mysql-community 51 M
replacing mysql-server.x86_64 5.1.66-2.el6_3
Installing for dependencies:
mysql-community-libs-compat x86_64 5.6.14-3.el6 mysql-community 1.6 M
Updating for dependencies:
openssl x86_64 1.0.1e-16.el6_5.7 ol6_latest 1.5 M
openssl-devel x86_64 1.0.1e-16.el6_5.7 ol6_latest 1.2 M
postfix x86_64 2:2.6.6-6.el6_5 ol6_latest 2.0 M
qt x86_64 1:4.6.2-28.el6_5 ol6_latest 3.9 M
qt-devel x86_64 1:4.6.2-28.el6_5 ol6_latest 9.8 M
qt-mysql x86_64 1:4.6.2-28.el6_5 ol6_latest 58 k
qt-sqlite x86_64 1:4.6.2-28.el6_5 ol6_latest 51 k
qt-x11 x86_64 1:4.6.2-28.el6_5 ol6_latest 12 M
Transaction Summary
====================================================================================================================================================================================================================
Install 6 Package(s)
Upgrade 8 Package(s)
Total download size: 107 M
Is this ok [y/N]: y
Downloading Packages:
(1/14): mysql-community-client-5.6.14-3.el6.x86_64.rpm | 18 MB 00:17
(2/14): mysql-community-common-5.6.14-3.el6.x86_64.rpm | 296 kB 00:00
(3/14): mysql-community-devel-5.6.14-3.el6.x86_64.rpm | 3.2 MB 00:01
(4/14): mysql-community-libs-5.6.14-3.el6.x86_64.rpm | 1.8 MB 00:03
(5/14): mysql-community-libs-compat-5.6.14-3.el6.x86_64.rpm | 1.6 MB 00:01
(6/14): mysql-community-server-5.6.14-3.el6.x86_64.rpm | 51 MB 00:16
(7/14): openssl-1.0.1e-16.el6_5.7.x86_64.rpm | 1.5 MB 00:00
(8/14): openssl-devel-1.0.1e-16.el6_5.7.x86_64.rpm | 1.2 MB 00:00
(9/14): postfix-2.6.6-6.el6_5.x86_64.rpm | 2.0 MB 00:00
(10/14): qt-4.6.2-28.el6_5.x86_64.rpm | 3.9 MB 00:01
(11/14): qt-devel-4.6.2-28.el6_5.x86_64.rpm | 9.8 MB 00:03
(12/14): qt-mysql-4.6.2-28.el6_5.x86_64.rpm | 58 kB 00:00
(13/14): qt-sqlite-4.6.2-28.el6_5.x86_64.rpm | 51 kB 00:00
(14/14): qt-x11-4.6.2-28.el6_5.x86_64.rpm | 12 MB 00:03
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total
警告: rpmts_HdrFromFdno: V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
Userid : MySQL Release Engineering <[email protected]>
Package: mysql-community-release-el6-4.noarch (@/mysql-community-release-el6-4.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Updating : openssl-1.0.1e-16.el6_5.7.x86_64 1/26
Updating : 1:qt-4.6.2-28.el6_5.x86_64 2/26
Installing : mysql-community-libs-compat-5.6.14-3.el6.x86_64 3/26
Installing : mysql-community-common-5.6.14-3.el6.x86_64 4/26
Installing : mysql-community-libs-5.6.14-3.el6.x86_64 5/26
Installing : mysql-community-client-5.6.14-3.el6.x86_64 6/26
Updating : 1:qt-sqlite-4.6.2-28.el6_5.x86_64 7/26
Updating : 1:qt-x11-4.6.2-28.el6_5.x86_64 8/26
Updating : 1:qt-devel-4.6.2-28.el6_5.x86_64 9/26
Installing : mysql-community-server-5.6.14-3.el6.x86_64 10/26
Installing : mysql-community-devel-5.6.14-3.el6.x86_64 11/26
Updating : 2:postfix-2.6.6-6.el6_5.x86_64 12/26
Updating : 1:qt-mysql-4.6.2-28.el6_5.x86_64 13/26
Updating : openssl-devel-1.0.1e-16.el6_5.7.x86_64 14/26
Cleanup : 1:qt-devel-4.6.2-25.el6.x86_64 15/26
Cleanup : 2:postfix-2.6.6-2.2.el6_1.x86_64 16/26
Cleanup : 1:qt-mysql-4.6.2-25.el6.x86_64 17/26
Erasing : mysql-devel-5.1.66-2.el6_3.x86_64 18/26
Erasing : mysql-server-5.1.66-2.el6_3.x86_64 19/26
Erasing : mysql-5.1.66-2.el6_3.x86_64 20/26
Cleanup : 1:qt-x11-4.6.2-25.el6.x86_64 21/26
Cleanup : openssl-devel-1.0.0-27.el6.x86_64 22/26
Cleanup : 1:qt-sqlite-4.6.2-25.el6.x86_64 23/26
Cleanup : 1:qt-4.6.2-25.el6.x86_64 24/26
Erasing : mysql-libs-5.1.66-2.el6_3.x86_64 25/26
Cleanup : openssl-1.0.0-27.el6.x86_64 26/26
Verifying : mysql-community-libs-5.6.14-3.el6.x86_64 1/26
Verifying : openssl-devel-1.0.1e-16.el6_5.7.x86_64 2/26
Verifying : 1:qt-x11-4.6.2-28.el6_5.x86_64 3/26
Verifying : 1:qt-devel-4.6.2-28.el6_5.x86_64 4/26
Verifying : openssl-1.0.1e-16.el6_5.7.x86_64 5/26
Verifying : 1:qt-mysql-4.6.2-28.el6_5.x86_64 6/26
Verifying : mysql-community-devel-5.6.14-3.el6.x86_64 7/26
Verifying : 1:qt-4.6.2-28.el6_5.x86_64 8/26
Verifying : 1:qt-sqlite-4.6.2-28.el6_5.x86_64 9/26
Verifying : mysql-community-server-5.6.14-3.el6.x86_64 10/26
Verifying : mysql-community-client-5.6.14-3.el6.x86_64 11/26
Verifying : mysql-community-common-5.6.14-3.el6.x86_64 12/26
Verifying : 2:postfix-2.6.6-6.el6_5.x86_64 13/26
Verifying : mysql-community-libs-compat-5.6.14-3.el6.x86_64 14/26
Verifying : openssl-devel-1.0.0-27.el6.x86_64 15/26
Verifying : mysql-5.1.66-2.el6_3.x86_64 16/26
Verifying : 1:qt-devel-4.6.2-25.el6.x86_64 17/26
Verifying : mysql-server-5.1.66-2.el6_3.x86_64 18/26
Verifying : mysql-devel-5.1.66-2.el6_3.x86_64 19/26
Verifying : 1:qt-x11-4.6.2-25.el6.x86_64 20/26
Verifying : openssl-1.0.0-27.el6.x86_64 21/26
Verifying : 1:qt-mysql-4.6.2-25.el6.x86_64 22/26
Verifying : mysql-libs-5.1.66-2.el6_3.x86_64 23/26
Verifying : mysql-libs-5.1.66-2.el6_3.x86_64 24/26
Verifying : 2:postfix-2.6.6-2.2.el6_1.x86_64 25/26
Verifying : 1:qt-4.6.2-25.el6.x86_64 26/26
Verifying : 1:qt-sqlite-4.6.2-25.el6.x86_64 27/26
Installed:
mysql-community-client.x86_64 0:5.6.14-3.el6 mysql-community-common.x86_64 0:5.6.14-3.el6 mysql-community-devel.x86_64 0:5.6.14-3.el6 mysql-community-libs.x86_64 0:5.6.14-3.el6
mysql-community-server.x86_64 0:5.6.14-3.el6
Dependency Installed:
mysql-community-libs-compat.x86_64 0:5.6.14-3.el6
Dependency Updated:
openssl.x86_64 0:1.0.1e-16.el6_5.7 openssl-devel.x86_64 0:1.0.1e-16.el6_5.7 postfix.x86_64 2:2.6.6-6.el6_5 qt.x86_64 1:4.6.2-28.el6_5 qt-devel.x86_64 1:4.6.2-28.el6_5 qt-mysql.x86_64 1:4.6.2-28.el6_5
qt-sqlite.x86_64 1:4.6.2-28.el6_5 qt-x11.x86_64 1:4.6.2-28.el6_5
Replaced:
mysql.x86_64 0:5.1.66-2.el6_3 mysql-devel.x86_64 0:5.1.66-2.el6_3 mysql-libs.x86_64 0:5.1.66-2.el6_3 mysql-server.x86_64 0:5.1.66-2.el6_3
Complete!
4. インストールの確認
[root@canvaslinux ~]# ls /etc | grep my
my.cnf
[root@canvaslinux ~]# /etc/init.d/mysqld status
mysqld は停止しています
[root@canvaslinux ~]# sudo /etc/init.d/mysqld start
MySQL データベースを初期化中: 2014-04-18 15:38:12 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-04-18 15:38:12 6120 [Note] InnoDB: The InnoDB memory heap is disabled
2014-04-18 15:38:12 6120 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-04-18 15:38:12 6120 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-04-18 15:38:12 6120 [Note] InnoDB: Using Linux native AIO
2014-04-18 15:38:12 6120 [Note] InnoDB: Using CPU crc32 instructions
2014-04-18 15:38:12 6120 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-04-18 15:38:12 6120 [Note] InnoDB: Completed initialization of buffer pool
2014-04-18 15:38:12 6120 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2014-04-18 15:38:12 6120 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2014-04-18 15:38:12 6120 [Note] InnoDB: Database physically writes the file full: wait...
2014-04-18 15:38:12 6120 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2014-04-18 15:38:12 6120 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2014-04-18 15:38:13 6120 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2014-04-18 15:38:13 6120 [Warning] InnoDB: New log files created, LSN=45781
2014-04-18 15:38:13 6120 [Note] InnoDB: Doublewrite buffer not found: creating new
2014-04-18 15:38:13 6120 [Note] InnoDB: Doublewrite buffer created
2014-04-18 15:38:13 6120 [Note] InnoDB: 128 rollback segment(s) are active.
2014-04-18 15:38:13 6120 [Warning] InnoDB: Creating foreign key constraint system tables.
2014-04-18 15:38:13 6120 [Note] InnoDB: Foreign key constraint system tables created
2014-04-18 15:38:13 6120 [Note] InnoDB: Creating tablespace and datafile system tables.
2014-04-18 15:38:13 6120 [Note] InnoDB: Tablespace and datafile system tables created.
2014-04-18 15:38:13 6120 [Note] InnoDB: Waiting for purge to start
2014-04-18 15:38:13 6120 [Note] InnoDB: 5.6.14 started; log sequence number 0
2014-04-18 15:38:14 6120 [Note] Binlog end
2014-04-18 15:38:14 6120 [Note] InnoDB: FTS optimize thread exiting.
2014-04-18 15:38:14 6120 [Note] InnoDB: Starting shutdown...
2014-04-18 15:38:15 6120 [Note] InnoDB: Shutdown completed; log sequence number 1625977
2014-04-18 15:38:15 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-04-18 15:38:15 6145 [Note] InnoDB: The InnoDB memory heap is disabled
2014-04-18 15:38:15 6145 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-04-18 15:38:15 6145 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-04-18 15:38:15 6145 [Note] InnoDB: Using Linux native AIO
2014-04-18 15:38:15 6145 [Note] InnoDB: Using CPU crc32 instructions
2014-04-18 15:38:15 6145 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-04-18 15:38:15 6145 [Note] InnoDB: Completed initialization of buffer pool
2014-04-18 15:38:15 6145 [Note] InnoDB: Highest supported file format is Barracuda.
2014-04-18 15:38:15 6145 [Note] InnoDB: 128 rollback segment(s) are active.
2014-04-18 15:38:15 6145 [Note] InnoDB: Waiting for purge to start
2014-04-18 15:38:15 6145 [Note] InnoDB: 5.6.14 started; log sequence number 1625977
2014-04-18 15:38:15 6145 [Note] Binlog end
2014-04-18 15:38:15 6145 [Note] InnoDB: FTS optimize thread exiting.
2014-04-18 15:38:15 6145 [Note] InnoDB: Starting shutdown...
2014-04-18 15:38:17 6145 [Note] InnoDB: Shutdown completed; log sequence number 1625987
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h canvaslinux password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
The latest information about MySQL is available on the web at
http://www.mysql.com
Please visit
http://bugs.mysql.com/
to report bugs. This database is public and can be browsed
and searched by anyone. If you log in to the system
you can enter new reports.
Note: new default config file not created.
Please make sure your config file is current
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
[ OK ]
mysqld を起動中: [ OK ]
5. MySQLのrootユーザのパスワードを設定
[root@canvaslinux ~]# /usr/bin/mysqladmin -u root password 'mysql' <-- 今回は「mysql」にした
6. ログインできるか確認
[root@canvaslinux ~]# mysql -u root -p
Enter password: <-- 「mysql」と入力
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.14 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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 |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> exit
Bye
7. サンプルデータベースの作成
[root@canvaslinux ~]# sudo mkdir /home/mysql_work <-- 作業用ディレクトリ作成
[root@canvaslinux ~]# sudo chown mysql:mysql /home/mysql_work <-- 所有者変更
[root@canvaslinux ~]# sudo chmod 775 /home/mysql_work <-- パーミッション変更
[root@canvaslinux ~]# cd /home/mysql_work
[root@canvaslinux mysql_work]# sudo wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2 <-- サンプルDBのファイルをダウンロード
--2014-04-18 15:43:50-- https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
launchpad.net をDNSに問いあわせています... 91.189.89.223, 91.189.89.222
launchpad.net|91.189.89.223|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Moved Temporarily
場所: https://launchpadlibrarian.net/24493586/employees_db-full-1.0.6.tar.bz2 [続く]
--2014-04-18 15:43:57-- https://launchpadlibrarian.net/24493586/employees_db-full-1.0.6.tar.bz2
launchpadlibrarian.net をDNSに問いあわせています... 91.189.89.229, 91.189.89.228
launchpadlibrarian.net|91.189.89.229|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 26715056 (25M) [text/plain]
`employees_db-full-1.0.6.tar.bz2' に保存中
100%[==========================================================================================================================================================================>] 26,715,056 1.23M/s 時間 77s
2014-04-18 15:45:20 (339 KB/s) - `employees_db-full-1.0.6.tar.bz2' へ保存完了 [26715056/26715056]
[root@canvaslinux mysql_work]# sudo tar xvf employees_db-full-1.0.6.tar.bz2
employees_db/._load_departments.dump
employees_db/load_departments.dump
employees_db/load_dept_emp.dump
employees_db/._load_dept_manager.dump
employees_db/load_dept_manager.dump
employees_db/._load_employees.dump
employees_db/load_employees.dump
employees_db/._load_salaries.dump
employees_db/load_salaries.dump
employees_db/._load_titles.dump
employees_db/load_titles.dump
employees_db/._employees.sql
employees_db/employees.sql
employees_db/employees_partitioned.sql
employees_db/._employees_partitioned2.sql
employees_db/employees_partitioned2.sql
employees_db/employees_partitioned3.sql
employees_db/objects.sql
employees_db/test_employees_md5.sql
employees_db/test_employees_sha.sql
employees_db/Changelog
employees_db/._README
employees_db/README
[root@canvaslinux mysql_work]# cd employees_db/
[root@canvaslinux employees_db]# sudo vi employees.sql
サンプルデータベースのストレージエンジンの設定を必要に応じて変更する。
※デフォルトでinnodbになっているため、基本的には変更不要
set storage_engine = InnoDB; <-- このままで良い
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;
[root@canvaslinux employees_db]# mysql -u root -p -t < employees.sql
Enter password: <-- rootユーザのパスワード（今回は「mysql」）を入力
+-----------------------------+
| INFO |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO |
+------------------+
| LOADING salaries |
+------------------+
[root@canvaslinux employees_db]# mysql -u root -p
Enter password: <-- mysql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.05 sec)
mysql> use employees;
mysql> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
| titles |
+---------------------+
6 rows in set (0.00 sec)
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.08 sec)
mysql> exit
8. アプリケーション用ユーザ作成
[root@canvaslinux employees_db]# mysql -u root -p
Enter password: <-- mysql
mysql> GRANT ALL ON employees.* TO hr@localhost IDENTIFIED BY "hr"; <-- ユーザ名=hr パスワード=hr
Query OK, 0 rows affected (0.22 sec)
mysql> exit
[root@canvaslinux employees_db]# mysql -u hr -p
Enter password: <-- hr
mysql> use employees;
mysql> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
| titles |
+---------------------+
6 rows in set (0.00 sec)
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.06 sec)
9. 情報
設定ファイル：/etc/my.cnf
データベースディレクトリ：/var/lib/mysql
データファイル：
mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
※ var/lib/mysql/ibdata1 がデータファイルだということ
※デフォルトでは、全DBデータが、単一のファイルに格納される
※単一ファイル管理の場合、ファイルの肥大化の解消のコストが高いため
　テーブル毎にファイルを分割するオプションを使用するのが望ましい
※テーブル毎にファイルを分割したい場合は、「innodb_file_per_table=1」を指定する
　→/etc/my.cnf
データベースのバックアップ（export）
→ mysqldump -u <ユーザ名> -p <DB名> > 出力ファイル名
例：mysqldump --add-drop-table -u hr -p employees > /home/mysql_work/exp_employees.sql
※：「--add-drop-table」を付加すると、出力スクリプトにdrop tableを挿入してくれる
データベースの復元
→ mysql -u <ユーザ名> -p <DB名> < 入力ファイル名
例：mysql -u hr -p employees < /home/mysql_work/exp_employees.sql
※または、drop databaseした上で、employeesデータベースの作成手順を再実行する
パラメータの参照
show variables;
show variables like '%buffer%';
データベース一覧
show database;
テーブル一覧
show tables;
テーブル定義
des <table名>;
desc employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.03 sec)
索引一覧
show indexes from <table名>;
show indexes from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees | 0 | PRIMARY | 1 | emp_no | A | 298936 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
実行計画参照
explain <SELECT文>;
explain select * from employees where emp_no = 10002;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | employees | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment