记录一些常用的命令，用于 增、删、改、查
如果忘记root密码或其他用户密码，不要急，按下面操作即可。
编辑mysql主配置文件 my.cnf
vim /etc/my.cnf
在[mysqld]字段下添加参数
skip-grant
重启数据库服务
service mysqld restart
这样就可以进入数据库不用授权了
mysql -uroot
修改相应用户密码
use mysql;
update user set password=password('your password') where user='root';
flush privileges;
修改/etc/my.cnf 去掉 skip-grant 重启mysql服务
不想上面那么麻烦，运行以下命令
mysqld_safe --skip-grant-tables &
再开一个终端输入
mysql -uroot
也可以不需要密码进入，之后就是改密码啦。
改密码的几种方式：
set password=password('hello123'); // mysql 5.7.11
update mysql.user set password=password('your password') where user='root';
mysqladmin -u root -poldpassword password newpasswd // 修改密码
mysqladmin -u root password newpassword // 设置新密码
insert into mysql.user (Host,User,Password) values ('%','system',password('hello')); //添加一个
replace into mysql.user (host,user,password) values ('%','system',password('hello123')); //修改
set password for [email protected]"%" = password('hello123');
grant usage on *.* to [email protected]'%' identified by 'hello123';
有些命令是即时生效，有些需要刷新授权才可以
skip-name-resolve //参数的目的是不再进行反解析(ip不反解成域名)，但是也有注意点，mysql.user 表里面的 host 不要用 localhost 之类的，要用127.0.0.1不然连自己都连不上数据库，会报错
skip-innodb // 启用innodb引擎 默认是MyISAM
mysql常用操作
显示都有哪些库
show databases;
使用库
use db;
显示表
show tables; show tables from mysql;
显示表的字段
desc tb;
查看当前登陆的用户
select user();
查看当前当前库
select database();
创建库
create database db1;
创建库指定字符集
create database db1 character set utf8
创建表
create table t1 (`id` int(4), `name` char(40));
显示数据库版本
select version();
显示mysql状态
show status;
修改mysql参数
show variables like 'max_connect%'; set global max_connect_errors = 1000;
查看mysql队列
show processlist;
查看支持的权限清单
show privileges;
显示支持的引擎
show engines;
显示可用的字符集
show character set;
显示表中的各列信息
show columns from mysql.user;
显示授权用户
show grants;
显示数据引擎状态
show engine innodb status\G;
显示警告
show warnings;
创建用户不授权（从5.0版本开始有这个命令）
create user [email protected] identified by '123456';
删除用户（注意在4.1.1版本之前只能删除没有任何权限的账户，5.0.2之后可以删除任何账户）
drop user guest;
重命名用户
rename user;
创建普通用户并授权
grant all on *.* to user1 identified by '123456'; grant all on db1.* to 'user2'@'10.0.2.100' identified by '111222'; grant all on db1.* to 'user3'@'%' identified by '231222'; grant all on *.* to user;
%包括所有host，当然也包括localhost，但是某些低版本并不支持，所以需要单独对localhost授权
撤销已赋予用户的权限
revoke all on *.* from [email protected];
刷新授权
flush privileges;
更改密码
update mysql.user set password=password("newpwd") where user='username' ;
update mysql.user set password=password("netpwd") //忽略条件改全部
查询
select count(*) from mysql.user; // 查询数量 select * from mysql.db; select * from mysql.db where host like '10.0.%';
修改内容
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 如果要改全部,就忽略条件
插入
update db1.t1 set name='aaa' where id=1;
清空表架构还在
truncate table db1.t1;
删除表
drop table db1.t1;
删除数据库
drop database db1;
修复表
REPAIR TABLE `table_name` 修复表 OPTIMIZE TABLE `table_name` 优化表
修改默认编码
默认编码是 latin1
mysql5.5 version
编辑 /etc/my.cnf
mysqld段中加入
character_set_server=utf8 init_connect='SET NAMES utf8'
5.1.40 version
编辑 /etc/my.cnf
mysqld段中加入
character_set_server=utf8
保存重启mysql
查看目前使用字符集
开启慢查询日志
vim /etc/my.cnf
添加到[mysqld]段中
log-slow-queries = /var/log/mysql_slow.log // 日志路径 long_query_time = 6 // 查询超过6秒就记录
查看慢查询时间 // 默认10s
show variables like "long_query_time";
查看慢查询日志路径
show variables like "%slow%";
show variables like 'character%';
mysql备份与恢复
备份
mysqldump -uroot -p db >1.sql
恢复
mysql -uroot -p db 2.sql
备份时指定字符集
mysqldump -uroot -p --default-character-set=utf8 db >1.sql
恢复也指定字符集
mysql -uroot -p --default-character-set=utf8 db < 1.sql
一台mysql服务器启动多个端口
初始化数据库的时候则要运行两次
mysql_install_db --datadir=/home/mysql1 --user=mysql mysql_install_db --datadir=/home/mysql2 --user=mysql
配置文件要编辑成这样
[mysqld0] port = 3300 socket = /tmp/mysql0.sock pid-file = /home/mysql0/localhost.localdomain0.pid datadir = /home/mysql0 #log = /data/mysql0/mysql0.log user = mysql
[mysqld1] port = 3301 socket = /tmp/mysql1.sock pid-file = /home/mysql1/localhost.localdomain1.pid datadir = /home/mysql1 #log = /data/mysql1/mysql1.log
/usr/local/mysql/bin/mysqld_multi start 0-1 这里的0或1是根据配置文件中"[mysqld0]"来定的。
MySQL的MyISAM和InnoDB对比及优化
MyISAM和InnoDB是在使用MySQL最常用的两个表类型，各有优缺点，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。
MyISAM是ISAM表的新版本，有如下扩展：
1、二进制层次的可移植性。
2、NULL列索引。
3、对变长行比ISAM表有更少的碎片。
4、支持大文件。
5、更好的索引压缩。
6、更好的键码统计分布。
7、更好和更快的auto_increment处理。
InnoDB 是 MySQL 上第一个提供外键约束的引擎，除了提供事务处理外，InnoDB 还支持行锁，提供和 Oracle 一样的一致性的不加锁读取，能增加并发读的用户数量并提高性能，不会增加锁的数量。
InnoDB 的设计目标是处理大容量数据时最大化性能，它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。
InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 有它自己的缓冲池，能缓冲数据和索引，InnoDB 还把数据和索引存放在表空间里面，可能包含好几个文件，这和 MyISAM 表完全不同，在 MyISAM 中，表被存放在单独的文件中，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。
以下是一些细节和具体实现的差别：
1、InnoDB不支持FULLTEXT类型的索引。
2、InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
3、对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
4、DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。
6、InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”
MyISAM和InnoDB优化：
key_buffer_size - 这对MyISAM表来说非常重要。如果只是使用MyISAM表，可以把它设置为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负载 -- 记住，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的 key_buffer 都被利用了 -- .MYI 文件只有 1GB，而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。
innodb_buffer_pool_size - 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 -- 如果你的数据量不大，并且不会暴增，那么无需把
innodb_additional_pool_size - 这个选项对性能影响并不太多，至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为 20MB(或者更大)，因此就需要看一下Innodb其他需要分配的内存有多少。
innodb_log_file_size 在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。我经常设置为 64-512MB，跟据服务器大小而异。
innodb_log_buffer_size 默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 -- 它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。
innodb_flush_logs_at_trx_commit 是否为Innodb比MyISAM慢1000倍而头大？看来也许你忘了修改这个参数了。默认值是 1，这意味着每次提交的更新事务（或者每个事务之外的语句）都会刷新到磁盘中，而这相当耗费资源，尤其是没有电池备用缓存时。很多应用程序，尤其是从 MyISAM转变过来的那些，把它的值设置为 2 就可以了，也就是不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去，因此通常不会丢失每秒1-2次更新的消耗。如果设置为 0 就快很多了，不过也相对不安全了 -- MySQL服务器崩溃时就会丢失一些事务。设置为 2 指挥丢失刷新到操作系统缓存的那部分事务。
table_cache -- 打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁，所以通常要加大缓存数量，使得足以最大限度地缓存打开的表。它需要用到操作系统的资源以及内存，对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话，那么设置为 1024 也许比较合适（每个线程都需要打开表），如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。
thread_cache -- 线程的创建和销毁的开销可能很大，因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大，那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。
query_cache -- 如果你的应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不要把它设置太大了，因为想要维护它也需要不少开销，这会导致MySQL变慢。通常设置为 32-512Mb。设置完之后最好是跟踪一段时间，查看是否运行良好。在一定的负载压力下，如果缓存命中率太低了，就启用它。
sort_buffer_size --如果你只有一些简单的查询，那么就无需增加它的值了，尽管你有 64GB 的内存。搞不好也许会降低性能。