vim /etc/my.cnf

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log-bin=mysql-bin
server-id=1

systemctl restart mysqld.service

#expire_logs_days = 10
#max_binlog_size = 100M 

Expire_logs_days :定义了mysql清除过期日志的时间。

二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。启动时和二进制日志循环时可能删除。
max_binlog_size

如果二进制日志写入的内容超出给定值，日志就会发生滚动。你不能将该变量设置为大于1GB或小于4096字节。 默认值是1GB。

mysqlbinlog  /var/lib/mysql/mysql-bin.000001 -v --base64-output=DECODE-ROWS
# 仅打印某天的log一直到最后一行(从5851634行，打印到末尾行)
mysqlbinlog  /var/lib/mysql/mysql-bin.000001 -v --base64-output=DECODE-ROWS | sed -n '5851634,$p' >> /home/yyxz/yyxz/logs/0919sqlbin.log
# 仅打印某天的log一直到最后一行(从5851634行，打印到5851635行)
mysqlbinlog  /var/lib/mysql/mysql-bin.000001 -v --base64-output=DECODE-ROWS | sed -n '5851634,5851635p' >> /home/yyxz/yyxz/logs/0919sqlbin.log

#190717 11:42:05 server id 1  end_log_pos 3491 CRC32 0x4a91dcfe     Write_rows: table id 119 flags: STMT_END_F### INSERT INTO `yyxz_dev`.`t_send_to_target_configs`### SET###   @1=67###   @2='ARTICLE'###   @3=NULL###   @4=1###   @5='GROUP'###   @6=1563334925###   @7=1563334925# at 3491

# mysqladmin flush-logs 也可以重新开始新的binary log
mysql -h localhost -u root -pmysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000002 |      154 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
# 数据库恢复指令mysqlbinlog  --no-defaults /var/lib/mysql/mysql-bin.000001|mysql -uroot -p

# 主数据库
[root@localhost ~]#  mysql -u root -p
mysql> GRANT replication slave ON *.* TO 'slave_yyxz'@'%' IDENTIFIED BY 'yyxz';
mysql> show master status;
+------------------+----------+-----------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB    | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+-----------------+------------------+-------------------+
| mysql-bin.000017 |   358322 | yyxz_dev |                  |                   |
+------------------+----------+-----------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

# 从数据库
# slave数据库设置server-id
sudo vim /etc/my.cnf
## log_bin
server-id=2
# 然后登陆msyql
mysql> change master to
    -> master_host="al.bianxh.top",
    -> master_user="slave_yyxz",
    -> master_password="yyxz",
    -> master_port=22027,
    -> master_log_file="mysql-bin.000017",
    -> master_log_pos=358322;
# 查看设置状态
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status;

# 如果使用expect命令来自动输入密码，需要提前安装expect
yum install expect
# 数据库备份
mysqldump -h 192.168.1.226 -u root -pyyxz  yyxz_prod_mirror > /home/yyxz/yyxz/logs/mysql_$(date "+%Y%m%d_%H:%M:%S").sql
## 阿里云备份脚本/home/yyxz/yyxz-master/DBBackup/backup-AliyunRDS.sh
mysqldump --single-transaction -h rm-m5efcwv7k72h83os1.mysql.rds.aliyuncs.com -P 3306 -u yyxz --set-gtid-purged=off -pyyxz  yyxz_prod > /home/yyxz/yyxz-master/DBBackup/yyxz_prod_AliyunRDS.sql
# 数据库恢复
mysql -h 192.168.1.226 -u root -pyyxz yyxz_cayz < mysql_20191009_15\:21\:54.sql

bianxhdeMacBook-Pro:~ bianxh$ cat ~/.bash_profile 
...
alias ll="ls -al"

[root@localhost ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

# centos的yum 源中默认是没有mysql的，所以我们需要先去官网下载mysql的repo源并安装。MySQL8.0 YUM源
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

# 安装 yum repo文件并更新 yum 缓存
rpm -ivh /home/yyxz/download/mysql80-community-release-el7-3.noarch.rpm
# /etc/yum.repos.d目录下查看到mysql的源信息
[root@localhost yum.repos.d]# ll /etc/yum.repos.d/
-rw-r--r--  1 root root 2076 Apr 25  2019 mysql-community.repo
-rw-r--r--  1 root root 2108 Apr 25  2019 mysql-community-source.repo
# 修改DNS地址为阿里云
vim /etc/resolv.conf
## DNS地址增加阿里云
nameserver 223.5.5.5
## 重启网络服务使其生效
service network restart
# 查看可用的mysql安装源
yum repolist enabled | grep mysql
# 查看可用的mysql版本以及禁用/启用情况：
yum repolist all | grep mysql

# 修改源文件,禁用5.7,启用8.0
## 把里面的8.0enabled改为1就可以了，其他的版本改为0
vim /etc/yum.repos.d/mysql-community.repo
# 修改完之后查看可用版本(显示8.0即为成功)
yum repolist enabled | grep mysql
# 8.0版本启用后,安装Mysql
yum install -y mysql-community-server
# 升级后新建用户并授权
create user 'yyxz'@'%' identified by 'yyxz';
grant all privileges on *.* to 'yyxz'@'%';
FLUSH PRIVILEGES;

# 进入网站下载：https://dev.mysql.com/downloads/mysql/
# 选择：Red Hat Enterprise Linux / Oracle Linux
# 下载地址
https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-community-client-8.0.18-1.el7.x86_64.rpm
https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-community-server-8.0.18-1.el7.x86_64.rpm

mysql> show global variables like "%timeout%";
官方文档说明：https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html

 **售后工程师 :**    **您好，麻烦您使用explain 分析下您这条语句在RDS实例和您本地的执行情况，看看扫描的行数等信息是否一样，另外您也可以参考以下方法看下您的SQL消耗时间情况：
set profiling=1;                       (启动profile ，这是一个session级别的配置)
select * form xxx;                 （ 执行查询语句）
show profiles;                        (查看每个查询消耗的时间信息)
show profile for query N          (查询ID为N的每个阶段消耗的时间，N为上个命中query的值)
show profile cpu for query N;       (查询CPU消耗的信息)
SHOW PROFILE block io, cpu FOR QUERY 126;       (查询io,CPU消耗的信息)
对比下您的执行情况**  

# 查看innodb缓存
 SHOW VARIABLES LIKE 'innodb_buffer_pool%';
# 查询当下的动作
 SHOW PROCESSLIST;