mysql备份格式_MySQL备份

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

备份多个数据库(--databases)

#mysqldump-uroot -p --databases aa test >abc.sql

还原

#mysql-uroot -p

备份有规则的数据库

#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= 3306datadir=/var/lib/mysqla/socket= /tmp/mysql.sock1

pid-file = /var/lib/mysqla/134.pid

user=mysql

server-id = 136[mysqld135]

port= 3307datadir=/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.03sec)

mysql>start slave;

Query OK,0 rows affected (0.00sec)

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.04sec)

mysql>start slave;

Query OK,0 rows affected (0.00sec)

mysql> show slave status\G;

测试