创建数据库

create database if not exists database2 
default character set utf8mb4 
default collate utf8mb4_general_ci;


#utf8mb4_unicode_ci utf8mb4_general_ci

mysql -h 192.168.1.1. -P 3306 -u root -p123456

常用语句

SHOW DATABASES #查询所有库名
select database();  #当前使用的数据库

show variables like 'port'; #查看数据库使用端口
show variables like ‘character%’; 查看数据库编码
show variables like ‘%datadir%’; 查看数据文件存放路径

select distinct concat (‘user: ‘’’,user,’’’@’’’,host,’’’;’)as qurey from mysql.user;
 查看数据库的所有用户信息

创建用户mysql5

#创建管理员用户
GRANT ALL PRIVILEGES ON *.* TO "user"@"%" IDENTIFIED BY "密码";

• mysql8

#创建默认密码
create user 'user'@'%' identified by '密码';
grant all privileges on *.* to  'uroot'@'%' with grant option;

#创建mysql_native_password
create user 'user'@'%' identified with mysql_native_password by '密码';
grant all privileges on *.* to  'uroot'@'%' with grant option;

#创建caching_sha2_password
create user 'user'@'%' identified with caching_sha2_password by '密码';
grant all privileges on *.* to  'user'@'%' with grant option;

#修改密码类型
alter user 'user'@'%' identified with caching_sha2_password by '密码';

修改用户密码
SET PASSWORD FOR 'user'@'%'=PASSWORD('新密码');
修改用户的权限
GRANT 权限 ON 所在数据库.* TO 'user'@'%' identified by '新密码'；
#常用权限
update delete select

创建只读帐号

create user '帐号'@'%' identified with mysql_native_password by '密码';
GRANT SELECT ON 表名.* TO '帐号'@'%';

mysql修改密码为mysql_native_password

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';

mysql5备份还原

删除数据库

DROP DATABASE database_name;

检测表名是否存在

#方法一
SHOW TABLES LIKE 'table_name';
#方法二
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'database_name' AND table_name = 'table_name';

• 获取所有表名

#获取所有表名
SHOW TABLES;
#或
SELECT GROUP_CONCAT(TABLE_NAME) FROM information_schema.TABLES  WHERE TABLE_SCHEMA = 'idianjiao_student';

#获取表名和行数
SELECT  table_name,table_rows FROM  information_schema.tables WHERE table_schema = 'idianjiao_student' ORDER BY table_rows DESC;
SELECT  table_name,table_rows FROM  information_schema.tables WHERE table_schema = 'idianjiao_student' ORDER BY table_rows DESC;

还原数据库

./mysql -uroot -p123456 test < /var/log/mysql/xx.sql
./mysql -h172.16.0.136 -P3306 -uroot -p123456 test < /var/log/mysql/xx.sql

备份数据库

#备份所有数据库
mysqldump -uroot -proot --all-databases >/tmp/all.sql

#导出db1、db2两个数据库
mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql

#导出db1中的a1、a2表
mysqldump -uroot -proot --databases db1 --tables a1 a2  >/tmp/db1.sql

#导出时忽略指定的表
mysqldump -uroot -proot --ignore-table=db1.a1 --ignore-table=db1.a2 --databases db1   >/tmp/db1.sql

使用my.cnf(指定密码)备份

mysqldump --defaults-extra-file=my5.cnf --single-transaction --databases test ekko_0 ekko_1 > /var/log/mysql/backup.sql

docker检测mysql是否可以连接

docker run --rm -it mysql:tag mysql -h remote_host -P remote_port -u user_name -p