mysql主从复制

MySQL主从复制


1、原理：


 复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置，从服务器接收从那时起发生的任何更新，然后封锁等等主服务器通知新的更新。
2、复制类型：


a、基于语句的复制。 在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对服务器上的表所进行的更新之间的冲突，配置：binlog_format = ‘STATEMENT’ 
b、基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持，配置：binlog_format = ‘ROW’ 
c、混合类型的复制。默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制,配置：binlog_format = ‘MIXED’


3、MySQL主从复制解决的问题：


  负载均衡
  容错性和高可用性
  数据分布
4、主从复制的实现


  a、master将改变存储于二进制文件中
  b、salve将master中的二进制文件储存在中继日志中
  c、slave重做中继日志并相应作出改变
5、主从复制的原则：


每个slave只能有一个master
每个slave只能有唯一一个ID
每个master可以有很多个slave
6、一主多从的架构


当主机负担过重时，可以设置多个从MySQL，并通过负载均衡的方法使得访问主机的资源可以合理的分配到不同的从MySQL，如果主机宕机，则其中的从MySQL就会转化为主MySQL。
当 Slave 增加到一定数量时，Slave 对 Master 的负载以及网络带宽 都会成为一个严重的问题。
不同的 Slave 扮演不同的作用(例如使用不同的索引，或者不同的存储引擎)
用一个 Slave 作为备用 Master，只进行复制
用一个远程的 Slave，用于灾难恢复。
8、多级复制架构


一主多从的架构能够解决大部分读请求压力特别大的场景需求，但主库的I/O压力和网络压力会随着从库的增加而增长，而使用多级复制架构就可以解决一主多从场景下，主库额外的I/O和网络压力。 但要注意的是，多级复制场景下主库的数据是经历两次才到达读取的从库，期间的延时比一主多从复制场景下只经历一次复制的要大。
双主复制/Dual Master架构


场景：双主/Dual Master架构适用于写压力比较大的场景，或者DBA做维护需要主从切换的场景，通过双主/Dual master架构避免了重复搭建从库的麻烦。
主从复制配置


1、修改主数据库的的配置文件：
 [mysqld]
 server-id=1
 log-bin=mysqlmaster-bin.log
 sync_binlog=1
 注意：下面这个参数需要修改为服务器内存的70%左    innodb_buffer_pool_size= 512M
innodb_flush_log_at_trx_commit=1
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
 lower_case_table_names=1
 log_bin_trust_function_creators=1
 修改之后要重启mysql：
  /etc/init.d/mysql restart


2 、修改从数据库的的配置文件（server-id配置为大于1的数字即可）：
[mysqld]
server-id=2
log-bin=mysqlslave-bin.log
sync_binlog=1


 注意：下面这个参数需要修改为服务器内存的70%左右
 innodb_buffer_pool_size= 512M
 innodb_flush_log_at_trx_commit=1     sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
 lower_case_table_names=1
log_bin_trust_function_creators=1
修改之后要重启mysql：
/etc/init.d/mysql restart


3、SSH登录到主数据库：
 （1）在主数据库上创建用于主从复制的账户（192.168.100.3换成你的从数据库IP）：
 mysql -uroot -p
 mysql>
 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.3' IDENTIFIED BY 'repl';
（2）主数据库锁表（禁止再插入数据以获取主数据库的的二进制日志坐标）：
 <mysql>
 FLUSH TABLES WITH READ LOCK;
（3）然后克隆一个SSH会话窗口，在这个窗口打开MySQL命令行：
 mysql -uroot -p
 <mysql>
  SHOW MASTER STATUS;
3


+————————+———-+————–+——————+——————-+ 
4


| 
File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
5


+————————+———-+————–+——————+——————-+ 
6


| 
mysqlmaster-bin.000001 | 332 | | | | 
7


+————————+———-+————–+——————+——————-+ 
8


row in set (0.00 sec)


mysql> 
exit; 
在这个例子中，二进制日志文件是mysqlmaster-bin.000001，位置是332，记录下这两个值，稍后要用到。 
（4）在主数据库上使用mysqldump命令创建一个数据快照： 
mysqldump 
-uroot -p -h127.0.0.1 -P3306 –all-databases –triggers –routines –events >all.sql 
2


# 
接下来会提示你输入mysql数据库的root密码，输入完成后，如果当前数据库不大，很快就能导出完成。 
（5）解锁第（2）步主数据的锁表操作： 
mysql> 
UNLOCK TABLES; 
7、SSH登录到从数据库： 
（1）通过FTP、SFTP或其他方式，将上一步备份的主数据库快照all.sql上传到从数据库某个路径，例如我放在了/home/yimiju/目录下； 
（2）从导入主的快照： 
cd /home/yimiju 
mysql -uroot -p -h127.0.0.1 -P3306 < all.sql 
接下来会提示你输入mysql数据库的root密码，输入完成后，如果当前数据库不大，很快就能导入完成。 
（3）给从数据库设置复制的主数据库信息（注意修改MASTER_LOG_FILE和MASTER_LOG_POS的值）： 
mysql -uroot -p 
mysql> 
CHANGE MASTER TO MASTER_HOST=’192.168.100.2’,MASTER_USER=’repl’,MASTER_PASSWORD=’repl’,MASTER_LOG_FILE=’mysqlmaster-bin.000001’,MASTER_LOG_POS=332; 
然后启动从数据库的复制线程： 
mysql> 
START slave; 
接着查询数据库的slave状态： 
mysql> 
SHOW slave STATUS \G 
如果下面两个参数都是Yes，则说明主从配置成功 
Slave_IO_Running: 
Yes 
Slave_SQL_Running: 
Yes 
（4）接下来你可以在主数据库上创建数据库、表、插入数据，然后看从数据库是否同步了这些操作。