Contents
- 1 一.MySQL数据库管理之备份与恢复
- 1.1 1.1备份数据的意义及实践
- 1.1.1 1.1.1备份单个数据库
- 1.1.2 1.1.2mysqldump备份加上-B参数的作用
- 1.1.3 1.1.3删除数据库中备份过的库john，然后将备份的数据重新导入数据库
- 1.1.4 1.1.4优化备份文件大小减少输出注释--compact
- 1.1.5 1.1.5指定压缩命令压缩备份数据
- 1.1.6 1.1.6利用mysqldump命令备份多个库（-B参数后可以指定多个库）
- 1.1.7 1.1.7分库备份
- 1.1.8 1.1.8一个库的多个表备份不加-B参数第一个库后面全是表
- 1.1.9 1.1.9备份数据库表结构（不包含数据）
- 1.1.10 1.1.10刷新binglog的参数
- 1.1.11 1.1.11mysqldum命令参数小结
- 1.1.12 1.1.12mysqldum命令在不同引擎的生产场景中的备份
- 1.2 1.2数据库恢复数据
- 1.3 1.3MySQL实现非交互式
- 1.4 1.4MySQLadim命令介绍
一.MySQL数据库管理之备份与恢复
1.1备份数据的意义及实践
数据备份是容灾的基础，是指为防止系统出现操作失误或系统故障导致数据丢失，而将全部或部分数据集合从应用主机的硬盘或阵列复制到其它的存储介质的过程。传统的数据备份主要是采用内置或外置的磁带机进行冷备份。但是这种方式只能防止操作失误等人为故障，而且其恢复时间也很长。随着技术的不断发展，数据的海量增加，不少的企业开始采用网络备份。网络备份一般通过专业的数据存储管理软件结合相应的硬件和存储设备来实现。
其作用是：保证数据的完整型，保护公司的数据
1.1.1备份单个数据库
备份单个数据库及多种参数的使用实践
mysql数据库自带了一个很好用的备份命令，就是mysqldump，它的基本使用如下：
语法：
mysqldump –u <用户名> -p <数据库名> 备份的文件名
范例一：备份名叫john的数据库
不加任何参数进行基础备份
~]# mysqldump -uroot -p12345678 -S /data/3306/mysql.sock john >/opt/john_$(date +%F).sql
1.查看备份的结果
[root@node ~]# grep -vE "#|\*|--|^$" /opt/john_2018-05-13.sql DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `Cno` int(10) NOT NULL COMMENT '课程号', `Cname` varchar(64) NOT NULL COMMENT '课程名', `Ccredit` tinyint(2) NOT NULL COMMENT '学分', PRIMARY KEY (`Cno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `course` WRITE; INSERT INTO `course` VALUES (1001,'linux中高级运维',3),(1002,'linux中高级架构师',5),(1003,'mysql高级Dba',4),(1004,'python运维开发',4),(1005,'java web开发',3); UNLOCK TABLES; DROP TABLE IF EXISTS `sc`; CREATE TABLE `sc` ( `SCid` int(12) NOT NULL AUTO_INCREMENT COMMENT '主键', `Cno` int(10) NOT NULL COMMENT '课程号', `Sno` int(10) NOT NULL COMMENT '学号', `Grade` tinyint(2) NOT NULL COMMENT '学生成绩', PRIMARY KEY (`SCid`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8; LOCK TABLES `sc` WRITE; INSERT INTO `sc` VALUES (1,1001,1,4),(2,1002,1,3),(3,1003,1,1),(4,1004,1,6),(5,1001,2,3),(6,1002,2,2),(7,1003,2,2),(8,1004,2,8),(9,1001,3,4),(10,1002,3,4),(11,1003,3,2),(12,1004,3,8),(13,1001,4,1),(14,1002,4,1),(15,1003,4,2),(16,1004,4,3),(17,1001,5,5),(18,1002,5,3),(19,1003,5,2),(20,1004,5,9); UNLOCK TABLES; DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `Sno` int(10) NOT NULL COMMENT '学号', `Sname` varchar(16) NOT NULL COMMENT '姓名', `Ssex` char(2) NOT NULL COMMENT '性别', `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄', `Sdept` varchar(16) DEFAULT NULL COMMENT '学生所在系别', PRIMARY KEY (`Sno`), KEY `index_Sname` (`Sname`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `student` WRITE; INSERT INTO `student` VALUES (1,'john','男',30,'计算机网络'),(2,'john01','男',30,'computer applica'),(3,'john02','男',28,'物流管理'),(4,'john03','男',29,'computer applica'),(5,'john04','女',26,'计算机科学与技术'),(6,'莹莹','女',22,'护士'); UNLOCK TABLES; DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id` int(4) DEFAULT NULL, `age` tinyint(2) DEFAULT NULL, `name` varchar(16) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `test` WRITE; INSERT INTO `test` VALUES (1,NULL,'john01'),(2,NULL,'卓恩'),(3,NULL,'kaka'),(5,23,'zhangsan'),(6,23,'wanger'); UNLOCK TABLES;
1.1.2mysqldump备份加上-B参数的作用
mysqldump备份时加上-B参数时的备份，然后比较不加-B备份的不同
加上-B备份的操作 ~]# mysqldump -uroot -p12345678 -S /data/3306/mysql.sock -B john >/opt/john-B_$(date +%F).sql 比较两者备份的不同 ~]# vimdiff /opt/john_2018-05-13.sql /opt/john-B_2018-05-13.sql 从下面结果看出，加上-B后，备份的时候，会有创建数据库并use库的过程
1.1.3删除数据库中备份过的库john，然后将备份的数据重新导入数据库
删除数据库john mysql> drop database john; Query OK, 4 rows affected (0.02 sec) 用不同备份的数据分别导入查看其情况 不使用-B备份数据进行恢复 [root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock </opt/john_$(date +%F).sql ERROR 1046 (3D000) at line 22: No database selected [root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock john </opt/john_$(date +%F).sql ERROR 1049 (42000): Unknown database 'john' 解决办法如下： [root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock -e "create database john;" [root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock john </opt/john_$(date +%F).sql 使用-B备份数据进行恢复 ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock </opt/john-B_2018-05-13.sql ##执行成功
1.1.4优化备份文件大小减少输出注释--compact
利用mysqldump命令的--compact参数优化备份的结果 debug调试的时候可以使用
备份时不进行锁表 生产环境不使用
取消注释进行数据备份--compact~]# mysqldump -uroot -p12345678 -S /data/3306/mysql.sock -B john --compact >/opt/john-compact_$(date +%F).sql
1.1.5指定压缩命令压缩备份数据
[root@node ~]# mysqldump -uroot -p12345678 -S /data/3306/mysql.sock -B john |gzip >/opt/john-giz$(date +%F).sql.gz [root@node ~]# ll /opt/john-giz2018-05-13.sql.gz -rw-r--r-- 1 root root 1531 May 13 19:25 /opt/john-giz2018-05-13.sql.gz
小结：
- 备份数据使用-B参数，会在备份数据中增加建库及use库的语句
- 备份数据使用-B参数，后面可以直接接多个库名
- 用gzip对备份数据进行压缩
- debug调试的时候可以使用–compact减少输出，不用于生产环境
- 指定字符集进行备份用–default-character-set=utf8或latin1（一般不会使用）
- mysqldump的备份原理是导出sql语句
1.1.6利用mysqldump命令备份多个库（-B参数后可以指定多个库）
备份多个库john和test数据库
[root@node ~]# mysqldump -uroot -p12345678 -S /data/3306/mysql.sock -B john test >/opt/john_test_$(date +%F).sql
mysql> drop database john; drop database test;
Query OK, 4 rows affected (0.01 sec)
Query OK, 4 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
[root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock </opt/john_test_$(date +%F).sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| john |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
1.1.7分库备份
单个库出现问题，恢复起来比较便捷
分库备份（对mysql、john、test、库进行备份） 利用命令进行备份~]# for i in {1..3};do mysqldump -uroot -p12345678 -S /data/3306/mysql.sock --events -B $(mysql -uroot -p12345678 -S /data/3306/mysql.sock -e "show databases;"|sed '1d;2d;5d') |gzip>/opt/$i_db.sql.gz;done或者[root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock -e "show databases;"|egrep -i -v "data|info|per"|sed -r 's#(.*)#mysqldump -uroot -p12345678 -S /data/3306/mysql.sock --events -B & |gzip >/opt/db3306-&.sql.gz #g'|bash
[root@node ~]# ll /opt/db3306-*.gz -rw-r--r-- 1 root root 1549 May 13 21:17 /opt/db3306-john.sql.gz -rw-r--r-- 1 root root 144425 May 13 21:17 /opt/db3306-mysql.sql.gz -rw-r--r-- 1 root root 1499 May 13 21:17 /opt/db3306-test.sql.gz
1.1.8一个库的多个表备份不加-B参数第一个库后面全是表
[root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | john | | mysql | | performance_schema | | test | +--------------------+ [root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock -e "use test;show tables;" +----------------+ | Tables_in_test | +----------------+ | course | | sc | | student | | test | +----------------+[root@node ~]# mysql -u root -p12345678 -S /data/3306/mysql.sock -e "use test;show tables"|sed '1'd|sed -r 's#(.*)#mysqldump -u root -p12345678 -S /data/3306/mysql.sock test & >/opt/table_&.sql#g'mysqldump -u root -p12345678 -S /data/3306/mysql.sock test course >/opt/table_course.sql mysqldump -u root -p12345678 -S /data/3306/mysql.sock test sc >/opt/table_sc.sql mysqldump -u root -p12345678 -S /data/3306/mysql.sock test student >/opt/table_student.sql mysqldump -u root -p12345678 -S /data/3306/mysql.sock test test >/opt/table_test.sql 命令如下：~]# mysql -u root -p12345678 -S /data/3306/mysql.sock -e "use test;show tables"|sed '1'd|sed -r 's#(.*)#mysqldump -u root -p12345678 -S /data/3306/mysql.sock test & >/opt/table_&.sql#g'|bash
补充说明：
分表备份缺点：文件多，琐碎
- 备份一份完整的备份数据，在做一份分库分表的备份
- 脚本批量恢复多个SQL备份文件
问题：多个库或者多个表备份到一块了，如何恢复单个库或者表
- 导入到测试库里，然后把需要的备份出来，最后恢复到正式库里
- 单表：grep 表名 bak.sql >表名.sql
单库：循环过滤库里所有表，grep 表名 bak.sql >表名.sql （多个表数据） - 事先做好分库分表备份
1.1.9备份数据库表结构（不包含数据）
利用mysqldump -d参数只备份表的结构，例如：john库的所有表结构
或者指定-t，--no-create-info参数直接备份数据；
~]# mysqldump -u root -p12345678 -S /data/3306/mysql.sock -B -d john >/opt/db-d.sql [root@node ~]# egrep -v "#|\*|--|^$" /opt/db-d.sql USE `john`; DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `Cno` int(10) NOT NULL COMMENT '课程号', `Cname` varchar(64) NOT NULL COMMENT '课程名', `Ccredit` tinyint(2) NOT NULL COMMENT '学分', PRIMARY KEY (`Cno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `sc`; CREATE TABLE `sc` ( `SCid` int(12) NOT NULL AUTO_INCREMENT COMMENT '主键', `Cno` int(10) NOT NULL COMMENT '课程号', `Sno` int(10) NOT NULL COMMENT '学号', `Grade` tinyint(2) NOT NULL COMMENT '学生成绩', PRIMARY KEY (`SCid`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `Sno` int(10) NOT NULL COMMENT '学号', `Sname` varchar(16) NOT NULL COMMENT '姓名', `Ssex` char(2) NOT NULL COMMENT '性别', `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄', `Sdept` varchar(16) DEFAULT NULL COMMENT '学生所在系别', PRIMARY KEY (`Sno`), KEY `index_Sname` (`Sname`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id` int(4) DEFAULT NULL, `age` tinyint(2) DEFAULT NULL, `name` varchar(16) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.1.10刷新binglog的参数
binglog是什么，记录数据库更新的SQL语句，二进制文件 mysqldump用于定时对某一时刻的数据的全备，例如：00点进行备份bak.sql 增量备份：当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的文件里，这个文件就叫做binglog文件 10点丢失数据需要恢复数据 1、00点时刻备份的bak.sql数据还原到数据库，这个时候数据恢复到了00点 2、00点-10:00数据，就要从binglog里恢复 binglog文件生效需要一个参数：log-bin ~]# grep log-bin /data/3306/my.cnf log-bin = /data/3306/mysql-bin [root@node ~]# ll /data/3306/mysql-bin.0000* -rw-rw---- 1 mysql mysql 107 May 11 19:15 /data/3306/mysql-bin.000001 -rw-rw---- 1 mysql mysql 107 May 11 19:20 /data/3306/mysql-bin.000002 -rw-rw---- 1 mysql mysql 107 May 11 19:23 /data/3306/mysql-bin.000003 -rw-rw---- 1 mysql mysql 107 May 11 19:24 /data/3306/mysql-bin.000004 -rw-rw---- 1 mysql mysql 126 May 11 19:49 /data/3306/mysql-bin.000005 -rw-rw---- 1 mysql mysql 126 May 11 19:54 /data/3306/mysql-bin.000006 -rw-rw---- 1 mysql mysql 126 May 12 16:36 /data/3306/mysql-bin.000007 -rw-rw---- 1 mysql mysql 126 May 12 16:41 /data/3306/mysql-bin.000008 -rw-rw---- 1 mysql mysql 264 May 12 16:45 /data/3306/mysql-bin.000009 -rw-rw---- 1 mysql mysql 770 May 12 16:50 /data/3306/mysql-bin.000010 -rw-rw---- 1 mysql mysql 1634003 May 13 22:32 /data/3306/mysql-bin.000011 -rw-rw---- 1 mysql mysql 150 May 13 22:36 /data/3306/mysql-bin.000012 -rw-rw---- 1 mysql mysql 150 May 13 22:36 /data/3306/mysql-bin.000013 -rw-rw---- 1 mysql mysql 107 May 13 22:36 /data/3306/mysql-bin.000014 binglog日志切割：确定全备和增量的临界点 -F刷新binglog日志，生成新文件，将来增量恢复从这个文件开始 --master-data在备份语句里添加CHANGE MASTER语句及binglog文件及位置点信息 值为1，可执行的CHANGE MASTER语句 值为2，注释的--CHANGE MASTER语句 --master-data除了增量恢复确定临界点外，做主从复制时作用更大 使用--master-data命令如下： ~]# mysqldump -u root -p12345678 -S /data/3306/mysql.sock -B john --master-data=1 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=107; ~]# mysqldump -u root -p12345678 -S /data/3306/mysql.sock -B john --master-data=2 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=107;
1.1.11mysqldum命令参数小结
1、-B备份多个库（添加create和use库的语句）
2、-d只备份库表结构
3、-t只备份数据（sql语句形式）
4、-T分离库表和数据不同的文件，数据是文本，非SQL语句
5、-A备份数据库中所有的数据
6、--compact去掉注释，适合调试输出，生产不使用
7、-F刷新binglog日志，生成新文件，将来增量恢复从这个文件开始
8、--master-data增加binlog日志文件名及对应的位置点（即CHANGE MASTER语句）
--master-data=1不注释，--master-data=2注释
9、-x –lock-all-tables 锁表，当某一时刻备份数据时需要加入此参数，以确定备份数据时是从某一时刻开始的
10、-l –lock-tables 对读锁表
11、--single-transaction 适合innodb事务数据库备份
innodb表在备份时，通常启动选项—single-transaction来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为REPEATABLE READ,以确保本次会话（dump）时，不会看到其他会话已经提交了的数据
12、-q 不做缓冲查询，直接导入输出
1.1.12mysqldum命令在不同引擎的生产场景中的备份
myisam引擎企业生产备份命令（适合所有引擎或混合引擎） mysqldump –uroot –p12345678 –A –B –F –R –master-data=2 –x –events|gzip >/opt/all.sql.gz 提示-F也可以不用，与—master-data有些重复 -R备份存储过程 innodb引擎企业生产备份命令：推荐使用的 mysqldump –uroot –p12345678 –A –B –F –R --master-data=2 –events –single-transaction|gzip>/opt/all.sql.gz 提示:-F也可以不用，与—master-data有些重复 其中—master-data作用： 使用—master-data=2进行备份文件会增加如下内容，适合普通备份增量恢复 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000024', MASTER_LOG_POS=107; 额外补充： 50G以内的数据 1、mysqldump逻辑备份 缺点：效率不是特别高。 优点：简单、方便、可靠、迁移，适用与数据量不是特别大的场景 超过50G的数据 1、 xtrabackup物理备份工具：全量和增量物理备份方案： 2、 物理备份方案：从库停止SQL线程，打包，cp 什么时候会使用mysqldump备份的数据？ 1、 恢复数据到测试库 2、 人为通过SQL将数据删除的时候 3、 主从复制
1.2数据库恢复数据
1.2.1数据库恢复事注意事项
提示说明：
数据恢复和字符集关联很大，如果字符集不正确会导致恢复的数据乱码
mysql命令以及source命令恢复数据库的原理就是把文件的SQL语句，在数据库里重新执行的过程
UTF8数据库，恢复的文件格式需要为“UTF8没有签名”格式 <<另存为--编码--UTF8没有签名>>
1.2.2利用source命令进行恢复
进入mysql数据库控制台，mysql –uroot –p登陆后 mysql>use 数据库 然后使用source命令，后面参数为脚本文件（如这里用到的sql） mysql>source bak_db.sql 这个文件是系统路径，默认是登录mysql前的系统路径 下面来做个例子利用source命令来对数据库中的john库进行恢复 1.删除库john前对数据库中的john库进行备份 ~]# mysqldump -u root -p12345678 -B john -S /data/3306/mysql.sock >/tmp/john_bak.sql 2.进入数据库控制台，删除数据库john，然后进行恢复 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | john | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> drop database john; Query OK, 4 rows affected (0.02 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> source /tmp/john_bak.sql mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | john | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
1.2.3利用mysql命令恢复（标准）
~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock </tmp/john_bak.sql mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | john | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) 注意: 假定开发人员让我们插入数据到数据库（可能是邮件发给我们的，内容可能是字符串或者下面的文件） sql文件里没有use db这样的字样时，在导入时就要指定数据库名了
1.2.4针对压缩的备份数据恢复
第一种方法： 删除源文件的备份方法： gzip –d /opt/mysql_bak.sql.gz mysql –uroot –p12345678 </opt/mysql_bak.sql 不删除源文件的进行解压： gzip –cd 01.sql.gz >02.sql 第二种方法： gunzip <john-giz2018-05-14.sql.gz >/opt/john-giz2018-05-14.sql gunzip <db_bak.sql.gz|mysql -uroot -p${passwd} 如下 gunzip <john-giz2018-05-14.sql.gz |mysql -uroot -p12345678 -S /data/3306/mysql.sock
1.3MySQL实现非交互式
1.3.1使用mysql -e参数查看MySQL数据库
[root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | john | | mysql | | performance_schema | | test | +--------------------+
1.3.1使用mysql -e参数查看SQL线程完整状态
[root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock -e "show full processlist;" +----+------+-----------+------+---------+------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+-----------------------+ | 12 | root | localhost | NULL | Sleep | 2170 | | NULL | | 22 | root | localhost | NULL | Query | 0 | NULL | show full processlist | +----+------+-----------+------+---------+------+-------+-----------------------+ 解决mysql sleep线程过多问题案例： mysql> kill 22 mysql> show variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 120 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 28800 | +----------------------------+----------+ 10 rows in set (0.00 sec) 解决办法： 第一步：mysql命令行 mysql> set global interactive_timeout = 60; Query OK, 0 rows affected (0.04 sec) mysql> set global wait_timeout = 60; Query OK, 0 rows affected (0.00 sec) 第二步： 配置文件里面修改： [mysqld] interactive_timeout = 60 ##有此参数wait_timeout会自动生效 wait_timeout = 60 第三步：其它方法 1、 php程序中，不使用持久链接，即使用mysql_connect而不是pconnect（JAVA调整连接池） 2、 php程序执行完毕，应该显示调用mysql_close 3、 逐步分析mysql的sql查询及慢查询日志，找到查询国漫的SQL优化之
1.3.2不重启数据库修改数据库参数
1.命令行修改 [root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock -e "show variables;"|grep key_buffer key_buffer_size 16777216 ##myisam索引的缓存区 [root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock -e "set global key_buffer_size = 1024*1024*32;" [root@node ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock -e "show variables;"|grep key_buffer key_buffer_size 33554432 2.配置文件修改 MySQL配置文件修改/etc/my.cnf添加key_buffer_size = 32M 说明： global 全局 session 当前会话
1.3.3常用命令小结
show full processlist; ##查看数据库里正在执行的完整SQL语句，完整显示 set global key_buffer_size = 1024*1024*32 ##不重启数据库调整数据库参数，直接生效，重启后失效 show variables; ##查看数据库的配置参数信息，例如：my.cnf里参数的生效情况 kill ID ##杀掉SQL线程的命令，ID为线程号 show session status; ##查看当前会话的数据库状态信息 show global status; ##查看整个数据库运行状态信息，很重要，分析并做监控 show engine innodb status; ##显示innodb引擎的性能状态
问题：计算每一天的insert,delete,create语句，执行如下命令语句或者命令行mysql -e命令操作
mysql> show global status; +------------------------------------------+-------------+ | Variable_name | Value | +------------------------------------------+-------------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 32 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 | | Bytes_received | 65983 | | Bytes_sent | 168030 | | Com_admin_commands | 0 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 64 | | Com_alter_tablespace | 0 | | Com_analyze | 0 | | Com_begin | 0 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 54 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 8 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_server | 0 | | Com_create_table | 32 | | Com_create_trigger | 0 | | Com_create_udf | 0 | | Com_create_user | 0 | | Com_create_view | 0 | | Com_dealloc_sql | 0 | | Com_delete | 0 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 8 | | Com_drop_event | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_procedure | 0 | | Com_drop_server | 0 | | Com_drop_table | 32 | | Com_drop_trigger | 0 | | Com_drop_user | 0 | | Com_drop_view | 0 | | Com_empty_query | 0 | | Com_execute_sql | 0 | | Com_flush | 12 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 32 | | Com_insert_select | 0 | | Com_install_plugin | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_lock_tables | 36 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_release_savepoint | 0 | | Com_rename_table | 0 | | Com_rename_user | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_resignal | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_rollback_to_savepoint | 0 | | Com_savepoint | 0 | | Com_select | 116 | | Com_set_option | 440 | | Com_signal | 0 | | Com_show_authors | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_collations | 0 | | Com_show_contributors | 0 | | Com_show_create_db | 9 | | Com_show_create_event | 0 | | Com_show_create_func | 0 | | Com_show_create_proc | 0 | | Com_show_create_table | 36 | | Com_show_create_trigger | 0 | | Com_show_databases | 10 | | Com_show_engine_logs | 0 | | Com_show_engine_mutex | 0 | | Com_show_engine_status | 0 | | Com_show_events | 0 | | Com_show_errors | 0 | | Com_show_fields | 36 | | Com_show_function_status | 0 | | Com_show_grants | 0 | | Com_show_keys | 0 | | Com_show_master_status | 5 | | Com_show_open_tables | 0 | | Com_show_plugins | 0 | | Com_show_privileges | 0 | | Com_show_procedure_status | 0 | | Com_show_processlist | 0 | | Com_show_profile | 0 | | Com_show_profiles | 0 | | Com_show_relaylog_events | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 1 | | Com_show_storage_engines | 0 | | Com_show_table_status | 36 | | Com_show_tables | 11 | | Com_show_triggers | 36 | | Com_show_variables | 0 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 0 | | Com_stmt_execute | 0 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 0 | | Com_stmt_reprepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Com_truncate | 0 | | Com_uninstall_plugin | 0 | | Com_unlock_tables | 36 | | Com_update | 0 | | Com_update_multi | 0 | | Com_xa_commit | 0 | | Com_xa_end | 0 | | Com_xa_prepare | 0 | | Com_xa_recover | 0 | | Com_xa_rollback | 0 | | Com_xa_start | 0 | | Compression | OFF | | Connections | 20 | | Created_tmp_disk_tables | 92 | | Created_tmp_files | 6 | | Created_tmp_tables | 200 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 11 | | Handler_commit | 200 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 64 | | Handler_read_first | 39 | | Handler_read_key | 50 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 814 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 542 | | Innodb_buffer_pool_pages_data | 343 | | Innodb_buffer_pool_bytes_data | 5619712 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 356 | | Innodb_buffer_pool_pages_free | 20136 | | Innodb_buffer_pool_pages_misc | 1 | | Innodb_buffer_pool_pages_total | 20480 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 0 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 9415 | | Innodb_buffer_pool_reads | 316 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 3463 | | Innodb_data_fsyncs | 133 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 7360512 | | Innodb_data_reads | 326 | | Innodb_data_writes | 598 | | Innodb_data_written | 11935232 | | Innodb_dblwr_pages_written | 356 | | Innodb_dblwr_writes | 19 | | Innodb_have_atomic_builtins | ON | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 342 | | Innodb_log_writes | 209 | | Innodb_os_log_fsyncs | 95 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 262656 | | Innodb_page_size | 16384 | | Innodb_pages_created | 28 | | Innodb_pages_read | 315 | | Innodb_pages_written | 356 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | | Innodb_rows_deleted | 0 | | Innodb_rows_inserted | 288 | | Innodb_rows_read | 324 | | Innodb_rows_updated | 0 | | Innodb_truncated_status_writes | 0 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 13396 | | Key_blocks_used | 0 | | Key_read_requests | 0 | | Key_reads | 0 | | Key_write_requests | 0 | | Key_writes | 0 | | Last_query_cost | 0.000000 | | Max_used_connections | 2 | | Not_flushed_delayed_rows | 0 | | Open_files | 7 | | Open_streams | 0 | | Open_table_definitions | 2 | | Open_tables | 2 | | Opened_files | 670 | | Opened_table_definitions | 127 | | Opened_tables | 139 | | Performance_schema_cond_classes_lost | 0 | | Performance_schema_cond_instances_lost | 0 | | Performance_schema_file_classes_lost | 0 | | Performance_schema_file_handles_lost | 0 | | Performance_schema_file_instances_lost | 0 | | Performance_schema_locker_lost | 0 | | Performance_schema_mutex_classes_lost | 0 | | Performance_schema_mutex_instances_lost | 0 | | Performance_schema_rwlock_classes_lost | 0 | | Performance_schema_rwlock_instances_lost | 0 | | Performance_schema_table_handles_lost | 0 | | Performance_schema_table_instances_lost | 0 | | Performance_schema_thread_classes_lost | 0 | | Performance_schema_thread_instances_lost | 0 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 1 | | Qcache_free_memory | 2079912 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 84 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | | Queries | 1068 | | Questions | 1068 | | Rpl_status | AUTH_MASTER | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 186 | | Slave_heartbeat_period | 0.000 | | Slave_open_temp_tables | 0 | | Slave_received_heartbeats | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 0 | | Sort_scan | 20 | | Table_locks_immediate | 125 | | Table_locks_waited | 0 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 1 | | Threads_connected | 1 | | Threads_created | 2 | | Threads_running | 1 | | Uptime | 11446 | | Uptime_since_flush_status | 11446 | +------------------------------------------+-------------+ 289 rows in set (0.01 sec)
1.4MySQLadim命令介绍
mysqladmin password 123456 ##为mysql用户添加密码 mysqadmin –uroot –p12345678 password 12345678 ##为mysql用户修改密码 mysqladmin –uroot –p12345678 status ##查看mysql的状态 mysqladmin -u root -p12345678 -S /data/3306/mysql.sock -i 1 status ##每秒mysql的状态 mysqladmin -u root -p12345678 -S /data/3306/mysql.sock flush-logs ##刷新binlog日志 mysqladmin -u root -p12345678 -S /data/3306/mysql.sock processlist ##查看mysql的线程状态 mysqladmin -u root -p12345678 -S /data/3306/mysql.sock processlist -i 1 ##1每秒查看mysql的线程状态 mysqladmin -u root -p12345678 -S /data/3306/mysql.sock extended-status ##查看mysql的所有状态信息 mysqladmin -u root -p12345678 -S /data/3306/mysql.sock shutdown ##优雅关闭mysql服务 mysqladmin -u root -p12345678 -S /data/3306/mysql.sock variables ##查看mysql配置文件的信息 mysqlbinlog -u root -p12345678 -S /data/3306/mysql.sock --start-positinon=367 --stop-positinon=669 master-bin.000005 >/path/file ##清楚669binlog位置点后的删除信息
如无特殊说明，文章均为本站原创，转载请注明出处
- 转载请注明来源：MySQL数据库管理之备份与恢复
- 本文永久链接地址：https://www.xionghaier.cn/archives/462.html
该文章由 John 发布
