MySQL主从复制原理应用基础

terwe

mysql主从复制
mysql支持单向 双向 链式级联 实时 异步复制，在复制过程中，一台服务器充当主服务器（Master）,而一个或多个其他服务器充当从服务器(Slave)

mysql主从复制的应用场景
1、主从服务器互为备份
2、主从服务器读写分离分担网站压力

读写分离
中大型公司：通过程序(php,java)
测试环境：代理软件(mysql-proxy,amoeba)
门户网站：分布式dbproxy（读写分离，hash负载均衡，健康检查）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136

主从同步实践操作（多实例环境） 1、主库上面设置server-id值并开启binlog参数 [iyunv@centos03 ~]# egrep "log-bin|server-id" /data/3306/my.cnf log-bin = /data/3306/mysql-bin server-id = 1 检查实际配置效果 [iyunv@centos03 ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like 'log_bin';" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin       | ON    | +---------------+-------+ 2、建立用于同步的账号 mysql>  grant replication slave  on *.* to rep@'172.16.80.%' identified by '123456'; 说明：replication slave 是mysql同步的必须权限，此处不要授权all mysql> flush privileges; 查看授权后的结果 mysql> show grants for rep@'172.16.80.%'; +--------------------------------------------------------------------------------------------------------------------------+ | Grants for rep@172.16.80.%                                                                                               | +--------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'rep'@'172.16.80.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 3、锁表，导出数据库 mysql> flush table with read lock;    #该窗口不能断，新开一个窗口做数据库导出操作 Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 |      332 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) [iyunv@centos03 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --events --master-data=2 > /opt/rep.sql #导出所有数据库 [iyunv@centos03 ~]# vim /opt/rep.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=332;  #可以看到该语句的记录位置和上面show master status是一样的，注释状态 4、数据库导出后，解锁 mysql> show master status;   #再次查看位置点，以验证上面的锁表操作是否有效 +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 |      332 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) 5、从库上面 [iyunv@centos03 ~]# egrep "log-bin|server-id" /data/3307/my.cnf      #log-bin = /data/3307/mysql-bin    #log-bin无需开启 server-id = 3   #server-id的值不能和主库上面的值一样 [iyunv@centos03 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock < /opt/rep.sql   #导入从主库备份的数据库 [iyunv@centos03 ~]# mysql -uroot -phello123 -S /data/3307/mysql.sock             mysql> change master to \     -> master_host='172.16.80.118',\     -> master_user='rep',\     -> master_password='123456',\     -> master_log_file='mysql-bin.000002',\     -> master_log_pos=332; Query OK, 0 rows affected (0.03 sec) 验证一下 [iyunv@centos03 ~]# cat /data/3307/data/master.info 18 mysql-bin.000002 332 172.16.80.118 rep 123456 3306 60 0 mysql> start slave;       Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G;    #观察Slave_IO和Slave_SQL 这两个线程的状态是否是yes *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 172.16.80.118                   Master_User: rep                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000002           Read_Master_Log_Pos: 332                Relay_Log_File: relay-bin.000002                 Relay_Log_Pos: 253         Relay_Master_Log_File: mysql-bin.000002              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: 332               Relay_Log_Space: 403               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)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

6、登录主库创建数据库，看是否会同步到从库上面 [iyunv@centos03 ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock mysql> create database martin; Query OK, 1 row affected (0.01 sec) 观察从库，可以看到已经同步过来 mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | martin             | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.00 sec) [iyunv@centos03 ~]# cd /data/3307 [iyunv@centos03 3307]# ls data  my.cnf  mysql  mysqld.pid  mysql_martin3307.err  mysql.sock  relay-bin.000001  relay-bin.000002  relay-bin.index  relay-log.info [iyunv@centos03 3307]# cat data/master.info 18 mysql-bin.000002 419 172.16.80.118 rep 123456 3306 60 0 [iyunv@centos03 3307]# mysqlbinlog  relay-bin.000002 /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create database martin

mysql主从复制原理总结：
1、异步同步方式
2、逻辑同步模式，多种模式，默认是通过sql语句执行
3、主库通过记录bin-log实现对从库的同步，bin-log记录数据库更新的语句
4、主库一个IO线程，从库一个IO线程和一个SQL线程
5、从库关键文件master.info  relay-log  relay-info
6、如果从库还需要做级联从库，从库需要打开log-bin和log-slave-updates参数

监控mysql主从状态（这里我们简单监控从库上面io和sql线程yes总数为不是2就认为主从出现问题了）
在客户端编写脚本
[iyunv@centos03 tools]# cat /tmp/mysql-replication.sh
#!/bin/bash
/application/mysql/bin/mysql -uroot -p123456 -e 'show slave status\G' -S /data/3307/mysql.sock|grep -Ei "Slave_IO_Running|Slave_SQL_Running"|awk '{print $2}'|grep -c Yes

修改zabbix-agent配置文件

UnsafeUserParameters=1
UserParameter=mysql.replication,/tmp/mysql-replication.sh

在服务器端添加监控项--触发器--图形




正常情况下的图形如下

此时我们模拟主库挂掉
[iyunv@centos03 tools]# /data/3306/mysql stop