安装

apt-get install mysql-server

修改配置文件

vim /etc/mysql/my.cnf

//允许远程连接
注释 bind-adress：127.0.0.1
或者 bind-adress：0.0.0.0

//不区分大小写
在[mysqld]节点下添加 lower_case_table_names=1

登陆

#1.不要密码直接回车
mysql -u root -p

#2.创建库databasename
CREATE DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;

#3.创建库databasename对应用户username，并设置密码
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

#4.授权databasename库的所有权限给username用户
GRANT ALL ON databasename.* TO 'username'@'%';

#5.刷新
flush privileges;

修改root用户登陆密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';

导入sql文件

use databasename;

#导入,/test/test.sql文件具体位置
source /test/test.sql

卸载mysql

//删除mysql
1.sudo apt-get remove mysql-*

//理残留的数据
2.dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

3.对话框选择yes 卸载完成

常见问题

远程连接Host is not allowed to connect to this MySQL server

//登陆mysql
1.mysql -u root -p

2.use mysql;

3.select t.host from user t where t.user='root';
+--------------+
|  host        |
+--------------+
|  localhost   |
+--------------+

4.update user set host='%' where user='root';

5.重启服务 或 flush privileges;

The user specified as a definer ('username'@'%') does not exist

//登陆mysql
1.mysql -u root -p

#赋予root用户所有权限
2.grant all on databasename.tablename to 'username'@'%' identified by "password";
或 grant all on databasename.tablename to 'username'@'%';

3.flush privileges;

密码错误，修改密码 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

1.vim /etc/my.cnf

2.[mysqld]文本段下任意行添加
skip-grant-tables

3./etc/init.d/mysql restart（service mysql restart）

//输入mysql回车，登录到mysql
4.mysql

5.use mysql;

6.update user set password=password("password") where user="root";

7.flush privileges;

8.quit

9.vim /etc/my.cnf

10.删除skip-grant-tables

11.重启服务 ok

apt-get: Could not resolve 'archive.ubuntu.com' 更新源

解决方案参考连接

1.vim /etc/hosts

//在最前面添加
2.91.189.88.149   security.ubuntu.com

mysql5.7以上group by

#[mysqld]最下面添加，关闭ONLY_FULL_GROUP_BY
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1251 client does not support ..问题

#先登录

#查看用户信息，如果查询结果中plugin不是mysql_native_password，则该用户需要修改密码
select host,user,plugin,authentication_string from mysql.user;

#修改用户密码 'username'@'%'   pugin不是mysql_native_password
ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'newpassword';

flush privileges;

创建用户并授权等相关操作

创建用户

#host 是user里面的host字段
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

用户授权

GRANT privileges ON databasename.tablename TO 'username'@'host';

privileges：All,SELECT,INSERT,UPDATE

更改用户密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

撤销权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

删除用户

DROP USER 'username'@'host';