配置与管理数据库服务器(MariaDB)

配置与管理数据库服务器(MariaDB)

数据库管理系统（DBMS）：用户与操作系统（os）之间的一层数据管理软件

1，mariadbc的安装

[root@localhost ~]# yum -y install mariadb-server mariadb-client
[root@localhost ~]# rpm -qa |grep mariadb
mariadb-5.5.68-1.el7.x86_64
mariadb-server-5.5.68-1.el7.x86_64
mariadb-libs-5.5.68-1.el7.x86_64
#启动mariadb数据库
[root@localhost ~]# systemctl start mariadb
#设置开机自启
[root@localhost ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
#查看开机自启
[root@localhost ~]# systemctl list-unit-files |grep mariadb
mariadb.service                               enabled 
//enable:自启		//disabled：不自启
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2，mariadb的配置

1	#刚开始是无密码登录
[root@localhost ~]# mysql -uroot -p
2	#配置mariadb安全设置
[root@localhost ~]# 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 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): 		#直接回车
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: 						#1
Re-enter new password: 				#1
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] n		#不删除远程连接
 ... 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		#删除test库和对test库的访问权限
 - 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的字符集
查看/etc/my.cnf.d中的includedir /etc/my.cnf.d,说明配置文件中引入改该目录下的配置文件
[root@localhost ~]# vim /etc/my.cnf.d/server.cnf 
[mysqld]
init_connect=SET collation_connection=utf8_unicode_ci`
init_connect=SET NAMES utf8`
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
（2）配置/etc/my.cnf.d/client.cnf
[root@localhost ~]# vim /etc/my.cnf.d/client.cnf
[client]
default-character-set=utf8
(3)配置/etc/my.cnf.d/mysql-clients.cnf 
[root@localhost ~]# vim /etc/my.cnf.d/mysql-clients.cnf 
[mysql]
default-character-set=utf8
(4)重启mariadb服务
[root@localhost ~]# systemctl restart mariadb
（5）进入mariadb查看字符集
MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)


4	#为其他主机远程连接数据库开放访问权限
MariaDB [(none)]> use mysql;
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 [mysql]> grant all privileges on *.* to root@"%" identified by "1";		#%任何主机都可以连数据库
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select host,user,password from user;
+-----------------------+------+-------------------------------------------+
| host                  | user | password                                  |
+-----------------------+------+-------------------------------------------+
| localhost             | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| localhost.localdomain | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| 127.0.0.1             | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| ::1                   | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| %                     | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
+-----------------------+------+-------------------------------------------+
5 rows in set (0.00 sec)
# %表示任何主机都被允许连接数据库
#登录时要关闭防火墙和Selinux服务
#查看帮助信息；help+命令字；例
MariaDB [(none)]> help create database;

#远程连接
mysql -h IP地址 -u root -p

#库的相关操作
create；show；drop；（增，查，删）

#表的相关操作
create；insert；select；update；delete；drop（增，插，查，改，删数据，删表）

#查看客户端帮助信息的命令
MariaDB [mysql]> help

#查看服务端帮助信息的命令
MariaDB [mysql]> help contents;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150