mariadb开启binlog日志备份

最新推荐文章于 2024-06-23 11:48:39 发布

只想挣钱，5毛以上活动莫叫我

最新推荐文章于 2024-06-23 11:48:39 发布

阅读量582

点赞数 1

文章标签： mariadb

本文链接：https://blog.csdn.net/qq_40736702/article/details/115016755

版权

主从备份

技术点：bin-log日志

开启主服务器的bin-log日志记录功能，将主服务器的bin-log日志传到从服务器，从服务器根据日志内容
将数据还原到本地。
主从服务器：

从服务器主动把主服务器上的数据同步到本地（备份）
从服务器分摊主服务器的查询压力（负载均衡）

准备：

master：192.168.1.109
slave：192.168.1.108
都开启二进制日志，在my.cnf中设置：

[root@master ~]# vim /etc/my.cnf
#在[mysqld]下添加
log-bin=mysql-bin
server=9(8)  #server随意设置，我这边是ip结尾数字

将mariadb设置开机自启动，然后开启mariadb：

systemctl enable mariadb
 systemctl start mariadb

在主服务器授权，从服务器保存授权信息

grant replication slave on *.* to slave@'192.168.1.108' identified by 	'oppo';

查看master节点的二进制日志的大小状态：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      396 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

从服务器接收授权：

change master to master_user='slave',master_password='oppo',master_host='192.168.1.109',master_log_file='mysql-bin.000002',master_log_pos=396;

在/var/lib/mysql目录下有个master.info的文件，则表示接受授权成功。
查看master.info：

[root@localhost mysql]# more master.info    
18
mysql-bin.000002
396
192.168.1.109
slave
oppo
3306
60
0





0
1800.000

0

启动从服务器：

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

查看从服务器是否开启成功：

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.109
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 396
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000002
            ` 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: 396
              Relay_Log_Space: 825
              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: 9
1 row in set (0.00 sec)

ERROR: No query specified

上面的两个进程启动则表明从服务器启动是成功的

检查主从配置是否成功

在master创建一个数据库：

MariaDB [(none)]> create database computer;
Query OK, 1 row affected (0.00 sec)

在从服务器查看是否生成数据库computer：

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| `computer`           | #主服务器创建的数据库已经同步到从服务器
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

主主备份

主主服务器（互为主从）

均摊写压力

设置配置文件mys.cnf

先停掉mariadb：

[root@master ~]# systemctl stop mariadb

修改08的配置文件：

log-bin=mysql-bin
#开启binlog日志
server=8
#server号每台maraidb都不一样，可以任意写
#replicate-do-db=test
binlog-ignore-db=mysql
#binlog-ignore-db=infomation_schema
#这三个是为了提高效率，将不必要的资源不写进二进制文件
auto-increment-increment=2
auto-increment-offset=1 #两台mysql各一个(2)
##这两个是为了防止主键冲突，数据的id号从1开始，每次递增2。即1，3，5......

修改09的配置文件：

log-bin=mysql-bin
#开启binlog日志
server=9
#server号每台maraidb都不一样，可以任意写
#replicate-do-db=test
binlog-ignore-db=mysql
#binlog-ignore-db=infomation_schema
#这三个是为了提高效率，将不必要的资源不写进二进制文件
auto-increment-increment=2
auto-increment-offset=2 #两台mysql各一个(1)
###这两个是为了防止主键冲突，数据的id号从1开始，每次递增2。即1，3，5......

开启两台maraidb数据库：

systemctm start maraidb

08为主授权，09为从接受授权：

MariaDB [(none)]> grant replication slave on *.* to slave@'192.168.1.109' identified by 'oppo'; 
Query OK, 0 rows affected (0.00 sec)

查看08master节点的二进制日志的大小状态：

MariaDB [(none)]> grant replication slave on *.* to slave@'192.168.1.109' identified by 'oppo'; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      401 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

09slave节点接收授权：

MariaDB [(none)]> change master to master_user='slave',master_password='oppo',master_host='192.168.1.108',master_log_file='mysql-bin.000006',master_log_pos=401;
Query OK, 0 rows affected (2.15 sec)

在slave节点的/var/lib/mysql目录下有个master.info的文件：

[root@master mysql]# cat master.info 
18
mysql-bin.000006
401
192.168.1.108
slave
oppo
3306
60
0





0
1800.000

0

启动从slave服务器：

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

验证是否启动成功：

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.108
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 489
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 617
        Relay_Master_Log_File: mysql-bin.000006
             `Slave_IO_Running: Yes`
            `Slave_SQL_Running: Yes`  #这两个进程启动为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: 489
              Relay_Log_Space: 913
              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: 8
1 row in set (0.00 sec)

ERROR: No query specified

在主服务器（08）上创建一个数据库home：

MariaDB [(none)]> create database home;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| home               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (1.59 sec)

在从（09）服务器查看是否有新创建的home库：

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| home               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (1.83 sec)

主（08）从（09）配置成功。

再按照该操作进行一次以09为主，08为从的操作即为主主备份。

一主多从

master：192.168.1.108
slave1：192.168.1.109
slave2：192.168.1.110

按照主从的配置，分别操作即可，注意server号、授权IP、接受授权的IP/文件、文件大小的不同。

多主一从

master1：192.168.1.108
master2：192.168.1.109
slave1：192.168.1.110

安装mariadb

分别在三台机器上安装mariadb：

yum -y install mariadb*

主服务器配置

master1修改配置文件：

vim /etc/my.cnf
#在[mysqld]下添加
log-bin=mysql-bin
server-id=8

启动mariadb：

systemctl start mariadb

授权：

MariaDB [(none)]> grant replication slave on *.* to slave@'192.168.1.110' identified by 'oppo';
Query OK, 0 rows affected (0.00 sec)

查看主服务器的bin-log状态：

grant replication slave on *.* to slave@'192.168.1.110' identified by 'oppo';
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      534 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

> `这一步记住查看到的信息，后面接受授权会用到`

同样在master2上修改配置文件

##修改从服务器配置文件：

#M-M-S
[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
log=/tmp/multi.log

[mysqld8]
port=3306
datadir=/var/lib/mysqla/
pid-file=/var/lib/mysqla/mysqld.pid
socket=/var/lib/mysqla/mysql.sock
user=mysql
server-id=10

[mysqld9]
port=3307
datadir=/var/lib/mysqlb/
pid-file=/var/lib/mysqlb/mysqld.pid
socket=/var/lib/mysqlb/mysql.sock
user=mysql
server-id=10

初始化从服务器数据库，生成mysqla,mysqlb目录：

[root@localhost mysql]# mysql_install_db --datadir=/var/lib/mysqla --user=mysql
Installing MariaDB/MySQL system tables in '/var/lib/mysqla' ...
210320 14:26:06 [Note] /usr/libexec/mysqld (mysqld 5.5.68-MariaDB) starting as process 16753 ...
`OK`
Filling help tables...
210320 14:26:08 [Note] /usr/libexec/mysqld (mysqld 5.5.68-MariaDB) starting as process 16761 ...
`OK`

[root@localhost mysql]# mysql_install_db --datadir=/var/lib/mysqlb --user=mysql
Installing MariaDB/MySQL system tables in '/var/lib/mysqlb' ...
210320 14:27:22 [Note] /usr/libexec/mysqld (mysqld 5.5.68-MariaDB) starting as process 16801 ...
`OK`
Filling help tables...
210320 14:27:24 [Note] /usr/libexec/mysqld (mysqld 5.5.68-MariaDB) starting as process 16810 ...
`OK`

为防止权限出问题，将mysqla，mysqlb修改为mysql所有：

[root@localhost lib]# chown -R mysql:mysql mysqla
[root@localhost lib]# chown -R mysql:mysql mysqlb

启动服务器线程：

[root@localhost lib]# mysqld_multi  --defaults-file=/etc/my.cnf start 8
[root@localhost lib]# mysqld_multi  --defaults-file=/etc/my.cnf start 9
#mysqld_multi  在配置文件中的模块
#--defaults-file  配置文件
#start 8	开启线程8（配置文件中[mysqld8]中的数字）

查看端口，确认是否开启：

[root@localhost lib]# netstat -natp |grep :330
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      17059/mysqld        
tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      17300/mysqld

分别登录两个mysql进程：

mysql -uroot -P 3306  -S /var/lib/mysqla/mysql.sock
mysql -uroot -P 3307  -S /var/lib/mysqlb/mysql.sock

对应上面的进程进行接受授权：

change master to master_user='slave',master_password='oppo',master_host='192.168.1.108',master_log_file='mysql-bin.000002',master_log_pos=534;


change master to master_user='slave',master_password='oppo',master_host='192.168.1.109',master_log_file='mysql-bin.000001',master_log_pos=534;

分别启动从进程：

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

分别查看slave的进程是否启动：

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.109
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 534
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             `Slave_IO_Running: Yes`
            `Slave_SQL_Running: Yes`
            ...............................

在数据库中操作看是否成功：
在master1中创建aaa数据库，然后在slave8线程中查看是否有新建的aaa数据库：

#master新建
MariaDB [(none)]> create database aaa;
Query OK, 1 row affected (0.00 sec)
#slave8查看
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

在master2中创建bbb数据库，然后在slave9线程中查看是否有新建的bbb数据库：

#master新建
MariaDB [(none)]> create database bbb; 
Query OK, 1 row affected (0.00 sec)
#slave9查看
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbb                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)