mysql备份、多表查询

一、 全量备份和恢复

备份

[root@mariadb ~]# mysql -uroot -p1 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tj                 |
| tj1                |
+--------------------+
[root@mariadb ~]# mysqldump -uroot -p1 --all-databases > all-$(date '+%Y%m%d').sql
[root@mariadb ~]# ls
all-20210826.sql 

删除

[root@mariadb ~]# mysql -uroot -p1 -e "drop database tj"
[root@mariadb ~]# mysql -uroot -p1 -e "drop database tj1"
[root@mariadb ~]# mysql -uroot -p1 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

恢复

[root@mariadb ~]# mysql -uroot -p1 < all-20210826.sql 
[root@mariadb ~]# mysql -uroot -p1 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tj                 |
| tj1                |
+--------------------+

二、 差异备份和恢复

开启二进制

[root@mariadb my.cnf.d]# vim mariadb-server.cnf
[root@mariadb my.cnf.d]# pwd
/etc/my.cnf.d
[root@mariadb my.cnf.d]# vim mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=1         #设置服务器标识符
log-bin=mysql_bin   #开启二进制日志功能
[root@mariadb ~]# systemctl restart mariadb.service
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.000 sec)

[root@mariadb my.cnf.d]# ls /var/lib/mysql/
aria_log.00000001  ib_logfile1        mysql-bin.000002  mysql-bin.000007  mysql.sock          tj1
aria_log_control   ibtmp1             mysql-bin.000003  mysql-bin.000008  mysql_upgrade_info
ib_buffer_pool     multi-master.info  mysql-bin.000004  mysql-bin.000009  performance_schema
ibdata1            mysql              mysql-bin.000005  mysql-bin.index   tc.log
ib_logfile0        mysql-bin.000001   mysql-bin.000006  mysql-bin.state   tj

备份

[root@mariadb ~]# mysqldump -uroot -p1 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-2021-08-26.sql
[root@mariadb ~]# ls
公共  视频  文档  音乐  all-2021-08-26.sql  anaconda-ks.cfg       zy.sh
模板  图片  下载  桌面  all-20210826.sql    initial-setup-ks.cfg
对库进行修改
MariaDB [tj]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.000 sec)

MariaDB [tj]> update student set age = 100 where name = 'lisi'
    -> ;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [tj]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry