study-22: Mysql 单实例、多实例安装（使用cmake安装MySQL5.5版本）

最新推荐文章于 2021-02-23 05:24:11 发布

流浪猫之家

最新推荐文章于 2021-02-23 05:24:11 发布

阅读量577

点赞数

一，Mysql5.5的cmake 安装方式

yum/rpm 安装：内部系统，不太关心性能的应用

编译安装：./configure , make , make install ；5.0 ~ 5.1版本常用安装方式

cmake安装：从5.5版本后，使用cmake方式安装mysql

二进制解压免安装：解压就能使用，许多DBA的钟爱

1，通过cmake安装Mysql 5.5

1）cmake软件安装

./configure

gmake

gmake install

2）安装依赖包

yum install ncurses-devel -y

3）创建用户和组

groupadd mysql

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

4）解压编译Mysql

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
-DMYSQL_DATADIR=/application/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0

make

make install

5）拷贝配置文件

cp support-files/my-small.cnf /etc/my.cnf

6）配置环境变量

echo "PATH=/application/mysql/bin/:$PATH" >> /etc/profile #增加的命令路径在前，默认的在后，防止调用错误
source /etc/profile

7）初始化数据文件

mkdir /application/mysql/data -p
chown -R mysql.mysql /application/mysql

chmod -R 1777 /tmp/ #调整，否则初始化会错误

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data/ --user=mysql #初始化

cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld

/application/mysql/bin/mysqladmin -u root password '123456' #增加root密码访问

/etc/init.d/mysqld start #手工启动服务

chkconfig mysqld on #开机启动服务

登录Mysql

8）删除root用户，授权system用户管理mysql，权限和root一样（非必须）

grant all privileges on *.* to system@'localhost' identified by '123456' with grant option; # 比授权普通用户多一个with grant option的授权的权限；

二，MYSQL多实例介绍

1，什么是MYSQL多实例

在一台机器上开启多个不同的服务端口（如：3307，3307），运行多个MYSQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供各自的服务。

2，推荐的配置结构（不同的实例，使用不同的数据文件、配置文件和启动文件等）

3，安装

3.1 建立Mysql多实例的条件

1）安装好Mysql安装依赖的包

yum install ncurses-devel libaio-devel -y

2）cmake安装5.5为例讲解多实例

首先按照我们前面讲过的四种Mysql的安装方法之一安装好数据库，如果是编译安装，则到make install之后截止；

3.2 建立Mysql帐号

groupadd mysql

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

3.3 如果机器上有单实例的Mysql运行，则删除单实例的：
pkill mysqld

rm -f /etc/init.d/mysqld

3.4 创建多实例目录结构，并初始化数据库

/data/ # 总的多实例根目录
|-- 3306 # 3306实例的目录
| `-- data # 3306实例的数据文件目录
`-- 3307 # 3307实例的目录
`-- data # 3307实例的数据文件目录

生产硬件配置：MEM 32G 双CPU 8核，磁盘 6*600G SAS 15K； 2 - 3个实例

#初始化数据库

mysql 5.1.X 初始化命令

/application/mysql/bin/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql

/application/mysql/bin/mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql

mysql 5.5.X 初始化命令

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql

3.5 创建多实例配置文件（server-id不一致）

3306/my.cnf ：

[client]
port = 3306
socket = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
#log_long_format
#log-error = /data/3306/error.log
#log-slow-queries = /data/3306/slow.log
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover

lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql

server-id = 1

innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M

[mysqld_safe]
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid

3307/my.cnf ：

[client]
port = 3307
socket = /data/3307/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /application/mysql
datadir = /data/3307/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
#long_query_time = 1
#log_long_format
#log-error = /data/3307/error.log
#log-slow-queries = /data/3307/slow.log
pid-file = /data/3307/mysql.pid
#log-bin = /data/3307/mysql-bin
relay-log = /data/3307/relay-bin
relay-log-info-file = /data/3307/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover

lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql

server-id = 3

innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M

[mysqld_safe]
log-error=/data/3307/mysql_3307.err
pid-file=/data/3307/mysqld.pid

3.6 创建多实例的启动：

#如果my.cnf不在/etc目下，则启动通过default-file指定，这里我们不手工进行启动，而是用后面的启动脚本来做启动、停止操作

mysqld_safe --default-file=/data/3306/my.cnf 2 > & 1 > /dev/null &

mysqld_safe --default-file=/data/3307/my.cnf 2 > & 1 > /dev/null &

3306/mysql ：

#init
port=3306
mysql_user="root"
mysql_pwd="oldboy"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}

#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}

#restart function
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}

case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

3307/mysql ：

#init
port=3307
mysql_user="root"
mysql_pwd="oldboy"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}

#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}

#restart function
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}

case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

3.7 多实例启动文件的停止：

启动停止脚本授权：

chown -R mysql.mysql /data

find /data -type f -name "mysql" | xargs chmod +x

mysqladmin -u root -p123456 -S /data/3306/mysql.sock shutdown # -S ：平滑停止数据库

mysqladmin -u root -p123456 -S /data/3307/mysql.sock shutdown

3.8 命令行登录Mysql

mysql -S /data/3306/mysql.sock

3.8 不同两个数据库之间免退出的命令行登录方式

system mysql -S /data/3306/mysql.sock
system mysql -S /data/3307/mysql.sock

3.9 修改Mysql的root密码

mysqladmin -uroot -S /data/3306/mysql.sock password '123456'

mysqladmin -uroot -S /data/3307/mysql.sock password '123456'

3.10 启动脚本只授权给root用户

find /data -type f -name "mysql" -exec chown root.root {} \;

find /data -type f -name "mysql" -exec chmod 700 {} \;

流浪猫之家

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
study-22: Mysql 单实例、多实例安装（使用cmake安装MySQL5.5版本）

一，Mysql5.5的cmake 安装方式yum/rpm 安装：内部系统，不太关心性能的应用编译安装：./configure , make , make install ；5.0 ~ 5.1版本常用安装方式cmake安装：从5.5版本后，使用cmake方式安装mysql二进制解压免安装：解压就能使用，许多DBA的钟爱1，通过cmake安装Mysql 5.5
复制链接

扫一扫