MYSQL备份还原工具、主从复制、高可用

1、使用mysqldump备份数据库并通过备份及二进制日志还原数据(备份完后再写入数据,然后再删库)

环境 InnoDB备份

1. 确定使用的字符集后做完全备份，若有二进制数据建议加上–hex-blob

[root@centos7 ~]#mysql -e "status"
--------------
mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         5.5.60-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 19 min 24 sec

Threads: 1  Questions: 115  Slow queries: 0  Opens: 8  Flush tables: 2  Open tables: 27  Queries per second avg: 0.098
--------------

[root@centos7 ~]#mysqldump -A -F --single-transaction --master-data=2 --default-character=utf8mb4 > /data/backup/all_bak_`date +%F`.sql

2. 完全备份完成后，在表中添加记录，后添加的记录应该记录在二进制日志中

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.01 sec)

MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     26792 |
| mysql-bin.000002 |    921736 |
| mysql-bin.000003 |      7270 |
| mysql-bin.000004 |       245 |
+------------------+-----------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> insert teachers(name)value('g');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [hellodb]> insert teachers(name)value('h');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [hellodb]> show master logs;               
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     26792 |
| mysql-bin.000002 |    921736 |
| mysql-bin.000003 |      7270 |
| mysql-bin.000004 |       627 |
+------------------+-----------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> select * from teachers;         
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | g             |   0 | NULL   |
|   6 | h             |   0 | NULL   |
+-----+---------------+-----+--------+
6 rows in set (0.01 sec)

3. 删库重启服务

[root@centos7 mysql]#rm -rf /var/lib/mysql/*
[root@centos7 mysql]#systemctl restart mariadb

4. 恢复前先临时禁用二进制日志记录，禁止用户访问，再恢复数据库

MariaDB [(none)]> set sql_log_bin=off;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [hellodb]> source /data/backup/all_bak_2019-08-11.sql

MariaDB [test]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

5. 还原二进制日志

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=245;

[root@centos7 ~]#ll