MySQL数据库_安装配置：
1.安装数据库Mariadb服务，并加入开机自启：
[root@localhost ~]# yum install -y mariadb-server #Centos7默认不支持MySQL，而是支持mariadb，它是MySQL的一个分支
[root@localhost ~]# systemctl start mariadb #启动mariadb
[root@localhost ~]# systemctl enable mariadb #开机自启mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
2.数据库Mariadb安全设定：
[root@localhost ~]# mysql_secure_installation #mariadb安全设定
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): #当前root密码，还未设置，输入为空
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 #是否设定root用户密码
New password: #971224
Re-enter new password: #971224
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用户远程登录
... skipping.
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 #是否移出测试数据库
- 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!
3.连接数据库Mariadb服务：
mysql命令：mysql命令是MySQL数据库服务器的客户端工具，它工作在命令行终端中，完成对远程MySQL数据库服务器的操作。
格式: mysql [参数] [数据库]
常用参数：
-
-h：MySQL服务器的ip地址或主机名
-
-P：Port默认是3306端口，在防火墙中服务名称为mysql
-
-u：连接MySQL服务器的登录用户，-u后面可以紧跟着用户，也可以有空格
-
-p：连接MySQL服务器的登录用户密码，-p后面必须紧跟着密码，没有空格
-
-e：执行MySQL内部命令
-
-D：指定登录到哪个数据库
远程登录：
[root@localhost ~]# mysql -h 192.168.32.143 -u root -p971224 #连接远程MySQL服务器
本地登录：
[root@localhost ~]# mysql -h localhost -u root -p #连接本地MySQL服务器，并打开数据库
[root@localhost ~]# mysql -h 172.0.0.0 -u root -p
Enter password:971224
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
退出登录：
MariaDB [(none)]> quit;
Bye
MariaDB [(none)]> exit;
Bye
MariaDB [(none)]> Ctrl-C -- exit!
Aborted
4.SQL语句：
（1）查询当前用户：
MariaDB [(none)]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)（2）设置用户密码：
MariaDB [(none)]> set password=password('971224');
Query OK, 0 rows affected (0.00 sec)（3）显示有哪些数据库：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)（4）使用mysql数据库：
MariaDB [(none)]> use mysql;
Database changed（5）查询mysql数据库有哪些表：
MariaDB [mysql]> show tables;
+---------------------------+
| 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)（6）创建用户并设置密码：
用法：create user '用户'@'[具体地址|域名|网段|%]' identified by '密码';
MariaDB [(none)]> create user yemao identified by '971224';
Query OK, 0 rows affected (0.00 sec)（7）查询数据库所有权限：
MariaDB [(none)]> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table | #修改表
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures | #更改或删除已存储的函数/过程
| Create | Databases,Tables,Indexes | To create new databases and tables | #创建新的数据库和表
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE | #使用创建函数/过程
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE | #使用创建临时表
| Create view | Tables | To create new views | #创建新视图
| Create user | Server Admin | To create new users | #创建新用户权限
| Delete | Tables | To delete existing rows | #删除现存行
| Drop | Databases,Tables | To drop databases, tables, and views | #删除数据库、表和视图
| Event | Server Admin | To create, alter, drop and execute events | #创建、更改、删除和执行事件
| Execute | Functions,Procedures | To execute stored routines | #执行存储的例程
| File | File access on server | To read and write files on the server | #读取和写入服务器上的文件
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess | #将您所拥有的特权授予其他用户
| Index | Tables | To create or drop indexes | #创建或删除索引
| Insert | Tables | To insert data into tables | #将数据插入表中
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) | #使用锁表(以及选择特权)
| Process | Server Admin | To view the plain text of currently executing queries | #查看当前执行的纯文本
| Proxy | Server Admin | To make proxy user possible | #使代理用户成为可能
| References | Databases,Tables | To have references on tables | #在表上有引用
| Reload | Server Admin | To reload or refresh tables, logs and privileges | #重新加载或刷新表、日志和特权
| Replication client | Server Admin | To ask where the slave or master servers are | #询问从服务器或主服务器的位置
| Replication slave | Server Admin | To read binary log events from the master | #从主服务器读取二进制日志事件
| Select | Tables | To retrieve rows from table | #从表中检索行
| Show databases | Server Admin | To see all databases with SHOW DATABASES | #查看所有数据库
| Show view | Tables | To see views with SHOW CREATE VIEW | #查看视图与显示创建视图
| Shutdown | Server Admin | To shut down the server | #关闭服务器
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. | #使用杀死线程，设置全局变量，更改主变量等
| Trigger | Tables | To use triggers | #使用触发器
| Create tablespace | Server Admin | To create/alter/drop tablespaces | #创建/修改/删除表空间
| Update | Tables | To update existing rows | #更新现存行
| Usage | Server Admin | No privileges - allow connect only | #没有特权-只允许连接
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)
190
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
