Mysql主从同步架构配置
Mysql主从同步架构配置
老七Linux
发布于 2018-05-09 16:28:01
发布于 2018-05-09 16:28:01
一、mysql主从介绍：
MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的。也就是说，当你在A机器写入一个表，再次查看B机器也会同步一个表。
1.1 MySQL主从是基于binlog的，主上须开启binlog才能进行主从。
主从过程大致有3个步骤：
- 主将更改操作记录到binlog里。
- 从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里。
- 从根据relaylog里面的sql语句按顺序执行 主上有一个log dump线程，用来和从的I/O线程传递binlog 从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地。
1.2 主从原理图：
Master：主mysql
Slave：备mysql
当有数据写进主mysql中的时候，就出自动生成一个log dump thread，记录到如上讲的binlog中，然后从数据库就会和主mysql有个线程进行交互，从服务器就会读取binlog日志到Slave，然后再次生成一个relay log（中继日志），然后再次和从服务器中的SQL thread线程进程交互执行。
来段大白话：
从服务器把主服务器上的binlog弄到自己服务器上去，然后根据这个binlog生成自己的relay日志，根据这个relay日志进行更改数据库，最终达到两边数据一致。
1.3 主从的作用;
- 数据的备份；
假如A服务器（主）突然硬件问题，宕机了。但是线上跑了很多重要的数据，我们完全可以使用B服务器（从）直接顶上。
- 负载均衡；
我们线上的所有数据均是从A上面读取，由于压力比较大，我们可以使用B服务器分享一部分用户到自己的服务器，但是只是可以读，不可以写入数据。看图可知，数据的读取是有方向性的，如果首先从B上读取那就混乱了，数据肯定不一致了。就会导致主从失败！
二、主从配置 – 主Mysql操作（zhdy-03）
2.1 配置my.cnf
在之前配置LAMP和LNMP的时候只是配置了datadir和socket，
现在我们需要增加server-id=144和log_bin=zhdy1（server-id为了混淆我使用的IP地址的最后一位，log_bin需要自定义一个名字，此为bin_log的前缀）
修改完配置文件后，启动或者重启mysqld服务。
[root@zhdy-03 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!2.2查看生成的新文件：
[[email protected]03 ~]# cd /data/mysql
[[email protected]03 mysql]# ll
total 176296
-rw-rw----. 1 mysql mysql 56 Aug 25 00:23 auto.cnf
drwx------. 2 mysql mysql 80 Aug 25 00:18 db1
drwx------. 2 mysql mysql 4096 Aug 25 00:18 db123
-rw-r-----. 1 mysql mysql 79691776 Aug 30 19:20 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug 30 19:20 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 25 00:18 ib_logfile1
drwx------. 2 mysql mysql 4096 Aug 25 00:18 lalala
drwx------. 2 mysql mysql 4096 Aug 25 00:18 mysql
drwx------. 2 mysql mysql 4096 Aug 25 00:18 mysql2
drwx------. 2 mysql mysql 4096 Aug 25 00:18 performance_schema
drwx------. 2 mysql mysql 20 Aug 25 00:18 test
-rw-r-----. 1 mysql mysql 499 Aug 25 00:18 xtrabackup_info
drwx------. 2 mysql mysql 4096 Aug 25 00:18 zhdy01
drwx------. 2 mysql mysql 36864 Aug 25 00:18 zhdy02
drwx------. 2 mysql mysql 12288 Aug 25 00:18 zhdy03
-rw-rw----. 1 mysql mysql 55367 Aug 30 19:20 zhdy-03.err
-rw-rw----. 1 mysql mysql 5 Aug 30 19:20 zhdy-03.pid
-rw-rw----. 1 mysql mysql 120 Aug 30 19:20 zhdy1.000001
-rw-rw----. 1 mysql mysql 15 Aug 30 19:20 zhdy1.index
zhdy1.000001；zhdy1.index 这两个文件非常重要，是我们实现主从的根本。其实在上面我们还可以看到很多的之前做测试使用的数据库，我们今天就用这些数据库做测试：
2.3 备份数据库
[[email protected]03 mysql]# mysqldump -uroot -pzhangduanya zhdy01 > /tmp/zhdy01.sql
Warning: Using a password on the command line interface can be insecure.2.4 创建一个新的库并把之前的数据恢复
[[email protected]03 mysql]# mysql -uroot -pzhangduanya -e "create database haha"
Warning: Using a password on the command line interface can be insecure.
[[email protected]03 mysql]# mysql -uroot -pzhangduanya haha < /tmp/zhdy01.sql
Warning: Using a password on the command line interface can be insecure.2.5 创建用作同步数据的用户
mysql> grant replication slave on *.* to 'repl'@'192.168.240.142' identified by 'zhangduanya';
授予repl从服务器ip为192.168.240.142, 密码为zhangduanya允许权限为replication slave
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
如上一条命令为锁表的意思！锁表的意思是暂停再次写入数据，先保持目前的状态。
我们先同步一下，让主从的数据保护一致。然后才可以实现主从实时同步。
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| zhdy1.000001 | 403309 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
我们需要记住file 和Position。待会会用到。2.6 查看当前数据库，开始备份
[[email protected]03 mysql]# ls
auto.cnf db123 ibdata1 ib_logfile1 mysql performance_schema xtrabackup_info zhdy02 zhdy-03.err zhdy1.000001
db1 haha ib_logfile0 lalala mysql2 test zhdy01 zhdy03 zhdy-03.pid zhdy1.index
再此，我用到的数据库为：haha，zhdy01，zhdy02，zhdy03，然后我就模拟线上的环境，把如上经常用到的全部备份并开始同步。2.7 备份数据库
zhdy01数据库我已经备份了（haha也是用的zhdy01的数据），再次备份其它两个：
[[email protected]03 mysql]# mysqldump -uroot -pzhangduanya zhdy02 > /tmp/zhdy02.sql
Warning: Using a password on the command line interface can be insecure.
[[email protected]03 mysql]# mysqldump -uroot -pzhangduanya zhdy03 > /tmp/zhdy03.sql
Warning: Using a password on the command line interface can be insecure.
[[email protected]03 mysql]# ls /tmp/*.sql
/tmp/zhdy01.sql /tmp/zhdy02.sql /tmp/zhdy03.sql三、主从配置 – 从Mysql操作（zhdy-02）
3.1 配置my.cnf
配置server-id=132，要求和主不一样。bin_log就不需要配置了，二进制文件只需要在主服务器上面配置即可。
server-id=1323.2 重新启动Mysql
[root@zhdy-02 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!3.3 把主服务器上面的数据库全部copy过来
[[email protected]02 ~]# scp 192.168.230.144:/tmp/*.sql /tmp/
[email protected]'s password:
zhdy01.sql 100% 393KB 392.9KB/s 00:00
zhdy02.sql 100% 2245KB 2.2MB/s 00:00
zhdy03.sql 100% 307KB 306.7KB/s 00:003.4 创建和主服务器上面一样的数据库：
mysql> create database haha;
Query OK, 1 row affected (0.02 sec)
mysql> create database zhdy01;
Query OK, 1 row affected (0.00 sec)
mysql> create database zhdy02;
Query OK, 1 row affected (0.00 sec)
mysql> create database zhdy03;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| haha |
| mysql |
| performance_schema |
| test |
| zhdy01 |
| zhdy02 |
| zhdy03 |
+--------------------+
8 rows in set (0.00 sec)3.5 导入数据：
[root@zhdy-02 ~]# mysql -uroot haha < /tmp/zhdy01.sql
[root@zhdy-02 ~]# mysql -uroot zhdy01 < /tmp/zhdy01.sql
[root@zhdy-02 ~]# mysql -uroot zhdy02 < /tmp/zhdy02.sql
[root@zhdy-02 ~]# mysql -uroot zhdy03 < /tmp/zhdy03.sql
[root@zhdy-02 ~]# ls /data/mysql/
haha ib_logfile1 zhdy01 zhdy02 zhdy033.6 配置从服务器
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
如下就是实现主从非常关键的一步：
mysql> change master to master_host='192.168.230.144', master_user='repl', master_password='zhangduanya', master_log_file='zhdy1.000001', master_log_pos=403309;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)master_host=’192.168.230.144’//主mysql的IP；
master_user=’repl’//刚刚在主服务器上创建的用户名；
master_password=’zhangduanya’//密码
master_log_file=’zhdy1.000001’//此为刚刚主服务器上面show master status看到的其中一个file的值；
master_log_pos=403309//如上
3.7 配置全部成功了，如何测试配置的是否成功？
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.230.144
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: zhdy1.000001
Read_Master_Log_Pos: 404262
Relay_Log_File: zhdy-02-relay-bin.000002
Relay_Log_Pos: 1232
Relay_Master_Log_File: zhdy1.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: 404262
Relay_Log_Space: 1407
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: 144
Master_UUID: 98d511ff-88e8-11e7-94f3-000c29896c09
Master_Info_File: /data/mysql/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: 0
1 row in set (0.00 sec)show slave stauts\G
看是否有
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
还需关注
Seconds_Behind_Master: 0 //为主从延迟的时间当然我在配置好了之后也出现了错误：
Slave_IO_Running: connecting
Slave_SQL_Running: Yes仔细检查后发现主mysql配置授权的时候密码有问题。重新授权后再次查看状态就ok了！
3.8 到主服务器（zhdy-03）执行恢复写操作：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)四、测试主从同步
4.1 主服务器上
binlog-do-db= //仅同步指定的库
binlog-ignore-db= //忽略指定库假如在主服务器上面有很多数据库，但是我只想同步zhdy01这个库，需要在：
vim /etc/my.cnf
binlog-do-db=zhdy01
要是多个的话就用英文状态下的逗号去分隔；或者有时候数据库比较多，我就一个zhdy02库不需要同步：
vim /etc/my.cnf
binlog-ignore-db=zhdy024.2 从服务器上
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=有时候我们需要在同步的时候忽略一些临时的表，这些表堵我们的用处不大，都是临时的。不建议使用上面去完成，有风险且会导致其它表有时候也不正常。所以建议用下面的两个参数去定义：
replicate_wild_do_table= //如zhdy01.%, 支持通配符%
replicate_wild_ignore_table=4.3 测试主从
主服务器操作：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db123 |
| haha |
| lalala |
| mysql |
| mysql2 |
| performance_schema |
| test |
| zhdy01 |
| zhdy02 |
| zhdy03 |
+--------------------+
12 rows in set (0.00 sec)
mysql> use haha;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_haha |
+-----------------------+
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
| wp_users |
+-----------------------+
12 rows in set (0.00 sec)
mysql> select count(*) wp_users;
+----------+
| wp_users |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
我们看到了wp_users有一条数据存在，从服务器操作：（查看wp_users的数据）
mysql> select count(*) wp_users;
+----------+
| wp_users |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)我尝试把主server上面的wp_users表内的内容清空：
mysql> truncate table wp_users;
Query OK, 0 rows affected (0.05 sec)再次去从server去查看：
mysql> select * from wp_users;
Empty set (0.00 sec)
数据已经清空了！！！再次测试删除一个表：
（！！！这绝对是危险动作，我的haha数据库是测试的，所以尽管删除好了，线上的机器一定要三思！！！）
主server操作：
mysql> drop table wp_users;
Query OK, 0 rows affected (0.06 sec)从server查看：
mysql> show tables;
+-----------------------+
| Tables_in_haha |
+-----------------------+
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
+-----------------------+
11 rows in set (0.01 sec)
wp_users表已经没有了！千万别在从server上面删除任何数据，一旦删除也就意味着数据不是一致的，主从就失败了！
如何再次恢复呢？
- 先把主server上面的数据与从server上面的数据保持一致，刚刚从server删除了什么数据，现在也需要把主server上面的数据也删除，为的就是数据一致！
- 首先主server上面操作：
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| zhdy1.000001 | 404480 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
记住Postion的这个值！- 从server上操作使用新的Postion值：
mysql> stop slave;
mysql> change master to master_host='192.168.230.144', master_user='repl', master_password='zhangduanya', master_log_file='zhdy1.000001', master_log_pos=404480;- 从server再次启动：
mysql> start slave;
mysql> show slave status\G再次去验证就发现已经再次连接了！
思路：
- 保证数据一致性的前提下，按照如上操作即可！
- 数据不一致的前提下，建议直接重新备份，然后再次导入！再次change master即可！
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2016/08/30，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。