MySQL常用命令

安装 MySQL 8.0

安装环境为CentOS7

wget https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm
yum -y install mysql80-community-release-el7-2.noarch.rpm
yum -y install mysql-community-server

# 启动MySQL并加入开机自启动
systemctl start  mysqld.service
systemctl enable  mysqld.service

# 查看默认密码
grep 'temporary password' /var/log/mysqld.log
# 修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '1q2w3e$R';

创建数据库

创建指定字符集数据库(库名 pipe，字符集使用 utf8mb4，排序规则 utf8mb4_general_ci)
CREATE database pipe DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

为用户授权

grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON eosdb.* TO 'eosuser'@'172.31.%' IDENTIFIED BY '1q2w3e$R';
grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, on computeplatform.* to root@'172.29.%';
GRANT ALL ON magento.* TO 'testuser'@'172.31.0.0/16';
#只读账户
GRANT SELECT ON testwalorder.* TO 'testwaluser02'@'%' IDENTIFIED BY '1q2wsd$%^3e$R';

刷新

FLUSH PRIVILEGES;

查看用户

select user,host from mysql.user;

查看权限

#查看自己的权限
show grants;
#查看其他 MySQL 用户权限：
show grants for root@localhost;

以手机号查询

SELECT *  from basic_customer where trim(mobile_phone) = '13510304062';

以customer_id 查询

SELECT *  from basic_customer where customer_id ='5603957';

更新ustomer_id ##新的内容 == 旧的内容

update basic_order set customerId = '5628353' where customerId = '5603957'

删除customer_id

DELETE FROM basic_customer where customer_id ='5603957';

查看user表字符集

show create table user;

查看表内容

select * from y_password_error_log;

查看当前安装的MySQL所支持的字符集

show charset;

创建数据库

CREATE database 360qb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
FLUSH PRIVILEGES;

创建用户

CREATE USER 'devuser'@'%' IDENTIFIED BY '1q2w3e$R';   

授权用户

GRANT ALL ON *.* TO 'root'@'%';  

备份数据库

mysqldump -uadmin -p12345 -hrds.aliyuncs.com --hex-blob --single-transaction --default-character-set=utf8 --set-gtid-purged=OFF testdb |gzip >/data/testdb.gz

导入数据库

mysql -u admin -h 10.0.0.1 -p < dbyw.sql

修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '1q2w3e$R';

删除数据库

drop database test;

命令行执行sql命令

mysql -uroot -p123456 -h 10.0.0.12 -e 'show databases;'

MySQL查看默认密码

 grep 'temporary password' /var/log/mysqld.log