返回列表 发帖

CentOS8_lnmp1.7_单独安装数据库(MariaDB)

系统的版本信息:
[root@centos8 ~]# cat /etc/redhat-release
CentOS Linux release 8.2.2004 (Core)
[root@centos8 ~]#
[root@centos8 ~]# uname -r
4.18.0-193.el8.x86_64


这里安装的是MariaDB,服务器IP为 192.168.168.154/24

tar -zxvf lnmp1.7-full.tar.gz
cd lnmp1.7-full
CheckMirror=n ./install.sh db
注释:CheckMirror=n 使用本地光盘作为Yum源,不需要连公网。

选择数据库版本:
图片1.png
2021-7-9 08:50


设置数据库用户root@localhost的密码:(以下是把密码设置为 888 )
图片2.png
2021-7-9 08:50


启用InnoDB Storage Engine,输入 Y 再按回车键:
图片3.png
2021-7-9 08:51


安装或取消安装:
图片4.png
2021-7-9 08:51



下面是全自动安装的^_^ ^_^


安装成功了:(安装好后,最好重启一下服务器)
图片14.png
2021-7-9 09:09



数据库的安装日志:
[root@centos8 ~]# ls
anaconda-ks.cfg  install_database.log  lnmp1.7-full  lnmp1.7-full.tar.gz

[root@centos8 ~]# tail -5 install_database.log
Executing: /usr/lib/systemd/systemd-sysv-install enable mariadb
Clean database src directory...
MariaDB: OK
MariaDB root password: 888
Install mariadb-10.4.13 completed! enjoy it.



默认开启SELinux的:
[root@centos8 ~]# cat /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=enforcing
# SELINUXTYPE= can take one of these three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted



默认启用防火墙firewalld:
[root@centos8 ~]# systemctl is-enabled firewalld
enabled

查看firewalld所有打开的服务:
[root@centos8 ~]# firewall-cmd --zone=public --list-services
cockpit dhcpv6-client ssh

查看firewalld所有打开的端口:(默认没有打开任何端口)
[root@centos8 ~]# firewall-cmd --zone=public --list-ports

[root@centos8 ~]#


查看firewalld的配置文件:
[root@centos8 ~]# cat /etc/firewalld/zones/public.xml
<?xml version="1.0" encoding="utf-8"?>
<zone>
  <short>Public</short>
  <description>For use in public areas. You do not trust the other computers on networks to not harm your computer. Only selected incoming connections are accepted.</description>
  <service name="ssh"/>
  <service name="dhcpv6-client"/>
  <service name="cockpit"/>
</zone>



MariaDB的主配置文件:
[root@centos8 ~]# cat /etc/my.cnf |grep -v "^$"
[client]
#password   = your_password
port        = 3306
socket      = /tmp/mysql.sock
[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
user    = mariadb
basedir = /usr/local/mariadb
datadir = /usr/local/mariadb/var
log_error = /usr/local/mariadb/var/mariadb.err
pid-file = /usr/local/mariadb/var/mariadb.pid
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 8M
tmp_table_size = 16M
explicit_defaults_for_timestamp = true
#skip-networking
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_data_home_dir = /usr/local/mariadb/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mariadb/var
innodb_buffer_pool_size = 16M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout



服务器本地登录MariaDB:
mysql -uroot -p888
图片5.png
2021-7-9 08:54



查看MariaDB能同时打开的表的总数量:
MariaDB [(none)]> show variables like 'table_open%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache           | 64    |
| table_open_cache_instances | 8     |
+----------------------------+-------+
2 rows in set (0.004 sec)


查看MariaDB当前打开的表的数量:
MariaDB [(none)]> show status like 'open_table%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Open_table_definitions | 17    |
| Open_tables            | 10    |
+------------------------+-------+
2 rows in set (0.001 sec)

笺注:假如Open_tables的值比较大,或等于table_open_cache的值,就可以把table_open_cache的值调大一些。


更改MariaDB能同时打开的表的总数量:
[root@centos8 ~]# cat /etc/my.cnf |grep table_open_cache |grep -v "^$"
table_open_cache = 512

重启MariaDB:
[root@centos8 ~]# systemctl restart mariadb


再次查看MariaDB能同时打开的表的总数量:
MariaDB [(none)]> show variables like 'table_open%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache           | 512   |
| table_open_cache_instances | 8     |
+----------------------------+-------+
2 rows in set (0.001 sec)


再次查看MariaDB当前打开的表的数量:
MariaDB [(none)]> show status like 'open_table%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Open_table_definitions | 17    |
| Open_tables            | 10    |
+------------------------+-------+
2 rows in set (0.001 sec)





######

查看MariaDB的进程最大可打开文件数:
[root@centos8 ~]# mysql -uroot -p888 -e "show global variables like '%open_files_limit%';"
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+


测试MariaDB的进程最大可打开文件数,找到MariaDB的PPID:
[root@centos8 ~]# ps -ef |grep mariadb |grep -v grep
root        1231       1  0 15:13 ?        00:00:00 /bin/sh /usr/local/mariadb/bin/mysqld_safe --datadir=/usr/local/mariadb/var --pid-file=/usr/local/mariadb/var/mariadb.pid
mariadb     1570    1231  0 15:13 ?        00:00:00 /usr/local/mariadb/bin/mysqld --basedir=/usr/local/mariadb --datadir=/usr/local/mariadb/var --plugin-dir=/usr/local/mariadb/lib/plugin --user=mariadb --log-error=/usr/local/mariadb/var/mariadb.err --open-files-limit=65535 --pid-file=/usr/local/mariadb/var/mariadb.pid --socket=/tmp/mysql.sock --port=3306

cat /proc/1231/limits
图片6.png
2021-7-9 08:57




操作系统的“进程最大可打开文件数”的查询方法:
[root@centos8 ~]# ulimit -n
1024


修改操作系统的“进程最大可打开文件数”:
[root@centos8 ~]# tail -5 /etc/security/limits.conf
# End of file
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535


重启服务器:
[root@centos8 ~]# init 6

重启服务器后,再次查看操作系统的“进程最大可打开文件数”:
[root@centos8 ~]# ulimit -n
65535





######

查看所有的库:(以下是初始状态)
图片7.png
2021-7-9 08:58



查看所有数据库用户及其主机信息:(以下是初始状态)
select user,host from mysql.user;
图片8.png
2021-7-9 08:59



查看数据库管理员root@localhost的权限:
MariaDB [(none)]> show grants for root@localhost;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING '*41DDB5DFD213B288EE050BD64DC6AA36815A3486' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                             |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)



###
自定义一个数据库用户并授权:

授权用户zhuohua@'%'(密码886),可以从任意IP访问,可以对所有的库进行任意操作:
MariaDB [(none)]> grant all on *.* to zhuohua@'%' identified by '886';
Query OK, 0 rows affected (0.402 sec)

查看用户zhuohua@'%' 的权限:
MariaDB [(none)]> show grants for zhuohua@'%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@%                                                                                            |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `zhuohua`@`%` IDENTIFIED BY PASSWORD '*F961C54AFEB4D281CE53D7CB8E7822890D86FFFC' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)


再次查看所有数据库用户及其主机信息:
mysql -uroot -p888 -e"select user,host from mysql.user;"
图片9.png
2021-7-9 09:00




###

为了让远程客户端可以访问数据库,要在防火墙firewalld打开TCP 3306端口:
[root@centos8 ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@centos8 ~]# firewall-cmd --reload
success

再次查看firewalld的配置文件:
[root@centos8 ~]# cat /etc/firewalld/zones/public.xml
<?xml version="1.0" encoding="utf-8"?>
<zone>
  <short>Public</short>
  <description>For use in public areas. You do not trust the other computers on networks to not harm your computer. Only selected incoming connections are accepted.</description>
  <service name="ssh"/>
  <service name="dhcpv6-client"/>
  <service name="cockpit"/>
  <port port="3306" protocol="tcp"/>
</zone>



###

Windows客户端使用SQLyog,远程访问MariaDB数据库:
图片10.png
2021-7-9 09:02


图片11.png
2021-7-9 09:02



创建一个库:
CREATE DATABASE db1;
图片12.png
2021-7-9 09:03


SHOW CREATE DATABASE db1;
图片13.png
2021-7-9 09:03

注释:数据库的默认字符集是 utf8mb4_general_ci





相关文章:
CentOS8_lnmp1.7_LNMPA
MariaDB的最大连接数/最大可打开表数/最大可打开文件数

CentOS8_lnmp1.7_升级MySQL/MariaDB
CentOS8防火墙(firewalld)

使用Navicat远程管理MariaDB
使用SQLyog远程管理MySQL

返回列表