8.mysql主从复制原理及实践

=========================
1.由架构引出主从复制（主从同步）的作用及重要性

=========================
2.文件及DB各种同步方案大集合
普通文件的数据同步方法：
（1）NFS网络文件共享可以同步存储数据；
（2）samba共享数据；
构建Samba文件共享服务器
http://blog.51cto.com/taokey/1203553
（3）定时任务或守护进程结合rsync,scp；
（4）inotify(sersync)+rsync触发式实时数据同步；
（5）ftp数据同步；//ftp自动上传，只要发生变化就推送过去。
（6）ssh key+scp/rsync；
（7）svn版本管理；
（8）rsync,sersync,inotify,nnion（双向同步），csync2（多向同步）。

mysql的主从同步方案，和（2）（3）是类似的，且无需借助第三方工具，而是自带的同步复制功能；另外，mysql的主从同步并不是磁盘上文件直接同步。

总结企业linux运维场景数据同步方案：
（1）文件级别同步方案：
scp,nfs,sftp,http,samba,rsync,csync2,union
2017年Linux运维人员必会开源运维工具体系
http://blog.51cto.com/oldboy/775056
思想：
a.文件级别也可以利用mysql,mongodb等软件。
b.两个服务器同时写数据，双写就是一个同步机制。
（2）文件系统级别同步：
drbd（基于文件系统同步网络RAID1），同时几乎任何业务数据。
mysql数据库的官方推荐drbd同步数据，所有单点服务例如：NFS,MFS(DRBD)等都可以用drbd。
（3）数据库同步方案：
a.自身同步机制：
mysql replication,mysql主从复制（逻辑的SQL从写）。
oracle dataguard（物理的磁盘块，逻辑的SQL语句从写）。
b.第三方drbd，参考URL：
Heartbeat+DRBD+MySQL高可用架构方案与实施过程细节
http://blog.51cto.com/oldboy/1240412

=========================
3.mysql主从复制及分布式数据库架构实现简介
主从复制：
mysql支持单向、双向、链式级联、实时、异步复制。在复制过程中，一台服务器充当主服务器（Master），而另一个或者多个其他的服务器充当从服务器（Slave）。
复制可以是单向：M==>S；双向：M<==>M；多M环状同步等。
如果设置了链式级联复制，那么，从（slave）服务器本身除了充当从服务器外，也会同时充当其下面的从服务器的主服务器。
链式级联复制类似：A->B->C->D的复制的方式。

------------------图

=========================
4.mysql主从同步的应用场合及切换从库不丢失数据多方案
意义：mysql主从复制有利于数据库架构的健壮性、提升访问速度和易于维护管理。
当前生产工作中，大多数应用的mysql主从同步都是异步的复制方式，即不是严格实时的数据同步。
当配置好主从复制后，所有对数据库内容的更新就必须在主服务器上进行，以避免用户对主服务器上数据库内容的更新与对从服务器上数据库内容的更新不一致而导致发生冲突。

=========================
5.mysql数据库读写分离介绍及企业生产实现方案
//主库更新时不能读和写，主库写，从库读（从库做集群）--读写分离
（1）主从服务器互为备份
（2）主从服务器读写分离分担网站压力
中大型公司：通过程序（php,java）
测试环境：代理软件（mysql-proxy,amoeba）
门户网站：分布式dbproxy（读写分离，hash负载均衡，健康检查）
（3）根据服务器拆分业务独立并分担压力

=========================
6.根据企业拆分业务应用到不同的从库思想

=========================
7.mysql主从复制原理
（1）如何实现mysql主从读写分离
a.通过程序实现读写分离（性能，效率最佳，推荐）
php和java程序都可以通过设置多个连接文件轻松的实现对数据库的读写分离，即当select时，就去连接读库的连接文件，当update,insert,dalete时就连接写库的连接文件。
b.通过软件实现读写分离
mysql-proxy,amoeba等代理软件也可以实现读写分离功能，但最常用好用的还是程序实现读写分离。
c.开发dbproxy
　　

（2）mysql主从复制原理
属异步的同步。
用户增删改时将数据写入数据文件中，把更新的结果写入mysql-bin.00000x(log-bin)中。
***MYSQL主从同步配置步骤***
<1>准备两台数据库环境，或者单台多实例环境，能否正常启动和登录。
[root@oldboyedu-01 ~]# /data/3306/mysql start
MySQL is running...
[root@oldboyedu-01 ~]# /data/3307/mysql start
Starting MySQL...
[root@oldboyedu-01 ~]# netstat -lntup|grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2130/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 5661/mysqld
[root@oldboyedu-01 ~]# ll /data/3306/
total 32
drwxr-xr-x 6 mysql mysql 4096 Jan 14 18:24 data
-rw-r--r-- 1 mysql mysql 1935 Jan 14 17:42 my.cnf
-rwxr-xr-x 1 mysql mysql 1310 Jan 13 03:24 mysql
-rw-rw---- 1 mysql mysql 107 Jan 14 18:24 mysql3306-bin.000001
-rw-rw---- 1 mysql mysql 32 Jan 14 18:24 mysql3306-bin.index
-rw-rw---- 1 mysql mysql 5 Jan 14 18:24 mysqld.pid
-rw-r----- 1 mysql root 4576 Jan 14 18:24 mysql_oldboy3306.err
srwxrwxrwx 1 mysql mysql 0 Jan 14 18:24 mysql.sock

<2>配置my.cnf文件，主库配置log-bin和server-id参数，从库配置 server-id不能和主库及其他从库一样，一般不开启从库log-bin功能。
注意:配置参数后要重启生效
开启binlog功能：
[root@oldboyedu-01 ~]# egrep "log-bin|server-id" /data/3306/my.cnf
#log-bin = /data/3306/mysql-bin
log-bin = /data/3306/mysql3306-bin
server-id = 1
说明：server-id目的是避免不同机器或实例ID重复；值一般使用ip地址最后8位。

查看是否生效：
-rw-rw---- 1 mysql mysql 107 Jan 14 17:47 mysql3306-bin.000001
-rw-rw---- 1 mysql mysql 32 Jan 14 17:47 mysql3306-bin.index
或者：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show variables like 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+

[root@oldboyedu-01 ~]# egrep "log-bin|server-id" /data/3307/my.cnf
#log-bin = /data/3307/mysql-bin
server-id = 3
说明：另一个实例做级联的话打开log-bin，否则不必打开。

<3>登录主库增加用于从库连接主库同步的账户例如:rep，并授权replication slave同步的权限。
创建用于从库复制的账号rep：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by 'oldboy123';
mysql> flush privileges;
检查创建的rep账号:
mysql> select user,host from mysql.user where user='rep';
+------+----------+
| user | host |
+------+----------+
| rep | 10.0.0.% |
+------+----------+
1 row in set (0.00 sec)
mysql> show grants for rep@'10.0.0.%';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for rep@10.0.0.% |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'10.0.0.%' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
+-----------------------------------------------------------------------------------------------------------------------+
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysql3306-bin.000001 | 640 | | |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
查看主库状态：
mysql> show master logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| mysql3306-bin.000001 | 640 |
+----------------------+-----------+
1 row in set (0.00 sec)

<4>登录主库，整库锁表flush table with read lock（窗口关闭后即失效，超时参数到了也失效）；然后show master status查看binlog的位置状态。
对数据库锁表只读（当前窗口不要关闭）：
生产环境时，操作主从复制，需要申请停机时间。数据量很大锁表会影响业务。当然我们后面会有一键完成主从的方案。
mysql> flush table with read lock;

<5>新开窗口，linux命令行备份或导出原有的数据库数据，并拷贝到从库所在的服务器目录。
如果数据量很大，并且允许停机，可以停机打包而不用 mysqldump。
另开窗口备份主库数据：
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --events|gzip >/opt/rep_$(date +%F).sql.gz
或者：
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --events --master-data=2 >/opt/rep.sql //也可加参数-x自动锁

<6>解锁主库，unlock tables。
导库后，解锁主库，恢复可写：
mysql> unlock tables;
说明：实际上做从库前无论主库更新了多少数据，最后从库都会从上面show status的位置很快赶上主库的进度的！

<7>把主库导出的原有数据恢复到从库。
主库灌到从库：
不同服务器可以scp过去。
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock </opt/rep.sql

CHANGE MASTER TO
MASTER_HOST='10.0.0.200',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='oldboy123',
MASTER_LOG_FILE='mysql3306-bin.000001',
MASTER_LOG_POS=640;

<8>根据主库的show master status查看binlog的位置状态，在从库执行change master to...语句。
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.200',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='oldboy123',
-> MASTER_LOG_FILE='mysql3306-bin.000001',
-> MASTER_LOG_POS=640;
Query OK, 0 rows affected (0.05 sec)
[root@oldboyedu-01 data]# pwd
/data/3307/data
[root@oldboyedu-01 data]# ls
d3307 ibdata1 ib_logfile0 ib_logfile1 ib_logfile2 master.info mysql oldboy performance_schema test
[root@oldboyedu-01 data]# cat master.info
18
mysql3306-bin.000001
640
10.0.0.200
rep
oldboy123
3306
60
0
说明：当主库给它发来日志时，就不断更新这里面内容。

<9>从库开启同步开关，start slave。
启动从库同步开关。
mysql> start slave;

查看两个线程IO（取log的，干活的）和SQL（写数据）状态：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.200
Master_User: rep
Master_Port: 3306
Connect_Retry: 60 //出问题时会重试！
Master_Log_File: mysql3306-bin.000001
Read_Master_Log_Pos: 640
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 257
Relay_Master_Log_File: mysql3306-bin.000001
Slave_IO_Running: Yes //
Slave_SQL_Running: Yes //
Seconds_Behind_Master: 0 //0就对了
[root@oldboyedu-01 ~]# ll /data/3307/
total 48
drwxr-xr-x 7 mysql mysql 4096 Jan 14 19:13 data
-rw-r--r-- 1 mysql mysql 1901 Jan 13 01:36 my.cnf
-rwxr-xr-x 1 mysql mysql 1310 Jan 13 03:25 mysql
-rw-rw---- 1 mysql mysql 5 Jan 14 18:24 mysqld.pid
-rw-r----- 1 mysql root 15535 Jan 14 19:16 mysql_oldboy3307.err
srwxrwxrwx 1 mysql mysql 0 Jan 14 18:24 mysql.sock
-rw-rw---- 1 mysql mysql 150 Jan 14 19:16 relay-bin.000001
-rw-rw---- 1 mysql mysql 257 Jan 14 19:16 relay-bin.000002
-rw-rw---- 1 mysql mysql 56 Jan 14 19:16 relay-bin.index
-rw-rw---- 1 mysql mysql 57 Jan 14 19:16 relay-log.info

[root@oldboyedu-01 ~]# mysqlbinlog /data/3307/relay-bin.000001

从库：
[root@oldboyedu-01 ~]# mysqlbinlog /data/3307/relay-bin.000002
create database oldgirl
找到了！

[root@oldboyedu-01 3307]# cat relay-bin.index //记录relay-log更新到哪了
/data/3307/relay-bin.00001
/data/3307/relay-bin.00002

[root@oldboyedu-01 3307]# cat relay-log.info //给SQL线程使用！
/data/3307/relay-bin.000002
346 //SQL向数据库写时，SQL线程读relay-log读哪了，即relay-log的位置。
mysql3306-bin.000001
729 //主库，IO取log回来的位置，即master.info中的位置。

[root@oldboyedu-01 3307]# cat data/master.info //给IO线程使用！
18
mysql3306-bin.000001
729 ////主库，IO取log回来的位置，即master.info中的位置。
10.0.0.200
rep
oldboy123
3306
60
0
说明：SQL线程通过relay-log这个位置读一点（要从哪读）写一点数据，根据index索引始终往下读。
说明：relay-log比较快，读一点记一点！根据index索引始终往下读！

==============================
MYSQL主从同步配置步骤：
1、准备两台数据库环境，或者单台多实例环境，能否正常启动和登录。
2、配置my.cnf文件，主库配置log-bin和server-id参数，从库配置 server-id不能和主库及其他从库一样，一般不开启从库log-bin功能。
注意:配置参数后要重启生效。
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...语句。
9、从库开启同步开关,，start slave。
10、从库show slave status\G，检查同步状态，并在主库进行更新测试。

posted @ 2018-01-25 22:41 bkycrmn 阅读(257) 评论(0) 收藏举报

刷新页面返回顶部

8.mysql主从复制原理及实践

公告