Ubuntu 18.04 安装配置 MySQL 5.7

祈竹發表於 2019-5-5 21:45:00

Ubuntu 18.04 安装 mysql 的过程中，竟然没有让你输入秘密？！（之前在 Ubuntu 14.04 下，安装过程中会询问密码），这导致安装完 mysql 初始秘密不知道的问题。
<div class="cnblogs_code">
<pre>$ sudo apt-get install mysql-server-5.7</pre>
</div>
解决方法如下：
1）安装完成后，会生成文件 /etc/mysql/debian.cnf ，初始用户名和秘密如下 (这里密码是随机的)
<div class="cnblogs_code">
<pre>$ sudo cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!

host = localhost
user = debian-sys-maint
password = 63vIY3PtyKh10cmZ
socket = /var/run/mysqld/mysqld.sock

host = localhost
user = debian-sys-maint
password = 63vIY3PtyKh10cmZ
socket = /var/run/mysqld/mysqld.sock</pre>
</div>
2）使用这个用户名和秘密登陆 mysql
<div class="cnblogs_code">
<pre>$ mysql -h localhost -u debian-sys-maint -p
Enter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> </pre>
</div>
3）然后做如下操作（这里设置秘密为 1，读者请自行设置自己的密码）
<div class="cnblogs_code">
<pre># For mysql 5.7 mysql> update mysql.user set authentication_string=password('1') where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1Changed: 1Warnings: 1

mysql> update mysql.user set plugin='mysql_native_password';
Query OK, 1 row affected (0.00 sec)
Rows matched: 4Changed: 1Warnings: 0

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

mysql> quit;
Bye # For mysql 8.0 mysql> update mysql.user set authentication_string='' where user='root' and host='localhost'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1  Changed: 0  Warnings: 0 mysql> alter user 'root'@'localhost' identified with mysql_native_password by '1'; # (old) mysql> alter user 'root'@'localhost' identified with caching_sha2_password by '1'; # (new)</pre>
<pre>Query OK, 0 rows affected (0.13 sec) mysql> select user, authentication_string from mysql.user; +------------------+------------------------------------------------------------------------+ | user             | authentication_string                                                  | +------------------+------------------------------------------------------------------------+ | debian-sys-maint | $A$005$e_2cye'a.>9=3=?ya/BZhalDCOWADFMnjOfE2gaSMhtM1DbJzQYI/PduJ4 | | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | root             | *E6CC90B878B948C35E92B003C792C46C58C4AF40                              | +------------------+------------------------------------------------------------------------+ 5 rows in set (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.26 sec) mysql> exit Bye </pre>
</div>
4）重新启动 mysql
<div class="cnblogs_code">
<pre>$ sudo service mysql restart</pre>
</div>
5）之后就可以使用 root 登陆了
<div class="cnblogs_code">
<pre>$ mysql -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> </pre>
</div>
6）检查默认字符编码 (mysql 8.0 not need this)
<div class="cnblogs_code">
<pre>mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| 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)</pre>
</div>
　　将其改为 utf8，打开 /etc/mysql/mysql.conf.d/mysqld.cnf，在最后添加一句，
<div class="cnblogs_code">
<pre>character-set-server=utf8</pre>
</div>
　　重新启动 mysql，
<div class="cnblogs_code">
<pre>$ sudo service mysql restart</pre>
</div>
　　再次查看字符编码，
<div class="cnblogs_code">
<pre>mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| 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)</pre>
</div>
 
7）使用 mycli 自动补全 (only support python3)
 默认 mysql 命令行登陆后，输入命令不会提示及自动补全，非常麻烦，通过使用 mycli 自动补全会方便很多。
具体可参考：https://pypi.org/project/mycli/
<div class="cnblogs_code">
<pre>$ sudo pip3 install -i https://pypi.tuna.tsinghua.edu.cn/simple/ mycli</pre>
</div>
使用 mycli 启动数据库连接，
<div class="cnblogs_code">
<pre>$ mycli -h localhost -u root -p ******** ( here your DB password)</pre>
</div>
 或者，
<div class="cnblogs_code">
<pre>$ mycli -h localhost -u root # 回车，输入密码</pre>
</div>
试一试，
<div class="cnblogs_code">
<pre>MySQL root@(none):(none)> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306|
+---------------+-------+</pre>
</div>
 
8)  安装 mysql 的 python 接口 mysqlclient (Django 对此接口的支持比 mysql-connector-python 好)
具体可参考：https://pypi.org/project/mysqlclient/
<div class="cnblogs_code">
<pre>$ sudo apt-get install default-libmysqlclient-dev # (ubuntu 16.04 软件包名为 libmysqlclient-dev)
$ sudo pip3 install -i https://pypi.tuna.tsinghua.edu.cn/simple/ mysqlclient</pre>
</div>
 
CentOS 7 下的安装方法：https://www.cnblogs.com/gaowengang/p/10744746.html
本文参考网友博客：https://blog.csdn.net/qq_38737992/article/details/81090373
 
9) 可以试一下 mysql workbench 图形化管理界面
<div class="cnblogs_code">
<pre>$ sudo apt-get install mysql-workbench</pre>
</div>
 
(完)
  
来源：https://www.cnblogs.com/gaowengang/p/10816537.html

頁: [1]

琼殿技术论坛's Archiver

Ubuntu 18.04 安装配置 MySQL 5.7