标签：mysql

MySQL RECOVER_YOUR_DATA数据库勒索

今天发现之前搭建的游戏无法正常运行，登入服务器看到之前的数据库全都不见了，留下一个RECOVER_YOUR_DATA数据库，里面有一张RECOVER_YOUR_DATA表，表内容为:
All your data is backed up. You must pay 0.018 BTC to 164hyKPAoC5ecqkJ2ygeGoGFRcauWRLujV In 48 hours,
your data will be publicly disclosed and deleted. (more information: go to http://iplis.ru/data2)
After payment send mail to us: rambler+280cs@onionmail.org and
we will provide a link for you to download your data. Your DBCODE is: 280CS

晕！数据库被勒索了，还好这些数据库只有测试数据，要是生成环境就尴尬了。可能是之前开放所有端口造成的，数据库安全还是得重视！！！

MySQL减少内存占用

之前买了一台512MB内存的VPS用于搭建wordpress，使用时经常报数据库连接错误。使用nmon或top命令查看占用内存最多的是mysql和apache2服务，apache2通过这篇文章解决。mysql的performance_schema 主要用于收集数据库服务器性能参数，适当调小可以减少内存占用，甚至可以修改配置将其关闭。

[mysqld]
performance_schema=OFF

wordpress 数据库postgresql迁移至mysql

环境: Ubuntu 14.04, postgresql 9.3, mysql 5.5, wordpress 4.7.1

之前用wordpress 3.4.2和postgresql, pg4wp搭建一个个人博客网站，后来想升级时发现pg4wp从1.3.1之后再也没升级过，这个比较坑了，后续的wordpress都没法再升级上去。没办法，硬着头皮把postgresql替换成mysql。

#postgresql operations，从postgresql上导出数据
\copy wp_postmeta to ‘/home/jerry/postmeta.txt’;
\copy wp_posts ‘/home/jerry/posts.txt’;
\copy wp_links ‘/home/jerry/links.txt’;
\copy wp_terms ‘/home/jerry/terms.txt’;
\copy wp_term_relationships ‘/home/jerry/terms_relationships.txt’;
\copy wp_term_taxonomy ‘/home/jerry/terms_taxonomy.txt’;

 

#mysql operations，新建数据库wordpress， 并导入之前的数据
CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

delete from wp_postmeta;
delete from wp_posts;
delete from wp_links;
delete from wp_terms;
delete from wp_term_relationships;
delete from wp_term_taxonomy;

load data infile ‘/home/jerry/postmeta.txt’ into table wp_postmeta;
load data infile ‘/home/jerry/posts.txt’ into table wp_posts;
load data infile ‘/home/jerry/links.txt’ into table wp_links;
load data infile ‘/home/jerry/terms.txt’ into table wp_terms;
load data infile ‘/home/jerry/terms_relationships.txt’ into table wp_term_relationships;
load data infile ‘/home/jerry/terms_taxonomy.txt’ into table wp_term_taxonomy;

mysql 5.5 load data问题

环境: Ubuntu 14.04,  mysql 5.5

使用mysql导入数据

load data infile ‘/home/jerry/aa.txt’ into table t1;

发现有两个问题：

  1. mysql环境变量secure_file_priv， 只有这个目录才能存放数据并导入到mysql内。查看路径  show variables like ‘%secure_file_priv%’; （但仍然无效）。设置其它路径， 在/etc/mysql/my.conf上[mysqld]下面添加一行了secure-file-priv = “”，重启mysql，仍无效。
  2. /etc/apparmor.d/usr.sbin.mysqld， 这个mysql是用来设置文件的读写权限，在这个文件的底部添加如下两行。

/home/jerry/ r,
/home/jerry/* rw,

sudo /etc/init.d/apparmor reload 重新加载下

 

目前看来mysql的文件的权限设置这块非常严格。