mysql高级管理-note

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”);

转载于:https://blog.51cto.com/davideylee/1393169