MYSQL学习笔记

./mysqld --defaults-file=../my.cnf --user=mysql
mysql -S /tmp/mysql.sock
start:
cd share/mysql
./mysql.server start (./mysql.server --help)
cp ./mysql.server /etc/rc.d/init.d/mysql
chkconfig --add mysql
chkconfig --list|grep mysql
service mysql start
libexec]#./mysqld --help --verbose | less
读配置文件顺序：
/etc/my.cnf  /etc/mysql/my.cnf /opt/mysql5152/etc/my.cnf ~/.my.cnf
./bin/mysqld_safe --help
show binary logs
system cat ls -l /tmp
help;
help contents;
help administration
mysql_install_db --basedir=/opt/mysql5152 --datadir=/opt/mysql5152/data2 --user=mysql
在data2下面生成两个库
mysql_install_db --basedir=/opt/mysql5152 --datadir=/opt/mysql5152/data3 --user=mysql
mysql_multi --defaults-file=./my.cnf start 3306(3307 3308)或者3306-3308
show grants for root@localhost;

mysqladmin ping
mysqld is alive
status or \s
charset gbk
? show
show create database test; 或者show create database test \G;
show create table t [\G]
show variables[like '%buffer%];
set global key_buffer_size=10000;
select @@max_binlog_size/1024/1024
show master logs;show binary logs
flush logs; show binary logs递增
reset master;初始化日志  删除掉后再重新生成
purge binary logs to '1.000003'; 03前的全部删掉了
show variables like '%expire%';
set global expire_log_days=5
mysqlbilog 00001.bin|grep insert
set global binlog_format=row;
show global variables like '%format%';
binlog_format:statement,row,mixed
set session binlog_format='STATEMENT'('ROW';'MIXED')
set global binlog_format='STATEMENT';
binlog_cache_size每个线程，而不是整个数据库
max_binlog_cache_size最大扩展
show status like '%binlog_cache_use%';   status可变的
show table status like 't' \G;
MYISAM不支持事物
select @@autocommit;
set autocommit=0 0关闭1打开
show variavles like '%binlog_cache_use%'; 看缓存了多少个事物
binlog_cache_disk_use 内存不够，磁盘上存储
sync_binlog
show create procedure test \G;
call test();调用
general_log=1
genaral_log_file=/tmp/mysqlgen.log
mysqladmin flush-logs重新生成
slow_query_log=1
slow_query_log_file=/tmp/mysqlslow.log1
show index from t;看t有没有索引
mysqldumpslow -s -t -t 2 /tmp/mysqlslow.log1把慢查询按时间最长的前两条查出来
.frm表结构文件

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25602767/viewspace-695846/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25602767/viewspace-695846/