ubuntu下安装mysql及操作

2019独角兽企业重金招聘Python工程师标准>>>

安装

sudo apt-get install mysql-server mysql-client

未出现错误则安装完成。

修改相关配置及root密码

配置远程连接

修改 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件，找到 bind-address，修改如下：

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
# 允许远程连接
bind-address            = 0.0.0.0

授权 root 远程连接，执行下列sql：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

mysql> select host, user, authentication_string from user;
+-----------+------------------+-------------------------------------------+
| host      | user             | authentication_string                     |
+-----------+------------------+-------------------------------------------+
| localhost | root             |                                           |
| localhost | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | *42276B9FD61650D300927D4A0A6EFFA93BBFD11D |
| %         | root             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-----------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)

修改字符集

在 /etc/mysql/conf.d/mysql.cnf 配置文件，添加如下：

[mysql]
default-character-set=utf8

在 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件，添加如下：

[mysqld]
character-set-server=utf8

查看：

mysql> show variables like '%character%';
+--------------------------+----------------------------+
| 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)

修改root密码

使用 mysqladmin

sudo mysqladmin -u root password newpassword

重启

sudo service mysql restart

尝试远程连接：

➜  ~ mysql -h192.168.8.106 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.25-0ubuntu0.18.04.2 (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> \s
--------------
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

Connection id:          15
Current database:
Current user:           root@192.168.8.106
SSL:                    Not in use
Current pager:          less
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.25-0ubuntu0.18.04.2 (Ubuntu)
Protocol version:       10
Connection:             192.168.8.106 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 2 hours 11 min 3 sec

Threads: 5  Questions: 818  Slow queries: 0  Opens: 314  Flush tables: 1  Open tables: 295  Queries per second avg: 0.104
--------------

到此 远程连接、字符集、root密码均配置完成，可以愉快的玩耍了。

转载于:https://my.oschina.net/who7708/blog/3022228