Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

最新推荐文章于 2021-02-03 14:27:31 发布

weixin_34190136

最新推荐文章于 2021-02-03 14:27:31 发布

阅读量186

点赞数

文章标签： php java 操作系统

原文链接：http://blog.51cto.com/10642812/2069182

版权

1.普通文件数据同步

（1）NFS网络文件共享可以同步存储数据。

（2）Samba共享数据：https://blog.51cto.com/taokey/1203553。

（3）定时任务或守护进程结合。

（4）Inotify+rsync触发式实时数据同步。

（5）ftp数据同步。http://down.51cto.com/data/1094997。

（6）ssh key+scp/rsync。

（7）svn版本管理。

（8）rsync，sersync，inotify，union(双向同步)，csync(多向同步)。

2.linux运维场景数据同步方案

2.1 scp，NFS，sftp，http，samba，rsync，csync，union

思想：

（1）文件级别也可以利用mysql，mongodb等软件。

（2）两个服务器同时写数据，双写就是一个同步机制。

2.2 文件系统级别同步

drdb（基于文件系统同步网络RAID1），同步几乎任何业务数据。

mysql数据库的官方推荐drdb同步数据，所有单点服务例如:NFS，MFS(DRBD)等都可以用drdb。

2.3 数据库同步方案

(1)自动同步机制：

Mysql replcation，mysql 主从复制（逻辑的SQL从写）。

Oracle dataguard(物理的磁盘块，逻辑的sql语句从写)。

(2)第三方drdb

https://blog.51cto.com/oldboy/1240412。

3. MySQL主从复制

3.1 mysql主从复制介绍

MySQL支持单向，双向，链式级联，实时、异步同步，在复制过程中一台服务器充当主服务器（master），而一个或多个其他的服务器充当从服务器（slave）。

复制可以是单向：M ==》S，也可以是双向M《==》M，当然也可以多M环状同步等。

如果设置了链式级联复制，那么从（slave）服务器本身除了充当从服务器外，也会同时充当其下面从服务器的主服务器。

链式级联复制类似A->B->C->D的复制形式。下面是主从复制的几种方式：

(1)单向主从同步逻辑图

Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

(2)双向主主同步逻辑图

Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

(3)线性级联单向双主同步逻辑图

Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

(4)环状级联单向多主同步

Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

(5)环状级联单向多主多从同步逻辑图

Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

在生产环境中所有对数据库内容的更新就必须在主服务器上进行，以避免用户对主服务器上数据内容的更新与对从服务器上数据库内容的更新不一致而导致发生冲突。

3.2 主从复制的应用场景

mysql主从复制有利于数据库架构的健壮性，提升访问速度和易于维护管理。

（1）主从服务器互为备份

主从服务器架构的设置，可以大大的加强数据库架构的健壮性，例如当主服务器出现问题时，我们可以人工或自动切换到从服务器继续提供服务。下面是主从同步的例子https://blog.51cto.com/oldboy/1240412。

对于非人为的硬件服障，对于人为的执行drop，delete无能为力。

（2）主从服务器读写分离分担网站压力

主从服务器架构可通过程序（php,java）或代理软件（mysql-proxy,amoeba）对用户（客户端）的请求实现读写分离，即通过在从库上仅仅处理用户的select查询请求，降低用户查询响应时间及读写请求对主服务器的压力，对于更新的数据（update，insert，delete）仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。

中大型公司：通过程序（php,java）。

测试环境：代理软件（mysql-proxy,amoeba）。

门户网站：分布式dbproxy(读写分离，hash负载均衡，健康检查)。

（3）根据服务器拆分业务独立并分担压力

可以把几个不同的从服务器，根据公司的业务进行拆分，例如有为用户提供查询功能的从服务器，有DBA用来备份的从服务器，还有提供公司内部人员访问的后台，脚本，日志分析及开发人员服务的从服务器，这样的拆分除了减轻主服务器的压力外使得对外用户浏览，对内处理公司内部用户业务，及DBA备份业务互不受影响。

主从架构生产环境从服务器分业务拆分使用案例：

M—>S1 ==>对外部用户提供服务（浏览帖子、浏览博客、浏览文章）负载均衡。

-->S2 ==>对外部用户提供服务（浏览帖子、浏览博客、浏览文章）负载均衡。

-->S3 ==>对外部用户提供服务（浏览帖子、浏览博客、浏览文章）负载均衡

-->S4 ==>对内部用户提供服务（后台访问、脚本任务、数据分析、开发人员浏览）

-->S5==>数据库备份服务（开启从服务器binlog功能，可实现增量备份及恢复）

逻辑图：

Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

3.3 如何实现MySQL主从读写分离

(1)通过程序实现读写分离（性能，效率最佳，推荐）

php和java程序都可以通过设置多个连接文件轻松的实现对数据库的读写分离，及当select查询时就去连接读库的连接文件，当update，insert,delete时就连接写库的连接文件。

(2)通过软件实现读写分离

MySQL-proxy，amoeba等代理软件也可以实现读写分离功能，但最好用程序实现读写分离。

(3)开发dbproxy

读写分离逻辑图如下所示：

Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

3.4 mysql主从复制原理

(1)mySQL主从复制架构图

Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

(2)原理:

左边是主库右边是从库，首先从库上需要两个线程来完成其中一个叫做sql线程另一个叫IO线程，在主库上只有一个线程负责完成主从同步叫IO线程，要想完成主从同步主库必须开启binlog，当用户请求主数据库有增删改操作的时候主库会把数据写到数据文件里面，并更新binlog，在主从同步的时候是由从库找主库的binlog文件中的change master这个位置，从库的配置文件要有主库的，主机ip、用户、密码、端口等。start master to …start slave开启同步。启同步之后首先是由从库的IO相主库发起请求，主库接收到从库的IO请求就会验证这个请求合不合法看用户名，密码，IP地址，端口是不是正确如果正确就可以连接了。然后从库在连接主库的时候会通过IO线程中的pos告诉主库我要从你的主库那个位置那个点给从库发binlog，比如说MASTER_LOG_FILE='mysql-bin.000010'MASTER_LOG_POS=4321，那主库的IO就会收到从库的这个请求，然后就会从mysql-bin.000010日志文件的的4321这个位置开始会给从库一直发binlog，从库收到log日志会把log日志写到rela-log中（称为中继日志），这时IO线程就不管了。然后还有一个文件较masterinfo这个文件记录了change master的内容，这时IO线程取到的日志扔到relay-log里面会根据最后取到的日志文件的位置点来更新change master。接下来再向主库的IO线程请求要上一次的那个文件的位置点往下的binlog，然后主库的IO把这些log再给从库发过来将日志写到slave-master里面，更新master info 里面的内容。那我们也没有同步啊没有将数据写到从库的磁盘中；其实在第一次从库的IO线程把binlog放到relay-log里面这个SQL线程就发现了，发现了就会时时把relay-log的语句经过转换写到从库的主句文件里。然后IO不断的往里面写sql不断的读，不断的写。

注意：如果需要在从库后面再加从库，从库一定要再开binlog。开启binlog的方法:

1.在从库上开binlog两条命令，将binlog打开。

2.再加一个log-slave-updates参数。

总结：

（1）同步的时候从库有两个线程来完成IO，SQL;主库有一个线程来完成IO线程。

（2）要在从库上面配置连接主库的IP、用户名、账号、密码、连接主库的文件位置以及pos点，在从库开启start salve之前要在同步位置点之前的所有数据要灌到从库上面来要不然从位置点以后是一致的前面不一致。

（3）在开启开关之前要保证主从库基于位置点是一致的，就是在文件的位置点以前从库是有完整数据的。

（4）要在主库上建立专门用与主库同步的账号。

（5）主库要打开binlog开关否则没法实现同步。

（6）从库打开开关的过程就是让IO，SQL线程都工作。

4.主从复制准备

4.1 定义服务器角色

主库（mysql master）:ip为192.168.136.113 port为3306

从库1（mysql slave）:ip为192.168.136.113 port为3307

从库2 （mysql slave）:ip为192.168.136.113 port为3308

提示：

一般常规主从复制在不同的机器上，并且监听的端口均为默认的3306，本文主从复制技术环境以单数据库多实例来讲解学习，上文已经写了怎么配置mysql多实例。虽然不在同一台机器上，但步骤和过程都是一样的。

4.2 数据库环境准备

（1）具备单机单数据库多实例的环境。

（2）或两台服务器每个机器一个数据库环境。

4.3 数据库读法的约定

主库，也称master 3306

从库，也称slave 3307

5.主库上执行的操作

5.1 设置server-id值并开启binlog参数

MySQL的主从复制的关键因素就是binlog日志是否开启，所有首先打开bin-log日志，按如下两个参数修改。重启服务器生效。

[root@linzhongniao ~]# egrep "\[mysqld]|log-bin|server-id" /data/3306/my.cnf
[mysqld]
log-bin = /data/3306/mysqlbin_linzhongniao
server-id = 1

提示：

1、上面的两个参数要放在my.cnf中的[mysqld]模块下，否则会出错。

2、一般server-id的值使用服务器ip地址的最后8位如113，目的是避免不同机器或实例ID重复（不适合多实例）。

(1)查看主库log_big是否开启

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables;"|egrep "log_bin|server_id"
log_bin ON
log_bin_trust_function_creators OFF
server_id   1
sql_log_bin ON

(2)看3306目录下面有没有日志，有就生效了

Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

5.2 建立用于同步的账号

建立用于与从库主从复制的账号rep

mysql> grant replication slave on *.* to 'rep'@'192.168.136.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

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

mysql> show grants for 'rep'@'192.168.136.%'\G
*************************** 1. row ***************************
Grants for rep@192.168.136.%: GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.136.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
1 row in set (0.00 sec)

提示：

（1）replication slave同步的必须权限，不要授权all。

（2）.表示所有库所有表。

（3）'rep'@'192.168.1.%' rep为同步账号，192.168.1.%授权主机段。

5.3 对数据库锁表只读

生产环境操作主从复制需要申请停机时间，数据量很大锁表会影响业务可以在备份数据库数据的时候让--master-data的值等于1。mysql5.1.X版本的锁表是flush tables，mysql5.5.X及以上版本是flush table。锁表不要退出窗口。

mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

提示：这个锁表命令的时间，在不同引擎的情况下,会受到下面参数的限制，超过设置的时间会自动解锁。

Interaction_timeout = 28800
wait_timeout = 28800

默认情况下的时间长为：28800秒(8小时)，主要看interactive_timeout和wait_timeout这两个参数。

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)

5.4 查看主库的状态

查看主库的状态及当前binlog日志文件名和二进制binlog日志偏移量
show master status;命令显示的数据要记录下来，后面的从库复制时是从这个位置开始的。

mysql> show master status;
+------------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------------+----------+--------------+------------------+
| mysqlbin_linzhongniao.000016 |  335 |  |  |
+------------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5.5 导出数据库数据

导出数据库数据，如果数据量很大并且允许停机可以停库直接将主库的所有数据文件打包不用mysqldump，并拷贝到从库的数据文件目录。

[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --events --master-data=2 >/opt/rep.sql

提示：-A参数表示所有数据库，-B参数增加use db和drop语句。--master-data参数最小化备份mysql数据库可以帮助从库直接找到binlog的位置。

为了保证导库期间，数据库没有数据写入，可以再检查主库状态信息，如果和之前查看的不对就出问题了没锁住表。

mysql> show master status;
+------------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------------+----------+--------------+------------------+
| mysqlbin_linzhongniao.000016 |  335 |  |  |
+------------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

备份数据后解锁主库恢复可写；

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

5.6 把主库导出的mysql数据迁移到从库

可以就用scp将备份的日志文件传给从服务器就可以，这里使用多实例就不讲解了。

6.从库上执行的操作

6.1 设置server-id值并关闭binlog参数

数据库的server-id值是唯一的，这里的server-id要和主库及其他从库不同，并注释掉从库里的binlog参数配置

[root@linzhongniao ~]# sed -i 's@log-bin = /data/3307/mysql-bin@#log-bin = /data/3307/mysql-bin@g' /data/3307/my.cnf 
[root@linzhongniao ~]# egrep "\[mysqld]|server-id|log-bin" /data/3307/my.cnf  [mysqld]
#log-bin = /data/3307/mysql-bin
server-id = 2

有两种情况需要打开log-bin记录数据库更新的sql语句

(1)级联同步A->B->C;那中间的B就要开启log-bin。如下所示：是从库开启binlog的配置

[root@linzhongniao ~]# egrep "\[mysqld]|log-bin|log-slave|expire_logs" /data/3307/my.cnf 
[mysqld]
log-bin = /data/3307/mysqlbin_linzhongniao
log-slave-updates
expire_logs_days = 7

这个地方看一下expire_logs_days，定期删除binlog日志文件，例如把参数的值设为7,就会把最近7天以前的binlog日志文件删除。

(2)在从库做数据备份数据备份必须要有全备和binlog日志，才是完整的备份。

重启从库：

/data/3307/mysql restart

6.2 还原主库导出的数据到从库

[root@mysqlduoshili ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock </opt/rep.sql

6.3 登录从库配置同步参数

mysql> CHANGE MASTER TO
 -> MASTER_HOST='192.168.136.113',   主库的IP
 -> MASTER_PORT=3306, 主库的端口，从库端口可以和主库不同
 -> MASTER_USER='rep',主库上建立的用于复制的用户rep
 -> MASTER_PASSWORD='123456', 这里是rep的密码
 -> MASTER_LOG_FILE='mysqlbin_linzhongniao.000016',这里是show master status;查看到的二进制日志文件名称注意不能有空格。
 -> MASTER_LOG_POS=335;这里也是show master status时看到的二进制日志偏移量注意不能多空格。
Query OK, 0 rows affected (0.02 sec)

6.4 启动从库同步开关

启动从库同步开关并查看同步状态，关闭主从复制stop slave;

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

mysql> show slave status\G
*************************** 1. row ***************************
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.136.113
  Master_User: rep
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: mysqlbin_linzhongniao.000016
  Read_Master_Log_Pos: 335
   Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 265
Relay_Master_Log_File: mysqlbin_linzhongniao.000016
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
  Replicate_Do_DB: 
  Replicate_Ignore_DB: mysql
   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: 335
  Relay_Log_Space: 415
  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
1 row in set (0.00 sec)

判断复制是否搭建成功就看如下IO和SQL两个线程是否显示yes状态

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show slave status\G"|egrep "IO_Running|SQL_Running|Seconds"
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

6.5 测试复制结果

在主数据库上创建一个nihao库

mysql> create database nihao;
Query OK, 1 row affected (0.10 sec)

mysql> show databases;   
+--------------------+
| Database   |
+--------------------+
| information_schema |
| linzhongniao   |
| linzhongniao_gbk   |
| mysql  |
| nihao  |
| performance_schema |
| school |
| test   |
+--------------------+
8 rows in set (0.00 sec)

接下来在查看一下从库，从库中也出现nihao库说明主从复制搭建成功。

mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| linzhongniao   |
| linzhongniao_gbk   |
| mysql  |
| nihao  |
| performance_schema |
| school |
| test   |
+--------------------+
8 rows in set (0.00 sec)

提示：Master-info和relay-info中继日志都会自动记录主库binlog更新数据的位置，例如我们在主库上创建一个库，master-info和relay-info中记录主库binlog日志的位置就由335变成了495。

[root@linzhongniao 3307]# cat data/master.info 
18
mysqlbin_linzhongniao.000016
495
192.168.136.113
rep
123456
3306
60
0

0
1800.000

0
[root@linzhongniao 3307]# cat relay-log.info 
/data/3307/relay-bin.000003
425
mysqlbin_linzhongniao.000016
495

上面的495对应主库mysqlbin_linzhongniao.000016日志文件的位置

[root@linzhongniao 3306]# mysqlbinlog mysqlbin_linzhongniao.000016|sed -n '31,40p'
# at 335
#181023 14:17:00 server id 1  end_log_pos 420   Query   thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1540275420/*!*/;
create database nihao
/*!*/;
# at 420
#181023 14:19:04 server id 1  end_log_pos 495   Query   thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1540275544/*!*/;
flush privileges
/*!*/;

7.总结：Mysql主从同步配置步骤

（1）准备两台数据库环境，或者单台多实例环境，能否正常启动和登录。

（2）配置my.cnf文件，主库配置log-bin和server-id功能。注意：配置参数后要重启生效。

（3）登录主库添加用于从库连接主库的账号列如：rep并授权replication slave同步的权限。

（4）登录主库，整库锁表flush table with read lock(窗口关闭后及失效，超时参数到了也会失效)；然后show master status;查看binlog的位置状态。

（5）新开窗口，linux命令行备份或导出原有的数据库数据，并拷贝到从库所在的服务器目录。如果数据量很大并且允许停机，可以停机打包将数据直接拷贝到从库的数据库目录中，而不用mysqldump。

（6）解锁主库 unlock tables。

（7）把主库导出的原有数据恢复到从库。

（8）根据主库的show master status查看binlog的位置状态，在从库执行change master to ...语句。

（10）从库开启同步开关，start slave。

（11）从库show slave stutes\G.检查同步状态，并在主库进行更新测试。

8.回顾mysql主从复制原理要点

(1) 异步方式同步。

(2) 逻辑同步模式，多种模式，默认是通过sql语句执行。

(3) 主库通过记录binlog实现对从库的同步。binlog是记录数据库的更新语句。

(4) 主库1个IO线程，从库由1个IO线程和2个SQL线程来完成的。

(5) 从库关键文件master.info记录了change master的所有信息，relay-log被称为中继日志记录了主库IO线程发过来的binlog日志，relay-info是SQL线程把binlog应用到数据库里的一个位置点功能。

(6) 如果从库还想级联从库，需要打开log-bin和log-slave-updates参数。

9.生产环境快速配置mysql主从复制方案

（1）安装好要配置从库的数据库，配置好log-bin和server-id参数。

（2）无需配置主库my.cnf文件，主库的log-bin和server-id 参数默认就是最好的。

（3）登录主库增加用于从库连接主库同步的账户例如：rep并授权replication slave 同步的权限。

（4）在半夜使用mysqldump带--master-data=1全备数据并恢复到从库，在从库配置同步参数是不用配置二进制文件的名称和主从同步的位置。

（5）在从库执行change master to..语句，无需binlog文件及对应位置点。

（6）从库开启同步开关，start slave。

（7）从库show master status\G 检查同步的状态，并在主库进行更新测试。

10.用--master-data=1方式全备数据

生产环境快速配置mysql主从复制方案实战；

10.1 主库上的操作

生产环境快速配置主从复制的方法不用锁表。

10.1.1 备份数据

用--master-data=1备份数据，在从库change master时不用指定binlog日志文件和位置点。主库不用查看master的状态。只需开启binlog，并创建用于主从同步的账户。

[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --events --master-data=1 >/opt/rep1.sql

10.2 从库的操作

10.2.1 设置server-id值并关闭binlog参数

[root@linzhongniao ~]# egrep "\[mysqld]|server-id|log-bin" /data/3308/my.cnf
[mysqld]
#log-bin = /data/3308/mysqlbin_linzhongniao
server-id = 3

10.2.2 还原主库导出的数据到从库

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3308/mysql.sock </opt/rep1.sql

10.2.3 登录从库配置同步参数

mysql> CHANGE MASTER TO
 -> MASTER_HOST='192.168.136.113', 
 -> MASTER_PORT=3306,
 -> MASTER_USER='rep',
 -> MASTER_PASSWORD='123456';
Query OK, 0 rows affected (0.01 sec)

10.2.4 启动从库主从复制

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

mysql> show slave status\G
*************************** 1. row ***************************
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.136.113
  Master_User: rep
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: mysqlbin_linzhongniao.000038
  Read_Master_Log_Pos: 194
   Relay_Log_File: relay-bin.000054
Relay_Log_Pos: 265
Relay_Master_Log_File: mysqlbin_linzhongniao.000038
 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: 1
  Exec_Master_Log_Pos: 194
  Relay_Log_Space: 573
  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
1 row in set (0.00 sec)

10.2.5 测试

在主库中创建nihaod

mysql> create database nihaod;
Query OK, 1 row affected (0.00 sec)

在从库中查看是否同步

mysql> show databases like 'nihao%';
+-------------------+
| Database (nihao%) |
+-------------------+
| nihao |
| nihaoa|
| nihaob|
| nihaod|
+-------------------+
4 rows in set (0.00 sec)

11.生产环境主从同步配置注意事项

（1）申请设备资源。用来做从库服务器。从库服务器和主库差距不要太大。

（2）撰写方案文档和实施步骤。

例如你的服务器只有主库，而且已经跑了生产线上的应用了，现在由于业务需要第一次做从库，此时可能需要和公司申请停机维护时间（要确认这个时间内可以做一次全备），即在用户访问量最小，且不影响内部其他业务运转的时间点来停机（包括锁表）配置主从复制，一般都是凌晨进行。注意：停机（锁表，挺库）的最小时间段，为锁表后备份的时间，也就是说无需等待主从配置最好。

12.全备数据及获取binlog位置

也可以不申请停机时间，在定时任务备份时每天夜里，每天夜里服务小的时刻定时备份如：模拟主从同步的步骤，获取到全备及全备过程中binlog位置的信息或者直接用--master-data 参数解决。

(1)方法一：不用--master-date参数备份数据

不用--master-data参数或者--master-data=2备份数据，需要全备数据并且记录binlog更新的位置

#/bin/sh

#Date: 2018-02-07 
#Author: Create by linzhongniao   
#Mail: xxxxxxxxx@163.com 
#Function:This scripts function is More complex backup scripts, which need to find binlog log files and location points 
#Version: 1.1 

USER=system
PASS=123456
MYSOCK=/data/3306/mysql.sock
DATA_PATH=/server/backup
DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
LOG_FILE=${DATA_PATH}/mysql_backup_`date +%F`.log
MYSQL_PATH=/usr/local/mysql/bin
#--single-transaction Specifically for the InnoDB engine, when the data is updated when the data is updated, it can't see the whole isolation.
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u$USER -p$PASS -S $MYSOCK --events -A -B --single-transaction"
MYSQL_CMD="${MYSQL_PATH}/mysql -u$USER -p$PASS -S $MYSOCK"

cat |${MYSQL_CMD}<<EOF
flush table with read lock;
system echo "-----show master status result-----" >>$LOG_FILE;
system ${MYSQL_CMD} -e "show master status"|tail -1 >>$LOG_FILE;
system ${MYSQL_DUMP}|gzip > $DATA_FILE;
unlock tables;
quit
EOF

（2)方法二：用--master-data=1备份数据

用--master-data=1备份数据值对数据进行全备即可，不需要记录binlog的位置

#/bin/bash
#Date: 2018-02-07 
#Author: Create by linzhongniao   
#Mail: xxxxxx@163.com 
#Function:This scripts function is The simplest backup script, do not look for the binlog file and the corresponding location 
#Version: 1.1 

USER=system
PASS=123456
MYSOCK=/data/3306/mysql.sock
DATA_PATH=/server/backup
DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
MYSQL_PATH=/usr/local/mysql/bin
#--single-transaction Specifically for the InnoDB engine, when the data is updated when the data is updated, it can't see the whole isolation.
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u$USER -p$PASS -S $MYSOCK --events -A -B --single-transaction --master-data=1"
${MYSQL_DUMP}|gzip > $DATA_FILE

13.查看线程的状态

主库：

mysql> show processlist\G
*************************** 1. row ***************************
 Id: 8
   User: rep
   Host: 192.168.136.113:54344
 db: NULL
Command: Binlog Dump
   Time: 33231
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
 Id: 11
   User: root
   Host: localhost
 db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
2 rows in set (0.00 sec)

从库：

   mysql> show processlist\G
*************************** 1. row ***************************
 Id: 11
   User: system user
   Host: 
 db: NULL
Command: Connect
   Time: 32286
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
 Id: 12
   User: system user
   Host: 
 db: NULL
Command: Connect
   Time: 1815
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
 Id: 15
   User: root
   Host: localhost
 db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
3 rows in set (0.00 sec)

13.1 主从复制主线程的几种状态

下面列出了主服务器的Binlog Dump线程的State列的最常见的状态。如果你没有在主服务器上看见任何Binlog Dump线程，这说明复制没有在运行—即，目前没有连接任何从服务器。

     Sending binlog event to slave

二进制日志由各种事件组成，一个事件通常为一个更新加一些其它信息。线程已经从二进制日志读取了一个事件并且正将它发送到从服务器。

     Finished reading one binlog; switching to next binlog

线程已经读完二进制日志文件并且正打开下一个要发送到从服务器的日志文件。

     Has sent all binlog to slave; waiting for binlog to be updated

线程已经从二进制日志读取所有主要的更新并已经发送到了从服务器。线程现在正空闲，等待由主服务器上新的更新导致的出现在二进制日志中的新事件。

     Waiting to finalize termination

线程停止时发生的一个很简单的状态。

13.2 主从复制从库IO线程的状态

下面列出了从服务器的I/O线程的State列的最常见的状态。该状态也出现在Slave_IO_State列，由SHOW SLAVE STATUS显示。这说明你可以只通过该语句仔细浏览所发生的事情。

     Connecting to master

线程正试图连接主服务器。

     Checking master version

建立同主服务器之间的连接后立即临时出现的状态。

     Registering slave on master

建立同主服务器之间的连接后立即临时出现的状态。

     Requesting binlog dump

建立同主服务器之间的连接后立即临时出现的状态。线程向主服务器发送一条请求，索取从请求的二进制日志文件名和位置开始的二进制日志的内容。

     Waiting to reconnect after a failed binlog dump request

如果二进制日志转储请求失败(由于没有连接)，线程进入睡眠状态，然后定期尝试重新连接。可以使用--master-connect-retry选项指定重试之间的间隔。

     Reconnecting after a failed binlog dump request

线程正尝试重新连接主服务器。

     Waiting for master to send event

线程已经连接上主服务器，正等待二进制日志事件到达。如果主服务器正空闲，会持续较长的时间。如果等待持续slave_read_timeout秒，则发生超时。此时，线程认为连接被中断并企图重新连接。

     Queueing master event to the relay log

线程已经读取一个事件，正将它复制到中继日志供SQL线程来处理。

     Waiting to reconnect after a failed master event read

读取时(由于没有连接)出现错误。线程企图重新连接前将睡眠master-connect-retry秒。

     Reconnecting after a failed master event read

线程正尝试重新连接主服务器。当连接重新建立后，状态变为Waiting for master to send event。

     Waiting for the slave SQL thread to free enough relay log space

正使用一个非零relay_log_space_limit值，中继日志已经增长到其组合大小超过该值。I/O线程正等待直到SQL线程处理中继日志内容并删除部分中继日志文件来释放足够的空间。

     Waiting for slave mutex on exit

线程停止时发生的一个很简单的状态。

13.3 复制从SQL线程状态

下面列出了从服务器的SQL线程的State列的最常见的状态。

     Reading event from the relay log

线程已经从中继日志读取一个事件，可以对事件进行处理了。

     Has read all relay log; waiting for the slave I/O thread to update it

线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。

     Waiting for slave mutex on exit

线程停止时发生的一个很简单的状态。

I/O线程的State列也可以显示语句的文本。这说明线程已经从中继日志读取了一个事件，从中提取了语句，并且正在执行语句。

13.4 查看mysql线程同步状态的用途

通过mysql线程同步状态查看数据库同步是否完成，用于主库宕机或者人工数据库主从切换迁移等。主库宕机选择最快的从库提升为主，，就需要查看主从复制状态当然也可以利用mysql的半同步功能，选择固定的库为主。

转载于:https://blog.51cto.com/10642812/2069182

weixin_34190136

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

1.普通文件数据同步（1）NFS网络文件共享可以同步存储数据。（2）Samba共享数据：https://blog.51cto.com/taokey/1203553。（3）定时任务或守护进程结合。（4）Inotify+rsync触发式实时数据同步。（5）ftp数据同步。http://down.51cto.com/data/1094997。（6）ssh key+scp/rsync。（7）...
复制链接

扫一扫