mysql备份与多表联查

1.mysql配置文件

mysql的配置文件为/etc/my.cnf

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

[client]
port = 3306		//设置监听端口	
socket = /tmp/mysql.sock	//指定套接字文件位置

[mysqld]
port = 3306
socket = /tmp/mysql.sock

basedir = /usr/local/mysql	//指定MySQL的安装路径
datadir = /data/mysql		//指定MySQL的数据存放路径
pid-file = /data/mysql/mysql.pid	//指定进程ID文件存放路径
user = mysql		//指定MySQL以什么用户的身份提供服务
bind-address = 0.0.0.0	//MySQL服务器的IP地址

server-id = 1 //服务器ID编号

back_log = 600	//MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，主线程会花些时间检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内最多可以有多少个请求存在堆栈中。
max_connections = 1000	//MySQL的最大连接数
max_connect_errors = 6000	//对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST
max_heap_table_size = 8M	//定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变
read_buffer_size = 2M	//MySQL读入缓冲区大小
read_rnd_buffer_size = 8M	//MySQL的随机读缓冲区大小
sort_buffer_size = 8M		//MySQL执行排序使用的缓冲大小
join_buffer_size = 8M		//联合查询操作所能使用的缓冲区大小
query_cache_limit = 2M		//指定单个查询能够使用的缓冲区大小，默认1M
key_buffer_size = 4M		//指定用于索引的缓冲区大小
expire_logs_days = 30 //删除超过指定时间的binlog

log_error = /data/mysql/mysql-error.log //错误日志路径
long_query_time = 1 	//慢查询的执行用时上限
log-slow-queries = [file]	//把执行用时超过long_query_time变量值的查询命令记入日志(慢查询日志); 如果没有给出file参数，MySQL将在数据库目录里创建一个hostname-slow.log文件作为这种日志文件(hostname是服务器主机名)

2.mysql数据库备份与恢复

2.1 备份类型

从策略角度，备份可分为：

• 全量备份：每次对数据进行完整的备份。可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。
  • 特点：数据恢复快、备份时间长
• 增量备份：只有那些在上次完全备份或者增量备份后被修改、或增加的文件才会被备份。
  • 特点：没有重复的备份数据、备份时间短、恢复数据时必须按一定的顺序进行
• 差异备份：备份那些自从上次完全备份之后发生变化（增加、修改）的文件，只备份数据库部分的内容，它比最初的完全备份小
  • 特点：存储和恢复速度快。

从物理与逻辑的角度来看，备份可分为：

• 物理备份：指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可以分为脱机备份(冷备份)和联机备份(热备份)。
  • 冷备份:在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性。
  • 热备份:在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件。
• 逻辑备份：指对数据库逻辑组件(如"表"等数据库对象)的备份。

2.2 mysql备份工具mysqldump

//语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

//备份整个数据库（全备）
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| wys                |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> show tables from wys;
+---------------+
| Tables_in_wys |
+---------------+
| akl           |
| student       |
+---------------+
2 rows in set (0.000 sec)

[root@apache ~]# ls
anaconda-ks.cfg
[root@apache ~]# mysqld
mysqld_multi        mysqld_safe         mysqld_safe_helper  mysqldump           mysqldumpslow
[root@apache ~]# mysqldump -uroot -p --all-databases > all-$(date +%Y%m%d).sql
Enter password: 
[root@apache ~]# ls
all-20210827.sql  anaconda-ks.cfg

//备份wys库
[root@apache ~]# mysqldump -uroot -p --databases wys > wys-$(date +%Y%m%d).sql
Enter password: 
[root@apache ~]# ls
all-20210827.sql  anaconda-ks.cfg  wys-20210827.sql

//备份wys库的student表和akl表
[root@apache ~]# mysqldump -uroot -p wys student akl > tables-20210826.sql
Enter password: 
[root@apache ~]# ls
all-20210827.sql  anaconda-ks.cfg  tables-20210826.sql  wys-20210827.sql

2.3 mysql数据恢复

//模拟误删数据库
MariaDB [(none)]> drop database wys;
Query OK, 2 rows affected (0.005 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

//恢复wys数据库
[root@apache ~]# ls
all-20210827.sql  anaconda-ks.cfg  tables-20210826.sql  wys-20210827.sql
[root@apache ~]# mysql -uroot -p -h127.0.0.1 < wys-20210827.sql 
Enter password: 
[root@apache ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| wys                |
+--------------------+

//恢复wys数据库的student表和akl表
MariaDB [(none)]> use wys;
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 [wys]> show tables;
Empty set (0.000 sec)

MariaDB [wys]> source tables-20210826.sql
Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

......

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

MariaDB [wys]> show tables;
+---------------+
| Tables_in_wys |
+---------------+
| akl           |
| student       |
+---------------+
2 rows in set (0.000