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

原创

清欢难寻 2019-02-23 20:40:38 ©著作权

©著作权归作者所有：来自51CTO博客作者清欢难寻的原创作品，请联系作者获取转载授权，否则将追究法律责任

实验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

暂停二进制功能 mysql> set sql_log_bin=0;
还原完全备份和增量备份 mysql> source /data/backup/all.·date +%F·bak mysql> source /data/backup/inc.sql
确认数据是否还原 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 00 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) 至此，主从复制已完毕！