系统运维-22-4-MySQL基础应用

最新推荐文章于 2023-08-25 07:50:07 发布

ligan1115

最新推荐文章于 2023-08-25 07:50:07 发布

阅读量298

点赞数

分类专栏： DEVOPS 文章标签： sql mysql_secure_installation 安全初始化 help show

本文链接：https://blog.csdn.net/ligan1115/article/details/89918928

版权

本文介绍了MariaDB的特性，包括插件式存储引擎和多种存储引擎，以及如何在Centos 6上进行安装和配置。此外，还详细阐述了MariaDB的程序组成和常用命令，以及如何通过mysql_secure_installation进行安全初始化。最后展示了初始化后的简单信息查询实例。

摘要由CSDN通过智能技术生成

MariaDB的特性：
   插件式存储引擎：存储管理器有多种实现版本，功能和特性可能均略有差别，用户可根据需要灵活选择
   存储引擎也称为“表类型”
   1）更多的存储引擎
       MyISAM -- Aria
       InnoDB -- XtraDB
   2）诸多扩展和新特性
   3）提供了较多的测试组件
   4）承诺开源

安装和使用MariaDB
   Centos 7直接提供
   Centos 6:
       通用二进制格式安装过程
           1）准备数据目录
               以/mydata/data为例子
           2）配置mariadb
               # groupadd -r -g 306 mysql
               # useradd -r -g 306 -u 306 mysql
               # tar xf mariadb-VERSION.tar.xz -C /usr/local
               # ln -sv
               # cd /usr/local/mysql
               # chown -R root:mysql ./*
               # scripts/mysql_install_db --datadir=/mydata/data --user=mysql
               # cp supper-files/mysql.server /etc/rc.d/init.d/mysqld
               # chkconfig --add mysqld
           3）准备配置文件
               配置格式：类ini格式，为各程序均通过单个配置文件提供配置信息
                   [prog_name]
               配置文件查找次序
                   /etc/my.cnf -- /etc/mysql/my.cnf -- --default-extra-file=/PATH/TO/CONF_FILE -- ~/.my.cnf

# mkdir /etc/mysql
# cp support-files/my-large.cnf /etc/mysql/my.cnf

               添加三个选项
               datadir = /mydata/data
               innodb_file_per_table = on
               skip_name_resolve = on

   MariaDB的程序组成
       C：
           mysql，CLI交互式客户端程序
           mysqldump，mysqladmin
       S：
           mysqld_safe
           mysqld
           mysqld_multi

   服务器监听的两种socket地址：
       ip socket：监听在tcp的3306端口，支持远程通信
       unix-sock：监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）,仅仅支持本地通信
           server：localhost，127.0.0.1

   命令行交互式客户端程序：mysql
       mysql
           -uUSERNAME 用户名，默认为root
           -hHOST 服务器主机，默认为localhost
           -pPASSWORD 用户的密码，默认为空密码
           注意：mysql用户账号由两部分组成：‘USERNAME’@‘HOST’
           其中HOST用于限制此用户可通过哪些主机远程连接mysql服务
               支持使用通配符
                   %：匹配任意长度的任意字符
                   _：陪陪任意单个字符

           命令：
               客户端命令：本地执行
                   mysql>help
                   每个命令都完整形式和简写格式
                       status, \s
               服务端命令：通过mysql协议发送服务器执行并取回结果
                   每个命令都必须命令结束符号，默认为分好

       关系型数据库的常见组件
           数据库：database
           表：table
               行：row
               列：column
           索引：index
           视图：view
           用户：user
           权限：privilege
           存储过程：procedure
           存储函数：function
           触发器：trigger
           事件调度器：event scheduler

查看用户和密码发现存在空密码的实例：
   [root@lab1 ~]# systemctl start mariadb
   [root@lab1 ~]# mysql -uroot -pRedhat
   Welcome to the MariaDB monitor. Commands end with ; or \g.
   Your MariaDB connection id is 3
   Server version: 5.5.60-MariaDB MariaDB Server

mysql_secure_installation 安全初始化实例：

   [root@lab1 ~]# rpm -ql mariadb-server | grep secure
   /usr/bin/mysql_secure_installation
   /usr/share/man/man1/mysql_secure_installation.1.gz
   [root@lab1 ~]# /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

   In order to log into MariaDB to secure it, we'll need the current
   password for the root user. If you've just installed MariaDB, and
   you haven't set the root password yet, the password will be blank,
   so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

   Change the root password? [Y/n] y
   New password:
   Re-enter new password:
   Password updated successfully!
   Reloading privilege tables..
   ... Success!

   By default, a MariaDB installation has an anonymous user, allowing anyone
   to log into MariaDB without having to have a user account created for
   them. This is intended only for testing, and to make the installation
   go a bit smoother. You should remove them before moving into a
   production environment.

Remove anonymous users? [Y/n] n
... skipping.

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
... skipping.

   By default, MariaDB comes with a database named 'test' that anyone can
   access. This is also intended only for testing, and should be removed
   before moving into a production environment.

   Remove test database and access to it? [Y/n]
   - Dropping test database...
   ... Success!
   - Removing privileges on test database...
   ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...

All done! If you've completed all of the above steps,