Mysql数据备份

备份的种类
逻辑备份:SQL语句的备份
物理备份:数据页备份

select xxxx from t1 into outfile '/tmp/redis.txt'

-A 全部备份
mysqldump -uroot  -p --master-data=2 -A >/backup/mybackup;

-B 备份一个或多个库
mysqldump -uroot -p --master-data=2 -B 库名1 库名2 >/backup/mybackup;

备份单库中的表
mysqldump -uroot --master-data=2 -p 库名 表名>/backup/mybackup;

--master-data=2 备份时记录二进制日志状态
--single-transaction 开启innodb热备功能

---

主从复制基于二进制日志完成的.

1、3307中创建复制用户

主库开启二进制日志
vim /data/3307/my.cnf
log_bin=/data/3307/mysql-bin

[root@standby 3307]# systemctl restart mysqld3307

[root@standby backup]# mysql -S /data/3307/mysql.sock
grant replication slave on . to repl@'10.0.0.%' identified by '123';

mysql> show master status
-> ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.3308节点开启主从复制功能

[root@standby 3307]# mysql -S /data/3308/mysql.sock

mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.200',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

mysql> start slave;
mysql> show slave status\G

如果显示：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
表示成功了