«

详解MySQL的主从复制、读写分离、备份恢复

时间：2024-3-2 00:15     作者：韩俊     分类： Mysql

一、MySQL主从复制

1、简介

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

主从复制目的：

可以做数据库的实时备份，保证数据的完整性；
可做读写分离，主服务器只管写，从服务器只管读，这样可以提升整体性能。

原理图：

从上图可以看出，同步是靠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 statusG
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 statusG

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 的关键参数

-B指定多个库，增加建库语句和use语句。
--compact去掉注释，适合调试输出，生产不用。
-A 备份所有库。
-F刷新binlog日志。
--master-data 增加binlog日志文件名及对应的位置点。
-x，--lock-all-tables
-l,--locktables
-d 只备份表结构
-t 只备份数据
--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。
# 搞定！！！

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持毛票票！

标签: mysql

热门推荐

1 MySQL部署时提示Table mysql.plugin doesn’t exist的解决方法
2 MySQL下常见的启动失败与备份失败问题的解决教程
3 ThinkPHP连接Oracle数据库
4 PHP实现的Redis多库选择功能单例类
5 windows7下使用MongoDB实现仓储设计
6 SQLserver存储与设置定时执行存储的方法是什么
7 使用mybatisPlus生成oracle自增序列遇到的坑如何解决
8 Mysql IO 内存方面的优化
9 Mysql逗号拼接字符串的关联查询及统计问题怎么解决
10 超出MySQL最大连接数的问题怎么解决

日历

标签

hbase maven eclipse sybase PostgreSQL spring emcache oracle mongodb redis nginx golang java vue android ios mysql linux 微信小程序 css html php教程 javascript python php

搜索

最新文章

热门文章