mysql安装

参考: https://www.jianshu.com/p/7f1cb7edd332

移除mysql残留包

sudo apt-get autoremove --purge mysql-server*
sudo apt-get remove mysql-server
sudo apt-get autoremove mysql-server
sudo apt-get remove mysql-common
# 清理残留数据
sudo dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

在线安装mysql

sudo apt-get install mysql-server
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev

# 启动mysql
sudo service mysql start
# 添加mysql用户,解决 No directory 报错
usermod -d /var/lib/mysql/ mysql
# 关闭mysql
sudo service mysql stop

默认配置：

• /etc/mysql/my.cnf -> /etc/alternatives/my.cnf -> /etc/mysql/mysql.cnf

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

• /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
user            = mysql
bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1
key_buffer_size         = 16M
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
max_binlog_size   = 100M

修改mysql密码

配置mysql跳过密码检测: sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
添加skip-grant-tables到[mysqld]下面

# 重启服务
sudo service mysql restart
# 进入mysql客户端
sudo mysql

修改密码：

-- mysql5.x
select user,host,authentication_string,plugin,password('123456') from mysql.user where user='root';
GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; 
-- mysql 8.x
select user,host,authentication_string,plugin,sha1('123456') from mysql.user where user='root';
update mysql.user set authentication_string = sha1('123456') where user='root';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
-- 授权登录位置
update mysql.user set host='%' where user='root';

flush privileges
-- 授权任意位置登录
create user 'root'@'%' identified by '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
flush privileges;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 查看字符编码
show variables like '%char%';
-- 设置字符编码
set character_set_connectiont=utf8mb4;
set character_set_database=utf8mb4;
set character_set_results=utf8mb4;
set character_set_server=utf8mb4;
-- 查看权限
show grants for 'root'@'localhost';

修改配置文件中的字符编码：

[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
character-set-filesystem=utf8mb4
[client]
default-character-set=utf8mb4