Mysql授权
Mysql>grant all on *.* to user@IPidentified by “pass” 授权user在IP登陆数据库有所有权限
Mysql>select user,host,password frommysql.user; 查看授权用户
Mysql日志
日志配置
Vi /etc/my.cnf
[mysqld]
Port = 3306
Socket = /var/lib/mysql.sock
Log-slow-queries = mysql-slow.log
Log-error = mysql.err
Log = mysql.log
Log-bin = mysql-bin
查看变量
Mysql>show variables like “%log%”
日志刷新
Mysql>flush logs;
Mysql>show master status
Mysql>reset master
查看日志
mysqlbinlog --no-defaults mysql-bin.000001| more
mysql>\S系统状态各种字符集
Mysql备份恢复
备份test数据库：mysqldump –uroot –pAa23456 test –l –F > /tmp/test.sql
-l 加读锁操作–F flush logs刷新日志
备份完后有对数据库做了其他的操作比如insert into t1 values(6)
Flush logs;产生mysql-bin.000002
Truncate t1;
Drop table t1;
Show master status;删除记录在mysql-bin.000003
恢复test数据库 mysql –uroot –pAa123456 test –v -f < /tmp/test.sql
-v查看导入的详细信息–f 当中遇到错误时，skip过去
从日志中恢复备份后对数据库的一些操作 mysqlbinlog --no-defaults mysql-bin.000002 | mysql –uroot–pAa123456 test
如果导致数据库奔溃的操作也记录在日志里该怎么恢复:
查看position位置mysqlbinlog --no-defaults mysql-bin.000002 | more
查看到position位置是500则恢复操作mysqlbinlog --no-defaults --stop-position=”500” mysql-bin.000002 |mysql –uroot –pAa123456 test
还有其他参数--start-position --start-data --stop-data
Mysql主从复制
优点
主服务器出问题，可以快速切换的从服务器
可以在从服务器上执行查询操作，降低主服务器的访问压力
可以在从服务器上执行备份，以避免备份期间影响主服务器的服务
注意：如果是对实时性要求高的查询还是放到主服务器上完成
配置
授权从服务器
mysql>grant all slave on *.* touser@slaveIP identified by “pass” 或mysql>grantreplication slave on *.* to user@slaveIP identified by “pass”
修改在服务器的配置文件my.snf，开启binlog，设置server-id的值
Log-bin=mysql-bin
Server-id=1
使主服务器的读锁有效
Mysql>flush tables with read lock;
备份
mysqldump –uroot –pAa23456 test –l –F >/tmp/test.sql
清除主服务器bin-log日志
Mysql>reset master
备份完毕全部解锁
Mysql>unlock tables;
清除从服务器bin-log日志
Mysql>reset master
拷贝到从服务器恢复
mysql –uroot –pAa123456 test –v -f </tmp/test.sql
修改在服务器的配置文件my.snf，开启binlog，设置server-id的值，master-host主服务器的ip，用户和密码及端口
Log-bin=mysql-bin
Server-id=2
Master-host=masterIP
Master-user=user
Master-password=password
Master-port=3306
测试登陆
Mysql –uuser –ppassword test –hmasterIP
重启mysql
Pkill mysqld或kill -9 pid或kill -2 pid
Mysql-safe –user=mysql &
查看从服务器
Mysql>show slave status\G
Slave_IO_Running:Yes 代表拿到binlog日志
Slave_SQL_Running:Yes 可以执行binlog日志
其他操作
Start slave
Stop slave
Show slave status
Show master logs
Change master to
Show processlist
无法同步问题
Slave_SQL_Running:No
Second_Behind_Master:null
原因：
1.程序可能在slave上进行了写操作
2.也可能是slave机器重启后，事务回滚造成的
解决1：
Mysql>slave stop;
Mysql>set GLOABLESQL_SLAVE_SKIP_COUNTER=1;
Mysql>slave start;
解决2：
Mysql>Slave stop;在slave上
Mysql>show master status;在master上得到偏移量
Mysql>change master to master_host=”masterIP”,
master_user=”user”,
master_password=”password”,
master_port=”3306”,
master_log_file=”mysql-bin.000003”,
master_log_pos=98;在从服务器上执行手动同步
Mysql>slave start;
Mysql>show slave status;查看Slave_SQL_Running:Yes,Second_Behind_Master:0正常
mysql分区分表
垂直分表和水平分表(mysql5.1+)
4中分区类型
RANGE 连续区间
LIST 离散值
HASH 表达式的返回值
KEY 类似于HASH只支持一列或多列,md5
例子
RANGE分区
LIST分区
HASH分区
Mysql存储过程
Mysql>\d // 改变终结符
Mysql>create procedure p1();
Begin
Set @i=1;
While @i<=1000000 do
Insert into t1 values(@i);
Set @i=@i+1;
End while;
End //
Mysql>\d ;
Mysql>show procedure status;
Mysql>call p1();
Mysql表引擎
Mysql5.1默认是Myisam表引擎，支持事务的有innodb表引擎，要想使用分区分表innodb表引擎必需使用独立表空间(innodb_file_per_table=1)，而非共享表空间。
Mysql数据库优化
Mysql基础操作
Mysql表复制
Mysql>create table t2 like t1;
Mysql>insert into t2 select * fromt1;
Mysql索引
Mysql>alter table t1 add indexindexname (column_list);
Mysql>alter table t1 add indexunique (column_list);
Mysql>alter table t1 add indexprimary key (column_list);
Mysql>create index indexname on t1(column_list);
Mysql>create unique index indexname on t1 (column_list);
Mysql>alter table t1 modify id int unsigned not null;(消除auto_increment)
Mysql>drop index indexname on t1;
Mysql>alter table t1 drop index indexname;
Mysql>alter table t1 drop primary key;
Mysql视图
Mysql>create view viewname as select* from t1 where id>4 amd id<20;
Mysql>drop view viewname;
Mysql内置函数
字符串函数
Concat(string1,string2)
Lcase(string)
Ucase(string)
Length(string)
Ltrim(string)
Rtrim(string)
Repeat(string,count)
Repace(string,search_string,replace_string)
Substr(string,position[,length])
Space(count)
数学函数
Bin(decimal_number)
Ceiling(number)
Floor(number)
Max(number1,number2)
Min(number1,number2)
Sqrt(number)
Rand()
日期函数
Curdate()
Curtime()
Now()
Unx_timestamp(date)
From_unixtime()
Week(date)
Year(date)
Datediff(expr1,expr2)
Mysql预处理语句
Mysql>prepare stmt1 from ‘select *from t1 where id >?’;
Mysql>set @i=1;
Mysql>execute stmt1 using @i
Mysql>drop prepare stmt1;
Mysql事务处理(innodb表引擎)
Mysql>set autocommit=0
Mysql>delete from t1 where id = 1;
Mysql>savepoint p1;
Mysql>delete from t1 where id = 2;
Mysql>savepoint p2;
Mysql>rollback to p1;
Mysql存储过程
Mysql>\d // 改变终结符
Mysql>create procedure p1();
Begin
Set @i=1;
While @i<=100 do
Insert into t1(name) values(concat(“user”,@i));
Set @i=@i+1;
End while;
End //
Mysql>\d ;
Mysql>show procedure status;
Mysql>call p1();
Mysql触发器
Mysql>\d //
Mysql>create trigger tg1 before insert on t1 for each row
Begin
Insert into t2(id)values(new.id);
End//
Mysql>\d ;
Mysql>\d //
Mysql>create trigger tg2 before delete on t1 for each row
Begin
Delete from t2 whereid=old.id;
End//
Mysql>\d ;
Mysql>create trigger tg3 before update on t1 for each row
Begin
Update t2 set id=new.id whereid=old.id;
End//
Mysql>\d ;
Mysql>show triggers;
Mysql>drop trigger tg1;
重排auto_increment值
Mysql>delete fromtablename;(auto_increment不会回归1)
Mysql>truncate table tablename;
Mysql>alter table tablenameauto_increment=1
常用sql技巧
正则regexp
Mysql>selectname,email from t1 where email regexp “@163[.,]com$”
Mysql>select name,email from t1 where email like “%@163.com” orlike “%@163,com”
Rand()随机提取
Mysql>select* from stu order by rand();
Mysql>select* from stu order by rand() limit 3;
Groupby的with rollup检索更多的分组聚合信息
Mysql>select cname,pname,count(pname) from demo group by cname,pname;
Mysql>select cname,pname,count(pname) from demo group by cname,pname with rollup;
BitGroup functions做统计
Mysql>selectid,bit_or(kind) from order_rab group by id;
Mysql>selectid,bit_and(kind) from order_rab group by id;
Innodb表引擎的外键使用
Mysql>createtable temp(id int,name char(20),foreign key(id) references outTable(id) ondelete cascade on update cascade);
Help的使用
?%
?create
?opti%
?reg%
?contents
Sql语句优化
优化sql语句的步骤
Slow-log,desc
Show[sesion]
Showstatus
Showglobal status
Showstatus like ‘Com_%’
Showglobal status like ‘Com_%’
Showvariables like ‘%long%’
Show variables like ‘%slow%’
Explain和desc解析sql语句
索引问题
Createindex indexname on t1(name(4))
%加在字符串后面
And Or 前后都要使用索引
Show status like‘Handler_read%’;Handler_read_key,Handler_read_rnd_next
两个简单的优化方法
Checktable t1;
Optimizetable t1;
常用sql的优化
Select* from t1 into outfile “/tmp/t1.txt”;
Altertable t1 disable keys;(开启非唯一索引)
Loaddata infile “/tmp/t1.txt” into table t1(name);
Altertable t1 enable keys;(关闭非唯一索引)
Setunique_checks=0; (关闭唯一索引)
Setunique_checks=1;(开启唯一索引)
Setautocommit=0;(innodb表引擎)
Insertinto t1(name) values(user1),(user2),(user3),(user4);(打开表一次关闭一次)
Insertdelayed和bulk_insert_buffer_size
Groupby和Order by
Select* from t1 where id in(select uid from t2);(避免使用嵌套查询)
Select* from t1,t2 where t1.id=t2.uid;
Selectt1.* from t1 left join t2 on t1.id=t2.uid where t2.uid is not null;
Mysql数据库优化
优化表类型
拆分表
使用中间表
Createindex indexname as select from t1 where id<4;(热门帖子)
Select* from t1 procedure analyse()\G;
Myisam表锁
Locktable t1 read; select可行insert update drop 都停止
Locktable t1 write; select insert update drop 都停止
Unlock tables
Mysql服务器优化
四种字符集
\s;
My.cnf
[client]
Default-character-set= utf8 (conn.characterset)
[mysqld]
Character-set-server= utf8 (server\db\table characterset)
Collation-server = utf8_general_ci (校验characterset)
Showcharacter set;
Binarylog 日志
Showvariables like “%bin%”;
My.cnf
Log-bin=mysql-bin
Slowlog慢查询
Showvariables like “%slow%”;
Showvariables like “%long%”;
My.cnf
Log_slow_queries= slow.log
Long_query_time= 5
Socket问题
My.cnf
[client]
Port= 3306
Soket= /tmp/mysql.sock
[mysqld]
Port = 3306
Soket= /tmp/mysql.sock
Skip-locking
Mysql–uroot –p123 –protocol tcp –hlocalhost (socket文件丢失)
重启mysqld会生成/tmp/mysql.sock
Root密码问题
Servicemysqld stop
Mysqld_safe--skip-grant-tables --user=mysql &
Mysql–uroot
Mysql>setpassword=password(“123”);
Mysql>updateuser set password=password(“123”) where user=’root’;
Mysql>set password for root@localhost=password(“123”);