[root@fudanwuxi ~]# yum install mariadb mariadb-server.x86_64
[root@fudanwuxi ~]# systemctl restart mariadb.service
[root@fudanwuxi ~]# systemctl enable mariadb.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
2、初始化
[root@fudanwuxi ~]# 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 tologinto MariaDB to secure it, we'll need the current
password forthe root user. If you've just installed MariaDB, and
you haven't setthe root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): #输入当前数据库root用户的密码,初始没有密码,直接按回车
OK, successfully used password, moving on...
Setting the root password ensures that nobody can logintothe MariaDB
root user withoutthe proper authorisation.
Set root password? [Y/n] y #为数据库root用户设置密码
New password: #第一遍密码
Re-enter new password: #第二遍密码
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
tologinto MariaDB without having to have a user account created for
them. This is intended only for testing, andto 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 atthe root password fromthe 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 toit? [Y/n] y #移除测试数据库及相关数据
- 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 oftheabove steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
3、登陆数据库(修改密码、创建用户、授权、取消授权)
修改root密码
[root@fudanwuxi ~]# mysql -u root -p #-u用来指定以数据库用户root登陆,-p用来验证用户数据库的密码
Enter password: #输入用户root的数据库密码
Welcome tothe MariaDB monitor. Commands endwith ; or \g.
Your MariaDB connection idis10
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation 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 |
| performance_schema |
+--------------------+3 rows inset (0.00 sec)
MariaDB [(none)]> set password = PASSWORD('fudanwuxi'); #修改密码为fudanwuxi
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> exit#退出数据库
Bye
[root@fudanwuxi ~]# mysql -u root -p #重新进行登陆测试
Enter password: #输入初始化设定的密码时候拒绝登陆
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@fudanwuxi ~]# mysql -u root -p #重新测试登陆
Enter password: #输入刚才修改的密码fudanwuxi,登陆成功
Welcome tothe MariaDB monitor. Commands endwith ; or \g.
Your MariaDB connection idis12
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
创建一个普通用户
[root@fudanwuxi ~]# mysql -u root -p #以数据库root用户进行登陆
Enter password:
Welcome tothe MariaDB monitor. Commands endwith ; or \g.
Your MariaDB connection idis12
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create user ajie@localhost identified by 'ajie123'; #创建一个数据库用户ajie,密码为ajie123
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit#退出root用户登陆
Bye
[root@fudanwuxi ~]# mysql -u ajie -p #使用ajie进行登陆
Enter password: #输入密码ajie123
Welcome tothe MariaDB monitor. Commands endwith ; or \g.
Your MariaDB connection idis13
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases; #查看有哪些数据库,由于权限不足,只能查看到一个数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+1 row inset (0.00 sec)
MariaDB [(none)]>
数据库普通用户提权
[root@fudanwuxi ~]# mysql -u root -p #使用数据库root用户登陆
Enterpassword:Welcome to the MariaDB monitor. Commands end with ; or \g.
YourMariaDB connection id is 14Serverversion:5.5.56-MariaDBMariaDBServerCopyright (c) 2000, 2017, Oracle, MariaDBCorporationAb and others.
Type'help;' or '\h' for help. Type'\c' to clear the current input statement.
MariaDB [(none)]> grant select,update,delete,insert ON mysql.* to ajie@localhost; #授予ajie用户查询、更新、删除、插入权限。mysql.*代表授予ajie的权限对数据库mysql下的所有表单有效
QueryOK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
[root@fudanwuxi ~]# mysql -u ajie -p #退出root,使用ajie来登陆
Enterpassword:Welcome to the MariaDB monitor. Commands end with ; or \g.
YourMariaDB connection id is 15Serverversion:5.5.56-MariaDBMariaDBServerCopyright (c) 2000, 2017, Oracle, MariaDBCorporationAb and others.
Type'help;' or '\h' for help. Type'\c' to clear the current input statement.
MariaDB [(none)]> show databases; #此时查看所有数据库的时候,可以看到有mysql了
+--------------------+
| Database |
+--------------------+
| information_schema || mysql |
+--------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> use mysql #进入使用mysql这个数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
MariaDB [mysql]> show tables; #查看数据库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.01 sec)
MariaDB [mysql]>
取消普通用户授权
[root@fudanwuxi ~]# mysql -u root -p #登陆root用户
Enter password:
Welcome tothe MariaDB monitor. Commands endwith ; or \g.
Your MariaDB connection idis17
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation 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.* from ajie@localhost; #移除刚才授予ajie的权限
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for ajie@localhost; #查看用户ajie的权限,已经没有了之前授予的权限
+-------------------------------------------------------------------------------------------------------------+
| Grants for ajie@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ajie'@'localhost' IDENTIFIED BY PASSWORD '*A65FBC245EF7CC4346000B7CFA058E0D5A234219' |
+-------------------------------------------------------------------------------------------------------------+1 row inset (0.00 sec)
MariaDB [(none)]> exit#退出
Bye
[root@fudanwuxi ~]# mysql -u ajie -p #使用ajie登陆
Enter password:
Welcome tothe MariaDB monitor. Commands endwith ; or \g.
Your MariaDB connection idis19
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases; #查看数据库,看不到之前授予权限时候能看到的mysql数据库了
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+1 row inset (0.00 sec)
MariaDB [(none)]>
4、创建数据库及表单(查找、修改、删除)
创建一个数据库wechat
[root@fudanwuxi ~]# mysql -u root -p #以root登陆
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation 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 |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create database wechat; #新增一个数据库,名称为wechat。删除使用drop。
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases; #再查看数据库,多了一个wechat
+--------------------+| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wechat |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
[root@fudanwuxi ~]# 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.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation 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 |
| performance_schema |
| wechat |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use wechat
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 [wechat]> show tables;
+------------------+| Tables_in_wechat |
+------------------+| myid |
+------------------+
1 row in set (0.00 sec)
MariaDB [wechat]> select * from myid;
+---------------+----------+-------+| name | address | price |
+---------------+----------+-------+
| jschinamobile | jiangsu | 999 |
| www.wuxier.cn | jiangsu | 555 |
| fudan | shanghai | 1000 |
| ajie | dongtai | 666 |
+---------------+----------+-------+
4 rows in set (0.00 sec)
MariaDB [wechat]> delete from myid; #删除
Query OK, 4 rows affected (0.01 sec)
MariaDB [wechat]> select * from myid; #再查询表单myid中数据为空
Empty set (0.00 sec)
恢复数据库
[root@fudanwuxi ~]# mysql -u root -p wechat < /root/backup_wechat.dump #输入重定向来导入数据wechat进行还原
Enter password:
[root@fudanwuxi ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation 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 |
| performance_schema |
| wechat |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use wechat;
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 [wechat]> show tables;
+------------------+| Tables_in_wechat |
+------------------+| myid |
+------------------+
1 row in set (0.00 sec)
MariaDB [wechat]> select * from myid; #查看表单myid中的信息,还原成功
+---------------+----------+-------+| name | address | price |
+---------------+----------+-------+
| jschinamobile | jiangsu | 999 |
| www.wuxier.cn | jiangsu | 555 |
| fudan | shanghai | 1000 |
| ajie | dongtai | 666 |
+---------------+----------+-------+
4 rows in set (0.02 sec)