<服务>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-d7178abe4eda6076e0f16c124fead753
[root@why ~]# vi why.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1335;
md5-4cc4e4f94247e56c15df9bbe8ba526ba
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 1335 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
md5-b96ef8722f4e8932e64b15e631e7c506
mysql> unlock tables; #解除锁表
Query OK, 0 rows affected (0.00 sec)
md5-e8e81778ed5c42c8726a5e585c421548
[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-6cb32c2931670234e2582313330d22db
[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-f8e86b4df331fde5245a6327dfe6877c
[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-2a445ed7b1e6a4662311c80eee9f53de
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-132a05f8f219df2765810f2768c575fd
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-3ba00015155bbdc43bc61a109e4b1eca
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-4e37b095ba207fc01f70378e420e1411
binlog-ignore-db=mysql,performance_schema,information_schem
md5-f5b151b903ab1d624f04ba2003a621a4
replicate-ignore-db=mysql,performance_schema,information_schem
md5-b3b0aa5f7aee5fa7ff17e740abb662de
binlog-ignore-db
replicate-ignore-db
md5-92225ced907efbde7c48bf0de808a7e0
stop slave
set global sql_slave_skip_counter=1
start slave
md5-b5da43a6fab15aa6d6288ddfb9b67734
auto_increment_increment = 2 自增id间隔
auto_increment_offset = 1 #id初始位置
log-bin = mysql-bin
log-slave-updates
md5-32e0dee7481991782ae2c267125750ff
auto_increment_increment = 2 自增id间隔
auto_increment_offset = 2 #id初始位置
log-bin = mysql-bin
log-slave-updates
md5-f67c13a75dcd37c9a70b07d3e913b438
[root@why ~]# service mysqld restart
Shutting down MySQL..... [确定]
Starting MySQL..... [确定]
md5-f0573096480c11f6dcd8bc3b6935f218
[root@mysql why]# mysqldump -uroot -p123456 -A -B --master-data=1 -x --events > 182bak.sql
md5-c17003a7c21374ff47548465a0b4e0ec
[root@why ~]# mysql -uroot -p123456 < 182bak.sql
md5-a078391f724c93583fc2a9a0611f1da8
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-27103baeaaddbf6920c823c95f59dc26
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-bf50c459d1b1ba04b3f902b29d30c1ec
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-52440a9d63013c3b464469f1e913009a
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
能否锁表等主要是看公司的量化目标是多少。允许宕机多久还是保证数据不丢失,一般公司提供的硬件以及人力所决定，如果不够就需要申请，或者调整量化目标。
服务和数据，就是鱼和熊掌的关系，不可兼得，不停服务，就会有数据的丢失，我倾向于停服务，保数据。当然运维的存在是避免这种事情的发生，而并不是说去救火。
