阿里云的空间没有续费，资源被释放了，代老师又要重启应用，这下就只有重新装系统装软件了。在这篇文章中，记录一下关于数据库Mysql的安装及配置过程。
安装Mariadb
- 安装命令
1
| yum -y install mariadb mariadb-server
|
- 启动
同时补充其他几个命令
1
2
3
| systemctl stop mariadb # 关闭服务
systemctl restart mariadb # 重启服务
systemctl status mariadb # 查看状态
|
- 进行Mariadb相关的简单初始化配置
1
| mysql_secure_installation
|
输入root密码，当然第一次密码为空，直接回车
1
| Enter current password for root (enter for none):
|
设置密码
1
2
3
| Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码
|
其他配置
1
2
3
4
| Remove anonymous users? [Y/n] <– 是否删除匿名用户，回车
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车,
Remove test database and access to it? [Y/n] <– 是否删除test数据库，回车
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，回车
|
配置Mariadb的字符集
登录数据库
查看当前字符集
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| MariaDB [(none)]> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
MariaDB [(none)]> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
|
修改字符集为utf-8
在[mysqld]后添加
1
| character_set_server=utf8
|
再次查看字符集
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| MariaDB [(none)]> show variables like '%character%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
MariaDB [(none)]> show variables like '%collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
|
设置权限
授予root外网登陆权限
1
| mysql>grant all privileges on *.* to root@'%' identified by <password> WITH GRANT OPTION;
|
到此为止，mysql的安装及初始化配置已经完成。
开启开机启动
1
| systemctl enable mariadb
|
新版本的CentOS已经不使用chkconfig命令了
设置开机不自动启动则是
1
| systemctl disable mariadb
|
查看此服务是否为开机启动
1
| systemctl is-enabled mariadb
|
查看开机自启动的命令
1
| ls /etc/systemd/system/multi-user.target.wants/
|
异常情况恢复
如果root自己把自己的权限弄没了，可以通过如下命令进入
1
| # mysqld_safe --skip-grant-tables &
|
这时进入mysql是不需要密码的。
如何把root用户都删除了，这时通过CREATE USER命令或者GRANT命令都是没有用的。需要通过SQL语句来插入一个用户。
1
| MariaDB [mysql]> INSERT INTO mysql.user (Host, User, Password) VALUES('localhost', 'root', PASSWORD('123456'));
|
然后把root用户其他所有的XXX_priv字段改为Y。这时，root用户的权限才算恢复过来了。