mysql

# install
sudo apt-get update
sudo apt-get install mysql-server

# start
systemctl start mysql

# Launch at reboot
systemctl enable mysql

# login
mysql -u root -p

# 远程访问
mysql -h 127.0.0.1 -u root -p -P 3306

# show status
systemctl status mysql.service

# 初始化配置
sudo mysql_secure_installation

# 以root权限登录
sudo mysql

# 查看密码权限
SHOW VARIABLES LIKE 'validate_password%';

# 设置全局密码策略为LOW
SET GLOBAL validate_password_policy = 0;

创建用户，指定任意ip
CREATE USER 'allen'@'%' IDENTIFIED BY "1234";

用户授权：
GRANT ALL ON db.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword';

授权查询权限
GRANT SELECT ON *.* TO 'username'@'host_or_wildcard' IDENTIFIED BY 'password';

授权所有权限在test数据库
grant all privileges on 'test'.* to 'test'@'%' identified by '1234';  

对lisi用户仅对db1.author表有查询、插入和更新的操作
grant select,insert,update on db1.author to 'lisi'@'192.168.11.88';

查询用户
select host,user from user;

取消zhangsan用户从任意地址登录权限
drop user 'zhangsan'@'%';

取消zhangsan用户从192.168.11.% 网段登录权限
drop user 'zhangsan'@'192.168.11.%';

修改用户密码
set password for 'lisi'@'192.168.11.88'=password('321');

权限刷新
FLUSH PRIVILEGES;

查询用户权限：
show grants for root@'localhost';

显示用户权限
Show Grants for bob@'%';

创建用户权限
grant create user on *.* to admin@'localhost' with grant option;

显示mysql用户
SELECT User FROM mysql.user;

取消用户权限
REVOKE ALL PRIVILEGES ON phpmyadmin.* FROM 'phpmyadmin'@'localhost';

导出结构不导出数据:
mysqldump -d 数据库名 -uroot -p > xxx.sql

导出数据不导出结构:
mysqldump -t 数据库名 -uroot -p > xxx.sql

数据库数据和结构导出:
mysqldump 数据库名 -u root -p  > data.sql

导出特定的表:
mysqldump -uroot -p -B数据库名 --table 表名 > xxx.sql

导出添加筛选条件的表
mysqldump -u用户名 -p密码 数据库名 表名 --where="筛选条件" > 导出文件路径

数据库导入:
1. use database_name;
2. set names utf8; (或其他需要的编码)
3. source example.sql (sql文件存放路径)

创建数据库，并指定字符集
CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

删除数据库
drop database RUNOOB;

更新
UPDATE table SET name='Mary' WHERE id=1;

插入
INSERT INTO worker (name) VALUES ('tom');

删除数据
DELETE FROM `movies` WHERE `movie_id` = 18;

纵向显示数据
SELECT * FROM test\G

选取有限列
SELECT * FROM test LIMIT n;

显示表结构
DESC `table`;

显示数据库
SHOW DATABASES;

选择数据库
use DATABASE;

重命名表名
rename table MyClass to YouClass;

列出列名
select column_name, column_comment from information_schema.columns where table_schema ='db' and table_name = 'tablename' ;

# 1. 修改配置文件
sudo vim /etc/mysql/my.cnf
把bind-address参数的值改成你的内/外网IP或0.0.0.0,或者直接注释掉这行.

# 2. 创建host
mysql> use mysql
mysql> update user set host='%' where user='root';
mysql> flush privileges;

# 3. 授权用户远程访问
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mypwd' WITH GRANT OPTION;
mysql> flush privileges;