MySQL数据库企业级应用实践(主从复制）
链接：https://pan.baidu.com/s/1ANGg3Kd_28BzQrA5ya17fQ
提取码：ekpy
复制这段内容后打开百度网盘手机App，操作更方便哦
1.MySQL主从复制简介
MySQL数据库的主从复制方案，与使用scp/rsync等命令进行的文件级别复制类似，都是数据的远程传输，只不过MySQL的主从复制是其自带的功能，无需借助第三方工具，而且，MySQL的主从复制并不是数据库磁盘上的文件直接拷贝，而是通过逻辑的
binlog日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的SQL语句，重新应用到MySQL数据库中。
2.概述
- MySQL数据库支持单向，双向，链式级联，环状等不同业务场景的复制。在复制过程中，一台服务器充当主服务器（Master），接收来自用户的内容更新，而一个或多个其他的服务器充当从服务器（Slave），接收来自主服务器binlog文件的日志内容，解析出SQL，重新更新到从服务器，使得主从服务器数据达到一致。
- 如果设置了链式级联复制，那么，从服务器（Slave）本身除了充当从服务器外，也会同时充当其下面从服务器的主服务器。链式级联复制类似A-->B-->C的复制形式。
下图为单向主从复制架构逻辑图，此架构只能在Master端进行数据写入
下图为双向主主复制逻辑架构图，此架构可以在Master1端或Master2端进行数据写入，或者两端同时写入数据（需要特殊设置）
下图为线性级联单向双主复制逻辑架构图，此架构只能在Master1端进行数据写入，工作场景中，Master1和master2作为主主互备，Slave1作为从库，中间的Master2需要做特殊的设置。
下图为环状级联单向多主同步逻辑架构图，任意一个点都可以写入数据，此架构比较复杂，属于极端环境下的“成品”，一般场景慎用
3.小结
在当前的生产工作中，MySQL主从复制都是异步的复制方式，既不是严格实时的数据同步，但是正常情况下给用户的体验是真实的。
4.MySQL主从复制的企业应用场景
4.1从服务器作为主服务器的实时数据备份
- 主从服务器架构的设置可以大大加强MySQL数据库架构的健壮性。例如：当主服务器出现问题时，我们可以人工或设置自动切换到从服务器继续提供服务，此时从服务器的数据与宕机时的主数据库几乎是一致的。
- 这类似NFS存储数据通过
inotify + rsync同步到备份的NFS服务器，只不过MySQL的复制方案是其自带的工具。- 利用MySQL的复制功能进行数据备份时，在硬件故障，软件故障的场景下，该数据备份是有效的，但对于人为地执行drop，delete等语句删除数据的情况，从库的备份功能就没用了，因为从服务器也会执行删除的语句。
4.2主从服务器实现读写分离，从服务器实现负载均衡
- 主从服务器架构可通过程序（PHP，java等）或代理软件（mysql-proxy，Amoeba）实现对用户（客户端）的请求读写分离，即让从服务器仅仅处理用户的select查询请求，降低用户查询响应时间，以及同时读写在主服务器上带来的访问压力。对于更新的数据（例如：update，insert，delete语句），则仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。
- 百度，淘宝，新浪等绝大多数的网站都是用户浏览页面多于用户发布内容，因此通过在从服务器上接收只读请求，就可以很好地减轻主库的读压力，且从服务器可以很容易地扩展为多台，使用LVS做负载均衡效果就非常棒了，这就是传说中的数据库读写分离架构。逻辑架构图如下所示：
4.3把多个从服务器根据业务重要性进行拆分访问
可以把几个不同的从服务器，根据公司的业务进行拆分。例如：有为外部用户提供查询服务的从服务器，有内部DBA用来数据备份的从服务器，还有为公司内部人员提供访问的后台，脚本，日志分析及供开发人员查询使用的从服务器。这样的拆分除了减轻主服务器的压力外，还可以使数据库对外部用户浏览，内部用户业务处理及DBA人员的备份等互不影响。
5.实现MySQL主从读写分离的方案
5.1通过程序实现读写分离（性能和效率最佳，推荐）
PHP和Java程序都可以通过设置多个连接文件轻松地实现对数据库的读写分离，即当语句关键字为select时，就去连接读库的连接文件，若为update，insert，delete时，则连接写库的连接文件。
通过程序实现读写分离的缺点就是需要开发人员对程序进行改造，使其对下层不透明，但这种方式更容易开发和实现，适合互联网业务场景。根据业务重要性拆分从库方案
5.2通过开源的软件实现读写分离
MySQL-proxy，Amoeba等代理软件也可以实现读写分离功能，这些软件的稳定性和功能一般，不建议生产使用。绝大多数公司常用的还是在应用端发程序实现读写分离。
5.3大型门户独立开发DAL层综合软件
百度，阿里等大型门户都有开发牛人，会花大力气开发适合自己业务的读写分离，负载均衡，监控报警，自动扩容，自动收缩等一系列功能的DAL层软件。
6.MySQL主从复制原理过程详细描述
6.1简单描述MySQL Replication的复制原理过程
- 在Slave服务器上执
行startslave命令开启主从复制开关，开始进行主从复制- 此时，Slave服务器的I/O线程会通过在Master上已经授权的复制用户权限请求连接Master服务器，并请求从指定binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行
change master命令指定的）之后开始发送binlog日志内容。- Master服务器接收到来自Slave服务器的I/O线程的请求后，其上负责复制的I/O线程会根据Slave服务器的I/O线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的I/O线程。返回的信息中除了binlog日志内容外，还有在Master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。
- 当Slave服务器的I/O线程获取到Master服务器上I/O线程发送的日志内容，日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的
Relay Log（即中继日志）文件（MySQL-relay-bin.xxxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取Master端新binlog日志时能够告诉Master服务器从新binlog日志的指定文件及位置开始请求新的binlog日志内容。- Slave服务器端的SQL线程会实时检测本地Relay Log中I/O线程新增加的日志内容，然后及时地把Relay Log文件中的内容解析成SQL语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这些SQL语句，并在
relay-log.info中记录当前应用中继日志的文件名及位置点。
6.2MySQL Replication的复制原理逻辑图
6.3针对MySQL主从复制原理的重点进行小结
- 主从复制是异步的逻辑的SQL语句级的复制
- 复制时，主库有一个I/O线程，从库有两个线程，即I/O和SQL线程
- 实现主从复制的必要条件是主库要开启记录binlog功能
- 作为复制的所有MySQL节点的server-id都不能相同。
- binlog文件只记录对数据库有更改的SQL语句（来自主数据库内容的变更），不记录任何查询（如select，show）语句。
7.MySQL主从复制实践
7.1主从复制数据库实战环境准备
[root@yangwenbo /]# ss -antup | grep 330
tcp LISTEN 0 128 *:3307 *:* users:(("mysqld",2576,11))
tcp LISTEN 0 128 *:3306 *:* users:(("mysqld",4043,11))7.2修改主库的配置文件（3306为主，3307为从）
[root@yangwenbo /]# vim /data/3306/my.cnf
4 [mysqld] #下面两个参数必须放在[mysqld]模块下，否则会出错
53 server-id = 1 #用于同步的每台机器或实例server-id都不能相同
54 log-bin=mysql-bin #binlog日志的位置
[root@yangwenbo /]# vim /data/3307/my.cnf
4 [mysqld]
53 server-id = 5 #不能与主库相同（可以为任意数值）7.3重启主库MySQL服务
[root@yangwenbo /]# /data/3306/mysql restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL....7.4登陆3306主库，检查参数的更改情况
[root@yangwenbo /]# mysql -uroot -p971108 -S /data/3306/mysql.sock #登陆3306实例
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'server_id'; #查看MySQL的系统变量（like类似于grep过滤）
+---------------+-------+
| Variable_name | Value |
+---------------+-------+ #配置的server_id为1
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+ #binlog功能已开启
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
[root@yangwenbo ~]# ls /data/3306/data #这样，binlog功能就开启了
ibdata1 ib_logfile1 mysql mysql-bin.index test
ib_logfile0 ib_logfile2 mysql-bin.000001 performance_schema7.5登陆3306主库，建立用于主从复制的账号
mysql> grant replication slave on *.* to 'yunjisuan'@'192.168.%' identified by '971108';
Query OK, 0 rows affected (0.00 sec)
#语句说明：
1）replication slave为mysql同步的必须权限，此处不要授权all权限
2）*.* 表示所有库所有表，也可以指定具体的库和表进行复制。例如yunjisuan.test中，yunjisuan为库名，test为表名
3）'yunjisuan'@'192.168.0.%' yunjisuan为同步账号。192.168.%为授权主机网段，使用了%表示允许整个192.168.0.0网段可以用yunjisuan这个用户访问数据库
4）identified by '971108'; 971108为密码，实际环境下设置的复杂些为好7.6创建完账号并授权后，需要刷新权限，使授权的权限生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)7.7检查主库创建的yunjisuan复制账号命令及结果
mysql> select user,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| root | 127.0.0.1 |
| yunjisuan | 192.168.% | #出现这行表示复制账号已经配置好了
| root | ::1 |
| | localhost |
| root | localhost |
| | yangwenbo |
| root | yangwenbo |
+-----------+-----------+
7 rows in set (0.00 sec)
#说明：MySQL里的授权用户是以数据表格的形式存储在mysql这个库的user表里。mysql> show grants for yunjisuan@'192.168.%';
+-----------------------------------------------------------------------------------------------+
| Grants for yunjisuan@192.168.% |
+-----------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'yunjisuan'@'192.168.%' IDENTIFIED BY PASSWORD '*2E086B4AB841306370F090F3973AC88BDAA569D3' |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)注意：设置主从复制之前要注意两台机器内容一致（可以通过备份的方式以及Rsync来实现）
7.8登陆3307从库，配置复制参数
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.0.102', #这里是主库的IP
MASTER_PORT=3306, #这里是主库的端口，从库端口可以和主库不同
MASTER_USER='yunjisuan'; #这里是主库上建立的用于复制的用户yunjisuan
MASTER_PASSWORD='971108', #这里是yunjisuan用户的密码
MASTER_LOG_FILE='mysql-bin.000001', #这里是show master status时查看到的二进制日志文件名称
MASTER_LOG_POS=962; #这里是show master status时查看到的二进制日志偏移量
Query OK, 0 rows affected (0.02 sec)注意：上方的
962是参照主库的位置
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 962 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)7.9从库查看授权结果
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.102
Master_User: yunjisuan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 962
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
#中间省略。。。
Seconds_Behind_Master: 0
#以下省略。。。7.10从库激活主从复制
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)7.11从库查看主从复制状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.102
Master_User: yunjisuan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 962
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes #只要这里两个是`Yes`，
Slave_SQL_Running: Yes 就说明主从复制状态正常
#中间省略。。。
Seconds_Behind_Master: 0 #0表示已经同步状态
#以下省略。。。7.12主从同步是否成功，最关键的为下面的3项状态参数：
[root@yangwenbo ~]# mysql -uroot -p971108 -S /data/3307/mysql.sock -e "show slave status\G" | egrep "IO_Running|SQL_Running|Seconds_Behind_Master"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Slave_IO_Running:Yes,这个时I/O线程状态，I/O线程负责从从库到主库读取binlog日志，并写入从库的中继日志，状态为Yes表示I/O线程工作正常。Slave_SQL_Running:Yes,这个是SQL线程状态，SQL线程负责读取中继日志（relay-log）中的数据并转换为SQL语句应用到从数据库中，状态为Yes表示I/O线程工作正常。Seconds_Behind_Master:0,这个是复制过程中从库比主库延迟的秒数，这个参数极度重要，但我们可以更准确地判断主从延迟的方法为：在主库写时间戳，然后从库读取时间戳，和当前数据库时间进行比较，从而认定是否延迟。
7.13检验主从复制是否成功
7.13.1已知查看主库下信息如下
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)7.13.2已知查看从库下信息如下
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)从以上信息来看，主从数据一致
7.13.3为检验成果，在主库上创建一个小库
mysql> create database yangwenbo;
Query OK, 1 row affected (0.08 sec)7.13.4检验当下主库内数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yangwenbo |
+--------------------+
5 rows in set (0.00 sec)7.13.5检验当下从库内数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yangwenbo |
+--------------------+
5 rows in set (0.00 sec)以上信息数据表明，主从复制成功
7.14重置主从复制记录
mysql> reset slave all; #重置主从复制记录8. MySQL主从复制配置完整步骤小结
- 准备两台数据库环境或单台多实例环境，确定能正常启动和登陆
- 配置
my.cnf文件：主库配置log-bin和server-id参数；从库配置server-id，该值不能和主库及其他从库一样，一般不开启从库log-bin功能。注意，配置参数后要重启才能生效。- 登陆主库，增加从库连接主库同步的账户，例如：yunjisuan，并授权
replication slave同步的权限。- 登陆主库，整库锁表
flush table with readlock（窗口关闭后即失效，超时参数设置的时间到了，锁表也失效），然后show master status查看binlog的位置状态。- 新开窗口，在Linux命令行备份导出原有的数据库数据，并拷贝到从库所在的服务器目录。如果数据库数据量很大，并且允许停机，可以停机打包，而不用
mysqldump。- 导出主库数据后，执行
unlock tables解锁主库。- 把主库导出的数据恢复到从库
- 根据主库的
show master status查看到的binlog的位置状态，在从库执行change master to....语句。- 从库开启复制开关，即执行
start slave；。- 从库
show slave status\G,检查同步状态，并在主库进行更新测试。
9.MySQL主从复制线程状态说明及用途
9.1MySQL主从复制主库I/O线程状态说明
9.1.1登陆主数据库查看MySQL线程的同步状态
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 1
User: yunjisuan
Host: 192.168.0.102:33254
db: NULL
Command: Binlog Dump
Time: 949
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 2. row ***************************
Id: 2
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
2 rows in set (0.00 sec)
ERROR:
No query specified
#提示：上述状态的意思是线程已经从binlog日志读取所有更新，并已经发送到了从数据库服务器。线程目前为空闲状态，等待由主服务器上二进制日志中的新事件更新。下图中列出了主服务器binlog Dump线程中State列的最常见状态。如果你没有在主服务器上看见任何binlog Dump线程，则说明复制没有运行，二进制binlog日志由各种事件组成，事件通常会为更新添加信息。
9.1.2登陆从数据库查看MySQL线程工作状态
从库有两个线程，即I/O和SQL线程。从库I/O线程的状态如下：
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1181
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1211
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 3
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
3 rows in set (0.00 sec)
ERROR:
No query specified下图列出了从服务器的I/O线程的State列的最常见的状态。该状态也出现在Slave_IO_State列，由SHOW SLAVE STATUS显示。
下图列出了从服务器的SQL线程的State列的最常见状态
9.2查看MySQL线程同步状态的用途
- 通过MySQL线程同步状态可以看到同步是否正常进行，故障的位置是什么，另外还可查看数据库同步是否完成，可用于主库宕机切换数据库或人工数据库主从切换迁移等。
- 例如：主库宕机，要选择最快的从库将其提升为主库，就需要查看主从库的线程状态，如果主从复制在正常情况下进行角色切换，也需要查看主从库的线程状态，根据复制状态确定更新是否完成。
10.模拟MySQL从库停止复制故障案例（先在从库创建一个库，然后去主库创建同名的库来模拟数据冲突）
10.1已知主库内信息数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)10.2已知从库内信息数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yangwenbo |
+--------------------+
5 rows in set (0.00 sec)已知从库比主库多了个小库
yangwenbo
10.3这时主库创建小库yangwenbo
mysql> create database yangwenbo;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yangwenbo |
+--------------------+
5 rows in set (0.01 sec)10.4这时查看从库信息数据无任何变化
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yangwenbo |
+--------------------+
5 rows in set (0.00 sec)10.5从库查看主从复制状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.102
Master_User: yunjisuan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 291
Relay_Log_File: relay-bin.000011
Relay_Log_Pos: 301
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1007
Last_Error: Error 'Can't create database 'yangwenbo'; database exists' on query. Default database: 'yangwenbo'. Query: 'create database yangwenbo'
Skip_Counter: 0
Exec_Master_Log_Pos: 198
Relay_Log_Space: 690
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: 1007
Last_SQL_Error: Error 'Can't create database 'yangwenbo'; database exists' on query. Default database: 'yangwenbo'. Query: 'create database yangwenbo'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR:
No query specified10.6对于该冲突，解决办法如下
方法一：
（1）直接将从库的yangwenbo小库删掉（当然如果里面有重要数据，可以先进行备份，找时间再恢复）
mysql> stop slave; #首先要停库；
Query OK, 0 rows affected (0.01 sec)
mysql> drop database yangwenbo; #再者删掉这个小库
Query OK, 0 rows affected (0.00 sec)
mysql> start slave; #开启库
Query OK, 0 rows affected (0.01 sec)（2）查看从库主从复制状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.102
Master_User: yunjisuan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 291
Relay_Log_File: relay-bin.000012
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#中间省略。。。
Seconds_Behind_Master: 0 #0表示已经同步状态
#以下省略。。。提示：
set global sql_slave_skip_counter=n; #n取值>0,忽略执行N个更新。（3）查看主库内信息数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yangwenbo |
+--------------------+
5 rows in set (0.00 sec)（4）查看主库内信息数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yangwenbo |
+--------------------+
5 rows in set (0.00 sec)主从信息数据已恢复已知，证明已成功
方法二：
（1）关闭从同步，调动sql_slave指针
mysql> stop slave; #首先要停库；
Query OK, 0 rows affected (0.00 sec)
mysql> set global sql_slave_skip_counter=1; #将sql线程同步指针向下移动一个，如果多次不同步，可以重复操作
Query OK, 0 rows affected (0.00 sec)
mysql> start slave; #开启库
Query OK, 0 rows affected (0.00 sec)（2）查看从库主从复制状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.102
Master_User: yunjisuan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 291
Relay_Log_File: relay-bin.000012
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#中间省略。。。
Seconds_Behind_Master: 0 #0表示已经同步状态
#以下省略。。。提示：
set global sql_slave_skip_counter=n; #n取值>0,忽略执行N个更新。（3）查看主库内信息数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yangwenbo |
+--------------------+
5 rows in set (0.00 sec)（4）查看主库内信息数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yangwenbo |
+--------------------+
5 rows in set (0.00 sec)主从信息数据已恢复已知，证明已成功
10.7其他可能引起复制故障的原因：
- MySQL自身的原因及人为重复插入数据。
- 不同的数据库版本会引起不同步，低版本到高版本可以，但是高版本不能往低版本同步。
- MySQL的运行错误或程序bug
- binlog记录模式，例如：row level模式就比默认的语句模式要好。
11.让MySQL从库记录binlog日志的方法
- 从库需要记录binlog的应用场景：当前的从库还要作为其他从库的主库，例如级联复制或双主互为主从场景的情况下。下面介绍从库记录binlog日志的方法。
- 在从库的my.cnf中加入如下参数，然后重启服务生效即可。
54
55 log-slave=updates #必须要有这个参数
56 log-bin = /data/3307/mysql-bin
57 expire_logs_days = 7 #相当于find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 | xargs rm -f
58[root@yangwenbo ~]# /data/3307/mysql restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL....12. MySQL主从复制集群架构的数据备份策略
- 有主从复制了，还需要做定时全量加增量备份么？答案是肯定的！
因为，如果主库有语句级误操作（例如：drop database yunjisuan；），从库也会执行drop database yunjisuan；，这样MySQL主从库就都删除了该数据。- 把从库作为数据库备份服务器时，备份策略如下：高并发业务场景备份时，可以选择在一台从库上备份（Slave5），把从库作为数据库备份服务器时需要在从库开启binlog功能，其逻辑图如下所示
步骤如下：
- 选择一个不对外提供服务的从库，这样可以确保和主库更新最接近，专门用于做数据备份。
- 开启从库的binlog功能
备份时可以选择只停止SQL线程，停止应用SQL语句到数据库，I/O线程保留工作状态，执行命令为stop slave sql_thread;,备份方式可以采取mysqldump逻辑备份或直接物理备份，例如：使用cp，tar（针对/data目录）工具或xtrabackup（第三方的物理备份软件）进行备份，则逻辑备份和物理备份的选择，一般是根据总的备份数据量的多少进行选择的，数据量低于30G，建议选择mysqldump逻辑备份方法，安全稳定，最后把全备和binlog数据发送到备份服务器上留存。
13.MySQL主从复制延迟问题的原因及解决方案
13.1主库的从库太多，导致复制延迟
从库数量以3~5个为宜，要复制的从节点数量过多，会导致复制延迟。
13.2从库硬件比主库差，导致复制延迟。
查看Master和Slave的系统配置，可能会因为机器配置不当，包括磁盘I/O，CPU，内存等各方面因素造成复制的延迟。这一般发生在高并发大数据量写入场景中。
13.3慢SQL语句太多
假如一条SQL语句执行时间是20秒，那么从执行完毕到从库上能查到数据至少需要20秒，这样就延迟20秒了。
一般要把SQL语句的优化作为常规工作，不断的进行监控和优化，如果单个SQL的写入时间长，可以修改后分多次写入。通过查看慢查询日志或show full processlist命令，找出执行时间长的查询语句或大的事务。
13.4主从复制的设计问题
例如，主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。
更高版本的MySQL可以支持多线程复制，门户网站则会自己开发多线程同步功能。
13.5主从库之间的网络延迟
主从库的网卡，网线，连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟，另外，跨公网主从复制很容易导致主从复制延迟。
13.6主库读写压力大，导致复制延迟。
主库硬件要搞好一点，架构的前端要加buffer及缓存层。
14.通过read-only参数让从库只读访问
read-only参数选项可以让从服务器只允许来自从服务器线程或具有SUPER权限的数据库用户进行更新，确保从服务器不接受来自用户端的非法用户更新。
14.1read-only参数允许数据库更新的条件为：
- 具有SUPER权限的用户可以更新，不受read-only参数影响，例如：管理员root。
- 来自从服务器线程可以更新，不受read-only参数影响，例如：前文的yunjisuan用户。
- 再生产环境中，可以在从库Slave中使用read-only参数，确保从库数据不被非法更新。
14.2read-only参数的配置方法如下：
方法一：直接带--read-only参数启动或重启数据库，
使用
killall mysqld或mysqladmin -uroot -p123123 -S /data/3307/mysql.sock shutdown mysqld_safe --defaults-file=/data/3307/my.cnf --read-only &
方法二：在my.cnf里[mysqld]模块下加read-only参数重启数据库，配置如下：
[mysqld]
read-only15.如何实现对主数据库锁表只读
- 前言：假设某公司有一主库，一从库（主从复制），由于从库1压力过大，公司决定再搭建一个从库2，写出具体流程
- 答：可以先把主库设为只读状态，打包备份到从库2，使主从数据一致，这时主库再手动解除只读
15.1对主数据库锁表只读（当前窗口不要关掉）
mysql> flush table with read lock; #对主数据库锁表只读
Query OK, 0 rows affected (0.00 sec)
mysql> create database yunjisuan; #已无法写入
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock在引擎不同的情况下，这个锁表命令的时间会受下面参数的控制。锁表时，如果超过设置时间不操作会自动解锁。
15.2默认情况下自动解锁的时长参数值如下：
mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 120 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 | #自动解锁时间受本参数影响
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 | #自动解锁时间受本参数影响
+----------------------------+----------+
10 rows in set (0.00 sec)15.3锁表后查看主库状态。可通过当前binlog日志文件名和二进制binlog日志偏移量来查看
注意，
show master status;命令显示的信息要记录在案，后面的从库导入全备后，继续和主库复制时就是要从这个位置开始。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 475 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)15.4锁表后，一定要单开一个新的SSH窗口，导出数据库的所有数据，如果数据量很大（50GB以上），并且允许停机，可以停库直接打包数据文件进行迁移，那样更快
[root@yangwenbo /]# mkdir beifen #可以临时创建一个目录
[root@yangwenbo /]# mysqldump -uroot -p971108 -S /data/3306/mysql.sock --events -A -B | gzip >/beifen/mysql_bak.$(date +%F).sql.gz
#注意：-A表示备份所有库；-B表示增加use DB和 drop 等（导库时会直接覆盖原有的）[root@localhost ~]# ll /server/backup/mysql_bak.2017-07-21.sql.gz
-rw-r--r--. 1 root root 137344 Jul 21 10:17 /server/backup/mysql_bak.2017-07-21.sql.gz15.5为了确保导出数据期间，数据库没有数据插入，导库完毕可以再次检查主库状态信息，结果如下：
[root@yangwenbo /]# mysql -uroot -p971108 -S /data/3306/mysql.sock -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 475 | | |
+------------------+----------+--------------+------------------+
#提示：若无特殊情况，binlog文件及位置点和锁表后导出数据前是一致的，即没有变化。15.6解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
#导出数据完毕后，解锁主库，恢复可写，命令如下.因为主库还要对外提供服务，不能一直锁定不让用户访问15.7把主库导出的MySQL备份数据包迁移到从库（使其保证主从一致），再搭建主从复制，就OK了！
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
