1、主从同步(主从复制的)的原理。
答:主从同步的核心是二进制日志文件binary log,对数据库所有的增加、修改、删除操作都会在日志表里面记录一下的。mysql主从复制是异步的,串行化的,有延迟的,并不是实时的。
第一步,master主节点将改变的数据记录在本地的二进制日志中binary log,该过程称为二进制日志事件。 第二步,slave将master的binary log拷贝到自己的relay log(中继日志文件)中。 第三步,中继日志事件,将数据读取到自己的数据库之中。
2、mysql集群的优点,如下所示:
1)、负载均衡。 2)、失败迁移。
3、由于我的机器一台是window10安装的mysql,一台是centos7安装的mysql,所以它们的配置文件分别是windows的配置文件是my.ini,linux的配置文件是my.cnf。
首先,我需要配置一下允许远程连接我的window的mysql,如下所示:
1 Enter password: ******
2 Welcome to the MySQL monitor. Commands end with ; or \g.
3 Your MySQL connection id is 3
4 Server version: 5.7.24-log MySQL Community Server (GPL)
5
6 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
7
8 Oracle is a registered trademark of Oracle Corporation and/or its
9 affiliates. Other names may be trademarks of their respective
10 owners.
11
12 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
13
14 mysql> use mysql;
15 Database changed
16 mysql> grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
17 Query OK, 0 rows affected, 1 warning (0.03 sec)
18
19 mysql> flush privileges;
20 Query OK, 0 rows affected (0.03 sec)
21
22 mysql> select host,user from user;
23 +-----------+---------------+
24 | host | user |
25 +-----------+---------------+
26 | % | root |
27 | % | user1 |
28 | localhost | mysql.session |
29 | localhost | mysql.sys |
30 | localhost | root |
31 +-----------+---------------+
32 5 rows in set (0.00 sec)
33
34 mysql>
然后使用linux的远程连接一下window的mysql,可以连接即可,如果不可以连接,需要关闭window的防火墙。反之,开启Linux的mysql可以远程连接并关闭防火墙。
1 [root@k8s-node3 ~]# mysql -uroot -h192.168.0.116 -p123456
2 mysql: [Warning] Using a password on the command line interface can be insecure.
3 Welcome to the MySQL monitor. Commands end with ; or \g.
4 Your MySQL connection id is 4
5 Server version: 5.7.24-log MySQL Community Server (GPL)
6
7 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
8
9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15 mysql> show databases;
16 +
4、由于我的mysql是默认安装的,所以my.ini在这个路径下面C:\ProgramData\MySQL\MySQL Server 5.7\my.ini,根据个人需求进行配置吧,开始配置,如下所示:
1 [mysqld]
2 # 主节点master增加唯一标识符
3 server-id=1
4 # 开始配置二进制日志文件
5 log-bin="D:/program/mysql/mysql-bin"
6 # 开始配置二进制日志错误文件
7 log-error="D:/program/mysql/mysql-error"
8 # 主从同步的时候忽略的数据库
9 binlog-ignore-db=mysql
10 # 可选参数,指定主从同步的时候,同步那些数据库
11 binlog-do-db=test
我上面的配置一开始配置错了,导致mysql重启起不来了。还有下面的配置和自己的配置冲突了,我这里将默认的先注释了,如下所示:
Windows中的数据库授权那台计算机中的数据库是自己的从数据库。
1 mysql> grant replication slave,reload,super on *.* to 'root'@'192.168.110.%' identified by 'root';
2 Query OK, 0 rows affected, 1 warning (0.00 sec)
3
4 mysql>
5 mysql> flush privileges;
6 Query OK, 0 rows affected (0.00 sec)
7
8 mysql>
9 mysql>
查看主数据库的状态,每次在做主从同步前,需要观察主机状态的最新值,需要记住File、Position的值的。命令如下所示:
1 mysql> show master status;
2 +--------------------+----------+--------------+------------------+-------------------+
3 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4 +--------------------+----------+--------------+------------------+-------------------+
5 | mysql-error.000001 | 154 | biehl | mysql | |
6 +--------------------+----------+--------------+------------------+-------------------+
7 1 row in set (0.00 sec)
8
9 mysql>
如果my.ini配置正确的话,重启window的mysql之后,会在自己的指定目录D:\program\mysql生成下面三个文件,mysql-bin.000001、mysql-error.err、mysql-bin.index。
5、开始在linux的mysql配置,如下所示:
1 [mysqld]
2 # 从节点slave增加唯一标识符
3 server-id=2
4 # 配置日志路径
5 log-bin=mysql-bin
6 # 配置主从同步的数据库名称
7 replicate-do-db=test
Linux中的数据库授权那台计算机中的数据库是自己的主数据库,由于修改了my.ini所以这里重启一下Mysql的数据库。
1 [root@k8s-node3 ~]# systemctl restart mysqld.service
2 [root@k8s-node3 ~]# systemctl status mysqld.service
3 ● mysqld.service - MySQL Server
4 Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
5 Active: active (running) since Fri 2020-07-31 12:21:36 CST; 7s ago
6 Docs: man:mysqld(8)
7 http://dev.mysql.com/doc/refman/en/using-systemd.html
8 Process: 93026 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
9 Process: 92998 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
10 Main PID: 93028 (mysqld)
11 Tasks: 27
12 Memory: 246.4M
13 CGroup: /system.slice/mysqld.service
14 └─93028 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
15
16 Jul 31 12:21:28 k8s-node3 systemd[1]: Starting MySQL Server...
17 Jul 31 12:21:36 k8s-node3 systemd[1]: Started MySQL Server.
18 [root@k8s-node3 ~]#
由于没有重启Mysql的数据库之前报错了,自己重启一下然后执行命令即可。
1 mysql> change master to master_host='192.168.0.116',master_user='root',master_password='123456',master_port=3306,master_log_file='mysql-error.000001',master_log_pos=154 ;
2 ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
3 mysql> exit
4 Bye
5 [root@k8s-node3 ~]# mysql -uroot -h127.0.0.1 -p123456
6 mysql: [Warning] Using a password on the command line interface can be insecure.
7 Welcome to the MySQL monitor. Commands end with ; or \g.
8 Your MySQL connection id is 2
9 Server version: 5.7.30-log MySQL Community Server (GPL)
10
11 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
12
13 Oracle is a registered trademark of Oracle Corporation and/or its
14 affiliates. Other names may be trademarks of their respective
15 owners.
16
17 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
18
19 mysql> change master to master_host='192.168.0.116',master_user='root',master_password='123456',master_port=3306,master_log_file='mysql-error.000001',master_log_pos=154 ;
20 Query OK, 0 rows affected, 1 warning (0.01 sec)
21
22 mysql>
6、开启主从同步,这里在linux执行即可,如下所示:
1 mysql> start slave;
2 Query OK, 0 rows affected (0.01 sec)
3
4 mysql>
然后检验是否真的执行了,检查从节点的工作状态,如下所示:
1 mysql> show slave status \G
2 *************************** 1. row ***************************
3 Slave_IO_State:
4 Master_Host: 192.168.0.116
5 Master_User: root
6 Master_Port: 3306
7 Connect_Retry: 60
8 Master_Log_File: mysql-error.000001
9 Read_Master_Log_Pos: 154
10 Relay_Log_File: k8s-node3-relay-bin.000001
11 Relay_Log_Pos: 4
12 Relay_Master_Log_File: mysql-error.000001
13 Slave_IO_Running: No
14 Slave_SQL_Running: Yes
15 Replicate_Do_DB: biehl
16 Replicate_Ignore_DB:
17 Replicate_Do_Table:
18 Replicate_Ignore_Table:
19 Replicate_Wild_Do_Table:
20 Replicate_Wild_Ignore_Table:
21 Last_Errno: 0
22 Last_Error:
23 Skip_Counter: 0
24 Exec_Master_Log_Pos: 154
25 Relay_Log_Space: 154
26 Until_Condition: None
27 Until_Log_File:
28 Until_Log_Pos: 0
29 Master_SSL_Allowed: No
30 Master_SSL_CA_File:
31 Master_SSL_CA_Path:
32 Master_SSL_Cert:
33 Master_SSL_Cipher:
34 Master_SSL_Key:
35 Seconds_Behind_Master: NULL
36 Master_SSL_Verify_Server_Cert: No
37 Last_IO_Errno: 1236
38 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
39 Last_SQL_Errno: 0
40 Last_SQL_Error:
41 Replicate_Ignore_Server_Ids:
42 Master_Server_Id: 1
43 Master_UUID: 262f593f-746e-11e9-b769-d8c497e293c1
44 Master_Info_File: /var/lib/mysql/master.info
45 SQL_Delay: 0
46 SQL_Remaining_Delay: NULL
47 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
48 Master_Retry_Count: 86400
49 Master_Bind:
50 Last_IO_Error_Timestamp: 200731 12:25:35
51 Last_SQL_Error_Timestamp:
52 Master_SSL_Crl:
53 Master_SSL_Crlpath:
54 Retrieved_Gtid_Set:
55 Executed_Gtid_Set:
56 Auto_Position: 0
57 Replicate_Rewrite_DB:
58 Channel_Name:
59 Master_TLS_Version:
60 1 row in set (0.00 sec)
61
62 mysql>
主要观察这两个都是yes即可,Slave_IO_Running: No和 Slave_SQL_Running: Yes,这里如果不都是yes,看Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'报错信息。
1 mysql> stop slave;
2 Query OK, 0 rows affected (0.00 sec)
3
4 mysql> reset slave;
5 Query OK, 0 rows affected (0.00 sec)
6
7 mysql> start slave;
8 Query OK, 0 rows affected (0.01 sec)
9
10 mysql> show slave status \G
11 *************************** 1. row ***************************
12 Slave_IO_State: Waiting for master to send event
13 Master_Host: 192.168.0.116
14 Master_User: root
15 Master_Port: 3306
16 Connect_Retry: 60
17 Master_Log_File: mysql-bin.000001
18 Read_Master_Log_Pos: 154
19 Relay_Log_File: k8s-node3-relay-bin.000003
20 Relay_Log_Pos: 367
21 Relay_Master_Log_File: mysql-bin.000001
22 Slave_IO_Running: Yes
23 Slave_SQL_Running: Yes
24 Replicate_Do_DB: biehl
25 Replicate_Ignore_DB:
26 Replicate_Do_Table:
27 Replicate_Ignore_Table:
28 Replicate_Wild_Do_Table:
29 Replicate_Wild_Ignore_Table:
30 Last_Errno: 0
31 Last_Error:
32 Skip_Counter: 0
33 Exec_Master_Log_Pos: 154
34 Relay_Log_Space: 578
35 Until_Condition: None
36 Until_Log_File:
37 Until_Log_Pos: 0
38 Master_SSL_Allowed: No
39 Master_SSL_CA_File:
40 Master_SSL_CA_Path:
41 Master_SSL_Cert:
42 Master_SSL_Cipher:
43 Master_SSL_Key:
44 Seconds_Behind_Master: 0
45 Master_SSL_Verify_Server_Cert: No
46 Last_IO_Errno: 0
47 Last_IO_Error:
48 Last_SQL_Errno: 0
49 Last_SQL_Error:
50 Replicate_Ignore_Server_Ids:
51 Master_Server_Id: 1
52 Master_UUID: 262f593f-746e-11e9-b769-d8c497e293c1
53 Master_Info_File: /var/lib/mysql/master.info
54 SQL_Delay: 0
55 SQL_Remaining_Delay: NULL
56 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
57 Master_Retry_Count: 86400
58 Master_Bind:
59 Last_IO_Error_Timestamp:
60 Last_SQL_Error_Timestamp:
61 Master_SSL_Crl:
62 Master_SSL_Crlpath:
63 Retrieved_Gtid_Set:
64 Executed_Gtid_Set:
65 Auto_Position: 0
66 Replicate_Rewrite_DB:
67 Channel_Name:
68 Master_TLS_Version:
69 1 row in set (0.00 sec)
70
71 mysql>
如果报了主从使用了相同的server-id,需要进行检查,在主从中分别查看serverid,show variables like '%server_id%';
1 Enter password: ******
2 Welcome to the MySQL monitor. Commands end with ; or \g.
3 Your MySQL connection id is 4
4 Server version: 5.7.24-log MySQL Community Server (GPL)
5
6 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
7
8 Oracle is a registered trademark of Oracle Corporation and/or its
9 affiliates. Other names may be trademarks of their respective
10 owners.
11
12 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
13
14 mysql> show variables like '%server_id%';
15 +----------------+-------+
16 | Variable_name | Value |
17 +----------------+-------+
18 | server_id | 1 |
19 | server_id_bits | 32 |
20 +----------------+-------+
21 2 rows in set, 1 warning (0.01 sec)
22
23 mysql>
然后在从节点上进行查看,如下所示:
1 mysql> show variables like '%server_id%';
2 +----------------+-------+
3 | Variable_name | Value |
4 +----------------+-------+
5 | server_id | 2 |
6 | server_id_bits | 32 |
7 +----------------+-------+
8 2 rows in set (0.00 sec)
9
10 mysql>
7、测试一下主节点和从节点是否可以同步,可以在主节点的数据表插入一条数据,观察从节点的数据库是否有数据同步,需要注意的是从节点需要创建好数据库和数据表的,不然总是报一些莫名其妙的错误。在主节点添加数据,可以发现从节点已经新增数据了。
可以测试修改,删除操作,发现都是可以正常执行的。