在原有3306端口mysqld服务的情况再搭建第二个3308端口的mysql实例

1 download the tar.gz
[root@472322 tmp]# wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.13.tar.gz/from/http://cdn.mysql.com/
--2013-09-11 22:04:52-- http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.13.tar.gz/from/http://cdn.mysql.com/
Resolving dev.mysql.com... 137.254.60.11
Connecting to dev.mysql.com|137.254.60.11|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.13.tar.gz [following]
--2013-09-11 22:04:52-- http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.13.tar.gz
Resolving cdn.mysql.com... 23.3.96.242, 23.3.96.235
Connecting to cdn.mysql.com|23.3.96.242|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 35951160 (34M) [application/x-tar-gz]
Saving to: 鈥渕ysql-5.6.13.tar.gz鈥

100%[=============================================================================================================================>] 35,951,160 1.57M/s in 28s

2013-09-11 22:05:21 (1.21 MB/s) - 鈥渕ysql-5.6.13.tar.gz鈥saved [35951160/35951160]


2 安装cmake软件包
yum install cmake


3 create account of mysql
groupadd mysql
useradd -g mysql mysql
autoreconf --force --install
libtoolize --automake --force
automake --force --add-missing

4 complie the sources
mkdir -p /data56m1/m1
tar -xvf mysql-5.6.13.tar.gz

5 cmake
mkdir -p /usr/local/mysql56m1
mkdir -p /data56m1
time cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql56m1 -DMYSQL_DATADIR=/data56m1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR==/data56m1/mysql.sock -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
PS: 5.6.13源码安装是默认带federated引擎的,不过要生效,需要在my.cnf里面的mysqld选项里面加上启动参数federated。

[root@472322 mysql-5.6.13]# time cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql56m1 -DMYSQL_DATADIR=/data56m1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR==/data56m1/mysql.sock -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
.........
-- Looking for sched_getcpu - found
-- Performing Test HAVE_IB_GCC_ATOMIC_BUILTINS
-- Performing Test HAVE_IB_GCC_ATOMIC_BUILTINS - Success
-- Performing Test HAVE_IB_GCC_ATOMIC_BUILTINS_64
-- Performing Test HAVE_IB_GCC_ATOMIC_BUILTINS_64 - Success
-- Performing Test HAVE_IB_ATOMIC_PTHREAD_T_GCC
-- Performing Test HAVE_IB_ATOMIC_PTHREAD_T_GCC - Success
-- Looking for asprintf
-- Looking for asprintf - found
-- Check size of pthread_t
-- Check size of pthread_t - done
-- Using cmake version 2.6.4
-- Not building NDB
-- Performing Test HAVE_PEERCRED
-- Performing Test HAVE_PEERCRED - Success
-- Library mysqlclient depends on OSLIBS -lpthread;m;rt;dl
-- Googlemock was not found. gtest-based unit tests will be disabled. You can run cmake . -DENABLE_DOWNLOADS=1 to automatically download and build required components from source.
-- If you are inside a firewall, you may need to use an http proxy: export http_proxy=http://foo.bar.com:80
-- Library mysqlserver depends on OSLIBS -lpthread;m;rt;crypt;dl
-- Configuring done
-- Generating done
-- Build files have been written to: /tmp/mysql-5.6.13

real 0m24.507s
user 0m13.984s
sys 0m7.372s
[root@472322 mysql-5.6.13]#


6 build the db directy
[root@472322 mysql-5.6.13]# time make
Scanning dependencies of target INFO_BIN
[ 0%] Built target INFO_BIN
Scanning dependencies of target INFO_SRC
[ 0%] Built target INFO_SRC
Scanning dependencies of target abi_check
[ 0%] Built target abi_check
Scanning dependencies of target zlib
[ 0%] Building C object zlib/CMakeFiles/zlib.dir/adler32.c.o
[ 0%] Building C object zlib/CMakeFiles/zlib.dir/compress.c.o
[ 0%] Building C object zlib/CMakeFiles/zlib.dir/crc32.c.o
[ 0%] Building C object zlib/CMakeFiles/zlib.dir/deflate.c.o
......
Linking CXX static library libsql_embedded.a
[ 98%] Built target sql_embedded
[100%] Generating mysqlserver_depends.c
Scanning dependencies of target mysqlserver
[100%] Building C object libmysqld/CMakeFiles/mysqlserver.dir/mysqlserver_depends.c.o
Linking C static library libmysqld.a
/usr/bin/ar: creating /tmp/mysql-5.6.13/libmysqld/libmysqld.a
[100%] Built target mysqlserver
Scanning dependencies of target mysql_client_test_embedded
[100%] Building C object libmysqld/examples/CMakeFiles/mysql_client_test_embedded.dir/__/__/tests/mysql_client_test.c.o
Linking CXX executable mysql_client_test_embedded
[100%] Built target mysql_client_test_embedded
Scanning dependencies of target mysql_embedded
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir/__/__/client/completion_hash.cc.o
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir/__/__/client/mysql.cc.o
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir/__/__/client/readline.cc.o
Linking CXX executable mysql_embedded
[100%] Built target mysql_embedded
Scanning dependencies of target mysqltest_embedded
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysqltest_embedded.dir/__/__/client/mysqltest.cc.o
Linking CXX executable mysqltest_embedded
[100%] Built target mysqltest_embedded
Scanning dependencies of target my_safe_process
[100%] Building CXX object mysql-test/lib/My/SafeProcess/CMakeFiles/my_safe_process.dir/safe_process.cc.o
Linking CXX executable my_safe_process

[100%] Built target my_safe_process

real 18m11.448s
user 16m23.402s
sys 1m31.842s

[root@472322 mysql-5.6.13]# time make install
......
-- Installing: /usr/local/mysql56m1/man/man1/mysqldump.1
-- Installing: /usr/local/mysql56m1/man/man1/mysql_config_editor.1
-- Installing: /usr/local/mysql56m1/man/man1/myisamlog.1
-- Installing: /usr/local/mysql56m1/man/man1/mysql-stress-test.pl.1
-- Installing: /usr/local/mysql56m1/man/man8/mysqld.8
-- Installing: /usr/local/mysql56m1/support-files/solaris/postinstall-solaris


real 0m22.927s
user 0m2.963s
sys 0m3.353s


7 init db & config my56m1.cnf
cd /usr/local/mysql56m1
chown -R mysql .
chgrp -R mysql .
cp support-files/my-default.cnf /etc/my56m1.cnf
vim /etc/my56m1.cnf

[client]
#password    = your_password
port         = 3308
socket       = /data56m1/mysql3308.sock


[mysqld]
port         = 3308
socket       = /data56m1/mysql3308.sock


federated
log_bin             = /data56m1/mysql-bin.log
log_bin_index       = /data56m1/mysql-bin.log.index
relay-log           = /data56m1/mysql-relay-bin
relay-log-index     = /data56m1/mysql-relay-bin.index


skip-external-locking
bind-address = 0.0.0.0


log_bin_trust_function_creators=1
binlog_format=mixed
log_slave_updates=1
innodb_flush_log_at_trx_commit = 0
log-error = /data56m1/error.err


# These are commonly set, remove the # and set as required.sedir = /usr/local/mysql56m1
basedir = /usr/local/mysql56m1
datadir = /data56m1
log_error = /data56m1/error.err
innodb_file_per_table
server_id = 11


innodb_data_home_dir = /data56m1
innodb_data_file_path = ibdata01:1G:autoextend


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

8 init db
[root@472322 mysql56m1]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql56m1 --datadir=/data56m1 --defaults-file=/etc/my56m1.cnf
Installing MySQL system tables...2013-09-12 01:21:09 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OK


Filling help tables...2013-09-12 01:21:12 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
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/local/mysql56m1/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql56m1/bin/mysqladmin -u root -h 472322.com password 'new-password'

Alternatively you can run:
/usr/local/mysql56m1/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/local/mysql56m1/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems with the ./bin/mysqlbug script!
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
New default config file was created as /usr/local/mysql56m1/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings

9 copy start command
cp support-files/mysql.server /etc/init.d/mysqld56m1
chmod 700 /etc/init.d/mysqld56m1
echo "export PATH=$PATH:/usr/local/mysqld56m1/bin">>/etc/profile
source /etc/profile

10 add command to system parameter
cd /data56m1
chkconfig --add mysqld56m1

11 start service
service mysqld56m1 start
[root@472322 data56m1]# service mysqld56m1 start
Starting MySQL.. ERROR! The server quit without updating PID file (/data56m1/472322.com.pid).

11.1 check data directory
[root@472322 data56m1]# ll
total 1160456
-rw-r----- 1 mysql root 5037 Sep 12 03:07 472322.com.err
-rw-rw---- 1 mysql mysql 56 Sep 12 02:58 auto.cnf
-rw-rw---- 1 mysql mysql 1073741824 Sep 12 03:02 ibdata01
-rw-rw---- 1 mysql mysql 12582912 Sep 12 03:07 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Sep 12 03:07 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Sep 12 03:02 ib_logfile1
drwx------ 2 mysql mysql 4096 Sep 12 02:56 mysql
-rw-rw---- 1 mysql mysql 67100 Sep 12 02:56 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1211196 Sep 12 02:56 mysql-bin.000002
-rw-rw---- 1 mysql mysql 143 Sep 12 03:02 mysql-bin.000003
-rw-rw---- 1 mysql mysql 81 Sep 12 02:58 mysql-bin.log.index
drwx------ 2 mysql mysql 4096 Sep 12 02:56 performance_schema
drwx------ 2 mysql mysql 4096 Sep 12 02:56 test

奇怪配置文件里面的error.err并没有出现，而是产生了一个472322.com.err的文件，那么十有八九就是/etc/my56m1.cnf没有起作用，否则，不可能没有error.err的而是默认的472322.com.err 日志

我在第8步的时候执行init db的时候，命令中指定了 scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql56m1 --datadir=/data56m1 --defaults-file=/etc/my56m1.cnf 配置文件是/etc/my56m1.cnf啊

再去看第8步执行的日志信息：

看到如下信息：
New default config file was created as /usr/local/mysql56m1/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings
好像是说没有找到默认的basedir根目录下的my.cnf所以就自动创建了一个根目录的my.cnf。难道mysqld服务启动的时候没有识别--defaults-file参数？




11.2 check /etc/init.d/mysqld56m1
打开/etc/init.d/mysqld56m1启动文件看看


extra_args=""
if test -r "$basedir/my.cnf"
then
extra_args="-e $basedir/my.cnf"
else
if test -r "$datadir/my.cnf"
then
extra_args="-e $datadir/my.cnf"
fi
fi


...


# Get arguments from the my.cnf file,
# the only group, which is read from now on is [mysqld]
if test -x ./bin/my_print_defaults
then
print_defaults="./bin/my_print_defaults"
elif test -x $bindir/my_print_defaults
then
print_defaults="$bindir/my_print_defaults"
elif test -x $bindir/mysql_print_defaults
then
print_defaults="$bindir/mysql_print_defaults"
else
# Try to find basedir in /etc/my.cnf
conf=/etc/my.cnf
print_defaults=
if test -r $conf
then
subpat='^[^=]*basedir[^=]*=\(.*\)$'
dirs=`sed -e "/$subpat/!d" -e 's//\1/' $conf`
for d in $dirs
do
d=`echo $d | sed -e 's/[ ]//g'`
if test -x "$d/bin/my_print_defaults"
then
print_defaults="$d/bin/my_print_defaults"
break
fi
if test -x "$d/bin/mysql_print_defaults"
then
print_defaults="$d/bin/mysql_print_defaults"
break
fi
done
fi


# Hope it's in the PATH ... but I doubt it
test -z "$print_defaults" && print_defaults="my_print_defaults"
fi
...
看到都指向了my.cnf，先去找/etc/my.cnf，如果找不到，再找$basedir/my.cnf，为什么我init db的时候defaults-file设置无效呢？


12 copy config file to basedir directory
我尝试做了操作，把/etc/my56m1.cnf copy到$basedir/my.cnf
[root@472322 data56m1]# cp /etc/my56m1.cnf /usr/local/mysql56m1/my.cnf
[root@472322 data56m1]#


[root@472322 data56m1]# service mysqld56m1 start
Starting MySQL. SUCCESS!
Ok成功了。

13 check the error log
[root@472322 data56m1]# ll
total 1160480
-rw-r----- 1 mysql root 5037 Sep 12 03:07 472322.com.err
-rw-rw---- 1 mysql mysql 6 Sep 12 03:21 472322.com.pid
-rw-rw---- 1 mysql mysql 56 Sep 12 02:58 auto.cnf
-rw-rw---- 1 mysql mysql 12417 Sep 12 03:21 error.err
-rw-rw---- 1 mysql mysql 1073741824 Sep 12 03:22 ibdata01
-rw-rw---- 1 mysql mysql 12582912 Sep 12 03:07 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Sep 12 03:22 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Sep 12 03:02 ib_logfile1
drwx------ 2 mysql mysql 4096 Sep 12 02:56 mysql
srwxrwxrwx 1 mysql mysql 0 Sep 12 03:21 mysql3308.sock
-rw-rw---- 1 mysql mysql 67100 Sep 12 02:56 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1211196 Sep 12 02:56 mysql-bin.000002
-rw-rw---- 1 mysql mysql 143 Sep 12 03:02 mysql-bin.000003
-rw-rw---- 1 mysql mysql 120 Sep 12 03:21 mysql-bin.000004
-rw-rw---- 1 mysql mysql 108 Sep 12 03:21 mysql-bin.log.index
drwx------ 2 mysql mysql 4096 Sep 12 02:56 performance_schema
drwx------ 2 mysql mysql 4096 Sep 12 02:56 test
[root@472322 data56m1]#
OK，看到error.err生成了，起效果了！

14 check login
[root@472322 data56m1]# mysql -P3308 -s /data56m1/mysql3308.sock
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@472322 data56m1]# mysql -P3308 -S /data56m1/mysql3308.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.13-log Source distribution
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)


mysql> select @@port;
+--------+
| @@port |
+--------+
| 3308 |
+--------+
1 row in set (0.00 sec)
mysql>

疑问所在： 为什么我init db的时候--defaults-file=/etc/my56m1.cnf设置无效呢？