mysql优化小记(持续更新)

Mysql配置文件常用修改->
1.有关慢查询
开户和设置慢查询时间:
vi /etc/my.cnf
log_slow_queries=slow.log
long_query_time=5


查看设置后是否生效
mysql> show variables like "%quer%";


慢查询次数:
mysql> show global status like "%quer%";


2.设置mysql的默认存储引擎:
vi /etc/my.cnf
default_storage_engine = innodb
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
查看默认存储引擎:
mysql> show engines;


3.设置mysql字符集问题:
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
character-set-server=utf8
collation-server=utf8_general_ci
查看生效后的结果: \s 查看


4.创建唯一索引问题:
alter table t1 add unique u_name (name);
create unique index u_name on t1(name);
mysql> desc t1;
mysql> show index from t1;
mysql> drop index name on t1;


查看bin-log日志:
mysql> show binary logs;


查看最后一个bin-log日志:
mysql> show master status;


Mysql中常用的Help方法->
在mysql中那么多的命令如何才能记得住是个问题，这里有一个特别好的获得帮助的好方法，当然是在mysql>的提示下的操作:
1.? % 可以获得所有的mysql>里的命令，这个是最多的,那么这里的东西如何去进一步获得帮助呢?
2.? create
3.? opti% 因为记不住optimize的全称，这个时候可以用%来替代
4.? reg% 获得了记不住了的regexp用法.
5.查看所有用? contents可以得到所有的帮助大纲,通过这个目录再用?继续往下细查.
mysql> ? create;




Mysql用户权限->
mysql用户权限
1.授予wei全部数据库的全部权限，当然肯定包括增，删，改，查了.命令如下:
mysql>grant all privileges *.* to wei@localhost identified by "wei";
2.授予test数据库的全部权限.
mysql>grant all privilegs on test.* to wei@localhost identified by "wei";
3.回收wei的所有的权限.
mysql>revoke all privileges on *.* from 'wei'@'localhost';
4.只回收wei对test数据库的delete权限:
mysql>revoke delete privileges on test.* from 'wei'@'localhost';
4.刷新权限
mysql>flush privileges;
5.查看权限
mysql>show grants for wei@localhost;
6.删除mysql用户
mysql>drop user wei;


复制表结构+复制表数据
(1) mysql> create table t3 like t1;
(2) mysql> insert into t3 select * from t1;
#这样可能保障数据表完全相同


Create table t2 select * from t1;#但这样做可能会丢失一部分带有索引的表结构