MySQL管理与优化-CSDN博客

本文详细介绍MySQL的日常管理操作，包括更改root密码、连接数据库、常用命令、用户管理、SQL语句、备份恢复及错误解决方法。同时，深入探讨了数据库性能优化策略，如参数调整、索引优化等。

设置更改root密码
连接mysql
mysql常用命令
mysql用户管理
常用sql语句
mysql数据库备份恢复
错误解决

1. 设置更改root密码

/usr/local/mysql/bin/mysql -uroot  #命令不在PATH下所以需要使用绝对路径
将mysql的命令加入到PATH
export PATH=$PATS:/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
vi /etc/profile #编辑该配置文件
将`export PATH=$PATS:/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin`放到该配置文件最后一行
然后使用`source /etc/profile`使配置文件生效
 mysqladmin -uroot password '123456'  #刚装好mysql为空密码给root设置一个密码
 mysql -uroot -p123456
mysqladmin -uroot -p'zengbaihua' passwd 'zengbaihua1' #知道原密码的情况下修改密码
#提示
[root@test ~]# mysqladmin -uroot -p'zengbaihua' password 'zengbaihua1'
Warning: Using a password on the command line interface can be insecure.
#说明：实际上已经更改成功，提示为密码前端显示不安全
密码重置
 vi /etc/my.cnf  #mysql下增加skip-grant
 重启mysql服务 /etc/init.d/mysqld restart
 mysql -uroot
 use mysql;
 update user set password=password('aminglinux') where user='root';

2. 连接mysql

mysql -uroot -p123456  #常见方式默认是sock
mysql -uroot -p123456 -h127.0.0.1 -P3306   #登录远程数据库
mysql -uroot -p123456 -S/tmp/mysql.sock  #只适合在本机不使用tcp/ip  而用sock
mysql -uroot -p123456 -e “show databases”  #进入数据库并执行一条命令（列出数据库）
-h 指定远程数据库的ip
-P 指定端口
-e 指定进入后执行一条命令

3. mysql常用命令

 查询库 show databases;
 切换库 use mysql;
 查看库里的表 show tables;
 查看表里的字段 desc tb_name;
 查看建表语句 show create table tb_name\G;
 查看当前用户 select user();
 查看当前使用的数据库 select databsase();
 创建库 create database db1;
 创建表 use db1; create table t1(`id` int(4), `name` char(40));
 删除表 drop table t1
 查看当前数据库版本 select version();
 查看数据库状态 show status;
 查看各参数 show variables; show variables like 'max_connect%';
 修改参数 set global max_connect_errors=1000;
 查看队列 show processlist; show full processlist;
 刷新权限 flush privileges;

4. mysql用户管理

 grant all on *.* to 'user1' identified by 'passwd';#所有库表 用户user1 密码：passwd
 grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
 #针对db1这个库
 #允许这个ip来登录数据库
 #user2用户名
 #passwd密码
 grant all on db1.* to 'user3'@'%' identified by 'passwd';
 #db1库的所有权限
 #用户user3
 #允许所有来源ip连接
 #密码passwd
 show grants;
 #查看用户授权
 show grants for user2@192.168.133.1;
 #查看指定用户的授权

5. 常用sql语句

 select count(*) from mysql.user;  #查询mysql库的user表有多少行
 select * from mysql.db;           #查mysql库的db表的所有数据
 select db from mysql.db;          #查mysql库的db表的db字段
 select db,user from mysql.db;     #查mysql库的db表的db字段和user字段
 select * from mysql.db where host like '192.168.%';   #like模糊查询 
 insert into db1.t1 values (1, 'abc');  #插入数据 第一个字段1第二个字段abc
 update db1.t1 set name='aaa' where id=1;  #将id等于1的name改为aaa
 delete from db1.t1 where id=2；#删除db1库t1表id等于2的行
 truncate table db1.t1; #清空db1库t1表（清空数据）
 drop table db1.t1;  #删除db1库t1表
 drop database db1;   ##删除db1库
 select 查
 insert 插入
 update 改
 delete 删
 truncate 清空（内容）

6. mysql数据库备份恢复

 备份库  mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
 恢复库 mysql -uroot -p123456 mysql < /tmp/mysql.sql
 备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql
 恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql
 备份所有库 mysqldump -uroot -p -A >/tmp/123.sql  #-A表示所有
 只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql

7.错误解决

如果出现密码明明是正确的但是却无法登陆报错的情况：

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

这是因为MySQL中默认存在一个用户名为空的账户，只要在本地，可以不用输入账号密码即可登录到MySQL中。mysql在验证用户登陆的时候，首先是验证host列，如果host列在验证user列，再password列，而现在按照我之前的连接语句：按照host列找到为空的那列（空匹配所有用户名），所以匹配到了这条记录，然后发现这条记录的密码为空，而我的语句里面有密码，那么就会报错.

解决办法：删除匿名用户首先修改my.cnf，增加以下语句跳过密码验证：

skip-grant-tables

然后登录root用户，执行以下语句删除匿名用户：

mysql> use mysql;
mysql> delete from user where user='';
Query OK, 2 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

总结

innoDB MyIsam
Memory（也叫HEAP）堆内存嘛
Mrg_Myisam：（分表的一种方式–水平分表）
Blackhole（黑洞引擎）
https://blog.csdn.net/xiaoyi23000/article/details/80521423
https://blog.csdn.net/u013399093/article/details/70568837
https://www.cnblogs.com/abobo/p/4242417.html

使用xtrabackup备份innodb引擎的数据库 innobackupex 备份 Xtrabackup 增量备份 http://zhangguangzhi.top/2017/08/23/innobackex%E5%B7%A5%E5%85%B7%E5%A4%87%E4%BB%BDmysql%E6%95%B0%E6%8D%AE/#%E4%B8%89%E3%80%81%E5%BC%80%E5%A7%8B%E6%81%A2%E5%A4%8Dmysql
相关视频
链接：http://pan.baidu.com/s/1miFpS9M 密码：86dx
链接：http://pan.baidu.com/s/1o7GXBBW 密码：ue2f

扩展

转载于:https://my.oschina.net/zenghong133/blog/3052443