CentOS安装Mysql（MariaDb）及配置

阿里云的空间没有续费，资源被释放了，代老师又要重启应用，这下就只有重新装系统装软件了。在这篇文章中，记录一下关于数据库Mysql的安装及配置过程。

安装Mariadb

1. 安装命令

   yum -y install mariadb mariadb-server

2. 启动

   systemctl start mariadb

   同时补充其他几个命令

   systemctl stop mariadb # 关闭服务
   systemctl restart mariadb # 重启服务
   systemctl status mariadb # 查看状态

3. 进行Mariadb相关的简单初始化配置

   mysql_secure_installation

   输入root密码，当然第一次密码为空，直接回车

   Enter current password for root (enter for none):

   设置密码

   Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
   New password: <– 设置root用户的密码
   Re-enter new password: <– 再输入一次你设置的密码

   其他配置

   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的字符集

登录数据库

$ mysql -u root -p

查看当前字符集

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

vi /etc/my.cnf

在[mysqld]后添加

character_set_server=utf8

再次查看字符集

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外网登陆权限

mysql>grant all privileges on *.* to root@'%' identified by <password> WITH GRANT OPTION;

到此为止，mysql的安装及初始化配置已经完成。

开启开机启动

systemctl enable mariadb

新版本的CentOS已经不使用chkconfig命令了

设置开机不自动启动则是

systemctl disable mariadb

查看此服务是否为开机启动

systemctl is-enabled mariadb

查看开机自启动的命令

ls /etc/systemd/system/multi-user.target.wants/

异常情况恢复

如果root自己把自己的权限弄没了，可以通过如下命令进入

# mysqld_safe --skip-grant-tables &

这时进入mysql是不需要密码的。

如何把root用户都删除了，这时通过CREATE USER命令或者GRANT命令都是没有用的。需要通过SQL语句来插入一个用户。

MariaDB [mysql]> INSERT INTO mysql.user (Host, User, Password) VALUES('localhost', 'root', PASSWORD('123456'));

然后把root用户其他所有的XXX_priv字段改为Y。这时，root用户的权限才算恢复过来了。