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;
测试