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 statusG;
slave stop; set global sql_slave_skip_counter=1; slave start;
show slave statusG;
#监控innodb的状态
show global variables like '%io_thread%';
show engine innodb statusG #查看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：
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)
