一、使用binlog日志
功能：mysql的binlog日志有增量备份的功能
1，binlog日志（二进制日志）
记录在数据库服务器上执行的且使数据发生改变的sql语句。
2，启用binlog日志
vim /etc/my.cnf文件的mysqld订一块中添加log-bin字段
| [root@localhost ~]# vim /etc/my.cnf [root@localhost ~]# grep -vE "^#|^$" /etc/my.cnf [mysqld] log-bin //添加该字段启动binlog日志 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 character_set_server=utf8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@localhost ~]# ls /var/lib/mysql/localhost-bin* ls: 无法访问/var/lib/mysql/localhost-bin*: 没有那个文件或目录 [root@localhost ~]# service mysql restart Shutting down MySQL.... [确定] Starting MySQL... [确定] [root@localhost ~]# ll /var/lib/mysql/localhost-bin* -rw-rw---- 1 mysql mysql 120 3月 19 14:32 /var/lib/mysql/localhost-bin.000001 -rw-rw---- 1 mysql mysql 23 3月 19 14:32 /var/lib/mysql/localhost-bin.index [root@localhost ~]# |
注：重新启动mysql服务之后，会生成两个binlog文件（默认存放在/var/lib/mysql目录下）；
localhost-bin.000001：这个文件查看使用mysqlbinlog命令；当该文件大于500M的时候，会自动生成新的，并且自动编号。
该文件只记录在数据库服务器上执行的且使数据发生改变的sql语句。
localhost-bin.index ：记录备份的文件名，如localhost-bin.000001
3，binlog记录的方式
偏移量
时间点
根据binlog记录的方式来读localhost-bin.000001文件：
mysqlbinlog [选项] localhost-bin.000001
选项为偏移量或时间点；
指定偏移量的格式：
--start-position=起始位置
--stop-position=结束为止
指定时间点的格式：
--start-datetime="YYYY-mm-ddHH:MM:SS"
--stop-datetime="YYYY-mm-ddHH:MM:SS"
4，使用binlog日志恢复数据
[root@localhost~]#mysqlbinlog --start-position="306" --stop-position="873"/mysqllog/gbw-bin.000002 | mysql -uroot -p123456 -hlocalhost
5，更改binlog文件的目录
默认放在/var/lib/mysql目录下；
示例：
选择将binlog文件放在/var/lib/mysql/binlog下；
名字显示为zsp-bin.xxx格式
具体步骤如下：
| [root@localhost mysql]# pwd /var/lib/mysql [root@localhost mysql]# mkdir binlog [root@localhost mysql]# chown mysql:mysql binlog [root@localhost mysql]# ll -d binlog drwxr-xr-x 2 mysql mysql 4096 3月 19 19:18 binlog [root@localhost mysql]# vim /etc/my.cnf [root@localhost mysql]# grep -vE "^#|^$" /etc/my.cnf [mysqld] log-bin=/var/lib/mysql/binlog/zsp-bin.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 character_set_server=utf8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@localhost mysql]# service mysql restart Shutting down MySQL.. [确定] Starting MySQL. [确定] [root@localhost mysql]# ll binlog 总用量 12 -rw-rw---- 1 mysql mysql 143 3月 19 19:18 zsp-bin.000001 -rw-rw---- 1 mysql mysql 74 3月 19 19:18 zsp-bin.index [root@localhost mysql]# |
6，手动生成新的binlog日志的方式
重新启动mysql服务器：service mysql restart（生产环境中很少用）
在linux命令行执行：mysql -hlocalhost -uroot -p123456 -e "flush logs"
注：-e选项，在命令行下执行sql语句
登录mysql执行：flush logs //刷新日志
在完整备份的时候执行：mysqldump-hlocalhost -uroot -p123456 --flush-logs test88 > /root/88.sql
7，删除binlog日志
第一种：删除早于指定版本的binlog日志
登录mysql状态下执行： purge master logs to "binlog文件";
示例：删除000008之前的全删掉
| mysql > purge master logs to "binlog"; |
第二种：删除当前所有的binlog日志文件
登录mysql状态下执行：reset master;
第三种：命令行rm删除对应文件
二、mysql的binlog日志能够增量备份的功能
1，mysql日志类型：（不同的日志记录不同的信息）
注：不同的日志记录不同的信息，功能也不相同；
如果开启的话，可以在/etc/my.cnf文件里开启；
默认存储的位置都在/var/lib/mysql目录下
错误日志：数据库服务在启动和运行时，产生的错误信息，默认是开启的；
查询日志：在数据库服务器上执行的所有的sql操作，默认不开启；
开启可以在配置文件/etc/my.cnf中添加选项：
general-log //开启查询日志（默认文件在数据库根目录下）
general-log-file= //指定查询日志的位置（没必要指定）
注：如果使用指定位置的话，需要跟完整的路径和文件名，如果指定位置，则就已经开启了，所以，两个设置语句只存在一个即可！
默认文件位置和文件名：
重新启动mysql服务，会在/var/lib/mysql目录下产生localhost.log文件
慢查询日志：超出指定时间，显示查询结果的sql语句，默认不开启；
[mysqld]
slow-query-log //开启慢查询日志（默认存放在数据库根目录下）
slow-query-log-file= //指定存放的路径(和开启设置语句存在一个)
long-query-time= //指定超时时间，默认为10秒
默认文件位置和文件名：
配置完成，重启mysql服务之后在/var/lib/mysql生成文件localhost.slow.log
数据备份练习题
0、设置数据管理员从本机登陆的密码为tarena.
setpassword=password("tarena");
1、备份数据库服务器上的所有库，备份文件名alldb.sql备份文件保存到/mysqldir文件夹里
mysqldump -h localhost -uroot -p tarena --all-databases> /mysqldir/alldb.sql
2、备份数据库服务器上的mysql、userdb 两个数据库，备份文件名twodb.sql 备份文件保存到/mysqldir文件里
[root@localhost mysql]# mysqldump -hlocalhost -u root-ptarena -B mysql userdb > /mysqldir/twodb.sql
3、备份数据库服务器上的userdb库里的user_tab表，备份文件名suerdb-user.sql 备份文件保存到/mysqldir文件里
[root@localhost ~]# mysqldump -hlocalhost -u root-ptarena userdb user_tab > /mysqldir/suserdb-user.sql
4、删除userdb库及studb.t1表，使用备份文件恢复删除的数据。
[root@localhost ~]# mysqldump -hlocalhost -uroot -ptarena-B studb userdb > /mysqldir/tow.sql
注：如果只备份userdb库的话，则备份文件里没有create database 语句，所以恢复的时候需要指定库名，
如果同时备份多个的话，就会有createdatabase语句，这时可以恢复（不加数据库名）
| [root@localhost ~]# mysqldump -hlocalhost -uroot -ptarena studb t3 > /mysqldir/studb.t3.sql mysql> drop database userdb; mysql> drop table studb.t3; [root@localhost ~]# mysql -h localhost -uroot -ptarena < /mysqldir/twodb.sql [root@localhost ~]# mysql -hlocalhost -uroot -ptarena studb < /mysqldir/studb.t3.sql |
5、可以在192.168.1.100的主机上使用plj用户，备份数据库服务器上的所有库和表 ，备份到本机的/mysqlback目录里，并验证是否能备份成功。
| [root@localhost ~]# mysql -ptarena mysql> grant all on *.* to plj@"localhost" identified by '123' with grant option; [root@localhost ~]# mysqldump -hlocalhost -uplj -p123 --all-databases > /mysqldir/pljmysql.sql [root@localhost ~]# ll /mysqldir/pljmysql.sql -rw-r--r-- 1 root root 603203 3月 19 16:36 /mysqldir/pljmysql.sql [root@localhost ~]# |
binlog日志练习题
1、开启当前数据库服务的binlog日志功能：以自己的名字命名binlog文件，binlog文件存放到/mysqllog 目录里
| [root@localhost mysql]# pwd /var/lib/mysql [root@localhost mysql]# mkdir binlog [root@localhost mysql]# chown mysql:mysql binlog //修改该文件的所有者和所属组都为mysql [root@localhost mysql]# ll -d binlog drwxr-xr-x 2 mysql mysql 4096 3月 19 19:18 binlog [root@localhost mysql]# vim /etc/my.cnf [root@localhost mysql]# grep -vE "^#|^$" /etc/my.cnf [mysqld] log-bin=/var/lib/mysql/binlog/zsp-bin.log //添加这一段，指定开启binlog日志，并指定存储位置 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 character_set_server=utf8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@localhost mysql]# service mysql restart //重启mysql服务会重读my.cnf文件，生成binlog文件 [root@localhost mysql]# ll binlog 总用量 8 -rw-rw---- 1 mysql mysql 143 3月 19 19:18 zsp-bin.000001 -rw-rw---- 1 mysql mysql 74 3月 19 19:18 zsp-bin.index [root@localhost mysql]# |
2、在db1库里创建test1表并向表内插入3条记录并用单独的binlog文件保存db1库的初始数据
| [root@localhost binlog]# mysql -ptarena -e "flush logs" //刷新一下，生成一个新的binlog文件。。02 [root@localhost binlog]# ll 总用量 12 -rw-rw---- 1 mysql mysql 143 3月 19 19:18 zsp-bin.000001 -rw-rw---- 1 mysql mysql 120 3月 19 19:40 zsp-bin.000002 -rw-rw---- 1 mysql mysql 222 3月 19 19:40 zsp-bin.index [root@localhost binlog]# mysql -ptarena -e "create database db1" [root@localhost binlog]# mysql -ptarena mysql> create table db1.test1(id int(3)); mysql> insert into db1.test1 values -> (1),(2),(3) -> ; mysql> quit [root@localhost binlog]# mysql -ptarena -e "flush logs" //执行flush刷新生成新的binlog文件 [root@localhost binlog]# ll 总用量 16 -rw-rw---- 1 mysql mysql 143 3月 19 19:18 zsp-bin.000001 -rw-rw---- 1 mysql mysql 422 3月 19 19:40 zsp-bin.000002 //该文件保存了之前的操作 -rw-rw---- 1 mysql mysql 120 3月 19 19:40 zsp-bin.000003 -rw-rw---- 1 mysql mysql 259 3月 19 19:40 zsp-bin.index [root@localhost binlog]# zsp-bin.000002文件里的内容保存的就是db1库的初始数据 |
3、在db2库里创建test2表并向表内插入3条记录并用单独的binlog文件保存db2库的初始数据
| [root@localhost binlog]# mysql -ptarena mysql> create database db2; mysql> create table db2.test2(id int(3)); mysql> insert into db2.test2 values -> (3),(4),(5) -> ; mysql> quit [root@localhost binlog]# mysql -ptarena -e "flush logs" //刷新，生成新的binlog文件 [root@localhost binlog]# ll 总用量 20 -rw-rw---- 1 mysql mysql 143 3月 19 19:18 zsp-bin.000001 -rw-rw---- 1 mysql mysql 422 3月 19 19:40 zsp-bin.000002 -rw-rw---- 1 mysql mysql 572 3月 19 19:46 zsp-bin.000003 //生成新..04之后，该文件保存了db2上的操作 -rw-rw---- 1 mysql mysql 120 3月 19 19:46 zsp-bin.000004 -rw-rw---- 1 mysql mysql 296 3月 19 19:46 zsp-bin.index [root@localhost binlog]# |
zsp-bin.000003中单独保存了db2的初始数据
4、删除test1、test2表里的所有记录后，分别再插入3条新的记录，然后再删除test1、test2表里的所有记录
| mysql> delete from db1.test1; mysql> delete from db2.test2; mysql> insert into db1.test1 values (100),(200),(300); mysql> insert into db2.test2 values (300),(400),(500); |
5、用binlog日志恢复2个表的初始数据和后插入的3条记录
| [root@localhost binlog]# mysqlbinlog /var/lib/mysql/binlog/zsp-bin.000002 | mysql -hlocalhost -uroot -ptarena [root@localhost binlog]# mysqlbinlog /var/lib/mysql/binlog/zsp-bin.000003 | mysql -hlocalhost -uroot -ptarena [root@localhost binlog]# mysqlbinlog zsp-bin.000004 //先查看一下具体内容 [root@localhost binlog]# mysqlbinlog --start-position="900" --stop-position="1266" /var/lib/mysql/binlog/zsp-bin.000004 | mysql -hlocalhost -uroot -ptarena |
6、查看是否恢复成功
| [root@localhost binlog]# mysql -ptarena -e "select * from db1.test1" Warning: Using a password on the command line interface can be insecure. +------+ | id | +------+ | 100 | | 200 | | 300 | | 1 | | 2 | | 3 | +------+ [root@localhost binlog]# mysql -ptarena -e "select * from db2.test2" Warning: Using a password on the command line interface can be insecure. +------+ | id | +------+ | 3 | | 4 | | 5 | | 300 | | 400 | | 500 | +------+ [root@localhost binlog]# |
7 通过计划任务实现以下功能：
| 7.1,每周日半夜11点对数据库服务器上的webde库做完整备份，并用系统日期做备份文件名，数据库备份文件保存到/mysqlbak目录里。每次完整备份后，创建新的binlog日志文件。 [root@localhost binlog]# service crond start 正在启动 crond： [确定] [root@localhost binlog]# chkconfig crond on [root@localhost binlog]# crontab -e crontab: installing new crontab [root@localhost binlog]# crontab -l 0 23 * * 7 /usr/bin/mysqldump mysqldump -hlocalhost -u root -ptarena webdb > /mysqlbak/webdb-$(date +\%y\%m\%d).sql 0 23 * * 7 /usr/bin/mysql -hlocalhsot -uroot -ptarena -e "flush logs" [root@localhost binlog]# 7.2,每周一半夜12点备份数据库服务器上所有的binlog文件到系统的/binlogdir目录下。 [root@localhost binlog]# crontab -e //做计划任务 crontab: installing new crontab [root@localhost binlog]# crontab -l 0 23 * * 7 /usr/bin/mysqldump mysqldump -hlocalhost -u root -ptarena webdb > /mysqlbak/webdb-$(date +\%y\%m\%d).sql 0 23 * * 7 /usr/bin/mysql -hlocalhsot -uroot -ptarena -e "flush logs" 0 0 * * 1 /bin/cp -rp /var/lib/mysql/binlog/* /binlogdir [root@localhost binlog]# |
