mysql基础一

创建数据库：

create database sanya;

删除数据库

drop database advdds;

调用数据库

use sanya;

导入数据
source /var/tmp/sql/20200702/data/culture.sql; 

导出数据

/opt/server/mysql/bin/mysqldump  database -uroot > /var/tmp/database_20201124.sql
查看用户表
select user,host from mysql.user;
创建用户并授权
GRANT all privileges ON sanya.* TO 'sanya'@'10.3.0.%' identified by 'sanya@123456';
配置生效
flush privileges;

============================================

mysql8.0 的授权 与mysql 之前版本的授权不同
mysql> create database test;
mysql> use test;
mysql> CREATE USER `root`@`192.168.%.%` IDENTIFIED BY '123456';
mysql> GRANT ALL ON test.* TO `root`@`192.168.%.%`;

客户端连接数据库会报1251 或 2059 错误，原因如下：

MySQL 8.0 之前的版本中加密规则是mysql_native_password，MySQL 8.0 之后,加密规则是caching_sha2_password
修改加密规则即可

mysql> ALTER USER 'root'@'192.168.%.%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'root'@'192.168.%.%' IDENTIFIED WITH mysql_native_password BY '12345678'; #经测试发现，两个密码必须不一样才行,要不然一直连不上
mysql> FLUSH PRIVILEGES;

在创建用户时，加上mysql_native_password参数，就不用再改密码了。

CREATE USER `root`@`192.168.%.%` IDENTIFIED WITH mysql_native_password BY '123456';
GRANT ALL ON test.* TO `root`@`192.168.%.%`;
FLUSH PRIVILEGES;

docker容器中mysql数据库的导入导出

mysql数据库的导出
[root@yfcs20200604 ~]# docker exec mysql-8.0 mysqldump -u test -p test --skip-lock-tables test123 > /var/tmp/test123_20201010.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

导入
[root@localhost soft]# docker exec -i mysql8.0 mysql -uroot -p123456 test123 < /data/soft/test123_20201010.sql #一定要加-i参数