MySQL管理寻常的命令

2024-07-24 12:36:20

字体：大中小

来源：转载

供稿：网友

MySQL管理常用的命令：

　　一、用户管理

　　1、创建用户

　　# 任何ip的客户端都可以访问
　　create user 'tester'@'%' identified by '123456';
　　# 只有本地的客户端才可以访问
　　create user 'tester'@'localhost' identified by '123456';
　　# 只有指定的192.168.1.90这个ip才可以访问
　　create user 'tester'@'192.168.1.90' identified by '123456';
　　2、修改用户

　　(1)修改密码

　　update mysql.user set authentication_string=password('新密码') where user='tester' and host='localhost'; # 5.7版本使用
　　update mysql.user set password=password('新密码') where user='tester' and host='localhost'; # 5.6版本使用
　　(2)修改host

　　update mysql.user set host='192.168.1.100' where user='tester';
　　(3)修改用户后需要刷新硬盘或重启数据库才生效；其中刷新硬盘需要有reload权限

　　GRANT reload ON *.* to 'root'@'%';
　　(4)删除用户

　　DELETE FROM mysql.user WHERE user='tester' and host='localhost';
　　(5)忘记密码的重置流程

　　1)在/etc/my.cnf 加入skip-grant-tables跳过授权表

　　2)重启MySQL，后无密码登录

　　3)修改密码

　　update mysql.user set authentication_string=password('root') where user='root';
　　4)在/etc/my.cnf删除skip-grant-tables

　　5)重启MySQL,输入密码进入

　　二、权限管理

　　1、权限授予

　　(1)查询用户权限

　　SHOW GRANTS FOR tester;
　　SELECT * FROM mysql.user WHERE user='tester' /G
　　(2)授予权限

　　GRANT ALL PRIVILEGES ON *.* TO 'tester'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
　　(3)撤销权限

　　REVOKE ALL PRIVILEGES ON *.* FROM 'tester'@'%';
　　2、账号权限体系

　　(1)服务级用户权限

　　GRANT ALL PRIVILEGES ON *.* TO 'tester'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
　　(2)数据库级用户权限

　　GRANT ALL PRIVILEGES ON staff.* TO 'tester'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
　　(3)表级用户权限

　　GRANT ALL PRIVILEGES ON staff.employee TO 'tester'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
　　(4)字段级用户权限

　　三、表空间管理(InnoDB)

　　(1)共享表空间(默认)

　　数据和文件都存放在data目录下的ibdata1文件里，多数据共用一个。

　　查看共享表空间大小和存放路径：

　　show variables like 'innodb_data%';
　　(2)独占表空间

　　每个数据库都有自己的文件夹和文件

　　.frm保存元数据，表结构，表结构的定义

　　.ibd存放数据和索引文件

　　查看value为ON，则开启了独占表空间：

　　show variables like 'innodb_file_per_table';
　　四、备份与还原

　　1、备份数据

　　热备份：进行备份时，数据库的读写操作不受影响

　　温备份：进行备份时，数据库的读操作可以进行，但不能进行写操作

　　冷备份：进行备份时，数据库不可以进行读写操作

　　2、备份命令

　　#全库备份
　　mysqldump --single-transaction -uroot -p123456 -A > all_201810911.sql
　　# 备份数据库staff
　　mysqldump --single-transaction -uroot --password=123456 staff > E:/mysql_bak/staff_20180729.sql
　　# 备份192.168.1.90服务器上的数据库staff
　　mysqldump --single-transaction --opt -uroot --password=123456 -h292.168.1.90 staff > E:/mysql_bak/staff_20180729.sql
　　# 备份数据库staff的员工表employee
　　mysqldump --single-transaction -uroot --password=123456 staff employee > E:/mysql_bak/staff_20180729.sql
　　3、还原数据库

　　mysql -uroot -p123456 < all_201810911.sql
　　mysql -uroot -p123456 staff < staff_201810911.sql
　　或还原数据表

　　source
　　E:/mysql_bak/201807/staff_20180729.sql;
　　五、主从同步

　　主数据库配置(可读可写)

　　server-id=1 # 主库和从库需要不一致
　　log-bin=mysql-bin # 开启log-bin二进制日志文件
　　binlog-do-db=db_test # 需要同步的数据库
　　binlog-ignore-db=staff # 不需要同步的数据库
　　从数据库配置

　　server-id=2 # 主库和从库需要不一致
　　log-bin=mysql-bin # 开启log-bin二进制日志文件
　　replicate-do-db=db_test # 需要同步的数据库
　　read_only # 为保证数据库的数据一致性，从数据库只允许读取操作，不允许写操作
　　六、查询所有数据库占用磁盘空间大小

　　select
　　TABLE_SCHEMA,
　　concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
　　concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
　　from information_schema.tables
　　group by TABLE_SCHEMA
　　ORDER BY data_size desc;
　　#order by data_length desc;

（编辑：武林网）

上一篇：mysql需求情景列转行

下一篇：mysql5.7windows下zip安装注意事宜