Never too late to learn.

0%

CentOS 安装 MySQL

​ 随着CentOS 7的发布,MySQL被从标准库中舍弃,取而代之的是MariaDB。本文记录CentOS 7上安装MySQL服务。

Step 1: 下载安装包

1
$ sudo wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

Step 2: 安装

校验md5

1
2
$ sudo md5sum mysql80-community-release-el7-3.noarch.rpm
893b55d5d885df5c4d4cf7c4f2f6c153 mysql80-community-release-el7-3.noarch.rpm

更新安装包

1
2
3
4
5
$ sudo rpm -ivh mysql80-community-release-el7-3.noarch.rpm
warning: mysql80-community-release-el7-3.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql80-community-release-el7-3 ################################# [100%]

安装

1
$ sudo yum install mysql-server

Step3: 启动和登陆登陆

1
$ sudo systemctl start mysqld

获取root临时密码

1
2
$ sudo grep 'temporary password' /var/log/mysqld.log
2019-10-11T03:12:41.106688Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: cpYJDkeMU9_Z

配置和安全

1
$ sudo mysql_secure_installation

输入root临时密码,系统将提示你密码已启用,输入新密码

接下来提示安全选项,一直Y继续

  • Remove anonymous users
  • Disallow remote root login
  • Remove test database
  • Reload privilege tables
1
$ mysql -u root -p

输入密码登陆,进入mysql命令行界面:

1
mysql>

输入\h

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
mysql> \h

For information about MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.

For server side help, type 'help contents'

Step 4: 创建用户和数据库

创建数据库

1
mysql> create database testdb;

创建用户

1
2
3
4
5
mysql> create user user_name@'%' IDENTIFIED BY 'user_passwords';
Query OK, 0 rows affected (0.05 sec)

mysql> grant all on *.* to user_name@'%';
Query OK, 0 rows affected (0.01 sec)

退出

1
> exit;

重置root密码

停止mysql服务

1
$ sudo systemctl stop mysqld

使用不需要密码的登陆方式重启

1
$ sudo mysqld_safe --skip-grant-tables &

使用root免密登陆

1
$ mysql -u root

重设root用户密码

1
2
3
4
mysql> use mysql;
mysql> update user SET PASSWORD=PASSWORD("new_password") WHERE USER='root';
mysql> flush privileges;
mysql> exit;
1
mysqladmin -u root -p'old_password' password new_password

重启mysql服务

1
sudo systemctl restart mysqld

配置文件

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
$ cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

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

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

问题:

1
2
3
4
5
6
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_passwords';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
解决方法:
mysql> SHOW VARIABLES LIKE 'validate_password%';
mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)
1
2
3
Cannot create a JSON value from a string with CHARACTER SET ‘binary’.
## 解决方法
sequel pro 导出时,取消勾选`output blob fields as hex`
参考链接:

Phoenixnap: How To Install MySQL On CentOS 7

Linode: How to Install MySQL on CentOS 7

MySQL 8.0 Reference Manual

Coffee? ☕