git ユーザ名、メールアドレスの設定
git ユーザ名、メールアドレスの設定
リポジトリにpushしたときに登録されるユーザ名を設定し忘れて残念な時があるので備忘録がてら。
ユーザ名
git config --global user.name hironomiu
メールアドレス
git config --global user.email hironomiu@hoge.hoge
mysql ScientificLinux 6でremiを使ってMySQL5.5のインストール
mysql remiを使ったインストール
epel remiをインストール
[root@localhost ~]# rpm -ivh http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-5.noarch.rpm http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-5.noarch.rpm を取得中 警告: /var/tmp/rpm-tmp.PadQj9: ヘッダ V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY 準備中... ########################################### [100%] 1:epel-release ########################################### [100%] [root@localhost ~]# rpm -ivh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm http://rpms.famillecollet.com/enterprise/remi-release-6.rpm を取得中 警告: /var/tmp/rpm-tmp.dS6Bzq: ヘッダ V3 DSA/SHA1 Signature, key ID 00f97f56: NOKEY 準備中... ########################################### [100%] 1:remi-release ########################################### [100%]
/etc/yum.repos.d/remi.repoの$releseverを6に置換える
mysql mysql-serverのインストール（remiを指定）
[root@localhost ~]# yum install --enablerepo=remi mysql mysql-server
Loaded plugins: security
epel/metalink | 19 kB 00:00
epel | 4.0 kB 00:00
epel/primary_db | 3.6 MB 00:41
remi | 2.9 kB 00:01
remi/primary_db | 249 kB 00:44
sl | 3.2 kB 00:00
sl-security | 1.9 kB 00:00
sl-security/primary_db | 2.2 MB 02:31
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mysql.i686 0:5.1.61-1.el6_2.1 will be updated
---> Package mysql.i686 0:5.5.23-1.el6.remi will be an update
--> Processing Dependency: mysql-libs(x86-32) = 5.5.23-1.el6.remi for package: mysql-5.5.23-1.el6.remi.i686
---> Package mysql-server.i686 0:5.5.23-1.el6.remi will be installed
--> Processing Dependency: perl-DBI for package: mysql-server-5.5.23-1.el6.remi.i686
--> Processing Dependency: perl-DBD-MySQL for package: mysql-server-5.5.23-1.el6.remi.i686
--> Processing Dependency: perl(DBI) for package: mysql-server-5.5.23-1.el6.remi.i686
--> Processing Dependency: libaio.so.1(LIBAIO_0.4) for package: mysql-server-5.5.23-1.el6.remi.i686
--> Processing Dependency: libaio.so.1(LIBAIO_0.1) for package: mysql-server-5.5.23-1.el6.remi.i686
--> Processing Dependency: libaio.so.1 for package: mysql-server-5.5.23-1.el6.remi.i686
--> Running transaction check
---> Package libaio.i686 0:0.3.107-10.el6 will be installed
---> Package mysql-libs.i686 0:5.1.61-1.el6_2.1 will be updated
--> Processing Dependency: libmysqlclient.so.16 for package: perl-DBD-MySQL-4.013-3.el6.i686
--> Processing Dependency: libmysqlclient.so.16 for package: php-mysql-5.3.3-3.el6_2.6.i686
--> Processing Dependency: libmysqlclient.so.16 for package: 2:postfix-2.6.6-2.2.el6_1.i686
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16) for package: perl-DBD-MySQL-4.013-3.el6.i686
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16) for package: php-mysql-5.3.3-3.el6_2.6.i686
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16) for package: 2:postfix-2.6.6-2.2.el6_1.i686
---> Package mysql-libs.i686 0:5.5.23-1.el6.remi will be an update
---> Package perl-DBD-MySQL.i686 0:4.013-3.el6 will be installed
---> Package perl-DBI.i686 0:1.609-4.el6 will be installed
--> Running transaction check
---> Package compat-mysql51.i686 0:5.1.54-1.el6.remi will be installed
---> Package php-mysql.i686 0:5.3.3-3.el6_2.6 will be updated
---> Package php-mysql.i686 0:5.3.10-2.el6.remi will be an update
--> Processing Dependency: php-pdo(x86-32) = 5.3.10-2.el6.remi for package: php-mysql-5.3.10-2.el6.remi.i686
--> Running transaction check
---> Package php-pdo.i686 0:5.3.3-3.el6_2.6 will be updated
---> Package php-pdo.i686 0:5.3.10-2.el6.remi will be an update
--> Processing Dependency: php-common(x86-32) = 5.3.10-2.el6.remi for package: php-pdo-5.3.10-2.el6.remi.i686
--> Running transaction check
---> Package php-common.i686 0:5.3.3-3.el6_2.6 will be updated
--> Processing Dependency: php-common = 5.3.3-3.el6_2.6 for package: php-zts-5.3.3-3.el6_2.6.i686
--> Processing Dependency: php-common = 5.3.3-3.el6_2.6 for package: php-gd-5.3.3-3.el6_2.6.i686
--> Processing Dependency: php-common = 5.3.3-3.el6_2.6 for package: php-cli-5.3.3-3.el6_2.6.i686
--> Processing Dependency: php-common = 5.3.3-3.el6_2.6 for package: php-5.3.3-3.el6_2.6.i686
--> Processing Dependency: php-common = 5.3.3-3.el6_2.6 for package: php-mbstring-5.3.3-3.el6_2.6.i686
--> Processing Dependency: php-common = 5.3.3-3.el6_2.6 for package: php-ldap-5.3.3-3.el6_2.6.i686
--> Processing Dependency: php-common = 5.3.3-3.el6_2.6 for package: php-xml-5.3.3-3.el6_2.6.i686
---> Package php-common.i686 0:5.3.10-2.el6.remi will be an update
--> Running transaction check
---> Package php.i686 0:5.3.3-3.el6_2.6 will be updated
---> Package php.i686 0:5.3.10-2.el6.remi will be obsoleting
---> Package php-cli.i686 0:5.3.3-3.el6_2.6 will be updated
---> Package php-cli.i686 0:5.3.10-2.el6.remi will be an update
---> Package php-gd.i686 0:5.3.3-3.el6_2.6 will be updated
---> Package php-gd.i686 0:5.3.10-2.el6.remi will be an update
--> Processing Dependency: libt1.so.5 for package: php-gd-5.3.10-2.el6.remi.i686
---> Package php-ldap.i686 0:5.3.3-3.el6_2.6 will be updated
---> Package php-ldap.i686 0:5.3.10-2.el6.remi will be an update
---> Package php-mbstring.i686 0:5.3.3-3.el6_2.6 will be updated
---> Package php-mbstring.i686 0:5.3.10-2.el6.remi will be an update
---> Package php-xml.i686 0:5.3.3-3.el6_2.6 will be updated
---> Package php-xml.i686 0:5.3.10-2.el6.remi will be an update
---> Package php-zts.i686 0:5.3.3-3.el6_2.6 will be obsoleted
--> Running transaction check
---> Package t1lib.i686 0:5.1.2-6.el6_2.1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==========================================================================================================================================================================
Package Arch Version Repository Size
==========================================================================================================================================================================
Installing:
mysql-server i686 5.5.23-1.el6.remi remi 10 M
php i686 5.3.10-2.el6.remi remi 2.3 M
replacing php-zts.i686 5.3.3-3.el6_2.6
Updating:
mysql i686 5.5.23-1.el6.remi remi 5.8 M
Installing for dependencies:
compat-mysql51 i686 5.1.54-1.el6.remi remi 1.4 M
libaio i686 0.3.107-10.el6 sl 20 k
perl-DBD-MySQL i686 4.013-3.el6 sl 133 k
perl-DBI i686 1.609-4.el6 sl 704 k
t1lib i686 5.1.2-6.el6_2.1 sl-security 152 k
Updating for dependencies:
mysql-libs i686 5.5.23-1.el6.remi remi 770 k
php-cli i686 5.3.10-2.el6.remi remi 2.2 M
php-common i686 5.3.10-2.el6.remi remi 854 k
php-gd i686 5.3.10-2.el6.remi remi 130 k
php-ldap i686 5.3.10-2.el6.remi remi 45 k
php-mbstring i686 5.3.10-2.el6.remi remi 892 k
php-mysql i686 5.3.10-2.el6.remi remi 117 k
php-pdo i686 5.3.10-2.el6.remi remi 103 k
php-xml i686 5.3.10-2.el6.remi remi 153 k
Transaction Summary
==========================================================================================================================================================================
Install 7 Package(s)
Upgrade 10 Package(s)
Total download size: 26 M
Is this ok [y/N]: y
Downloading Packages:
(1/17): compat-mysql51-5.1.54-1.el6.remi.i686.rpm | 1.4 MB 02:40
(2/17): libaio-0.3.107-10.el6.i686.rpm | 20 kB 00:01
(3/17): mysql-5.5.23-1.el6.remi.i686.rpm | 5.8 MB 14:59
(4/17): mysql-libs-5.5.23-1.el6.remi.i686.rpm | 770 kB 02:16
(5/17): mysql-server-5.5.23-1.el6.remi.i686.rpm | 10 MB 24:53
(6/17): perl-DBD-MySQL-4.013-3.el6.i686.rpm | 133 kB 00:29
(7/17): perl-DBI-1.609-4.el6.i686.rpm | 704 kB 01:34
(8/17): php-5.3.10-2.el6.remi.i686.rpm | 2.3 MB 06:18
(9/17): php-cli-5.3.10-2.el6.remi.i686.rpm | 2.2 MB 05:22
(10/17): php-common-5.3.10-2.el6.remi.i686.rpm | 854 kB 02:16
(11/17): php-gd-5.3.10-2.el6.remi.i686.rpm | 130 kB 00:11
(12/17): php-ldap-5.3.10-2.el6.remi.i686.rpm | 45 kB 00:01
(13/17): php-mbstring-5.3.10-2.el6.remi.i686.rpm | 892 kB 03:12
(14/17): php-mysql-5.3.10-2.el6.remi.i686.rpm | 117 kB 00:12
(15/17): php-pdo-5.3.10-2.el6.remi.i686.rpm | 103 kB 00:34
(16/17): php-xml-5.3.10-2.el6.remi.i686.rpm | 153 kB 00:27
(17/17): t1lib-5.1.2-6.el6_2.1.i686.rpm | 152 kB 00:33
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 6.7 kB/s | 26 MB 66:36
warning: rpmts_HdrFromFdno: Header V3 DSA/SHA1 Signature, key ID 00f97f56: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-remi
Importing GPG key 0x00F97F56:
Userid : Remi Collet <RPMS@FamilleCollet.com>
Package: remi-release-6-1.el6.remi.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-remi
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
Updating : php-common-5.3.10-2.el6.remi.i686 1/29
WARNING : These php-* RPM are not official Fedora/Redhat build and
overrides the official ones. Don't file bugs on Fedora Project nor Redhat.
Use dedicated forums http://forums.famillecollet.com/
Updating : mysql-libs-5.5.23-1.el6.remi.i686 2/29
WARNING : This MySQL RPM is not an official Fedora/Redhat build and it
overrides the official one. Don't file bugs on Fedora Project nor Redhat.
Use dedicated forums http://forums.famillecollet.com/
Installing : perl-DBI-1.609-4.el6.i686 3/29
Updating : php-pdo-5.3.10-2.el6.remi.i686 4/29
Updating : php-cli-5.3.10-2.el6.remi.i686 5/29
Updating : mysql-5.5.23-1.el6.remi.i686 6/29
Installing : libaio-0.3.107-10.el6.i686 7/29
Installing : compat-mysql51-5.1.54-1.el6.remi.i686 8/29
Installing : perl-DBD-MySQL-4.013-3.el6.i686 9/29
Installing : t1lib-5.1.2-6.el6_2.1.i686 10/29
Updating : php-gd-5.3.10-2.el6.remi.i686 11/29
Installing : php-5.3.10-2.el6.remi.i686 12/29
Updating : php-mysql-5.3.10-2.el6.remi.i686 13/29
Updating : php-xml-5.3.10-2.el6.remi.i686 14/29
Updating : php-mbstring-5.3.10-2.el6.remi.i686 15/29
Updating : php-ldap-5.3.10-2.el6.remi.i686 16/29
Installing : mysql-server-5.5.23-1.el6.remi.i686 17/29
Cleanup : php-mysql-5.3.3-3.el6_2.6.i686 18/29
Cleanup : php-5.3.3-3.el6_2.6.i686 19/29
Cleanup : php-cli-5.3.3-3.el6_2.6.i686 20/29
Cleanup : php-pdo-5.3.3-3.el6_2.6.i686 21/29
Cleanup : mysql-5.1.61-1.el6_2.1.i686 22/29
Cleanup : php-ldap-5.3.3-3.el6_2.6.i686 23/29
Cleanup : php-mbstring-5.3.3-3.el6_2.6.i686 24/29
Cleanup : php-xml-5.3.3-3.el6_2.6.i686 25/29
Erasing : php-zts-5.3.3-3.el6_2.6.i686 26/29
Cleanup : php-gd-5.3.3-3.el6_2.6.i686 27/29
Cleanup : php-common-5.3.3-3.el6_2.6.i686 28/29
Cleanup : mysql-libs-5.1.61-1.el6_2.1.i686 29/29
Installed:
mysql-server.i686 0:5.5.23-1.el6.remi php.i686 0:5.3.10-2.el6.remi
Dependency Installed:
compat-mysql51.i686 0:5.1.54-1.el6.remi libaio.i686 0:0.3.107-10.el6 perl-DBD-MySQL.i686 0:4.013-3.el6 perl-DBI.i686 0:1.609-4.el6 t1lib.i686 0:5.1.2-6.el6_2.1
Updated:
mysql.i686 0:5.5.23-1.el6.remi
Dependency Updated:
mysql-libs.i686 0:5.5.23-1.el6.remi php-cli.i686 0:5.3.10-2.el6.remi php-common.i686 0:5.3.10-2.el6.remi php-gd.i686 0:5.3.10-2.el6.remi
php-ldap.i686 0:5.3.10-2.el6.remi php-mbstring.i686 0:5.3.10-2.el6.remi php-mysql.i686 0:5.3.10-2.el6.remi php-pdo.i686 0:5.3.10-2.el6.remi
php-xml.i686 0:5.3.10-2.el6.remi
Replaced:
php-zts.i686 0:5.3.3-3.el6_2.6
Complete!
[root@localhost ~]#
mysql_install_dbの実行
/var/lib/mysqlで作成（ディレクトリが無い場合は755、mysql:mysqlで作成）。mysqlユーザが存在しない場合はあわせて作成。
[root@localhost lib]# su - mysql -bash-4.1$ mysql_install_db --datadir=/var/lib/mysql Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system 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 localhost.localdomain 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. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /usr/mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/bin/mysqlbug script! -bash-4.1$ exit logout
mysqlの起動
chkconfigで起動設定とかも好みで。
[root@localhost lib]# service mysqld start mysqld を起動中: [ OK ] [root@localhost lib]# [root@localhost lib]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.23 MySQL Community Server (GPL) by Remi Copyright (c) 2000, 2011, 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 ユーザ定義関数を使ってFizzBuzzをSQLで書いてみる
mysql ユーザ定義関数を使ってFizzBuzzをSQLで書いてみる
社内のskypeグループでSQLでFizzBuzzが話題になったときに書いてみたSQL。
（ただ単にユーザ定義関数を使いたかっただけだったりします。。。）
SQL
create table digit(n int); insert into digit value(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); set @var:=0, @var3:=3 ,@var5:=5; select if(fizz is null,if(buzz is null,var,buzz),if(buzz is null,fizz,concat(fizz,buzz))) as '?' from ( select @var3:=@var3-1 , @var5:=@var5-1 , @var:=@var+1 as var, @fizz:=if (@var3=0,"fizz",null) as fizz, @buzz:=if (@var5=0,"buzz",null) as buzz, if (@var3=0,@var3:=3,null), if (@var5=0,@var5:=5,null) from( select d1.n + d2.n * 10 + 1 n from digit d1, digit d2 ) d ) a ;
いざ動かしてみると。
mysql> set @var:=0, @var3:=3 ,@var5:=5;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select if(fizz is null,if(buzz is null,var,buzz),if(buzz is null,fizz,concat(fizz,buzz))) as '?' from (
-> select @var3:=@var3-1 , @var5:=@var5-1 , @var:=@var+1 as var,
-> @fizz:=if (@var3=0,"fizz",null) as fizz,
-> @buzz:=if (@var5=0,"buzz",null) as buzz,
-> if (@var3=0,@var3:=3,null),
-> if (@var5=0,@var5:=5,null)
-> from(
-> select d1.n + d2.n * 10 + 1 n
-> from digit d1, digit d2
-> ) d ) a
-> ;
+----------+
| ? |
+----------+
| 1 |
| 2 |
| fizz |
| 4 |
| buzz |
| fizz |
| 7 |
| 8 |
| fizz |
| buzz |
| 11 |
| fizz |
| 13 |
| 14 |
| fizzbuzz |
| 16 |
| 17 |
| fizz |
| 19 |
| buzz |
| fizz |
| 22 |
| 23 |
| fizz |
| buzz |
| 26 |
| fizz |
| 28 |
| 29 |
| fizzbuzz |
| 31 |
| 32 |
| fizz |
| 34 |
| buzz |
| fizz |
| 37 |
| 38 |
| fizz |
| buzz |
| 41 |
| fizz |
| 43 |
| 44 |
| fizzbuzz |
| 46 |
| 47 |
| fizz |
| 49 |
| buzz |
| fizz |
| 52 |
| 53 |
| fizz |
| buzz |
| 56 |
| fizz |
| 58 |
| 59 |
| fizzbuzz |
| 61 |
| 62 |
| fizz |
| 64 |
| buzz |
| fizz |
| 67 |
| 68 |
| fizz |
| buzz |
| 71 |
| fizz |
| 73 |
| 74 |
| fizzbuzz |
| 76 |
| 77 |
| fizz |
| 79 |
| buzz |
| fizz |
| 82 |
| 83 |
| fizz |
| buzz |
| 86 |
| fizz |
| 88 |
| 89 |
| fizzbuzz |
| 91 |
| 92 |
| fizz |
| 94 |
| buzz |
| fizz |
| 97 |
| 98 |
| fizz |
| buzz |
+----------+
100 rows in set (0.00 sec)
mysql 5.6新機能slave_parallel_workersを設定する際の注意
mysql 5.6新機能slave_parallel_workersを設定する際の注意
Multi-threaded Slavesを使ってみようと設定したときにはまったのでそこを記載。
slaveのmy.cnfに下記を追加
relay_log_info_repository = TABLE master_info_repository = TABLE slave_checkpoint_group = 524281 slave_parallel_workers = 4
slaveを再起動するとエラーログに下記のエラーが出る
120331 12:18:52 [ERROR] Failed to open the relay log './inst2-5-4-6_1-relay-bin.000050' (relay_log_pos 275). 120331 12:18:52 [ERROR] Could not find target log during relay log initialization. 120331 12:18:52 [ERROR] Failed to initialize the master info structure
slave status
Slave_SQL_RunningがNoになりエラーメッセージでFailed during slave workers initializationが。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: replicator
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: inst1-5-6-4_1-bin.000005
Read_Master_Log_Pos: 610703806
Relay_Log_File: inst2-5-4-6_1-relay-bin.000026
Relay_Log_Pos: 275
Relay_Master_Log_File: inst1-5-6-4_1-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: db1,db2,db3,db4
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1593
Last_Error: Failed during slave workers initialization
Skip_Counter: 0
Exec_Master_Log_Pos: 610703806
Relay_Log_Space: 1110
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1593
Last_SQL_Error: Failed during slave workers initialization
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3307
Master_UUID: f1ef071a-7630-11e1-bae5-00226862863d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 120325 22:53:45
Master_SSL_Crl:
Master_SSL_Crlpath:
1 row in set (0.00 sec)
relay-log.infoの削除
[Bug #64026]
[bzr push into mysql-trunk branch]
色々エラーメッセージを便りに調べてみたけどイマイチわからない。
Bug #64026の内容からもしかしてrelay_log_info_repository = TABLEにしたら
relay-log.infoっていらない？と思いslaveを停止（service mysql stop）し下のrelay-log.infoを削除（rm relay-log.info.*）
-rw-rw---- 1 mysql mysql 81 3月 25 22:56 relay-log.info.0 -rw-rw---- 1 mysql mysql 81 3月 25 22:56 relay-log.info.1 -rw-rw---- 1 mysql mysql 225 3月 31 10:41 relay-log.info.2 -rw-rw---- 1 mysql mysql 227 3月 31 10:43 relay-log.info.3
＊ slaveを起動
無事エラーなくslave_parallel_workers用のプロセスも無事起動された。
注：Checkpoint_group_bitmapだけ削除してます。
mysql> select * from mysql.slave_worker_info\G
*************************** 1. row ***************************
Master_id: 3308
Worker_id: 0
Relay_log_name: ./inst2-5-6-4_1-relay-bin.000016
Relay_log_pos: 180094449
Master_log_name: inst1-5-6-4_1-bin.000006
Master_log_pos: 681610195
Checkpoint_relay_log_name: ./inst2-5-6-4_1-relay-bin.000016
Checkpoint_relay_log_pos: 180094138
Checkpoint_master_log_name: inst1-5-6-4_1-bin.000006
Checkpoint_master_log_pos: 681609884
Checkpoint_seqno: 0
Checkpoint_group_size: 65535
*************************** 2. row ***************************
Master_id: 3308
Worker_id: 1
Relay_log_name: ./inst2-5-6-4_1-relay-bin.000014
Relay_log_pos: 334663893
Master_log_name: inst1-5-6-4_1-bin.000006
Master_log_pos: 501515938
Checkpoint_relay_log_name: ./inst2-5-6-4_1-relay-bin.000014
Checkpoint_relay_log_pos: 334568336
Checkpoint_master_log_name: inst1-5-6-4_1-bin.000006
Checkpoint_master_log_pos: 501420381
Checkpoint_seqno: 510
Checkpoint_group_size: 65535
*************************** 3. row ***************************
Master_id: 3308
Worker_id: 2
Relay_log_name: ./inst2-5-6-4_1-relay-bin.000016
Relay_log_pos: 180094138
Master_log_name: inst1-5-6-4_1-bin.000006
Master_log_pos: 681609884
Checkpoint_relay_log_name: ./inst2-5-6-4_1-relay-bin.000016
Checkpoint_relay_log_pos: 178505386
Checkpoint_master_log_name: inst1-5-6-4_1-bin.000006
Checkpoint_master_log_pos: 680021132
Checkpoint_seqno: 8495
Checkpoint_group_size: 65535
*************************** 4. row ***************************
Master_id: 3308
Worker_id: 3
Relay_log_name: ./inst2-5-6-4_1-relay-bin.000016
Relay_log_pos: 195502175
Master_log_name: inst1-5-6-4_1-bin.000006
Master_log_pos: 697017921
Checkpoint_relay_log_name: ./inst2-5-6-4_1-relay-bin.000016
Checkpoint_relay_log_pos: 195501801
Checkpoint_master_log_name: inst1-5-6-4_1-bin.000006
Checkpoint_master_log_pos: 697017547
Checkpoint_seqno: 1
Checkpoint_group_size: 65535
4 rows in set (0.00 sec
slave status
こちらもSlave_SQL_RunningがYESとなりエラーなく稼働
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: replicator
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: inst1-5-6-4_1-bin.000006
Read_Master_Log_Pos: 884314089
Relay_Log_File: inst2-5-6-4_1-relay-bin.000016
Relay_Log_Pos: 382796286
Relay_Master_Log_File: inst1-5-6-4_1-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1,db2,db3,db4
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 884312032
Relay_Log_Space: 382798514
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3307
Master_UUID: f1ef071a-7630-11e1-bae5-00226862863d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
1 row in set (0.00 sec)
Oracle CentOS5 rlwrapを利用したsqlplusのコマンド履歴機能
Oracle CentOS5 rlwrapを利用したsqlplusのコマンド履歴機能
centos上のOracleにローカルからsqlplusでSQLを実行してるとコマンド履歴がないのでちょっと苦労する。
そこでrlwrapを使ってこれを補ってみる。今回はrpm版のrlwrapを使用する。
rlwrap-0.37-1.el5.x86_64.rpmの取得（2012/03/29時点の最新版）
$ wget ftp://195.220.108.108/linux/epel/5/x86_64/rlwrap-0.37-1.el5.x86_64.rpm
--2012-03-30 15:23:09-- ftp://195.220.108.108/linux/epel/5/x86_64/rlwrap-0.37-1.el5.x86_64.rpm
=> `rlwrap-0.37-1.el5.x86_64.rpm'
195.220.108.108:21 に接続しています... 接続しました。
anonymous としてログインしています... ログインしました!
==> SYST ... 完了しました。 ==> PWD ... 完了しました。
==> TYPE I ... 完了しました。 ==> CWD /linux/epel/5/x86_64 ... 完了しました。
==> SIZE rlwrap-0.37-1.el5.x86_64.rpm ... 94353
==> PASV ... 完了しました。 ==> RETR rlwrap-0.37-1.el5.x86_64.rpm ... 完了しました。
長さ: 94353 (92K)
100%[======================================================================================>] 94,353 76.5K/s 時間 1.2s
2012-03-30 15:23:14 (76.5 KB/s) - `rlwrap-0.37-1.el5.x86_64.rpm' へ保存終了 [94353]
rmpパッケージをインストール
$ sudo rpm -ivh rlwrap-0.37-1.el5.x86_64.rpm 警告: rlwrap-0.37-1.el5.x86_64.rpm: ヘッダ V3 DSA signature: NOKEY, key ID 217521f6 準備中... ########################################### [100%] 1:rlwrap ########################################### [100%]
動作確認
oracleユーザで適当にSQLを実行。sqlplusを呼び出す際にrlwrap sqlplus ～の形で呼び出す。
今回は「select count(*) from v$session;」を2度目はctrl+pで履歴から選択して実行した。
[oracle]$ rlwrap sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 30 15:24:14 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select count(*) from v$session;
COUNT(*)
----------
25
SQL> select count(*) from v$session;
COUNT(*)
----------
23
ctrl+a、ctrl+eなどの行内の移動とかも普通に使えるので楽チン。
mysql mysqldumpのオプション設定のtips
mysql mysqldumpのオプション設定のtips
マニュアルにも記載されているけど結構見落としがちなのでtipsとして。
MySQL 5.1 リファレンスマニュアル mysqldump
オプションで--skip～を設定してるのに何故か設定どおりに動いてくれないって思うことがありませんか？
大体の理由はマニュアル中のこれが原因だったりします。
グループオプションの一部を選択して効果を有効化･無効化する場合、オプションは前から後ろへの順で処理されるため、記述する順番が重要になります。
例えば--skip-set-charsetを設定した場合（検証を5.0で行っているので--optを明示的に設定しています。）
mysqldump --skip-set-charset --opt -t hoge hoge > hoge.dmp のダンプ内容。「SET NAMES」までが出力されてしまってます。
-- MySQL dump 10.11 -- -- Host: localhost Database: hoge -- ------------------------------------------------------ -- Server version 5.0.67-community-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump --opt -t hoge hoge --skip-set-charset > hoge.dmp のダンプ内容。意図したとおり「SET NAMES」までが省略されています。
-- MySQL dump 10.11 -- -- Host: localhost Database: hoge -- ------------------------------------------------------ -- Server version 5.0.67-community-log /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
順番によりパラメータ値が決定されるのは知っておくと無用なトラブルにならないので知っておくと便利です。
mysql バージョン5.6のexplain
mysql バージョン5.6のexplain
mysql5.6（正確には5.6.3）からselect文以外のDMLでもexplainが使えるように変更されたので試してみた。
データの確認
こんなデータを使ってみる。
mysql> select * from hogetb; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | 10 | 10 | | 2 | 10 | 10 | | 3 | 10 | 10 | +----+------+------+ 3 rows in set (0.01 sec)
select文のexplain
これは今まで通り。
mysql> explain select * from hogetb\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: hogetb
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra:
1 row in set (0.00 sec)
mysql> select * from hogetb;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | 10 | 10 |
| 2 | 10 | 10 |
| 3 | 10 | 10 |
+----+------+------+
3 rows in set (0.00 sec)
delete文のexplain
全件削除、PK指定の削除ともに実行計画が出てますね。当然データは変更されていません。
mysql> explain delete from hogetb\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Deleting all rows
1 row in set (0.00 sec)
mysql> explain delete from hogetb where id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: hogetb
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
mysql> select * from hogetb;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | 10 | 10 |
| 2 | 10 | 10 |
| 3 | 10 | 10 |
+----+------+------+
3 rows in set (0.00 sec)
update文のexplain
こちらも全件削除、PK指定の削除ともに実行計画が出てますね。当然データは変更されていません。
mysql> explain update hogetb set col1 = 20\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: hogetb
type: ALL
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
Extra:
1 row in set (0.00 sec)
mysql> explain update hogetb set col1 = 20 where id = 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: hogetb
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.01 sec)
mysql> select * from hogetb;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | 10 | 10 |
| 2 | 10 | 10 |
| 3 | 10 | 10 |
+----+------+------+
3 rows in set (0.00 sec)
insert文のexplain
こちらも出来てます。当然データは変更されていません。
mysql> explain insert into hogetb values (null,50,50)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used
1 row in set (0.00 sec)
mysql> select * from hogetb;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | 10 | 10 |
| 2 | 10 | 10 |
| 3 | 10 | 10 |
+----+------+------+
3 rows in set (0.00 sec)
実行計画の作成アルゴリズムとか深いところも気になりますね。
