MySQL编译安装 以及 单主机多MySQL实例的搭建
在某些特殊情况下,需要在一台服务器上搭建多个MySQL实例,来满足需求,比如在生产环境创建正常生产所需的MySQL实例的基础上,需要创建专门用于备份和恢复数据的实例。
本文以创建2个MySQL实例为例,搭建步骤如下。
关键字: MySQL,编译安装,单主机,多实例,最佳实践
一、初始信息:
- MySQL程序版本:mysql-5.6.37
- 服务器系统:CentOS6.8
- 2个实例的服务名分配:mysqld1 与 mysql2
- 2个实例的端口分配:33061 与 33062
二、创建 mysql 组和用户
groupadd mysql
useradd mysql -g mysql
三、创建目录并授予权限
1. 程序目录(basedir)
mkdir -pv /usr/local/mysql/mysql_instance_1
mkdir -pv /usr/local/mysql/mysql_instance_2
chown -R mysql.mysql /usr/local/mysql/mysql_instance_1
chown -R mysql.mysql /usr/local/mysql/mysql_instance_2
2. 数据目录(datadir)
mkdir -pv /data/datadir_1_mysql
mkdir -pv /data/datadir_2_mysql
chown -R mysql.mysql /data/datadir_1_mysql
chown -R mysql.mysql /data/datadir_2_mysql
四、准备依赖程序:
yum install cmake make gcc gcc-c++ Perl bison ncurses ncurses-devel -y
五、创建第一个MySQL实例
1. 解压程序包
tar -xvf mysql-5.6.37.tar.gz
cd mysql-5.6.37
- 【注:】每次编译前应清理上次编译(无论上次编译成功或失败)产生的信息,如下:
make clean
rm -f CMakeCache.txt
2. 编译安装
MySQL从5.5版本以后不再使用configure编译,而是使用cmake编译
cd mysql-5.6.37
cmake .
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql/mysql_instance_1 \
-DSYSCONFDIR=/usr/local/mysql/mysql_instance_1 \
-DMYSQL_DATADIR=/data/datadir_1_mysql \
-DMYSQL_TCP_PORT=33061 \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql_instance_1/mysqld.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_SSL=bundled \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1
直至 cmake 执行成功无报错后,执行
make -j 4 && make install
3. 复制配置文件
cp /usr/local/mysql/mysql_instance_1/support-files/my-default.cnf /usr/local/mysql/mysql_instance_1/my.cnf
4. 修改配置文件
[mysqld]
skip_name_resolve = 1
innodb_file_per_table = 1
basedir = /usr/local/mysql/mysql_instance_1
datadir = /data/datadir_1_mysql
port = 33061
server_id = 10
socket = /usr/local/mysql/mysql_instance_1/mysqld.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error = /data/datadir_1_mysql/mysql_1.log
pid-file = /data/datadir_1_mysql/node3.pid
[client]
socket = /usr/local/mysql/mysql_instance_1/mysqld.sock
5. 复制启动服务文件并添加开机启动(CentOS6.x系统)
cp /usr/local/mysql/mysql_instance_1/support-files/mysql.server /etc/rc.d/init.d/mysqld1
chkconfig --add mysqld1
chkconfig --list mysqld1
6. 初始化数据库
/usr/local/mysql/mysql_instance_1/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/mysql_instance_1 --datadir=/data/datadir_1_mysql
7. 启动第一个MySQL实例
service mysqld1 start
8. [选择执行]加载MySQL安全脚本
/usr/local/mysql/mysql_instance_1/bin/mysql_secure_installation
9. 查看启动的服务
ps aux | grep mysql
六、创建第二个MySQL实例
1. 解压程序
rm -rf mysql-5.6.37
tar -xvf mysql-5.6.37.tar.gz
cd mysql-5.6.37
- 【注:】每次编译前应清理上次编译(无论上次编译成功或失败)产生的信息,如下:
make clean
rm -f CMakeCache.txt
2. 编译安装
MySQL从5.5版本以后不再使用configure编译,而是使用cmake编译
cd mysql-5.6.37
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/mysql_instance_2 -DSYSCONFDIR=/usr/local/mysql/mysql_instance_2 -DMYSQL_DATADIR=/data/datadir_2_mysql -DMYSQL_TCP_PORT=33062 -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql_instance_2/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_SSL=bundled -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1
cmake命令执行成功后,执行:
make -j 4 && make install
3. 复制配置文件
cp /usr/local/mysql/mysql_instance_2/support-files/my-default.cnf /usr/local/mysql/mysql_instance_2/my.cnf
4. 修改配置文件
[mysqld]
skip_name_resolve = 1
innodb_file_per_table = 1
basedir = /usr/local/mysql/mysql_instance_2
datadir = /data/datadir_2_mysql
port = 33062
server_id = 20
socket = /usr/local/mysql/mysql_instance_2/mysqld.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error = /data/datadir_2_mysql/mysql_2.log
pid-file = /data/datadir_2_mysql/node3.pid
[client]
socket = /usr/local/mysql/mysql_instance_2/mysqld.sock
5. 复制启动服务文件
cp /usr/local/mysql/mysql_instance_2/support-files/mysql.server /etc/rc.d/init.d/mysqld2
6. 修改服务文件,修改以下语句,
【注:】要保证每个实例的 lock_file 不同,否则报错。
lock_file_path="$lockdir/mysql_sec"
7. 添加开机启动(CentOS6.x系统)
chkconfig --add mysqld2
chkconfig --list mysqld2
8. 初始化数据库
/usr/local/mysql/mysql_instance_2/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/mysql_instance_2 --datadir=/data/datadir_2_mysql
9.启动第二个MySQL实例
service mysqld2 start
10. [可选执行] 加载MySQL安全脚本
/usr/local/mysql/mysql_instance_2/bin/mysql_secure_installation
11. 查看启动的服务
ps aux | grep mysql
七、使用mysql客户端登录MySQL服务
1. 登录MySQL实例1:
/usr/local/mysql/mysql_instance_1/bin/mysql -uroot -p
2. 登录MySQL实例2:
/usr/local/mysql/mysql_instance_2/bin/mysql -uroot -p
八、引用文档
- MySQL 编译参数官方文档
https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html#option_cmake_with_systemd
- 博客:源码编译安装 MySQL 5.6.10最佳实践