时间戳

首页 > 技术文章 > MySQL备份

MySQL备份

sswind 2019-12-20 11:31 原文

1. 普通备份和还原

mysqldump 备份：

备份：

mysqldump -u 用户名 -p 数据库名 > /备份路径/备份文件名（备份整个库）

mysqldump -u 用户名 -p 数据库名 表名 > /备份路径/备份文件名（备份单个表）

　　备份多个库： --databases 库1,库2

　　备份所有库：--all-databases

　　备份多个表：库名 表1 表2 

还原：mysql 数据库 < 备份文件

注意：还原时，若导入的是某个表，还要指定导入到哪个库中

mysqlhotcopy 备份：

备份：mysqlhotcopy --flushlog -u='用户' -p='密码' --regexp=正则 备份目录

还原：cp -a 备份目录 数据目录（/var/lib/mysql 或者自定义目录）

 

mysqldump 和 mysqlhotcopy 实例：

mysql备份和还原

把数据库 test 备份到 /dbbak 目录下
#mysqldump -uroot -p test > /dbbak/test.sql
模拟数据库test 误删（删除数据库test）
#mysql> drop database test;
通过 test.sql 文件还原
#mysql -uroot -p test < test.sql

备份多个数据库（--databases）
#mysqldump -uroot -p --databases aa test > abc.sql
还原
#mysql -uroot -p < abc.sql

备份有规则的数据库
#mysqlhotcopy --flushlog -u='root' -p='123456' --regexp=^a /dbbak/
还原
cp -a a1 a2 a3 aa /usr/local/mysql/data/

mysql-binlog 日志备份

二进制日志（log-bin 日志）：所有对数据库状态更改的操作（create、drop、update等）

修改my.cnf 配置文件 开启binlog 日志记录功能

# vim /etc/my.cnf
log-bin=mysql-bin   #启动二进制日志

按时间还原：

--start-datetime

--stop-datetime

格式：

mysqlbinlog --start-datetime 'YYYY-MM-DD HH:MM:SS' --stop-datetime 'YYYY-MM-DD HH:MM:SS' 二进制日志 |mysql -uroot -p

按文件大小还原：

--start-position

--stop-position

 

mysql-binlog 日志备份示例：

开启二进制日志

 查看二进制日志

 按时间还原：

[root@localhost /usr/local/mysql/data]# mysqlbinlog --start-datetime='2019-12-20 9:57:21' --stop-datetime='2019-12-20 9:58:18' mysql-bin.000006 | mysql -uroot -p

按大小还原：

2. 主从备份

前提条件：安装mysql，开启二进制日志

[root@centos2 ~]# vim /etc/my.cnf
...
[mysqld]
...
log-bin=mysql-bin
server-id=129

...

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

 

mysql> grant replication slave on *.* to root@'192.168.80.131' identified by '123456';
mysql> change master to
    -> master_user='root',
    -> master_password='123456',
    -> master_host='192.168.80.129',
    -> master_log_file='mysql-bin.000014',
    -> master_log_pos=391;

 之后在从服务器上会产生授权信息文件

 

 开启从服务器 start slave，并查看

start slave;        开启从服务器

show slave status\G;         查看从服务器的内容

测试

 

3. 主主备份

3.1 以129 为主，131 为从配置一遍主从

在主配置文件中配置（开启二进制日志和其他内容）

 3.2 在131 上做相同的配置

replicate-do-db=test
#在从服务器上设置需要同步的数据库，要同步多个库时可以写多行，每行一个。
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#指定哪个数据库不用同步

启动MySQL服务

129为主，131为从

主服务授权

从服务器保存授权信息

131为主，129为从

主服务器授权

从服务器保存授权信息

129 和 131 都执行 start slave（互为主从）

 测试

4. 一主多从

　　与配置一主一从类似，配置从服务器时，注意从服务器的server-id 不要相同，

授权：

mysql> grant replication slave on *.* to root@'%' identified by '123456';

从服务器保存授权信息：

mysql> change master to
    -> master_user='root',
    -> master_password='123456',
    -> master_host='192.168.80.130',
    -> master_log_file='mysql-bin.00006',
    -> master_log_pos=265;

测试

5. 多主一从

5.1 主服务器配置

主服务器开启二进制日志，启动服务，注意每台服务器的server-id 不同

[root@localhost ~]# vim /etc/my.cnf
...
log-bin=mysql-bin
server-id=134   
...

授权

mysql> grant replication slave on *.* to root@'192.168.80.136' identified by '123456';

5.2 从服务器配置

修改配置文件

[root@localhost ~]# vim /etc/my.cnf
#插入以下内容

[mysql_multi]
mysqld = /usr/local/mysql/bin/mysql_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
password = 123456

[mysqld134]
port = 3306
datadir=/var/lib/mysqla/
socket = /tmp/mysql.sock1
pid-file = /var/lib/mysqla/134.pid
user = mysql
server-id = 136

[mysqld135]
port = 3307
datadir=/var/lib/mysqlb/
socket = /tmp/mysql.sock2
pid-file = /var/lib/mysqla/135.pid
user = mysql
server-id = 136

初始化数据库，创建目录 mysqla，mysqlb

[root@localhost /usr/local/mysql]# ./scripts/mysql_install_db --datadir=/var/lib/mysqla --user=mysql
[root@localhost /usr/local/mysql]# ./scripts/mysql_install_db --datadir=/var/lib/mysqlb --user=mysql

设置 mysqla，mysqlb 目录及以下文件的属主为mysql（防止出现权限问题）

[root@localhost /var/lib]# chown -R mysql.mysql mysqla
[root@localhost /var/lib]# chown -R mysql.mysql mysqlb


[root@localhost /usr/local]# chown -R mysql mysql/

启动从服务器线程

[root@localhost ~]# mysqld_multi --defaults-file=/etc/my.cnf start 134
[root@localhost ~]# mysqld_multi --defaults-file=/etc/my.cnf start 135

 登录并保存授权信息

[root@localhost ~]# mysql -P 3306 -S /tmp/mysql.sock1
[root@localhost ~]# mysql -P 3307 -S /tmp/mysql.sock2
mysql> change master to
    -> master_user='root',
    -> master_password='123456',
    -> master_host='192.168.80.135',
    -> master_log_file='mysql-bin.000009',
    -> master_log_pos=566;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
mysql> change master to
    -> master_user='root',
    -> master_password='123456',
    -> master_host='192.168.80.134',
    -> master_log_file='mysql-bin.000009',
    -> master_log_pos=566;
Query OK, 0 rows affected (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

测试

推荐阅读