主从复制
2019-07-12 本文已影响0人
happywho250
主从复制
mysql增量备份技术
原理
- 从库开启同步以后
主从配置注意事项和顺序
- 从库开启同步以后，创建一个i/o线程，读取主库的bin-log日志
- 将主库的bin-log日志同步到从库的中继日志(relay-log)
- 将中继日志中的事件重放
作用
- mysql数据库备份的作用，热备份。
- 主负责写 从负责读 减少服务器压力
- 主库不加索引提高写入的速度，从库加索引提高读的速度
- 多个从库 可以做到分摊读取
- 当主库挂掉的情况下 可以用从库代替
准备：
- 将需要同步的数据库，保持主从数据库中的数据同步.
- 从数据库版本需要和主数据库版本保证相同，或者从数据库版本大于主数据库版本.
主库配置
-
/etc/mysql/mysql.cond.d/mysqld.cnf配置文件
server-id = 1 # 集群配置，主从的server-id需要不一致
log_bin = /var/log/mysql/mysql-bin.log # binlog日志的位置
expire_logs_days = 10 # binlog保存的时间，10表示保存最近10条
max_binlog_size = 100M #每个日志文件的最大值，超过该值就进行日志切割
binlog-do-db = test # 需要复制的数据库名称，如果有多个就重复多次
log-slave-updates # 将从服务器从主服务器中收到的更新记录到从服务器的二进制日志文件中.
sync_binlog = 1 #控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失)
auto_increment_offset = 1 #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_increment = 1 #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
- 需要给从库创建一个有权限的mysql账号
use mysql;
create user 'username'@'ip_address' identified by 'password'; # ip_address 为% 表示所有ip地址
flush privileges;
grant replication slave on test.* to 'username'@'ip_address' # 给该用户test库下所有表的权限.[*.*表示所有库所有表]
flush privileges;
查看主服务器状态
show master status\G;
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000006 ## 当前记录二进制日志的文件
Position: 154 # 日志文件中的位置
Binlog_Do_DB: test # 复制的数据库的名称
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
- 主服务器需要设置
bind_addr = 0.0.0.0 # 设置所有地址全部可以进行连接,但是在创建用户时指定好客户端的ip地址 以免被滥用
从服务器配置
- 修改配置
server-id = 1001 # 与主服务server-id不同
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
replicate-do-db = test
slave-net-timeout = 60 #
#log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作
innodb_flush_log_at_trx_commit = 0 #log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作
slave-net-timeout = 60 #MySQL主从复制的时候，当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据
- 执行同步命令
change master to master_host='主服务器ip',master_user='username',master_password='password',master_log_file='mysql-bin.000006',master_log_pos=154;
#开启同步功能,start|stop slave
mysql>start slave;
查看从库的状态
show slave status\G
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xxx
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 154
Relay_Log_File: Slave-relay-bin.000021
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
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: 154
Relay_Log_Space: 527
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
Master_UUID: 698972a1-90c7-11e9-9c44-aaaa0014ac96
Master_Info_File: /var/lib/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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ps:Slave_IO_State: Waiting for master to send event 等待主库数据
这两项必须为yes.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
