Author xiuhongChen Date 2020/9/23 Desc Centos7中安装Mysql、mysqldump全量备份、binlog增量备份、数据恢复、开启定时任务备份数据库等

注意：虚拟机版本不同，mysql源不一样。 版本centos7安装步骤：

在MySQL官网中下载YUM源rpm安装包：http://dev.mysql.com/downloads/repo/yum/

#下载mysql源安装包 shell> wget --no-check-certificate http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

#安装mysql源 shell> yum localinstall mysql57-community-release-el7-8.noarch.rpm

#检查mysql源是否安装成功 shell> yum repolist enabled | grep “mysql.-community.”

#如果存在多个数据源，启用或者禁用最新mysql数据源，此处使用Mysql5.7 yum-config-manager --disable mysql55-community yum-config-manager --disable mysql56-community yum-config-manager --enable mysql57-community-dmr

yum repolist enabled | grep mysql

shell> yum install mysql-community-server

shell> vim /etc/my.conf

shell> systemctl start mysqld.service #启动Mysql shell> systemctl restart mysqld.service #重启Mysql shell> systemctl stop mysqld.service #关闭Mysql shell> systemctl status mysqld.service #查看Mysql服务状态 备注：centos6.5中启动mysql命令和centos7不一致，service mysqld start

看到下图说明启动成功：

mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。 有三种方式找到root默认密码：

方法一、找回初始密码(推荐) grep "password" /var/log/mysqld.log SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

方法二、使用操作系统账号和密码，进行修改密码 1、mysql -uroot -p 2、输入你的centos的密码 3、UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword') WHERE User = ‘root’ AND Host = ‘localhost’; 注：如果显示 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements，说明你的密码不符合安全要求， 4、FLUSH PRIVILEGES; 5、quit;

方法三、强制进行修改密码（本例使用）

#允许root用户远程可以访问 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

备注：若在腾讯云服务器上安装Mysql，记得在安全组开放3306端口 使用navicate可以登陆成功。

Mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。

-u: 指定用户 -p: 指定密码 -single-transaction: 确保事务性操作，只对 innodb 有效，保证备份期间没有 DDL 操作 -l (–lock-table): 对于非 Innodb 引擎的备份进行锁表，只能进行读操作。与 single-transaction 互斥 -x，–lock-all-table: 给实例下的所有数据库的表进行加锁，保证一致性，该参数会导致在备份过程中数据库只读，不可写 -d: 只备份表结构，不备份数据 -master-data: 有两个值: 1 和 2。1 时只记录change master 语句，为 2 时change master 会注释掉，建议设置为 2 -all-database,-A: 备份 MySQL 实例下的所有数据库 -database: 指定对应的数据库进行备份 -R, -routines: 备份所有的存储过程 -tiggers: 备份触发器 -E, –events: 备份数据库中的调度时间 -hex-blob: 将对 blog/binary 等格式的数据转为 16 进制形式进行保存 -tab=path: 在指定路径下分别生成结构文件和数据文件，会对每个表分别生成一个记录表结构的 sql 文件和记录数据的 txt 文件 -w,-where= 过滤条件，对于单表进行过滤条件的备份

1)导出整个数据库(包括数据库中的数据） mysqldump -uusername -p dbname > dbname.sql

2)导出数据库结构（不含数据） mysqldump -uusername -p -d dbname > dbname.sql

3)导出数据库中的某张数据表（包含数据） mysqldump -uusername -p dbname tablename > tablename.sql mysqldump -uroot -p -B dbname --tables tablename > tablename.sql

4)导出数据库中的某张数据表的表结构（不含数据） mysqldump -uusername -p -d dbname tablename > tablename.sql

5)备份数据库时使用参数（–ignore-table）排除某个表或多个表 mysqldump -uroot -p --ignore-table=dbname.table1 --ignore-table=dbname.table2 dbname > backup.sql

1)Shell命令 mysql -uusername -p dbname < backup.sql mysql -uusername -p -f dbname < backup.sql #-f 表示忽略出现的SQL错误 nohup mysql -uroot -p -f dbname < backup.sql > /dev/null 2>&1 & #在导入较大的sql文件时可以放到后台执行 mysql -utest -p hap_dev_backup < hap_dev_backup_ignore.sql

2）Mysql命令–source mysql -uroot -p #登录到数据库 use dbname; #切换到需要导入的库中 source /usr/local/software/mysql_backup/sys1.sql #执行SQL文件

注意：只能在cmd界面下执行source命令，不能在navicate等mysql工具里面执行source命令，会报错，因为cmd是直接调用mysql.exe来执行命令的。

binlog日志，即binary log，是二进制日志文件。它有两个作用，一是增量备份，即只备份新增的内容；二是用于主从复制等，即主节点维护了一个binlog日志文件，从节点从binlog中同步数据。我们可以通过binlog日志恢复数据。下面就介绍一下开启MySQL binlog日志的过程：

1）登录MySQL，查看binlog日志的状态 mysql> show variables like ‘%log_bin%’；

2）开启binlog 若为OFF，则修改配置文件开启binlog日志： shell> vim /etc/my.cnf log-bin=/var/lib/mysql/mysql-bin server-id=123454

3）重启Mysql服务器，并查看日志状态 shell> systemctl restart mysqld.service #服务器命令行执行 mysql> show variables like ‘%log_bin%’ #登陆Mysql查看

登陆到Mysql或者利用navicate等数据库工具。 1）查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值 mysql> show master status;

2)查看所有binlog日志列表 mysql> show master logs; 3)将内存中log日志写磁盘，保存在当前binlog文件中，并产生一个新的binlog日志文件 mysql> flush logs; 注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

4)删除所有二进制日志，并重新（mysql-bin.000001）开始记录 mysql> reset master;

1）登陆到Mysql查看日志内容 只查看第一个binlog文件的内容 mysql> show binlog events;

查看指定binlog文件的内容 mysql> show binlog events in ‘mysql-bin.000001’; 如下：图一显示”binlog_format=ROW”时更新行的日志情况，并没记录详细SQL语句 图二显示“binlog_format=MIXED”时插入数据日志，显示详细的SQL，便于阅读（关于binlog_format模式可参考第四部分） 查询binlog文件前10条纪录 show binlog events in ‘mysql-bin.000018’ limit 10

从pos点:2开始查起，查询10条 show binlog events in ‘mysql-bin.000018’ from 2 limit 10;

从pos点:2开始查起，偏移2行，查询10条 show binlog events in ‘mysql-bin.000018’ from 2 limit 2,10;

2）用Mysqlbinlog工具查看 基于开始/结束时间 shell> mysqlbinlog --start-datetime=‘2019-01-16 8:30:00’ --stop-datetime=‘2019-01-16 08:33:00’ /var/lib/mysql/mysql-bin.000018

Position：位于文件中的位置，即第一行的（#at 11584），说明该事件记录从文件第11584字节开始 Timestamp：事件发生的时间戳，即第二行的（#190116 8:31:39） Server id：服务器标识 Thread id：代理线程id Exec_time：事件的执行花费时间 Error_code：错误码 Type： 事件类型，此处为Query

1）当停止或重启服务器时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的日志文件，文件序号递增； 2）如果日志文件超过max_binlog_size（默认值1G）系统变量配置的上限时，也会生成新的日志文件（在这里需要注意的是，如果你正使用大的事务，二进制日志还会超过max_binlog_size，不会生成新的日志文件，事务全写入一个二进制日志中,这种情况主要是为了保证事务的完整性） 3）日志被刷新时，新生成一个日志文件。flush logs;

当前时间为17:40,我们执行“flush logs;”刷新日志，然后执行下列操作，将日志记录到文件“mysql-bin.000019”中。

1）创建表 17.41

2）插入数据 INSERT INTO hap_ar_customers VALUES (201, ‘C001’, ‘上海达芙妮’, 101, ‘Y’, ‘2016-08-24 16:48:17’, -1, -1, ‘2016-08-24 16:48:17’, -1);

3）查询 select * from hap_ar_customers 4）删除数据 时间：17:42 delete from hap_ar_customers where CUSTOMER_ID = 201; 5）mysql-bin.000019中记录了刚才的所有操作，所以从该binlog中恢复数据

备注： 对于多个日志文件需要恢复的时候不要单个单个执行： [root@localhost /]# mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!! [root@localhost /]# mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!! 上述操作就十分危险，如果第一个日志包含创建临时表语句CREATE TEMPORARY TABLE，第二个日志要使用该临时表，第一个导入binlog日志的进程退出后临时表会被删除，执行第二个日志文件要使用临时表时会因找不到而报 “unknown table.”

总结：所谓恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。

Mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

每一条会修改数据的sql语句会记录到binlog中。 优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。 缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。 缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

在mysql的配置文件my.cnf中，可以通过一下选项配置binglog相关

在LINUX中，周期执行的任务一般由cron这个守护进程来处理[ps -ef|grep cron]。cron读取一个或多个配置文件，这些配置文件中包含了命令行及其调用时间。 cron的配置文件称为“crontab”，是“cron table”的简写。

cron是一个linux下 的定时执行工具，可以在无需人工干预的情况下运行作业。 service crond start //启动服务 service crond stop //关闭服务 service crond restart //重启服务 service crond reload //重新载入配置 service crond status //查看服务状态

root用户制定的任务的crontab，目前只有root用户可以使用，这种方法更加方便直接给其他用户设置计划任务，而且还可以指定执行shell等（关于该文件的权限问题详见第4小节）

crontab权限问题到/var/adm/cron/下一看，文件cron.allow和cron.deny是否存在

用户所建立的crontab文件中，每一行都代表一项任务，每行的每个字段代表一项设置，它的格式共分为六个字段，前五段是时间设定段，第六段是要执行的命令段，格式如下： minute hour day month week command 顺序：分 时 日 月 周

编辑一个shell脚本，打印当前时间到txt文件中：echo $(date +%F%n%T) >> test1.txt 执行crontab -e 修改文件增加定时任务： 1）每小时的22分钟执行脚本：22 * * * * /root/test1.sh 2）每个工作日(Mon – Fri) 11:59 执行： 59 11 * * 1,2,3,4,5 /root/test1.sh 59 11 * * 1-5 /root/test1.sh 3）每两分钟执行一次命令：*/2 * * * * /root/test1.sh

服务器上全量&增量备份脚本还在老电脑上，待我找到后po出来。

希望可以帮助到正在管理数据库的小伙伴！