Mysql（mariadb）实现主从复制（一主双从）以及简单故障排错
程序员文章站
2024-03-21 09:55:46
...
一、环境介绍
LNMP【 centos8（mariadb10.3.11），centos7（mariadb 5.5.64）】
centos8（192.168.32.8） 主服务器
centos7（192.168.32.7） 从服务器1
centos7（192.168.32.77） 从服务器2
二、原理
（1）主数据库进行增删改操作后，相应操作记录的语句（比如 create database test）会记录到binlog日志文件中（binlog日志文件一般和数据库data文件夹在一起）。
（2）从数据库会请求主数据库的binlog日志文件，获取到新的操作语句，然后在自己的从数据库上自动执行相同的操作语句，进而实现主从的同步。
注：这里，我们所需要配置的只是主从环境以及开启binlog日志，其他的mysql会自动完成。
三、详细主从复制过程
（1）Master开启bin-log功能，binlog日志文件用于记录数据库的增删改操作。
（2）需要开启三个线程，Master：I/O线程；Slave：I/O线程，SQL线程。
（3）Slave start；通过I/O线程连接Master，并且请求某个bin-log，position之后的内容。
（4）Master服务器收到Slave I/O线程发过来的日志请求信息，然后Master I/O线程将bin-log内容、position返回给Slave IO线程。
（5）Slave服务器收到bin-log日志内容，将bin-log日志内容写入到relay-log中继日志，创建一个master.info文件，该文件记录master IP、用户名、密码、master bin-log名称、bin-log position。
（6）Slave已经开启了sql线程，由sql线程实时监测relay-log日志内容是否有更新，如果有更新，则解析文件中的sql语句，并在Slave数据库中执行相同的操作语句。
注：可以通过show slave status \G 来查看具体的中继日志路径以及连接的master的其他信息。
四、主从复制的实现
主节点配置
在主服务器centos8（192.168.32.8）中配置
[aaa@qq.com ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8 #server-id用于区别二进制日志文件 这里用IP作为区分号
log-bin #开启二进制文件日志
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
[aaa@qq.com ~]#systemctl restart mariadb
先创建一个用户账号，方便之后数据库主从的管理
[aaa@qq.com ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant replication slave on *.* to "kaivi"@"192.168.32.%" identified by 'centos';
查看二进制日志位置：
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28052 |
| mariadb-bin.000002 | 546 | #二进制日志开始起点，后面配置从服务器的时候有作用
+--------------------+-----------+
2 rows in set (0.000 sec)
从节点1配置
在从服务器centos7（192.168.32.7）中配置
在从节点配置中，二进制文件不用启动
[aaa@qq.com ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=7 #server-id用于区别二进制日志文件 这里用IP作为区分号
read_only=ON #建议加上只读选项，以防被修改数据库文件
[aaa@qq.com ~]#systemctl restart mariadb 启动数据库服务
关键步骤：
[aaa@qq.com ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> help change master to #查看对应的帮助文件
找到相关配置文件进行适当的修改：
帮助参考
CHANGE MASTER TO
MASTER_HOST='192.168.32.8', #主服务器的ip地址
MASTER_USER='kaivi', #创建的一个复制账号
MASTER_PASSWORD='centos', #数据库密码
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', #二进制复制的位置
MASTER_LOG_POS=344； #具体开始的编号位置
具体从那里开始进行复制，可以查看日志：
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28052 |
| mariadb-bin.000002 | 344 | #二进制日志开始起点 对应下面的MASTER_LOG_FILE和MASTER_LOG_POS
+--------------------+-----------+
2 rows in set (0.000 sec)
修改之后，在数据库中运行：
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.32.8',
-> MASTER_USER='kaivi',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000002',
-> MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.00 sec)
这步骤之后，在对应的数据库安装文件中会生成一些新的文件
相关的主从复制信息都记录在relay-log.info文件里面，可以查看
可以用show slave status 命令来查看从数据库的一些基本信息
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.32.8
Master_User: kaivi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 344
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: No #线程暂时还没有启用
Slave_SQL_Running: No #线程暂时还没有启用
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: 344
Relay_Log_Space: 245
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: NULL #复制的延迟时间
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: 0
1 row in set (0.01 sec)
现在启动线程，这时候上面的Slave_IO_Running和 Slave_SQL_Running对应的参数就会发生变化
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.32.8
Master_User: kaivi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 874
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 546
Relay_Master_Log_File: mariadb-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: 874
Relay_Log_Space: 1673
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 #主服务器的serverid
从服务器上前后线程列表的对比：
MariaDB [(none)]> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)
后：
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
| 6 | system user | | NULL | Connect | 2933 | Waiting for master to send event | NULL | 0.000 |
| 7 | system user | | NULL | Connect | 2785 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
3 rows in set (0.00 sec)
从服务器上前后连接的对比：
从节点2配置
同理在从节点2的配置和从节点1配置基本一样：
在从服务器centos7（192.168.32.77）中配置
在从节点配置中，二进制文件不用启动
配置文件下面2个都是可以的，这里我们采用第一个配置文件
[aaa@qq.com ~]#vim /etc/my.cnf
[aaa@qq.com ~]#vim /etc/my.cnf.d/server.cnf
[aaa@qq.com ~]#vim /etc/my.cnf
[mysqld]
server-id=77 #server-id用于区别二进制日志文件 这里用IP作为区分号
read_only=ON
[aaa@qq.com ~]#systemctl start mariadb
[aaa@qq.com ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.32.8',
-> MASTER_USER='kaivi',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000002',
-> MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.32.8
Master_User: kaivi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 344
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
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: 344
Relay_Log_Space: 245
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: NULL
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: 0
1 row in set (0.00 sec)
MariaDB [(none)]> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
MariaDB [(none)]> start slave; 启动线程，开始连接复制
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
| 3 | system user | | NULL | Connect | 4380 | Waiting for master to send event | NULL | 0.000 |
| 4 | system user | | NULL | Connect | 3709 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
3 rows in set (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.32.8
Master_User: kaivi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 1001
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 1199
Relay_Master_Log_File: mariadb-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: 1001
Relay_Log_Space: 1495
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)
MariaDB [(none)]> Ctrl-C -- exit!
Aborted
[aaa@qq.com ~]#ss -nt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
ESTAB 0 0 192.168.32.77:22 192.168.32.1:64688
ESTAB 0 0 192.168.32.77:34914 192.168.32.8:3306
ESTAB 0 52 192.168.32.77:22 192.168.32.1:64427
五、测试数据
在主数据库中查看线程列表以及连接状态：
MariaDB [(none)]> show processlist;
+----+-------------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 9 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
| 33 | kaivi | 192.168.32.77:34914 | NULL | Binlog Dump | 4300 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
| 35 | kaivi | 192.168.32.7:56024 | NULL | Binlog Dump | 4263 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
+----+-------------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
8 rows in set (0.000 sec)
MariaDB [(none)]> Ctrl-C -- exit!
Aborted
[aaa@qq.com ~]#ss -nt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
ESTAB 0 0 192.168.32.8:22 192.168.32.1:64703
ESTAB 0 52 192.168.32.8:22 192.168.32.1:64408
ESTAB 0 0 [::ffff:192.168.32.8]:3306 [::ffff:192.168.32.77]:34914
ESTAB 0 0 [::ffff:192.168.32.8]:3306 [::ffff:192.168.32.7]:56024
[aaa@qq.com ~]#
在主数据库中新建数据库，看是否成功同步到2个从数据库服务器：
[aaa@qq.com ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.3.11-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)
MariaDB [(none)]>
在从数据库服务器上查看是否复制同步：
在从节点1（192.168.32.7）查看：
[aaa@qq.com ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]>
在从节点2（192.168.32.77）查看：
[aaa@qq.com ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]>
扩展——从服务器提升为主服务器
背景：主服务器突然down机，类似突然停电。这时候就需要提升其他的从服务器为主服务器继续提供对应的服务
1）选择哪一个从服务器来提升？
理论上多个从节点复制不完全一致，我们应该找复制量最多的，同步速度最快的。
分别查看从节点数据库中：show slave status\G 比较其中的一个选项 Exec_Master_Log_Pos: 的数值大小，越大说明数据保留越完整
或者查看每一个从节点中/var/lib/mysq/relay-log.info中的信息也一样。所以从节点服务器选择好了
2）如何提升选择的节点服务器
从服务器一般不启用二进制日志文件，若要提升为主节点服务器，则先把二进制日志文件先启用起来
修改配置文件：vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=# #指定一个日志编号
log-bin
重启数据库服务：systemctl restar mariadb
3）从服务器之前的从节点配置需要重现刷新或者删除
从节点清除信息之前需要停止其线程
进入到数据库里面： stop slave；停止线程
然后在从节点清除信息：reset slave ； #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
这个清除从节点信息不完全
如果要彻底清除从节点信息则：reset slave all；#清除所有从服务器上设置的主服务器同步信息，如PORT, HOST, USER和
PASSWORD 等
可以通过 show slave status\G 查看是否清除完全 或者对应其slave的基本信息；如果彻底清除从节点信息，则会显示为空。
4）查看二进制日志是否启动，来判断是否服务起来：
show master logs； 这里的信息就是之后配置其他服务器指向自己之后复制的开始日志二进制文件（很重要）
5）让其他的从节点重新指向提升的主服务器
先进入到从节点的数据库，把线程给停了：stop slave；
然后彻底清除从节点信息：reset slave all；
重新建立从节点change master to：
CHANGE MASTER TO
MASTER_HOST='192.168.32.8', #主服务器的ip地址
MASTER_USER='kaivi', #创建的一个复制账guang号
MASTER_PASSWORD='centos', #数据库密码
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', #二进制复制的位置
MASTER_LOG_POS=344；
在其他从服务器数据库中用show slave status\G查看是否配置文件出现,检查是否指向新的主节点服务器
如果指向了，就直接开启线程即可：start slave；
可以查看参数Slave_IO_Running和 Slave_SQL_Running是否连接成功为YES状态
这里需要注意的是否有用户能够进行管理，这里的操作是在刚刚提升为主服务器数据库中操作。
可以通过select user,host from mysql.user 查看
没有的话需要重新创建：
MariaDB [(none)]> grant replication slave on *.* to "kaivi"@"192.168.32.%" identified by 'centos';
6）最后验证一些是否成功即可
六、 故障排错
思路：跳过冲突错误
错误查看：show slave status\G Last_Errno： 1007
Last_Error：xxxxxxxxxxxx
跳过去的方法有多种方法
一种是基于这个事件来跳过
对应有一个变量：sql_slave_skip_counter
第一步在从服务器中停止线程：stop slave；
第二步在从服务器中设置全局变量跳过错误：set global sql_slave_skip_counter=1；
1 表示跳过一个事件，但是不是所有的事件都能跳过，有些重大事件的冲突是跳不过的
第三步，重新启动线程：start slave；
后续的复制不再受影响
总结：
MariaDB [(none)]> stop slave;
MariaDB [(none)]> set global sql_slave_skip_counter=1;
MariaDB [(none)]> start slave;
第二种方式：
跳过错误编号：Last_Errno： 1007
需要用到一个变量：slave_skip_errors 但是这个变量是静态的，需要修改配置文件
默认是是OFF 不跳过任何错误
MariaDB [(none)]> select @@slave_skip_errors;
+---------------------+
| @@slave_skip_errors |
+---------------------+
| OFF |
+---------------------+
1 row in set (0.000 sec)
[aaa@qq.com ~]#vim /etc/my.cnf/server.cnf
[mysqld]
slave_skip_errors=1007 #可以加个别的标号也可以是ALL
[aaa@qq.com ~]#systemctl restart mariadb
七、主服务器非新建时新增从服务器配置
如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点
通过备份恢复数据至从服务器
复制起始位置为备份时，二进制日志文件及其POS
实验环境：
主服务器centos8（192.168.32.8）10.3.11-MariaDB
新增节点服务器（从服务器）（192.168.32.18）10.3.11-MariaDB
主从服务器完全备份配置
主服务上面操作
[aaa@qq.com ~]#mkdir /backup
[aaa@qq.com ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup-`date +%F`.sql
在实际生产中需要增加字符集
[aaa@qq.com ~]#ll /backup/
total 472
-rw-r--r-- 1 root root 479527 Nov 28 17:42 fullbackup-2019-11-28.sql
拷贝到节点服务器（从服务器192.168.32.18）
[aaa@qq.com ~]#scp /backup/fullbackup-2019-11-28.sql 192.168.32.18:/data/
aaa@qq.com's password:
fullbackup-2019-11-28.sql 100% 468KB 75.8MB/s 00:00
从服务器上操作
[aaa@qq.com ~]#yum install mariadb-server
[aaa@qq.com ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18 #server-id用于区别二进制日志文件 这里用IP作为区分号
read-only #只读 为了安全
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
[aaa@qq.com ~]#systemctl restart mariadb
配置从节点，从完全备份的位置之后开始复制
在配置复制到从节点之前，模拟全备份之后还会有其他的数据库操作
全备份时候的数据查看：
[aaa@qq.com ~]#mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)
模拟全备份后的数据库操作：
MariaDB [(none)]> drop database db1;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> drop database db2;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> create database test.db;
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb | #全备份后数据库操作 其中还删除了数据库db1和db2
+--------------------+
4 rows in set (0.000 sec)
全备份迁移
先要查找迁移全备份的位置。
[aaa@qq.com ~]#grep -i '^change master to' /data/fullbackup-2019-11-28.sql
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
上面的查询表面在MASTER_LOG_FILE=‘mariadb-bin.000003’, MASTER_LOG_POS=389之后的数据就是全备份之后数据库发生变化的数据。
可以直接在需要导入的全备份数据前面增加如下命令：
[aaa@qq.com ~]#vim /data/fullbackup-2019-11-28.sql
CHANGE MASTER TO
MASTER_HOST='192.168.32.8',
MASTER_USER='kaivi',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
增加以上的内容。如果这个文件很大，则在数据库中执行这个命令也一样。
进入从服务器数据库，先关闭二进制日志文件，以免产生导入无用日志。
[aaa@qq.com ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select @@sql_bin_log;
ERROR 1193 (HY000): Unknown system variable 'sql_bin_log'
MariaDB [(none)]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.000 sec)
MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+
1 row in set (0.000 sec)
MariaDB [(none)]> Ctrl-C -- exit!
Aborted
[aaa@qq.com ~]#mysql < /data/fullbackup-2019-11-28.sql #导入迁移全备份
[aaa@qq.com ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.32.8
Master_User: kaivi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 389
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
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: 389
Relay_Log_Space: 256
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: NULL
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: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)
可以分析到，这里还是迁移全备份时候数据库的日子，Slave_IO_Running和Slave_SQL_Running两个线程也还没有开启。
开启复制线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.32.8
Master_User: kaivi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 772
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 940
Relay_Master_Log_File: mariadb-bin.000003
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: 772
Relay_Log_Space: 1251
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
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
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
Slave_DDL_Groups: 3
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
+--------------------+
4 rows in set (0.000 sec)
数据库以及同步，删除了db1和db2.创建了新的testdb库。
测试是否同步
[aaa@qq.com ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.3.11-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database kaivi;
Query OK, 1 row affected (0.000 sec)
[aaa@qq.com ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kaivi |
| mysql |
| performance_schema |
| testdb |
+--------------------+
5 rows in set (0.001 sec)
上一篇： Redis持久化（数据备份与恢复）