<服务>MySQL主从同步
目录：
MySQL主从同步
MySQL主从同步有利于提高架构提供数据服务，提升访问速度和易于维护管理。MySQL支持单向，双向，链式，实时和异步复制，一台充当主服务器，一台或者多台充当从服务器。可以进行单向或者双向的复制甚至环形同步。
MySQL主从同步方式
- 异步binlog同步(会丢数据)
- 双写
- 通过外部程序记录
- 实时同步
- google半同步机制
读写分离
通过主服务器写入，从服务器提供读取服务，进而实现对写入少，去读多的网站架构。 读写分离一般会在通过程序来进行实现。 测试环境一般用过代理软件来实现mysql-proxy或amoeba。
主从同步原理
前提：在主库需要开启binlog,建立用于从库同步的用户和密码，先在一个备份点，在从库导入备份数据，从库设定master信息并进行开启slave同步。
主从同步在主库端启用一个IO线程，从库启用一个IO线程和一个SQL线程，从库端的IO线程向主库发送请求，主库端负责复制的IO线程根据slave端的IO线程请求查找到对应POS点的binlog日志和这些binlog日志后一个位置点进行发送给客户端，在客户端把binlog追加到relay-log(中继日志)的末尾，并把后一个位置点更新master.info文件中的binlog日志名称并更新位置点，于此同时SQL线程在发现relay-log中的更新就会通过日志写入从库数据库中，然后从库客户端从matser.info中获取位置点继续向主库进行请求数据，进行循环获取，写入。不过在第一次提交的时候交给主进程用户名密码进行确认步骤，其他都是一致的。
另外从库复制过来后写入数据库文件并不写入log-bin(默认情况下)，可以修改log_slave_updates和binlog参数写入log-bin，就可以通过从库继续同步到从库。
通过主主同步，主从同步和级联同步即可以完成mysql官网的几种同步方式。
配置MySQL主从同步
我这边使用的是单机多实例的形式实现主从同步，也可以通过两台服务器进行操作，原理相同。 多实例配置可以参考<服务>数据库和MySQL服务多实例配置
环境准备
[root@why ~]# mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &
[1] 10637
[root@why ~]# mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &
[2] 11377
[root@why ~]# ss -nlput | grep 330
tcp LISTEN 0 128 *:3307 *:* users:(("mysqld",12075,11))
tcp LISTEN 0 128 *:3306 *:* users:(("mysqld",11359,12))
我这边以3306作为主库，3307作为从库进行实践
检查配置
[root@why ~]# egrep "log-bin|server-id" /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
server-id = 1
[root@why ~]# egrep "log-bin|server-id" /data/3307/my.cnf
#log-bin = /data/3307/mysql-bin
server-id = 3
[root@why ~]# mysql -S /data/3306/mysql.sock -e "show variables like 'log_bin'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
建立用于从库同步的用户和密码
[root@why ~]# mysql -S /data/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.32-log Source distribution
Copyright (c) 2000, 2013, 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> grant replication slave on *.* to 'why'@'192.168.0.130' identified by '123456';
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> flush table with read lock; #锁表
Query OK, 0 rows affected (0.00 sec)
锁表参数
- interactive_timeout = 60
- wait_timeout = 60
备份数据
查看当前数据
[root@why ~]# mysql -S /data/3306/mysql.sock -e "select * from why.student;"
+----+--------+-----+------+
| id | name | age | dept |
+----+--------+-----+------+
| 1 | why | 12 | dba |
| 2 | mabiao | 13 | dev |
| 3 | yanwei | 15 | dev |
+----+--------+-----+------+
[root@why ~]# mysqldump -S /data/3306/mysql.sock -A -B --events --master-data=2> why.sql
md5-1fdd1017369ffa916edd4e193ae449e7
[root@why ~]# vi why.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1335;
md5-d3b025e08c2b918ed6c5d8ec07ed4d54
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 1335 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
md5-6489742e9c78b9e1cd4a718b31ea13ca
mysql> unlock tables; #解除锁表
Query OK, 0 rows affected (0.00 sec)
md5-816443b205f10fbe3dda956489052376
[root@why ~]# mysql -S /data/3307/mysql.sock < why.sql
[root@why ~]# mysql -S /data/3304/mysql.sock -e "select * from why.student;"
+----+--------+-----+------+
| id | name | age | dept |
+----+--------+-----+------+
| 1 | why | 12 | dba |
| 2 | mabiao | 13 | dev |
| 3 | yanwei | 15 | dev |
+----+--------+-----+------+
md5-5eb30e121f38d565f1841f9f451e9d42
[root@why ~]# mysql -S /data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, 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> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.130',
-> MASTER_PORT=3306,
-> MASTER_USER='why',
-> MASTER_PASSWORD='123465',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=1335;
Query OK, 0 rows affected (0.04 sec)
md5-6a35209f1f5c72c288365d601b8ae7d6
[root@why ~]# cat /data/3307/data/master.info
18
mysql-bin.000002
1335
192.168.0.130
why
123465
3306
60
0
0
1800.000
0
md5-de574dd07e60e6114543c745db09b5ae
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
Slave_IO_Running: Yes 负责读取主库的BIN_LOG日志并写入中继日志中
Slave_SQL_Running: Yes 负责读取并执行中继日志的BIN_LOG,转换成SQL语句应用到数据库
Seconds_Behind_Master: 0 主从复制延迟时间
md5-436ff2a5c5b8f8b3e1ace1fff4baf157
show processlist;
mysql> show processlist;
+----+------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 3 | root | localhost | NULL | Sleep | 684 | | NULL |
| 7 | why | 192.168.0.182:39273 | NULL | Binlog Dump | 672 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 8 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
md5-2312b71e68b1567eed47ef8a1f0cdcc5
mysql> show processlist;
+----+-------------+-----------+----------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+----------+---------+------+-----------------------------------------------------------------------+------------------+
| 1 | root | localhost | why_utf8 | Sleep | 999 | | NULL |
| 2 | system user | | NULL | Connect | 1038 | Waiting for master to send event | NULL |
| 3 | system user | | NULL | Connect | 1038 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 6 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+-----------+----------+---------+------+-----------------------------------------------------------------------+------------------+
4 rows in set (0.09 sec)
md5-953dc4fdbb14462d350d4cc9c01f49f4
binlog-ignore-db=mysql,performance_schema,information_schem
md5-5323aa1ecc5ce2ee51c8ee1c80e0dedd
replicate-ignore-db=mysql,performance_schema,information_schem
md5-ac5e522b635613a5b468b22af9922df6
binlog-ignore-db
replicate-ignore-db
md5-cd6963ce757ca1c2fcc723c21c24a8e7
stop slave
set global sql_slave_skip_counter=1
start slave
md5-6408c0d9368f8abbbd83ba735882306a
auto_increment_increment = 2 自增id间隔
auto_increment_offset = 1 #id初始位置
log-bin = mysql-bin
log-slave-updates
md5-ab75a5a5c1dcb2a81558e2425c97f517
auto_increment_increment = 2 自增id间隔
auto_increment_offset = 2 #id初始位置
log-bin = mysql-bin
log-slave-updates
md5-8735ee0de87b49c8c80d219ce72d93e2
[root@why ~]# service mysqld restart
Shutting down MySQL..... [确定]
Starting MySQL..... [确定]
md5-c7b5962e1e5c48ac6693448eacc0655c
[root@mysql why]# mysqldump -uroot -p123456 -A -B --master-data=1 -x --events > 182bak.sql
md5-215c6e6e1536099b4df3ae191837b894
[root@why ~]# mysql -uroot -p123456 < 182bak.sql
md5-6c323ba1d418e5dfa599f09cb8b97d47
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.182',
-> MASTER_PORT=3306,
-> MASTER_USER='why',
-> MASTER_PASSWORD='oldboy123'
-> ;
Query OK, 0 rows affected (0.21 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.182
Master_User: why
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1138145
Relay_Log_File: why-relay-bin.000002
Relay_Log_Pos: 1000
Relay_Master_Log_File: mysql-bin.000001
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: 1138145
Relay_Log_Space: 1154
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: 3
1 row in set (0.00 sec)
ERROR:
No query specified
md5-8f83b03f0b0646bb199f8aaf793e7c69
mysql> create database testtwomaster;
Query OK, 1 row affected (0.01 sec)
mysql> use testtwomaster;
Database changed
mysql> create table test(id int(4) not null AUTO_INCREMENT,name char(20) not null,primary key(id));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into test(name) values('masterone2');
Query OK, 1 row affected (0.05 sec)
mysql> select * from test;
+----+------------+
| id | name |
+----+------------+
| 1 | masterone1 |
| 3 | masterone2 |
+----+------------+
2 rows in set (0.01 sec)
md5-da5be983b35c08084a0cbd4c8c04ef5c
mysql> use testtwomaster;
Database changed
mysql> select * from test;
+----+------------+
| id | name |
+----+------------+
| 1 | masterone1 |
| 3 | masterone2 |
+----+------------+
2 rows in set (0.00 sec)
mysql> insert into test(name) values('mastertwo1');
Query OK, 1 row affected (0.02 sec)
mysql> insert into test(name) values('mastertwo2');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+----+------------+
| id | name |
+----+------------+
| 1 | masterone1 |
| 3 | masterone2 |
| 4 | mastertwo1 |
| 6 | mastertwo2 |
+----+------------+
4 rows in set (0.00 sec)
md5-27492c3df0c93dec467a5d170fe0e628
mysql> insert into test(name) values('masterone3');
Query OK, 1 row affected (0.05 sec)
mysql> insert into test(name) values('masterone4');
Query OK, 1 row affected (0.04 sec)
mysql> select * from test;
+----+------------+
| id | name |
+----+------------+
| 1 | masterone1 |
| 3 | masterone2 |
| 4 | mastertwo1 |
| 6 | mastertwo2 |
| 7 | masterone3 |
| 9 | masterone4 |
+----+------------+
6 rows in set (0.00 sec)
有关备份周期和恢复
- 按天备份，恢复时间短，运维成本高，占用空间大
- 按周备份，恢复麻烦
中小型公司，全量备份一天一次，在业务低估的时候执行全备，备份锁表，而大公司周备，通过binlog做增量
恢复流程
恢复数据最好不要有数据写入，例如iptable只允许当前我登录的客户端写入，或者负载均衡器停止往这边发送数据，或者锁表；如果不允许，就在导入之前切一下binlog，再进行全量恢复和增量恢复，增量恢复的时候记得把binlog中的错误的操作删除，最后把这些切后的binlog导入
如果mysql没有数据写入，关闭sql_log_bin参数，如果关闭，mysql binlog恢复就不会记录binlog，用户写的也不记录。这样可以防止恢复的数据写入binlog
能否锁表等主要是看公司的量化目标是多少。允许宕机多久还是保证数据不丢失,一般公司提供的硬件以及人力所决定，如果不够就需要申请，或者调整量化目标。
服务和数据，就是鱼和熊掌的关系，不可兼得，不停服务，就会有数据的丢失，我倾向于停服务，保数据。当然运维的存在是避免这种事情的发生，而并不是说去救火。
