20190222：分库备份并压缩、完全备份和利用二进制实现增量备份、恢复误删除的表、主从复制...

最新推荐文章于 2024-09-29 08:44:52 发布

weixin_34194551

最新推荐文章于 2024-09-29 08:44:52 发布

阅读量189

点赞数

文章标签：数据库 php 开发工具

原文链接：http://blog.51cto.com/14128387/2353966

版权

实验1：分库备份并压缩

[root@centos7 ~]#systemctl restart mariadb
[root@centos7 ~]#mysql -e 'show databases';
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hellodb |
| mysql |
| performance_schema |
| test |
| wordpress |
+--------------------+
[root@centos7 ~]#mysqldump -A |gzip > /data/all.sql.gz
[root@centos7 ~]#ll /data/all.sql.gz
-rw-r--r-- 1 root root 191 Feb 23 08:27 /data/all.sql.gz
[root@centos7 ~]#mysqldump -A |xz > /data/all.sql.xz
[root@centos7 ~]#ll /data/all
-rw-r--r-- 1 root root 141127 Feb 23 08:01 /data/all_bak.sql.gz
-rw-r--r-- 1 root root 105968 Feb 23 08:27 /data/all.sql.xz （可看到xz的压缩比更好）
我们解压： cd /data/ -> xz -d all.sql.xz 把数据库关闭，把数据库删除并恢复，步骤如下：
[root@centos7 data]#systemctl stop mariadb
[root@centos7 data]#rm -fr /var/lib/mysql/ （注意是删库不是删目录）
[root@centos7 data]#ll -d /var/lib/mysql/
drwxr-xr-x. 2 mysql mysql 6 Feb 23 08:43 /var/lib/mysql/
[root@centos7 data]#ls
all.sql all.sql.gz
[root@centos7 data]#ls /var/lib/mysql/
[root@centos7 data]#systemctl start mariadb （此时会初始化文件夹，会有新的数据生成）
[root@centos7 data]#ls /var/lib/mysql/
aria_log.00000001 aria_log_control ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock performance_schema test (皆是新数据)
[root@centos7 data]#mysql < all.sql 导入数据库
[root@centos7 data]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.60-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select user,host from mysql.user;
+--------+---------------+
| user | host |
+--------+---------------+
| wang | % |
| root | 127.0.0.1 |
| magedu | 192.168.141.% |
| wpuser | 192.168.151.% |
| root | ::1 |
| root | localhost |
+--------+---------------+
6 rows in set (0.00 sec)
[root@centos7 data]#for db in mysql -e 'show databases'|grep -Ev 'Database|information_schema|performance_schema';do mysqldump -B $db |gzip > /data/${db}bakdate +%F.sql;done 函数方法
[root@centos7 data]#ll
total 156
-rw-r--r-- 1 root root 525 Feb 23 09:36 db1_bak_2019-02-23.sql
-rw-r--r-- 1 root root 1868 Feb 23 09:36 hellodb_bak_2019-02-23.sql
-rw-r--r-- 1 root root 139780 Feb 23 09:36 mysql_bak_2019-02-23.sql
-rw-r--r-- 1 root root 516 Feb 23 09:36 test_bak_2019-02-23.sql
-rw-r--r-- 1 root root 519 Feb 23 09:36 wordpress_bak_2019-02-23.sql
我们不用函数，用管道传过去：
[root@centos7 data]#mysql -e 'show databases'|grep -Ev 'Database|information_schema|performance_schema'|sed -r 's/(.)/mysqldump -B \1 |gzip > \/data\/\1.bak.sql/' |bash sed命令
[root@centos7 data]#ll
total 156
-rw-r--r-- 1 root root 526 Feb 23 09:45 db1.bak.sql
-rw-r--r-- 1 root root 1868 Feb 23 09:45 hellodb.bak.sql
-rw-r--r-- 1 root root 139780 Feb 23 09:45 mysql.bak.sql
-rw-r--r-- 1 root root 516 Feb 23 09:45 test.bak.sql
-rw-r--r-- 1 root root 519 Feb 23 09:45 wordpress.bak.sql
我们加上时间注释“date +%F”:
[root@centos7 data]#mysql -e 'show databases'|grep -Ev 'Database|information_schema|performance_schema'|sed -r 's/(.)/mysqldump -B \1 |gzip > \/data\/\1_date +%F.bak.gz/' |bash
You have new mail in /var/spool/mail/root
[root@centos7 data]#ll
total 312
-rw-r--r-- 1 root root 526 Feb 23 09:48 db1_2019-02-23.bak.gz
-rw-r--r-- 1 root root 526 Feb 23 09:45 db1.bak.sql
-rw-r--r-- 1 root root 1868 Feb 23 09:48 hellodb_2019-02-23.bak.gz
-rw-r--r-- 1 root root 1868 Feb 23 09:45 hellodb.bak.sql
-rw-r--r-- 1 root root 139780 Feb 23 09:48 mysql_2019-02-23.bak.gz
-rw-r--r-- 1 root root 139780 Feb 23 09:45 mysql.bak.sql
-rw-r--r-- 1 root root 516 Feb 23 09:48 test_2019-02-23.bak.gz
-rw-r--r-- 1 root root 516 Feb 23 09:45 test.bak.sql
-rw-r--r-- 1 root root 519 Feb 23 09:48 wordpress_2019-02-23.bak.gz
-rw-r--r-- 1 root root 519 Feb 23 09:45 wordpress.bak.sql
此时，我们完成数据库的备份！！

分库并压缩实验步骤：

1、for db in mysql -uroot -e'show databases'|grep -Ev 'Database|information_schema|performance_schema';do mysqldump -B $db |gzip > /data/${db}bakdate +%F.sql;done
2、mysql -uroot -e'show databases'|grep -Ev 'Database|information_schema|performance_schema'|sed -r 's/(.)/mysqldump -B \1 |gzip > \/data\/\1.bak.sql/' |bash
3、mysql -uroot -e'show databases'|grep -Ev 'Database|information_schema|performance_schema'|sed -r 's/(.)/mysqldump -B \1 |gzip > \/data\/\1_date +%F.bak.gz/' |bash

实验2、完全备份
准备：在配置文件中设置二进制日志，创建两个新的文件夹存放二进制日志

[root@centos7 ~]#mkdir /data/logbin/
[root@centos7 ~]#mkdir /data/backup
[root@centos7 ~]#ll /data
total 0
drwxr-xr-x 2 root root 6 Feb 23 09:52 backup
drwxr-xr-x 2 root root 6 Feb 23 09:51 logbin
[root@centos7 ~]#chown mysql.mysql /data/logbin
[root@centos7 ~]#
[root@centos7 ~]#vim /etc/my.cnf
[mysqld]
log_bin=/data/logbin/mysql-bin
[root@centos7 ~]#systemctl restart mariadb
[root@centos7 ~]#

1|查看列表：MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 100 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
2、用事务将数据完全备份：mysqldump -A
[root@centos7 ~]#mysqldump -A --single-transaction --master-data=2 |gzip > /data/backup/all.bak.gz
[root@centos7 ~]#ll /data/backup/
total 140
-rw-r--r-- 1 root root 141203 Feb 23 10:47 all.bak.gz
3、我们想看增长情况：
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 638 |
+------------------+-----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> insert teachers(name,age)values('mage',20);
Query OK, 1 row affected (0.01 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 872 |
+------------------+-----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> insert teachers(name,age)values('yuzheng',20);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 1109 | 638 872 1109 一直在涨。
+------------------+-----------+
2 rows in set (0.00 sec)
4、现在清空数据库，实现还原：
a、[root@centos7 ~]#systemctl restart mariadb
[root@centos7 ~]# 先开启服务，构建初始的数据库状态
b、查看系统数据库：
[root@centos7 ~]#ll /data/logbin/
total 1064
-rw-rw---- 1 mysql mysql 264 Feb 23 10:37 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1128 Feb 23 10:55 mysql-bin.000002
-rw-rw---- 1 mysql mysql 30373 Feb 23 10:55 mysql-bin.000003
-rw-rw---- 1 mysql mysql 1038814 Feb 23 10:55 mysql-bin.000004
-rw-rw---- 1 mysql mysql 245 Feb 23 10:55 mysql-bin.000005
-rw-rw---- 1 mysql mysql 150 Feb 23 10:55 mysql-bin.index
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 1128 |
| mysql-bin.000003 | 30373 |
| mysql-bin.000004 | 1038814 |
| mysql-bin.000005 | 245 |
+------------------+-----------+
5 rows in set (0.00 sec)
c、解压数据库：
[root@centos7 ~]#cd /data/backup/
[root@centos7 backup]#ls
all.bak.gz
[root@centos7 backup]#gzip -d all.bak.gz
[root@centos7 backup]#ll
total 512
-rw-r--r-- 1 root root 522178 Feb 23 10:47 all.bak
[root@centos7 backup]#vim all.bak
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=872;
[root@centos7 logbin]#mysqlbinlog --start-position=872 mysql-bin.000002 > inc.sql
[root@centos7 logbin]#ll
total 1080
-rw-r--r-- 1 root root 1911 Feb 23 11:28 inc.sql
-rw-rw---- 1 mysql mysql 264 Feb 23 10:37 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1128 Feb 23 10:55 mysql-bin.000002
-rw-rw---- 1 mysql mysql 30373 Feb 23 10:55 mysql-bin.000003
-rw-rw---- 1 mysql mysql 1038814 Feb 23 10:55 mysql-bin.000004
-rw-rw---- 1 mysql mysql 264 Feb 23 11:24 mysql-bin.000005
-rw-rw---- 1 mysql mysql 264 Feb 23 11:27 mysql-bin.000006
-rw-rw---- 1 mysql mysql 264 Feb 23 11:28 mysql-bin.000007
-rw-rw---- 1 mysql mysql 245 Feb 23 11:28 mysql-bin.000008
-rw-rw---- 1 mysql mysql 240 Feb 23 11:28 mysql-bin.index
[root@centos7 logbin]#vim inc.sql 查看重定向的文件
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
#at 4
#190223 10:37:50 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.60-MariaDB created 190223 10:37:50 at startup
ROLLBACK/!/;
BINLOG '
/rFwXA8BAAAA8QAAAPUAAAAAAAQANS41LjYwLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD+sXBcEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAam7p6Q==
'/!/;
#at 872
#190223 10:51:15 server id 1 end_log_pos 943 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1550890275/!/;
SET @@session.pseudo_thread_id=5/!/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/!/;
d、追加导入文件进inc.sql，增量备份,
[root@centos7 logbin]#mysqlbinlog mysql-bin.000002 >> inc.sql
[root@centos7 logbin]#mysqlbinlog mysql-bin.000003 >> inc.sql
[root@centos7 logbin]#mysqlbinlog mysql-bin.000004 >> inc.sql
You have new mail in /var/spool/mail/root
[root@centos7 logbin]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> set sql_log_bin=off；
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+二进制日志为0，关闭
1 row in set (0.00 sec)
e、恢复数据：
MariaDB [wordpress]> source /data/backup/all.bak 使生效
MariaDB [wordpress]> show databases; 可见数据库已恢复
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hellodb |
| mysql |
| performance_schema |
| test |
| wordpress |
+--------------------+
7 rows in set (0.00 sec)
f、导入数据库：source /data/logbin/inc.sql
MariaDB [mysql]> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 100 | F |
| 11 | mage | 20 | NULL |
| 12 | yuzheng | 20 | NULL |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec) 可见数据已被还原并且是较新状态；
g、数据库恢复后，即可开启二进制日志，让用户开启访问。
MariaDB [mysql]> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.01 sec) 1表示开启。

实验2、完全备份和利用二进制日志实现增量备份，并还原至数据库的故障前的最新状态

1 开启二进制功能
mkdir /data/logbin
chown mysql.mysql /data/logbin

vim /etc/my.cnf
log_bin=/data/logbin/mysql_bin

2 完全全库备份
mysqldump -uroot -A --single-transaction --master-data=2 |gzip > /data/backup/all.·date +%F·bak.gz

3 数据库继续修改 insert

4 数据库故障
rm -rf /var/lib/mysql/*
systemctl stop mariadb

5 恢复
1)解压缩完全备份文件
gzip -d /data/backup/all.·date +%F·bak.gz

2）查看完全备份时二进制文件和位置
cat /data/backup/all.·date +%F·bak

3）导出前面二进制文件和位置以后的二进制内容
mysqlbinlog --start-position=479 mysql-bin.000001 > /data/backup/inc.sql
mysqlbinlog mysql-bin.000002 >> /data/backup/inc.sql
mysqlbinlog mysql-bin.000003 >> /data/backup/inc.sql

4）数据库启动并初始化
systemctl start mariadb

5) 暂停二进制功能
mysql> set sql_log_bin=0;

6) 还原完全备份和增量备份
mysql> source /data/backup/all.·date +%F·bak
mysql> source /data/backup/inc.sql

7) 确认数据是否还原
select

8）开启二进制功能，9并恢复用户访问
mysql> set sql_log_bin=１;

恢复误删除的表

1、刷新日志会增长
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 1128 |
| mysql-bin.000003 | 30373 |
| mysql-bin.000004 | 1038814 |
| mysql-bin.000005 | 264 |
| mysql-bin.000006 | 264 |
| mysql-bin.000007 | 264 |
| mysql-bin.000008 | 245 |
+------------------+-----------+
8 rows in set (0.00 sec)

MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 1128 |
| mysql-bin.000003 | 30373 |
| mysql-bin.000004 | 1038814 |
| mysql-bin.000005 | 264 |
| mysql-bin.000006 | 264 |
| mysql-bin.000007 | 264 |
| mysql-bin.000008 | 288 |
| mysql-bin.000009 | 245 |
+------------------+-----------+
9 rows in set (0.00 sec)
2、做数据库备份：
[root@centos7 ~]#cd /data/backup/
[root@centos7 backup]#ls
all.bak inc.sql
[root@centos7 backup]#rm -rf /data/backup/all.bak
[root@centos7 logbin]#mysqldump -A --single-transaction --master-data=2 > /data/backup/all.bak 将数据库备份出来
3、增加表中数据，并drop表：
MariaDB [hellodb]> insert teachers(name,age)values('a',20);
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> insert teachers(name,age)values('b',70);
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> insert students(name,age)values('p',90);
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | 1 | NULL |
| 26 | k | 70 | F | NULL | NULL |
| 27 | p | 90 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
MariaDB [hellodb]> drop table teachers;
ERROR 1051 (42S02): Unknown table 'teachers'
5、还原教师表：
a、先加读锁，用户暂时不能访问，不能写：
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
b、暂停数据库：[root@centos7 backup]#systemctl stop mariadb
[root@centos7 backup]#
c、先将备份过的数据进行还原：
[root@centos7 logbin]#ll /data/backup/all.bak
-rw-r--r-- 1 root root 522389 Feb 23 12:07 /data/backup/all.bak
d、查看备份文件，从000009开始备份，数值是245，就是最开始的数，我们可以不写，
[root@centos7 logbin]#less /data/backup/all.bak
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=245;
[root@centos7 logbin]#mysqlbinlog mysql-bin.000009 > /data/backup/inc.sql
e、将[root@centos7 logbin]#vim /data/backup/inc.sql
#190223 12:13:28 server id 1 end_log_pos 820 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1550895208/!/;
DROP TABLE teachers / generated by server /将此命令刚才误删除的操作给注释掉即可。
f、[root@centos7 logbin]#ll /data/backup/
total 516
-rw-r--r-- 1 root root 522389 Feb 23 12:07 all.bak 最早时的备份
-rw-r--r-- 1 root root 3536 Feb 23 12:31 inc.sql 刚才的增量备份（二者合一起即可完成还原最新状态）
g、进行还原要清空数据库，
[root@centos7 logbin]#pwd
/data/logbig
[root@centos7 logbin]#cd /var/lib/mysql/
[root@centos7 mysql]#ls
aria_log.00000001 db1 ibdata1 ib_logfile1 performance_schema wordpress
aria_log_control hellodb ib_logfile0 mysql test
[root@centos7 mysql]#rm -rf /var/ib/mysql/
[root@centos7 mysql]#
h、可以新开一台克隆机，例如，192.168.141.150的centos7的克隆机，vim /etc/my.cnf 填入“ log_bin " 开启二进制日志
k、将早前备份的数据及增量备份的数据scp过去：
[root@centos7 backup]#scp 192.168.141.150:/data/
The authenticity of host '192.168.141.150 (192.168.141.150)' can't be established.
ECDSA key fingerprint is SHA256:/B/BdPds6zjUDhs/DzDuqCMSubAcFpmn1k0DfECvpo8.
ECDSA key fingerprint is MD5:80:f2:c3:33:58:83:58:19:df:4c:0d:b0:ea:f8:fa:cc.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.141.150' (ECDSA) to the list of known hosts.
root@192.168.141.150's password:
all.bak 100% 510KB 19.0MB/s 00:00
inc.sql 100% 3536 791.6KB/s 00:00
m、使其生效，select出表的最新状态：
MariaDB [wordpress]> source /data/inc.sql;
MariaDB [wordpress]> source /data/all.bak;
MariaDB [hellodb]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
1 row in set (0.00 sec)

MariaDB [hellodb]> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

实验步骤：恢复误删除的表

1 完全全库备份
mysqldump -uroot -A --single-transaction --master-data=2 |gzip > /data/backup/all.·date +%F·bak.gz
2 数据库继续修改teachers表
insert
3 删除teachers表
4 数据库继续修改students
insert
5 停止服务
systemctl stop mariadb
6 查看完全备份时二进制文件和位置
cat /data/backup/all.·date +%F·bak
7 导出前面二进制文件和位置以后的二进制内容
mysqlbinlog --start-position=479 mysql-bin.000001 > /data/backup/inc.sql
8 修改备份中的误操作的指令
vim /data/backup/inc.sql 删除drop table 指令
9 清空数据
rm -rf /var/lib/mysql/*
10 利用备份还原
mysql> set sql_log_bin=0;
mysql> source /data/backup/all.·date +%F·bak
mysql> source /data/backup/inc.sql
11 检查无误，恢复正常访问
mysql> set sql_log_bin=1;
实验：主从复制
主服务器：192.168.141.200 从服务器：192.168.141.150
1、[root@centos7 ~]#vim /etc/my.cnf
[mysqld]
log_bin
server_id=1 systemctl restart mariadb
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
1 row in set (0.00 sec) 这是主服务器当前的文件位置，文件名称
MariaDB [(none)]> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)

2、创建用户：
MariaDB [(none)]> grant replication slave on . to repluser@'192.168.141.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 401 |
+--------------------+-----------+
1 row in set (0.00 sec)
3、导入新数据，日志就又变更了：
[root@centos7 ~]#ls
anaconda-ks.cfg hellodb_innodb.sql inc.sql initial-setup-ks.cfg percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
You have new mail in /var/spool/mail/root
[root@centos7 ~]#mysql < hellodb_innodb.sql
[root@centos7 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.60-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master logs;
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 7811 |
+--------------------+-----------+
1 row in set (0.00 sec)
（解析：现在若从245开始复制，就是创建用户，和hellodb数据库都会复制过去，若从7811开始复制，hellodb数据库将不会被复制）
此时我们进入192.168.141.150slave主机：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> select * from user,host from mysql.user;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> select user,host from mysql.user;
+------+---------------------+
| user | host |
+------+---------------------+
| root | 127.0.0.1 |
| root | ::1 |
| | centos7.localdomain |
| root | centos7.localdomain |
| | localhost |
| root | localhost |
+------+---------------------+
6 rows in set (0.00 sec) 可见既无账号又无数据库
此时，我们开始进行复制：
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.141.200', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec) “ok”已经定义了要复制的信息了
4、进入数据库的存放目录[root@centos7 ~]#cd /var/lib/mysql/
[root@centos7 mysql]#ll
total 29780
-rw-rw---- 1 mysql mysql 16384 Feb 24 10:18 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Feb 24 10:18 aria_log_control
-rw-rw---- 1 mysql mysql 30373 Feb 23 20:49 centos7-bin.000001
-rw-rw---- 1 mysql mysql 1038814 Feb 23 20:49 centos7-bin.000002
-rw-rw---- 1 mysql mysql 42 Feb 23 20:49 centos7-bin.index
-rw-rw---- 1 mysql mysql 18874368 Feb 24 10:18 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Feb 24 10:18 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Feb 23 20:49 ib_logfile1
-rw-rw---- 1 mysql mysql 264 Feb 24 10:18 mariadb-bin.000001
-rw-rw---- 1 mysql mysql 245 Feb 24 10:18 mariadb-bin.000002
-rw-rw---- 1 mysql mysql 42 Feb 24 10:18 mariadb-bin.index
-rw-rw---- 1 mysql mysql 245 Feb 24 10:23 mariadb-relay-bin.000001
-rw-rw---- 1 mysql mysql 27 Feb 24 10:23 mariadb-relay-bin.index
-rw-rw---- 1 mysql mysql 88 Feb 24 10:23 master.info
drwx------ 2 mysql mysql 4096 Feb 23 20:49 mysql
srwxrwxrwx 1 mysql mysql 0 Feb 24 10:18 mysql.sock
drwx------ 2 mysql mysql 4096 Feb 23 20:49 performance_schema
-rw-rw---- 1 mysql mysql 53 Feb 24 10:23 relay-log.info
drwx------ 2 mysql mysql 6 Feb 23 20:49 test
[root@centos7 mysql]#cat master.info
18
|mariadb-bin.000001
245
192.168.141.200
repluser
centos
3306
60
0
0
1800.000
0
此时查看从服务器的数据，没有任何变化，因为还没开启复制：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> select user,host from mysql.user;
+------+---------------------+
| user | host |
+------+---------------------+
| root | 127.0.0.1 |
| root | ::1 |
| | centos7.localdomain |
| root | centos7.localdomain |
| | localhost |
| root | localhost |
+------+---------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> show slave status\G
1. row
Slave_IO_State:
Master_Host: 192.168.141.200
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: |mariadb-bin.000001
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: |mariadb-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 245
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.01 sec) 非常详细的线程信息
5、MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.141.200
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 7811
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 8097
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 7811
Relay_Log_Space: 8393
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec) 非常详尽的信息已经连上主机master。
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec) “hellodb”已被复制过来

Database changed
MariaDB [hellodb]> select from teachers; 查看teachers表的记录：
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
此时我们去200主机上插入teaches表记录：
MariaDB [hellodb]> insert teachers (name,age)values('gxy',20);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | gxy | 20 | NULL |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
马上去150slave主机查看：
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | gxy | 20 | NULL |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec) 可以看到，第5条记录被同步更新。

假设此时，主服务器正常运行，从服务器突然停电了：slave重启后，是否还能时刻同步？
此刻主服务器还在照常工作：
MariaDB [hellodb]> insert teachers (name,age)values('xingya',10);
Query OK, 1 row affected (0.01 sec)
MariaDB [hellodb]> insert teachers (name,age)values('maodun',80);
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | gxy | 20 | NULL |
| 6 | xingya | 10 | NULL |
| 7 | maodun | 80 | NULL |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)
重启150slave主机并查看状态：
[root@centos7 ~]#systemctl start mariadb
MariaDB [(none)]> show slave status\G
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.141.200
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 8519
Relay_Log_File: mariadb-relay-bin.000004
Relay_Log_Pos: 1005
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 8519
Relay_Log_Space: 1301
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec) 可以看到slave是从8519开始复制的.
去200主机查看master logs：
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 8519 |
+--------------------+-----------+
1 row in set (0.00 sec) 也是8519，说明只要slave连上主服务器，一旦down机不影响重启继续复制主服务器的二进制日志。
MariaDB [hellodb]> select from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | gxy | 20 | NULL |
| 6 | xingya | 10 | NULL |
| 7 | maodun | 80 | NULL |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec) 此时的表记录也同步更新。说明就算重启开机， Slave_IO_Running: Yes Slave_SQL_Running: Yes 也会自动启动，只要服务器启动起来，线程就会自动起来。
新问题：假设150是新机，啥都没，200是老机，要拿150当从，200当主，我们应当在200上备份，还原到150，备份后的数据在做复制：
在200上做备份，拷贝到150上：
[root@centos7 ~]#mysqldump -A --single-transaction --master-data=1 > all.bak.sql
[root@centos7 ~]#scp all.bak.sql 192.168.141.150:
The authenticity of host '192.168.141.150 (192.168.141.150)' can't be established.
ECDSA key fingerprint is SHA256:/B/BdPds6zjUDhs/DzDuqCMSubAcFpmn1k0DfECvpo8.
ECDSA key fingerprint is MD5:80:f2:c3:33:58:83:58:19:df:4c:0d:b0:ea:f8:fa:cc.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.141.150' (ECDSA) to the list of known hosts.
root@192.168.141.150's password:
all.bak.sql 100% 511KB 10.1MB/s 00:00
此时，在150主机上：
[root@centos7 ~]#vim /etc/my.cnf
log_bin server_id=2
[root@centos7 ~]#systemctl restart mariadb
[root@centos7 ~]#mysql < all.bak.sql
MariaDB [hellodb]> select from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | gxy | 20 | NULL |
| 6 | xingya | 10 | NULL |
| 7 | maodun | 80 | NULL |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec) 此时主从复制还没实现
来到200主机上插入2条数据：
MariaDB [hellodb]> insert teachers (name,age)values('huge',40);
Query OK, 1 row affected (0.01 sec)
MariaDB [hellodb]> insert teachers (name,age)values('qianxi',19);
Query OK, 1 row affected (0.01 sec)
150主机上还是无更新：
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | gxy | 20 | NULL |
| 6 | xingya | 10 | NULL |
| 7 | maodun | 80 | NULL |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)
我们去做主从同步：
MariaDB [hellodb]> CHANGE MASTER TO MASTER_HOST='192.168.141.200', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=8519; 将changemasterto 的数据拷贝粘贴，可在“cat all.bak.sql”中查看8519等数据。
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> show slave status\G
1. row
Slave_IO_State:
Master_Host: 192.168.141.200
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 8519
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 8519
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec) 此时slave状态已经进入正轨，去开启线程即可。
开启线程，可见2条线程均为“yes”：
MariaDB [hellodb]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> show slave status\G
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.141.200
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 8991
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 1003
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 8991
Relay_Log_Space: 1299
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
此时，8519后的数据已被复制过来，如下：
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | gxy | 20 | NULL |
| 6 | xingya | 10 | NULL |
| 7 | maodun | 80 | NULL |
| 8 | huge | 40 | NULL |
| 9 | qianxi | 19 | NULL |
+-----+---------------+-----+--------+
9 rows in set (0.00 sec)
至此，主从复制已完毕！