复制介绍
MySQL复制:
- MySQL replication=异步复制
- 完全同步复制,PXC是同步复制--master和slave,没有区别
复制是单向的,,只能从master复制到slave上
slave上对于master包含的数据不能进行写操作
一组复制结构中可以有多个slave,对于master一般场景推荐只有一个
原理:
1.master用户写入数据,生成event记到binary log中
2.slave I/O thread接收master传来的binlog写入relay log中
3.slave SQL thread按顺序应用,重现master上的用户操作
slave 在做日志重放的是串行的io线程是一个(是串行的),sql线程和db一样多(5.6基于库的并行同步)
复制的使用价值
利用从库做读能力的提升
利用从库做master故障的接管
利用从库做备份减少对业务的影响
利用复制升级
利用slave进行特殊SQL统计
环境规范
1.1组主从里面端口号要一致
2.要求是每个实例上的server-id为IP最后一位+端口号
3.主从尽可能是版本一致，严禁主的版本高从的版本低，binlog传输到slave有可能应用不了
4.复制建一个帐号：grant replication slave on *.* to 'repl'@'%' identified by 'repl4slave';
5.拿到主库的一致性备份
/usr/local/mysql/bin/mysqldump -u root -p -h 127.0.0.1 --master-data=2 --single-transaction --databases glc > dumpglc.sql general_log内容：###########################160421 14:49:51 2 Connect root@127.0.0.1 on 2 Connect Access denied for user 'root'@'127.0.0.1' (using password: YES)160421 14:50:13 3 Connect root@127.0.0.1 on 3 Query /*!40100 SET @@SQL_MODE='' */ #设置SQL模式 3 Query /*!40103 SET TIME_ZONE='+00:00' */ #设置时区 3 Query SHOW STATUS LIKE 'binlog_snapshot_%' #查看当前binlog文件名及pos点，该信息用于复制。（ --master-data=2 参数） 3 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ #设置当前会话的事物隔离级别为RR，确保本次会话(dump)时，不会看到其他会话已经提交了的数据。 3 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 3 Query SHOW VARIABLES LIKE 'gtid\_mode' #查看gtid模式状态 3 Query SHOW STATUS LIKE 'binlog_snapshot_%' #查看当前binlog文件名及pos 3 Query UNLOCK TABLES #对表进行解锁 3 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG'AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('glc'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME #做什么用？ 3 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('glc')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME #做什么用？ 3 Query SHOW VARIABLES LIKE 'ndbinfo\_version' 3 Init DB glc 3 Query SHOW CREATE DATABASE IF NOT EXISTS `glc` 3 Query SAVEPOINT sp 3 Query show tables 3 Query show table status like 'MyClass' 3 Query SET SQL_QUOTE_SHOW_CREATE=1 #sql_quote_show_create，有两个值（1，0），默认是1，表示表名和列名会用``包着的。这个服务器参数只可以在session级别设置，不支持global设置的(不支持my.cnf设置)。 3 Query SET SESSION character_set_results = 'binary' #设置会话级别的查询结果字符集 3 Query show create table `MyClass` 3 Query SET SESSION character_set_results = 'utf8' #设置会话级别的查询结果字符集 3 Query show fields from `MyClass` 3 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `MyClass` #获取表的数据，SQL_NO_CACH 确保不会读取缓存里的数据 3 Query SET SESSION character_set_results = 'binary' 3 Query use `glc` 3 Query select @@collation_database #查看排序 3 Query SHOW TRIGGERS LIKE 'MyClass' #查看触发器 3 Query SET SESSION character_set_results = 'utf8' #设置查询结果集 3 Query ROLLBACK TO SAVEPOINT sp #回滚到 SAVEPOINT sp 3 Query show table status like 't1' 3 Query SET SQL_QUOTE_SHOW_CREATE=1 3 Query SET SESSION character_set_results = 'binary' 3 Query show create table `t1` 3 Query SET SESSION character_set_results = 'utf8' 3 Query show fields from `t1` 3 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1` 3 Query SET SESSION character_set_results = 'binary' 3 Query use `glc` 3 Query select @@collation_database 3 Query SHOW TRIGGERS LIKE 't1' 3 Query SET SESSION character_set_results = 'utf8' 3 Query ROLLBACK TO SAVEPOINT sp 3 Query RELEASE SAVEPOINT sp 3 Quit Dump表结构的时间字符集改成SET SESSION character_set_results = 'binary' 3 Query use `glc` 3 Query select @@collation_database 3 Query SHOW TRIGGERS LIKE 't1' 3 Query SET SESSION character_set_results = 'utf8' 3 Query ROLLBACK TO SAVEPOINT sp mysqldump --master-data=2 --single-transaction 简单的主从配置
基本环境介绍 MySQL版本：MySQL5.6.27
角色
Ip：port
Server-id
必备条件
Master
192.168.247.12
123316
启用log-bin主库上创建复制用户
Slave
192.168.247.27
273316
核心配置
Master View Code
log-binserver-id#禁掉gtidgtid_mode=off主库上创建复制用户grant replication slave on *.* to ‘repl’@‘%’ identified by ‘’flush privileges# mysqldump -S /tmp/mysql3316.sock --master-data=2 --single-transaction -A >20160516full.sql# scp 20160516full.sql 192.168.247.12:/tmp/
Slave
# mysql -S /tmp/mysql3316.sock show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.247.12 Master_User: repl Master_Port: 3316 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 425 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 493 Relay_Master_Log_File: mysql-bin.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: 425 Relay_Log_Space: 666 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: 123316 Master_UUID: f9f3216c-1865-11e6-b1f4-000c29b01c31 Master_Info_File: /data/mysql/mysql3316/data/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)ERROR:No query specified