mysql 主从
MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步。
-
MySQL主从是基于binlog的，主上须开启binlog才能进行主从
-
binlog，其实就是一个文件，文件里记录了一些日志，文件是 二进制文件，无法cat
-
主从过程大致有3个步骤：
-
主将更改操作记录到 binlog 里
- 从将主的binlog事件（sql语句）同步到本机上并记录在 relaylog（中继日志） 里
- 从根据 relaylog 里面的sql语句按顺序执行
-
mysql主从共有三个线程
-
mysql 主从共有三个线程。主上有一个log dump 线程，用来和从的I/O 线程传递binlog；
- 从上有两个线程，其中I/O 线程用来同步主的binlog并生成relaylog，另外一个sql线程用来把relaylog 里面的sql语句落地
-
mysql 主从原理图
- mysql 主从应用场景
1、数据备份，主机器宕机，从机器还能随时供服务
2、作为一个从库，读的库，减轻主库的压力，数据备份且可以分担主机器被调用数据时的压力，mysql主从，是有方向性的，写数据，必须从主机器开始；如果不依照原理会导致数据紊乱。
准备工作
两台机器都安装并启动mysql。
配置主
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
2[root@localhost ~]# vim /etc/my.cnf
3……
4server-id=131
5// 自定义
6log_bin=testlinux
7// 指定log前缀
8
9重启mysql服务
10[root@localhost ~]# /etc/init.d/mysqld restart
11Shutting down MySQL.. SUCCESS!
12Starting MySQL. SUCCESS!
13
14[root@localhost ~]# ls -l /data/mysql
15-rw-rw----. 1 mysql mysql 120 1月 23 21:00 testlinux.000001
16-rw-rw----. 1 mysql mysql 19 1月 23 21:00 testlinux.index
17// 此时，/data/mysql目录下会产生两个新的文件
18// 之后还会生成更多以testlinux开头的文件
19// 这些文件都非常重要，是实现主从的根本，没有这些文件主从也无法完成
20
21创建一个数据库为实验做准备
22[root@localhost ~]# mysql -uroot -p
23......
24MySQL > create database testlinux;
25Query OK, 1 row affected (0.00 sec)
26// 二进制文件testlinux.000001 大小增加，它里面记录了数据库的创建过程。
27
28创建一个用于同步数据的用户
29[root@localhost ~]# mysql -uroot -p
30......
31mysql> grant replication slave on *.* to 'repl'@'192.168.159.132' identified by '112233';
32Query OK, 0 rows affected (0.01 sec)
33// IP为“从”的IP
34
35mysql> flush tables with read lock;
36Query OK, 0 rows affected (0.12 sec)
37// 锁定数据表（目的是暂时使其不能继续写，保持现有状态用于同步）
38
39mysql> show master status;
40+-------------------+----------+--------------+------------------+-------------------+
41| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
42+-------------------+----------+--------------+------------------+-------------------+
43| testlinux.000001 | 441 | | | |
44+-------------------+----------+--------------+------------------+-------------------+
451 row in set (0.00 sec)
46#记住file和position(设置主从同步时会使用)
47
- 备份主库中的所有数据库
[root@localhost ~]# mysqldump -uroot -p112233 test > /tmp/test.sql
[root@localhost ~]# mysqldump -uroot -p112233 zrlog > /tmp/zrlog.sql
[root@localhost ~]# mysqldump -uroot -p112233 testlinux > /tmp/testlinux.sql
配置从
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
2……
3server-id=130
4……
5
6// 增加一个sever-id 和主不一样就行
7
8重启mysql
9[root@localhost ~]# /etc/init.d/mysqld restart
10Shutting down MySQL.. SUCCESS!
11Starting MySQL. SUCCESS!
12
13将主中备份的数据库发送到从中
14[root@localhost ~]# scp 192.168.159.131:/tmp/*.sql /tmp/
15The authenticity of host '192.168.159.131 (192.168.159.131)' can't be established.
16ECDSA key fingerprint is b2:66:7f:db:00:38:59:11:9e:75:75:02:fd:7a:95:d7.
17Are you sure you want to continue connecting (yes/no)? yes
18Warning: Permanently added '192.168.159.131' (ECDSA) to the list of known hosts.
19root@192.168.159.131's password:
20testlinux.sql 100% 0 0.0KB/s 00:00
21test.sql 100% 0 0.0KB/s 00:00
22zrlog.sql 100% 0 0.0KB/s 00:00
23
24创建库
25[root@localhost ~]# mysql -uroot -p
26Enter password:
27Welcome to the MySQL monitor.
28......
29mysql> create database testlinux;
30Query OK, 1 row affected (0.00 sec)
31mysql> create database test;
32Query OK, 1 row affected (0.00 sec)
33mysql> create database zrlog;
34Query OK, 1 row affected (0.00 sec)
35
36恢复数据库
37[root@localhost ~]# mysql -uroot -p159820 test < /tmp/test.sql
38[root@localhost ~]# mysql -uroot -p159820 zrlog < /tmp/zrlog.sql
39[root@localhost ~]# mysql -uroot -p159820 testlinux < /tmp/testlinux.sql
40
41// 该过程要保证主从数据库内容一致
42
43实现主从同步
44[root@localhost ~]# mysql -uroot -p
45.....
46mysql> stop slave;
47Query OK, 0 rows affected, 1 warning (0.06 sec)
48mysql> change master to master_host='192.168.159.131',master_user='repl',master_password='112233',master_log_file='testlinux.000001',master_log_pos=441;
49Query OK, 0 rows affected, 2 warnings (0.02 sec)
50// IP为主的IP；file、pos分别为主的filename和position。
51mysql> start slave;
52Query OK, 0 rows affected (0.00 sec)
53
54判断主从是否配置成功
55mysql> show slave status\G
56......
57 Slave_IO_Running: Yes
58 Slave_SQL_Running: Yes
59 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
60......
61
62解锁主库的表（在主上操作）
63[root@localhost ~]# mysql -uroot -p
64......
65mysql> unlock tables;
66Query OK, 0 rows affected (0.00 sec)
67......
68
测试主从
-
主上几个配置参数（在/etc/my.cnf中配置）
-
binlog-do-db= 仅同步指定的库
- binlog-ignore-db= 忽略指定的库
-
从上几个配置参数（在/etc/my.cnf中配置）
-
replicate_do_db= 同步指定的库
-
replicate_ignore_db= 忽略指定的库
-
replicate_do_table= 同步指定的表
-
replicate_ignore_table= 忽略指定的表
-
replicate_wild_do_table= 如aming.%，支持通配符
-
replicate_wild_ignore_table= 所有的忽略
-
注意： 进行从服务器的配置时尽量使用参数“replicatewild”，使匹配更精确，提升使用性能。
- 测试
主服务器：
mysql> show tables;
+—————————+
| Tables_in_adaitest |
+—————————+
| columns_priv |
| db |
| event |
+—————————+
删除表：
mysql> drop table db;
mysql> show tables;
+—————————+
| Tables_in_adaitest |
+—————————+
| columns_priv |
| event |
+—————————+
从服务器：
主服务器删除表之前：
mysql> show tables;
+—————————+
| Tables_in_adaitest |
+—————————+
| columns_priv |
| db |
| event |
+—————————+
主服务器删除表之后：
mysql> show tables;
+—————————+
| Tables_in_adaitest |
+—————————+
| columns_priv |
| event |
+—————————+
本文转自 豆渣锅 51CTO博客，原文链接:http://blog.51cto.com/754599082/2064416
