hironomiu's Blog

こそっと書いていきます。twitter_id:hironomiu

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 ユーザ定義関数を使ってFizzBuzzSQLで書いてみる

社内のskypeグループでSQLFizzBuzzが話題になったときに書いてみた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)

実行計画の作成アルゴリズムとか深いところも気になりますね。