大家好，又见面了，我是全栈君。

MySQL 系列（四）主从复制、备份恢复方案生产环境实战

去年公司有一个七年PHP开发经验的工程师，想要跳槽。

去国内某知名互联网公司面试后，被虐惨了，非要我给他讲讲什么是主从复制。

那好吧，读这篇文章的人好帅！

元芳，你怎么看？

本章内容：

主从复制
简介原理
备份主库及恢复从库，配置从库生效
读写分离
如果主宕机了，怎么办？
双主的情况
MySQL 备份及恢复方案
备份单个及多个数据库
mysqldump 的常用参数
如何增量恢复呢？
增量恢复的必要条件
生产环境 mysqldump 备份命令
恢复喽

一、MySQL主从复制

1、简介

我们为什么要用主从复制？

主从复制目的：

可以做数据库的实时备份，保证数据的完整性；

可做读写分离，主服务器只管写，从服务器只管读，这样可以提升整体性能。

原理图：

MySQL 系列（四）主从复制、备份恢复方案生产环境实战[通俗易懂]

从上图可以看出，同步是靠log文件同步读写完成的。

2、更改配置文件

两天机器都操作，确保 server-id 要不同，通常主ID要小于从ID。一定注意。

# 3306和3307分别代表2台机器

# 打开log-bin,并使server-id不一样

#vim /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
server-id = 1
#vim /data/3307/my.cnf
log-bin = /data/3307/mysql-bin
server-id = 3

#检查
1、
[root@bogon ~]# egrep "log-bin|server-id" /data/3306/my.cnf 
log-bin = /data/3306/mysql-bin
server-id = 1
[root@bogon ~]# egrep "log-bin|server-id" /data/3307/my.cnf  
log-bin = /data/3307/mysql-bin
server-id = 3
2、
[root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock -e "show variables like 'log_bin';"
Enter password: 
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| log_bin       | ON  |    # ON 为开始开启成功
+-----------------------+--------+

3、建立用于从库复制的账号rep

通常会创建一个用于主从复制的专用账户，不要忘记授权。

# 主库授权，允许从库来连接我取日志
[root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock 
Enter password:

# 允许从库192.168.200网段连接，账号rep，密码nick。
mysql> grant replication slave on *.* to 'rep'@'192.168.200.%' identified by 'nick';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 检查创建的rep账号：
mysql> select user,host from mysql.user;
+--------+-----------------------------+
| user | host              |
+--------+------------------------------+
| root | 127.0.0.1          |
| rep  | 192.168.200.%     |
| root | localhost           |
| root | localhost.localdomain |
+--------+------------------------------+
7    rows in set (0.00 sec)

4、备份主库，及恢复到从库

把主库现有数据备份下来，再恢复到从库，此时两个主机的数据一致。

如果事先有数据的话，这不不能忘。

1)    在主库上加锁，使只有只读权限。
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
#5.1、5.5锁表命令略有不同。
# 5.1锁表：flush tables with read lock;
# 5.5锁表：flush table with read lock;

2)    记住就是这个点备份的。
mysql> show master status;
+---------------------------+-------------+-------------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------------+------------+--------------------+-------------------------+
| mysql-bin.000013  |   410 |             |               |
+----------------------------+------------+--------------------+-------------------------+
1 row in set (0.00 sec)

3)    克隆窗口，备份数据。
[root@bogon ~]# mysqldump -uroot -p -S /data/3306/mysql.sock -A -B --events --master-data=2|gzip >/opt/rep.sql.gz
Enter password:
参数：    -A：备份所有的
#看rep.sql.gz参数
vim /opt/rep.sql.gz
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=410;

4)    查看master status；数值是否正常
mysql> show master status;
+--------------------------+------------+--------------------+-------------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------------+------------+--------------------+--------------------------+
| mysql-bin.000013 |    410 |            |                |
+--------------------------+------------+--------------------+--------------------------+
1 row in set (0.00 sec)

5)    解锁库
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

6)    恢复到从库
[root@bogon ~]# gunzip < /opt/rep.sql.gz | mysql -uroot -p -S /data/3307/mysql.sock
Enter password:

5、配置从库及生效

更改从库和主库的连接参数，配置生效。检查就成功了！

1)    进入从库。
[root@bogon ~]# mysql -uroot -p -S /data/3307/mysql.sock 
Enter password:

2)    更改从属服务器用于与主服务器进行连接和通讯的参数。
mysql> CHANGE MASTER TO
      MASTER_HOST='192.168.200.98',
      MASTER_PORT=3306,
      MASTER_USER='rep',
      MASTER_PASSWORD='nick',
      MASTER_LOG_FILE='mysql-bin.000013',
      MASTER_LOG_POS=410;
Query OK, 0 rows affected (0.01 sec)

3)    查看更改的参数。
[root@localhost ~]# cd /data/3307/data/
[root@localhost data]# cat master.info 
18
mysql-bin.000013
410
192.168.200.98
REP
nick
3306
60
0


0
1800.000

0

4)    生效！
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

5)    检查下列参数，符合则正常！
mysql> show slave status\G
Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes        #取logo。
            Slave_SQL_Running: Yes        #读relay-bin、logo,写数据。
Seconds_Behind_Master: 0        #落后主库的秒数。

6)    查看relay-bin.logo。
[root@localhost 3307]# cd /data/3307
[root@localhost 3307]# ll
总用量 48
drwxr-xr-x. 9 mysql mysql  4096 10月 29 18:52 data
-rw-r--r--. 1 mysql mysql  1900 10月 29 11:45 my.cnf
-rwx------. 1 root  root   1307 10月 20 17:06 mysql
-rw-rw----. 1 mysql mysql     6 10月 29 11:00 mysqld.pid
-rw-r-----. 1 mysql mysql 15090 10月 29 18:49 mysql_nick3307.err
srwxrwxrwx. 1 mysql mysql     0 10月 29 11:00 mysql.sock
-rw-rw----. 1 mysql mysql   150 10月 29 18:49 relay-bin.000001
-rw-rw----. 1 mysql mysql   340 10月 29 18:52 relay-bin.000002
-rw-rw----. 1 mysql mysql    56 10月 29 18:49 relay-bin.index
-rw-rw----. 1 mysql mysql    53 10月 29 18:52 relay-log.info

7)    查看relay-log.info。
[root@localhost 3307]# cat relay-log.info 
/data/3307/relay-bin.000002
340
mysql-bin.000013
497

8)    查看master.info。
[root@localhost 3307]# cat data/master.info 
18
mysql-bin.000013
497
192.168.200.98
rep
nick
3306
60
0


0
1800.000

0

6、读写分离

读写分离在生产环境比比皆是，也是必备技能。

忽略MySQL主从复制授权表同步，读写分离

[root@bogon 3306]# vim my.cnf
#添加以下四行
replicate-ignore-db = mysql
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

server-id = 1

1)    通过read-only参数防止数据写入从库的方法。
#修改配置文件。
vim /data/3307/my.cnf
[mysqld]
read-only
#对用户授权事不能指定有super或all privileges权限。不然没效果。
#创建账户suoning，并刷新权限。
mysql> grant select,insert,update,delete on *.* to 'suoning'@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#用创建账户登录，并创建库
[root@bogon 3307]# mysql -usuoning -p123 -S /data/3307/mysql.sock
mysql> create user kangkangkang@'192.%' identified by 'old123';
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

2)    主从同步故障
A.    Last_SQL_Errno: 1007
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
B.    忽略
skip-name-resolve        #忽略名字解析
slave-skip-errors = 1032,1062,1007    #忽略故障编号

server-id = 3

3)    从库开启bin-log
vim my.cnf
log-bin = /data/3307/mysql-bin
log-slave-updates           #表示从库记录bin-log
expire_logs_days = 7        #保留7天bin-log。

7、主宕机

我们来模拟一下，如果主机宕机了，那我们该如何让从快速替换，让损失降到最小？当然了，双机热备也是一个不错的选择，那下节会给大家讲解。

一主多从的环境下，如果主宕机了，选一台从做主，继续和其它从同步。

A.    查看每个从库的master.info，看谁的更靠前，最新，更大，丢的数据最少。
[root@localhost 3307]# cat /data/3307/data/master.info 
mysql-bin.000015
326

B.    确保所有relay log全部更新完毕。
在每个从库上执行stop slave io_thread;show processlist;
知道看到Has read all relay log;表示从库更新都执行完毕。

C.    登陆mysql -uroot -p -S /data/3307/mysql.sock
stop slave;
reset master;
quit

D.    进入到数据库目录，删除master.info relay-log.info
cd /data/3307/data/
rm -f master.info relay-log.info

E.    3307提升为主库
vim /data/3307/my.cnf
开启log-bin = /data/3307/mysql-bin
如存在log-slave-updates，read-only等一定要注释。
/data/3307/mysql restart

F.    其它从库操作
stop slave;
change master to master_host ='192.168.200.98';
start slave;
show slave status\G

8、双主

使用主主前提：表的主键自增。

双主情况下，ID会是这样情况，通过程序指定ID写库M1：1,3,5，M2：2,4,6。

[root@localhost 3307]# vim my.cnf
[mysqld]
auto_increment_increment        = 2
auto_increment_offset           = 2
[root@localhost 3307]# ./mysql restart

[root@localhost 3306]# vim my.cnf
[mysqld]
auto_increment_increment        = 2
auto_increment_offset           = 1
log-bin = /data/3306/mysql-bin
log-slave-updates
[root@localhost 3306]# ./mysql restart

[root@localhost 3306]# mysqldump -uroot -pnick -S /data/3307/mysql.sock -A -B --master-data=1 -x --events > /opt/3307bak.sql
[root@localhost 3306]# mysql -uroot -pnick -S /data/3306/mysql.sock < /opt/3307bak.sql

mysql> CHANGE MASTER TO
      MASTER_HOST='192.168.200.98',
      MASTER_PORT=3307,
      MASTER_USER='rep',
      MASTER_PASSWORD='nick';
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G

二、MySQL备份及恢复

1、备份单个数据库

最基础的备份单个数据库

1>    语法：mysqldump –u 用户名 –p 数据库名> 备份的数据库名

2>    备份nick_defailt数据库，查看内容。
[root@localhost ~]# mysqldump -uroot -p -B nick_defailt >/opt/mysql_nick_defailt.bak
Enter password: 
[root@localhost ~]# egrep -v "#|\*|--|^$" /opt/mysql_nick_defailt.bak 
DROP TABLE IF EXISTS `oldsuo`;
CREATE TABLE `oldsuo` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LOCK TABLES `oldsuo` WRITE;
INSERT INTO `oldsuo` VALUES (2,'ç´¢å®',0,NULL),(3,'ç´¢å°¼',0,NULL),(4,'åº•åº•',0,NULL);
UNLOCK TABLES;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `qq` varchar(15) DEFAULT NULL,
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `suo` int(4) DEFAULT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL,
  `sex` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (NULL,2,'oldsuo',NULL,0,NULL,NULL),(NULL,3,'kangknag',NULL,0,NULL,NULL),(NULL,4,'kangkang',NULL,0,NULL,NULL),(NULL,5,'oldsuo',NULL,0,NULL,NULL),(NULL,6,'kangknag',NULL,0,NULL,NULL),(NULL,7,'kangkang',NULL,0,NULL,NULL);
UNLOCK TABLES;

3>    启用压缩备份数据库
[root@localhost~]#mysqldump -uroot -p -B nick_defailt|gzip>/opt/mysql_nick_defailt.bak.gz
Enter password: 
[root@localhost ~]# ll /opt/
总用量 28
-rw-r--r--. 1 root root 2109 10月 24 16:36 data_bak.sq2
-rw-r--r--. 1 root root 2109 10月 24 16:36 data_bak.sql
-rw-r--r--. 1 root root 1002 10月 27 11:55 mysql_nick_defailt.bak
-rw-r--r--. 1 root root 1002 10月 27 11:56 mysql_nick_defailt.bak.gz
-rw-r--r--. 1 root root 3201 10月 27 11:46 mysql_nick_defailt_B.bak
drwxr-xr-x. 2 root root 4096 11月 22 2013 rh
-rw-r--r--. 1 root root 1396 10月 24 16:11 student_bak.sql

4>    恢复nick_defailt数据库
[root@localhost ~]# mysql -uroot -p nick_defailt </opt/mysql_nick_defailt.bak 
Enter password:
#加-B恢复方法
[root@localhost ~]#  mysql -uroot -p  </opt/mysql_nick_defailt_B.bak               
Enter password:

5>    总结
1、    备份用-B参数。增加use db,和create database的信息。
2、    用gzip对备份的数据压缩。

2、备份多个数据库

备份多个数据库的情况呢？

#多个数据库名中间加空格
[root@localhost ~]# mysqldump -uroot -p -B nick_defailt oldsuo oldsuo_1|gzip>/opt/mul.sql.gz
Enter password:

3、备份单个及多个表

那如果备份单个和多个表，怎么办？

1>    语法：mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名
[root@localhost ~]# mysqldump -uroot -p nick_defailt student >/opt/mysql_nick_defailt_student.bak
Enter password:

2>    语法：mysqldump -u 用户名 -p 数据库名 表名1  表名2  > 备份的文件名
[root@localhost ~]# mysqldump -uroot -p  nick_defailt student oldsuo >/opt/mysql_nick_defailt.bak        
Enter password:

4、mysqldump 的参数

mysqldump 的关键参数

1、    -B指定多个库，增加建库语句和use语句。
2、    --compact去掉注释，适合调试输出，生产不用。
3、    -A 备份所有库。
4、    -F刷新binlog日志。
5、    --master-data 增加binlog日志文件名及对应的位置点。
6、    -x，--lock-all-tables 
7、    -l,--locktables
8、    -d 只备份表结构
9、    -t 只备份数据
10、   --single-transaction 适合innodb事务数据库备份。

5、增量恢复

重要的来了，生产环境一般是增量备份与恢复；

所谓增量，就是在原数据的基础上继续添加数据，不必每次都重新添加，省时省力。

A：增量恢复必备条件：

1.    开启MySQL数据库log-bin参数记录binlog日志。
[root@localhost 3306]# grep log-bin /data/3306/my.cnf 
log-bin = /data/3306/mysql-bin
2.    存在数据库全备。

B：生产环境 mysqldump备份命令：

# 进行数据库全备，（生产环境还通过定时任务每日凌晨执行）
mysqldump -uroot -pnick -S /data/3306/mysql.sock --default-character-set=gbk --single-transaction -F -B nick |gzip >/server/backup/mysql_$(date +%F).sql.gz
# innodb引擎备份
mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F --single-transaction -A -B |gzip >$DATA_FILE
# myisam引擎备份
mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F -A -B --lock-all-tables |gzip >$DATA_FILE

C：恢复：

# 通过防火墙禁止web等应用向主库写数据或者锁表。让主库暂时停止更新，然后再进行恢复。
# 误操作删除nick库！

1.    检查全备及binlog日志
[root@localhost 3306]# cd /server/backup/
[root@localhost backup]# gzip -d mysql_2015-10-31.sql.gz 
[root@localhost backup]# vim mysql_2015-10-31.sql
[root@localhost backup]# grep -i "change" mysql_2015-10-31.sql

2.    立即刷新并备份出binlog
[root@localhost 3306]# mysqladmin -uroot -pnick -S /data/3306/mysql.sock flush-logs
[root@localhost 3306]# cp /data/3306/mysql-bin.000030 /server/backup/
#误操作log-bin,倒数第二
[root@localhost backup]# mysqlbinlog -d nick mysql-bin.000030 >bin.sql    #导出为.sql格式。
[root@localhost backup]# vim bin.sql
找到语句drop database nick删除！！！（误操作语句）

3.    恢复
[root@localhost backup]# mysql -uroot -pnick -S /data/3306/mysql.sock <mysql_2015-10-31.sql        #恢复之前的数据库全备
[root@localhost backup]# mysql -uroot -pnick -S /data/3306/mysql.sock nick < bin.sql
#恢复删除误操作语言的bin-log。

# 搞定！！！

来自：http://www.cnblogs.com/suoning/p/5778243.html

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/108713.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权正版激活】： 官方授权正版激活支持Jetbrains家族下所有IDE 使用个人JB账号...