安裝
mysql
|
1
2
3
4
5
|
sudo apt-get --purge remove mysql-server mysql-common mysql-clientsudo apt-get install mysql-server mysql-common mysql-clientmysqladmin -u root password your-new-passwordsudo /etc/init.d/mysql restart |
mariadb
|
1
|
apt-get install mariadb-server |
字符集修改utf8
如果裝的mariadb, 默認(rèn)字符集已經(jīng)是utf8了。mysql則不是
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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/ |+--------------------------+----------------------------+ |
|
1
2
3
4
5
6
7
8
|
mysql> show variables like 'collation%';+----------------------+-------------------+| Variable_name | Value |+----------------------+-------------------+| collation_connection | utf8_general_ci || collation_database | latin1_swedish_ci || collation_server | latin1_swedish_ci |+----------------------+-------------------+ |
修改字符集：
|
1
|
sudo vim /etc/mysql/my.cnf |
添加以下內(nèi)容
|
1
2
3
4
|
[mysqld]collation-server = utf8_unicode_ciinit-connect='SET NAMES utf8'character-set-server = utf8 |
重啟：
|
1
|
service mysql restart |
登錄權(quán)限問題
Ubuntu18.04 安裝mysql或者mariadb之后，發(fā)現(xiàn)普通用戶和遠(yuǎn)程都沒有權(quán)限連接。
ERROR 1045: Access denied for user: 'root@localhost' (Using
password: YES)
修改了密碼也不對(duì)。然后sudo mysql -u root即可登錄。這顯然不是我們想要的。
解決方案
刪除root，重新創(chuàng)建用戶。
首先，登錄
|
1
|
sudo mysql -u root |
然后查看當(dāng)前用戶
|
1
2
3
4
5
6
7
8
9
|
SELECT User,Host FROM mysql.user;+------------------+-----------+| User | Host |+------------------+-----------+| admin | localhost || debian-sys-maint | localhost || magento_user | localhost || mysql.sys | localhost || root | localhost | |
刪除root賬號(hào)
|
1
2
|
mysql> DROP USER 'root'@'localhost';Query OK, 0 rows affected (0,00 sec) |
重新創(chuàng)建root：
|
1
2
|
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123456';Query OK, 0 rows affected (0,00 sec) |
授權(quán)
|
1
2
3
4
5
|
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) |
關(guān)于重置密碼
host為%時(shí)允許遠(yuǎn)程登錄
|
1
|
SET PASSWORD FOR root@'localhost' = PASSWORD('password'); |
or
|
1
|
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root'; |
or
|
1
2
3
|
USE mysqlUPDATE user SET Password = PASSWORD('newpwd')WHERE Host = 'localhost' AND User = 'root'; |
允許任何地方登錄
|
1
2
3
|
USE mysqlUPDATE user SET Password = PASSWORD('newpwd')WHERE Host = '%' AND User = 'root'; |
參考
https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04
https://help.ubuntu.com/community/MysqlPasswordReset
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持服務(wù)器之家。
原文鏈接：https://www.cnblogs.com/woshimrf/p/ubuntu-install-mysql.html
