学习笔记之mariadb的入门操作
来源：互联网 发布：知乎神州普惠待遇 编辑：程序博客网 时间：2024/05/20 13:17
由于CentOS 7 将MySQL全部都改成了mariadb。所以在CentOS 下yum安装mysql是没有用的。虽然还是有一大堆软件包叫做mysql。
- mysql-community-release.noarch el7-5 installed
- php-mysql.x86_64 5.4.16-23.el7_0.3 @RHEL
- akonadi-mysql.x86_64 1.9.2-4.el7 RHEL
- dovecot-mysql.x86_64 1:2.2.10-4.el7_0.1 RHEL
- libdbi-dbd-mysql.x86_64 0.8.3-16.el7 RHEL
- mysql-connector-java.noarch 1:5.1.25-3.el7 RHEL
- mysql-connector-odbc.x86_64 5.2.5-6.el7 RHEL
- qt-mysql.i686 1:4.8.5-8.el7 RHEL
- qt-mysql.x86_64 1:4.8.5-8.el7 RHEL
不过不用担心。mariadb和mysql几乎是一样的。首先，mariadb就是由mysql的创始人负责维护的。而mariadb就是mysql创始人女儿的名字。
首先第一步，安装mariadb！！！
1.安装mariadb
- <span style="font-size:18px;">[root@ns ftp]# yum -y install mariadb mariadb-server
- 已加载插件：product-id, subscription-manager
- This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
- </span>
2.改密码
下面的操作之前一定要记得先启动mariadb
systemctl start mariadb
注意，这里的root和Linux的root没半毛钱关系.maria的初始密码默认是空的。需要你改一下的。不改也能登陆，就是不安全而已，你的库不要密码就能登陆，很可怕
改密码
- [root@ns ~]# mysqladmin -uroot -p password zhelitianmima
- Enter password:
- [root@ns ~]#
登陆mariadb
补充一点。mysql的密码都在mysql这个库里面，有一张表叫user，这里管理了可以登陆数据库的用户
- <span style="font-size:18px;">MariaDB [(none)]> SHOW TABLES FROM mysql;
- +---------------------------+
- | Tables_in_mysql |
- +---------------------------+
- | columns_priv |
- | db |
- | event |
- | func |
- | general_log |
- | help_category |
- | help_keyword |
- | help_relation |
- | help_topic |
- | host |
- | ndb_binlog_index |
- | plugin |
- | proc |
- | procs_priv |
- | proxies_priv |
- | servers |
- | slow_log |
- | tables_priv |
- | time_zone |
- | time_zone_leap_second |
- | time_zone_name |
- | time_zone_transition |
- | time_zone_transition_type |
- | user |
- +---------------------------+
- 24 rows in set (0.00 sec)</span>
- <span style="font-size:18px;">
- </span>
- <span style="font-size:18px;">MariaDB [(none)]> SELECT User,Host,Password FROM mysql.user;
- +------+-----------+-------------------------------------------+
- | User | Host | Password |
- +------+-----------+-------------------------------------------+
- | root | localhost | *3C0631D77CED7755AD5677173D2155BE7D1E514F |
- | root | ns.server | |
- | root | 127.0.0.1 | |
- | root | ::1 | |
- | | localhost | |
- | | ns.server | |
- | suse | % | *45AC16EC077811822DE5C6C4DDB499641E9C817E |
- +------+-----------+-------------------------------------------+
- 7 rows in set (0.00 sec)</span>
- [root@ns ~]# 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!
介绍几个基本的命令
- show databases; 显示数据库
- use mysql; 进入数据库
- show tables; 显示数据库中的表
- desc user; 查看user表的数据结构
- flush privileges; 刷新数据库信息
- select host.user,password from user; 查询user表中的host，user，password字段
create database westos; 创建westos数据库
use westos;
create table linux( 创建表，username，password字段
username varchar(15) not null,
password varchar(15) not null
);
select * from mysql.user; 查询mysql库下的user表中的所以
alter table linux add age varchar(4); 添加age字段到linux表中
ALTER TABLE linux DROP age 删除age字段
ALTER TABLE linux ADD age VARCHAR(5) AFTER name 在name字段后添加字段age
有个问题。要是忘了数据库的超级用户密码怎么办呢
- <span style="font-size:18px;">[root@ns ~]# systemctl stop mariadb
- [root@ns ~]# mysqld_safe --skip-grant-tables &>/dev/null &
- [3] 2660
- [root@ns ~]# mysql -u root
- Welcome to the MariaDB monitor. Commands end with ; or \g.
- Your MariaDB connection id is 1
- Server version: 5.5.41-MariaDB MariaDB Server
-
- Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
-
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
-
- MariaDB [(none)]>
-
- </span>
接下来就改mysql那个数据库里的user下的root的密码吧
就是这个sql语句（我将密码改为了123，放心是加密的，不信看看）
- MariaDB [(none)]> SELECT User,Host,Password FROM mysql.user;
- +------+-----------+-------------------------------------------+
- | User | Host | Password |
- +------+-----------+-------------------------------------------+
- | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | ns.server | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | 127.0.0.1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | ::1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | | localhost | |
- | | ns.server | |
- | suse | % | *45AC16EC077811822DE5C6C4DDB499641E9C817E |
- +------+-----------+-------------------------------------------+
- 7 rows in set (0.00 sec)
- [root@ns etc]# nmap localhost
-
- Starting Nmap 6.40 ( http://nmap.org ) at 2016-11-27 22:24 CST
- Nmap scan report for localhost (127.0.0.1)
- Host is up (0.000026s latency).
- Other addresses for localhost (not scanned): 127.0.0.1
- Not shown: 995 closed ports
- PORT STATE SERVICE
- 21/tcp open ftp
- 22/tcp open ssh
- 25/tcp open smtp
- 80/tcp open http
- 3306/tcp open mysql
-
- Nmap done: 1 IP address (1 host up) scanned in 0.12 seconds
改了之后退出来杀掉所有跟mysql相关的进程。咋杀我就不说了阿
那我要是不想别人远程登陆我的数据库怎么办呢
修改/etc/my.cnf 在第11行加上skip-networking=1，再重启mysql。
systemctl restart mariadb
- 1 [mysqld]
- 2 datadir=/var/lib/mysql
- 3 socket=/var/lib/mysql/mysql.sock
- 4 # Disabling symbolic-links is recommended to prevent assorted security risks
- 5 symbolic-links=0
- 6 # Settings user and group are ignored when systemd is used.
- 7 # If you need to run mysqld under a different user or group,
- 8 # customize your systemd unit file for mariadb according to the
- 9 # instructions in http://fedoraproject.org/wiki/Systemd
- 10
- 11 skip-networking=1
- 12 [mysqld_safe]
- 13 log-error=/var/log/mariadb/mariadb.log
- 14 pid-file=/var/run/mariadb/mariadb.pid
- 15 #
- 16 # include all files from the config directory
- 17 #
- 18 !includedir /etc/my.cnf.d
- 19
关于数据库的sql语句就不在这里细说了。直接说如何直接以图形的方式管理数据库吧
1.现在网上下一个phpMyAdmin-3.4.0-all-languages.tar.bz2这个软件包
2.再在yum安装PHP php-mysql http 这3款软件
3.在/var/www/html下解压缩phpMyAdmin
4.在phpMyAdmin-3.4.0-all-languages这个目录中的config.sample.inc.php下找个
$cfg['blowfish_secret'] = ‘随便填个值’ 并将config.sample.inc.php 改名为config.inc.php
关闭防火墙。
得到上面的图形
以图形方式管理数据库
0 0
