unix下mysql5.7的安装，远程链接，修改编码方法

1.安装
直接使用apt-get安装 ： apt-get install mysql-server-5.7
2.安装后会提示你输入初始密码

3.远程链接
安装好的mysql默认是本地链接，需要自己修改配置文件
打开mysqld.cnf文件：sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

找到bind-address哪一行，默认是：127.0.0.1，修改成：0.0.0.0，便可以通过3306端口进行远程链接

               

注意：如果服务器防火墙开启，则需要在防火墙中开启一下啊端口： sudo ufw allow 3306(基于unbutu的系统)
     如果服务器防火墙关闭，则不要进行任何操作
4.修改MySQL默认编码
mysql默认编码是latin1，不支持中文，所以要用修改配置文件的方法来达到支持中文的目的
1.输入 SHOW VARIABLES LIKE 'char%';查看mysql的编码，可以看到character-set-database和character-set-server默认编码是latin1

   

2.打开配置文件：sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf ，在lc-messages-dir= /usr/share/mysql 语句后添加 character-set-server=utf8 语句，保存退出

                 

3.打开配置文件：sudo vi /etc/mysql/conf.d/mysql.cnf ，在[mysql]后面添加default-character-set=utf8 ，保存退出

                  

4.重新启动mysql：service mysql restart
5.进入mysql，输入 SHOW VARIABLES LIKE 'char%'; 查看编码

  

安装

mysql

sudo apt-get --purge remove mysql-server mysql-common mysql-client
sudo apt-get install mysql-server mysql-common mysql-client

mysqladmin -u root password your-new-password
sudo /etc/init.d/mysql restart

mariadb

apt-get install mariadb-server

字符集修改utf8

如果装的mariadb, 默认字符集已经是utf8了。mysql则不是

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

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

修改字符集：

sudo vim /etc/mysql/my.cnf

添加以下内容

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

重启：

service mysql restart

登录权限问题

Ubuntu18.04 安装mysql或者mariadb之后，发现普通用户和远程都没有权限连接。

ERROR 1045: Access denied for user: 'root@localhost' (Using 
password: YES)

修改了密码也不对。然后sudo mysql -u root即可登录。这显然不是我们想要的。

解决方案

删除root，重新创建用户。

首先，登录

sudo mysql -u root

然后查看当前用户

SELECT User,Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| admin            | localhost |
| debian-sys-maint | localhost |
| magento_user     | localhost |
| mysql.sys        | localhost |
| root             | localhost |

删除root账号

mysql> DROP USER 'root'@'localhost';
Query OK, 0 rows affected (0,00 sec)

重新创建root：

mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0,00 sec)

授权

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0,00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,01 sec)

关于重置密码

host为%时允许远程登录

SET PASSWORD FOR root@'localhost' = PASSWORD('password');

or

UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

or

USE mysql
UPDATE user SET Password = PASSWORD('newpwd')
WHERE Host = 'localhost' AND User = 'root';

允许任何地方登录

USE mysql
UPDATE user SET Password = PASSWORD('newpwd')
WHERE Host = '%' AND User = 'root';