目前，大部分的主流关系型数据库都提供主从热备功能，通过配置两台 (或多台) 数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库的这一功能，实现数据库的读写分离，从而改善数据库的负载压力
利用数据库的读写分离，Web服务器在写数据的时候，访问主数据库 (Master)，主数据库通过主从复制机制将数据更新同步到从数据库 (Slave)，这样当 Web服务器读数据的时候，就可以通过 从数据库 获得数据。这一方案使得在大量读操作的Web应用可以轻松地读取数据，而主数据库也只会承受少量的写入操作，还可以实现数据热备份，可谓是一举两得的方案
注 : MySQL 读写分离实现 一般使用阿里巴巴开源项目Amoeba(变形虫)项目实现，或者使用基于MySQL-Proxy做调度服务器模式，另外就是自己写一个类似于哈希算法的程序库来选择目标数据库
MySQL数据复制原理
有如下三步骤 :
1> Master节点 将改变记录到二进制日志(binary log)中 (这些记录叫做二进制日志事件，binary log events，可以通过show binlog events进行查看)
2> Slave 将 Master 的二进制日志事件(binary log events)拷贝到它的中继日志(relay log)
注 : Slave服务器中有一个I/O线程(I/O Thread)在不停地监听Master的二进制日志(Binary Log)是否有更新，如果没有它会睡眠等待Master产生新的日志事件；如果有新的日志事件(Log Events)，则会将其拷贝至Slave服务器中的中继日志(Relay Log)
3> Slave重做中继日志(Relay Log)中的事件，将Master上的改变反映到它自己的数据库中
注 : Slave服务器中有一个SQL线程(SQL Thread)从中继日志读取事件，并重做其中的事件从而更新Slave的数据，使其与Master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小
主节点开启 binary log
1> 创建用户
mysql> SELECT user,host FROM mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| test | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)
mysql> CREATE user 'a'@'192.168.139.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user,host FROM mysql.user;
+---------------+---------------+
| user | host |
+---------------+---------------+
| root | % |
| test | % |
| a | 192.168.139.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+---------------+
6 rows in set (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO a@'192.168.139.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
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 (0.00 sec)
log_bin 的值是 OFF，表时数据库未开启 binary log，另外 MySQL 也是默认不开启 binary log
注意开启 3306端口
# firewall-cmd --permanent --add-port=3306/tcp
# firewall-cmd --reload
2> 配置 主服务器 : 修改 MySQL 配置文件 my.cnf，增加 log-bin 和 server-id 两个配置信息
# vi /etc/my.cnf
# For advice on how to change settings please see
[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 = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=utf8
collation-server=utf8_general_ci
validate_password=off
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_output=file,table
general_log=ON
general_log_file=/var/lib/mysql/mysql.general_log
log-bin=mysql-bin # 启用二进制日志，可以是 /var/lib/mysql/mysql-bin
server-id=131 # 服务器唯一ID，可以取IP最后一段
3> 重启 MySQL
# systemctl restart mysqld
4> 登陆 MySQL
# mysql -uroot -p
5> 授权给 slave服务器，如果授权失败将会报错
mysql> GRANT REPLICATION SLAVE ON *.* to a@'192.168.139.%' identified by '123456';
6> 查询主数据库状态
mysql-bin.000001 : 日志文件的数据文件
mysql-bin.index : 日志文件的索引文件
mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
这里需要记录 File 以及 Position 的值，在配置从服务器时会用到
查看 binary log 文件中的数据
# cd /var/lib/mysql
# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.000001 mysql.sock private_key.pem server-key.pem test
ca-key.pem client-key.pem ib_logfile0 metastore mysql-bin.index mysql.sock.lock public_key.pem solr
ca.pem ib_buffer_pool ib_logfile1 mysql mysql.general_log performance_schema server-cert.pem sys
# cat mysql-bin.index
./mysql-bin.000001
# mysqlbinlog mysql-bin.000001 # 通过 mysqlbinlog 来查看 binary log 数据
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180308 20:38:06 server id 131 end_log_pos 123 CRC32 0xa28a0f16 Start: binlog v 4, server v 5.7.20-log created 180308 20:38:06 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
ri6hWg+DAAAAdwAAAHsAAAABAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACuLqFaEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ARYPiqI=
'/*!*/;
# at 123
#180308 20:38:06 server id 131 end_log_pos 154 CRC32 0x42e82495 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 -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW BINLOG EVENTS; # 也可用于查看二进制日志 数据
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 131 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 131 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
7> 创建测试数据库
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE user(id INT, name VARCHAR(10));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO user VALUES(1,'xiaoming');
Query OK, 1 row affected (0.01 sec)
mysql> SHOW BINLOG EVENTS; # binary log 将会记录数据库的所有操作
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 131 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 131 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 131 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 131 | 412 | use `test`; CREATE USER 'a'@'192.168.139.%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| mysql-bin.000001 | 412 | Anonymous_Gtid | 131 | 477 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 477 | Query | 131 | 708 | use `test`; GRANT REPLICATION SLAVE ON *.* TO 'a'@'192.168.139.%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| mysql-bin.000001 | 708 | Anonymous_Gtid | 131 | 773 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 773 | Query | 131 | 865 | drop database test |
| mysql-bin.000001 | 865 | Anonymous_Gtid | 131 | 930 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 930 | Query | 131 | 1024 | CREATE DATABASE test |
| mysql-bin.000001 | 1024 | Anonymous_Gtid | 131 | 1089 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1089 | Query | 131 | 1206 | use `test`; CREATE TABLE user(id INT, name VARCHAR(10)) |
| mysql-bin.000001 | 1206 | Anonymous_Gtid | 131 | 1271 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1271 | Query | 131 | 1343 | BEGIN |
| mysql-bin.000001 | 1343 | Table_map | 131 | 1393 | table_id: 254 (test.user) |
| mysql-bin.000001 | 1393 | Write_rows | 131 | 1442 | table_id: 254 flags: STMT_END_F |
| mysql-bin.000001 | 1442 | Xid | 131 | 1473 | COMMIT /* xid=53 */ |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
17 rows in set (0.00 sec)
查看 MySQL 的 binary log
# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180308 20:38:06 server id 131 end_log_pos 123 CRC32 0xa28a0f16 Start: binlog v 4, server v 5.7.20-log created 180308 20:38:06 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
ri6hWg+DAAAAdwAAAHsAAAABAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACuLqFaEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ARYPiqI=
'/*!*/;
# at 123
#180308 20:38:06 server id 131 end_log_pos 154 CRC32 0x42e82495 Previous-GTIDs
# [empty]
# at 154
#180308 21:16:23 server id 131 end_log_pos 219 CRC32 0x3d2b2a4e Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180308 21:16:23 server id 131 end_log_pos 412 CRC32 0x26c47064 Query thread_id=3 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1520514983/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE USER 'a'@'192.168.139.%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
/*!*/;
# at 412
#180308 21:20:28 server id 131 end_log_pos 477 CRC32 0xd60efea4 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 477
#180308 21:20:28 server id 131 end_log_pos 708 CRC32 0xec5194b0 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1520515228/*!*/;
GRANT REPLICATION SLAVE ON *.* TO 'a'@'192.168.139.%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
/*!*/;
# at 708
#180308 21:41:35 server id 131 end_log_pos 773 CRC32 0x6f225dbe Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 773
#180308 21:41:35 server id 131 end_log_pos 865 CRC32 0xc88d1fc8 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1520516495/*!*/;
drop database test
/*!*/;
# at 865
#180308 21:42:03 server id 131 end_log_pos 930 CRC32 0xec736620 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 930
#180308 21:42:03 server id 131 end_log_pos 1024 CRC32 0xf57895a6 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1520516523/*!*/;
CREATE DATABASE test
/*!*/;
# at 1024
#180308 21:42:50 server id 131 end_log_pos 1089 CRC32 0xecce2ffb Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1089
#180308 21:42:50 server id 131 end_log_pos 1206 CRC32 0x1754939f Query thread_id=3 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1520516570/*!*/;
CREATE TABLE user(id INT, name VARCHAR(10))
/*!*/;
# at 1206
#180308 21:43:49 server id 131 end_log_pos 1271 CRC32 0xd82a62d9 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1271
#180308 21:43:49 server id 131 end_log_pos 1343 CRC32 0xa7d21e22 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1520516629/*!*/;
BEGIN
/*!*/;
# at 1343
#180308 21:43:49 server id 131 end_log_pos 1393 CRC32 0x8053746e Table_map: `test`.`user` mapped to number 254
# at 1393 # 这一整块就是一个事件
#180308 21:43:49 server id 131 end_log_pos 1442 CRC32 0x363dee3e Write_rows: table id 254 flags: STMT_END_F
BINLOG '
FT6hWhODAAAAMgAAAHEFAAAAAP4AAAAAAAEABHRlc3QABHVzZXIAAgMPAh4AA250U4A=
FT6hWh6DAAAAMQAAAKIFAAAAAP4AAAAAAAEAAgAC//wBAAAACHhpYW9taW5nPu49Ng==
'/*!*/;
# at 1442
#180308 21:43:49 server id 131 end_log_pos 1473 CRC32 0xce418da2 Xid = 53
COMMIT/*!*/;
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*/;
如上蓝色标记所示，就是一个事件，事件由两部分构成 :
<1> 事件头
<2> 事件体
每次重启 mysqld 服务，服务器都会调用 flush logs，之后会重新创建一个新的 binlog日志
# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.000001 mysql.sock private_key.pem server-key.pem test
ca-key.pem client-key.pem ib_logfile0 metastore mysql-bin.index mysql.sock.lock public_key.pem solr
ca.pem ib_buffer_pool ib_logfile1 mysql mysql.general_log performance_schema server-cert.pem sys
# systemctl restart mysqld
# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.000001 mysql.general_log performance_schema server-cert.pem sys
ca-key.pem client-key.pem ib_logfile0 metastore mysql-bin.000002 mysql.sock private_key.pem server-key.pem test
ca.pem ib_buffer_pool ib_logfile1 mysql mysql-bin.index mysql.sock.lock public_key.pem solr
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000002'; # 查看指定 binlog 中的日志信息
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: test
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 131 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 131 | 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.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> SHOW MASTER LOGS; # 查看所有的日志文件(相当于查看索引文件)
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1496 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> RESET MASTER; # 清空所有的日志文件
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
使用 binlog 对误删除数据进行恢复
mysql> INSERT INTO user(id,name) VALUES(2,'孙大圣'),(3,'小飞侠');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROm user;
+------+-----------+
| id | name |
+------+-----------+
| 1 | xiaoming |
| 2 | 孙大圣 |
| 3 | 小飞侠 |
+------+-----------+
3 rows in set (0.00 sec)
mysql> FLUSH LOGS; # 刷新日志文件，会产生一个新的日志文件
Query OK, 0 rows affected (0.01 sec)
mysql> DELETE FROM user WHERE id >=2;
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT *FROM user;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+
1 row in set (0.00 sec)
# 使用 binlog 恢复数据
# cd /var/lib/mysql
# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.000001 mysql.general_log performance_schema server-cert.pem sys
ca-key.pem client-key.pem ib_logfile0 metastore mysql-bin.000002 mysql.sock private_key.pem server-key.pem test
ca.pem ib_buffer_pool ib_logfile1 mysql mysql-bin.index mysql.sock.lock public_key.pem solr
# mysqlbinlog mysql-bin.000001 | mysql -uroot -p # 该操作将会恢复所有误删除的数据
Enter password:
# 查看数据库数据，会发现误删除的数据已经恢复
mysql> SELECt * FROM user;
+------+-----------+
| id | name |
+------+-----------+
| 1 | xiaoming |
| 2 | 孙大圣 |
| 3 | 小飞侠 |
+------+-----------+
3 rows in set (0.00 sec)
只恢复一部分数据
mysql> DELETE FROM user WHERE id>1;
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM user;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+
1 row in set (0.00 sec)
# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180308 22:02:12 server id 131 end_log_pos 123 CRC32 0xcd9e67ce Start: binlog v 4, server v 5.7.20-log created 180308 22:02:12 at startup
ROLLBACK/*!*/;
BINLOG '
ZEKhWg+DAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABkQqFaEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Ac5nns0=
'/*!*/;
# at 123
#180308 22:02:12 server id 131 end_log_pos 154 CRC32 0x55555313 Previous-GTIDs
# [empty]
# at 154
#180308 22:05:35 server id 131 end_log_pos 219 CRC32 0x900c89a2 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180308 22:05:35 server id 131 end_log_pos 291 CRC32 0xf3c9d3dc Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1520517935/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#180308 22:05:35 server id 131 end_log_pos 341 CRC32 0xbe57d1f8 Table_map: `test`.`user` mapped to number 249
# at 341
#180308 22:05:35 server id 131 end_log_pos 406 CRC32 0x591799bd Write_rows: table id 249 flags: STMT_END_F
BINLOG '
L0OhWhODAAAAMgAAAFUBAAAAAPkAAAAAAAEABHRlc3QABHVzZXIAAgMPAh4AA/jRV74=
L0OhWh6DAAAAQQAAAJYBAAAAAPkAAAAAAAEAAgAC//wCAAAACeWtmeWkp+Wco/wDAAAACeWwj+mj
nuS+oL2ZF1k=
'/*!*/;
# at 406
#180308 22:05:35 server id 131 end_log_pos 437 CRC32 0x04fd3b2e Xid = 17
COMMIT
/*!*/;
# at 437
#180308 22:06:18 server id 131 end_log_pos 484 CRC32 0x0015c2c8 Rotate to mysql-bin.000002 pos: 4
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*/;
# mysqlbinlog mysql-bin.000001 --start-position 219 --stop-position 437 | mysql -uroot -p
# 将会查看的数据已经恢复
mysql> select * FROM user;
+------+-----------+
| id | name |
+------+-----------+
| 1 | xiaoming |
| 2 | 孙大圣 |
| 3 | 小飞侠 |
+------+-----------+
3 rows in set (0.00 sec)
配置从节点 进行 主从复制
主从步骤 : 1> 配置主节点 : 创建用户，赋予权限，开启 binlog 日志
2> 配置从节点 : 配置同步日志，指定主节点的 ip、端口、用户等信息，启动从节点
1> 在主库中创建用户，可被从节点进行远程登录
mysql> CREATE user 'a'@'172.17.0.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user,host FROM mysql.user;
+---------------+---------------+
| user | host |
+---------------+---------------+
| root | % |
| test | % |
| a | 172.17.0.% |
| a | 192.168.139.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+---------------+
7 rows in set (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'a'@'172.17.0.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 确定 Binlog 已经开启
mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)
2> 配置 Slave服务器
log-bin=mysql-bin # 启用二进制日志
server-id=132 # 服务器唯一ID，可以取IP最后一段
# vi /etc/my.cnf
# For advice on how to change settings please see
[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 = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=utf8
collation-server=utf8_general_ci
validate_password=off
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_output=file,table
general_log=ON
general_log_file=/var/lib/mysql/mysql.general_log
log-bin=mysql-bin
server-id=132
重启 MySQL
# systemctl restart mysqld
登陆 mysql
# mysql -uroot -p
执行同步SQL语句
mysql> change master to master_host='192.168.139.131',master_port=3306,master_user='a',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
启动Slave 同步进程
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
主从同步检查，查看从节点信息
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.139.131
Master_User: a
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 7485
Relay_Log_File: 5b5fdbad6693-relay-bin.000006
Relay_Log_Pos: 320
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: 7485
Relay_Log_Space: 8078
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: 131
Master_UUID: 2fa54e59-cedd-11e7-92ec-000c291d8370
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)
一定要保证 Slave_IO_Running 和 Slave_SQL_Running 都是 YES ，这样当 主数据库 进行数据库操作时，从数据 也会有相应的数据，相对的操作从库不会对主库产生影响
常见错误 :
1> ERROR] Slave I/O: error connecting to master 'replication@VMS00782:3306' - retry-time: 60 retries: 2, Error_code: 1045
错误原因 : 从库用来连接主库的用户权限或者密码不对
解决方法 : 首先在主库上检查用来主从复制的用户权限，如果没有问题在检查从库使用的密码是否正确
2> 140331 10:08:18 [ERROR] Error reading master configuration
140331 10:08:18 [ERROR] Failed to initialize the master info structure
140331 10:08:18 [Note] Event Scheduler: Loaded 0 events
错误原因 : 这个可能是从库的 master.info文件有损坏
解决方法 : reset slave
3> Error 'Duplicate entry '1' for key 1' on query. Default database: 'movivi1'. Query: 'INSERT INTO `v1vid0_user_samename` VALUES(null,1,'123','11','4545','123')'
错误原因 : 可能是从库的约束比主库更多写造成的
解决方法 :
mysql> stop slave;
mysql> set global sql_slave_skip_counter =1 ;
mysql> start slave;
4> Slave SQL: Error 'Table 'xxxx' doesn't exist' on query. Default database: 't591'. Query: 'INSERT INTO `xxxx`(type,post_id,browsenum) SELECT type,post_id,browsenum FROM xxxx WHERE hitdate='20090209'', Error_code: 1146
错误原因 : slave上缺少错误中的表
解决方法 : 在slave上添加上对应的表，然后 start slave
5> Error 'Unknown column 'qdir' in 'field list'' on query. Default database: 'club'. Query: 'insert into club.question_del (id, pid, ques_name, givepoint, title, subject, subject_pid, createtime, approve, did, status, intime, order_d, endtime,banzhu_uid,banzhu_uname,del_cause,qdir) select id, pid, ques_name, givepoint, title, subject, subject_pid, createtime, approve, did, status, intime, order_d, endtime,'1521859','admin0523','无意义回复',qdir from club.question where id=7330212'
错误原因 : 从库上对应的表上缺少字段
解决方法 : 根据主库上表结构，在从库对应表上添加缺少的字段，然后start slave
6> Last_Errno: 1146
Last_Error: Error executing row event: 'Table 'panda.t' doesn't exist'
错误原因 : 主库删除的表在从库中不存在，导致从库在遇到删除不存在表的错误时无法继续同步。
解决方法 : 利用 slave-skip-errors参数，跳过对于的1146错误 (这个参数是一个只读的，需要在配置文件中修改，并重启从库)
<1> 在 my.cnf 的 [mysqld] 下面添加 slave_skip_errors=1146
<2> 重启从库 service mysql restart
<3> 在从库上启动同步
mysql> start slave;
<4> 去掉my.cnf中的slave_skip_errors=1146
<5> 重启从库
<6> 启动从库复制
2956
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
