MYSQL安装及多实例

post thumb
Mysql
作者 Louis 发表于 2018年6月7日

安装MariaDB

yum 安装mariadb

centos7.4系统光盘带的是5.5

$yum install mariadb-server -y -q
$rpm -q --scripts mysql-server	#安装前脚本
$ll /var/lib/mysql/

centos7.4二进制安装mariadb-10.2.15

1.准备用户,解压缩包

$useradd -r  -d /data/mysqldb -s /sbin/nologin mysql
$tar xvf mariadb-10.2.15-linux-x86_64.tar.gz -C /usr/local

2.准备二进制的程序

$cd /usr/local
$ln -s mariadb-10.2.15-linux-x86_64/ mysql
$chown -R root:root /usr/local/mysql/	#修改文件的所属组
$setfacl -R -m u:mysql:rwx /usr/local/mysql/
$echo PATH=/usr/local/mysql/bin:$PATH >/etc/profile.d/mysql.sh

3.准备数据目录:

建议逻辑卷,添加新硬盘/dev/sdb

$echo '- - -' > /sys/class/scsi_host/host2/scan
$lsblk
$pvcreate /dev/sdb
$vgcreate vg0 /dev/sdb
$lvcreate -n lv_mysql -l 100%FREE vg0
$mkfs.xfs /dev/vg0/lv_mysql
$vim /etc/fstab	#增加自动挂载,避免重启挂载,mount /dev/vg0/lv_mysql /data
$mount -a			#挂载
$mkdir /data/mysqldb -pv
$chown mysql.mysql /data/mysqldb
$chmod 770 /data/mysqldb	#改变权限,更安全

4.创建数据库文件及配置文件

$cd /usr/local/mysql/
$scripts/mysql_install_db  --datadir=/data/mysqldb --user=mysql
Installing MariaDB/MySQL system tables in '/data/mysqldb' ...
OK
···  
$ll /data/mysqldb/	#生成数据库文件
$cp /etc/my.cnf /etc/my.cnf.bak
$cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf
$vim /etc/my.cnf  
[client]
socket=/tmp/mysql.sock
[mysqld]
datadir=/data/mysqldb
socket=/tmp/mysql.sock

5.启动服务及安全初始化

$cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
$chkconfig --add mysqld
$ service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]
$/user/local/mysql/bin/mysql_secure_installation	#安全加护

源码编译安装mariadb-10.2.15

1.编译前准备

$yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel -y
$yum install -y cmake
$useradd -r  -d /data/mysqldb -s /sbin/nologin mysql
$mkdir /data/mysqldb -pv
$mkdir /app/mysql -pv
$setfacl -R -m u:mysql:rwx /app/mysql
$chown mysql.mysql /data/mysqldb
$chmod 770 /data/mysqldb

2.编译安装阶段,使用cmake

$tar -xf mariadb-10.2.15.tar.gz
$cd mariadb-10.2.15/
$cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysqldb/ \
-DSYSCONFDIR=/etc \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/app/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
$make -j 3 && make install && for i in {1..5};do echo -e "\a" ;done

3.编译后的相关配置

$cd /app/mysql
$scripts/mysql_install_db  --datadir=/data/mysqldb  --user=mysql  --basedir=/app/mysql
$cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf
$vim /etc/my.cnf
[mysqld]
datadir		= /data/mysqldb
$cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld
$chkconfig --add mysqld
$service mysqld start

MariaDB简单多实例实现

创建多实例的文件目录

$yum install mariadb-server -y
$mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pv
$chown -R mysql.mysql /mysqldb/
$mysql_install_db  --datadir=/mysqldb/3306/data  --user=mysql 
$mysql_install_db  --datadir=/mysqldb/3307/data  --user=mysql
$mysql_install_db  --datadir=/mysqldb/3308/data  --user=mysql

配置相关多实例的配置文件

$vim /etc/my.cnf
[mysqld]
port=3306
datadir=/mysqldb/3306/data
socketdir=/mysqldb/3306/socket/mysql.sock
[mysqld_safe]
log-error=/mysqldb/3306/log/mariadb.log
pid-file=/mysqldb/3306/pid/mariadb.pid
$cp /etc/my.cnf /mysqldb/3306/etc/
$cp /etc/my.cnf /mysqldb/3307/etc/
$cp /etc/my.cnf /mysqldb/3308/etc/
$vim /mysqldb/3307/etc/my.cnf
:%s/3306/3307/g
$vim /mysqldb/3308/etc/my.cnf
:%s/3306/3308/g

编辑多实例的服务脚本

$vim /mysqldb/3306/mysqld  #编辑启动脚本
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/app/mysql/bin"
mysql_basedir="/mysqldb"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /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: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

$chmod 700 /mysqldb/{3306,3307,3308}/mysqld
$/mysqldb/3306/mysqld start
$/mysqldb/3307/mysqld start
$/mysqldb/3308/mysqld start
$ ss -ntl
State      Recv-Q Send-Q      Local Address:Port      Peer Address:Port              
LISTEN     0      50                      *:3307                 *:*                 
LISTEN     0      50                      *:3308                 *:*                  
LISTEN     0      128                     *:111                  *:*                  
LISTEN     0      5           192.168.122.1:53                   *:*                  
LISTEN     0      128                     *:22                   *:*                  
LISTEN     0      128             127.0.0.1:631                  *:*                  
LISTEN     0      50                      *:3306                 *:*                  
LISTEN     0      128                    :::111                 :::*                  
LISTEN     0      128                    :::22                  :::*                  
LISTEN     0      128                   ::1:631				   :::*  
$mysql -S /mysqldb/3306/socket/mysql.sock
MariaDB [(none)]> show variables like '%port%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| extra_port                          | 0     |
| innodb_import_table_FROM_xtrabackup | 0     |
| innodb_support_xa                   | ON    |
| large_files_support                 | ON    |
| port                                | 3306  |
| progress_report_time                | 5     |
| report_host                         |       |
| report_password                     |       |
| report_port                         | 3306  |
| report_user                         |       |
+-------------------------------------+-------+
10 rows in set (0.00 sec)
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
MariaDB [(none)]> update mysql.user set password=password("centos") where user="root";
MariaDB [(none)]>  flush privileges; 	#使密码生效
MariaDB [(none)]> \s  					#等价status
上一篇
hexo简易部署到云主机