論mysql5.7.13性能優(yōu)化之索引優(yōu)化

第一步：配置MySQL打開(kāi)binlog日志

# vim /etc/mysql/mysql.conf.d/mysqld.cnf 

• 找到下面兩行吐咳，將前面的#去掉

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log # binlog日志目錄在/var/log/mysql/目錄下
binlog_do_db            = nst #這里是你要binlog日志記錄的數(shù)據(jù)庫(kù)名字(nst 是我的數(shù)據(jù)庫(kù)名)

• 重啟MySQL

sudo service mysql restart

• 查看binlog是否生效

# mysql -u root -p nst

mysql> show master status; # 可以看到有記錄
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      154 | nst          |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

第二步：數(shù)據(jù)測(cè)試并進(jìn)行恢復(fù)

• 查看binlog日志文件

# cd /var/log/mysql/
# sudo mysqlbinlog --base64-output=decode-rows -v mysql-bin.000005 # 5.7版本對(duì)sql語(yǔ)句加密了胧后，使用--base64-output=decode-rows 查看=

• 進(jìn)行如下三步操作:

  • 創(chuàng)建數(shù)據(jù)表test

  mysql> create table test(
      -> id int,
      -> name char(64)
      -> );
  

  • 插入數(shù)據(jù)

  mysql> insert into test values(1, 'feng'),(2, 'scort');
  

  • 刪除表

  mysql> drop table test;
  

• 現(xiàn)在test表沒(méi)有了笆怠，我想恢復(fù)test表！那我們就使用binlog日志文件來(lái)恢復(fù)

  • 查看binlog日志文件嫉沽，記錄下要恢復(fù)的開(kāi)始位置和結(jié)束位置

  # cd /var/log/mysql/
  # sudo mysqlbinlog --base64-output=decode-rows -v mysql-bin.000005 # 5.7版本對(duì)sql語(yǔ)句加密了，使用--base64-output=decode-rows 查看
  
  
  -----內(nèi)容如下-----
  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  DELIMITER /*!*/;
  # at 4
  #170814  8:08:13 server id 1  end_log_pos 123 CRC32 0x77cdc807  Start: binlog v 4, server v 5.7.19-0ubuntu0.16.04.1-log created 170814  8:08:13
  # Warning: this binlog is either in use or was not closed properly.
  # at 123
  #170814  8:08:13 server id 1  end_log_pos 154 CRC32 0x6912ae9a  Previous-GTIDs
  # [empty]
  # at 154
  #170814  8:25:22 server id 1  end_log_pos 219 CRC32 0x8e9ef4db  Anonymous_GTID  last_committed=0    sequence_number=1   rbr_only=no
  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  # at 219
  #170814  8:25:22 server id 1  end_log_pos 334 CRC32 0x91d186ce  Query   thread_id=228   exec_time=0 error_code=0
  use `nst`/*!*/;
  SET TIMESTAMP=1502670322/*!*/;
  SET @@session.pseudo_thread_id=228/*!*/;
  SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  SET @@session.sql_mode=1436549152/*!*/;
  SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  /*!\C utf8 *//*!*/;
  SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
  SET @@session.lc_time_names=0/*!*/;
  SET @@session.collation_database=DEFAULT/*!*/;
  DROP TABLE `test` /* generated by server */
  /*!*/;
  # at 334
  #170814  8:25:50 server id 1  end_log_pos 399 CRC32 0xe931e689  Anonymous_GTID  last_committed=1    sequence_number=2   rbr_only=no
  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  # at 399
  #170814  8:25:50 server id 1  end_log_pos 513 CRC32 0xe5e319b0  Query   thread_id=228   exec_time=0 error_code=0
  SET TIMESTAMP=1502670350/*!*/;
  create table test(
  id int,
  name char(64)
  )
  /*!*/;
  # at 513
  #170814  8:26:32 server id 1  end_log_pos 578 CRC32 0x817100ec  Anonymous_GTID  last_committed=2    sequence_number=3   rbr_only=yes
  /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  # at 578
  #170814  8:26:32 server id 1  end_log_pos 649 CRC32 0x489ac5f1  Query   thread_id=228   exec_time=0 error_code=0
  SET TIMESTAMP=1502670392/*!*/;
  BEGIN
  /*!*/;
  # at 649
  #170814  8:26:32 server id 1  end_log_pos 698 CRC32 0x29773e5b  Table_map: `nst`.`test` mapped to number 369
  # at 698
  #170814  8:26:32 server id 1  end_log_pos 754 CRC32 0x05cff49c  Write_rows: table id 369 flags: STMT_END_F
  ### INSERT INTO `nst`.`test`
  ### SET
  ###   @1=1
  ###   @2='feng'
  ### INSERT INTO `nst`.`test`
  ### SET
  ###   @1=2
  ###   @2='scort'
  # at 754
  #170814  8:26:32 server id 1  end_log_pos 785 CRC32 0xf408e2ea  Xid = 7852
  COMMIT/*!*/;
  # at 785
  #170814  8:27:08 server id 1  end_log_pos 850 CRC32 0xbca456aa  Anonymous_GTID  last_committed=3    sequence_number=4   rbr_only=no
  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  # at 850
  #170814  8:27:08 server id 1  end_log_pos 965 CRC32 0x649ac834  Query   thread_id=228   exec_time=0 error_code=0
  SET TIMESTAMP=1502670428/*!*/;
  DROP TABLE `test` /* generated by server */
  /*!*/;
  SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  DELIMITER ;
  # End of log file
  /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  

  at 334處看到了創(chuàng)建表的sql，這個(gè)位置是開(kāi)始位置;
  最后一條記錄commit的地方at 785,這個(gè)地方就是結(jié)束位置

  • 開(kāi)始恢復(fù)

  # sudo mysqlbinlog --start-position=334 --stop-position=785 mysql-bin.000005 | mysql -u root -p nst
  

  • 登錄庫(kù)存查看結(jié)果,恢復(fù)成功

  # mysql -u root -p nst
  
  mysql> show tables;
  +-------------------------+
  | Tables_in_nst           |
  +-------------------------+
  | test                    |
  +-------------------------+
  
  # 恢復(fù)成功
  mysql> select * from test;
  +------+-------+
  | id   | name  |
  +------+-------+
  |    1 | feng  |
  |    2 | scort |
  +------+-------+