搬瓦工中文网
安装
mysql
| 1 2 3 4 5 | 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
| 1 | apt-get install mariadb-server |
字符集修改utf8
如果装的mariadb, 默认字符集已经是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 |
添加以下内容
| 1 2 3 4 | [mysqld] collation-server = utf8_unicode_ci init-connect='SET NAMES utf8' character-set-server = utf8 |
重启：
| 1 | service mysql restart |
登录权限问题
Ubuntu18.04 安装mysql或者mariadb之后，发现普通用户和远程都没有权限连接。
ERROR 1045: Access denied for user: ‘root@localhost’ (Using
password: YES)
修改了密码也不对。然后sudo mysql -u root即可登录。这显然不是我们想要的。
解决方案
删除root，重新创建用户。
首先，登录
| 1 | sudo mysql -u root |
然后查看当前用户
| 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账号
| 1 2 | mysql> DROP USER 'root' @ 'localhost' ; Query OK, 0 rows affected (0,00 sec) |
重新创建root：
| 1 2 | mysql> CREATE USER 'root' @ '%' IDENTIFIED BY '123456' ; Query OK, 0 rows affected (0,00 sec) |
授权
| 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) |
关于重置密码
host为%时允许远程登录
| 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 mysql UPDATE user SET Password = PASSWORD ( 'newpwd' ) WHERE Host = 'localhost' AND User = 'root' ; |
允许任何地方登录
| 1 2 3 | USE mysql UPDATE user SET Password = PASSWORD ( 'newpwd' ) WHERE Host = '%' AND User = 'root' ; |
