为什么要做主从复制？
在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。通过做主从复制(读写分离)来减轻主数据库的负载
做数据的热备
业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。如果对数据库的读和写都在同一个数据库服务器中操作，业务系统性能会降低
主从复制的原理
- 主库db的更新事件(update、insert、delete)被写到binlog
- 从库发起连接，连接到主库
- 此时主库创建一个binlog dump thread，把binlog的内容发送到从库
- 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
- 还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db
注：上面的解释是解释每一步做了什么，整个mysql主从复制是异步的，不是按照上面的步骤执行的。
同步操作通过 3 个线程实现，其基本步骤如下：
主服务器将数据的更新记录到二进制日志中(记录被称作二进制日志事件)-- 主库线程；
从库将主库的二进制日志复制到本地的中继日志(relay log)-- 从库 I/O 线程；
从库读取中继日志中的事件，将其重放到数据中 -- 从库 SQL 线程。
本篇博客记录的是一主一从模式
环境搭建
准备工作
- 主从数据库版本最好一致
- 主从数据库内数据保持一致
- 主数据库：192.168.190.131 从数据库：192.168.190.132
配置主库Master
修改MySQL配置
mysql查看默认配置文件：mysql --help|grep 'my.cnf'，找到主数据库的配置文件my.cnf，我的在/etc/my.cnf
# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0 # Recommended in standard MySQL setupsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #主服务器唯一IDserver-id=1#设置需要复制的数据库binlog-do-db=testdb#二进制日志#配置开启 binlog日志,日志的文件前缀为mysqlbin生成的文件名如: mysqlbin.000001, mysqlbin.000002log_bin=mysqlbin#配置二进制日志的格式binlog_format=STATEMENT#自动删除的过期天数expire_logs_days=3 #该选项用来开启查询日志，可选值：0关闭;1开启general_log=1#设置日志的文件名,如果没有指定,默认的文件名为host_name.loggeneral_log_file=mysql_query.log #该参数用来控制慢查询日志是否开启：1开启,0关闭slow_query_log=1#该参数用来指定慢查询日志的文件名slow_query_log_file=slow_query.log#该选项用来配置查询的时间限制,超过这个时间将认为是慢查询,需要进行日志记录,默认是10slong_query_time=3 [mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid创建用户
重启服务，创建用于同步的用户，用户：salve密码：123456
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';查看master状态
mysql> show master status;+-----------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-----------------+----------+--------------+------------------+-------------------+| mysqlbin.000007 | 2400 | testdb | | |+-----------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)配置从库Slave
修改MySQL配置
# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0 # Recommended in standard MySQL setupsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #从服务器唯一IDserver-id=2#启用中继日志relay-log=mysql-relay#二进制日志#配置开启 binlog日志,日志的文件前缀为mysqlbin生成的文件名如: mysqlbin.000001, mysqlbin.000002log_bin=mysqlbin#配置二进制日志的格式binlog_format=STATEMENT#自动删除的过期天数expire_logs_days=3 #该选项用来开启查询日志，可选值：0关闭;1开启general_log=1#设置日志的文件名,如果没有指定,默认的文件名为host_name.loggeneral_log_file=mysql_query.log #该参数用来控制慢查询日志是否开启：1开启,0关闭slow_query_log=1#该参数用来指定慢查询日志的文件名slow_query_log_file=slow_query.log#该选项用来配置查询的时间限制,超过这个时间将认为是慢查询,需要进行日志记录,默认是10slong_query_time=3 [mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.190.131', -> MASTER_USER='salve', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mysqlbin.000007', -> MASTER_LOG_POS=2400;Query OK, 0 rows affected, 2 warnings (0.02 sec)启动slave同步进程
mysql>start slave;停止stop slave;重新配置主从reset master；
查看slave状态
mysql> show slave statusG*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.190.131 Master_User: salve Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysqlbin.000007 Read_Master_Log_Pos: 2400 Relay_Log_File: mysql-relay.000003 Relay_Log_Pos: 2277 Relay_Master_Log_File: mysqlbin.000007 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: 2400 Relay_Log_Space: 2446 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: 0Master_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: 9510d25d-7d7b-11ea-9a71-000c29980d35 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 the slave I/O thread to update it 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: 01 row in set (0.00 sec)当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。如果是虚拟机克隆的情况下，注意修改/var/lib/mysql/auto.cnf中的uuid
注意事项
master主节点
在主节点的二进制事件日志中仅记录与指定数据库(数据表)相关的事件日志，但是主节点的二进制日志不完整，没有记录所有对主节点的修改操作。如果要使用该方式，则在主节点的配置文件中添加如下参数:
binlog_do_db="***,***,***"; #数据库白名单列表,二进制日志记录的数据库(多数据库用逗号隔开或重复设置多行),即需要同步的库.不在内的不同步。(不添加这行表示同步所有)binlog_ingore_db="***,***,***"; #数据库黑名单列表, 二进制日志中忽略的数据库 (多数据库用逗号隔开或重复设置多行),即不需要同步,要过滤掉的库.slave从节点
从服务器SQL Thread在Replay中继日志中的事件时仅读取于特定数据库相关的事件，并应用于本地. (但是浪费I/O ,浪费带宽) 推荐使用从节点复制过滤相关设置项:
replicate_do_db ="webdb"; #复制库的白名单. 设定需要复制的数据库(多数据库使用逗号隔开或重复设置多行)replicate_ingore_db ="mysql"; #复制库的黑名单. 设定需要忽略的复制数据库 (多数据库使用逗号隔开或重复设置多行)replicate_do_table="webdb.user"; #复制表的白名单. 设定需要复制的表(多数据库使用逗号隔开或重复设置多行)relicate_ingore_table="webdb.uw"; #复制表的黑名单. 设定需要忽略的复制的表(多数据库使用逗号隔开或重复设置多行) replicate-wild-do-table #同replication-do-table功能一样，但是可以通配符. 更高级别的应用，通配符,应用到哪一类表的。replicate-wild-ignore-table #同replication-ignore-table功能一样，但是可以加通配符.当在主库存在的库而从库不存在的库同步时,会出现sql错误,这时候可以排除或者从库手动导入主库数据库。从库可以使用通配符"库名.%"方式过滤主从同步时某个库的设置
replicate-wild-do-table=webdb.% #只复制webdb库下的所有表replicate-wild-ignore-table=mysql.% #忽略mysql库下的所有表 特别注意: 生产库上一般不建议设置过滤规则, 如果非要设置, 强烈建议从库使用通配符方式过滤某个库
replicate-wild-do-table= "库名.%"replicate-wild-ignore-table= "库名.%"而不建议从库使用DB方式过滤某个库
replicate_do_db ="库名"replicate_ingore_db ="库名"验证
在主服务器创建testdb数据库，新建mytbl表，插入几条数据，发现从服务器也能查询到testdb数据库，mytbl表，以及表中的数据
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
