@mrz1 2018-01-24T13:49:26.000000Z 字数 9446 阅读 1176

yum安装和编译安装mariadb

编译

实现多实例

yum安装方式实现多实例

1.安装和启动

安装：yum install mariadb-server
路径：rpm -ql mariadb-server
[root@centos7 ~]#ls /var/lib/mysql //默认没东西
[root@centos7 ~]#systemctl start mariadb //启动mariadb
[root@centos7 ~]#ls /var/lib/mysql   //生成数据文件  
aria_log.00000001  aria_log_control  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  performance_schema  test
配置文件
[root@centos7 ~]#cat  /etc/my.cnf
[mysqld]
port=3306 （这里以3306为例 想用什么端口在这里修改）
datadir=/var/lib/mysql //数据库路径
socket=/var/lib/mysql/mysql.sock //用于本机连接
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d

2.创建目录和修改权限

[root@centos7 ~]#mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pv //创建目录
[root@centos7 ~]#getent passwd mysql //因为是yum安装不需要创建账号
mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin
[root@centos7 ~]#chown -R mysql.mysql /mysqldb/ //修改权限

准备配置文件

[root@centos7 ~]#mysql -e 'show variables like "basedir"' //查看mysql安装目录
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| basedir       | /usr  |
+---------------+-------+
//生成三套实例
[root@centos7 ~]#mysql_install_db --datadir=/mysqldb/3306/data --user=mysql --basedir=/usr 
[root@centos7 ~]#mysql_install_db --datadir=/mysqldb/3307/data --user=mysql --basedir=/usr
[root@centos7 ~]#mysql_install_db --datadir=/mysqldb/3308/data --user=mysql --basedir=/usr
//创建配置文件 以3306为例（3307,3307文件修改下数字即可）
[root@centos7 ~]cp /etc/my.cnf /mysqldb/3306/etc/
[root@centos7 ~]#cat  /mysqldb/3306/etc/my.cnf 
[mysqld]
datadir=/mysqldb/3306/data
socket=/mysqldb/3306/socket/mysql.sock
skip_grant_tables //再启动服务忽略授权变口令(_和- 都行)添加这一行表不能修改了
symbolic-links=0
[mysqld_safe]
log-error=/mysqldb/3306/log/mariadb.log
pid-file=/mysqldb/3306/pid/mariadb.pid
[root@centos7 ~]#cat /usr/lib/systemd/system/mariadb.service  //yum安装启动的配置文件
这里我们需要自己写配置文件；以3306为例（3307,3307文件修改下数字即可）
[root@centos7 3306]#cat mysqld     
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd="centos"
cmd_path="/usr/bin"  //参考 which mysqld_safe 
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  &
      /******************************************************************
      破解口令 比上一行多 --skip_grant_tables
       ${cmd_path}/mysqld_safe --skip_grant_tables --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

第四步：设置权限并启动

[root@centos7 3306]#chmod 700 mysqld  设置权限 因为里面有密码 设置为700
[root@centos7 3306]#ll ../3308/mysqld           
-rwx------ 1 root root 1000 Jan 23 18:18 ../3308/mysqld
[root@centos7 3306]#ll ../3307/mysqld  
-rwx------ 1 root root 1000 Jan 23 18:17 ../3307/mysqld
[root@centos7 3306]#ll ../3306/mysqld  
-rwx------ 1 root root 1000 Jan 23 18:15 ../3306/mysqld
启动多实例
[root@centos7]#/mysqldb/3306/mysqld start
[root@centos7]#/mysqldb/3307/mysqld start
[root@centos7]#/mysqldb/3308/mysqld start
[root@centos7 3306]#ss -ntl //查看端口
LISTEN  0   50  *:3307  *:*                  
LISTEN  0    50    *:3308  *:*                  
LISTEN  0    50    *:3306  *:*

第五步：连接

[root@centos7 ~]#mysql //直接这样连接会出现错误 需要指定路径
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@centos7 ~]#mysql -S /mysqldb/3306/socket/mysql.sock //指定路径 这是没口令的连接
MariaDB [(none)]> show variables like "port"; //查看端口
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)
[root@centos7 ~]#mysql -S /mysqldb/3307/socket/mysql.sock -uroot -pcentos //这是有口令的连接 -u用户；-p密码

第六步：设置口令

命令：/mysqldb/3307/mysqld stop 关闭mysqldb/3307
第一种方法
[root@centos7]#mysqladmin -uroot -S /mysqldb/3308/socket//mysql.sock password 'centos' //添加口令
[root@centos7 3307]#/mysqldb/3308/mysqld stop 这里不输入口令停止不了
Stoping MySQL...
Enter password: 
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
[root@centos7 3307]#/mysqldb/3308/mysqld stop 输入正确口令成功
Stoping MySQL...
Enter password: 
第二种方法
[root@centos7 ~]#mysql -S /mysqldb/3307/socket/mysql.sock
MariaDB [(none)]> select user,password,host from mysql.user; 查看表内容
+------+----------+-------------------+
| user | password | host              |
+------+----------+-------------------+
| root |          | localhost         |
| root |          | centos7.qifei.com |
| root |          | 127.0.0.1         |
| root |          | ::1               |
|      |          | localhost         |
|      |          | centos7.qifei.com |
+------+----------+-------------------+
insert（插入） update（修改）delet（删除）
设置密码
MariaDB [(none)]> update mysql.user set password=password("centos1") where user='root'; //注意这是改的所有
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0
MariaDB [(none)]> select user,password,host from mysql.user;                          
+------+-------------------------------------------+-------------------+
| user | password                                  | host              |
+------+-------------------------------------------+-------------------+
| root | *07012D77331829FBC7415FCFE0041354CE238D41 | localhost         |
| root | *07012D77331829FBC7415FCFE0041354CE238D41 | centos7.qifei.com |
| root | *07012D77331829FBC7415FCFE0041354CE238D41 | 127.0.0.1         |
| root | *07012D77331829FBC7415FCFE0041354CE238D41 | ::1               |
|      |                                           | localhost         |
|      |                                           | centos7.qifei.com |
+------+-------------------------------------------+-------------------+
删除匿名账户
MariaDB [(none)]> drop user ''@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> drop user ''@centos7.qifei.com;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> drop user 'root'@centos7.qifei.com;       
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> drop user 'root'@'::1';  
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,password,host from mysql.user;
MariaDB [(none)]> delete from mysql.user where host="centos7.qifei.com";   这样也可以删除
Query OK, 0 rows affected (0.00 sec)
+------+-------------------------------------------+-------------------+
| user | password                                  | host              |
+------+-------------------------------------------+-------------------+
| root | *07012D77331829FBC7415FCFE0041354CE238D41 | localhost         |
| root | *07012D77331829FBC7415FCFE0041354CE238D41 | 127.0.0.1         |
+------+-------------------------------------------+-------------------+
修改完刷新生效
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

命令

MariaDB [(none)]> show databases; 数据库列表
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

编译安装多实例

第一步：创建账户

[root@centos7 ~]#useradd -r -s /sbin/nologin mysql        
[root@centos7 ~]#id mysql           
uid=988(mysql) gid=983(mysql) groups=983(mysql)
[root@centos7 ~]#getent passwd mysql
mysql:x:988:983::/home/mysql:/sbin/nologin

第二部：准备包组和需要的文件等

[root@centos7 ~]#yum groupinstall "development tools" //安装包组
[root@centos7 ~]#yum install cmake //安装cmake
[root@centos7 ~]#tar xvf mariadb-10.2.12.tar.gz //解压
[root@centos7 ~]#mkdir /mysqldb //创建数据库文件
[root@centos7 ~]#chown mysql.mysql /mysqldb/ //数据库文件权限
[root@centos7 ~]#yum install  ncurses-devel
[root@centos7 ~]#yum install gnutls-devel
[root@centos7 ~]#yum install "libaio-devel"
[root@centos7 ~]#yum install "openssl-devel"
[root@centos7 ~]#yum install libxml2-devel
以上五个包是没有安装过，如果安装了请忽略！
[root@centos7 ~]#cd mariadb-10.2.12/
[root@centos7 mariadb-10.2.12]#cmake . -DCMAKE_INSTALL_PREFIX=/app/mysql -DMYSQL_DATADIR=/mysqldb/ -DSYSCONFDIR=/etc -DMYSQL_USER=mysql -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/app/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_DEBUG=0 -DWITHOUT_MROONGA_STORAGE_ENGINE=1
[root@centos7 mariadb-10.2.12]#make && make instal

第二步：path和生成数据实例及配置文件

path
[root@centos7 mysql]#echo 'PATH=/app/mysql/bin/:$PATH' >/etc/profile.d/mysql.sh
[root@centos7 mysql]#. /etc/profile.d/mysql.sh 
生成数据实例
[root@centos7 app]#/app//mysql/scripts/mysql_install_db --basedir=/app/mysql --datadir=/mysqldb/3306/data --user=mysql
[root@centos7 app]#ll /mysqldb/          
total 0
drwxr-xr-x 3 root root 18 Jan 23 21:36 3306
配置文件
cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf
[root@centos7 app]#vim /etc/my.cnf
[mysqld]
datadir=/mysqldb/3306/data    //这里只需添加自己数据路径即可
port            = 3306
[root@centos7 app]#cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos7 app]#vim  /etc/init.d/mysqld 
添加到启动服务
[root@centos7 app]#chkconfig --add mysqld
[root@centos7 app]#chkconfig --list
mysqld  0:off   1:off   2:on    3:on    4:on    5:on    6:off
[root@centos7 app]#ll /etc/init.d/
-rwxr-xr-x  1 root root 12184 Jan 23 21:43 mysqld
[root@centos7 app]#chown -R mysql.mysql /mysqldb/3306/
[root@centos7 app]#ll -d /mysqldb/3306/               
drwxr-xr-x 3 mysql mysql 18 Jan 23 21:36 /mysqldb/3306/
添加权限
[root@centos7 app]#chgrp -R mysql /app//mysql/
[root@centos7 app]#chmod 775 /app/mysql/

第三步：启动

[root@centos7 ~]#service mysqld start //启动
Starting mysqld (via systemctl):                           [  OK  ]
[root@centos7 ~]#ss -ntl
LISTEN  0   80  :::3306 :::*  
这是3306端口已经启动 需要添加端口 多实例
[root@centos7 ~]#/app//mysql/scripts/mysql_install_db --basedir=/app/mysql --datadir=/mysqldb/3307/data --user=mysql
[root@centos7 ~]#/app//mysql/scripts/mysql_install_db --basedir=/app/mysql --datadir=/mysqldb/3308/data --user=mysql
以下操作3307、3308都需要做 只是端口数字需要改变以下
[root@centos7 ~]#vim /mysqldb/3307/my.cnf //修改和添加了这些
port            = 3307
[mysqld]
datadir=/mysqldb/3307/data
port            = 3307
socket          = /mysqldb/3307/mysql.sock
[mysql_safe]
log-error=/mysqldb/3307/mariadb.log
pid-file=/mysqldb/3307/mariadb.pid
[root@centos7 3307]#vim mysqld 
#!/bin/bash
port=3307  记得修改这个端口
mysql_user="root"
mysql_sock="${mysql_basedir}/${port}/mysql.sock" 这里也修改了一下
[root@centos7 3307]#./mysqld start 启动

yum安装和编译安装mariadb

实现多实例

yum安装方式实现多实例

编译安装多实例

内容目录