Mysql总结4-binlog备份恢复及集群
来源：互联网 发布：安卓系拦截广告软件 编辑：程序博客网 时间：2024/05/22 14:57
1.增量备份
1.1 概念
1.1.1 增量备份
增量备份 mysql数据库会以二进制的形式,把用户对mysql数据库的操作,记录到文件.当用户希望恢复的时候可以使用备份文件,进行恢复.
增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。
这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。
增量备份优点 没有重复的备份数据，因此备份的数据量不大，备份所需的时间很短。
增量备份缺点: 增量备份的数据恢复是比较麻烦的。您必须具有上一次全备份和所有增量备份磁带（一旦丢失或损坏其中的一个增量，就会造成恢复的失败），并且它们必须沿着从全备份到依次增量备份的时间顺序逐个反推恢复，因此这就极大地延长了恢复时间。
1.1.2 binlog日志
1.1.2.1 binlog日志简介
由配置文件log-bin负责启用，Mysql将在数据根目录创建两个新文件XXX-bin.001和xxx-bin.index，若配置选项没有给出文件名，Mysql将使用主机名称命名这两个文件，其中.index文件包含一份全体日志文件的清单。
1.1.2.2 binlog日志生成文件规则
- 当MySQL数据库停止或重启时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的binlog日志文件，文件序号递增.
- 如果日志文件超过max_binlog_size系统变量配置的上限时，也会生成新的日志文件。
- flush log 通过该命令主动刷新
1.1.2.3 记录内
记录的操作有创建数据库,删除数据库,创建表, dml(Insert、delete、update)语句,不记录select的语句,
包括记录的语句内容,操作时间和position.
Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件，而不会记录SELECT和没有实际更新的UPDATE语句.
1.1.2.4 配置修改
在my.cnf或my.ini中增加
[mysqld]//log-bin若不显示指定存储目录，则默认存储在mysql的data目录下log-bin=mysql-bin// binlog_format的几种格式：（STATEMENT，ROW和MIXED）binlog_format=row1.1.2.5 配置说明
log-bin若不显示指定存储目录，则默认存储在mysql的data目录下
binlog_format的几种格式：（STATEMENT，ROW和MIXED）
- STATEMENT：基于SQL语句的复制(statement-based replication, SBR)
- ROW：基于行的复制(row-based replication, RBR)
- MIXED：混合模式复制(mixed-based replication, MBR)
1.1.2.6产生文件示例
启动后会产生mysql-bin.*这样的文件，每启动一次，就会增加一个或者多个。
[root@localhost data]# ls -l| grep mysql-bin-rw-rw----. 1 mysql mysql 107 Jul 5 11:19 mysql-bin.000001-rw-rw----. 1 mysql mysql 19 Jul 5 11:19 mysql-bin.index[root@localhost data]# mysql-bin.index 为索引文件,有哪些增量备份文件
mysqlmysql-bin.000001 存放用户对数据库操作的文件.
1.1.2.7 查看配置情况
mysql> show variables like 'log_bin%';+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| log_bin | ON || log_bin_trust_function_creators | OFF |+---------------------------------+-------+mysql> show variables like 'binlog%';+-----------------------------------------+-------+| Variable_name | Value |+-----------------------------------------+-------+| binlog_cache_size | 32768 || binlog_direct_non_transactional_updates | OFF || binlog_format | ROW || binlog_stmt_cache_size | 32768 |+-----------------------------------------+-------+//此参数是设置日志的过期天数,过期的日志将会自动删除,这样有利于我们管理日志的工作量.在my.cnf或my.ini中修改.//本配置表示,3天之后过期的日志将会别删除.EXPIRE_LOGS_DAYS=31.1.2.8 查看binlog日志内容
//linux[root@localhost data]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 ;mysqlbinlog: unknown variable 'default-character-set=utf8'//windowscmd > C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqlbinlog.exe c:\binlog\mylog.000001这里我们碰到了mysqlbinlog的一个bug，解决方法有两个：
方法一：使用–no-defaults选项
[root@localhost data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001 方法二：将my.cnf中[client]选项组中default-character-set=utf8选项临时屏蔽掉（该选项即时生效，不用重启数据库）,使用完mysqlbinlog命令时再恢复。因为使用mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件my.cnf(windows下是my.ini),而不是服务器已经加载进内存的配置文件。
查看输出格式如下：
# at 188#140705 11:23:55 server id 1 end_log_pos 271 Query thread_id=1 exec_time=0 error_code=0SET TIMESTAMP=1404573835/*!*/;create database test/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;可以筛选日志的结果或者将结果导出到文件.
[root@localhost /]# mysqlbinlog binlog_files | more [root@localhost /]# mysqlbinlog binlog_files > tmpfile 1.1.2.9 binlog日志内容解读
MySQL对记录binlog的操作都会记录操作的时间,并且分配了一个位置(position).
- Position:位于文件中的位置,即第一行的(#at 188)和第二行的(end_log_pos 271),说明该事件记录从文件第4字节开始.
- Timestamp:事件发生的时间戳，即第二行的（#140705 11:23:55）
- Exec_time:事件的执行花费时间
- Error_code:错误码
- Type 事件类型：
- Master ID:创建二进制事件的主机服务器ID
- Master Pos:事件在原始二进制文件中的位置
Flags:标志信息
一些常用操作
mysql> show master logs; #查看数据库所有日志文件。mysql> show binlog events \g; #查看当前使用的binlog文件信息。mysql> show binlog events in 'mysql-bin.000016'; #查看指定的binlog文件信息。mysql> flush logs; #将内存中log日志写磁盘，保存在当前binlog文件中，并产生一个新的binlog日志文件。mysql> flush logs; reset master; #删除所有二进制日志，并重新（mysql-bin.000001）开始记录。
1.2 案例操作1
本案例主要模拟操作包括三种操作类型:
1)通过sql恢复备份的全量数据,
2)通过sql恢复备份的增量备份1,
3)通过binlog恢复备份的增量备份2.
1.2.1 查看当前使用bin配置
mysql> show master logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 107 |+------------------+-----------+[root@localhost data]# ll -htotal 5.1G-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ibdata1-rw-rw---- 1 mysql mysql 2.0G Jul 18 14:12 ibdata2-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile0-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile1drwxr-xr-x 2 mysql mysql 4.0K Jul 18 13:52 mysql-rw-rw---- 1 mysql mysql 107 Jul 23 13:29 mysql-bin.000001-rw-rw---- 1 mysql mysql 19 Jul 23 13:29 mysql-bin.indexsrwxrwxrwx 1 mysql mysql 0 Jul 18 14:14 mysql.sockdrwx------ 2 mysql mysql 4.0K Jul 18 14:01 performance_schema-rw-rw---- 1 mysql mysql 483 Jul 23 13:29 R820-08.err-rw-rw---- 1 mysql mysql 5 Jul 18 14:14 R820-08.piddrwx------ 2 mysql mysql 19 Jul 22 23:15 test当前binlog配置为: mysql-bin.000001
1.2.2 模拟全量数据
1.2.2.1 准备全量数据
//1.准备全量数据mysql> create database backup_full;mysql> create table full (c1 int(10), c2 varchar(20)) engine=innodb;mysql> insert into full values (1, 'full1'),(2, 'full2'),(3, 'full3'),(4, 'full4'),(5, 'full5'),(6, 'full6'),(7, 'full7'),(8, 'full8'),(9, 'full9'),(10, 'full10');//2.备份前需要将数据库加读锁，防止数据在备份时其它链接写入mysql> flush tables with read lock;//3.通过命令flush logs;将log日志刷盘，写入当前binlog（mysql-bin.000001），在生成一个新的binlog（mysql-bin.000002）为增备做准备。[原有数据和本次操作的日志会在mysql-bin.000001]mysql> flush logs;//4.进行数据备份。在linux命令行下执行(备份的为sql格式的数据)mysqldump -u用户名 -p密码 -hIP地址 -P端口 数据库名 > /tmp/backup_full.sql mysqldump -uroot -pjesse -h127.0.0.1 -P3355 buckup_full > /tmp/backup_full.sql //5.解除表锁。mysql> unlock tables; 至此全量备份全部结束，将全量数据文件buckup_full.sql保存即可。
数据库再有新的数据更新会记录在新的binlog（mysql-bin.000002）里面。
1.2.2.2 第一份增量数据(导出sql,后通过sql恢复)
//1.准备本次增量操作数据mysql> create database backup_increment; mysql> use backup_increment; mysql> create table increment (c1 int(10), c2 varchar(20)) engine=innodb; mysql> insert into increment values (11, 'increment1'),(12, 'increment2'),(13, 'increment3'),(14, 'increment4'),(15, 'increment5'); //2.备份前需要将数据库加读锁，防止数据在备份时写入。mysql> flush tables with read lock; //3.通过命令flush logs;将log日志刷盘，写入当前binlog（mysql-bin.000002），在生成一个新的binlog（mysql-bin.000003）为下次增备做准备。[本次增量的操作日志会在mysql-bin.000002]mysql> flush logs; //4. 1)将binlog第一个增备文件mysql-bin.000002直接复制保存即可。// 2)也可以将二进制文件导出到文本文件保存，在linux命令行下执行mysqlbinlog mysql-bin.000002 > /tmp/increment1.txt //5. 解除表锁。mysql> unlock tables; 至此第一个增量备份全部结束，将增量binlog文件mysql-bin.000002或者有binlog文件导出的文本文件/tmp/increment1.txt保存即可。
数据库再有新的数据更新会记录在新的binlog（mysql-bin.000003）里面
1.2.2.3 第二增量数据(复制备份binlog文件,通过binlog文件恢复)
//1.准备本次增量操作数据mysql> use backup_increment; mysql> insert into increment values (16, 'increment16'),(17, 'increment17'),(18, 'increment18'),(19, 'increment19'),(20, 'increment20'); //2.备份前需要将数据库加读锁，防止数据在备份时写入。mysql> flush tables with read lock; //3.通过命令flush logs;将log日志刷盘，写入当前binlog（mysql-bin.000003），在生成一个新的binlog（mysql-bin.000004）为下次增备做准备。[本次增量的操作日志会在mysql-bin.000003]mysql> flush logs; //4. 1)将binlog第一个增备文件mysql-bin.000003直接复制保存即可。// 2)也可以将二进制文件导出到文本文件保存，在linux命令行下执行mysqlbinlog mysql-bin.000003 > /tmp/increment2.txt //5. 解除表锁。mysql> unlock tables; 1.2.3 操作还原
1.2.3.1 模拟数据库故障
即删除全备数据及增备数据库。
//删除相关的表和数据库mysql> drop table backup_full.full; mysql> drop database backup_increment; //查看删除情况mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | backup_full | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> select * from backup_full.full; ERROR 1146 (42S02): Table 'backup_full.full' doesn't exist mysql> select * from backup_increment.increment; ERROR 1146 (42S02): Table 'backup_increment.increment' doesn't exist //此时数据库数据被清空1.2.3.2 还原全备数据
1.2.3.2.1 方法1
进入数据库，通过source
mysql> use backup_full; mysql> source /tmp/backup_full.sql; mysql> select * from backup_full.full; +------+--------+ | c1 | c2 | +------+--------+ | 1 | full1 | | 2 | full2 | | 3 | full3 | | 4 | full4 | | 5 | full5 | | 6 | full6 | | 7 | full7 | | 8 | full8 | | 9 | full9 | | 10 | full10 | +------+--------+ 6.2.3.2.2 方法2
直接还原数据文件
mysql -u用户名 -p密码 -hIP地址 -P端口 数据库名 < /tmp/buckup_full.sqlmysql -uroot -pjesse -h127.0.0.1 -P3355 backup_full < /tmp/buckup_full.sqlmysql> select * from backup_full.full;+------+--------+| c1 | c2 |+------+--------+| 1 | full1 || 2 | full2 || 3 | full3 || 4 | full4 || 5 | full5 || 6 | full6 || 7 | full7 || 8 | full8 || 9 | full9 || 10 | full10 |+------+--------+1.2.3.3 还原增备数据
1.2.3.3.1 sql方式
mysql> source /tmp/increment1.txt; mysql> select * from backup_increment.increment; +------+------------+ | c1 | c2 | +------+------------+ | 11 | increment1 | | 12 | increment2 | | 13 | increment3 | | 14 | increment4 | | 15 | increment5 | +------+------------+ 1.2.3.3.2 binlog方式
mysqlbinlog binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口 mysqlbinlog mysql-bin.000003 | mysql -uroot -pjesse -h127.0.0.1 -P3355 mysql> select * from backup_increment.increment; +------+-------------+ | c1 | c2 | +------+-------------+ | 11 | increment1 | | 12 | increment2 | | 13 | increment3 | | 14 | increment4 | | 15 | increment5 | | 16 | increment16 | | 17 | increment17 | | 18 | increment18 | | 19 | increment19 | | 20 | increment20 | +------+-------------+ 参考文章
1.2.3.3 注意事项
1.2.3.3.1 多个binlog文件一次完成
在采用binlog恢复的时候: 最好将所有文件使用一个连接完成，如果使用不同连接的话有时会导致不安全.
如果第一个日志包含创建临时表语句CREATE TEMPORARY TABLE，第二个日志要使用该临时表，第一个导入binlog日志的进程退出后临时表会被删除，执行第二个日志文件要使用临时表时会因找不到而报 “unknown table.”
//方法1:所有二进制文件放在单个连接里[root@localhost /]# mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p //方法2;将所有二进制文件写在一个文件里,然后执行[root@localhost /]# mysqlbinlog binlog.000001 > /tmp/statements.sql [root@localhost /]# mysqlbinlog binlog.000002 >> /tmp/statements.sql [root@localhost /]# mysql -u root -p -e "source /tmp/statements.sql" //使用方法2时 如果二进制文件里包含GTID信息需要过滤掉[root@localhost /]# mysqlbinlog --skip-gtids binlog.000001 > /tmp/dump.sql [root@localhost /]# mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql [root@localhost /]# mysql -u root -p -e "source /tmp/dump.sql" 1.3 案例操作2
恢复binlog文件中的指定时间或者位置(pos)来恢复数据.
查看所有binlog文件
// 查看当前binlog文件信息（包含文件名，当前位置等）mysql> SHOW BINARY LOGS; mysql> SHOW master status;//能查看binlog的file名称,position,Binlog_Do_DB,Binnlog_ignore_DB,Executed_Gtid_sec等信息.通过mysqlbinlog工具将binary log文件由二进制转换为可读的文本文件，可以选择基于时间或位置的事件。
1.3.1 通过事件的时间来恢复
参数 –start-datetime 和 –stop-datetime 指定恢复binlog日志的起止时间点，时间使用DATETIME格式。
比如在时间点2005-04-20 10:00:00我们删除掉一个库，我们要恢复该时间点前的所有日志.
//只有--stop-datatime 就是从最开始到stop时间全部//恢复指定时间之前的[root@localhost /]# mysqlbinlog --stop-datetime="2005-04-20 10:00:00" /usr/local/mysql/data/binlog.123456 | mysql -u root -p 我们可能几个小时后才发现该错误，后面又有一系列的增删查改等操作，我们还需要恢复后续的binlog，我们可以指定起始时间.
//只有--start-datatime 就是从start时间到最后//恢复从指定时间到最后的 [root@localhost /]# mysqlbinlog --start-datetime="2005-04-20 10:01:00" /usr/local/mysql/data/binlog.123456 | mysql -u root -p //也可以用来复制备份执行时间的binlog到sql [root@localhost /]# mysqlbinlog --start-datetime="2005-04-20 9:55:00" --stop-datetime="2005-04-20 10:05:00" /usr/local/mysql/data/binlog.123456 > /tmp/mysql_restore.sql 1.3.2 通过事件的位置来恢复
参数 –start-position 和 –stop-position 指定恢复binlog日志的起止位置点，通过位置的恢复需要我们有更加精细的操作.例如在某个时间点我们执行了错误的语句，且这个时间点前后都有大并发操作，要确定破坏性sql的时间点，我们可以先导出大致的时间段的日志到文件以缩小查找范围，再去分析和确定.
//从指定位置恢复binlog日志数据.
[root@localhost /]# mysqlbinlog --stop-position=368312 /usr/local/mysql/data/binlog.123456 | mysql -u root -p
[root@localhost /]# mysqlbinlog --start-position=368315 /usr/local/mysql/data/binlog.123456 | mysql -u root -p
参考文章
1.4 备份建议
通过全备份和增量备份配合使用.
方案如下: 每周一做一个全备份,mysqldump,启用增量备份,把过期时间设置大于7(如:EXPIRE_LOGS_DAYS=8).
如果数据库崩了就用全备份+增量备份. 如果缺少部分数据,则可以用增量备份来实现.
2. 主从复制
2.1 理论
2.1.1 定义
从服务端看:要有N台从服务器和主服务器保持数据一致.从客户端看:比如有一条insert语句和一条select 语句,
则要区分读/写语句,并且分别请求从/主服务器.
主服务器来进行数据的增删改,从服务被动同步.
2.1.2 服务器端读写分离的具体技术
1:数据库集群技术
集群由3个概念
1):sql节点 sql node
2):数据节点 data node
3):管理节点 ndb managerment
Sql语句发送”1sql节点”, “1sql”节点发往”2数据节点”,再由3管理节点完成数据节点的之间的同步.
集群技术相对复杂,至少有3种节点,4台服务器才能完成.
2.2 数据库复制 replication
数据库复制 replication 的实现原理
1:主服务器凡运行语句,都产生一个二进制日志 binlog
2:从服务器不断读取主服务器的binlog
3:从主服务读取到的binlog,转换为自身可执行的relaylog,
4:执行relaylog
实现步骤:
- 1:首先确保主服务器打开二进制日志功能.
这样,主服务器一旦有数据变化,立即产生二进制日志.
2 :从服务器也需要开启二进制日志和relay日志功能.
这样可以从主服务器读取binlog,并产生relaylog3:在主服务器建立一个从服务器的账号,并授予数得上权限.
4: 指定从服务对应的主服务器,开启从服务器.
1.修改配置文件 /ect/my.cnf1.1 master 修改//在最后追加#[repl]#server-idserver-id=130#binary loglog-bin=mysql-bin#statement row miexed三种类型,miexed是前两者的混合binlog-format=mixed1.2 slaver 修改//在最后追加#[repl]#server-idserver-id=129#relay-logrelay-log=mysql-relay~ 1.3 master和slaver都重启service mysqld start 1.4重启完查看产生日志 1.4.1 查看日志文件mysql-bin.000001[root@bogon mysql]# pwd/var/lib/mysql[root@bogon mysql]# lltotal 122956-rw-r-----. 1 mysql mysql 56 Oct 9 23:05 auto.cnf-rw-------. 1 mysql mysql 1679 Oct 9 23:05 ca-key.pem-rw-r--r--. 1 mysql mysql 1074 Oct 9 23:05 ca.pem-rw-r--r--. 1 mysql mysql 1078 Oct 9 23:05 client-cert.pem-rw-------. 1 mysql mysql 1679 Oct 9 23:05 client-key.pem-rw-r-----. 1 mysql mysql 338 Nov 19 23:14 ib_buffer_pool-rw-r-----. 1 mysql mysql 12582912 Nov 19 23:15 ibdata1-rw-r-----. 1 mysql mysql 50331648 Nov 19 23:15 ib_logfile0-rw-r-----. 1 mysql mysql 50331648 Oct 9 23:05 ib_logfile1-rw-r-----. 1 mysql mysql 12582912 Nov 19 23:15 ibtmp1drwxr-x---. 2 mysql mysql 4096 Oct 9 23:05 mysql-rw-r-----. 1 mysql mysql 154 Nov 19 23:15 mysql-bin.000001-rw-r-----. 1 mysql mysql 19 Nov 19 23:15 mysql-bin.indexsrwxrwxrwx. 1 mysql mysql 0 Nov 19 23:15 mysql.sock-rw-------. 1 mysql mysql 6 Nov 19 23:15 mysql.sock.lockdrwxr-x---. 2 mysql mysql 4096 Oct 9 23:05 performance_schema-rw-------. 1 mysql mysql 1675 Oct 9 23:05 private_key.pem-rw-r--r--. 1 mysql mysql 451 Oct 9 23:05 public_key.pemdrwxr-x---. 2 mysql mysql 4096 Nov 19 23:12 replDatabase-rw-r--r--. 1 mysql mysql 1078 Oct 9 23:05 server-cert.pem-rw-------. 1 mysql mysql 1675 Oct 9 23:05 server-key.pemdrwxr-x---. 2 mysql mysql 12288 Oct 9 23:05 sys#1.4.2 查看当前日志文件mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 | 154 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> //注意每重启一次服务需要重新连接一下msyql,否则报如下错误/*mysql> show master status;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 5Current database: *** NONE ****/2.连接账户2.1 主账户准备账户在主机上执行[root@bogon etc]# mysql -uroot -pmysql> grant replication client,replication slave on *.* -> to 'repl'@'192.168.%.%' -> identified by 'repl';Query OK, 0 rows affected, 1 warning (0.06 sec)mysql> 2.2 从账户连接账户2.2.1 执行如下sql//其中参数含义为主服务主机,用户,密码,当前的日志文件,和posid(1.4.2中查看的)mysql> change master to -> master_host='192.168.17.130', -> master_user='repl', -> master_password='repl', -> master_log_file='mysql-bin.000003', -> master_log_pos=154;Query OK, 0 rows affected, 2 warnings (0.12 sec)mysql> //从账户在第一步中配置好 需要重启服务,否则可能会报如下错误/*mysql> change master to -> master_host='192.168.17.130', -> master_user='repl', -> master_password='repl', -> master_log_file='mysql-bin.000003', -> master_log_pos=154;ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.mysql> exit;*/2.2.2 此时配置完成,查看slave status如下mysql> show slave status \G*************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.17.130 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 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: 154 Relay_Log_Space: 154 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: NULLMaster_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 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)2.2.3 启动slavemysql> start slave;Query OK, 0 rows affected (0.04 sec)此时再查看salve状态,两次查看如下,一次连接中,一次等待中mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.17.130 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Connecting 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: 154 Relay_Log_Space: 154 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: NULLMaster_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 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specifiedmysql> show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.17.130 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 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: 154 Relay_Log_Space: 523 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: 0Master_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: 130 Master_UUID: 51db3682-ad03-11e7-8903-000c29824847 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)mysql> 3.其它注意总结-3.1 报错ERROR: No query specified是因为\G; 后面不能有;3.2 终止slave服务msyql> stop slave;msyql> reset slave;//可以重新设定slave3.3 随时可以通过 mysql> show slave status \G 来查看同步状态如果开启同步之前主服务有一个表,则此时如果对表做修改,则不能同步到从(从不会重新建表,然后复制数据)3.4 中间出现了同步失败的情况(原因是在构建主从备份时,主库存在数据库,则准备删掉后重新建表,而此时从库同步时要删除该表,不存在则报错了.此时看slave status如下)3.4.1 查看错误现状mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.17.130 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1079 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes 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: 1008 Last_Error: Error 'Can't drop database 'replDb'; database doesn't exist' on query. Default database: 'replDb'. Query: 'drop database `replDb`' Skip_Counter: 0 Exec_Master_Log_Pos: 750 Relay_Log_Space: 852 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: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1008 Last_SQL_Error: Error 'Can't drop database 'replDb'; database doesn't exist' on query. Default database: 'replDb'. Query: 'drop database `replDb`' Replicate_Ignore_Server_Ids: Master_Server_Id: 130 Master_UUID: 51db3682-ad03-11e7-8903-000c29824847 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 171120 23:34:23 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)解决办法:3.4.2 stop slave;mysql> stop slave;Query OK, 0 rows affected (0.03 sec)3.4.3 查看停止后的slave status;mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.17.130 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1239 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 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: 1008 Last_Error: Error 'Can't drop database 'replDb'; database doesn't exist' on query. Default database: 'replDb'. Query: 'drop database `replDb`' Skip_Counter: 0 Exec_Master_Log_Pos: 750 Relay_Log_Space: 1012 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: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1008 Last_SQL_Error: Error 'Can't drop database 'replDb'; database doesn't exist' on query. Default database: 'replDb'. Query: 'drop database `replDb`' Replicate_Ignore_Server_Ids: Master_Server_Id: 130 Master_UUID: 51db3682-ad03-11e7-8903-000c29824847 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 171120 23:34:23 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)3.4.4 删掉(重命名移走)slave的错误日志[root@bogon ~]# mv /var/log/mysqld.log /var/log/mysqld.log.2错误日志路径查看/etc/my.cnf中的配置log-error=/var/log/mysqld.log3.4.5 查看master的status,修改新的master_log_file和master_log_pos,然后再次在slave中执行mysql> mysql> change master to -> master_host='192.168.17.130', -> master_user='repl', -> master_password='repl', -> master_log_file='mysql-bin.000003', -> master_log_pos=1239;Query OK, 0 rows affected, 2 warnings (0.01 sec)3.4.6 查看slave的statusmysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.17.130 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1239 Relay_Log_File: mysql-relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 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: 1239 Relay_Log_Space: 154 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: NULLMaster_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: 130 Master_UUID: 51db3682-ad03-11e7-8903-000c29824847 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)3.4.7 再次重启slavemysql> start slave;Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.17.130 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1239 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 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: 1239 Relay_Log_Space: 523 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: 0Master_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: 130 Master_UUID: 51db3682-ad03-11e7-8903-000c29824847 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)3.主主复制
3.1 定义
在主从的配置中,2台服务器地位有差别,一主一从.
两台服务器之间都可以插入,修改,删除数据,相互复制数据.
从服务器一是起到备份作用,一是起到分担查询压力的作用.
接下来的配置,2台服务器之间,没有明显的地位差距, 两者可以同步对方的内容.
3.2 大致思路:
1: 2台服务器都设置上2进制日志和relay日志
2: 都设置上replcation账号
3: 都设置对方为自己的master
主主复制下一定要注意避免的问题- -同步冲突
3.3 操作步骤
在主从备份中增加如下: 1. 原master中,加入relay-log#relay-logrelay-log=mysql-relay2.原slave中加入log#binary loglog-bin=mysql-bin#statement row miexed三种类型,miexed是前两者的混合binlog-format=mixed3.原slave中新增repl账户[root@bogon etc]# mysql -uroot -pmysql> grant replication client,replication slave on *.* -> to 'repl'@'192.168.%.%' -> identified by 'repl';Query OK, 0 rows affected, 1 warning (0.06 sec)mysql> 4.原slave中查看 master statusshow master status;5.原master中新配对应主连接mysql> change master to -> master_host='192.168.17.119', -> master_user='repl', -> master_password='repl', -> master_log_file='mysql-bin.000001', -> master_log_pos=101;6.原master启动slave mysql>start slave;3.4 主键冲突如何解决?
让1台服务器 1,3,5,7来增长
另1台服务器 2,4,6,8来增长
一台服务器：
set global auto_increment_increment = 2; set global auto_increment_offset = 1; set session auto_increment_increment = 2; set session auto_increment_offset = 1; 另一台服务器：
set global auto_increment_increment = 2;set global auto_increment_offset = 2; set session auto_increment_increment=2;set session auto_increment_offset = 2; 注：auto-increment-increment和 auto-increment-offset 要写到配置文件中，防止下次重启后失效．
但是如果后期加服务器怎么办?
可以在业务逻辑中解决..
通过incr先生成好ID存放在redis中,然后redis的inr来获取.或者主键加上服务器标志.
4.被动主主复制
将主主复制其中一台B的read_only设置为只读,然后接受另一台A的同步. 如果A挂了.则通过命令行修改B的read_only为不只读,则数据可以写入到B上. 这样做的目的是实现主的一个备份. 这种情况一般还会每个主有各自的N个slave. 插入到从服务器上.
mysql> show variables like '%read%';+-----------------------------------------+---------------------------+| Variable_name | Value |+-----------------------------------------+---------------------------+| innodb_purge_threads | 4 || innodb_random_read_ahead | OFF || innodb_read_ahead_threshold | 56 || innodb_read_io_threads | 4 || innodb_read_only | OFF || innodb_thread_concurrency | 9 || innodb_thread_sleep_delay | 9 || innodb_write_io_threads | 4 || max_delayed_threads | 20 || max_insert_delayed_threads | 20 || myisam_repair_threads | 1 || net_read_timeout | 30 || performance_schema_max_thread_classes | 50 || performance_schema_max_thread_instances | -1 || pseudo_thread_id | 2 || read_buffer_size | 8192 || read_only | OFF || read_rnd_buffer_size | 91136 || super_read_only | OFF || thread_cache_size | 10 || thread_handling | one-thread-per-connection || thread_stack | 262144 || tx_read_only | OFF |+-----------------------------------------+---------------------------+23 rows in set, 1 warning (0.00 sec)mysql>在my.cnf中增加配置read_only=14. mysql_proxy
https://downloads.mysql.com/archives/proxy/
MySQL Proxy is not GA, and is not recommended for Production use.
We recommend MySQL Router for production use. Download MySQL Router »
//查看帮助命令mysql-proxy --help 执行命令(mysql-proxy的端口4040):./binmsyql-proxy -P 192.168.0.1:4040 --proxy-backend-addresses=192.168.0.2:3306 --proxy-backend-addresses=192.18.0.3:3306自动就有负载均衡的功能,均衡不是体现在sql语句,一会请求mysqlA服,一会请求mysqlB服.均衡是体现”连接”的均衡上.
如何支持读写分离:
mysql_proxy带有脚本在./share/doc/mysql-proxy目录下,里面有rw-splitting.lua脚本.
简写读写分离语句如下: ./bin/msyql-proxy -b 192.168.0.2:3306 -r 192.18.0.3:3306 -s /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-spliting.lua注意:rw-spliting.lua 有配置最少多少个连接才启用读写分离. 5. 小技巧
本机登陆不用账户密码
/usr/libexec/mysqld –skip-grant-tables //不用密码就可以登陆(启动跳过权限检测)
阅读全文
0 0
- Mysql总结4-binlog备份恢复及集群
- mysql dump备份恢复，binlog基于时间点的恢复
- Mysql备份恢复总结
- mysql备份及恢复
- 用mysqldump备份及结合binlog日志恢复的全过程
- mysqldump备份及结合binlog日志恢复的全过程
- 用mysqldump备份及结合binlog日志恢复的全过程
- mysql binlog恢复数据库
- MYSQL数据BINLOG恢复
- mysql binlog恢复数据
- mysql 基于binlog恢复
- mysql-binlog备份初探
- mysql 冷备份binlog
- MySQL备份和恢复总结
- mysql自动化备份及恢复
- mysql数据库备份及恢复
- mysql数据库备份及恢复
- Linux MySQL备份及恢复
- 1021第三届程序设计大赛 哥德巴赫猜想
- 1026第二届程序设计大赛 丑陋的数
- IDEA快捷键
- Ubuntu
- overflow的那些事儿
- Mysql总结4-binlog备份恢复及集群
- 模拟实现strncpy,strncat,strncmp
- java反射详解--三分钟学会使用java反射
- 练习6
- hibernate_day02_14_Query对象查询所有记录
- ZooKeeper的安装和配置
- mysql命令使用详解
- 解决 批处理 for 体内的字符串截取问题
- MySQL创建用户与授权方法
