Mariadb简介
MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可MariaDB的目的是完全兼容MySQL,包括API和命令行,是MySQL的替代品。
1.安装数据库
[root@dns-server named]# yum search mariadb
[root@dns-server named]# yum install mariadb-server.x86_64 下载mariadb
[root@dns-server named]# mysql_secure_installation 做好安全设定
[root@dns-server named]# vim /etc/my.cnf
11 skip-networking=1
[root@dns-server named]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.35-MariaDB MariaDB Server
Copyright © 2000, 2013, Oracle, Monty Program Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
2.数据库的管理
1)建立
SHOW DATABASES;
CREATE DATABASE kk;
USE kk;
CREATE TABLE linux(
-> username varchar(10) not null,
-> password varchar(50) not null
-> );
DESC linux;
INSERT INTO linuxVALUES (‘BABA’,‘123’);
Query OK, 1 row affected (0.00 sec)
MariaDB [kk]> SELECT * FROM linux;
±---------±---------+
| username | password |
±---------±---------+
| BABA | 123 |
±---------±---------+
1 row in set (0.00 sec)
MariaDB [kk]> INSERT INTO linux VALUES (‘yeye’,‘kk’);
Query OK, 1 row affected (0.01 sec)
MariaDB [kk]> SELECT username,password FROM linux;
±---------±---------+
| username | password |
±---------±---------+
| BABA | 123 |
| yeye | kk |
±---------±---------+
2 rows in set (0.00 sec)
2)更改文件
MariaDB [kk]> ALTER TABLE linux ADD class varchar(20);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [kk]> SELECT * FROM linux
-> ;
±---------±---------±------+
| username | password | class |
±---------±---------±------+
| BABA | 123 | NULL |
| yeye | kk | NULL |
±---------±---------±------+
2 rows in set (0.00 sec)
##添加class字段到文件最后面
MariaDB [kk]> ALTER TABLE linux DROP class;
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [kk]> SELECT * FROM linux
-> ;
±---------±---------+
| username | password |
±---------±---------+
| BABA | 123 |
| yeye | kk |
±---------±---------+
2 rows in set (0.00 sec)
MariaDB [kk]> ALTER TABLE linux ADD age varchar(20) AFTER username;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [kk]> SELECT * FROM linux
-> ;
±---------±-----±---------+
| username | age | password |
±---------±-----±---------+
| BABA | NULL | 123 |
| yeye | NULL | kk |
±---------±-----±---------+
2 rows in set (0.00 sec)
##添加age字段到username之后,不能添加到最前面,因为设置第一字段为基准点,如果添加到第一字段前文件内容会发生错乱
MariaDB [kk]> UPDATE linux SET age=(‘40’) where username=‘BABA’;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [kk]> SELECT * FROM linux
-> ;
±---------±-----±---------+
| username | age | password |
±---------±-----±---------+
| BABA | 40 | 123 |
| yeye | NULL | kk |
±---------±-----±---------+
2 rows in set (0.00 sec)
MariaDB [kk]> UPDATE linux SET age=(‘60’) where username=‘yeye’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [kk]> SELECT * FROM linux
-> ;
±---------±-----±---------+
| username | age | password |
±---------±-----±---------+
| BABA | 40 | 123 |
| yeye | 60 | kk |
±---------±-----±---------+
2 rows in set (0.00 sec)
##单独更改文件内容 对应username对应age
3)备份文件
[root@dns-server named]# mysqldump -u root -p kk > /mnt/westos.sql
Enter password: ##将数据备份到/mnt目录下
然后执行删除kk
MariaDB [(none)]> DROP DATABASE kk; ##将kk数据库删除
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> SHOW DATABASES;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
±-------------------+
3 rows in set (0.00 sec)
方法1:vim /mnt/westos.sql
CREATE DATABASE kk;
USE kk;
[root@dns-server mnt]# mysql -u root -p < /mnt/westos.sql
Enter password:
方法2:
vim /mnt/westos.sql
注释掉刚才写好的两行
[root@dns-server mnt]# mysql -u root -p -e “CREATE DATABASE kk;” ##创建database
Enter password:
[root@dns-server mnt]# mysql -u root -p kk < /mnt/westos.sql
Enter password:
将westos.sql导入到kk中
4)删除文件
DELETE FROM redhat where username=‘BABA’;
DROP TABLE linux;
DROP DATABASE kk;
5)用户授权
MariaDB [(none)]> CREATE USER kk@‘localhost’ identified by ‘westos’;##建立kk用户
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT SELECT,INSERT on kk.* TO kk@localhost;
Query OK, 0 rows affected (0.00 sec) ##给用户添加授权
MariaDB [(none)]> SHOW GRANTS FOR kk@localhost; ##查看用户拥有的权力 ‘’
±----------------------------------------------------------------------------------------------------------+
| Grants for kk@localhost |
±----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘kk’@‘localhost’ IDENTIFIED BY PASSWORD '86395FF05A68EFE06838C93D89A625211ED65961’ |
| GRANT SELECT, INSERT ONkk
. TO ‘kk’@‘localhost’ |
±----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;##刷新
Query OK, 0 rows affected (0.00 sec)
测试:登陆kk用户
[root@dns-server ~]# mysql -u kk -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 5.5.35-MariaDB MariaDB Server
Copyright © 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 |
| kk |##能够看到kk DATABASE
±-------------------+
2 rows in set (0.00 sec)
Database changed
MariaDB [kk]> SELECT * FROM linux;
±---------±-----±---------+
| username | age | password |
±---------±-----±---------+
| BABA | 40 | 123 |
| yeye | 60 | kk |
±---------±-----±---------+
2 rows in set (0.00 sec)
MariaDB [(none)]> USE kk; ##进入kk
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 [kk]> INSERT INTO linux VALUES (‘LEE’,‘java’,‘123’); ##添加一份数据进去
Query OK, 1 row affected (0.00 sec)
MariaDB [kk]> SELECT * FROM linux;
±---------±-----±---------+
| username | age | password |
±---------±-----±---------+
| BABA | 40 | 123 |
| yeye | 60 | kk |
| LEE | java | 123 |
±---------±-----±---------+##能够看到数据添加成功
3 rows in set (0.00 sec)
3.图形方式处理数据库
1.下载
在软件仓库中下载一个
phpMyAdmin-3.4.0-all-languages.tar.b软件包
2.[root@dns-server mnt]# yum install php php-mysql -y
3.system restart httpd
4.[root@dns-server mnt]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/htmlctl ##解压缩软件包
5.在/var/www/html中新建一个目录/mysqladmin
[root@dns-server html]# mv phpMyAdmin-3.4.0-all-languages/ mysqladmin
测试:
在firefox中输入:http://172.25.254.115/mysqladmin
图形方式处理数据库相比较命令方式更为简单且方便。