mysql数据库双机热备份技术
mysql数据库双机热备份技术
准备
#准备两台服务器192.168.10.30 192.168.10.31
#并且装好mysql，版本最好一样
#且服务器能相互ping通
由于 Mysql 不同版本之间的（二进制日志）binlog 格式可能会不太一样，因此最好的搭
配组合是主（Master）服务器的 Mysql 版本和从（Slave）服务器版本相同或者更低，主服务
器的版本肯定不能高于从服务器版本。
搭建
主服务器master 192.168.10.30
[root@bogon ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:b9:89:0a brd ff:ff:ff:ff:ff:ff
inet 192.168.10.30/24 brd 192.168.10.255 scope global eth0
inet6 fe80::20c:29ff:feb9:890a/64 scope link
valid_lft forever preferred_lft forever
3: pan0: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN
link/ether 6e:1c:58:c7:3b:d6 brd ff:ff:ff:ff:ff:ff
4: virbr0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UNKNOWN
link/ether 52:54:00:8f:69:ca brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
5: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 500
link/ether 52:54:00:8f:69:ca brd ff:ff:ff:ff:ff:ff
从服务器slave 192.168.10.31
[root@bogon home]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:2f:a5:21 brd ff:ff:ff:ff:ff:ff
inet 192.168.10.31/24 brd 192.168.10.255 scope global eth0
inet6 fe80::20c:29ff:fe2f:a521/64 scope link
valid_lft forever preferred_lft forever
3: pan0: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN
link/ether 2e:b2:94:ce:ba:4d brd ff:ff:ff:ff:ff:ff
4: virbr0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UNKNOWN
link/ether 52:54:00:8f:69:ca brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
5: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 500
link/ether 52:54:00:8f:69:ca brd ff:ff:ff:ff:ff:ff
两个数据库都执行
mysql> use test
Database changed
mysql> create table tb_mobile(mobile varchar(20) comment '手机号码', time
-> timestamp default now() comment '时间');
Query OK, 0 rows affected (0.02 sec)
主服务器master配置
#授权给从服务器的登陆权限
mysql> grant replication slave on *.* to 'replicate'@'192.168.10.31' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
验证从服务器是否可以登录
[root@bogon ~]# mysql -h192.168.10.30 -ureplicate -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.53 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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>
修改主服务器mysql配置文件
[mysqld]
server-id = 1 #指定主服务器编号，唯一 id
log-bin=mysql-bin #其中这两行是本来就有的，可以不用动，添加下面两行即可.指定日志文件
binlog-do-db = test #记录日志的数据库
binlog-ignore-db = mysql #不记录日志的数据库
[root@bogon ~]# service mysql restart
Shutting down MySQL... [ OK ]
Starting MySQL.. [ OK ]
进入 mysql 服务后，可通过指令查看 Master 状态
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 107
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
ERROR:
No query specified
注意这两行
File: mysql-bin.000001
Position: 107
从服务器配置
vim /etc/my.cnf
[mysqld]
server-id = 2
log-bin=mysql-bin
replicate-do-db = test
replicate-ignore-db = mysql,information_schema,performance_schema
[root@bogon ~]# service mysql restart
Shutting down MySQL. [ OK ]
Starting MySQL.. [ OK ]
进入mysql配置
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.10.30',master_user='replicate',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.02 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.10.30
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: bogon-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB: mysql,information_schema,performance_schema
成功标志
查看下面两项值均为 Yes，即表示设置从服务器成功。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果不是 yes,说明你的/etc/my.cnf 文件配置有误，你需要一个字母一个字母检查。
还有防火墙要关闭。 service iptables sto
验证
master
mysql> use test;
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> select * from tb_mobile;
Empty set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb_mobile (mobile) values('1827354637');
Query OK, 1 row affected (0.01 sec)
slave
mysql> use test;
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> select * from tb_mobile;
+------------+---------------------+
| mobile | time |
+------------+---------------------+
| 1827354637 | 2023-11-20 06:45:39 |
+------------+---------------------+
1 row in set (0.00 sec)
浙公网安备 33010602011771号