centos下mysql多实例安装3306、3307实例(2014-10-15)
时间:2022-03-14 01:04
cd /usr/src
wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
tar -zxvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.21-linux-glibc2.5-x86_64 /usr/local/mysql
关闭防火墙
临时关闭:service iptables stop
永久关闭:chkconfig iptables off
关闭selinux
vim /etc/sysconfig/selinux
将SELINUX修改为DISABLED,即SELINUX=DISABLED
初始化用户组及用户
创建一个mysql用户组及用户,且这个用户是不可登录的
创建用户组:groupadd mysql
创建不可登录用户:useradd -g mysql -s /sbin/nologin -d /opt/mysql mysql
查看下创建后的用户信息:id msyql
# id mysql
uid=500(mysql) gid=500(mysql) groups=500(mysql)
创建相关目录
cd /
mkdir /data
cd /data
mkdir mysql
cd mysql
mkdir {mysql_3306,mysql_3307}
cd /data/mysql/mysql_3306
mkdir {data,log,tmp}
cd /data/mysql/mysql_3307
mkdir {data,log,tmp}
更改目录权限
chown -R mysql:mysql /data/mysql/
chown -R mysql:mysql /usr/local/mysql/
添加环境变量
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile
复制my.cnf文件到etc目录
cd /usr/local/mysql/support-files
cp my-default.cnf /etc/my.cnf
修改my.cnf
将以下内容,全部替换
/etc/my.cnf中的内容:
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld_multi]
mysqld = /usr/local/mysql /bin/mysqld_safe
mysqladmin = /usr/local/mysql /bin/mysqladmin
log = /data/mysql/mysqld_multi.log
[mysqld]
user=mysql
basedir = /usr/local/mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld3306]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3306/data
port=3306
server_id=3306
socket=/tmp/mysql_3306.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3306/log/slow.log
log-error = /data/mysql/mysql_3306/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3306/log/mysql3306_bin
[mysqld3307]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3307/data
port=3307
server_id=3307
socket=/tmp/mysql_3307.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3307/log/slow.log
log-error = /data/mysql/mysql_3307/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3307/log/mysql3307_bin
初始化数据库
初始化3306数据库
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf
初始化3307数据库
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf
说明:
--basedir:mysql的安装目录
--datadir:数据库的数据文件目录
--defaults-file:mysql配置文件目录
需要看两个Ok,才说明成功,如下图:
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
mysqld_multi进行多实例管理
启动全部实例:/usr/local/mysql/bin/mysqld_multi start
查看全部实例状态:/usr/local/mysql/bin/mysqld_multi report
启动单个实例:/usr/local/mysql/bin/mysqld_multi start 3306
停止单个实例:/usr/local/mysql/bin/mysqld_multi stop 3306
查看单个实例状态:/usr/local/mysql/bin/mysqld_multi report 3306
修改密码
由于mysql的root用户初始密码是空,所以需要登录mysql进行修改密码,下面以3306为例:
mysql -S /tmp/mysql_3306.sock
set password for root@'localhost'=password('123456');
flush privileges;
新建用户及授权
一般新建数据库都需要新增一个用户,用于程序连接,这类用户只需要insert、update、delete、select权限。新增一个用户,并授权如下:
grant select,delete,update,insert on *.* to admin@'192.168.1.%' identified by '123456';
flush privileges;
关闭防火墙
临时关闭:service iptables stop 永久关闭:chkconfig iptables off
关闭selinux
vim /etc/sysconfig/selinux 将SELINUX修改为DISABLED,即SELINUX=DISABLED
初始化用户组及用户
创建一个mysql用户组及用户,且这个用户是不可登录的 创建用户组:groupadd mysql 创建不可登录用户:useradd -g mysql -s /sbin/nologin -d /opt/mysql mysql 查看下创建后的用户信息:id msyql # id mysql uid=500(mysql) gid=500(mysql) groups=500(mysql)
创建相关目录
cd / mkdir /data cd /data mkdir mysql cd mysql mkdir {mysql_3306,mysql_3307} cd /data/mysql/mysql_3306 mkdir {data,log,tmp} cd /data/mysql/mysql_3307 mkdir {data,log,tmp}
更改目录权限
chown -R mysql:mysql /data/mysql/ chown -R mysql:mysql /usr/local/mysql/
添加环境变量
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile source /etc/profile
复制my.cnf文件到etc目录
cd /usr/local/mysql/support-files cp my-default.cnf /etc/my.cnf
修改my.cnf
将以下内容,全部替换 /etc/my.cnf中的内容:[client] port=3306 socket=/tmp/mysql.sock [mysqld_multi] mysqld = /usr/local/mysql /bin/mysqld_safe mysqladmin = /usr/local/mysql /bin/mysqladmin log = /data/mysql/mysqld_multi.log [mysqld] user=mysql basedir = /usr/local/mysql sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld3306] mysqld=mysqld mysqladmin=mysqladmin datadir=/data/mysql/mysql_3306/data port=3306 server_id=3306 socket=/tmp/mysql_3306.sock log-output=file slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql_3306/log/slow.log log-error = /data/mysql/mysql_3306/log/error.log binlog_format = mixed log-bin = /data/mysql/mysql_3306/log/mysql3306_bin [mysqld3307] mysqld=mysqld mysqladmin=mysqladmin datadir=/data/mysql/mysql_3307/data port=3307 server_id=3307 socket=/tmp/mysql_3307.sock log-output=file slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql_3307/log/slow.log log-error = /data/mysql/mysql_3307/log/error.log binlog_format = mixed log-bin = /data/mysql/mysql_3307/log/mysql3307_bin
初始化数据库
初始化3306数据库 /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf 初始化3307数据库 /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf说明: --basedir:mysql的安装目录 --datadir:数据库的数据文件目录 --defaults-file:mysql配置文件目录
需要看两个Ok,才说明成功,如下图: cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
mysqld_multi进行多实例管理
启动全部实例:/usr/local/mysql/bin/mysqld_multi start 查看全部实例状态:/usr/local/mysql/bin/mysqld_multi report 启动单个实例:/usr/local/mysql/bin/mysqld_multi start 3306 停止单个实例:/usr/local/mysql/bin/mysqld_multi stop 3306 查看单个实例状态:/usr/local/mysql/bin/mysqld_multi report 3306
修改密码
由于mysql的root用户初始密码是空,所以需要登录mysql进行修改密码,下面以3306为例: mysql -S /tmp/mysql_3306.sock set password for root@'localhost'=password('123456'); flush privileges;
新建用户及授权
一般新建数据库都需要新增一个用户,用于程序连接,这类用户只需要insert、update、delete、select权限。新增一个用户,并授权如下: grant select,delete,update,insert on *.* to admin@'192.168.1.%' identified by '123456'; flush privileges;