欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

运维那点事4:搭建mysql(fedora20)

程序员文章站 2022-03-01 18:41:27
...

目录:

  1. 安装mysql(MariaDB);从Fedora 19以后,Fedora将mysql 替换成了mariadb。两者同源。
  2. 设置自启动;
  3. 启动服务;
  4. 配置开始 mysql_secure_installation;
  5. 自动配置后 看剩下的数据库;设置用户密码;创建数据库和对应的管理员;
  6. 设置编码
  7. 还原数据库的时候出现Access denied; you need the SUPER privilege for this operation的情况

内容:

 

  • 安装mysql(MariaDB)

#yum install mysql-server 

 

  • 设置自启动

旧命令 #chkconfig --level 3 mysqld on

新命令 #systemctrl enable mysqld.service

 

这里新版本会出现错误,此时要换成#systemctrl enable mariadb.service

 

  • 启动服务

#service mysqld start  或 #systemctrl start mysqld.service

 

  • 自动配置开始 mysql_secure_installation

到了这一步,网上很多人习惯直接mysql然后修改用户名、密码、删除测试库、测试账户、远程root登录限制等;这里推荐运行 mysql_secure_installation,每一步都有提示,甚至每步直接回车即可(还是看看提示内容好点哈);

 

  •    自动配置后

看剩下的数据库:

 

写道
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.37-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program 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)]> select user, host, password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *8FE3F0AC8CDC292A5F386E8BA3A61757EAE1E053 |
| root | 127.0.0.1 | |
| root | ::1 | |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

 

写道
MariaDB [(none)]> set password for root@'::1'=password('XXXXXXXXX');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user, host, password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *8FE3F0AC8CDC292A5F386E8BA3A61757EAE1E053 |
| root | 127.0.0.1 | *8FE3F0AC8CDC292A5F386E8BA3A61757EAE1E053 |
| root | ::1 | *8FE3F0AC8CDC292A5F386E8BA3A61757EAE1E053 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

 

创建数据库和对应的管理员;

写道
MariaDB [(none)]> create database db_name default character set utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all privileges on db_name .* to 'gzmh'@'%' identified by 'XXXXXXX';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user, host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| gzmh | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)

 

写道
GRANT SUPER ON *.* TO user@'localhost' IDENTIFIED BY 'password';

 

写道
MariaDB [(none)]> select user, host from mysql.user;
+----------+-----------+
| user | host |
+----------+-----------+
| gzmh | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| songyang | localhost |
+----------+-----------+
5 rows in set (0.00 sec)

 

 

  • 设置编码(参考http://heavengate.blog.163.com/blog/static/2023810532013312105131655/)

    先查看编码:

 

    MariaDB [(none)]>show variables like 'character%';  

 

    修改编码:

 

    到mysql 配置文件进行配置。这里fedora下的数据库mariadb和一般的mysql文件位置有区别,如fedora20下是:

写道
[root@localhost my.cnf.d]# pwd
/etc/my.cnf.d
[root@localhost my.cnf.d]# ls
client.cnf mysql-clients.cnf server.cnf
[root@localhost my.cnf.d]#

 

 但是节点都是一样的。稍微看下英语解释,了解作用域就OK哈。找好节点然后对号入座,如下:

写道
在[client]节点下添加
default-character-set=utf8
在[mysqld]节点下添加
character-set-server=utf8
collation-server=utf8_general_ci

 

  • Access denied; you need the SUPER privilege for this operation

     用navicat for mysql 进行psc文件还原的时候,如果操作到视图,会出现以下错误:

      Access denied; you need the SUPER privilege for this operation

     参考了下http://hi.baidu.com/honfei/item/b94416af1700e317a8cfb7af文章。将原先的备份文件psc进行“提取SQL”,这时查看里面内容,就会发现有 DEFINER= ‘root’@'localhost'之类的东西。只要把里面这一部分,包括后面的localhost整个,替换成你当前数据库同用户名的用户。这里要保证你写所更换的,如换成“gzabc@%”,是有权限的就OK。别出现'gzabc'@'localhos't是有权限,而'gzabc'@'%'没权限哈。