yum方式安装mysql多实例_yum && 编译 安装mysql 5.7 多实例

yum安装

[root@localhost ~]# wget http://repo.mysql.com/mysql57-community-release-el7.rpm

[root@localhost ~]# rpm -ivh mysql57-community-release-el7.rpm

[root@localhost ~]# yum install mysql mysql-server

用科技大学的mysql源下载会更快些

[root@localhost ~]# rpm -ivh http://mirrors.ustc.edu.cn/mysql-repo/mysql57-community-release-el7.rpm

初始化

[root@localhost ~]# grep "password" /var/log/mysqld.log

2017-04-12T00:45:32.972626Z 1 [Note] A temporary password is generated for root@localhost: kvyqpEMl<9b0 #随机密码

[root@localhost ~]# mysqladmin -u root -p password #重置密码

Enter password:

New password:

Confirm new password:

[root@localhost ~]# systemctl start mysqld

[root@localhost ~]# systemctl enable mysqld

编译安装

环境准备

[root@localhost ~]yum install -y gcc gcc-c++ openssl openssl-devel ncurses ncurses-devel cmake bison*

[root@localhost ~]# mkdir /data/

[root@localhost ~]# mkdir /data/mysql/

[root@localhost ~]# mkdir /data/mysql/data/

[root@localhost ~]# groupadd mysql

[root@localhost ~]# useradd -g mysql -s /sbin/nologin mysql

[root@localhost ~]# cd /usr/local/src/

[root@localhost ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-boost-5.7.18.tar.gz

[root@localhost src]# tar zvxf mysql-boost-5.7.18.tar.gz

[root@localhost src]# cd mysql-5.7.18

编译安装

cmake . \-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \-DMYSQL_DATADIR=/data/mysql/data \-DSYSCONFDIR=/etc \-DWITH_BOOST=boost \-DEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci \-DEXTRA_CHARSETS=all \-DMYSQL_UNIX_ADDR=/data/mysql/data/mysqld.sock \-DENABLED_LOCAL_INFILE=1\-DWITH_MYISAM_STORAGE_ENGINE=1\-DWITH_INNOBASE_STORAGE_ENGINE=1\-DWITH_MEMORY_STORAGE_ENGINE=1\-DWITH_PARTITION_STORAGE_ENGINE=1\-DWITH_READLINE=1\-DMYSQL_TCP_PORT=3306

# -DCMAKE_INSTALL_PREFIX=/usr/local/mysql #安装路径

# -DMYSQL_DATADIR=/data/mysql/data #数据文件存放位置

# -DSYSCONFDIR=/etc #my.cnf路径

# -DWITH_BOOST=boost #指定boost库

# -DDEFAULT_CHARSET=utf8 #默认字符

# -DDEFAULT_COLLATION=utf8_general_ci #默认排序规则

# -DEXTRA_CHARSETS=all #安装所有的字符集

# -DMYSQL_UNIX_ADDR=/data/mysql/data/mysqld.sock #连接数据库socket路径

# -DENABLED_LOCAL_INFILE=1 #允许从本地导入数据

# -DWITH_MYISAM_STORAGE_ENGINE=1 #支持MyIASM引擎

# -DWITH_INNOBASE_STORAGE_ENGINE=1 #支持InnoDB引擎

# -DWITH_MEMORY_STORAGE_ENGINE=1 #支持Memory引擎

# -DWITH_PARTITION_STORAGE_ENGINE=1 #安装支持数据库分区

# -DWITH_READLINE=1 #快捷键功能

# -DMYSQL_TCP_PORT=3306 #端口

[root@localhost mysql-5.7.18]# make -j `grep processor /proc/cpuinfo | wc -l` && make install

# grep processor /proc/cpuinfo 根据cpu核心数指定编译时的线程数(多核cpu会快很多)

初始化配置

[root@localhost ~]# chown -R mysql:mysql /data/mysql/data/

[root@localhost mysql-5.7.18]# /usr/local/mysql/bin/mysqld --initialize --user=mysql

2017-04-16T09:35:15.850060Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-04-16T09:35:16.331637Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-04-16T09:35:16.408336Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-04-16T09:35:16.467944Z 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: ffbe4813-2287-11e7-9d8c-000c298c8776.

2017-04-16T09:35:16.469737Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-04-16T09:35:16.470947Z 1 [Note] A temporary password is generated for root@localhost: sRXga&j#w5n

[root@localhost mysql-5.7.18]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql

配置文件

[root@localhost ~]# cp /etc/my.cnf /etc/my.cnf.bak

[root@localhost ~]# rm -rf /etc/my.cnf

[root@localhost ~]# vim /etc/my.cnf

[client]

port = 3306

socket = /data/mysql/data/mysql.sock

default-character-set = utf8

[mysqld]

port = 3306

socket = /data/mysql/data/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql/data

pid-file = /data/mysql/data/mysql.pid

user = mysql

bind-address = 0.0.0.0

server-id = 1

init-connect = 'SET NAMES utf8'

character-set-server = utf8

#skip-name-resolve

#skip-networking

back_log = 300

max_connections = 1000

max_connect_errors = 6000

open_files_limit = 65535

table_open_cache = 128

max_allowed_packet = 4M

binlog_cache_size = 1M

max_heap_table_size = 8M

tmp_table_size = 16M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

sort_buffer_size = 8M

join_buffer_size = 8M

key_buffer_size = 4M

thread_cache_size = 8

query_cache_type = 1

query_cache_size = 8M

query_cache_limit = 2M

ft_min_word_len = 4

log_bin = mysql-bin

binlog_format = mixed

expire_logs_days = 30

log_error = /data/mysql/data/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/data/mysql-slow.log

performance_schema = 0

explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB

#default-storage-engine = MyISAM

innodb_file_per_table = 1

innodb_open_files = 500

innodb_buffer_pool_size = 64M

innodb_write_io_threads = 4

innodb_read_io_threads = 4

innodb_thread_concurrency = 0

innodb_purge_threads = 1

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 32M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M

myisam_sort_buffer_size = 8M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

interactive_timeout = 28800

wait_timeout = 28800

[mysqldump]

quick

max_allowed_packet = 16M

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

read_buffer = 4M

write_buffer = 4M

EOF

系统服务

[root@localhost ~]# cp /usr/local/src/mysql-5.7.18/support-files/mysql.server /etc/init.d/mysqld

[root@localhost ~]# chmod +x /etc/init.d/mysqld

[root@localhost ~]# chkconfig --add mysqld

[root@localhost ~]# chkconfig mysqld on

[root@localhost ~]# service mysqld start

方法二

[root@localhost ~]# vim /usr/lib/systemd/system/mysqld.service

[Unit]

Description=Mysql

After=syslog.target network.target remote-fs.target nss-lookup.target

[Service]

Type=forking

PIDFile=/data/mysql/data/mysql.pid

ExecStart=/usr/local/mysql/support-files/mysql.server start

ExecReload=/bin/kill -s HUP $MAINPID

ExecStop=/bin/kill -s QUIT $MAINPID

PrivateTmp=false

[Install]

[root@localhost ~]# systemctl daemon-reload

[root@localhost ~]# systemctl enable mysqld.service

[root@localhost ~]# mysqladmin -u root -p password #重置密码

Enter password:

New password:

Confirm new password

环境变量

[root@localhost ~]# vim /etc/profile

PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH

export PATH

[root@localhost ~]# source /etc/profile

msyql多实例(完全独立)

##准备数据目录

[root@localhost local]# mkdir /data/mysql/{3306,3307}

[root@localhost local]# chown mysql:mysql /data/mysql/

[root@localhost local]# chown mysql:mysql /data/mysql/

[root@localhost local]# mv /usr/local/mysql /tmp

[root@localhost local]# cd /usr/local/src/mysql-5.7.18/

[root@localhost mysql-5.7.18]# make install

##初始化数据库

[root@localhost mysql-5.7.18]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306

[root@localhost mysql-5.7.18]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307

##配置数据库目录

[root@localhost mysql-5.7.18]# mv /usr/local/mysql /usr/local/mysql3306

[root@localhost mysql-5.7.18]# make install

[root@localhost mysql-5.7.18]# mv /usr/local/mysql /usr/local/mysql3307

[root@localhost mysql-5.7.18]# cd /usr/local/

[root@localhost local]# cp /etc/my.cnf /usr/local/mysql3306

[root@localhost local]# cp /etc/my.cnf /usr/local/mysql3307

[root@localhost local]# mv /etc/my.cnf /etc/my.cnf.bak

##修改my.cnf文件中的路径

[root@localhost local]# vim mysql3306/my.cnf

[client]

port = 3306

socket = /data/mysql/3306/mysql.sock

default-character-set = utf8

[mysqld]

port = 3306

socket = /data/mysql/3306/mysql.sock

basedir = /usr/local/mysql3306

datadir = /data/mysql/3306

pid-file = /data/mysql/3306/mysql.pid

user = mysql

bind-address = 0.0.0.0

server-id = 1

[root@localhost local]# vim mysql3307/my.cnf

[client]

port = 3307

socket = /data/mysql/3307/mysql.sock

default-character-set = utf8

[mysqld]

port = 3307

socket = /data/mysql/3307/mysql.sock

basedir = /usr/local/mysql3307

datadir = /data/mysql/3307

pid-file = /data/mysql/3307/mysql.pid

user = mysql

bind-address = 0.0.0.0

server-id = 2

##配置mysql 启动脚本

[root@localhost local]# vim mysql3306/support-files/mysql.server

then

basedir=/usr/local/mysql3306

bindir=/usr/local/mysql3306/bin

if test -z "$datadir"

then

datadir=/data/mysql/3306

fi

sbindir=/usr/local/mysql3306/bin

libexecdir=/usr/local/mysql3306/bin

[root@localhost local]# vim mysql3307/support-files/mysql.server

then

basedir=/usr/local/mysql3307

bindir=/usr/local/mysql3307/bin

if test -z "$datadir"

then

datadir=/data/3307/data

fi

sbindir=/usr/local/mysql3307/bin

libexecdir=/usr/local/mysql3307/bin

## 启动服务

[root@localhost local]# /usr/local/mysql3306/support-files/mysql.server start

[root@localhost local]# /usr/local/mysql3307/support-files/mysql.server start

[root@localhost local]# netstat -anpt

Active Internet connections (servers and established)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 40724/mysqld

tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 47249/mysqld

mysqld_multi管理多实例

##环境准备

[root@localhost local]# mkdir /data/mysql/{3306,3307}

[root@localhost local]# chown mysql:mysql /data/mysql/

[root@localhost local]# cd /usr/local/src/mysql-5.7.18/

[root@localhost mysql-5.7.18]# make install

## 修改my.cnf

[root@localhost mysql-5.7.18]# vim /etc/my.cnf

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

log = /data/mysql/mysqld_multi.log

[mysqld1]

port = 3306

socket = /data/mysql/3306/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql/3306

pid-file = /data/mysql/3306/mysql.pid

user = mysql

log_error = /data/mysql/3306/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/3306/mysql-slow.log

general_log=ON

general_log_file= /data/mysql/3306/mysql.log

performance_schema = 0

explicit_defaults_for_timestamp

[mysqld2]

port = 3307

socket = /data/mysql/3307/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql/3307

pid-file = /data/mysql/3307/mysql.pid

user = mysql

log_error = /data/mysql/3307/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/3307/mysql-slow.log

general_log=ON

general_log_file= /data/mysql/3307/mysql.log

performance_schema = 0

explicit_defaults_for_timestamp

## 初始化数据库

# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306

# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307

## 服务管理

# /usr/local/mysql/bin/mysqld_multi report #查看所有实例状态

# /usr/local/mysql/bin/mysqld_multi start #启动所有实例

# /usr/local/mysql/bin/mysqld_multi start 1 #启动实例1

# mysqladmin -uroot -prVrzzT9s -S /data/mysql/3306/mysql.sock shutdown #关闭某个实例

## 设置密码

[root@localhost ~]# mysqladmin -u root -p password -P 3306 -S /data/mysql/3306/mysql.sock

[root@localhost ~]# mysqladmin -u root -p password -P 3307 -S /data/mysql/3307/mysql.sock