[root@monitor ~]# systemctl stop mysqld [root@monitor ~]# rpm -qa | egrep -i 'mysql|maria' [root@monitor ~]# rpm -qa | egrep -i 'mysql|maria' mariadb-libs-5.5.56-2.el7.x86_64 # 而实际这个mariadb-libs-5.5.56-2.el7.x86_64不需要卸载没有关系。 [root@monitor ~]# rpm -e --nodeps + 包名1 包名2 ..... # 删除老版本mysql的开发头文件和库 rm -rf /usr/lib/mysql rm -rf /usr/include/mysql # 注意：卸载后/var/lib/mysql中的数据及/etc/my.cnf不会删除，如果确定没用后就手工删除 rm -rf /etc/my.cnf rm -rf /var/lib/mysql
下载安装配置
# 下载 [root@monitor ~]# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.22-el7-x86_64.tar.gz -P /usr/local/ #下载慢的话通过浏览器下载后再传上去 # 解压添加用户和环境变量 [root@monitor ~]# mkdir -p /usr/local/mysql && cd /usr/local/ && tar -xzvf mysql-5.7.22-el7-x86_64.tar.gz && cp -arf mysql-5.7.22-el7-x86_64/* /usr/local/mysql/ && cd /usr/local/mysql/ && mkdir data && mkdir log && echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile && source /etc/profile && groupadd mysql && useradd -r -g mysql -s /bin/false mysql # 查看目录 [root@monitor mysql]# pwd /usr/local/mysql [root@monitor mysql]# ll total 36 drwxr-xr-x 2 root root 4096 Jan 4 11:07 bin -rw-r--r-- 1 7161 31415 17987 Mar 4 2018 COPYING drwxr-xr-x 2 root root 6 Jan 4 11:08 data drwxr-xr-x 2 root root 55 Jan 4 11:07 docs drwxr-xr-x 3 root root 4096 Jan 4 11:07 include drwxr-xr-x 5 root root 229 Jan 4 11:07 lib drwxr-xr-x 2 root root 6 Jan 4 11:08 log drwxr-xr-x 4 root root 30 Jan 4 11:07 man -rw-r--r-- 1 7161 31415 2478 Mar 4 2018 README drwxr-xr-x 28 root root 4096 Jan 4 11:07 share drwxr-xr-x 2 root root 90 Jan 4 11:07 support-files # 更新配置文件 [root@monitor mysql]# cp -arf /etc/my.cnf /etc/my.cnf.bak`date +%F` cat >/etc/my.cnf << EOF [client] socket=/usr/local/mysql/mysql.sock [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data pid-file=/usr/local/mysql/data/mysqld.pid socket=/usr/local/mysql/mysql.sock log_error=/usr/local/mysql/log/mysql.error.log EOF # 改为mysql用户和组 [root@monitor mysql]# cd /usr/local/mysql && chmod 750 data/ && chown -R mysql . && chgrp -R mysql . [root@monitor mysql]# ll total 36 drwxr-xr-x 2 mysql mysql 4096 Jan 4 11:07 bin -rw-r--r-- 1 mysql mysql 17987 Mar 4 2018 COPYING drwxr-x--- 2 mysql mysql 6 Jan 4 11:08 data drwxr-xr-x 2 mysql mysql 55 Jan 4 11:07 docs drwxr-xr-x 3 mysql mysql 4096 Jan 4 11:07 include drwxr-xr-x 5 mysql mysql 229 Jan 4 11:07 lib drwxr-xr-x 2 mysql mysql 6 Jan 4 11:08 log drwxr-xr-x 4 mysql mysql 30 Jan 4 11:07 man -rw-r--r-- 1 mysql mysql 2478 Mar 4 2018 README drwxr-xr-x 28 mysql mysql 4096 Jan 4 11:07 share drwxr-xr-x 2 mysql mysql 90 Jan 4 11:07 support-files # 初始化 [root@monitor mysql]# pwd /usr/local/mysql [root@monitor mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql 或 [root@monitor mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize [root@monitor mysql]# grep 'temporary password' /usr/local/mysql/log/mysql.error.log 2019-01-04T03:11:29.386050Z 1 [Note] A temporary password is generated for root@localhost: n!rDqonId0qi # 添加服务启动项 [root@monitor mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld && service mysqld status ERROR! MySQL is not running [root@monitor mysql]# chkconfig --add mysqld && chkconfig mysqld on [root@monitor mysql]# service mysqld start Starting MySQL. SUCCESS! # 首次登录要更改密码，初始化时查到的初始密码为n!rDqonId0qi [root@monitor mysql]# mysql -uroot -p Enter password:n!rDqonId0qi Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.7.22 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> use mysql; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'GoldrockMonitor01!'; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'GoldrockMonitor01!'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select host,user,password from user; ERROR 1054 (42S22): Unknown column 'password' in 'field list' mysql> select user,host,authentication_string from user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *8E058CA664EDC92380733BD602CD9A297CEE7FC4 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | +---------------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec) mysql>
mysql> show variables like '%log_bin%'; +---------------------------------+---------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------+ | log_bin | off | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+---------------------------------+ 6 rows in set (0.00 sec)
退出MySQL，使用vi编辑器修改MySQL的my.cnf配置文件
在my.cnf配置文件中添加如下图的两句
[root@localhost /]*# vim /etc/my.cnf
log-bin=mysql-bin #*#开启二进制日志（默认放到data）
查看/usr/local/mysql目录下的内容
使用命令：ls /usr/local/mysql
此时再次进入MySQL，查看binlog日志的状态。显示binlog日志为ON开启状态
mysql> show variables like '%log_bin%'; +---------------------------------+---------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------+ | log_bin | ON | | log_bin_basename | /var/mysql/data/mysql-bin | | log_bin_index | /var/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+---------------------------------+ ————————————————
1、执行：FLUSH LOGS;
刷新日志是为了实验内容更直观，更容易观察到整个实验过程的内容。
我看到网上许多文章有在用REST MASTER;而未说明此命令的严重性
这条命令会删除所有日志文件，并将文件名和记录点进行重置归零，99%的情况下是用不到这条命令的
删除日志可以用PURGE MASTER LOGS...这样保险一点
2、新日志文件已经生成，先观察一下内容，有几个点需要了解
查看二进日日志文件命令：mysqlbinlog mysql-bin.000001
# at 4 # 180903 16:19:12 server id 1 end_log_pos 123 CRC32 0xe03659b3 Start: binlog v 4, server v 5.7.22-log created 180903 16:19:12 先看上边两个箭头： # at 4（事件开始点） # 180903 16:19:12 （代表的是时间） server id 1（主备复制时需要为每个MYSQL数据库指定唯一的SERVER ID，我的未配置，默认是1） end_log_pos 123（事件结束点） 再看下边两个箭头： # at 123（事件开始点，和上边的事件结束点是对应的） end_log_pos 154（事件结束点） at 4 和 at 123之间的内容就是事件内容
3、模拟业务场景，建表，插入数据，最后将某个表删除；为了真实，我建了两个库，同时向不同的库写入内容，最后将其中一个库中的某个表删除。
mysql> FLUSH LOGS; Query OK, 0 rows affected (0.01 sec) mysql> create database t1; Query OK, 1 row affected (0.03 sec) mysql> create database t2; Query OK, 1 row affected (0.00 sec) mysql> use t1; Database changed mysql> create table t1 (id int); Query OK, 0 rows affected (0.03 sec) mysql> use t2; Database changed mysql> create table t2 (id int); Query OK, 0 rows affected (0.03 sec) mysql> insert into t2 values (3); Query OK, 1 row affected (0.01 sec) mysql> insert into t2 values (4); Query OK, 1 row affected (0.01 sec) mysql> use t1; Database changed mysql> insert into t1 values (1); Query OK, 1 row affected (0.01 sec) mysql> insert into t1 values (2); Query OK, 1 row affected (0.01 sec) mysql> use t2; Database changed mysql> insert into t2 values(20); Query OK, 1 row affected (0.01 sec) mysql> use t1; Database changed mysql> insert into t1 values(10); Query OK, 1 row affected (0.01 sec) mysql> drop table t1; Query OK, 0 rows affected (0.02 sec) mysql> use t2; Database changed mysql> insert into t2 values(222); Query OK, 1 row affected (0.01 sec) mysql> 建立T1、T2库，建立T1、T2表。 向T1插入数据：1、2、10 向T2插入数据：3、4、20、222 模拟场景，删除T1表，T2库T2表业务还在继续运行
现在将要通过日志将T1表进行恢复。
首先要先找到那个删除命令的日志点：
mysqlbinlog master-bin.000014|grep -5a "DROP TABLE"
看到#AT 2439 （记下这个数字）
在这个事件点执行的DROP TABLE操作。
由于日志文件内不只有T1库的日志，还有T2库的日志，一会只取T1数据库的日志
而且还只取2439日志点之前的日志，再进行重新应用
如果把2439的日志取的话，再应用时数据库会重新建库建表，插数据， 还会执行这条删表语句。
mysqlbinlog mysql-bin.000001 -d t1 --skip-gtids --stop-position=2439>test.sql
-d：参数是指定某个数据库日志
命令意思是将mysql-bin.000001日志文件内的T1数据库日志，事件点2439之前的日志，输出到test.sql
# tail test.sql
看看文件最后几行
登录数据库：
mysql> use t1;
Database changed
mysql> source test.sql
再查看表内容
这样数据就回来了。