L002-oldboy-mysql-dba-lesson02

 

 

 

 

 

 

L002-oldboy-mysql-dba-lesson02

 

 

 

[root@web01 ~]# mysql -uroot -ptestpassword

mysql> use mysql;

mysql> show tables;

show create table user;

use test;

create table t1 (id int, name char(100), age smallint, primary key(id)) engine=innodb;

 

create table t1 (id int, name char(100), age smallint, primary key(id)) engine=innodb;

show create table t1;

alter table t1 add index test_index(name);

alter table t1 add index test_index2(age);

alter table t1 add index test_index3(name,age);

show global variables;

 

show global variables like '%pool%';

show variables like '%timeout%';

set global wait_timeout=20; set session wait_timeout=20;

 

insert into t1 values(1,'aaaa',25);

insert into t1 values(2,'bbbbb',25);

insert into t1 values(3,'bbbbb',25);

insert into t1 values(3,'cccc',25);

insert into t1 values(4,'cccc',25);

select * from t1;

 

show global variables like '%per_table%';

show global variables like '%innodb%';

show engines \G

show global variables like '%autocommit%';

 use mysql;

 show table status like '%host%'\G

show global variables like '%autocommit%';

 

show global variables like '%_io_thread%';

show global variables like '%key_check%';

show global variables like '%isolation%';

 

 

 

 

 

 

[root@web01 ~]# vi /var/lib/mysql/my.cnf

131 innodb_buffer_pool_size = 384M

132 innodb_buffer_pool_instances = 4

133 innodb_file_per_table = 1      #设置innodb独立独立表空间

134 innodb_flush_method = O_DIRECT

135 default_storage_engine = innodb

136 transaction-isolation = READ-COMMITTED 

 

 

[root@web01 ~]# mysqladmin -ptestpassword shutdown   #优雅的关闭数据库

[root@web01 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql/my.cnf &

 

 

 

 

#删除bin-log；

mysql> show master logs;

mysql> purge master logs to 'mysql-bin.000003';

mysql> show master logs; 

mysql> purge master logs before '2016-03-17 00:00:00';

mysql> show master logs;

 

show global variables like '%expire%';

show full processlist;  

show global variables like '%slow%';   # 开启慢查询

show global variables like '%connection%';

 

show global variables like '%prof%'; 

setprofiling=1; set global profiling=1;

show global variables like '%prof%';  

select * from mysql.user;

show profile;   #查看上一句sql的资源消耗瓶颈在哪里！

show profile all;

 

 

#修复表语句

[root@web01 ~]# /usr/local/mysql/bin/myisamchk -r /var/lib/mysql/mysql/*.MYI

 

#主从同步出现错误的时候，一般会跳过这一条错误，尽快恢复主从结构；

show slave status\G;

slave stop; set global sql_slave_skip_counter=1; slave start;

show slave status\G;

 

#监控innodb的状态

show global variables like '%io_thread%';

show engine innodb status\G   #查看innodb有无死锁

 

 

show global status;       #查看目前数据库的状态

show global status like '%Com_insert%';

 

 

#统计mysql数据库每秒查询值（同理每秒删除、插入、更新）

[root@web01 ~]# mysqladmin -S /tmp/mysql.sock  -uroot -ptestpassword -i 1 -r exten |grep Com_select     #-i 1 间隔1秒钟； -r当前值减去历史积累值；exten = exten-status

[root@web01 ~]# mysqladmin -S /tmp/mysql.sock  -uroot -ptestpassword -i 1 -r exten |grep -E "Com_select|Com_update|Com_delete|Com_insert"

 

 

#不重启mysql，直接使得参数生效的方法（有一定风险，慎重使用）

[root@web01 ~]# /usr/local/mysql/bin/mysqladmin -S /tmp/mysql.sock -ptestpassword shutdown

[root@web01 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql/my.cnf &

show global variables like '%log_slave_updates%';

[root@web01 ~]# yum install -y gdb

[root@web01 ~]# gdb -p $(pidof mysqld) ex "set opt_log_slave_updates=1"

(gdb) quit    y

show global variables like '%log_slave_updates%';  #此法比restart 数据库要快很多

 

 

#mysql版本升级

需要执行此命令，进行修复一些元数据。

把老表all db dump出来进行恢复之后

[root@web01 ~]# /usr/local/mysql/bin/mysql_upgrade -S /tmp/mysql.sock -ptestpassword

 

 

#mysql更换引擎

mysql> alter tables t1 engine=innodb;

 

[root@oldboy mysql]# mysql -S /tmp/mysql.sock -u root -ptestpassword -e "use test;show tables" | grep -v Tables_in | sed 's/^/alter table /g' | sed 's/$/ engine=innodb;/g' > abc.txt

[root@oldboy mysql]# vi abc.txt

alter table t1 engine=innodb;

alter table t2 engine=innodb;

alter table t1 engine=innodb;

 

 

 

[MySQL]表在线重定义pt-online-schema-change：

http://www.2cto.com/database/201401/272616.html

http://blog.itpub.net/22664653/viewspace-772250/

 

mysql分页优化 PPC2009_mysql_pagination：

http://wenku.baidu.com/link?url=ad9HREssC8iiVe_wPMvVBMqiAyoTukEGRS1Y_qo-8xtTOfIeYhExsjAazvXVAKzbVSL5FvlUgeqJSny1C9eSJnJcCoucD750rvc8oLiv-JC

 

 

 

 

mysql> select current_time ;

mysql> select now();   #这个带日期

mysql> select unix_timestamp();

+------------------+

| unix_timestamp() |

+------------------+

|1458482559 |

+------------------+

1 row in set (0.00 sec)

mysql> select from_unixtime(1458482559);

+---------------------------+

| from_unixtime(1458482559) |

+---------------------------+

| 2016-03-20 22:02:39       |

+---------------------------+

1 row in set (0.00 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

来自为知笔记(Wiz)

附件列表

 

转载于:https://www.cnblogs.com/bolinzhihua/p/5650767.html