blast2go本地化 mysql_study
mysql
yum -y install mysql
mysqladmin -uroot -passwd "oebiotech"
mysql -uroot -poebiotech 登录mysql
1．修改MySQL的登录设置：
# vi /etc/my.cnf
在[mysqld]的段中加上一句：skip-grant-tables
例如：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
保存并且退出vi。
3．重新启动mysqld
# /etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
4．登录并修改MySQL的root密码
#mysql
mysql> USE mysql ;
mysql> UPDATE user SET Password = password('123456') WHERE User='root';
mysql> flush privileges ;
mysql> quit
5．将MySQL的登录设置修改回来
# vi /etc/my.cnf
将刚才在[mysqld]的段中加上的skip-grant-tables删除
保存并且退出vi。
6.重新启动mysqld
# /etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
1.java 环境
java -version
配置系统默认java sudo update-alternatives --install /usr/bin/java java /usr/java/latest/jre/bin/java
选择java sudo update-alternatives --config java
java -version
2.修改数据库的datadir
mysql默认的datadir=var/lib/mysql,mysql大量的数据（>50G）会产生在var/lib/mysql,mysql这个文件夹里面，
基于此，我想把他放在一个稍微大点的盘中去
http://blog.sina.com.cn/s/blog_670445240101iv3w.html
3.下载数据
4 执行b2gdb.sql用以创建b2gdb和一些Table：
mysql -h localhost -uroot -proot < /sam/local_b2g_db/b2gdb.sql（b2gdb.sql在哪，前面的路径指哪）
会报字符串的问题
ERROR 1101 (42000) at line 9 in file: '/software/blast2go/local_b2g_db/b2gdb.sql':
BLOB/TEXT column 'description' can't have a default value 这个字段不允许有默认值 这个问题折腾了好几天尴尬！
解决办法 source /software/blast2go/local_b2g_db/b2gdb.sql
mysql -uroot -poebiotech -e "show databases;use b2gdb;show tables;"
发现gene——info这个表没有创建成功
vi /software/blast2go/local_b2g_db/b2gdb.sql 把gene_info中的 BLOB/TEXT这一行 去掉 default ''
CREATE TABLE `gene_info` (
`tax_id` int(11) NOT NULL default '0',
`GeneID` int(11) NOT NULL default '0',
`Symbol` varchar(16) NOT NULL default '',
`LocusTag` varchar(16) NOT NULL default '',
`Synonyms` varchar(16) NOT NULL default '',
`dbXrefs` varchar(16) NOT NULL default '',
`chromosome` varchar(16) NOT NULL default '',
`map_location` varchar(16) NOT NULL default '',
`description` text NOT NULL ,
`type_of_gene` varchar(16) NOT NULL default '',
`Symbol_from_nomenclature_authority` varchar(16) NOT NULL default '',
`Full_name_from_nomenclature_authority` varchar(16) NOT NULL default '',
`Nomenclature_status` varchar(16) NOT NULL default '',
`Other_designations` varchar(16) NOT NULL default '',
`Modification_date` varchar(16) NOT NULL default '',
KEY `index_geneid` (`GeneID`),
KEY `index_symbol` (`Symbol`),
KEY `index_tax_id` (`tax_id`)
) ENGINE=MyISAM;
进入mysql mysql -uroot -poebiotech b2gdb
CREATE TABLE `gene_info` (
`tax_id` int(11) NOT NULL default '0',
`GeneID` int(11) NOT NULL default '0',
`Symbol` varchar(16) NOT NULL default '',
`LocusTag` varchar(16) NOT NULL default '',
`Synonyms` varchar(16) NOT NULL default '',
`dbXrefs` varchar(16) NOT NULL default '',
`chromosome` varchar(16) NOT NULL default '',
`map_location` varchar(16) NOT NULL default '',
`description` text NOT NULL ,
`type_of_gene` varchar(16) NOT NULL default '',
`Symbol_from_nomenclature_authority` varchar(16) NOT NULL default '',
`Full_name_from_nomenclature_authority` varchar(16) NOT NULL default '',
`Nomenclature_status` varchar(16) NOT NULL default '',
`Other_designations` varchar(16) NOT NULL default '',
`Modification_date` varchar(16) NOT NULL default '',
KEY `index_geneid` (`GeneID`),
KEY `index_symbol` (`Symbol`),
KEY `index_tax_id` (`tax_id`)
) ENGINE=MyISAM;
创建gene_info成功
5 创建数据库用户 blast2go
mysql -h localhost -uroot -proot -e "GRANT ALL ON b2gdb.* TO 'blast2go'@'localhost' IDENTIFIED BY 'blast4it';"
mysql -h localhost -uroot -proot -e "FLUSH PRIVILEGES;"
#GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'blast24it';
#FLUSH PRIVILEGES;
#INSERT INTO mysql.user(Host,User,Password) values("localhost","blast2go",password("blast4it"));
FLUSH PRIVILEGES;
创建只读用户：
CREATE USER pasa IDENTIFIED BY 'test';
GRANT SELECT ON *.* TO 'pasa'@'localhost' IDENTIFIED BY '123456';
创建所有权限用户：
GRANT ALL ON *.* TO 'chenyong'@'localhost' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
6 导入gene_info和gene2accession表 大概会花8-9个小时
mysql -h localhost -uroot -proot b2gdb -e "LOAD DATA LOCAL INFILE '/software/blast2go/gene_info' INTO TABLE gene_info FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';"
mysql -h localhost -uroot -proot b2gdb -e "LOAD DATA LOCAL INFILE '/software/blast2go/gene2accession' INTO TABLE gene2accession FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';"
7.导入 ImportIdMapping
java -cp .:mysql-connector-java-5.0.8-bin.jar: ImportIdMapping ”/software/blast2go/local_b2g_db/idmapping.tb“ localhost b2gdb blast2go blast4it
8 导入idmapping.tb表格
cd /software/blast2go/b2g4pipe/
java -cp blast2go.jar:ext/mysql-connector-java-3.0.11-stable-bin.jar es.blast2go.prog.util.ImportPIR /software/blast2go/local_b2g_db/idmapping.tb localhost b2gdb root oebiotech TRUE
可以看到输出则导入完成
Starting to import /sam/idmapping.tb to b2gdb@localhost with user root
Open database connection to database b2gdb on localhost as root with password starts with sam
Open database connection to database b2gdb on localhost as root with password starts with sam
Imported 40344363 gi-uniprot mappings to the DB.
Imported 40344363 gi-uniprot mappings to the DB.
Import of gi-uniprot mapping finished
9运行官方的例子b2g4pipe
下载 https://blast2go.com/data/blast2go/b2g4pipe_v2.5.zip
配置b2gPipe.properties文件中b2gdb信息：(打开那个文件夹，进行如下修改)
// GO and B2G Data Access Basic
Dbacces.dbname=b2gdb
Dbacces.dbhost=localhost:3306
Dbacces.dbuser=blast2go
Dbacces.dbpasswd=blast4it
cd /software/blast2go/b2g4pipe/
sh ./runPipeExample.sh
10 assocdb-data数据的更新：
下载解压新的assocdb-data文件后，
mysql -s -h localhost -uroot -poebiotech b2gdb < go_201307-assocdb-data
不用担心与旧的数据冲突，它们会被自动删除然后更新。
10.2 gene2accession、gene_info、idmapping.tb的更新：
由于它们是以导入数据文件的方式导入数据库，需要登陆到数据库中，手动清空对应的原来数据表中的数据：
$ mysql -u root -p
Enter password:
mysql> use b2gdb;
Database changed
mysql> truncate table gene2accession;
Query OK, 0 rows affected (16.67 sec)
mysql> truncate table gene_info;
Query OK, 0 rows affected (2 min 2.49 sec)
mysql> truncate table gi2uniprot;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
再开始导入数据
mysql -h localhost -uroot -proot b2gdb -e "LOAD DATA LOCAL INFILE '/software/blast2go/gene_info' INTO TABLE gene_info FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';"
mysql -h localhost -uroot -proot b2gdb -e "LOAD DATA LOCAL INFILE '/software/blast2go/gene2accession' INTO TABLE gene2accession FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';"
11删除数据库
mysql> DROP DATABASE b2gdb; 删除我安装的数据库
12 让其他主机访问我的b2gdb数据库
mysql -u root -poebiotech -e "grant all privileges on *.* to root@'%' identified by ' oebiotech';"
mysql -u root -poebiotech -e "grant all privileges on *.* to root@'localhost' identified by ' oebiotech';"
FLUSH PRIVILEGES;
其中 第一个*表示数据库名；第二个*表示该数据库的表名；如果像上面那样 *.*的话表示所有到数据库下到所有表都允许访问；
‘%’：表示允许访问到mysql的ip地址；当然你也可以配置为具体到ip名称；%表示所有ip均可以访问；
后面到‘oebiotech’为root’为root 用户的password；
导入 mysql -s -h localhost -uroot -poebiotech b2gdb < go_monthly-assocdb-data
查看当前的进程情况
mysql> show processlist ;
如果发现info列提示有lock的信息，找到id,kill 掉。
也可以按照提示信息，链接的时候加上-A :
遇到的问题及解决
1.导入gene_info表的时候报错BLOB/TEXT column 'description' can't have a default value
vi /software/blast2go/local_b2g_db/b2gdb.sql 把gene_info中的 BLOB/TEXT这一行 去掉 default '' 我们去掉默认值为空就可以了
2.数据库共享的问题
只要允许其他节点访问数据库就可以了
mysql -u root -poebiotech -e "grant all privileges on *.* to root@'%' identified by 'oebiotech';"
mysql -u root -poebiotech -e "grant all privileges on *.* to blast2go@'%' identified by ' blast24it';"
FLUSH PRIVILEGES;
3.用户访问的问题
SELECT HOST, USER FROM `mysql`.`user`; 查看user表 看您的用户和主机名是否在user表中。
DEFAULT CHARSET=utf8;
USE b2gdb;
drop table if exists gene_info;
CREATE TABLE gene_info (
`tax_id` int(11) NOT NULL default '0',
`GeneID` int(11) NOT NULL default '0',
`Symbol` varchar(16) NOT NULL default '',
`LocusTag` varchar(16) NOT NULL default '',
`Synonyms` varchar(16) NOT NULL default '',
`dbXrefs` varchar(16) NOT NULL default '',
`chromosome` varchar(16) NOT NULL default '',
`map_location` varchar(16) NOT NULL default '',
`description` text NOT NULL ,
`type_of_gene` varchar(16) NOT NULL default '',
`Symbol_from_nomenclature_authority` varchar(16) NOT NULL default '',
`Full_name_from_nomenclature_authority` varchar(16) NOT NULL default '',
`Nomenclature_status` varchar(16) NOT NULL default '',
`Other_designations` varchar(16) NOT NULL default '',
`Modification_date` varchar(16) NOT NULL default '',
KEY `index_geneid` (`GeneID`),
KEY `index_symbol` (`Symbol`),
KEY `index_tax_id` (`tax_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; #存储引擎和字符格式
创建gene_info表
CREATE TABLE gene_info (
tax_id int(11) NOT NULL default '',
GeneID int(11) NOT NULL default '',
Symbol varchar(16) NOT NULL default '',
LocusTag varchar(16) NOT NULL default '',
Synonyms varchar(16) NOT NULL default '',
dbXrefs varchar(16) NOT NULL default '',
chromosome varchar(16) NOT NULL default '',
map_location varchar(16) NOT NULL default '',
description text NOT NULL default '',
type_of_gene varchar(16) NOT NULL default '',
Symbol_from_nomenclature_authority varchar(16) NOT NULL default '',
Full_name_from_nomenclature_authority varchar(16) NOT NULL default '',
Nomenclature_status varchar(16) NOT NULL default '',
Other_designations varchar(16) NOT NULL default '',
Modification_date varchar(16) NOT NULL default '',
KEY index_geneid (GeneID),
KEY index_symbol (Symbol),
KEY index_tax_id (tax_id)
) ENGINE=MyISAM;
ALTER TABLE gene_info (
tax_id int(11) NOT NULL default '',
GeneID int(11) NOT NULL default '',
Symbol varchar(16) NOT NULL default '',
LocusTag varchar(16) NOT NULL default '',
Synonyms varchar(16) NOT NULL default '',
dbXrefs varchar(16) NOT NULL default '',
chromosome varchar(16) NOT NULL default '',
map_location varchar(16) NOT NULL default '',
description text NOT NULL default '',
type_of_gene varchar(16) NOT NULL default '',
Symbol_from_nomenclature_authority varchar(16) NOT NULL default '',
Full_name_from_nomenclature_authority varchar(16) NOT NULL default '',
Nomenclature_status varchar(16) NOT NULL default '',
Other_designations varchar(16) NOT NULL default '',
Modification_date varchar(16) NOT NULL default '',
KEY index_geneid (GeneID),
KEY index_symbol (Symbol),
KEY index_tax_id (tax_id),
KEY index_tax_id (tax_id)
) ENGINE=MyISAM;
SELECT HOST, USER FROM `mysql`.`user`; 查看user表
允许blast2go从任何节点访问b2gdb数据库
mysql -h localhost -uroot -poebiotech -e "GRANT ALL ON b2gdb.* TO 'blast2go'@'%' IDENTIFIED BY 'blast4it';"
数据库的备份和还原
备份：mysqldump -uroot -poebiotech 数据库名称（b2gdb） > b2gdb.sql
还原：mysql -uroot -poebiotech -e “use b2gdb；source b2gdb.sql;”
还原： mysql -uroot -poebiotech b2gdb < b2gdb.sql
#!/bin/sh
#author(orange)
# Database info
DB_USER="root"
DB_PASS="oebiotech"
DB_HOST="192.168.10.229"
DB_NAME="b2gdb"
# Others vars
BIN_DIR="/usr/bin" #the mysql bin path
BCK_DIR="/data/backup" #the backup file directory
DATE=`/bin/date'+%Y%m%d-%H%M'`
# TODO
#/usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher >/mnt/mysqlBackup/db_`date +%F`.sql
$BIN_DIR/mysqldump--opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME > $BCK_DIR/offical_$DATE.sql
#$BIN_DIR/mysqldump--opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME ｜ gzip > $BCK_DIR/db_$DATE.sql.gz
# 还原数据库
# 把 *.sql.gz 使用gunzip或 本地的解压软件 解压为 *.sql 文件
# 用mysql-front导入前一天的 *.sql 文件即可恢复数据
#任务计划
#crontab -e
#00 05 * * * /bin/sh /shell/mysql_backup.sh
#每天早上5:00am执行
#crontab -l 查看任务是否创建成
mysql的权限管理
1.创建，修改，删除mysql数据表结构权限
grant create|alter|drop on testdb.* to user@'192.168.10.%';
grant all on testdb.* to user@'192.168.10.%'; 网段
grant all on testdb.* to user@'%'; 所有的ip（主机）
2.grant 操作外键,临时表，mysql索引，视图 查看视图源代码的权限，mysql存储过程，函数权限。
操作外键,临时表，mysql索引，视图
grant references on testdb.* to user@'%';
grant create temporary tables on testdb.* to user@'%'
grant index on testdb.* to user@'%';
操作视图 查看视图源代码的权限。
grant create view on testdb.* to user@'%';
grant show view on testdb.* to user@'%';
操作mysql存储过程，函数权限。
grant create routine on testdb.* to user@'%';
grant alter routine on testdb.* to user@'%';
grant execute * on testdb.* to user@'%';
mysql 数据查询
or&and 或 并且
use database；SELECT HOST, USER FROM `mysql`.`user` where HOST= 'big01' or HOST= 'localhost';
use database；SELECT HOST, USER FROM `mysql`.`user` where HOST= 'big01' and HOST= 'localhost';
use database；SELECT HOST, USER FROM `mysql`.`user` where （HOST= 'big01' and user= 'root'）or （HOST= 'big01' and user= 'blast2go'）;
主键
外键
alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id)
//FK_ID是外键的名称
/*
CREATE TABLE `tb_active` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`),
CONSTRAINT `FK_ID` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
*/
mysql 如何修改、添加、删除表主键
在我们使用mysql的时候，有时会遇到须要更改或者删除mysql的主键，我们能够简单的使用alter table table_name drop primary key;来完成。以下我使用数据表table_test来作了样例。
1、首先创建一个数据表table_test：
create table table_test(
`id` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`name`)
)ENGINE=MyISAM DEFAULT CHARSET=gb2312;
2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。
先删除主键
alter table table_test drop primary key;
然后再增加主键
alter table table_test add primary key(id);
注:在增加主键之前,必须先把反复的id删除掉。
ALTER TABLE gene2accession CHANGE `protein_accession` `protein_accession.version`varchar(16) NOT NULL default ''
ALTER TABLE gene2accession CHANGE `protein_accession` 被替换的 `protein_accession.version`varchar 替换后的(16) NOT NULL default '';
搭建主从数据库方法
主机 操作系统 ip地址 主要软件
master centos6.5 192.168.10.1 cmake-2.8.6.tar.gz/mysql-5.2.22.tar.gz
slave1 centos6.5 192.168.10.2 cmake-2.8.6.tar.gz/mysql-5.2.22.tar.gz
slave2 centos6.5 192.168.10.3 cmake-2.8.6.tar.gz/mysql-5.2.22.tar.gz
web centos6.5 192.168.10.4 cmake-2.8.6.tar.gz/mysql-5.2.22.tar.gz
客户端 centos6.5 192.168.10.5
在主从复制的前提下实现读写分离
搭建主从复制
1.时间同步
master yum -y install ntp(服务的端口号是123)
vi /etc/ntp.conf
server 127.0.0.1
ntpserver
service ntpd start
iptables -I INPUT -p udp --dport 123 -j ACCEPT
service iptables save
slave yum -y install ntpdate
/usr/sbin/ntpdate ntpserverip
2.配置mysql-master
yum -y install mysql* or tar -zxvf mysql* ; ./configure make make install
vi /etc/my.cnf
server-id =1 ##主mysql的标示，每个mysql都是唯一的，不能重复
log-bin = master-bin ##启用二进制数日志，这行文件中有写到，我们修改参数即可
log-slave-updates = true ## 支持连级复制，这行需要添加
service mysqld restart
给从服务器授权，在从服务器上创建一个用户（slaves，密码为123456，授权“replication slave 复制权限”）
grant replication slave on *.* to 'slave'@'192.168.1.%' identified by '123456';
查看服务器的状态： show master status;
3.配置slave-mysql服务器
vim /etc/my.cnf
server-id =2 ##主mysql的标示，每个mysql都是唯一的，不能重复
relay-log = relay-log-bin ##启用中继日志
relay-log-index = slave-relay-bin.index ## 定制中继日志的索引文件，这行需要添加
service mysqld restart
mysql -uroot -p
同步配置
change master to master_host='ip',master_user='slaves',master_passwd='passwd',master_log_file='master-bin.000001',master_log_pos=261
启动slave1的同步功能
mysql -uroot -p -e "start slave";
show slave status\G;
4.验证主从复制
在master上创建一个test.db ,看从数据库是否同步过来了
读写分离:
MySQL读写分离能提高系统性能的原因在于：
物理服务器增加，机器处理能力提升。拿硬件换性能。
主从只负责各自的读和写，极大程度缓解X锁和S锁争用。
slave可以配置myiasm引擎，提升查询性能以及节约系统开销。
master直接写是并发的，slave通过主库发送来的binlog恢复数据是异步。
slave可以单独设置一些参数来提升其读的性能。
增加冗余，提高可用性。
排错记录
1.服务无法启动，cat/etc/my.cnf 查看log文件
180128 18:46:28 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
180129 10:50:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180129 10:50:23 InnoDB: Initializing buffer pool, size = 8.0M
180129 10:50:23 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
180129 10:50:23 [ERROR] Plugin 'InnoDB' init function returned error.
180129 10:50:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180129 10:50:23 [ERROR] Unknown/unsupported table type: innodb
180129 10:50:23 [ERROR] Aborting
删除datadir=/var/lib/mysql/ib_logfile0 ib_logfile1
2.服务无法启动，初始化下数据库
180129 11:35:58 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
180129 11:35:58 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[root@node1 mysql]# mysql_install_db
Installing MySQL system tables...
ERROR: 1136 Column count doesn't match value count at row 1
180129 11:37:18 [ERROR] Aborting
180129 11:37:18 [Note] /usr/libexec/mysqld: Shutdown complete
Installation of system tables failed! Examine the logs in
/var/lib/mysql for more information.
You can try to start the mysqld daemon with:
shell> /usr/libexec/mysqld --skip-grant &
and use the command line tool /usr/bin/mysql
to connect to the mysql database and look at the grant tables:
shell> /usr/bin/mysql -u root mysql
mysql> show tables
