文档章节

MySQL学习笔记

香胖
 香胖
发布于 2015/11/20 06:21
字数 3696
阅读 5
收藏 0

1. 对myisam引擎的备份命令:

    mysqldump --user=root --all-databases --flush-privileges --lock-all-tables --master-data=1 --flush-logs --triggers --routines --events --hex-blob > /home/data/full_dump.sql


2.对InnoDB引擎的备份命令:

    mysqldump --user=root --all-databases --flush-privileges --single-transaction --master-data=1 --flush-logs --triggers --routines(


3. key_buffer_size 该参数是Myisam引擎存储索引的缓冲区。

    mysql> set global key_buffer_size=8*1024*1024;

    Query OK, 0 rows affected (0.01 sec)

 

4. 如何更改运行数据库的参数:

   先set global,再修改my.cnf,实现立即生效以及重启生效。


5. mysqlbinlog拆库命令:

   mysqlbinlog -d db1 mysql-bin.00005 > /data/db1.sql


6. Mysqlbinlog指定起始点拆分: --result-file=name, -r name

   mysqlbinlog mysql-bin.00005 --start-position=550 --stop-position=1589 -r db1.sql


7. 主从复制原理:

  整体上来说,复制有3个步骤:   

       (1)    master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);
       (2)    slave将master的binary log events拷贝到它的中继日志(relay log);
        (3)    slave重做中继日志中的事件,将改变反映它自己的数据。

下图描述了复制的过程:

                                  

          该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前,master在二日志记录这些改变。MySQL将事务串行的写入二进制日志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,master通知存储引擎提交事务。
       下一步就是slave将master的binary log拷贝到它自己的中继日志。首先,slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接,然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件,如果已经跟上master,它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
       SQL slave thread(SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件,并重放其中的事件而更新slave的数据,使其与master中的数据一致。只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。
        此外,在master中也有一个工作线程:和其它MySQL的连接一样,slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的,也就是说master上的并行更新操作不能在slave上并行操作。

 

   从库开启bin-log的参数:

   1. 开始log_bin on

   2. log_salve_updates on


  授权从库账号:

   grant replication slave on db2.* to salve@"10.0.0.%" identified by 'XXXXX';

 

  造成锁表失效的原因:

  interactive_timeout = 60

  wait_timeout =60

  show variables like '%timeout%';进行查询设置。

  本问题较好的解决方法是dump的时候刷新LOG。

 

  忽略主从复制同步信息的参数:

  replicate-ignore-db=mysql

  binlog-do-db=testdb

  binlog-ignore-db=mysql

  binlog-ignore-db=performance_schema

  binlog-ignore-db=information_schema

 

  关于read-only参数,只能禁止普通用户更新,但不能阻止root和replication等SUPER用户更新!

 

  对于提示SALVE的SQL线程NO的时候,如果影响不大,可以跳过一个执行的event继续执行SQL线程,操作如下:

  stop slave;      set global sql_slave_skip_counter =1 ;   start slave;

  或者根据错误号来忽略错误:

  slave_skip_errors = 1007,1062,1032;

  

   主宕机切换从:

   stop slave; show full processlist; 检查两个THREAD是否完成,BINLOG是否完全,再

   reset master;

   删掉master-info  slave-info,和配置文件里的read-only, log_slave_updates;

   service mysqld restart

 

 

 MySQL全量备份和增量备份

   首先恢复之前的全量备份 db_name.sql,然后用mysqlbinlog进行时间点还原: 
   mysql -uroot -proot db_name < db_name.sql 
   mysqlbinlog --stop-datetime="2013-10-12 10:30:00" /var/log/mysql/mysql-bin.000001  |  mysql -uroot -proot 

  

将一个mysqlbinlog文件导为sql文件

cd  cd /usr/local/mysql
./mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/001.sql

将mysql-bin.000001日志文件导成001.sql

可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间

./mysqlbinlog --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 > /opt/004.sql

将mysql-bin.000002文件中截止到2009-04-10 17:41:28的日志导成004.sql

 ./mysqlbinlog --start-date="2009-04-10 17:30:05" --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002  /usr/local/mysql/data/mysql-bin.0000023> /opt/004.sql

----如果有多个binlog文件,中间用空格隔开,打上完全路径

 ./mysqlbinlog --start-date="2009-04-10 17:30:05" --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 |mysql -u root -p123456

或者  source /opt/004.sql

将mysql-bin.000002日志文件中从2009-04-10 17:30:05到2008-04-10 17:41:28截止的sql语句导入到mysql中

 

 

   刷新BINLOG命令:

   mysqladmin -u root -p flush-logs

 

   锁表命令:FLUSH TABLES WITH READ LOCK;

 

 

   主从不同步案例:

   1.  日志里面有大量的这种警告,意思应该是statement 格式不安全,用vim 打开他看了一下,发现好多这类警告,我说为什么错误日志怎么变这么大了呢!!
statement format 应该是 binlog的一种格式,进入从库查看一下
show global variables like 'binlog_format';
果然当前的格式为statement 

我需要把格式改为 mixed格式
修改从库的 my.cfg
[mysqld]下面加入下面这行
binlog_format=mixed

然后重启mysql服务,发现错误日志里的 警告 都停止了。这回清静多了~~

我突然想起一件事,记得有朋友说过 RBR 模式可以解决很多因为主键冲突导致的主从无法同步情况,想到这里我就想要不要把 slave-skip-errors = 1062 去掉再试试,
于是就进入到my.cnf 里在注释掉了 slave-skip-errors = 1062
再次重新启动 mysql服务
进入从库

show slave status \G;
.........               
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 

1.网络的延迟

由于mysql主从复制是基于binlog的一种异步复制,通过网络传送binlog文件,理所当然网络延迟是主从不同步的绝大多数的原因,特别是跨机房的数据同步出现这种几率非常的大,所以做读写分离,注意从业务层进行前期设计。

2.主从两台机器的负载不一致

由于mysql主从复制是主数据库上面启动1个io线程,而从上面启动1个sql线程和1个io线程,当中任何一台机器的负载很高,忙不过来,导致其中的任何一个线程出现资源不足,都将出现主从不一致的情况。

3.max_allowed_packet设置不一致

主数据库上面设置的max_allowed_packet比从数据库大,当一个大的sql语句,能在主数据库上面执行完毕,从数据库上面设置过小,无法执行,导致的主从不一致。

4.key自增键开始的键值跟自增步长设置不一致引起的主从不一致。

5.mysql异常宕机情况下,如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出现binlog或者relaylog文件出现损坏,导致主从不一致。

6.mysql本身的bug引起的主从不同步。

7.版本不一致,特别是高版本是主,低版本为从的情况下,主数据库上面支持的功能,从数据库上面不支持该功能。

以上就是常见的一些主从不同步的情况。或许还有其他的一些不同步的情况,请说出你所遇到的主从不一致的情况。

基于以上情况,先保证max_allowed_packet、自增键开始点和增长点设置一致,再者牺牲部分性能在主上面开启sync_binlog,对于采用innodb的库,推荐配置下面的内容

1、innodb_flush_logs_at_trx_commit = 1
2、innodb-support_xa = 1 # Mysql 5.0 以上
3、innodb_safe_binlog      # Mysql 4.0

同时在从数据库上面推荐加入下面两个参数

1、skip_slave_start
2、read_only

 

 

--skip-slave-start的重要性 

    原来做复制的主机因为数据丢失需要重新创建复制环境,机器上已经有了主库数天前的备份,于是删除数据目录直接把备份放上去,结果发现复制没有抱错,show slave status一切正常,select count(*)某张大表也是不断增加,但是查询该表的max id确迟迟不动。

    于是把这条最大的数据拿出来看,发现数据是今天的。而slave的同步信息显示才读取到2天前的binlog而已。

这台机器既做slave又做master,设置了

    log-bin
    log-slave-updates

    环境比较复杂,一开始猜想是不是环境设置问题造成的,但是检查回来没啥问题,再仔细想想。猜到问题原因,问了下,果然是没有删除master.info造成的,因为默认Mysql的slave会随数据库启动而启动,因此mysql就直接从当前位置开始读取,造成读取了几条今天的数据,而后因为change master把复制的信息重置了,因此光从max id看就是没有变化而数据却在实际增加,等到了这几条数据就会报1062违反重复的错误。所以为了安全期间,复制环境的数据库还是设置--skip-slave-start参数,防止复制随着mysql启动而自动启动。

 

 

 

1. MySQL数据库主从同步延迟原理。
    要说延时原理,得从mysql的数据库主从复制原理说起,mysql的主从复制都是单线程的操作
    主库对所有DDL和DML产生binlog,binlog是顺序写,所以效率很高,slave的Slave_IO_Running线程到主库取日志,效率很比较高,下一步,问题来了,slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的,不是顺序的,成本高很多,还可能可slave上的其他查询产生lock争用,由于Slave_SQL_Running也是单线程的,所以一个DDL卡主了,需要执行10分钟,那么所有之后的DDL会等待这个DDL执行完才会继续执行,这就导致了延时。有朋友会问:“主库上那个相同的DDL也需要执行10分,为什么slave会延时?”,回答是master可以并发,Slave_SQL_Running线程却不可以。

2. MySQL数据库主从同步延迟是怎么产生的。
    当主库的TPS并发较高时,产生的DDL数量超过slave一个sql线程所能承受的范围,那么延时就产生了,当然还有就是可能与slave的大型query语句产生了锁等待。

3. MySQL数据库主从同步延迟解决方案。
    丁奇的transefer是一个不错的方案,不过一般公司受限于对mysql的代码修改能力的限制和对mysql的掌控能力,还是不太适合。
最简单的减少slave同步延时的方案就是在架构上做优化,尽量让主库的DDL快速执行。还有就是主库是写,对数据安全性较高,比如sync_binlog=1,innodb_flush_log_at_trx_commit = 1 之类的设置,而slave则不需要这么高的数据安全,完全可以讲sync_binlog设置为0或者关闭binlog,innodb_flushlog也可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave。
    mysql-5.6.3已经支持了多线程的主从复制。原理和丁奇的类似,丁奇的是以表做多线程,oracle使用的是以数据库(schema)为单位做多线程,不同的库可以使用不同的复制线程。

    sync_binlog=1 o
    This makes MySQL synchronize the binary log's contents to disk each time it commits a transaction
    默认情况下,并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能binlog中最后的语句丢 失了。要想防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使binlog在每N次binlog写入后与硬盘 同步。即使sync_binlog设置为1,出现崩溃时,也有可能表内容和binlog内容之间存在不一致性。如果使用InnoDB表,MySQL服务器 处理COMMIT语句,它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃,重启时,事务被InnoDB回滚,但仍 然存在binlog中。可以用--innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。(注释:在MySQL 5.1中不需要--innodb-safe-binlog;由于引入了XA事务支持,该选项作废了),该选项可以提供更大程度的安全,使每个事务的 binlog(sync_binlog =1)和(默认情况为真)InnoDB日志与硬盘同步,该选项的效果是崩溃后重启时,在滚回事务后,MySQL服务器从binlog剪切回滚的 InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等,并使从服务器保持与主服务器保持同步(不接收 回滚的语句)。

    innodb_flush_log_at_trx_commit (这个很管用)
    抱怨Innodb比MyISAM慢 100倍?那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘,这是很费时的。特别是使用电 池供电缓存(Battery backed up cache)时。设成2对于很多运用,特别是从MyISAM表转过来的是可以的,它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘,所以你一般不会丢失超过1-2秒的更新。设成0会更快一点,但安全方面比较差,即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。

 

 

MySQL三种复制技术详解:

MySQL数据的主从复制、半同步复制和主主复制详解 - goustzhu地盘 - 博客频道 - CSDN.NET
http://blog.csdn.net/goustzhu/article/details/9339621

 

 

 MySQL BINLOG的三种模式:

   1. statment level:早期的默认格式,忠实陈述所执行的SQL语句,因为新版本带来的新功能太多,容易导致很多报错。

   2. row level:行级格式,忠实记录实际发生的数据变化,比如ALTTER TABLE SET XX=XXX,将记录下影响到的每一行数据,等于重建表,因此可能导致日志文件很大!

   3. mixed level: 略。

 

存储引擎知识:

   1. 事务的ACID特性:

      Atomicity原子性

      Consisitency一致性

      Isolation隔离性

      Durability持久性

   2. 提交事务

      start transaction

      commit

      rollback

   3. MyISAM引擎特点:

     1. 不支持事务

     2. 表级锁

     3. 读写互相阻塞

     4. 只缓存索引(key_buffer_size)

     5. 读取速度快,占用资源少。

     6. 不支持外键约束,但支持全文索引。

 

 

 

© 著作权归作者所有

共有 人打赏支持
香胖
粉丝 0
博文 40
码字总数 20679
作品 0
苏州
MySQL入门很简单-学习笔记 - 索引页

索引一下,方便阅读,后面有完整的PDF下载地址 MySQL入门很简单-学习笔记 - 第1 章 数据库概述 MySQL入门很简单-学习笔记 - 第 2 章 Windows平台下安装与配置MySQL MySQL入门很简单-学习笔...

晨曦之光
2012/03/09
0
0
读《PHP和MySQL Web开发》

【第一部分】PHP基础与入门 1、PHP学习笔记1:基础知识快速浏览 http://my.oschina.net/bluefly/blog/472673 2、PHP学习笔记2:文件 http://my.oschina.net/bluefly/blog/477601 3、PHP学习笔...

slyso
2015/07/14
0
0
Golang学习笔记目录

Golang 介绍 Go语言是谷歌2009发布的第二款开源编程语言。 Go语言专门针对多处理器系统应用程序的编程进行了优化,使用Go编译的程序可以媲美C或C++代码的速度,而且更加安全、支持并行进程。...

ChainZhang
2017/12/26
0
0
Golang 学习笔记(10)—— mysql操作

本文为转载,原文:Golang 学习笔记(10)—— mysql操作 Golang go-sql-driver/mysql go操作mysql的驱动包很多,这里讲解当下比较流行的go-sql-driver/mysql 安装 执行下面两个命令: 安装完...

ChainZhang
01/04
0
0
MySQL入门教程系列-1.5 如何学习MySQL

在这里持续更新 MySQL入门教程系列-1.5 如何学习MySQL 如何学习 MySQL 这是一个伪命题,每个人都有适合自己的一套学习方法,各

同一种调调
2016/09/28
15
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

新工作与老项目

新的工作不知不觉的干了一个多月了。怎么说呢,跟想象中的差别不少,本来想的能进来跟大公司的同事能有很多交流,能在团队中跟大牛学习更快。结果公司的这个项目上只有两个程序员,项目是十年...

zypy333
10分钟前
0
0
mysql 在windows的安装

mysql 在windows的安装。 mysql64位的server的下载地址是: https://dev.mysql.com/downloads/mysql/ 使用的是5.7版本。 下载安装包,解压至D:\mysql\mysql-5.7.23-winx64\ 在D:\mysql\mysq...

lxzh504
22分钟前
1
0
云技术、大数据(hadoop)入门常见问题回答

当我们学习一门新技术的时候,我们总是产生各种各样的问题,这些问题整理出来,包括该 1.如何学习hadoop? 2.hadoop常见问题? 3.还有hbase、hive安装使用等? 你知道搭建hadoop平台需要些什...

董黎明
22分钟前
1
0
小程序自定义底部tab

场景 1.tabBar是在内页而非首页,这时就不得不自定义一个tabBar了 2.自定义风格 3.子页数量超过5个,得到更多了tab 4.改变点击tab默认事件,比如出登录界面,或者弹出上拉子菜单等 步骤 1.照...

萤火的萤火
28分钟前
1
0
shell炫技

1.为脚本添加“--help” #!/bin/shif [ ${#@} -ne 0 ] && [ "${@#"--help"}" = "" ]; then printf -- '...help...\n'; exit 0;fi; 2.输出字体添加颜色 https://misc.flogisoft.com......

HJCui
28分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部