mysql数据库的备份与恢复

4.mysql数据库的备份与恢复

4.1准备工作:首先建表填充数据

对于mysql5.1常规编译方式创建不同字符集格式的数据库命令

create database wj_default;  相当于拉丁字符集

建立一个名为wj_gbk的gbk字符集数据库

mysql> create database wj_gbk default character set gbk collate gbk_chinese_ci;

建立一个名为wj_utf8的utf8字符集数据库

mysql> create database wj_utf8  character set utf8  collate utf8_general_ci;

提示:字符集的不一致是数据库乱码的罪魁祸首,服务器字符集,操作系统字符集,数据库服务端字符集,库与表的字符集,客户端软件字符集

4.2填充中文数据暴露乱码问题

4.2.1mysql命令行插入中文乱码问题解决:

1.set names gbk;只能是查询时候用

2.在文件中指定set names gbk;

使用source命令时,将sql文件传入到linux系统上,不要切换目录,登录mysql,使用source命令就可以找到文件了

[wj@hcf ~]$ mysql -uroot -p'wj123' wj <test.sql     wj库名

[wj@hcf ~]$ mysql -uroot -p'wj123' wj -e "set names utf8;select * from wj.test;"

ERROR 1193 (HY000) at line 1: Unknown system variable 'name'

3.mysql –uroot –p’wj123’ –default-character-set=gbk <test.sql

Starting Mysql .. Error! The server quit without updating PID file(/application/mysql-5.5.32/data/C64.pid).

Rm –f /application/mysql-5.5.32/data/C64.pid

cat /application/mysql-5.5.32/data/C64.err

4.2.2查看字符集:

mysql> show character set;

[wj@hcf tools]$ mysql -uroot -p'wj123' wj -e "show character set;"|egrep "gbk|gb2312|utf|latin"|awk '{print $1}'

 

show variables;

更改my.cnf客户端模块的参数,可以实现set names gbk的效果,并且永久生效

vi /etc/my.cnf中在[client]下加default-character-set=gbk提示:无需重启服务,退出重新登录就生效.

更改服务端字符集简单的方法:

更改my.cnf参数(mysql5.5不支持了,mysql5.1及以前版本支持)

[mysqld] default-character-set=gbk(5.1及以前版本)  character_set_database   character_set_server

                   character-set-server=gbk(5.5)

4.5数据库备份及压缩备份

mysqldump -uroot -p'wj123'wj >/opt/$(date+%F).sql

mysqldump -uroot -p'wj123' –B wj >/opt/$(date+%F).sql.b 建库切库

mysqldump -uroot -p'wj123' –B wj |gzip>/opt/$(date+%F).sql.gz 备份压缩

mysqldump -uroot -p'wj123' -- default-character-set=utf8–B wj |gzip>/opt/$(date+%F).sql.gz字符集备份

mysql –uroot –p’wj123’ wj </opt/2016-10-30.sql

提示:备份时-B的作用是增加创建数据库和切换数据库的命令

4.6备份多个库

Mysqldump –B databasename1 databasename2

mysqldump -uroot -p'wj123' –events  –B wj test |gzip>/opt/$(date+%F).sql.gz

 

分库备份,多个数据库分开备份(大库小库)先把数据库读出来

mysqldump -uroot -p'wj123' –events  –B wj |gzip>/opt/$(date+%F).sql.gz

mysqldump -uroot -p'wj123' –events  –B test |gzip>/opt/$(date+%F).sql.gz

然后做个脚本实现

4.7备份单个表

mysqldump -uroot -p'wj123' databasename tablename2|gzip>/opt/$(date+%F).sql.gz

4.8备份多个表

mysqldump -uroot -p'wj123' databasename tablename1 tablename2 …|gzip>/opt/$(date+%F).sql.gz

4.9分表备份(大表小表)一个表一个表备份

先把数据库读出来,再把表读出来,再切分

mysqldump -uroot -p'wj123' databasename tablename1|gzip>/opt/$(date+%F).sql.gz

mysqldump -uroot -p'wj123' databasename tablename2|gzip>/opt/$(date+%F).sql.gz

4.10备份数据库的结构(参数-d)生产偶尔用

mysqldump -uroot -p'wj123'  -d  databasename |gzip>/opt/$(date+%F).sql.gz

备份数据库一个表结构,没有行信息

mysqldump -uroot -p'wj123'  -d  databasename tablename|gzip>/opt/$(date+%F).sql.gz

4.11只备份数据(-t)

4.12备份时去掉注释(--compact)是有注释的1/3测试时用的多

mysqldump -uroot -p'wj123'  --compact  databasename |gzip>/opt/$(date+%F).sql.gz

egrep –v “^\-|\*|^$” /mnt/tmp.sql查看mysql备份的文件

4.13备份数据库时锁表()

-x –lock-all-tables锁表

-l –lock-tables  lock all tables for read.给所有的表加读锁

-q –quick 表如果比较大时备份时候快

--single-transaction  innodb引擎使用这个

5.数据库恢复

1.source

只要是sql语句就可以用source执行,source test.sql这个文件是系统路径,如果不加全路径,默认是登录mysql前的系统路径

2.mysql命令恢复(标准)

如果在导出时指定-B参数,恢复时无需指定库恢复,因为-B参数带了use database,而恢复时指定库就类似于use database

mysql –uroot –p’wj123’ wj </opt/2016-10-30.sql

mysql>system mysql –uroot –p’wj123’ wj<2016-10-30.sql

3.分库分表备份的数据恢复

通过脚本读指定的库和表,调用mysql命令恢复.

6.mysql带-e参数实现非交互式对话(命令行方便过滤)

mysql –help |less 查看mysql有哪些参数

6.1恢复mysql数据

[wj@hcf ~]$ mysql -uroot -p'wj123' -e 'select * from wj.test';

[wj@hcf ~]$ mysql -uroot -p'wj123' -e 'use wj;select * from test'; 封号隔开不同的命令

[wj@hcf ~]$ mysql -uroot -p'wj123' -e 'use wj;select * from test'|grep dkj  可以加过滤(命令行)

工作中有人访问数据库,执行此命令可以看到正在的sql语句,需快速看,如果2秒钟看还有这条sql语句需要优化这条sql语句.

[wj@hcf ~]$ mysql -uroot -p'wj123' -e "show full processlist";查看mysql当前线程数

[wj@hcf ~]$ mysql -uroot -p'wj123' -e "show full processlist;" >aaa.log 如果滚屏定向到log中再用less查看这个log

[wj@hcf ~]$ mysql -uroot -p'wj123' -e "show variables;"|head -5

[wj@hcf ~]$ mysql -uroot -p'wj123' -e "show variables like 'wait_timeout' "避免引号冲突

查看分析mysql的健康状态

[wj@hcf ~]$ mysql -uroot -p'wj123' -e "show global status;"|egrep "Com_select|Com_insert|Com_update|Com_delete"

不重启mysql,修改mysql参数,但要求重启后还能生效

[wj@hcf ~]$ mysql -uroot -p'wj123' -e "show variables;"|grep key_buffer_size

grep key_buffer /etc/my.cnf  对应mysqld中的key_buffer_size的值,另一个对应myisamchk检查mysql

myisam索引的缓冲区,不用重启修改这个参数

[wj@hcf ~]$ mysql -uroot -p'wj123' -e "set global key_buffer_size=32M"

ERROR 1232 (42000) at line 1: Incorrect argument type to variable 'key_buffer_size'

[wj@hcf ~]$ mysql -uroot -p'wj123' -e "set global key_buffer_size=32777218"

vi /etc/my.cnf中修改mysqld中的key_buffer_size保证重启后也生效

7.mysql生产环境

7.1mysql数据库批量插入数据shell脚本实现

http://oldboy.blog.51cto.com/2561410/597511

7.2不登录数据库执行mysql命令小结:

http://oldboy.blog.51cto.com/2561410/632608

1.通过echo实现(比较常见)

[wj@hcf ~]$ echo "show variables;"| mysql -uroot -p'wj123'|grep key_buffer_size

2.通过cat实现(此法用的不多)

提示此法适合多行字符串比较多的时候

3.通过mysql –e参数实现

7.3mysql线程中,”大海捞针”

平时登录数据库show processlist;发现结果经常超长,找自己要看的比较困难,而且,sql显示不全.如果直接执行show full processlist那更是瞬间滚了N屏了.找到有问题的sql语句非常困难.

[wj@hcf ~]$ mysql -uroot -p'wj123' -e "show full processlist;"|grep –v Sleep >aaa.log

过滤当前执行的sql语句完整内容,后面还可以加iconv等对中文转码.