 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


#down
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.33-1.el7.x86_64.rpm-bundle.tar
mkdir mysql-com
tar xvf mysql-5.7.33-1.el7.x86_64.rpm-bundle.tar -C mysql-com
cd mysql-com

#install
sudo yum install mysql-community-{server,client,common,libs}-*

#init
sudo systemctl start mysqld.service
sudo grep 'temporary password' /var/log/mysqld.log
sudo systemctl enable mysqld.service

#password as 123456 for test
set global validate_password_policy=LOW;
set global validate_password_length=6;
alter user 'root'@'localhost' identified by '123456';
SHOW VARIABLES LIKE 'validate_password%'; 
use mysql;
update user set host='%' where user='root';
flush privileges;
select user,host from user;


#port and ip
bind-address=0.0.0.0
port=3306
#log
log_timestamps = SYSTEM
explicit_defaults_for_timestamp=true
#user
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

3.2.Debian，这里有个坑，deb系列（Ubuntu）的好多版本默认的Mysql的root用户采用的是socket授权，需要改成密码插件授权：

 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


#down
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-server_5.7.33-1debian10_amd64.deb-bundle.tar
mkdir mysql-com
tar xvf mysql-server_5.7.33-1debian10_amd64.deb-bundle.tar -C mysql-com
cd mysql-com

#init
sudo apt-get install libaio1
sudo dpkg-preconfigure mysql-community-server_*.deb
sudo dpkg -i mysql-{common,community-client,client,community-server,server}_*.deb
sudo apt-get -f install
#user
select user,host,plugin from mysql.user;
+---------------+-----------+-----------------------+
| user          | host      | plugin                |
+---------------+-----------+-----------------------+
| root          | localhost | auth_socket           |
| mysql.session | localhost | mysql_native_password |
| mysql.sys     | localhost | mysql_native_password |
+---------------+-----------+-----------------------+
3 rows in set (0.00 sec)

#auth_socket to password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';


#port and ip
bind-address=0.0.0.0
port=3306
#log
log_timestamps = SYSTEM
explicit_defaults_for_timestamp=true

4.忘记密码

1
2
3
4
5
6


#忘记密码
skip-grant-tables
#
systemctl restart mysqld 
#use mysql;
update mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost';

5.一般我很少在Mysql全局配置文件中设置字符集，而是要采用单独创建数据库的时候指定字符集，之前hive的源数据采用utf8死的很惨很惨。。。

例如：

1

create database hql default character set utf8 collate utf8_general_ci;

1
2
3


#关于SQL的关键字和函数名等不区分大小写，但是对于数据值是否区分大小写，和字符集与校对规则有关。

#ci（大小写不敏感），cs（大小写敏感），_bin（二元，即比较是基于字符编码的值而与language无关）