mysql主从同步实验
【实验环境】
master rhel6.3（最小化）
slave rhel6.3（最小化）
IP分配：
master192.168.30.110
slave192.168.30.106
一、配置Master
1、Master上为slave添加一个用户，并授权
mysql>grant replication slave on*.*to'rep'@'192.168.30.106'identified by'123456';
Query OK,0rows affected(0.05sec)
2、编辑https://www.doczj.com/doc/4b2752390.html,f
[root@master~]#vim/etc/https://www.doczj.com/doc/4b2752390.html,f
server-id=1//指定ID，这个ID必须唯一
log-bin=mysql-bin//开启log-bin二进制日志文件
注:[mysqld]里面默认有server-id=1，需要去掉这行，否则在实验过程中，经常会因为忽略了这一行的存在而报server-id相同的错误。
3、重启mysql
[root@master~]#service mysqld restart
4、记录下二进制日志文件名以及位置
这里我们将模拟实际生产环境来进行实验，在AB同步之前需要先将主服务器备份给从服务器，以确保两台服务器的数据是一样的。
到这一步，我们需要记录下二进制日志文件的文件名以及位置，这样可以告诉从服务器同步的时候从哪个文件哪个位置开始同步，所以必须保证这个位置不变。但是备份的操作也会改变其位置，所以在做备份之前需要先锁表。
(1)锁表
mysql>flush tables with read lock;
Query OK,0rows affected(0.01sec)
(2)记录二进制日志文件及日志
mysql>show master status;
+------------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
|mysql-bin.000005|107|||
+------------------+----------+--------------+------------------+
1row in set(0.00sec)
//需要记录下二进制文件的名字和位置
(3)备份
[root@master~]#mysqldump-uroot-p rep>/usr/local/mysql/rep.sql
(4)解锁表
mysql>unlock tables;
Query OK,0rows affected(0.00sec)
二、拷贝数据
在同步之前，需要保证两台服务器上的数据库是一致的，如果数据库都是新装的，则可以跳过这一步
(1)将主服务器上的备份文件拷贝到从服务器上
[root@master~]#scp/usr/local/mysql/rep.sql root@192.168.30.106:/usr/local/mysql/
root@192.168.30.106's password:
rep.sql100%1259 1.2KB/s00:00
(2)复制
[root@slave~]#mysql
如果备份的时候遇到这样的问题：
[root@master~]#mysqldump-uroot-p--all-databases>/usr/local/mysql/rep1.sql
Enter password:
mysqldump:Got error:1142:SELECT,LOCK TABL command denied to user'root'@'localhost'for table'cond_instanc es'when using LOCK TABLES
解决办法:
[root@master~]#mysqldump-uroot-p--all-databases--skip-lock-tables>/usr/local/mysql/rep1.sql
三、配置slave
1、配置https://www.doczj.com/doc/4b2752390.html,f
[root@slave~]#vim/etc/https://www.doczj.com/doc/4b2752390.html,f
server-id=2//设置server-id，不可重复
log-bin=mysql-bin//开启二进制日志，用于链式复制的情况下，即这台服务器如果需要作为其他从服务器的主服务器，则需要开启这个选项
read_only=1//设置为只读
relay_log=mysql-relay-bin//配置中继日志
log_slave_updates=1//表示slave将复制事件写进自己的二进制日志
注：https://www.doczj.com/doc/4b2752390.html,f中默认有server-id=1这一行，可直接对这行修改或者去掉该行，自行添加一个唯一的id
2、重启mysql
[root@slave~]#service mysqld restart
Shutting MySQL...SUCCESS!
Starting MySQL...SUCCESS!
3、对从数据库进行相应的设置
mysql>change master to
->master_host='192.168.30.110',
->master_user='rep',
->master_password='123456',
->master_log_file='mysql-bin.000005',
->master_log_pos=107;
Query OK,0rows affected(0.06sec)
mysql>start slave;
Query OK,0rows affected(0.03sec)
执行show processlist查看进程
mysql>show processlist\G
***************************3.row***************************
Id:7
User:system user
Host:
db:NULL
Command:Connect
Time:664
State:Slave has read all relay log;waiting for the slave I/O thread to update it Info:NULL
看到以上信息，表示slave已经连接到master，并开始接受并执行日志。mysql>show slave status\G;
***************************1.row***************************
Slave_IO_State:Waiting for master to send event
Master_Host:192.168.30.110
Master_User:rep
Master_Port:3306
Connect_Retry:60
Master_Log_File:mysql-bin.000005
Read_Master_Log_Pos:267
Relay_Log_File:mysql-relay-bin.000002
Relay_Log_Pos:413
Relay_Master_Log_File:mysql-bin.000005
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:267
Relay_Log_Space:569
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:0
Master_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:1
验证是否正常工作：
mysql>show slave status\G;
Slave_IO_State:Waiting for master to send event
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
看到上面两个YES说明，IO以及SQL线程都已正常工作。
四、测试
在master上面创建一个新的表
mysql>use test;
mysql>create table user(id int);
mysql>show tables;
+----------------+
|Tables_in_test|
+----------------+
|user|
+----------------+
在slave上查看同步情况，可以看到
mysql>show tables;
+----------------+
|Tables_in_test|
+----------------+
|user|
+----------------+
到此，mysql的主从同步完成！
========================
实验可能遇到的问题
1、在slave上，使用命令show slave status看到如下报错
The slave I/O thread stops because master and slave have equal MySQL server ids;these ids must be
different for replication to work(or the--replicate-same-server-id option must be used on slave but
this does not always make sense;please check the manual before using it)
解决办法：
从上面的输出可以看到发送错误的原因是server-id重复了。
检查主从上的server-id是否唯一（注意，https://www.doczj.com/doc/4b2752390.html,f配置中默认已经有server-id=1）
2、手动执行同步的时候，查看slave status时，sql线程为no
可能因为之前已经同步，再次执行会报错，因为之前的数据库可能已经存在，再次执行创建的sql语句，则会有错
解决办法：
之前的数据库数据，可以通过锁表，备份并恢复到从数据库上，然后重新定义新的二进制日志文件名以及位置，再次开启同步即可。（即跳过之前执行过的操作）
3查看master status状态看到io线程没有正常工作，显示connecting
Slave_IO_Running:Connecting
Slave_SQL_Running:Yes
Last_IO_Error:error reconnecting to master'rep@192.168.122.224:3306'-retry-time:60retries:86400
从上面的信息可以看到IO线程连接超时，
解决办法：
(1)查看防火墙是否放行3306
(2)注意mysql修改了配置之后是否有重启，还有网络状态如何
(3)看看master主机IP地址是否写错
(4)主服务器上授权grant语句有没写错（比如登陆的IP，用户名或者密码）
4今天在做MySQL主从复制时遇到个ERROR1201(HY000):Could not initialize master info structure.
出现这个问题的原因是之前曾做过主从复制！
解决方案是：运行命令stop slave;
成功执行后继续运行reset slave;
然后进行运行GRANT命令重新设置主从复制。
具体过程如下：
Command代码
mysql>change master to master_host='127.0.0.1',master_user='user',master_pass
word='user',master_log_file='mysql-bin-000202',master_log_pos=553;
ERROR1201(HY000):Could not initialize master info structure;more error messa
ges can be found in the MySQL error log
mysql>stop slave;
Query OK,0rows affected,1warning(0.00sec)
mysql>reset slave;
Query OK,0rows affected(0.00sec)
mysql>change master to master_host='127.0.0.1',master_user='user',master_pass word='user',master_log_file='mysql-bin-000202',master_log_pos=553;
Query OK,0rows affected(0.11sec)
========================
其他
1、主从服务器同步维护
由于各种原因，导致主从数据不一致，在负载低的时候，进行手动同步.
在主服务器上执行
mysql>flush tables with read lock;
Query OK,0rows affected(0.00sec)
mysql>show master status;
+------------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
|mysql-bin.000005|355|||
+------------------+----------+--------------+------------------+
在从服务器上执行
先得到当前主服务器的二进制文件名和偏移量,执行命令使从服务器与主服务器同步mysql>select master_pos_wait('mysql-bin.000005','355');
+-------------------------------------------+
|master_pos_wait('mysql-bin.000005','355')|
+-------------------------------------------+
|0|
+-------------------------------------------+
同步完成后，回到主服务器上，解锁
mysql>unlock tables;
=====================
注意：
read-only=1这个选项对于root用户无效，root用户照样可以执行插入更新的操作。mysql>insert into test1.test values('2');
Query OK,1row affected(0.06sec)
mysql>select*from test1.test;
+------+
|id|
+------+
|1|
|2|
+------+
这个选项只对普通用户生效:
新添加一个用户并授权
mysql>grant all privileges on test1.test to'dbuser'@localhost identified by'123456';
退出mysql后，使用dbuser这个刚刚新建的用户测试
mysql>use test1;
Database changed
mysql>insert into test values('3');
ERROR1290(HY000):The MySQL server is running with the--read-only option so it cannot execute this statement 可以看到，提示mysql设置了read-only的选项，不允许插入操作。
美河学习在线https://www.doczj.com/doc/4b2752390.html, MySQL主从复制 MySQL主从复制、搭建、状态检查、中断排查及备库重做 本文档主要对MySQL主从复制进行简单的介绍，包括原理简介、搭建步骤、状态检查、同步中断及排查、备库重建。
目录 一、MySQL主从复制概述 (2) 1、主从复制简介 (2) 2、主从复制原理、机制 (2) 3、主从复制原理图 (3) 二、MySQL主从复制搭建 (4) 1、Master端配置部署 (4) 2、Slave端配置部署 (4) 3、建立主从同步 (4) 三、主从复制状态检查及异常处理 (6) 1、主从复制状态检查 (6) 2、IO_thread异常 (7) 3、sql_thread异常 (8) 4、主从复制延迟 (9)
一、MySQL主从复制概述 1、主从复制简介 MySQL主从复制就是将一个MySQL实例（Master）中的数据实时复制到另一个MySQL实例(slave)中，而且这个复制是一个异步复制的过程。 实现整个复制操作主要由三个进程完成的，其中两个进程在Slave（sql_thread和IO_thread），另外一个进程在 Master（IO进程）上。 2、主从复制原理、机制 要实施复制，首先必须打开Master端的binary log（bin-log）功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。 复制的基本过程如下： 1)、Slave上面的IO_thread连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容； 2)、Master接收到来自Slave的IO_thread的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave 的IO_thread。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log file的以及bin-log pos； 3)、Slave的IO_thread接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”； 4)、Slave的Sql_thread检测到relay-log中新增加了内容后，会马上解析relay-log 的内容成为在Master端真实执行时候的那些可执行的内容，并在本数据库中执行。
1.mysql数据同步实现原理 即读写操作在两台服务器上进行，每台服务器即主也是从。当其中的任何一台服务器收到操作请求时，其进行相应的数据变化，并把变化的数据复制到另一台服务器中。 2.配置服务器master 初始服务器 通过mysql工具连接服务器master后，新建两个数据库audit，idm。导入初始化数据库文件，完成数据库的初始化 给用户授权 从开始菜单中打开mysql5的命令行，输入正确的密码，进入mysql控制台命令行模式后，输入如下命令： #授权来自192.168.0.189的backup用户拥有对所有库的复制数据的权限，该用户的密码设为123456 GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.0.189' IDENTIFIED BY '123456'; #刷新权限设置 FLUSH PRIVILEGES ; 修改配置文件 修改主目录中的my.inf文件，在mysqld下面加入如下内容 server-id = 1 log-bin=mysql-bin binlog-do-db = audit binlog-do-db = idm binlog-ignore-db = information_schema binlog-ignore-db = mysql binlog-ignore-db = test master-host = 192.168.0.189 master-user = backup master-password = 123456 master-port = 3306
replicate-do-db = audit replicate-do-db = idm master-connect-retry = 60 3.配置服务器slave 初始服务器 通过mysql工具连接服务器ha002后，新建两个数据库audit，idm。导入初始化数据库文件，完成数据库的初始化 给用户授权 从开始菜单中打开mysql5的命令行，输入正确的密码，进入mysql控制台命令行模式后，输入如下命令： #授权来自192.168.0.188的backup用户拥有对所有库的复制数据的权限，该用户的密码设为123456 GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.0.188' IDENTIFIED BY '123456'; #刷新权限设置 FLUSH PRIVILEGES ; 修改配置文件 修改主目录中的my.inf文件，在mysqld下面加入如下内容 server-id = 2 master-host = 192.168.0.188 master-user = backup master-password = 123456 master-port = 3306 relicate-do-db = audit replicate-do-db = idm master-connect-retry = 60 log-bin=mysql-bin binlog-do-db = audit binlog-do-db = idm binlog-ignore-db = information_schema
一：MySQL复制： MySQL复制简介： 将master服务器中主数据库的ddl和dml操作通过二进制日志传到slaves服务器上，然后在master服务器上将这些日志文件重新执行，从而使得slave服务器和master服务器上的数据信息保持同步。 Mysql复制的原理： 将数据分布到多个系统上去，是通过将Mysql的某一台master主机的数据复制到其它（slave）主机上，并重新执行一遍来实现的； 复制过程中一个服务器充当master服务器，而一台或多台其它服务器充当slave服务器。master服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。 这些日志可以记录发送到slave服务器的更新。当一个slaves服务器连接master服务器时，它通知master服务器从服务器在日志中读取的最后一次成功更新的位置。slave服务器接收从那时起发生的任何更新，然后封锁并等待master服务器通知新的更新。 mysql复制的优点： 在slave服务器上执行查询操作，降低master服务器的访问压力 当master服务器上出现了问题可以切换到slave服务器上，不会造成访问中断等问题 在slave服务器上进行备份，以避免备份期间影响master服务器的服务使用及日常访问
Mysql自身的复制功能：是构建大型、高性能应用程序的基础。 mysql支持的复制类型： 基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持 混合类型的复制:：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。 MySQL复制技术的特点： 数据分布(Data distribution ) 备份(Backups) 负载平衡(load balancing) 高可用性和容错性High availability and failover 复制的工作过程： master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）； slave将master的binary log events拷贝到它的中继日志(relay log)； slave重做中继日志中的事件，将改变反映它自己的数据。
MYSQL四种备份方法总结 Mysql数据库备份主要有4种方法： 1、mysqldump 2、直接拷贝(cp、tar,gzip,cpio) 3、mysqlhotcopy 4、同步复制 mysqldump生成能够移植到其它机器的文本文件，缺省地，文件内容包含创建正在倾倒的表的CREATE语句和包含表中行数据的INSERT语句。也就是说，mysqldump产生的输出可在以后用作mysql的输入来重建数据库。mysqldump比直接拷贝要慢些。 使用直接拷贝，如果正在备份的表正被读写就容易导致表损坏，而且不建议对isam表使用直接拷贝的方法来备份，因为ISAM表只能在相似的硬件结构的机器上拷贝。 1、mysqldump备份: 使用方法：mysqldump [OPTIONS] database [tables] 输出文件的开头看起来象这样： # MySQL Dump 6.0 # # Host: localhost Database: samp_db #--------------------------------------- # Server version 3.23.2-alpha-log # # Table structure for table 'absence' # CREATE TABLE absence( student_id int(10) unsigned DEFAULT '0' NOT NULL, date date DEFAULT '0000-00-00' NOT NULL, PRIMARY KEY (student_id,date) ); # # Dumping data for table 'absence' # INSERT INTO absence VALUES (3,'1999-09-03'); INSERT INTO absence VALUES (5,'1999-09-03'); INSERT INTO absence VALUES (10,'1999-09-08'); ...... 文件剩下的部分有更多的INSERT和CREATE TABLE语句组成。例： %mysqldump samp_db >/opt/mysqldatabak/samp_db.2006-5-15 %mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999-10-02.gz #产生压缩备份 %mysqldump samp_db student score event absence >grapbook.sql #备份数据库的某些表 %mysqladmin -h https://www.doczj.com/doc/4b2752390.html, create samp_db %mysqldump samp_db | mysql -h https://www.doczj.com/doc/4b2752390.html, samp_db #直接恢复到另一个服务器上使用--add-drop-table选项告诉服务器将DROP TABLE IF EXISTS语句写入备份文件，这样当我们以后用来恢复数据库时，如果表已经存在，你
一、概述 MySQL从3.23.15版本以后提供数据库复制（replication）功能，利用该功能可以实现两个数据库同步、主从模式、互相备份模式的功能。本文档主要阐述了如何在linux系统中利用mysql的replication进行双机热备的配置。 二、环境 操作系统：Linux 2.6.23.1-42.fc8 # SMP（不安装XEN） Mysql版本：5.0.45-4.fc8 设备环境：PC（或者虚拟机）两台 三、配置 数据库同步复制功能的设置都在MySQL的配置文件中体现，MySQL的配置文件（一般是https://www.doczj.com/doc/4b2752390.html,f）：在本环境下为/etc/https://www.doczj.com/doc/4b2752390.html,f 。 3.1 设置环境： IP 的设置： A主机 IP：10.10.0.119 Mask：255.255.0.0 B主机 IP：10.10.8.112 Mask：255.255.0.0 在IP设置完成以后，需要确定两主机的防火墙确实已经关闭。可以使用命令service iptables status 查看防火墙状态。如果防火墙状态为仍在运行。使用service iptables stop 来停用防火墙。如果想启动关闭防火墙，可以使用setup 命令来禁用或定制。 最终以两台主机可以相互ping通为佳。 3.2 配置A主(master) B从(slave)模式 3.2.1 配置A 为master 1、增加一个用户同步使用的帐号： GRANT FILE ON *.* TO ‘backup’@'10.10.8.112' IDENTIFIED BY ‘1234’; GRANT REPLICATION SLAVE ON *.* TO ‘backup’@'10.10.8.112' IDENTIFIED BY ‘1234’; 赋予10.10.8.112也就是Slave 机器有File权限，只赋予Slave机器有File权限还不行，还要给它REPLICATION SLAVE的权限才可以。 2、增加一个数据库作为同步数据库： create database test; 3、创建一个表结构： create table mytest (username varchar(20),password varchar(20)); 4、修改配置文件： 修改A的/etc/https://www.doczj.com/doc/4b2752390.html,f 文件，在https://www.doczj.com/doc/4b2752390.html,f 配置项中加入下面配置：server-id = 1 #Server 标识 log-bin
前言 作为一名编程人员，对MySQL一定不会陌生，尤其是互联网行业，对MySQL的使用是比较多的。对于求职者来说，MySQL又是面试中一定会问到的重点，很多人拥有大厂梦，却因为MySQL败下阵来。实际上，MySQL并不难，今天这份最全的MySQL总结，助你向大厂“开炮”，面试不再被MySQL难倒。 注意：关于MySQL的内容整理，包括了面试题、学习笔记、使用文档以及Xmind思维图几个部分，需要高清完整版的请转发+关注，然后私信回复“666”获得免费领取方式 01、MySQL 面试题集合总结 1.1 MySQL 面试题（基础部分）： ?drop、truncate、delete区别 ?数据库三范式是什么? ?union和union all有什么不同? ?char、varchar2、varchar有什么区别？ ?合并查询有哪些？ ?SQL语句执行顺序 ?null的含义 ?MySQL、SqlServer、oracle写出字符存储、字符串转时间 ?update语句可以修改结果集中的数据吗？ ?B树和B+树的区别 ?你建过索引吗? 建索引的原则 ?索引的类型, 如主键索引 ?查看SQL执行计划
?有十万条数据, 写SQL语句查询其中某字段较大值的几条数据 ?子查询与关联查询的区别 ?MySQL InnoDB、Mysaim的特点？ ?乐观锁和悲观锁的区别？？ ?行锁和表锁的区别？ ?数据库隔离级别是什么？有什么作用？ ?MySQL主备同步的基本原理。 ?如何优化数据库性能（索引、分库分表、批量操作、分页算法、升级硬盘SSD、业务优化、主从部署） ?SQL什么情况下不会使用索引（不包含，不等于，函数） ?一般在什么字段上建索引（过滤数据最多的字段） ?MySQL，B+索引实现，行锁实现，SQL优化 ?如何解决高并发减库存问题 ?数据库事务的几种粒度 1.2 MySQL 面试题（实战部分）： ?数据库三范式，根据秒杀场景设计数据表 ?数据库的主从复制 ?死锁怎么解决 ?mysql并发情况下怎么解决（通过事务、隔离级别、锁） ?触发器的作用？ ?什么是存储过程？用什么来调用？ ?存储过程的优缺点？
mysql数据库同步跳过临时错误 slave stop; set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; slave start; 几个跟热备有关的mysql命令:(需要在mysql命令行界面或query ） stop slave #停止同步 start slave #开始同步，从日志终止的位置开始更新。 show slave status #查看同步状态 SET SQL_LOG_BIN=0|1 #主机端运行，需要super权限，用来开停日志，随意开停，会造成主机从机数据不一致，造成错误 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n # 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。 RESET MASTER #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER RESET SLAVE #从机运行，清除日志同步位置标志，并重新生成https://www.doczj.com/doc/4b2752390.html, 虽然重新生成了https://www.doczj.com/doc/4b2752390.html,,但是并不起用，最好，将从机的mysql进程重启一下，LOAD TABLE tblname FROM MASTER #从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有 reload 和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值 LOAD DATA FROM MASTER #从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值 CHANGE MASTER TO master_def_list #在线改变一些主机设置，多个用逗号间隔,比如CHANGE MASTER TO MASTER_HOST='https://www.doczj.com/doc/4b2752390.html,', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret' MASTER_POS_WAIT() #从机运行 SHOW MASTER STATUS #主机运行，看日志导出信息 SHOW SLAVE HOSTS #主机运行，看连入的从机的情况。 SHOW SLAVE STATUS (slave) SHOW MASTER LOGS (master) SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ] PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'
主从复制的原理： 分为同步复制和异步复制，实际复制架构中大部分为异步复制。 复制的基本过程如下： 1)、Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容； 2)、Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave 的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置； 3)、Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”； 4)、Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。 Mysql为了解决这个风险并提高复制的性能，将Slave端的复制改为两个进程来完成。提出这个改进方案的人是Y ahoo!的一位工程师“Jeremy Zawodny”。这样既解决了性能问题，又缩短了异步的延时时间，同时也减少了可能存在的数据丢失量。当然，即使是换成了现在这样两个线程处理以后，同样也还是存在slave 数据延时以及数据丢失的可能性的，毕竟这个复制是异步的。只要数据的更改不是在一个事物中，这些问题都是会存在的。如果要完全避免这些问题，就只能用mysql的cluster来解决了。不过mysql的cluster是内存数据库的解决方案，需要将所有数据都load到内存中，这样就对内存的要求就非常大了，对于一般的应用来说可实施性不是太大。 复制常用架构 Mysql复制环境90%以上都是一个Master带一个或者多个Slave的架构模式，主要用于读压力比较大的应用的数据库端廉价扩展解决方案。因为只要master和slave的压力不是太大（尤其是slave端压力）的话，异步复制的延时一般都很少很少。尤其是自slave端的复制方式改成两个进程处理之后，更是减小了slave端的延时。而带来的效益是，对于数据实时性要求不是特别的敏感度的应用，只需要通过廉价的pc server来扩展slave的数量，将读压力分散到多台slave的机器上面，即可解决数据库端的读压力瓶颈。这在很大程度上解决了目前很多中小型网站的数据库压力瓶颈问题，甚至有些大型网站也在使用类似方案解决数据库瓶颈。 Mysql主从复制配置过程： 环境：master: 192.168.0.3 Slave: 192.168.0.4 Mysql版本为5.0.67（编译安装） database: eric 1.Master服务器启动mysql， a)#mysql –uroot –proot b)创建一个有复制权限的用户，只限slave远程连接访问. i. mysql>grant replication slave on *.* to replication@192.168.0.4
Mysql数据库安装及生产环境下主从库同步配置
目录 1安装M YSQL数据库...................................................................................................................................... 2生产环境下M Y SQL数据库主从同步配置................................................................................................. 2.1 主数据库配置 (5) 2.2 从数据库配置 (5) 3监控服务器............................................................................................................................................... 3.1 监控主数据库服务器 (6) 3.2 监控从数据库服务器 (6)
1安装Mysql数据库 安装环境： 系统：CentOS-6.6-x86_64 数据库：MySQL-server-5.5.42-1.el6.x86_64；MySQL-client-5.5.42-1.el6.x86_64 1.SSH方式登录到MySQL服务器 2.创建存放安装文件的目录 [root@localhost /]# mkdir -p /sw/mysql55 3.上传安装文件到上一步创建的目录 4.检查是否已安装过MySQL [root@localhost /]# rpm -qa | grep -i mysql MySQL-client-5.5.42-1.el6.x86_64 MySQL-server-5.5.42-1.el6.x86_64 5.如果已安装则移除，否则请跳过此步 [root@localhost /]# yum -y remove MySQL-server-5.5.42-1.el6.x86_64 [root@localhost /]# yum -y remove MySQL-client-5.5.42-1.el6.x86_64 删除老版本mysql的开发头文件和库 rm -fr /usr/lib/mysql rm -fr /usr/include/mysql rm -fr /var/lib/mysql rm -f /etc/https://www.doczj.com/doc/4b2752390.html,f 6.安装MySQL [root@localhost /]# cd /sw/mysql55/ [root@localhost mysql55]# rpm -ivh MySQL-server-5.5.42-1.el6.x86_64.rpm Preparing... ########################################### [100%] 1:MySQL-client ########################################### [100%] [root@localhost mysql55]# rpm -ivh MySQL-client-5.5.42-1.el6.x86_64.rpm Preparing... ########################################### [100%] 1:MySQL-server ########################################### [100%] 7.配置MySQL [root@localhost mysql55]# cp /usr/share/mysql/https://www.doczj.com/doc/4b2752390.html,f /etc/https://www.doczj.com/doc/4b2752390.html,f [root@localhost mysql55]# vi /etc/https://www.doczj.com/doc/4b2752390.html,f [client] #password = your_password port = 8819 socket = /var/lib/mysql/mysql.sock default-character-set=utf8 [mysqld] port = 8819 socket = /var/lib/mysql/mysql.sock lower_case_table_names=1
Mysql 数据库主主（master-master）同步方案 一、MySQL同步概述 1.MySQL数据的复制的基本介绍 目前MySQL数据库已经占去数据库市场上很大的份额，其一是由于MySQL数据的开源性和高性能，当然还有重要的一条就是免费~不过不知道还能免费多久，不容乐观的未来，但是我们还是要能熟练掌握MySQL数据的架构和安全备份等功能，毕竟现在它还算是开源界的老大吧！ MySQL数据库支持同步复制、单向、异步复制，在复制的过程中一个服务器充当主服务，而一个或多个服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。 请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。 单向复制有利于健壮性、速度和系统管理： 健壮性：主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。
速度快：通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。SELECT查询可以发送到从服务器以降低主服务器的查询处理负荷。但修改数据的语句仍然应发送到主服务器，以便主服务器和从服务器保持同步。如果非更新查询为主，该负载均衡策略很有效，但一般是更新查询。 系统管理：使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。 2.MySQL数据复制的原理 MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。 每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。 认识到二进制日志只是一个从启用二进制日志的固定时间点开始的记录非常重要。任何设置的从服务器需要主服务器上的在主服务器上启用二进制日志时的数据库拷贝。如果启动从服务器时，其数据库与主服务器上的启动二进制日志时的状态不相同，从服务器很可能失败。 将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句。请注意LOAD DATA FROM MASTER目前只在
MySQL主从同步原理+部署 一.主从的作用： 1.可以当做一种备份方式 2.用来实现读写分离，缓解一个数据库的压力 二.MySQL主从备份原理 master 上提供binlog , slave 通过 I/O线程从 master拿取 binlog,并复制到slave的中继日志中 slave 通过 SQL线程从 slave的中继日志中读取binlog ,然后解析到slave中 部署主从环境：主服务器：192.168.1.110（编译好的MySQL5.1版本的数据库）从服务器：192.168.1.120（编译好的MySQL5.1版本的数据库） （温馨提示：主和从数据库版本必须是一样。或者主库的数据库版本必须比从库高,不然会导致很多故障的发生。） 三：生产环境应用MySQL主从同步场景： 1.一般用主库做为提供业务用户写操作（比如：在互联网上写一条微博，这时候就会 写到mysql数据库的主库中） 2.一般用从库做为提供业务用户读操作（比如：在互联网上，我想看一条微博，这时 候里面提供数据就是MySQL数据库的从库中。） (1)在主服务器（192.168.1.110）上操作。 [root@Jiechao ~]# ifconfig eth0 eth0 Link encap:Ethernet HWaddr 00:0C:29:5E:6F:A7 inet addr:192.168.1.110 Bcast:192.168.1.255 Mask:255.255.255.0 inet6 addr: fe80::20c:29ff:fe5e:6fa7/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:141354 errors:0 dropped:0 overruns:0 frame:0 TX packets:140807 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:142083379 (135.5 MiB) TX bytes:17815696 (16.9 MiB) Interrupt:193 Base address:0x2000 [root@Jiechao ~]# vi /etc/https://www.doczj.com/doc/4b2752390.html,f [mysqld]在mysqld下添加以上两行。 server-id = 1 log-bin=jiechao-bin [root@Jiechao ~]# /etc/init.d/mysqld restart Shutting down MySQL[ OK ] Starting MySQL.[ OK ]
如上图所示，整个数据层有Group1，Group2，Group3三个集群组成，这三个集群就是数据水平切分的结果，当然这三个集群也就组成了一个包含完整数据的DB。每一个Group包括1个Master（当然Master也可以是多个）和N个Slave，这些Master和Slave的数据是一致的。比如Group1中的一个slave发生了宕机现象，那么还有两个slave是可以用的，这样的模型总是不会造成某部分数据不能访问的问题，除非整个Group里的机器全部宕掉，但是考虑到这样的事情发生的概率非常小（除非是断电了，否则不易发生吧）。 在没有引入集群以前，我们的一次查询的过程大致如下：请求数据层，并传递必要的分库区分字段（通常情况下是user_id）?数据层根据区分字段Route到具体的DB?在这个确定的DB内进行数据操作。这是没有引入集群的情况，当时引入集群会是什么样子的呢？看图一即可得知，我们的路由器上规则和策略其实只能路由到具体的Group，也就是只能路由到一个虚拟的Group，这个Group并不是某个特定的物理服务器。接下来需要做的工作就是找到具体的物理的DB服务器，以进行具体的数据操作。基于这个环节的需求，我们引入了负载均衡器的概念（LB）。负载均衡器的职责就是定位到一台具体的DB服务器。具体的规则如下：负载均衡器会分析当前sql的读写特性，如果是写操作或者是要求实时性很强的操作的话，直接将查询负载分到Master，如果是读操作则通过负载均衡策略分配一个Slave。我们的负载均衡器的主要研究放向也就是负载分发策略，通常情况下负载均衡包括随机负载均衡和加权负载均衡。随机负载均衡很好理解，就是从N个Slave中随机选取一个Slave。这样的随机负载均衡是不考虑机器性能的，它默认为每台机器的性能是一样的。假如真实的情况是这样的，这样做也是无可厚非的。假如实际情况并非如此呢？每个Slave的机器物理性能和配置不一样的情况，再使用随机的不考虑性能的负载均衡，是非常不科学的，这样一来会给机器性能差的机器带来不必要的高负载，甚至带来宕机的危险，同时高性能的数据库服务器也不能充分发挥其物理性能。基于此考虑从，我们引入了加权负载均衡，也就是在我们的系统内部通过一定的接口，可以给每台DB服务器分配一个权值，然后再运行时LB根据权值在集群中的比重，分配一定比例的负载给该DB服务器。当然这样的概念的引入，无疑增大了系统的复杂性和可维护性。有得必有失，我们也没有办法逃过的。 有了分库，有了集群，有了负载均衡器，是不是就万事大吉了呢？事情远没有我们想象的那么简单。虽然有了这些东西，基本上能保证我们的数据层可以承受很大的压力，但是这样的设计并不能完全规避数据库宕机的危害。假如Group1中的slave2 宕机了，那么系统的LB并不能得知，这样的话其实是很危险的，因为LB不知道，它还会以为slave2为可用状态，所以还是会给slave2分配负载。这样一来，问题就出来了，客户端很自然的就会发生数据操作失败的错误或者异常。这样是非常不友好的！怎样解决这样的问
keepalived实现对mysql主从复制的主备自动切换使用MySQL+keepalived是一种非常好的解决方案，在MySQL-HA环境中，MySQL 互为主从关系，这样就保证了两台MySQL数据的一致性，然后用keepalived实现虚拟IP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换。 实验环境中用两台主机搭建了一个mysql主从复制的环境，两台机器分别安装了keepalived，用一个虚IP实现mysql服务器的主备自动切换功能. 模拟环境： VIP：192.168.1.197 ：虚拟IP地址 Master：192.168.1.198 ：主数据库IP地址 Slave：192.168.1.199 ：从数据库IP地址 备注：MySQL的主从同步配置不在此文档中说明（前提：主从同步已完成） 安装步骤： 1、keepalived的安装 Yum install -y keepalived Chkconfig keepalived on 2、keepalived.conf文件的配置 Master：keepalived.conf vi /etc/keepalived/keepalived.conf ! Configuration File for keepalived global_defs { notification_email { kenjin@https://www.doczj.com/doc/4b2752390.html, } notification_email_from kenjin@https://www.doczj.com/doc/4b2752390.html, smtp_connect_timeout 3 smtp_server https://www.doczj.com/doc/4b2752390.html,
MySQL 提供了数据库的同步功能，这对我们实现数据库的冗灾、备份、恢复、负载均衡等都是有极大帮助的。本文描述了常见的同步设置方法。 一、准备服务器 由于MySQL不同版本之间的(二进制日志)binlog格式可能会不一样，因此最好的搭配组合是Master的MySQL版本和Slave的版本相同或者更低，Master的版本肯定不能高于Slave版本。 本文中，我们假设主服务器(以下简称Master)和从服务器(以下简称Slave)的版本都是5.0.15，操作系统是Linux Ubuntu 5.0.x。 假设同步Master的主机名为：rep1，Slave主机名为：rep2，2个MySQL的basedir目录都是/usr/local/mysql，datadir都是：/usr/local/mysql/data。 二、设置同步服务器 1、设置同步Master 每个同步服务器都必须设定一个唯一的编号，否则同步就不能正常运行了。接下来开始修改https://www.doczj.com/doc/4b2752390.html,f，增加以下几行： server-id = 1 log-bin set-variable=binlog-ignore-db=mysql 然后在Master上增加一个账号专门用于同步，如下： mysql>GRANT REPLICATION SLAVE ON *.* TO rep@rep2 IDENTIFIED BY 'rep'; 如果想要在Slave上有权限执行"LOAD TABLE FROM MASTER"或"LOAD DATA FROM MASTER" 语句的话，必须授予全局的FILE 和SELECT 权限： mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep@rep2 IDENTIFIED BY 'rep';
实现mysql主从数据同步 当前版本：V 1.0 更新时间：2016-06-03 作者：kangmiao
一、测试服务器： 主服务（master）：Window7_64位系统，ip：10.0.7.194 数据库版本：MySQL Server 5.6 从服务器（slave）：Window Server 2008_32位系统，ip：10.0.2.29 数据库版本：MySQL Server 5.6 注：数据库5.6版本之前的配置有些不一样。 二、数据库安装（略） 二、主服务器（master）配置（单向同步） 注：测试是在数据库无数据的环境下，修改配置文件之前先备份原来的。 主服务器，一定得配置Server_id=1,从服务器的Server_id=2或3或4或5等，必须为1到232–1之间的一个正整数值，一定不能互相之间重复，主从之间不能重复，从服务器和从服务器之间，也不能重复，他们之间是通过Server_id进行身份识别的。上 1、修改MySQL配置文件my.ini（此文件一般在目录E:\ProgramData\MySQL\MySQL Server 5.6），在[mysqld]下面配置；查看是否已存在配置项，存在则修改，只能有一个； #数据库ID号，为1时表示为Master，所有master和slave的server-id必须不同； server-id=1 #启用二进制日志，选项给出了二进制日志的所有文件的基本名（master会产生很多logbin日志，比如指定了mysql-bin的基本名，则所产生的二进制日志文件的命名如: mysql-bin.000001 , mysql-bin.000002 ……） log-bin=mysql-bin #选项给出了二进制索引文件的文件名，这个索引文件保存了所有binlog文件的列表。 log-bin-index=mysql-bin.index #事务每次commit后，都会写二进制日志，这个日志最初都是在日志缓存里面的，而这个参数就是设定将缓存的日志刷到磁盘文件。多少个commit刷一次磁盘，这个参数就填多少。默认值是0,这样就将控制权交给系统，由操作系统决定 sync_binlog=1 binlog_format=mixed #双向同步时开启，将master的修改记入二进制日志，方便作为其他Slaves的Master #log-slave-updates=1 #双向同步时开启，同步是忽略错误 #slave-skip-errors=all #如果从服务器发现主服务器断掉，重新连接的时间差，在5.6版本中主从配置此项都启动报错，暂去掉。 #master-connect-retry=60 #同步的数据库，若不配置，则除不同步的数据库外，其他数据库库都同步 binlog-do-db=testmsdb
A tlas+lvs+keepalived +mysql+主从复制负载均衡搭建 部署文档 2015-8-31 （V1.0） 一、部署背景信息 基于mysql的负载均衡有很多种方式，如haproxy，前面一篇博客有介绍，还可以用更高效lvs 做负载均衡，下面是基于percona xtradb cluster的三个节点的多主复制+atlas的lvs负载均衡，其
实这里是不需要用atlas的，因为atlas是用来做连接池和读写分离的，而多主架构是不需要读写分离的（如果是基于mysql replication的就需要atlas做负载均衡了），但为了测试atlas能不能用lvs做负载均衡，就顺便做了下实验。 1.节点规划 1.1 mysql数据节点： db169 db172 db173 三个节点为xtradb cluster节点。 1.2 keepalived节点： db162 db163 虚拟ip为192.168.1.201 haproxy节点（仅为了对比lvs的性能才安装的）：db169（部署在xtradb cluster的一个节点上） 1.3 atlas节点：和xtradb cluster节点部署在一起，也为三个节点 注意：atlas和mysql要部署在一个节点上，如果不在一个节点上则不能用lvs dr模式负载均衡 1.4 客户端测试节点：db55 ip地址为192.168.1.* ,节点名为db+ip地址末位 2.安装lvs及keepavlied（db162、db163上） 2.1安装依赖包 yum -y install kernel-devel make gcc openssl-devel libnl* 下载并连接linux kernel文件，注意版本要一致（uname -a） [root@db163 ~]# ln -s /usr/src/kernels/2.6.32-358.el6.x86_64/ /usr/src/linux 安装keepalived、lvs [root@db162 ~]# yum install ipvsadm [root@db162 ~]# yum install keepalived [root@db163 ~]# yum install ipvsadm [root@db163 ~]# yum install keepalived 2.2.配置keepavlied，注意lvs不需要单独配置，在keepalived里配置就行了 [root@db162 ~]# cat /etc/keepalived/keepalived.conf ! Configuration File for keepalived global_defs { router_id MySQL_LB1 } vrrp_sync_group VSG { group {