运维笔记20 （mariaDB的基础语句）
来源：互联网 发布：淘宝禁售拉卡拉 编辑：程序博客网 时间：2024/05/16 04:10
概述：LAMP架构盛极一时，这离不开MySQL的免费与易用，但是在Oracle收购了Sun之后，很多公司开始担忧MySQL的开源前景，而最近Oracle进一步闭源的举措更是让人难以安心，众多互联网公司纷纷开始寻求MySQL的替代方案。mariaDB原是mysql的一个分支，在mysql创始人加入后，更加吸引了大众的目光。
1.安装mariaDB
我们一直使用的RHEL7的自带yum源已经有了mariadb的rpm包，我们可以使用yum非常方便的安装。
[root@localhost dhcp]# yum install mariadb-server -y
[root@localhost dhcp]# yum list all | grep mariadbmariadb-server.x86_64 1:5.5.35-3.el7 rhel_dvd
但是yum提供的mariadb的版本实在是有点低，我们去看下mariadb官网。
官网上已经出了10.2.2的beta版本，不过人家可提示你了，不要在生产环境使用beta版。这篇博客的主要目的是对常用语句的总结，就先不尝试这个的源码安装了。
2.使用mariaDB
[root@localhost dhcp]# systemctl start mariadb打开mariadb服务skip-networking=1修改mariadb配置文件使其跳过网络使用[root@localhost dhcp]# mysql_secure_installation先进行数据库安全初始化，下面会有很多文字提示你。/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not foundNOTE: 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 currentpassword for the root user. If you've just installed MariaDB, andyou 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): ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)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 MariaDBroot user without the proper authorisation.Set root password? [Y/n] yNew password:进入数据库[root@localhost dhcp]# mysql -uroot -pEnter password: Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 11Server version: 5.5.35-MariaDB MariaDB ServerCopyright (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)]>显示数据库MariaDB [(none)]> show databases -> ;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema |+--------------------+3 rows in set (0.00 sec)创建数据库MariaDB [(none)]> create database mo;
删除数据库MariaDB [(none)]> drop database mo;
使用数据库MariaDB [(none)]> use mo
创建表MariaDB [mo]> create table user ( username char(30) not null,password char(30) not null);
显示存在的表MariaDB [mo]> show tables;+--------------+| Tables_in_mo |+--------------+| user |+--------------+1 row in set (0.00 sec)显示某一个表的细节MariaDB [mo]> desc user;+----------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------+------+-----+---------+-------+| username | char(30) | NO | | NULL | || password | char(30) | NO | | NULL | |+----------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)向表中插入数据MariaDB [mo]> insert into user values("mo","123");查找表中的数据MariaDB [mo]> select * from user;+----------+----------+| username | password |+----------+----------+| mo | 123 |+----------+----------+1 row in set (0.00 sec)修改表1）给表添加一个新的列
MariaDB [mo]> alter table user add age char(5);Query OK, 1 row affected (0.38 sec) Records: 1 Duplicates: 0 Warnings: 0MariaDB [mo]> select * from user;+----------+----------+------+| username | password | age |+----------+----------+------+| mo | 123 | NULL |+----------+----------+------+1 row in set (0.00 sec)2）删除表的一列MariaDB [mo]> alter table user drop age;Query OK, 1 row affected (0.13 sec) Records: 1 Duplicates: 0 Warnings: 0MariaDB [mo]> select * from user;+----------+----------+| username | password |+----------+----------+| mo | 123 |+----------+----------+1 row in set (0.00 sec)3）在一个任意位置添加表的某一列MariaDB [mo]> alter table user add age char(5) after username;Query OK, 1 row affected (0.13 sec) Records: 1 Duplicates: 0 Warnings: 0MariaDB [mo]> select * from user;+----------+------+----------+| username | age | password |+----------+------+----------+| mo | NULL | 123 |+----------+------+----------+1 row in set (0.00 sec)更新数据库的某个数据MariaDB [mo]> update user set age=15;Query OK, 1 row affected (0.31 sec)Rows matched: 1 Changed: 1 Warnings: 0MariaDB [mo]> select * from user;+----------+------+----------+| username | age | password |+----------+------+----------+| mo | 15 | 123 |+----------+------+----------+1 row in set (0.00 sec)
查询表的数据：MariaDB [mo]> select * from user;+----------+------+----------+| username | age | password |+----------+------+----------+| mo | 15 | 123 || li | 21 | 456 |+----------+------+----------+2 rows in set (0.00 sec)这条是查询某个表的所有数据，但是现在如果我想仅查找某个数据怎么办呢？观察这个表，从几何的意义考虑如果想锁定某一个数据，需要有这个数据的横纵坐标都决定才可以MariaDB [mo]> select password from user where username="li";+----------+| password |+----------+| 456 |+----------+1 row in set (0.00 sec)想查"li"的密码，上面的语句首先用select password决定了他的纵坐标，接下来用username="li"决定了横坐标，就查找到了想要的数据。
删除表的某一行MariaDB [mo]> delete from user where username="li";Query OK, 1 row affected (0.07 sec)MariaDB [mo]> select * from user;+----------+------+----------+| username | age | password |+----------+------+----------+| mo | 15 | 123 |+----------+------+----------+1 row in set (0.00 sec)
删除表MariaDB [mo]> drop table user;Query OK, 0 rows affected (0.07 sec)MariaDB [mo]> show tables;Empty set (0.00 sec)
删库MariaDB [mo]> drop database mo;Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema |+--------------------+3 rows in set (0.00 sec)
创建数据库的用户MariaDB [mysql]> create user mo -> ;Query OK, 0 rows affected (0.00 sec)
通过查询表单知道这个新建的用户没有任何权限。
给新建用户赋予密码
MariaDB [(none)]> create user mo@localhost identified by "redhat";
给新建用户赋予权限MariaDB [(none)]> create database user;ERROR 1044 (42000): Access denied for user 'mo'@'localhost' to database 'user'新建用户尚无创建的权限既然是授权，当然只能让root用户授权了。
MariaDB [(none)]> grant create on *.* to mo@localhost;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show grants;+----------------------------------------------------------------------------------------------------------------------------------------+| Grants for root@localhost |+----------------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' WITH GRANT OPTION || GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |+----------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)
这样就给mo用户授予了create权限。MariaDB [(none)]> revoke create on *.* from mo@localhost;Query OK, 0 rows affected (0.00 sec)
去掉授予的权限。
加入数据库的root密码忘记了怎么办。
systemctl stop mariadb
mysqld_safe --skip-grant-tables &
之后就可以进入mysql不需要密码了。
进入之后修改mysql的密码
MariaDB [(none)]> update mysql.user set password=password("redhat") where user="root";Query OK, 0 rows affected (0.00 sec)Rows matched: 3 Changed: 0 Warnings: 0
这样即可修改密码，而且密码是加密的退出mysql。一定要杀掉所有mysql的进程。再重启服务。
[root@localhost ~]# ps aux | grep mysqlroot 7765 0.0 0.0 113248 1556 pts/1 S 07:42 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tablesmysql 7920 0.1 4.7 859068 89572 pts/1 Sl 07:42 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sockroot 8031 0.0 0.0 112640 936 pts/1 R+ 07:46 0:00 grep --color=auto mysql之后就能使用修改的密码登录了。
有一个软件是用php写的mysql图形化软件，搜索phpmyadmin就可以下载使用。由于yum没有提供rpm包所以我们要自己编译源码包。[root@foundation3 mnt]# lshttpd.key phpMyAdmin-4.6.5.1-all-languages phpMyAdmin-4.6.5.1-all-languages.tar.bz2红字处就是phpMyadmin软件的tar包，我们打开看看。[root@foundation3 phpMyAdmin-4.6.5.1-all-languages]# lsbrowse_foreigners.php LICENSE show_config_errors.phpbuild.xml license.php sqlChangeLog lint.php sql.phpchangelog.php locale tbl_addfield.phpchk_rel.php logout.php tbl_change.phpcomposer.json navigation.php tbl_chart.phpconfig.sample.inc.php normalization.php tbl_create.phpCONTRIBUTING.md phpmyadmin.css.php tbl_export.phpdb_central_columns.php phpunit.xml.dist tbl_find_replace.phpdb_datadict.php phpunit.xml.hhvm tbl_get_field.phpdb_designer.php phpunit.xml.nocoverage tbl_gis_visualization.phpdb_events.php prefs_forms.php tbl_import.phpdb_export.php prefs_manage.php tbl_indexes.phpdb_import.php print.css tbl_operations.phpdb_operations.php README对于这种源码安装首先要看一下他的README。phpMyAdmin is intended to handle the administration of MySQL over the web.For a summary of features, list of requirements, and installation instructions,please see the documentation in the ./doc/ folder or at https://docs.phpmyadmin.net/
根据README的这句话，我们看下https://docs.phpmyadmin.net/这个链接。根据配置文件，我们知道，安装这个还需要一个webserver
接下来再安装诸如php-mysql，php这样的环境就可以了。
1 0
- 运维笔记20 （mariaDB的基础语句）
- MariaDB SQL语句基础
- Linux运维笔记-文档总结-mariadb数据库的各项操作（全）
- 学习笔记之MariaDB基础操作
- Linux运维笔记----Mariadb数据库基本管理
- SQL基础语言笔记（超级入门的简单语句）
- javascript基础笔记（四）之js里面的语句
- Linux之MariaDB基础总结（一）
- 学习笔记之mariadb的入门操作
- 学习笔记之mariadb的入门操作
- 学习笔记之MariaDB - 关系型数据库基础
- mysql(mariadb)语句
- mariaDB语句详解
- 学习笔记---Java基础语句（四）
- PHP+MariaDB最基础的数据库操作方法备忘录
- 黑马程序员-自己总结的java学习笔记（2）java语句基础。
- 翻译：MariaDB ALTER TABLE语句
- 5.MariaDB笔记——MariaDB开发者
- 文件编程简述
- signal函数、sigaction函数及信号集(sigemptyset,sigaddset)操作函数
- SQL语句order by两个字段同时排序。
- Android编译版本eng、user和userdebug的区别
- XML的解析
- 运维笔记20 （mariaDB的基础语句）
- ORA-00031: session marked for kill 处理Oracle中杀不掉的锁
- Caused by: android.system.ErrnoException: isConnected failed: EHOSTUNREACH (No route to host)
- 第十四章
- 微信公众号开发教程[020]-wxsdk4php：微信公众号和微信支付开发的所有API功能点的封装及调用实例
- SVM的原问题和对偶问题模型
- Jsp上传组件Smartupload介绍
- 11/29上课内容
- 459. Repeated Substring Pattern*
