MySQL基础

===MySQL基础====
mysql下载:
mysqld 常规MySQL服务器
mysqld-opt 优化mysql服务器,提供一些功能可以挖掘更好的功能
mysqld-max 与mysqld一样,但可以支持更新,更具实验性质的功能(更不稳定)
++安装mysql
参见自带的INSTALL-SOURCE文件
./configure –prefix=/app/mysql-5.0.51a –localstatedir=/data/mysql –with-charset=utf8 –with-extra-charsets=gbk,gb2312,utf8

++查看mysql安装编译参数
cat $PATH/mysql/bin/mysqlbug | grep CONFIGURE_LINE

++启动/关闭mysql
$path/mysqld_safe -user=mysql &
$/mysqladmin -p shutdown

++修改root口令
mysqladmin -u root -p password ‘新密码’

++查看服务器状态
$path/mysqladmin version -p

++连接远端mysql服务器
$path/mysql -u 用户名 -p #连接本机
$path/mysql -h 远程主机IP -u 用户名 -p#连接远程MYSQL服务器

++创建/删除 数据库或表
mysqladmin -u root -p create xxx
mysql>create database 数据库名;
create TABLE items (
id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
symbol CHAR(4) NOT NULL,
username CHAR(8),
INDEX sym (symbol),INDEX …..
UNIQUE(username)
) type=innodb;
mysql>drop database [if exists] 数据库名
mysql>create table 表名;
mysql>drop table 表名;

++复制表结构
create table target_table like source_table
select * into NEW_TABLE from OLD_TABLE where 1=2

++查看数据库和查看数据库下的表
mysql>show databases;
mysql>show tables;
mysql>desc 表名; #查看具体表结构信息
SHOW CREATE DATABASE db_name #显示创建db_name库的语句
SHOW CREATE TABLE tbl_name  #显示创建tbl_name表的语句

++创建用户
mysql>grant select,insert,update,delete,alter on mydb.* to test2@localhost identified by “abc”;
mysql>grant all privileges on *.* to test1@’%’ identified by “abc”;
mysql>flush privileges;

++用户管理
mysql> update user set password=password (’11111′) where user=’test1′; #修改test1密码为111111
DELETE FROM user WHERE User=”testuser” and Host=”localhost”; #删除用户帐号
SHOW GRANTS FOR user1@localhost; #显示创建user1用户的grant语句
revoke all privileges on *.* from ‘user1′@’%’ ; #回收user1对所有库表的权限

++mysql数据库的备份和恢复
mysqldump –all-databases -h 数据库服务器地址 -u 用户名 -p密码 > xxx.sql #备份所有数据库
mysqldump –databases 数据库名 -h 数据库服务器地址 -u 用户名 -p密码 >  xxx.sql #备份指定数据库
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 ##导出单独的表
mysql -uroot -p xxx < aaa.sql #导入表
mysqldump -u 用户名  -p -d –add-drop-table 数据库名 > xxxx.sql  #导出一个数据库结构
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

++忘记mysql密码
先停止所有mysql服务进程
mysqld_safe –skip-grant-tables & mysql
mysql>use mysql;
mysql>update user set password=password(’111111′) where user=’root’;
mysql>flush privileges;
然后重启mysql并以新密码登入即可

++当前使用的数据库
select database();

===数据库日常操作维护====
++创建表
create table table_name
(column_name datatype {identity |null|not null},f_time TIMESTAMP(8),
…)ENGINE=MyISAM AUTO_INCREMENT=3811 DEFAULT CHARSET=utf8;
例: CREATE TABLE guest (name varchar(10),sex varchar(2),age int(3),career varchar(10));
# desc guest可查看表结构信息
# TIMESTAMP(8) YYYYMMDD 其中(2/4/6/8/10/12/14)对应不同的时间格式
SHOW CREATE TABLE tbl_name  #显示创建tbl_name表的语句

++创建索引
可以在建表的时候加入index indexname (列名)创建索引,
也可以手工用命令生成 create index index_name on table_name (col_name[(length)],… )
CREATE INDEX number ON guest (number(10));
SHOW INDEX FROM tbl_name [FROM db_name] #显示现有索引
mysql> repair TABLE date QUICK;  #索引列相关变量变化后自动重建索引

++查询及常用函数
mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
mysql> select college, region, seed from tournament ORDER BY region, seed;
mysql> select col_name from tbl_name WHERE col_name > 0;
mysql> select DISTINCT ……  [DISTINCT关键字可以除去重复的记录]
mysql> select DATE_FORMAT(NOW(),’%m/%d/%Y’) as DATE, DATE_FORMAT(NOW(),’%H:%m:%s’) AS TIME;
mysql> select CURDATE(),CURTIME(),YEAR(NOW()),MONTH(NOW()),DAYOFMONTH(NOW()),HOUR(NOW()),MINUTE(NOW());
mysql> select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(20080808),FROM_UNIXTIME(UNIX_TIMESTAMP()); mysql> select PASSWORD(”secret”),MD5(”secret”);   #加密密码用
mysql> select count(*) from tab_name order by id [DESC|ASC]; #DESC倒序/ASC正序
* 函数count,AVG,SUM,MIN,MAX,LENGTH字符长度,LTRIM去除开头的空头,RTRIM去尾部空格,TRIM(str)去除首部尾部空格,LETF/RIGHT(str,x)返回字符串str的左边/右边x个字符,SUBSTRING(str,x,y)返回str中的x位置起至位置y的字符mysql> select BINARY ‘ross’ IN (’Chandler’,'Joey’, ‘Ross’); #BINARY严格检查大小写
* 比较运算符IN,BETWEEN,IS NULL,IS NOT NULL,LIKE,REGEXP/RLIKE
mysql> select count(*),AVG(number_xx),Host,user from mysql.user  GROUP by user [DESC|ASC] HAVING user=root; #分组并统计次数/平均值

++UNIX_TIMESTAMP(date)
返回一个Unix时间戳记(从’1970-01-01 00:00:00′GMT开始的秒数)
mysql> select UNIX_TIMESTAMP();
mysql> select UNIX_TIMESTAMP(’1997-10-04 22:23:00′);
mysql> select FROM_UNIXTIME(875996580);   #根据时间戳记算出日期

++控制条件函数
mysql> select if(1<10,2,3), IF(55>100,’true’,'false’);
#IF()函数有三个参数,第一个是被判断的表达式,如果表达式为真,返回第二个参数,如果为假,返回第三个参数.
mysql> select CASE WHEN (2+2)=4 THEN “OK” WHEN (2+2)<>4 THEN ‘NOT OK’ END AS status;

++系统信息函数
mysql> select DATABASE(),VERSION(),USER();
mysql> select BENCHMARK(9999999,LOG(RAND()*PI())) AS PERFORMANACE; #一个测试mysql运算性能工具

++将wp_posts表中post_content字段中文字”old”替换为”new”
update wp_posts set post_content=replace(post_content,’old’,'new’)

++改变表结构
alter table table_name alter_spec [, alter_spec ...]
例：alter table dbname add column userid int(11) not null primary key auto_increment;
这样，就在表dbname中添加了一个字段userid，类型为int(11)。

++建表后新增主键
alter  table tablename add primary key(id);

++调整列顺序
alter table tablename CHANGE id id int(11) first;

++修改表中数据
insert [into] table_name [(column(s))] values (expression(s))
例：mysql>insert into mydatabase values(’php’,'mysql’,'asp’,’sqlserver’,'jsp’,'oracle’);
mysql> create table user select host,user from mysql.user where 1=0;
mysql> insert into user(host,user) select host,user from mysql.user;

++更改表名
命令：rename table 原表名 to 新表名;

++表的数据更新
mysql>update table01 set field04=19991022[, field05=062218] where field01=1;

++删除数据
mysql>delete from table01 where field01=3;
#如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename.

++SHELL提示符下运行SQL命令
mysql -e “show slave status\G ”

++坏库扫描修复
# cd /var/lib/mysql/xxx && myisamchk table_name
myisamchk -r table_name.MYI
#mysql> repair table tablename ;

++insert into a (x) values (’11a’)
出现: ata truncated for column ‘x’ at row 1
解决办法:
在my.ini里找到
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_Create_USER,NO_ENGINE_SUBSTITUTION”
把其中的STRICT_TRANS_TABLES,去掉,然后重启mysql就ok了

++innodb支持事务
新表:create TABLE table-name (field-definitions) TYPE=INNODB;
旧表: alter TABLE table-name TYPE=INNODB;
mysql> start transaction #标记一个事务的开始
mysql> insert into…..      #数据变更
mysql> ROLLBACK或commit  #回滚或提交
mysql> SET  AUTOCOMMIT=1; #设置自动提交
mysql> select @@autocommit; #查看当前是否自动提交

++表锁定相关
mysql> LOCK TABLE users READ; # 对user表进行只读锁定
mysql> LOCK TABLES user READ, pfolios WRITE #多表锁控制
mysql> UNLOCK TABLES; #不需要指定锁定表名字, MySQL会自动解除所有表锁定

++如何清除mysql的mysql-bin日志
mysql> show master logs; #查询musql-bin,mysql操作日志
mysql> reset master;全部删除
mysql> purge master logs to ‘mysql-bin.00001′; #删除,保留最新
mysql> purge master logs to “date” ;删除除date以前的日志
#直接删除也可以，然后记得FLUSH LOGS
#也可以修改my.cnf
–expire_logs_days=3

=====一些mysql优化与管理======
++mysql大量的 unauthenticated user错误解决
编辑服务控制脚本,在启动命令行加入–skip-name-resolve ,然后重启MysqlD服务

++管理用命令
show variables #查看所有变量值
– max_connections 数据库允许的最大可连接数,
#需要加大max_connections可以在my.cnf中加入 max_connections=1000,可以对与下面的threads_connected值决定是否需要增大.

show status [like ....];
– threads_connected 数据库当前的连接线程数
#FLUSH STATUS 可以重置一些计数器

show processlist;
kill id;

++加初始启动SQL
修改启动脚本
MYSQL_INIT_FILE=/blog.zhangjianfeng.com/app/mysql/conf/xxx_init.sql
$bindir/mysqld_safe –datadir=$datadir –init-file=$MYSQL_INIT_FILE –pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

++my.cnf配置
[mysqld]
datadir=/jeffrey/app/data/mysql
–Enable Slow Query Log
long_query_time=3
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-queries-not-using-indexes
# mysqldumpslow -s c -t 20 host-slow.log  #访问次数最多的20个sql语句
# mysqldumpslow -s r -t 20 host-slow.log  #返回记录集最多的20个sql

–others
max_connections=500  #用过的最大连接数SHOW Status like ‘max_used_connection’;
wait_timeout=10   #终止所有空闲时间超过 10 秒的连接
table_cache=64 #任何时间打开表的总数
max_binlog_size=512M #循环之前二进制日志的最大规模
expire_logs_days = 7 #仅保留7天的binlog
max_connect_errors = 100
query_cache_size = 256M #查询缓存,,默认是0
#可用 SHOW STATUS LIKE ‘qcache%’;查看命中率
#FLUSH STATUS重置计数器,  FLUSH QUERY CACHE清缓存
thread_cache = 40 #线程使用,SHOW STATUS LIKE ‘Threads_created %’; 值快速增加的话考虑加大
key_buffer = 16M #show status like ‘%key_read%’;  Key_reads 代表命中磁盘的关键字请求个数
#A: 到底 Key Buffer 要设定多少才够呢? Q: MySQL 只会 Cache 索引(*.MYI)，因此参考所有 MYI文件的总大小
sort_buffer_size = 4M  #查询排序时所能使用的缓冲区大小,每连接独享4M
#show status like ‘%sort%’; 如sort_merge_passes很大，就表示加大
sort_buffer_sizesort_buffer_size = 6M #查询排序时所能使用的缓冲区大小,这是每连接独享值6M
read_buffer_size = 4M　#读查询操作所能使用的缓冲区大小
join_buffer_size = 8M    #联合查询操作所能使用的缓冲区大小
skip-locking  #取消文件系统的外部锁
skip-name-resolve
thread_concurrency = 8　　# 最大并发线程数,cpu数量*2
thread_cache_size = 64      # 按照内存大小来设置, 1G=8, 2G=16, 3G=32, >3G=64
tmp_table_size = 256M  #临时表大小
back_log = 300 #MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。

附my.cnf

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
datadir=/blog.zhangjianfeng.com/data/mysql
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
skip-name-resolve
key_buffer = 128M
max_allowed_packet = 2M
table_cache = 128
sort_buffer_size = 6M
read_buffer_size = 4M
join_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 32
query_cache_size= 256M
thread_concurrency = 4
max_binlog_size=512M
tmp_table_size = 256M
log_slow_queries = /blog.zhangjianfeng.com/logs/mysql-slow.log
long_query_time = 2
log-bin=mysql-bin
server-id       = 1
max_connections = 1000
back_log = 300
wait_timeout = 30
open_files_limit = 5000

[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 500M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 64M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

检查my.cnf设置是否合理的一个工具 
mysqlreport 

++ 杂
* 发现问题：系统响应慢、load avg >=5、IO wait >= 10、swap使用情况、mysql status、mysql report、mysql 5.1 profline
* xfs：如果可能，mysql数据文件系统分区采用xfs，效果高30％～50％
* innodb_buffer_pool_size：如果是专用的数据库服务器，设定为内存的80％吧
* MyIsam适合低并发、低更新、高读取的需求，InnoDB适合高并发、高更新、高读取的需求，MyIsam读取的速度比InnoDB快许多。
* Explain：查询检查、查询优化
* 联合索引：貌似MySQL一次查询只用到一个索引，联合索引要注意先后顺序的问题，“左派”比较吃香。
* 字段按需配置，能用TinyINT的不用INT，字段越短越好，具体差别可以参见：
* 把大表拆成小表，如果表字段里面有Text，尽量拆开吧。
* 缩短事务周期
* 字符型的字段，最好采用前缀索引。
* 复杂的查询拆成小的简单的查询，比如用循环查询替代。这点不敢苟同，还是按照自己的业务测试一下吧。一条sql语句总比N条要快吧，除非有问题。
* left join：把条目数少的放左边，如果你了解left join的话，这个是肯定的。
* 实时备份：用slave做实时备份吧，一个slave就是一个备份。

转://