Board logo

标题: CentOS8_lnmp1.7_单独安装数据库（MariaDB） [打印本页]
作者: admin    时间: 2021-7-9 09:07     标题: 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 设置数据库用户root@localhost的密码：（以下是把密码设置为 888 ） 图片2.png 启用InnoDB Storage Engine，输入 Y 再按回车键： 图片3.png 安装或取消安装： 图片4.png 下面是全自动安装的^_^ ^_^ 安装成功了：（安装好后，最好重启一下服务器） 图片14.png 数据库的安装日志： [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 Public 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. 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 查看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 操作系统的“进程最大可打开文件数”的查询方法： [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 查看所有数据库用户及其主机信息：（以下是初始状态） select user,host from mysql.user; 图片8.png 查看数据库管理员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 ### 为了让远程客户端可以访问数据库，要在防火墙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 Public 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. ### Windows客户端使用SQLyog，远程访问MariaDB数据库： 图片10.png 图片11.png 创建一个库： CREATE DATABASE db1; 图片12.png SHOW CREATE DATABASE db1; 图片13.png 注释：数据库的默认字符集是 utf8mb4_general_ci 相关文章： CentOS8_lnmp1.7_LNMPA MariaDB的最大连接数/最大可打开表数/最大可打开文件数 CentOS8_lnmp1.7_升级MySQL/MariaDB CentOS8防火墙(firewalld) 使用Navicat远程管理MariaDB 使用SQLyog远程管理MySQL

图片附件: 图片1.png (2021-7-9 08:50, 170.17 KB) / 下载次数 111
http://blog.zhuohua.store/attachment.php?aid=18476&k=59348b828488133750bcde1efe0a8192&t=1714250370&sid=qfPPNY



图片附件: 图片2.png (2021-7-9 08:50, 25.62 KB) / 下载次数 112
http://blog.zhuohua.store/attachment.php?aid=18477&k=ef50ea98179f42d310d6eb51511f0659&t=1714250370&sid=qfPPNY



图片附件: 图片3.png (2021-7-9 08:51, 82.83 KB) / 下载次数 112
http://blog.zhuohua.store/attachment.php?aid=18478&k=7570a8be6d1aca257a531c21799a4dca&t=1714250370&sid=qfPPNY



图片附件: 图片4.png (2021-7-9 08:51, 65.54 KB) / 下载次数 113
http://blog.zhuohua.store/attachment.php?aid=18479&k=c44c39228f89c03d777dcc4164326d18&t=1714250370&sid=qfPPNY



图片附件: 图片5.png (2021-7-9 08:54, 100.95 KB) / 下载次数 112
http://blog.zhuohua.store/attachment.php?aid=18480&k=27ba0f80e211353a73c07f1e74b67389&t=1714250370&sid=qfPPNY



图片附件: 图片6.png (2021-7-9 08:57, 170.44 KB) / 下载次数 109
http://blog.zhuohua.store/attachment.php?aid=18481&k=f8b684f48cea0afbc5cb80e4a31d389c&t=1714250370&sid=qfPPNY



图片附件: 图片7.png (2021-7-9 08:58, 38.98 KB) / 下载次数 115
http://blog.zhuohua.store/attachment.php?aid=18482&k=23cffb3f873ce81aaba9fdeac3c8a51f&t=1714250370&sid=qfPPNY



图片附件: 图片8.png (2021-7-9 08:59, 45.16 KB) / 下载次数 108
http://blog.zhuohua.store/attachment.php?aid=18483&k=b5939634d482bc5bf365ba0c0780cc99&t=1714250370&sid=qfPPNY



图片附件: 图片9.png (2021-7-9 09:00, 48.97 KB) / 下载次数 102
http://blog.zhuohua.store/attachment.php?aid=18484&k=b762ce69cb70d25a2336103e5e27754e&t=1714250370&sid=qfPPNY



图片附件: 图片10.png (2021-7-9 09:02, 266.58 KB) / 下载次数 103
http://blog.zhuohua.store/attachment.php?aid=18485&k=14777bbe61bf6eccc9a8f34b42fecc32&t=1714250370&sid=qfPPNY



图片附件: 图片11.png (2021-7-9 09:02, 169.83 KB) / 下载次数 110
http://blog.zhuohua.store/attachment.php?aid=18486&k=f3e0ae4b6ae04e2477cabcb4e7577f04&t=1714250370&sid=qfPPNY



图片附件: 图片12.png (2021-7-9 09:03, 115.28 KB) / 下载次数 113
http://blog.zhuohua.store/attachment.php?aid=18487&k=cd05fbebdf42e97a4c689ff875f0a741&t=1714250370&sid=qfPPNY



图片附件: 图片13.png (2021-7-9 09:03, 63.85 KB) / 下载次数 115
http://blog.zhuohua.store/attachment.php?aid=18488&k=2683b2d2b37a0af13d8e289d05db2ba0&t=1714250370&sid=qfPPNY



图片附件: 图片14.png (2021-7-9 09:09, 81.34 KB) / 下载次数 95
http://blog.zhuohua.store/attachment.php?aid=18489&k=ba06e2e72ca71b568565fffe2b84bb4b&t=1714250370&sid=qfPPNY





欢迎光临 blog.zhuohua.store (http://blog.zhuohua.store/) Powered by Discuz! 7.2