007-日常研究之MariaDB部署
一、MariaDB安装
环境：
CentOS7.9\ 10.5.15-MariaDB - MariaDB Server
1、 清理旧版本
1$$首先检查一下是否存在以前的安装包
2[root@localhost ~]# rpm -qa | grep mariadb
3mariadb-libs-5.5.68-1.el7.x86_64
4
5$$如果存在，则卸载：
6[root@localhost ~]# yum remove mariadb*
2、 添加MariaDB源
1$$官方源
2[root@localhost ~]# vim /etc/yum.repos.d/mariadb.repo
3[mariadb]
4name = MariaDB
5baseurl = http://yum.mariadb.org/10.5/centos7-amd64
6gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
7gpgcheck=1
8
9
10$$国内源（推荐）
11[mariadb]
12name = MariaDB
13baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.5/centos7-amd64/
14gpgkey = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
15gpgcheck = 1
16
17[root@localhost ~]# yum clean all && yum makecache //清除并重建缓存
3、 安装
1[root@localhost ~]# yum search mariadb //搜索
2Loaded plugins: fastestmirror
3Loading mirror speeds from cached hostfile
4 * base: mirrors.aliyun.com
5 * extras: mirrors.aliyun.com
6 * updates: mirrors.aliyun.com
7=========================================== N/S matched: mariadb ============================================
8MariaDB-backup.x86_64 : Backup tool for MariaDB server
9MariaDB-backup-debuginfo.x86_64 : Debug information for package MariaDB-backup
10MariaDB-client.x86_64 : MariaDB database client binaries
11MariaDB-client-debuginfo.x86_64 : Debug information for package MariaDB-client
12MariaDB-columnstore-engine.x86_64 : MariaDB ColumnStore storage engine
13
14[root@localhost ~]# yum list | grep mariadb //查看
15MariaDB-backup.x86_64 10.5.15-1.el7.centos mariadb
16MariaDB-backup-debuginfo.x86_64 10.5.15-1.el7.centos mariadb
17MariaDB-client.x86_64 10.5.15-1.el7.centos mariadb
18MariaDB-client-debuginfo.x86_64 10.5.15-1.el7.centos mariadb
19MariaDB-columnstore-engine.x86_64 10.5.15_5.6.4-1.el7.centos mariadb
20MariaDB-columnstore-engine-debuginfo.x86_64 10.5.15_5.6.4-1.el7.centos mariadb
21MariaDB-server.x86_64 10.5.15-1.el7.centos mariadb
22MariaDB-server-debuginfo.x86_64 10.5.15-1.el7.centos mariadb
23MariaDB-shared.x86_64 10.5.15-1.el7.centos mariadb
24MariaDB-shared-debuginfo.x86_64 10.5.15-1.el7.centos mariadb
25
26
27[root@localhost ~]# yum install MariaDB-server.x86_64 mariadb.x86_64 -y
28
29[root@localhost ~]# systemctl start mariadb
30[root@localhost ~]# systemctl enable mariadb
31Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
32[root@localhost ~]# systemctl status mariadb
33● mariadb.service - MariaDB 10.5.15 database server
34 Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
35 Drop-In: /etc/systemd/system/mariadb.service.d
36 └─migrated-from-my.cnf-settings.conf
37 Active: active (running) since Mon 2022-03-14 11:42:49 EDT; 12s ago
38 Docs: man:mariadbd(8)
39 https://mariadb.com/kb/en/library/systemd/
40 Main PID: 7070 (mariadbd)
41 Status: "Taking your SQL requests now..."
42 CGroup: /system.slice/mariadb.service
43 └─7070 /usr/sbin/mariadbd
44
45$$ 相关命令
46systemctl status mariadb # 查看状态
47systemctl start mariadb # 启动
48systemctl stop mariadb # 停止
49systemctl restart mariadb # 重启
50systemctl enable mariadb # 设置开机启动
51systemctl disable mariadb # 取消开机启动
二、初始化
1$$初始化数据库
2[root@localhost ~]# mysql_secure_installation
3首先是设置密码，会提示先输入密码
4
5Enter current password for root (enter for none):<–初次运行直接回车
6
7设置密码
8
9Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
10New password: <– 设置root用户的密码
11Re-enter new password: <– 再输入一次你设置的密码
12
13其他配置
14
15Remove anonymous users? [Y/n] <– 是否删除匿名用户，回车
16
17Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车,
18
19Remove test database and access to it? [Y/n] <– 是否删除test数据库，回车
20
21Reload privilege tables now? [Y/n] <– 是否重新加载权限表，回车
三、使用mysql
1、登录数据库
1[root@localhost ~]# mysql -uroot -p
2Enter password:
3Welcome to the MariaDB monitor. Commands end with ; or \g.
4Your MariaDB connection id is 15
5Server version: 10.5.15-MariaDB MariaDB Server
6
7Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
8
9Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10
11
12MariaDB [(none)]> show databases; //查看数据库
13+--------------------+
14| Database |
15+--------------------+
16| information_schema |
17| mysql |
18| performance_schema |
19+--------------------+
203 rows in set (0.001 sec)
2、创建数据库、数据库用户、数据库用户密码
1MariaDB [(none)]> CREATE DATABASE nextcloud_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; //创建数据库
2Query OK, 1 row affected (0.001 sec)
3
4
5MariaDB [(none)]> create user nextcloud@localhost identified by '123456789'; //创建数据库用户，允许本地登录
6Query OK, 0 rows affected (0.001 sec)
7
8
9MariaDB [(none)]> grant all privileges on nextcloud_db.* to nextcloud@localhost identified by '123456789';
10Query OK, 0 rows affected (0.001 sec) //设置用户权限为全部权限
11
12MariaDB [(none)]> flush privileges; //刷新权限
13Query OK, 0 rows affected (0.000 sec)
3、查看命令
1MariaDB [(none)]> show databases; //查看数据库
2+--------------------+
3| Database |
4+--------------------+
5| information_schema |
6| mysql |
7| nextcloud_db |
8| performance_schema |
9+--------------------+
104 rows in set (0.000 sec)
11
12MariaDB [(none)]> use mysql; //使用某个数据库
13MariaDB [mysql]>
14
15MariaDB [mysql]> show tables; //查看所有表
16+---------------------------+
17| Tables_in_mysql |
18+---------------------------+
19| column_stats |
20| columns_priv |
21| db |
22| event |
23| func |
24| general_log |
25| global_priv |
26| gtid_slave_pos |
27| help_category |
28| help_keyword |
29| help_relation |
30| help_topic |
31| index_stats |
32| innodb_index_stats |
33| innodb_table_stats |
34| plugin |
35| proc |
36| procs_priv |
37| proxies_priv |
38| roles_mapping |
39| servers |
40| slow_log |
41| table_stats |
42| tables_priv |
43| time_zone |
44| time_zone_leap_second |
45| time_zone_name |
46| time_zone_transition |
47| time_zone_transition_type |
48| transaction_registry |
49| user |
50+---------------------------+
5131 rows in set (0.001 sec)
四、配置文件my.cnf
1[root@localhost ~]# find / -name my.cnf
2/etc/my.cnf
3[root@localhost ~]# vim /etc/my.cnf
4[root@localhost etc]# vim /etc/my.cnf
5#
6# This group is read both by the client and the server
7# use it for options that affect everything
8#
9[client-server]
10
11#
12# include *.cnf from the config directory
13#
14 ncludedir /etc/my.cnf.d //子配置文件所在位置。此处只需要配置子文件即可
15
16
17[root@localhost mysql]# cd /etc/my.cnf.d
18[root@localhost my.cnf.d]# ll
19total 16
20-rw-r--r-- 1 root root 763 Feb 10 15:11 enable_encryption.preset
21-rw-r--r-- 1 root root 232 Feb 10 15:11 mysql-clients.cnf
22-rw-r--r-- 1 root root 1080 Feb 10 15:11 server.cnf
23-rw-r--r-- 1 root root 120 Feb 10 15:11 spider.cnf
24
25[root@localhost my.cnf.d]# vim server.cnf //配置服务端
26....
27[mysql]
28init_connect='SET collation_connection = utf8mb4_general_ci'
29init_connect='SET NAMES utf8mb4 '
30character-set-server=utf8mb4
31collation-server=utf8mb4_general_ci
32skip-character-set-client-handshake
33...
34
35
36[root@localhost my.cnf.d]# vim mysql-clients.cnf //配置客户端
37...
38[mysql]
39default-character-set = utf8mb4
40...
41
42
43[root@localhost ~]# systemctl restart mariadb
44
45[root@localhost my.cnf.d]# mysql -uroot -p //验证配置
46Enter password:
47
48MariaDB [(none)]> \s
49--------------
50mysql Ver 15.1 Distrib 10.5.15-MariaDB, for Linux (x86_64) using readline 5.1
51
52Connection id: 4
53Current database:
54Current user: root@localhost
55SSL: Not in use
56Current pager: stdout
57Using outfile: ''
58Using delimiter: ;
59Server: MariaDB
60Server version: 10.5.15-MariaDB MariaDB Server
61Protocol version: 10
62Connection: Localhost via UNIX socket
63Server characterset: utf8mb4
64Db characterset: utf8mb4
65Client characterset: utf8mb4
66Conn. characterset: utf8mb4
67UNIX socket: /var/lib/mysql/mysql.sock
68Uptime: 3 min 11 sec
69
70Threads: 1 Questions: 44 Slow queries: 0 Opens: 37 Open tables: 30 Queries per second avg: 0.230
五、安装数据库管理phpMyAdmin
1、 phpMyAdmin介绍
phpMyAdmin 是一个以PHP为基础，以Web-Base方式架构在网站主机上的MySQL的数据库管理工具，让管理者可用Web接口管理MySQL数据库。
2、 先决条件
1$$关闭selinux
2[root@localhost ~]# sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
3
4$$然后我们安装phpMyAdmin需要的php扩展
5[root@localhost ~]# yum install php80-php-mysqlnd.x86_64 php80-php-mbstring.x86_64 -y
6[root@localhost ~]# chown -R nginx:nginx /apps/php80/lib/php
7
8$$重启php
9root@localhost ~]# systemctl restart php-fpm.service
3、 下载phpMyAdmin源代码
1[root@localhost ~]# wget https://files.phpmyadmin.net/phpMyAdmin/5.1.3/phpMyAdmin-5.1.3-all-languages.zip --no-check-certificate
2
3$$解压下载的源代码
4[root@localhost ~]# unzip phpMyAdmin-5.1.3-all-languages.zip
5
6$$移动phpMyAdmin源代码到网页目录下并把权限设置为nginx（此处请看Nginx和PHP配置篇）
7[root@localhost ~]# mkdir -pv /apps/nginx/phpMyAdmin
8[root@localhost ~]# mv phpMyAdmin-5.1.3-all-languages/* /apps/nginx/phpMyAdmin && chown -R nginx:nginx /apps/nginx/phpMyAdmin
9
10*/$$配置phpMyAdmin的短语密码
11[root@localhost phpMyAdmin]# cp config.sample.inc.php config.inc.php
12[root@localhost phpMyAdmin]# vim config.inc.php
13$cfg['blowfish_secret'] = 'wsxEDCRFghyBJuiknmYHujikdhbgtyHU'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
14%%%'wsxEDCRFghyBJuiknmYHujikdhbgtyHU'为自己随机生成32为密码就行。
15
16[root@localhost ~]# reboot
4、在nginx配置文件中添加虚拟主机phpMyAdmin
1[root@localhost ~]# vim /apps/nginx/conf/nginx.conf
2...
3 server {
4 listen 80;
5 server_name localhost;
6 location / {
7 root html;
8 index index.php index.html index.htm;
9 }
10 error_page 500 502 503 504 /50x.html;
11 location = /50x.html {
12 root html;
13 }
14 location ~ \.php$ {
15 root html;
16 fastcgi_pass 127.0.0.1:9000;
17 fastcgi_index index.php;
18 fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
19 include fastcgi_params;
20 fastcgi_param SCRIPT_NAME $fastcgi_script_name;
21 }
22 }
23
24 server {
25 listen 81;
26 server_name localhost;
27 location / {
28 root phpMyAdmin;
29 index index.php index.html index.htm;
30 }
31 error_page 500 502 503 504 /50x.html;
32 location = /50x.html {
33 root phpMyAdmin;
34 }
35 location ~ \.php$ {
36 root phpMyAdmin;
37 fastcgi_pass 127.0.0.1:9000;
38 fastcgi_index index.php;
39 fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
40 include fastcgi_params;
41 fastcgi_param SCRIPT_NAME $fastcgi_script_name;
42 }
43 }
44
45[root@localhost ~]# systemctl restart nginx
5、配置php配置文件
1## 查看数据库mysql.sock路径
2MariaDB [(none)]> \s
3--------------
4mysql Ver 15.1 Distrib 10.5.15-MariaDB, for Linux (x86_64) using readline 5.1
5
6Connection id: 4
7Current database:
8Current user: root@localhost
9SSL: Not in use
10Current pager: stdout
11Using outfile: ''
12Using delimiter: ;
13Server: MariaDB
14Server version: 10.5.15-MariaDB MariaDB Server
15Protocol version: 10
16Connection: Localhost via UNIX socket
17Server characterset: utf8mb4
18Db characterset: utf8mb4
19Client characterset: utf8mb4
20Conn. characterset: utf8mb4
21UNIX socket: /var/lib/mysql/mysql.sock
22Uptime: 3 min 11 sec
23
24
25## 修改php配置文件php.ini
26[root@localhost ~]# cd /apps/php80/
27[root@localhost php80]# vim php.ini
28...
29pdo_mysql.default_socket= /mnt/local/mysql/mysql.sock
30mysqli.default_socket = /mnt/local/mysql/mysql.sock
31...
32
33[root@localhost ~]# systemctl restart php-f