学习总结所用《 Linux就该这么学 》
数据库管理系统
- MariaDB数据库管理系统由MySQL项目创始者重新研发
- MariaDB由开源社区进行维护，不受商业专利限制
- MariaDB和MySQL在性能上基本保持一致，两者的操作命令也十分相似
- 相较于MySQL，MariaDB数据库管理系统有了很多新鲜的扩展特性，例如对微秒级别的支持、线程池、子查询优化、进程报告等
初始化MariaDB服务
-
安装MariaDB服务，启动并加入开机启动项
# yum install -y mariadb mariadb-server # systemctl start mariadb # systemctl enable mariadb ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service' -
初始化数据库
mysql_secure_installation（5个步骤）# mysql_secure_installation /usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found 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. Set root password? [Y/n] y New password: (输入要为root管理员设置的数据库密码) 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] y (删除匿名账户) ... Success! 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] y (禁止root管理员从远程登录) ... Success! 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] y (删除test数据库并取消其访问权限) - 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, your MariaDB installation should now be secure. Thanks for using MariaDB!如果需要让root管理员远程访问数据库，则在初始化操作时设置允许root管理员从远程访问（n），再设置防火墙，使其放行对数据库服务程序的访问请求
数据库服务程序默认会占用3306端口，在防火墙策略中服务名称统一叫作mysql
# firewall-cmd --permanent --add-service=mysql success # firewall-cmd --reload success -
首次登录MariaDB数据库
# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 16 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>-u：指定登录的身份 -p：指定登录用户的登录密码
-
查看数据库管理系统中有哪些数据库
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)数据库命令必须以分号（;）结尾
-
修改root管理员的密码为123456，旧密码登录失败，新密码登录成功
MariaDB [(none)]> set password = password("123456"); Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> exit Bye # mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) # mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 18 Server version: 5.5.35-MariaDB MariaDB Server
管理用户以及授权
grant命令的常见格式以及解释
| 命令 | 作用 |
|---|---|
| GRANT 权限 ON 数据库.表单名称 TO 用户名@主机名 | 对某个特定数据库中的特定表单给予授权 |
| GRANT 权限 ON 数据库.* TO 用户名@主机名 | 对某个特定数据库中的所有表单给予授权 |
| GRANT 权限 ON . TO 用户名@主机名 | 对所有数据库及所有表单给予授权 |
| GRANT 权限1,权限2 ON 数据库.* TO 用户名@主机名 | 对某个数据库中的所有表单给予多个授权 |
| GRANT ALL PRIVILEGES ON . TO 用户名@主机名 | 对所有数据库及所有表单给予全部授权（需谨慎操作） |
-
使用root管理员创建数据库管理账户
lili，主机名是localhost，密码为lili123MariaDB [(none)]> create user lili@localhost identified by 'lili123'; Query OK, 0 rows affected (0.00 sec)此时，用户
lili还没有数据库的任何操作权限，甚至没法查看完整的数据库列表 -
查询账户
lili的主机名称、账户名称以及经过加密的密码值信息MariaDB [(none)]> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> SELECT HOST,USER,PASSWORD FROM user WHERE USER="lili"; +-----------+------+-------------------------------------------+ | HOST | USER | PASSWORD | +-----------+------+-------------------------------------------+ | localhost | lili | *E80B45F4C05E4CEE904BF4BF25035B25529A03A9 | +-----------+------+-------------------------------------------+ 1 row in set (0.00 sec) -
以root管理员身份授予用户
lili查询、更新、删除以及插入等权限MariaDB [mysql]> grant select,update,delete,insert on mysql.user to lili@localhost; Query OK, 0 rows affected (0.01 sec) -
查看用户
lili的权限MariaDB [mysql]> show grants for lili@localhost; +-------------------------------------------------------------------------------------------------------------+ | Grants for lili@localhost | +-------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'lili'@'localhost' IDENTIFIED BY PASSWORD '*E80B45F4C05E4CEE904BF4BF25035B25529A03A9' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO 'lili'@'localhost' | +-------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -
切换到用户
lili，查看mysql数据库以及表单user（其余表单因无权限被继续隐藏）MariaDB [mysql]> exit Bye [root@localhost ~]# mysql -u lili -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 25 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> show tables; +-----------------+ | Tables_in_mysql | +-----------------+ | user | +-----------------+ 1 row in set (0.00 sec) -
切回root管理员，移除刚才的授权，再查看用户信息
MariaDB [mysql]> exit Bye # mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 26 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> revoke select,update,delete,insert on mysql.user from lili@localhost; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show grants for lili@localhost; +-------------------------------------------------------------------------------------------------------------+ | Grants for lili@localhost | +-------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'lili'@'localhost' IDENTIFIED BY PASSWORD '*E80B45F4C05E4CEE904BF4BF25035B25529A03A9' | +-------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
创建数据库与表单
创建数据库的命令以及作用
| 用法 | 作用 |
|---|---|
| CREATE database 数据库名称。 | 创建新的数据库 |
| DESCRIBE 表单名称; | 描述表单 |
| UPDATE 表单名称 SET attribute=新值 WHERE attribute > 原始值; | 更新表单中的数据 |
| USE 数据库名称; | 指定使用的数据库 |
| SHOW databases; | 显示当前已有的数据库 |
| SHOW tables; | 显示当前数据库中的表单 |
| SELECT * FROM 表单名称; | 从表单中选中某个记录值 |
| DELETE FROM 表单名 WHERE attribute=值; | 从表单中删除某个记录值 |
-
创建数据库test，并查看数据库列表
MariaDB [(none)]> create database test; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.06 sec) -
在数据库test中创建数据表单mybook，并进行表单初始化（定义存储数据内容的结构）
MariaDB [(none)]> use test; Database changed MariaDB [test]> create table mybook (name char(15),price int,pages int); Query OK, 0 rows affected (0.02 sec)
管理表单及数据
where命令的参数及作用
| 参数 | 作用 |
|---|---|
| = | 相等 |
| <>或!= | 不相等 |
| > | 大于 |
| < | 小于 |
| >= | 大于或等于 |
| <= | 小于或等于 |
| BETWEEN | 在某个范围内 |
| LIKE | 搜索一个例子 |
| IN | 在列中搜索多个值 |
-
向数据表单mybook插入一条图书信息
MariaDB [test]> insert into mybook (name,price,pages) values ('linux','100','800'); Query OK, 1 row affected (0.00 sec) MariaDB [test]> select * from mybook; +-------+-------+-------+ | name | price | pages | +-------+-------+-------+ | linux | 100 | 800 | +-------+-------+-------+ 1 row in set (0.00 sec) -
修改数据表单mybook的price为50
MariaDB [test]> update mybook set price=50; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test]> select name,price from mybook; +-------+-------+ | name | price | +-------+-------+ | linux | 50 | +-------+-------+ 1 row in set (0.01 sec) -
删除数据表单mybook中的所有内容
MariaDB [test]> delete from mybook; Query OK, 1 row affected (0.02 sec) MariaDB [test]> select * from mybook; Empty set (0.00 sec) -
向数据表单mybook插入4条图书信息
MariaDB [test]> insert into mybook (name,price,pages) values -> ('linux1','30','500'), -> ('linux2','50','500'), -> ('linux3','80','500'), -> ('linux4','100','500'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from mybook; +--------+-------+-------+ | name | price | pages | +--------+-------+-------+ | linux1 | 30 | 500 | | linux2 | 50 | 500 | | linux3 | 80 | 500 | | linux4 | 100 | 500 | +--------+-------+-------+ 4 rows in set (0.00 sec) -
在mybook表单中查找出价格大于75元且价格不等于80元的图书
MariaDB [test]> select * from mybook where price>75 and price!=80; +--------+-------+-------+ | name | price | pages | +--------+-------+-------+ | linux4 | 100 | 500 | +--------+-------+-------+ 1 row in set (0.01 sec)
数据库的备份及恢复
-
将数据库test中的内容导出成一个文件，并保存到root管理员的家目录中
# mysqldump -u root -p test > /root/testDB.dump Enter password: # ls /root/ anaconda-ks.cfg initial-setup-ks.cfg testDB.dump-u：登录数据库的账户名称 -p：密码提示符
-
进入MariaDB数据库管理系统，彻底删除数据库test，再重新建立数据库test
MariaDB [(none)]> drop database test; Query OK, 1 row affected (0.06 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> create database test; Query OK, 1 row affected (0.00 sec) -
数据库恢复
数据库登录时在后面加数据库名可直接进入到数据库中
# mysql -u root -p test < /root/testDB.dump Enter password: # mysql -u root -p test Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | mybook | +----------------+ 1 row in set (0.00 sec) MariaDB [test]> describe mybook; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(15) | YES | | NULL | | | price | int(11) | YES | | NULL | | | pages | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
458
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
