MySQL学习笔记
博客专区 > 香胖 的博客 > 博客详情
MySQL学习笔记
香胖 发表于2年前
MySQL学习笔记
  • 发表于 2年前
  • 阅读 3
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

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
博文 31
码字总数 20679
×
香胖
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: