Mysql安装

版本5.7

1、下载文件

-rw-rw-r--. 1 wls81 wls81  25402568 Mar 25 14:30 mysql-community-client-5.7.25-1.el7.x86_64.rpm
-rw-rw-r--. 1 wls81 wls81    280904 Mar 25 14:29 mysql-community-common-5.7.25-1.el7.x86_64.rpm
-rw-rw-r--. 1 wls81 wls81   2271668 Mar 25 14:36 mysql-community-libs-5.7.25-1.el7.x86_64.rpm
-rw-rw-r--. 1 wls81 wls81 173130520 Mar 25 14:35 mysql-community-server-5.7.25-1.el7.x86_64.rpm

2、安装依赖包

　　#yum -y install make gcc-c++ cmake bison-devel ncurses-devel libaio libaio-devel

3、安装

rpm -ivh  mysql-community-client-5.7.25-1.el7.x86_64.rpm
rpm -ivh  mysql-community-common-5.7.25-1.el7.x86_64.rpm
rpm -ivh  mysql-community-libs-5.7.25-1.el7.x86_64.rpm
rpm -ivh  mysql-community-server-5.7.25-1.el7.x86_64.rpm

碰到问题：

file /usr/share/mysql/charsets/hebrew.xml from install of mysql-community-common-5.7.25-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64

解决:
出现以上安装错误列表的原因是：系统已经安装了其他版本的mysql-libs包和mysql数据库文件导致不兼容。

[root@localhost install-files]# yum remove mysql-libs
4、初始化

　　为了保证数据库目录为与文件的所有者为 mysql 登陆用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化

　　#mysqld --initialize --user=mysql

另外 --initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登陆后你需要设置一个新的密码，而使用 --initialize-insecure命令则不使用安全模式，则不会为 root 用户生成一个密码，这里演示使用的 --initialize 初始化的，会生成一个 root 账户密码，密码在log文件里。

　　#vim /var/log/mysqld.log

2019-04-01T07:11:18.166824Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-04-01T07:11:20.067923Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-04-01T07:11:20.408946Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-04-01T07:11:20.546091Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 59b06e5e-544d-11e9-9e94-049226c2f6c6.
2019-04-01T07:11:20.560811Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-04-01T07:11:20.562193Z 1 [Note] A temporary password is generated for root@localhost: s.E.W61k;(a6

5、启动MySQL

#service mysqld start

#mysql -u root -p

　　密码为步骤4结尾生成的密码

更改密码和用户权限

mysql> alter user 'root'@'localhost' identified by '1234567';
Query OK, 0 rows affected (0.00 sec)

mysql> 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
mysql> update user set user.Host='%' where user.User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

6、配置my.cnf

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

collation-server=utf8_unicode_ci
character-set-server = utf8
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED

max_connections = 1000
max_allowed-packet=32M

红色为新增，confluence若使用mysql数据库，上述前四个红色是必备的

7、重新登录mysql

查看编码和版本信息

mysql> show variables like "char%";
+--------------------------+----------------------------+
| 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.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.25    |
+-----------+
1 row in set (0.00 sec)

8、mysql create user


-- 查看系统用户
 select Host,User,Password from mysql.user;
 
 -- 创建一个远程用户
 create user test identified by '123456';
 
 -- 分配权限
 grant all privileges on *.* to 'test'@'%'identified by '123456' with grant option;
 
 -- 刷新mysql用户权限
 flush privileges ;
 
 -- 修改指定用户密码
 update mysql.user set password=password('新密码') where User="test" and Host="localhost";
 
 -- 删除用户
 delete from user where User='test' and Host='localhost';

知识：

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE DATABASE 的语法：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name

更改数据库的字符编码
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

 

flush privileges的两种情况

1、改密码。

2、授权超用户。

flush privileges 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。

MySQL用户数据和权限有修改后，希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令。

通常是在修改ROOT帐号的设置后，怕重启后无法再登录进来，那么直接flush之后就可以看权限设置是否生效。而不必冒太大风险。