MySQL主从复制

MySQL主从复制
一、企业网站单个MySQL问题分析
二、主从复制原理原理
三、MySQL 5.5主从复制——基于二进制日志位置的复制案例
一、企业网站单个MySQL问题分析
在企业网站中，后端MySQL数据库只有一台时，会有以下问题：单点故障，服务不可用；无法处理大量的并发数据请求；数据丢失将造成大灾难。

改造办法：
增加MySQL数据库服务器，对数据进行备份，形成主备。
确保准备MySQL数据库服务器是一样的
主服务器宕机了，备份服务器继续工作，数据有保障

MySQL主从复制与读写分离是密切相关

复制允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。默认情况下，复制是异步的，从站不需要永久连接以接收来自主站的更新。根据配置，您可以复制数据库中的所有数据库，所选数据库甚至选定的表。

MySQL中复制的优点：
·横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可能发生在一个或多个从站上。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。
·数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。
·分析 - 可以在主服务器上创建实时数据，而信息的分析可以在从服务器上进行，而不会影响主服务器的性能。
·远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。

二、主从复制工作原理
MySQL主从复制的类型：
·基于语句的复制
MySQL中的复制功能最初基于从主设备到从设备的SQL语句传播。这称为基于语句的日志记录。您可以通过启动服务器来使用此格式 --binlog-format=STATEMENT。

·基于行的复制
在基于行的日志记录中，主服务器将事件写入二进制日志，以指示各个表行的影响方式。因此，重要的是表始终使用主键来确保可以有效地识别行。您可以通过启动它来使服务器使用基于行的日志记录 --binlog-format=ROW。（5.7默认是基于行的复制）

·混合类型的复制
混合日志记录。对于混合日志记录，默认情况下使用基于语句的日志记录，但在某些情况下，日志记录模式会自动切换到基于行的日志，您可以通过使用该选项启动mysqld， 使MySQL显式使用混合日志记录--binlog-format=MIXED。

日志格式也可以由正在使用的存储引擎设置或限制，这有助于消除在使用不同存储引擎的主设备和从设备之间复制某些语句时出现的问题。
1、MySQL从服务器开启I/O线程，向主服务器请求数据同步（获取二进制日志）
2、MySQL主服务器开启I/O线程回应从服务器
3、从服务器得到主的二进制日志写入中继日志
4、从服务器开启SQL线程将日志内容执行，实现数据同步

三、MySQL 5.5主从复制——基于二进制日志位置的复制案例
实验环境：
CentOS 6.5
MySQL 5.5.23

主服务器：
MySQL-master：192.168.2.11
MySQL-slave1：:192.168.2.12
MySQL-slave2：:192.168.2.13

所有服务器关闭iptables或者进行规制设置
建立时间同步环境，在主服务器上安装配置NTP时间同步服务器
[root@msyql-m ~]# yum -y install ntp
[root@msyql-m ~]# cp -p /etc/ntp.conf /etc/ntp.conf.origin
[root@msyql-m ~]# vim /etc/ntp.conf
……
22 server 127.127.1.0 //手动添加此两行内容
23 fudge 127.127.1.0 startum 8
……
[root@msyql-m ~]# /etc/init.d/ntpd start
正在启动 ntpd： [确定]
[root@msyql-m ~]# chkconfig ntpd on
在从服务器上同步时间
[root@mysql-s1 ~]# yum -y install ntpdate
[root@mysql-s1 ~]# ntpdate 192.168.2.11
12 May 22:20:58 ntpdate[19385]: step time server 192.168.2.11 offset -8013.591633 sec
slave2服务器与slave1相同

注意，主从MySQL尽量保证版本相同

配置MySQL Master主服务器
[root@mysql-m ~]# vim /etc/my.cnf
26 [mysqld]
49 log-bin=mysql-bin
50 log-slave-updates=true //手动添加，开启从日志
58 server-id = 11
[root@mysql-m ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL… SUCCESS!

给从服务器授权
[root@mysql-m ~]# mysql -uroot -p123123
mysql> grant replication slave on . to ‘myslave’@‘192.168.2.%’ identified by ‘123123’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

配置从服务器
[root@msyql-s1 ~]# vim /etc/my.cnf
26 [mysqld]
50 relay-log=relay-log-bin
51 relay-log-index=slave-relay-bin.index
59 server-id = 12 //主从服务器的server-id不能相同！
[root@msyql-s1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL… SUCCESS!
[root@msyql-s1 ~]# mysql -uroot -p123123
mysql> change master to master_host=‘192.168.2.11’,master_user=‘myslave’,master_password=‘123123’,master_log_file=‘mysql-bin.000002’,master_log_pos=337;
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.2.11
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 337
Relay_Log_File: relay-log-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
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: 337
Relay_Log_Space: 107
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: NULL
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: 0
1 row in set (0.00 sec)

ERROR:
No query specified
通过查看slave状态，确保Slave_IO_Running: Yes Slave_SQL_Running: Yes

slave2配置相同，略
测试：
在MySQL主服务器创建db_test数据库
mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| db_test |
| mysql |
| performance_schema |
| test |
±-------------------+
5 rows in set (0.00 sec)
分别在两台在从服务器查看是否数据同步
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| db_test |
| mysql |
| performance_schema |
| test |
±-------------------+
5 rows in set (0.03 sec)

若在从服务器start slave;之后发现Slave_IO_Running: No、Slave_SQL_Running: Yes，则需要先stop slave; 重新change master to …; 再start slave;