MariaDB 单机多实例
原文链接 http://geekspeng.cn/2019/10/21/MariaDB-%E5%8D%95%E6%9C%BA%E5%A4%9A%E5%AE%9E%E4%BE%8B.html
注:以下为加速网络访问所做的原文缓存,经过重新格式化,可能存在格式方面的问题,或偶有遗漏信息,请以原文为准。
安装mariadb
# yum install mariadb-server -y
创建对应的目录文件
# mkdir -p /mariadb/data{3306,3307,3308}
# chown -R mysql:mysql /mariadb
<!-- more -->
初始化数据库文件
# mysql_install_db --datadir=/mariadb/data3306 --user=mysql
# mysql_install_db --datadir=/mariadb/data3307 --user=mysql
# mysql_install_db --datadir=/mariadb/data3308 --user=mysql
可能会报如下的错误
Neither host 'galera-57561c9a' nor 'localhost' could be looked up with
'/usr/libexec/resolveip'
Please configure the 'hostname' command to return a correct
hostname.
If you want to solve this at a later stage, restart this script
with the --force option
如果出现如上的错误,就按提示上加上 --force 选项
# mysql_install_db --datadir=/mariadb/data3306 --user=mysql --force
# mysql_install_db --datadir=/mariadb/data3307 --user=mysql --force
# mysql_install_db --datadir=/mariadb/data3308 --user=mysql --force
手动启动流程
创建对应配置文件
[mysqld]
port=3306
socket=/tmp/mysql3306.sock
pid-file=/tmp/mysql3306.pid
datadir=/mariadb/data3306[mysqld_safe]
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid
mysqld_safe 方式启动
配置文件
# vi /etc/my.cnf.d/3306.cnf
[mysqld]
port=3306
socket=/tmp/mysql3306.sock
pid-file=/tmp/mysql3306.pid
datadir=/mariadb/data3306
log-error=/var/log/mariadb/3306.log
# vi /etc/my.cnf.d/3307.cnf
[mysqld]
port=3307
socket=/tmp/mysql3307.sock
pid-file=/tmp/mysql3307.pid
datadir=/mariadb/data3307
log-error=/var/log/mariadb/3307.log
# vi /etc/my.cnf.d/3308.cnf
[mysqld]
port=3308
socket=/tmp/mysql3308.sock
pid-file=/tmp/mysql3308.pid
datadir=/mariadb/data3308
log-error=/var/log/mariadb/3308.log
- mysqld_safe 启动
# mysqld_safe --defaults-file=/etc/my.cnf.d/3306.cnf
# mysqld_safe --defaults-file=/etc/my.cnf.d/3307.cnf
# mysqld_safe --defaults-file=/etc/my.cnf.d/3308.cnf
- 通过脚本启动
#!/bin/bash
#chkconfig: 345 80 2
port=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/bin"
defaults-file="/etc/my.cnf.d/3306.cnf"
mysql_sock="/tmp/mysql3306.sock"function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
${cmd_path}/mysqld_safe --defaults-file=${defaults-file} &> /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
fi
}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: ./mysqld3306 {start|stop|restart}\n"
mysqld_multi
配置文件
# cp -a /etc/my.cnf /etc/my.cnf.bak
# vi /etc/my.cnf # 添加如下代码,里面没有列出来的值都是保持默认的值
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
user = mysql
[mysqld3306]
port=3306
socket=/tmp/mysql3306.sock
pid-file=/tmp/mysql3306.pid
datadir=/mariadb/data3306
log-error=/var/log/mariadb/3306.log
[mysqld3307]
port=3307
socket=/tmp/mysql3307.sock
pid-file=/tmp/mysql3307.pid
datadir=/mariadb/data3307
log-error=/var/log/mariadb/3307.log
[mysqld3308]
port=3308
socket=/tmp/mysql3308.sock
pid-file=/tmp/mysql3308.pid
datadir=/mariadb/data3308
log-error=/var/log/mariadb/3308.log
启动实例
注:[mysqld3306],[mysqld3307],[mysqld3308] 分别对应3306,3307,3308
# mysqld_multi --defaults-extra-file=/etc/my.cnf start 3306
# mysqld_multi --defaults-extra-file=/etc/my.cnf start 3307
# mysqld_multi --defaults-extra-file=/etc/my.cnf start 3308
查看启动的实例
# mysqld_multi --defaults-extra-file=/etc/my.cnf report
客户端登录
通过TCP/IP连接
# mysql -P3306 -hlocalhost --protocol=tcp
通过连接实例的方式(只能本地连接,不能用于远程连接)
# mysql -S /tmp/mysql3307.sock
停止实例
# mysqladmin -u root -p -S /tmp/mysql3306.sock shutdown
# mysqladmin -u root -p -S /tmp/mysql3307.sock shutdown
# mysqladmin -u root -p -S /tmp/mysql3308.sock shutdown
修改密码
# mysqladmin --no-defaults --port=3306 --user=root --protocol=tcp password '123456'
# mysqladmin --no-defaults --port=3307--user=root --protocol=tcp password '123456'
# mysqladmin --no-defaults --port=3308 --user=root --protocol=tcp password '123456'
另一种方式
# systemctl restart mariadb --skip-grant-tables --skip-networking
# mysql -e"UPDATE mysql.user SET password=password('somenewpassword') WHERE user='root'"
# systemctl restart mariadb