创建数据库
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 "密码";
#创建默认密码
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