不加任何参数直接备份
mysqldump -uroot zabbix >/opt/zabbix.bak.sql
恢复，这样恢复时需要自已创建表
mysql -uroot < zabbix.bak.sql
加-B备份的时候增加建表语句
mysqldump -uroot -p -B zabbix > /opt/mysql.bak.B.sql
diff zabbix.bak.sql zabbix.B.bak.sql
mysql -uroot -p < /opt/zabbix.B.bak.sql
备份时压缩
[root@zabbix opt]# mysqldump -uroot -p --compact -B zabbix |gzip > /opt/zabbix.B.bak.compact.sql.gz
Enter password:
[root@zabbix opt]# ll -h
total 72M
w-r--r-- 1 root root 22M May 29 21:27 zabbix.bak.sql
?w-r--r-- 1 root root 22M May 29 21:33 zabbix.B.bak.compact.sql
-rw-r--r-- 1 root root 6.9M May 29 21:35 zabbix.B.bak.compact.sql.gz
-rw-r--r-- 1 root root 22M May 29 21:27 zabbix.B.bak.sql
[root@zabbix opt]# ls
mysqldump.txt zabbix.bak.sql zabbix.B.bak.compact.sql zabbix.B.bak.compact.sql.gz zabbix.B.bak.sql
恢复压缩备份
[root@zabbix opt]# gzip -d zabbix.bak.sql.gz
备份多个库
[root@zabbix opt]# for i in `mysql -e "show databases;" |egrep -v "infor|perf|Database"` ; do mysqldump -uroot -p -B $i > $i.bak.sql;done
Enter password:
Enter password:
[root@zabbix opt]#
[root@zabbix opt]# ls
mysql.bak.sql zabbix.bak.sql
[root@zabbix opt]# ll -h
total 23M
-rw-r--r-- 1 root root 504K May 29 22:23 mysql.bak.sql
-rw-r--r-- 1 root root 22M May 29 22:23 zabbix.bak.sql
备份单个表
mysqldump -uroot -p -S /tmp/mysql.sock db_name tbl_name > db_tbl.sql
--events可以在给出警告的时候不提示警告
用sed命令拼接的方法备份
[root@zabbix opt]# mysql -e "show databases;" |egrep -v "infor|perf|Database"|sed -r 's#(.*)#mysqldump -uroot -p -B & > &.bak.sql#g'|bash
Enter password:
Enter password:
[root@zabbix opt]# ls
mysql.bak.sql zabbix.bak.sql
用awk命令拼接的方法备份
[root@zabbix opt]# mysql -e "show databases;" |egrep -v "infor|perf|Database"|awk '{print "mysqldump -uroot -p -B "$0" > "$0".bak.sql"}'|bash
Enter password:
Enter password:
[root@zabbix opt]# ls
mysql.bak.sql zabbix.bak.sql
备份所有库数据的同时还刷新binlog
[root@zabbix opt]# mysqldump -uroot -p -A -B -F > mysql.all.sql
Enter password:
[root@zabbix opt]# ll -h
total 45M
-rw-r--r-- 1 root root 23M May 29 23:13 mysql.all.sql
-rw-r--r-- 1 root root 504K May 29 22:29 mysql.bak.sql
-rw-r--r-- 1 root root 22M May 29 22:29 zabbix.bak.sql
命令
mysql -e相关命令
show processlist;
查看数据库里正在执行的SQL语句，可能无法看全完整SQL语句
show full processlist;
查看正在执行的完整SQL语句，完整显示
set global key_buffer_size = 1024*1024*16
不重启数据库调整数据库参数，直接生效，重启后失效
show variables;
查看数据库的配置参数信息，例如:my.cnf里参数的生效情况
show variables like '%log_bin%';
全局生效的方法，改/etc/my.cnf，然后在mysql命令行set设置
MariaDB [(none)]> show variables like 'key_buffer%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| key_buffer_size | 134217728 |
+-----------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> set global key_buffer_size=1024*1024*32;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show variables like 'key_buffer%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 33554432 |
+-----------------+----------+
1 row in set (0.00 sec)
kill ID
杀掉SQL线程的命令，ID为线程号
show session status;
查看当前会话的数据库状态信息。
show global status;
查看整个数据库运行状态信息，很重要，要分析并要做好监控。
show engine innodb status;
显示innodb引擎的性能状态（早期版本show innodb status;）
#### mysqladmin
```options
设置密码
mysqladmin password oldboy123
修改密码
mysqladmin -uroot -poldboy123 password oldboy
查看状态
相当于show status; mysqladmin -uroot -poldboy123 status;
每秒查看一次状态
mysqladmin -uroot -poldboy123 -i 1 status;
等同show global status;
mysqladmin -uroot -poldboy123 extended-status;
切割日志
mysqladmin -uroot -poldboy123 flush-logs
查看执行的SQL语句信息
mysqladmin -uroot -poldboy123 processlist;
mysqladmin -uroot -poldboy123 processlist -i 1; 每秒查看一次
关闭mysql服务
mysqladmin -uroot -poldboy123 shutdown
相当于show variables
mysqladmin -uroot -poldboy123 variables;
mysqldump
-B,--databases：会在备份的数据中增加建库（create）及"use库"的语句，可以直接接多个库名，同时备份多个库
-A,--all-databases:备份所有数据库。
-d,--no-data:只备份库表结构（SQL语句形式），没有行数据
-t,--no-create-info:只备份表内行数据（SQL语句形式）。没有表结构
-T，--tab=name:将库表和数据分离成不同的文件，行数据是纯文本，表结构是SQL语句，5.6版本默认没有权限操作，需要修改my.cnf参数。
-F，刷新binlog日志，生成新binlog文件，
--master-data={1|2},在备份结果中增加binlog日志文件名及对应的binlog位置点，会打开--lock-all-tables功能，除非有--single-transaction在，使用该参数时会关闭--lock-tables功能
-x,--lock-all-tables,备份时对所有数据库的表执行全局读锁，期间同时禁止--single-transaction和--lock-tables参数功能
-l,--lock-tables：锁定所有的表为只读
--single-transaction,在备份InoDB引擎数据表时，隔离级别，备份数据不会备份后来写入的数据，用户能正常写入数据
-R，--routines:备份存储过程和函数数据
--triggers，备份触发器数据
--compact,只显示很少的有用输出，适合学习和测试环境调试
mysqlbinlog
binlog是用来记录mysql内部增删改查等对mysql数据库更新的内容的记录
如果在mysqlbinlog里面有drop语句，需要把drop语句删除才能恢复，不然会重新drop表
-d,--database=name
根据指定库拆分binlog(拆分单表binlog可通过SQL关键字过滤)
-r,--result-file=name
指定解析binlog输出SQL语句的文件
-R，--read-from-remote-server
从mysql服务器读取binlog日志，是下面参数的别名，read-from-remote-master=BINLOG-DUMP-NON-GTIDS
#指定位置进行恢复
-j,--start-position=#
读取binlog的起始位置点，#号是具体的位置点
--stop-position=#
读取binlog的停止位置点，#号是具体的位置点
#指定开始和结束时间开始恢复
--start-datetime=name
读取binlog的起始位置点，name是具体的时间，格式为：2004-12-25 11:25:56
--stop datetime=name
读取binlog的停止位置点，name是具体的时间
--base64-output=decode-rows
解析ROW级别binlog日志的方法，例如：mysqlbinlog-base64-output=decode-rows -v mysql-bin.000016
mysql
-u 指定数据库用户
-p 指定数据库密码
-S 指定数据库socket文件
-h 指定数据库主机，默认localhost
-P 指定数据库端口，默认3306
--default-character-sets=name 指定字符集登录数据库或备份
备份脚本
#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
USER=root
PASS='pwd'
TIME=`date +%y%m%d`
FULL_BAKDIR=/backup/full_bak
BINLOG_DIR=/backup/binlog_bak
mkdir -p ${FULL_BAKDIR}/${TIME}
mkdir -p $BINLOG_DIR
function DB_NAME(){
mysql -u$USER -p$PASS -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema"
}
function FULL_BAK(){
for i in `DB_NAME` ;do
mysqldump -u$USER -p$PASS -R -F --triggers --single-transaction --master-data=1 -B $i 2>/dev/null |gzip > ${FULL_BAKDIR}/${TIME}/${i}_${TIME}.sql.gz
done
}
function BINLOG_BAK(){
cd / && \
tar zcf binlog_${TIME}.tar.gz application/mysql/data/mysql-bin* && \
mv binlog_${TIME}.tar.gz /$BINLOG_DIR/
find /backup/binlog_bak/ -name "binlog*.tar.gz" -mtime +3 | xargs rm -f
}
function main(){
FULL_BAK
BINLOG_BAK
}
main
