MySql主从配置
MySQL主从复制简介
Mysql是一个异步复制过程。
Mysql主从复制，是逻辑的同步复制。通过逻辑的binlog日志复制到同步的数据本地然后读取里面的sql语句应用到数据库的过程。
支持单向、双向、链式级联等不同场景的复制，在复制过程中，一台服务器充当主服务器，而一个或多个其他的服务器充当从服务器。
MYSQL主从复制原理介绍
MYsql的主从复制是一个异步的复制过程，数据库数据从一个master数据库复制到另一个slave数据库。在master与slave之间实现整个主从复制的过程是由三个线程参与完成，master端的IO线程，slave端的SQL线程和IO线程
要实现主从复制，首先必须打开master端的binlog功能，否则是无法实现此功能的，因为整个过程实际上就是slave从master端获取binlog日志，然后在slave上以相同顺序执行获取的binlog日志中所有记录的各种操作
MySql主从复制企业应用场景
应用场景1、从服务器作为主服务器的实时数据备份
==>利用mysql的复制功能做数据备份，在硬件宕机，服务器故障的场景数据备份是有效的，但是对人为执行的drop，delete等语句，从库的备份就没用了，因为从服务器也会执行删除语句
应用场景2、主从服务器实现读写分离，从服务器实现负载均衡，减轻主库读压力
实现Mysql主从读写分离的方案
1、通过程序实现读写分离（性能、效率最佳，推荐）
php和java程序都可以设置多个连接文件轻松实现对数据库的读写分离，即当select时，就是连接读库的连接文件，当update、insert、delete时就连接写库的连接文件。
通过程序实现读写分离的缺点就是需要开发对程序改造，对下层不透明，但是这种方式更容易开发和实现，适合互联网场景
2、通过开源软件实现（MySQL-proxy，Amoeba 还不成熟，都不成熟，不要用）
MySQL-proxy，Amoeba等代理软件也可以实现读写分离功能，这些软件的稳定性和功能一般，不建议生产使用，普通公司还是通过客户端程序实现读写分离
3、大型门户独立开发DAL层综合软件
baidu，阿里等大型网站有牛人，会花力气开发适合自己业务的读写分离、负载均衡、监控报警、自动扩容、自动收缩等一系列功能的DAL层软件
Mysql主从复制集群架构的数据备份策略
有主从复制了，还需要做定时全量加增加备份吗?答案是肯定的
1、因为主库有语句级操作，如drop database时，从库也会执行，所以还需要定时全量加增量备份。
2、可以选择从库作为备份服务。
a、选择一个不对外服务的从库，确保和主库更新最接近，专门做数据备份用。
b、从库开启binlog功能。备份时可选择只停止sql线程，停止应用sql语句到数据库，IO线程工作状态，执行命令为stop slave sql_thread，备份方式可采取mysqlddump（小于50G）或者直接物理备份cp tar (/data/） xtrabackup等，把全备和binlog发送到备份服务器上保存。
MySQL出现同步延迟有哪些原因？如何解决？
1.从库太多导致复制延迟
优化：建议从库数量3-5个为宜
2.从库硬件比主库硬件差
优化：提升硬件性能
3.慢SQL语句过多
优化：SQL语句执行时间太长，需要优化SQL语句
4.主从复制的设计问题
优化：主从复制单线程，可以通过多线程IO方案解决；另外MySQL5.6.3支持多线程IO复制。
5.主从库之间的网络延迟
优化：尽量链路短，提升端口带宽
6.主库读写压力大
优化：前端加buffer和缓存。不管有多延迟，只要不影响业务就没事
7、业务设计缺陷导致延迟影响业务
优化：从库没有数据改读主库
读写分离集群授权方案
方法一
设置用户web_w 和web_r实现
方法二
方案a、主库创建完用户和权限，从库上revork收回写权限 (help revoke)
方案b、replicate-ignore-db=mysql（主从都配) binlog-ignore-db=mysql 并不记录mysql库binlog
方法三
从库上设置read-only
MySQL read-only参数功能与使用实践 此参数一般在从库端，可以起服务时加此参数--read-only，也可以加在配置文件中 --read-only参数更新条件 a、具有super权限的用户可以更新 b、来自从服务器线程可以更新 演示效果 node81配置文件中加入后重启服务 [mysqld] read-only 在master(node80)端 mysql> grant insert,update,create on gtms.* to web1@'192.168.0.%' identified by "web1"; #此用户也会被复制到slave端 Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) [root@node80 ~]# mysql -uweb1 -pweb1 -h 192.168.0.81 -P3306 #测试在slave端写入数据，显示失败 mysql> use gtms Database changed mysql> insert into test values(5,"王八",13701800008); ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement [root@node81 ~]# mysql -uweb1 -pweb1 -h 192.168.0.80 -P3306 #测试在master端写入数据，显示成功，数据（检查略）也同步成功 mysql> use gtms Database changed mysql> insert into test values(5,"王八",13701800008); Query OK, 1 row affected (0.20 sec)
主从环境部署
环境
| IP | 操作系统版本 | 数据库版本 |
| 192.168.0.80(node80)master | CentOS6.7*64 | mysql5.5.49 |
| 192.168.0.81(node81)slave | CentOS6.7*64 | mysql5.5.49 |
字符集环境
mysql> show variables like "%char%"; +--------------------------+----------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql5.5.49/share/charsets/ | +--------------------------+----------------------------------------+
在node80创建测试数据
mysql> create database gtms CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.00 sec) mysql> use gtms Database changed mysql> create table test( id int (4), name varchar(16), phonenum char(12) ) ENGINE=innodb default charset=utf8; Query OK, 0 rows affected, 2 warnings (0.07 sec) mysql> insert into test values(1,"张三","13701800001"),(2,"王五","13701800002"),(3,"赵六","13701800003"); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from test; +------+--------+-------------+ | id | name | phonenum | +------+--------+-------------+ | 1 | 张三 | 13701800001 | | 2 | 王五 | 13701800002 | | 3 | 赵六 | 13701800003 | +------+--------+-------------+ 3 rows in set (0.00 sec)
开始配置主从
1、my.cnf中修改master端server-id设置为1，log_bin开启。slave设置server-id为2。并在master端创建replication账号
[root@node80 ~]# grep server-id /etc/my.cnf server-id = 1 [root@node81 ~]# grep server-id /etc/my.cnf server-id = 2
mysql> grant replication slave on *.* to rep@'192.168.0.%' identified by 'rep'; Query OK, 0 rows affected (0.07 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
2、导出master端的备份，应用到slave上，并查看binlog位置点
倒入备份时，可先把从的logbin关掉 set sql_log_bin=0 之后set sql_log_bin=1
[root@node80 ~]# mysqldump -uroot -prootabcd -A -B --master-data=2 --events --single-transaction | gzip >/tmp/node80.sql.gz [root@node81 ~]# gunzip /tmp/node80.sql.gz [root@node81 ~]# mysql -uroot -prootabcd </tmp/node80.sql [root@node81 ~]# head -23 /tmp/node80.sql | tail -2 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=4876;
3、在slave端进行CHANGE MASTER操作,并查看slave状态
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.0.80', -> MASTER_USER='rep', -> MASTER_PASSWORD='rep', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000008', -> MASTER_LOG_POS=4876; Query OK, 0 rows affected (0.12 sec) mysql> start slave; Query OK, 0 rows affected (0.04 sec)
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Queueing master event to the relay log Master_Host: 192.168.0.80 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 5098 Relay_Log_File: node81-relay-bin.000681 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000008 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: 5098 Relay_Log_Space: 556 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.01 sec)
或者
[root@node81 ~]# mysql -uroot -prootabcd -e "show slave status\G" | egrep "Slave|Seconds_Behind_Master" Slave_IO_State: Queueing master event to the relay log Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
此部操作后，在salve端会生成master.info及relay-bin
-rw-rw---- 1 mysql mysql 75 Mar 16 20:12 master.info -rw-rw---- 1 mysql mysql 303 Mar 16 20:12 node81-relay-bin.068770 -rw-rw---- 1 mysql mysql 303 Mar 16 20:12 node81-relay-bin.068771 -rw-rw---- 1 mysql mysql 107 Mar 16 20:12 node81-relay-bin.068772 -rw-rw---- 1 mysql mysql 52 Mar 16 20:12 node81-relay-bin.index
4、测试从master写入数据，slave进行验证
master端
mysql> use gtms Database changed mysql> insert into test values(1,"李四","13701800004"); Query OK, 1 row affected (0.00 sec)
slave端
mysql> select * from test; +------+--------+-------------+ | id | name | phonenum | +------+--------+-------------+ | 1 | 张三 | 13701800001 | | 2 | 王五 | 13701800002 | | 3 | 赵六 | 13701800003 | | 1 | 李四 | 13701800004 | +------+--------+-------------+ 4 rows in set (0.00 sec)
=======================从salve(node81)端再级联一个slave（node82）重要步骤如下
1、在node81上的my.cnf配置如下参数，node82的my.cnf的server-id设置为3
[mysqld] log-slave-updates #slave端作为其他mysql的master端，需要加此参数 log-bin=mysql-bin
2、导出node81备份应用到node82上，并查看binlog位置点(rep账号之前已经同步至node81)
3、CHANGE MASTER TO
4、start slave
========================================================
工作中mysql从库停止复制的故障案列
模拟故障，先在从库创建一个库，再在主库创建同名库来模拟冲突，从库会产生如下报错
mysql> show slave status\G; Slave_SQL_Running: No Last_Error: Error 'Can't create database 'data3308'; database exists' on query. Default database: 'data3308'. Query: 'create database data3308' Last_SQL_Errno: 1007 Last_SQL_Error: Error 'Can't create database 'data3308'; database exists' on query. Default database: 'data3308'. Query: 'create database data3308'
解决方案
mysql> stop slave; mysql> set global sql_slave_skip_counter=1; mysql> start slave;
#配置文件中的slave-skip-errors设置可设置忽略的错误号（需重启mysql服务）
slave-skip-errors = 1007,1062,1032,1008 可根据实际情况将可以忽略的报错写在配置文件中
半同步配置 mysql5.5开始，MySQL以插件的形式支持半同步复制
配置半同步
[root@node80 ~]# ls /usr/local/mysql/lib/plugin/semi* /usr/local/mysql/lib/plugin/semisync_master.so /usr/local/mysql/lib/plugin/semisync_slave.so mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; Query OK, 0 rows affected (0.18 sec) mysql> show plugins; #出现如下条目 | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | mysql> show global variables like '%semi%'; #出现如下条目 +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | OFF | #默认关闭 | rpl_semi_sync_master_timeout | 10000 | #可以调到1000，超时后会自动转为异步 | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; mysql> SET GLOBAL rpl_semi_sync_master_timeout= 1000; 写到配置文件中 [mysqld] rpl_semi_sync_master_enabled = 1 rpl_semi_sync_master_timeout= 1000 # second 等待salve端配置完成并重启后 mysql> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | #slave重启后被置1 | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+
slave端操作： mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; Query OK, 0 rows affected (0.27 sec) mysql> show plugins; #出现如下条目 | rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL | mysql> show global variables like '%semi%'; #出现如下条目 +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1; #默认是关闭的 写到配置文件中 [mysqld] rpl_semi_sync_slave_enabled = 1 重启io线程或直接stop slave start slave mysql> show global status like '%semi%'; | Rpl_semi_sync_slave_status | ON |
取消半同步
SET GLOBAL rpl_semi_sync_master_enabled = 0; UNINSTALL PLUGIN rpl_semi_sync_master; SET GLOBAL rpl_semi_sync_slave_enabled = 0; UNINSTALL PLUGIN rpl_semi_sync_slave; 删除配置文件中相关内容
