Mysqldump备份
一、mysqldump备份数据库
mysqldump 是逻辑备份，缺点：效率不是很高，优点：简单、方便、可靠，迁移；
适用于数据量50G以内的数据库；
1.1、不加参数备份
[root@mysql ~]# mysqldump -uroot -p db1>db_$(date +%F)db.sql
Enter password:
查看备份后的文件，可以看出，他是把数据库中的数据，以SQL语句的形式导出，备份记录中，首先删除掉表，然后重建
DROP TABLE IF EXISTS `aa`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `aa` ( `uname` varchar(50) DEFAULT NULL, `pass` char(2) DEFAULT NULL, `uid` int(11) DEFAULT NULL, `gid` int(11) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `home` varchar(50) DEFAULT NULL, `shell` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; /*!40101 SET character_set_client = @saved_cs_client */;
1.2、加B参数
[root@mysql ~]# mysqldump -uroot -p -B db1 >db_B_$(date +%F).sql
Enter password:
查看备份文件，发现多了创建数据库的语句
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `db1`;
B参数还可以指定多个库
[root@mysql ~]# mysqldump -uroot -p -B db1 db2>db12_B_$(date +%F).sql
Enter password:
1.3、恢复测试
删除数据库
(root@localhost) [db1]> drop database db1; Query OK, 14 rows affected (0.42 sec)
恢复，加了B参数的sql文件
[root@mysql ~]# mysql -uroot -p< db_B_2024-03-16.sql Enter password:
1.4、compact参数
compact参数取消注释，还取消了droptables等
[root@mysql ~]# mysqldump -uroot -p -B db1 db2 --compact>db12compacct_$(date +%F).sql
Enter password:
1.5、压缩备份文件
[root@mysql ~]# mysqldump -uroot -p -B db1 db2|gzip >db12gzip_$(date +F%).sql.gz
Enter password:
1.6、拼接出用户数据库
[root@mysql dump]# mysql -uroot -proot -e "show databases;" |grep -vE "Database|_schema|mysql"
二、mysqldump备份单个表
2.1、备份单个表
[root@mysql dump]# mysqldump -uroot -p db1 aa --compact > /root/dump/aa2.sql
Enter password:
2.2、备份表结构 参数d
[root@mysql dump]# mysqldump -uroot -p db1 ab -d --compact>/root/dump/ab2.sql
Enter password:
2.3、备份表中的数据
[root@mysql dump]# mysqldump -uroot -p db1 ab -t --compact >/root/dump/abt.sql
Enter password:
2.4、刷新binlog，参数F，
可以确定全备和增量的临界点
[root@mysql dump]# mysqldump -uroot -p db1 ab -t --compact -F >/root/dump/abt.sql
Enter password:
三、--master-data参数
--master-data参数，在备份语句中添加CHANGE-MASTER语句，及binlog文件位置点信息；
值为1，为可执行的CHANGE-MASTER语句；
值为2，为注释的CHANGE-MASTER语句；
[root@mysql dump]# mysqldump -uroot -p db1 ab --master-data=1
备份文件中多了如下语句：
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000067', MASTER_LOG_POS=154;
四、mysqldump参数总结
-B 指定多个库，增加建库语句和use语句；
--compact 去掉注释，适合调试，不适合生产；
-A 备份所有库
-F 刷新binlog日志，生产新文件，将来的增量恢复从这个文件开始；
--master-data 增加binlog日志文件名及对应的日志点，参数1不注释，参数2注释
-x 锁表
-l 只读锁表
-d 只备份表结构
-t 只备份数据
--single-transaction 适合InnoDB引擎，保障事务的一致性
-q 不做缓存
-R 备份存储过程和函数
五、生产环境备份推荐
[root@mysql dump]# mysqldump -uroot -p -A -B -R -F --master-data=2 --events --single-transaction |gzip >/root/dump/all.sql.gz Enter password:
六、数据库恢复
数据库恢复和字符集的关系很大，字符集不正确导致乱码；
mysqldump备份的恢复就是将sql语句重新执行；
6.1、source命令恢复
(root@localhost) [db1]> source /root/dump/aa2.sql
6.2、使用mysql命令恢复
[root@mysql ~]# mysql -uroot -p </root/dump/db2.sql
Enter password:
七、PROCESSLIST
6.1、查看连接进程
查看连接，如果是root用户，看到所有用户的当前连接，如果是其它普通帐号，只能看到自己占用的连接。
(root@localhost) [(none)]> show processlist; +----+------+-----------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+------------------+ | 2 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+-----------+------+---------+------+----------+------------------+ 1 row in set (0.00 sec)
6.2、解决sleep进程过多问题
查询sleep进程
(root@localhost) [db1]> select * from information_schema.PROCESSLIST where COMMAND='Sleep' order by time desc ;
临时解决可以用kill 加id 号结束进程；
长远解决方案：
修改my.cnf
interactive_timeout=120 wait_timeout=120
wait_timeout指的是mysql在关闭一个非交互的连接之前所要等待的秒数，其取值范围为1-2147483(Windows)，1-31536000(linux)，默认值28800。
interactive_timeout指的是mysql在关闭一个交互的连接之前所要等待的秒数(交互连接如mysql gui tool中的连接)，其取值范围随wait_timeout变动，默认值28800。
优化代码
查看代码是否每一次都有conn.close(),尤其是有一些代码出了异常后没有进行close
八、variables
8.1、查看版本
(root@localhost) [(none)]> Show global variables like '%innodb_version%' ; +----------------+--------+ | Variable_name | Value | +----------------+--------+ | innodb_version | 5.7.26 | +----------------+--------+ 1 row in set (0.00 sec)
8.2、查看软件目录
(root@localhost) [(none)]> show variables like '%basedir%'; +---------------+-------------+ | Variable_name | Value | +---------------+-------------+ | basedir | /mysql3306/ | +---------------+-------------+ 1 row in set (0.01 sec)
8.3、查看数据目录
(root@localhost) [(none)]> show variables like '%datadir%'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | datadir | /mysql3306/data/ | +---------------+------------------+ 1 row in set (0.00 sec)
8.4、查看端口
(root@localhost) [(none)]> show variables like '%port%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_support_xa | ON | | large_files_support | ON | | port | 3306 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | | require_secure_transport | OFF | +--------------------------+-------+ 8 rows in set (0.01 sec)
8.5、查看字符集
(root@localhost) [(none)]> show variables like '%character%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /mysql3306/share/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
8.6、查看binlog日志
(root@localhost) [(none)]> show variables like '%log_bin%'; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /mysql3306/binlog/binlog | | log_bin_index | /mysql3306/binlog/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+--------------------------------+ 6 rows in set (0.01 sec)
8.7、慢查询
(root@localhost) [(none)]> show variables like '%_query%'; +------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | | have_query_cache | YES | | long_query_time | 10.000000 | | slow_query_log | ON | | slow_query_log_file | slow.log | +------------------------------+-----------+ 6 rows in set (0.00 sec)
8.8、查看数据库状态
(root@localhost) [db2]> show status; +-----------------------------------------------+--------------------------------------------------+ | Variable_name | Value | +-----------------------------------------------+--------------------------------------------------+ | Aborted_clients | 0 | | Aborted_connects | 1 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 4 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 28 | | Bytes_received | 970 | | Bytes_sent | 16499 | | Com_admin_commands | 0 | | Com_assign_to_keycache | 0 |
8.9、查看状态中的select状态
(root@localhost) [db2]> show status like '%select%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Com_insert_select | 0 | | Com_replace_select | 0 | | Com_select | 4 | | Connection_errors_select | 0 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 26 | +--------------------------+-------+ 9 rows in set (0.00 sec)
8.10、查看insert次数
(root@localhost) [db2]> show status like '%insert%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Com_insert | 0 | | Com_insert_select | 0 | | Delayed_insert_threads | 0 | | Innodb_rows_inserted | 142 | | Qcache_inserts | 0 | +------------------------+-------+ 5 rows in set (0.01 sec)
九、mysqladmin命令
修改密码
[root@mysql ~]# mysqladmin -uroot -p password Root##123 Enter password:
