一台伺服器上運行2個MySQL服務

一台伺服器上運行2個MySQL服務

一台伺服器上運行2個MySQL服務

一.添加組添，加使用者到組，解壓

groupadd mysql

useradd mysql -g mysql -M -s /sbin/nologin

tar -zxvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz

tar -zxvf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz

mv mysql-5.6.23-linux-glibc2.5-x86_64 /mnt/sda4/mysql5.6.23

mv mysql-5.7.9-linux-glibc2.5-x86_64  /mnt/sda4/mysql65.7.9

mkdir -p /mnt/sda4/mysql5.6.23/data

mkdir -p /mnt/sda4/mysql5.7.9/data

二.預設的初始化路徑

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

安全啟動

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf  這種方式啟動後不能關閉，必須手動kill掉

使用啟動指令碼

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 就可以 service mysql stop|start|restart 了

三.mysql5.6.23版本

將檔案 mysqld_safe,mysql.server,mysqld_multi.server,mysql-log-rotate 裡面的/usr/local/mysql改為/mnt/sda4/mysql5.6.23 。5.7.9版本改為/mnt/sda4/mysql5.7.9

/mnt/sda4/mysql5.6.23/bin/mysqld_safe    將此檔案裡面的/tmp/mysql.sock---->/tmp/mysql5.6.23.sock 5.7.9版本改為/tmp/mysql5.7.9.sock

/mnt/sda4/mysql5.6.23/support-files/mysql.server

/mnt/sda4/mysql5.7.9/support-files/mysqld_multi.server

/mnt/sda4/mysql5.7.9/support-files/mysql-log-rotate

vim 裡面既 %s/\/usr\/local\/mysql/\/mnt\/sda4\/mysql5.6.23/g

四.修改許可權，設定檔

chown -R mysql:mysql /mnt/sda4/mysql5.6.23

[root@sam ~]# cat  /etc/my5.6.23.cnf

[mysqld]

basedir = /mnt/sda4/mysql5.6.23

datadir = /mnt/sda4/mysql5.6.23/data

port=3306

socket=/tmp/mysql.sock

bind-address = 0.0.0.0

skip-external-locking

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

max_allowed_packet = 4M

max_connections=2000

innodb_buffer_pool_size = 3G

innodb_log_buffer_size = 256M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

五.做對應的修改

/mnt/sda4/mysql5.6.23/scripts/mysql_install_db --user=mysql --basedir=/mnt/sda4/mysql5.6.23 --datadir=/mnt/sda4/mysql5.6.23/data

/mnt/sda4/mysql5.6.23/bin/mysqld_safe --defaults-file=/etc/my5.6.23.cnf  測試是否能夠啟動   

cp /mnt/sda4/mysql5.6.23/support-files/mysql.server /etc/init.d/mysql5.6.23

這樣就可以  service mysql stop|start|restart 了

六.mysql5.7.9版本初始化

這裡要注意MySQL5.7.6以後屏蔽了mysql_install_db 只能選擇mysql --initialize進行安裝

5.7.9的配置設定檔

[root@sam ~]# cat /etc/my5.7.9.cnf

[mysqld]

basedir = /mnt/sda4/mysql5.7.9

datadir = /mnt/sda4/mysql5.7.9/data

port=3307                                    <---定義和5.6.23不同的連接埠號碼3307

socket=/tmp/mysql.sock

bind-address = 0.0.0.0

skip-external-locking

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

max_allowed_packet = 4M

max_connections=2000

innodb_buffer_pool_size = 3G

innodb_log_buffer_size = 256M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

七.執行初始化和測試啟動指令碼

/mnt/sda4/mysql5.7.9/bin/mysqld --defaults-file=/etc/my5.7.9.cnf --initialize

/mnt/sda4/mysql5.7.9/bin/mysqld_safe --defaults-file=/etc/my5.7.9.cnf

[root@sam bin]# ./mysqld --defaults-file=/etc/my5.7.9.cnf --initialize

2015-11-19T14:40:52.540335Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2015-11-19T14:40:52.540810Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2015-11-19T14:40:52.540816Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.

2015-11-19T14:40:55.180025Z 0 [Warning] InnoDB: New log files created, LSN=45790

2015-11-19T14:40:55.500690Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2015-11-19T14:40:55.601943Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 8a648e5a-8ecb-11e5-911c-0050569c4b72.

2015-11-19T14:40:55.630337Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2015-11-19T14:40:55.631059Z 1 [Note] A temporary password is generated for root@localhost: ze:OCyh)U1&y  <------隨機密碼

[root@sam bin]# ./mysqld_safe  --defaults-file=/etc/my5.7.9.cnf

151119 22:42:26 mysqld_safe Logging to '/mnt/sda4/mysql5.7.9/data/sam.adsame.com.err'.

151119 22:42:26 mysqld_safe The file /usr/local/mysql/bin/mysqld

does not exist or is not executable. Please cd to the mysql installation

directory and restart this script from there as follows:

./bin/mysqld_safe&

See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information

八.查看啟動的進程

[root@sam ~]# ps aux |grep mysql

root    30783  0.0  0.0  66064  1336 pts/4    S    23:02  0:00 /bin/sh /mnt/sda4/mysql5.7.9/bin/mysqld_safe --datadir=/mnt/sda4/mysql5.7.9/data --pid-file=/mnt/sda4/mysql5.7.9/data/sam.adsame.com.pid

mysql    31110  0.5 10.2 4504488 414676 pts/4  Sl  23:02  0:00 /mnt/sda4/mysql5.7.9/bin/mysqld --basedir=/mnt/sda4/mysql5.7.9 --datadir=/mnt/sda4/mysql5.7.9/data --plugin-dir=/mnt/sda4/mysql5.7.9/lib/plugin --user=mysql --log-error=/mnt/sda4/mysql5.7.9/data/sam.adsame.com.err --pid-file=/mnt/sda4/mysql5.7.9/data/sam.adsame.com.pid --socket=/tmp/mysql5.7.9.sock --port=3307

root    31169  0.0  0.0  65932  1336 pts/4    S    23:02  0:00 /bin/sh /mnt/sda4/mysql5.6.23/bin/mysqld_safe --datadir=/mnt/sda4/mysql5.6.23/data --pid-file=/mnt/sda4/mysql5.6.23/data/sam.adsame.com.pid

mysql    31732  1.0 30.1 4601900 1217628 pts/4 Sl  23:02  0:01 /mnt/sda4/mysql5.6.23/bin/mysqld --basedir=/mnt/sda4/mysql5.6.23 --datadir=/mnt/sda4/mysql5.6.23/data --plugin-dir=/mnt/sda4/mysql5.6.23/lib/plugin --user=mysql --log-error=/mnt/sda4/mysql5.6.23/data/sam.adsame.com.err --pid-file=/mnt/sda4/mysql5.6.23/data/sam.adsame.com.pid --socket=/tmp/mysql5.6.23.sock --port=3306

root    31770  0.0  0.0  61160  740 pts/1    S+  23:04  0:00 grep mysql

九.用戶端串連

/mnt/sda4/mysql5.6.23/bin/mysql -uroot -P3306 -h127.0.0.1

/mnt/sda4/mysql5.7.9/bin/mysql -uroot -P3307 -h127.0.0.1

mysql5.7.9修改密碼

/mnt/sda4/mysql5.7.9/bin

[root@sam bin]# ./mysqladmin -uroot -P3307 -h127.0.0.1 -p password

本文永久更新連結地址：