企业高可用性标准
全年无故障率(非计划内故障停机)
99.9% 0.001 × 365× 24 × 60 = 525.6 min
99.99% = 52.56 min
99.999% = 5.25 min
2、高可用架构方案
负载均衡: 有一定的高可用
LVS + Nginx
主备:有高可用性,但是需要切换
Keepalive 、MHA (互联网金融 99.9以上的标准)
真正高可用:(多活系统)
NDB Cluster 、 Oracle RAC、Sysbase Cluster InnoDB Cluster (MGR)、PXC、MGC
3、主从复制
主从复制的主要作用?
X程:MySQL,数据量很大,被rm 了。12h 每小时100w$,股票爆跌。
主从复制配置注意事项
1、两个mysql实例,server_id,server_uuid
2、主库需要开启二进制日志
3、主库中需要建立专用的复制用户(replication slave)
grant replication slave on *.* to 'repl'@'192.168.1.145' identified by '123';
flush privileges;
4、主库全备
mysqldump -uroot -p --single-transaction --master-data=2 --triggers -A -R -E -F >all.sql
5、把全备份导入到从库(从库必须是最新安装的)
mysql -uroot < all.sql
6、在主库上查看主库相关信息
show master status;
show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 | 348 | | | 9e5a29f5-93f8-11e9-a3c5-000c2985543e:1-9 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
7、在从库上执行如下信息
change master to
master_host='master_ip',
master_port=3306,
master_user='repl',
master_password='123',
master_log_file='binlog.000002',
master_log_pos=348,
master_connect=retry=10;
启动slavej
>slave start
查看从服务器上主从复制的状态信息
>show slave status\G
>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.141 主服务器的ip地址
Master_User: repl 主从复制使用的用户
Master_Port: 3306 主服务器监听的端口
Connect_Retry: 10 网络抖动时主从尝试连接的间隔时间,默认60s
Master_Log_File: binlog.000003 从库读取到主服务器上的binlog文件
Read_Master_Log_Pos: 194 主库上的lsn 号
Relay_Log_File: s10-relay-bin.000011 从服务器上生成的文中级日志文件
Relay_Log_Pos: 401
Relay_Master_Log_File: binlog.000003
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: 194
Relay_Log_Space: 809
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: 100
Master_UUID: 9e5a29f5-93f8-11e9-a3c5-000c2985543e
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9e5a29f5-93f8-11e9-a3c5-000c2985543e:6-750964
Executed_Gtid_Set: 9e5a29f5-93f8-11e9-a3c5-000c2985543e:6-750964,
dace9090-93f8-11e9-915d-000c29bb4445:1-5
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql 主从同步原理: