单机MySQL多实例安装操作步骤
多实例: 同一套mysql程序,不同的“my.cnf,启动程序,端口”。属于逻辑上面的多实例。 |
|
6.1 安装MySQL依赖
[root@i-7u60amfs ~]# yum install ncurses-devel libaio-devel numactl.x86_64 -y |
wget http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-5.6/mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz |
小结:此处安装选择MySQL-5.6.38版本。 |
6.2 建立MySQL账号
[root@i-7u60amfs ~]# groupadd mysql [root@i-7u60amfs ~]# useradd -s /sbin/nologin -g mysql -M mysql [root@i-7u60amfs ~]# id mysql uid=500(mysql) gid=500(mysql) groups=500(mysql) |
6.3 获得MySQl软件
[root@i-7u60amfs app]# tar axf mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@i-7u60amfs local]# mv mysql-5.6.38-linux-glibc2.12-x86_64/ mysql |
6.4 安装MySQL软件
[root@i-7u60amfs app]# tar axf mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz [root@i-7u60amfs app]# ln -s mysql-5.6.38-linux-glibc2.12-x86_64 mysql |
6.5 创建MySQL多实例数据目录
[root@i-7u60amfs local]# mkdir /u01/{3306,3307}/data -pv [root@i-7u60amfs local]# tree /u01/ /u01/ ├── 3306 │ └── data └── 3307 └── data
|
6.6 创建MySQL多实例配置文件
3306配置文件 | 3307配置文件 |
[root@i-7u60amfs 3306]# cat /u01/3306/my.cnf [client] port = 3306 socket = /u01/3306/mysql.sock
[mysql] #no-auto-rehash auto-rehash #开启自动补全功能
[mysqld] port = 3306 socket = /u01/3306/mysql.sock basedir = /usr/local/mysql datadir = /u01/3306/data pid-file = /u01/3306/mysql-bin log-error = /u01/data/error.log #log-bin = /u01/3306/mysql-bin relay-log = /u01/3306/relay-bin relay-log-info-file = /u01/3306/relay-log.info
server-id = 1
[mysqld_safe] log-error=/u01/3306/mysql_oldboy3306.err pid-file=/u01/3306/mysqld.pid
| [root@i-7u60amfs 3307]# cat my.cnf [client] port = 3307 socket = /u01/3307/mysql.sock
[mysql] #no-auto-rehash auto-rehash
[mysqld] port = 3307 socket = /u01/3307/mysql.sock basedir = /usr/local/mysql datadir = /u01/3307/data pid-file = /u01/3307/mysql-bin log-error = /u01/data/error.log #log-bin = /u01/3307/mysql-bin relay-log = /u01/3307/relay-bin relay-log-info-file = /u01/3307/relay-log.info
server-id = 3
[mysqld_safe] log-error=/u01/3307/mysql_oldboy3307.err pid-file=/u01/3307/mysqld.pid
|
touch /u01/3306/mysql_oldboy3306.err |
touch /u01/3306/mysql_oldboy3307.err |
chown -R mysql:mysql /u01 |
小结:需要创建mysql启动报错日志文件。 |
6.7 创建多实例MySQL启动文件
3306启动脚本 | 3307启动脚本 |
#!/bin/sh
port=3306 mysql_user="root" mysql_pwd="oldboy123" cmdpath="/usr/local/mysql/bin" mysql_sock="/u01/${port}/mysql.sock"
#startup function function_start_mysql() { if [ ! -e "$mysql_sock" ];then echo "starting mysql…" /bin/sh ${cmdpath}/mysqld_safe --defaults-file=/u01/${port}/my.cnf 2>&1 > /devull & else echo "mysql is running…" exit fi }
#stop function function_stop_mysql() { if [ ! -e "$mysql_sock" ];then echo "mysql is stopped…" exit else echo "stoping mysql…" ${cmdpath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /u01/${port}/mysql.sock shutdown fi }
#restart function function_restart_mysql() { echo "restarting mysql…" function_stop_mysql sleep 2 function_start_mysql }
case $1 in
start)
function_start_mysql ;;
stop)
function_stop_mysql ;;
restart)
function_restart_mysql ;;
*) echo "usage: /data/${port}/mysql {start|stop|restart}" esac | #!/bin/sh
port=3307 mysql_user="root" mysql_pwd="oldboy123" cmdpath="/usr/local/mysql/bin" mysql_sock="/u01/${port}/mysql.sock"
#startup function function_start_mysql() { if [ ! -e "$mysql_sock" ];then echo "starting mysql…" /bin/sh ${cmdpath}/mysqld_safe --defaults-file=/u01/${port}/my.cnf 2>&1 > /devull & else echo "mysql is running…" exit fi }
#stop function function_stop_mysql() { if [ ! -e "$mysql_sock" ];then echo "mysql is stopped…" exit else echo "stoping mysql…" ${cmdpath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /u01/${port}/mysql.sock shutdown fi }
#restart function function_restart_mysql() { echo "restarting mysql…" function_stop_mysql sleep 2 function_start_mysql }
case $1 in
start)
function_start_mysql ;;
stop)
function_stop_mysql ;;
restart)
function_restart_mysql ;;
*) echo "usage: /data/${port}/mysql {start|stop|restart}" esac |
6.8 修改MySQL数据目录权限
[root@i-7u60amfs app]# chown -R mysql:mysql /u01/ |
[root@i-7u60amfs app]# find /u01 -type f -name mysql -exec chmod 700 {} \; |
|
小结:赋予多实例启动文件700的权限,是因为在多实例的启动脚本里面有MySQL的登录密码,为了数据库的安全性,需要降权。 |
6.9 配置MySQL服务的环境变量
[root@i-7u60amfs mysql]# echo 'export PATH=/usr/local/mysql/bin/:$PATH' >> /etc/profile [root@i-7u60amfs mysql]# source /etc/profile |
6.10 初始化MySQL数据库
[root@i-7u60amfs root]# cd /usr/local/mysql |
[root@i-7u60amfs mysql]#./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/u01/3306/data |
[root@i-7u60amfs mysql]#./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/u01/3307/data |
报错一:
[root@i-7u60amfs mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/app/mysql --datadir=/u01/3306/data
Installing MySQL system tables.../usr/local/mysql/bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory
解决方法:
原因:是用yum 安装的libnuma.so.1,但安装时默认安装的是32的,但db2需要的是64位的。
解决办法:
1.如果已经安装了libnuma.so.1,先yum remove libnuma.so.1
2.yum install numactl.x86_64
6.11 多实例MySQL启动数据库
[root@i-7u60amfs 3306]# /data/3307/mysql start |
[root@i-7u60amfs 3307]# /data/3307/mysql start |
报错:[root@i-7u60amfs 3307]# /u01/3306/mysql stop
stoping mysql…
Warning: Using a password on the command line interface can be insecure.
/usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
6.12 多实例MySQL登录数据库
方法一:
[root@i-7u60amfs 3307]# mysql -S /u01/3306/mysql.sock |
[root@i-7u60amfs 3307]# mysql -S /u01/3307/mysql.sock |
小结:多实例登录需要指定套接字。 |
方法二:
[root@i-7u60amfs 3307]# mysqld_safe --defaults-file=/u01/3306/my.cnf 2>&1 >/dev/null & |
[root@i-7u60amfs 3307]# mysqld_safe --defaults-file=/u01/3307/my.cnf 2>&1 >/dev/null & |