MySQL分布式集群-1.主从复制
2019-06-04 本文已影响0人
笨鸡
1.MySQL用户和权限
mysql> select user, host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
待续
2.binlog日志详解
Binary log 二进制日志文件
- 增量备份
- 主从复制数据恢复
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set, 1 warning (0.00 sec)
设置my.ini
server_id=1
log-bin=C:/ProgramData/MySQL/MySQL Server 5.7/mysql-bin-log
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | C:\ProgramData\MySQL\MySQL Server 5.7\mysql-bin-log |
| log_bin_index | C:\ProgramData\MySQL\MySQL Server 5.7\mysql-bin-log.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)
- mysql-bin-log.000001 这个是日志文件的数据文件
- mysql-bin-log.index 这个是日志文件的索引文件
C:\ProgramData\MySQL\MySQL Server 5.7>mysqlbinlog mysql-bin-log.000001
mysql> show binlog events;
mysql> show binlog events in 'mysql-bin-log.000001';
C:\ProgramData\MySQL\MySQL Server 5.7>mysqlbinlog mysql-bin-log.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190604 20:52:51 server id 1 end_log_pos 123 CRC32 0x625b60e9 Start: binlog v 4, server v 5.7.21-log created 190604 20:52:51 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
o2n2XA8BAAAAdwAAAHsAAAABAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACjafZcEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AelgW2I=
'/*!*/;
# at 123
#190604 20:52:51 server id 1 end_log_pos 154 CRC32 0x6f54043e Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mysql> show binlog events;
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin-log.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| mysql-bin-log.000001 | 123 | Previous_gtids | 1 | 154 | |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin-log.000001';
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin-log.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| mysql-bin-log.000001 | 123 | Previous_gtids | 1 | 154 | |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
事件由两部分构成
1.事件头
2.事件体
Binlog的操作
- 每次服务器重启，服务器会调用flush logs，会新创建一个新的binlog日志。
- flush logs刷新日志文件，会产生一个新的日志文件。
- show binlog events in 'mysql-bin-log.000001'
- show master status 查看当前日志的状态
- show master logs 查看所有的日志文件（相当于查看索引文件）
- reset master 清空所有的日志文件
mysql> show binlog events;
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin-log.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| mysql-bin-log.000001 | 123 | Previous_gtids | 1 | 154 | |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin-log.000001';
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin-log.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| mysql-bin-log.000001 | 123 | Previous_gtids | 1 | 154 | |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-bin-log.000001 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show master logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| mysql-bin-log.000001 | 154 |
+----------------------+-----------+
1 row in set (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> show master logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| mysql-bin-log.000001 | 154 |
+----------------------+-----------+
1 row in set (0.00 sec)
3.binlog数据恢复
mysql> select * from myisam1 where id > 140;
+-----+--------+
| id | title |
+-----+--------+
| 141 | 李莫愁 |
| 142 | 令狐冲 |
| 143 | 风清扬 |
| 144 | 张无忌 |
| 145 | 李莫愁 |
+-----+--------+
5 rows in set (0.01 sec)
mysql> insert into myisam1 values (146, '任我行');
Query OK, 1 row affected (0.01 sec)
mysql> delete from myisam1 where id = 146;
Query OK, 1 row affected (0.01 sec)
mysql> show binlog events in 'mysql-bin-log.000001';
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin-log.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| mysql-bin-log.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin-log.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin-log.000001 | 219 | Query | 1 | 297 | BEGIN |
| mysql-bin-log.000001 | 297 | Table_map | 1 | 356 | table_id: 108 (mysql_test.myisam1) |
| mysql-bin-log.000001 | 356 | Write_rows | 1 | 406 | table_id: 108 flags: STMT_END_F |
| mysql-bin-log.000001 | 406 | Query | 1 | 485 | COMMIT |
| mysql-bin-log.000001 | 485 | Anonymous_Gtid | 1 | 550 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin-log.000001 | 550 | Query | 1 | 628 | BEGIN |
| mysql-bin-log.000001 | 628 | Table_map | 1 | 687 | table_id: 108 (mysql_test.myisam1) |
| mysql-bin-log.000001 | 687 | Delete_rows | 1 | 737 | table_id: 108 flags: STMT_END_F |
| mysql-bin-log.000001 | 737 | Query | 1 | 816 | COMMIT |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
12 rows in set (0.00 sec)
删除操作在 Pos = 687，操作警告消除 --no-default
C:\ProgramData\MySQL\MySQL Server 5.7>mysqlbinlog mysql-bin-log.000001 --stop-position 687 | mysql -u root -p
WARNING: The range of printed events ends with a row
event or a table map event that does not have the
STMT_END_F flag set. This might be because the last
statement was not fully written to the log, or because you are
using a --stop-position or --stop-datetime that refers to an
event in the middle of a statement. The event(s) from the
partial statement have not been written to output.
Enter password: **********
mysql> select * from myisam1 where id > 140;
+-----+--------+
| id | title |
+-----+--------+
| 141 | 李莫愁 |
| 142 | 令狐冲 |
| 143 | 风清扬 |
| 144 | 张无忌 |
| 145 | 李莫愁 |
| 146 | 任我行 |
+-----+--------+
6 rows in set (0.00 sec)
mysql> delete from myisam1 where id > 140;
Query OK, 6 rows affected (0.00 sec)
mysql> select * from myisam1 where id > 140;
Empty set (0.00 sec)
# mysqlbinlog 操作position 687
mysql> select * from myisam1 where id > 140;
+-----+--------+
| id | title |
+-----+--------+
| 146 | 任我行 |
+-----+--------+
1 row in set (0.00 sec)
