Wu Bigo

Today I Learned

A collection of concise write-ups about what i learned today and it's build from http://bigo.github.io .These are things that don't really warrant a full blog post


dbms note

showing current configuration

mysql>SHOW VARIABLES;

mysqldump -u root -h 192.168.76.62 -pgld --all-databases > dump.sql
mysqldump -u root -h db -pgld --all-databases > dump.sql

then import data in mysql shell

mysql>source dump.sql

update the database if directly upgrade from 5.0 to 5.5

mysql_upgrade -u root -p

service mysql restart

A typical mysqldump command to move data from an external database to an Amazon RDS DB instance looks similar to the following:

mysqldump -u \ --databases \ --single-transaction \ --compress \ --order-by-primary \ -p | mysql -u \ --port= \ --host= \ -p

–single-transaction – Use to ensure that all of the data loaded from the local database is consistent with a single point in time. If there are other processes changing the data while mysqldump is reading it, using this option helps maintain data integrity. –compress – Use to reduce network bandwidth consumption by compressing the data from the local database before sending it to Amazon RDS. –order-by-primary – Use to reduce load time by sorting each table’s data by its primary key.

You must create any stored procedures, triggers, functions, or events manually in your Amazon RDS database. If you have any of these objects in the database that you are copying, then exclude them when you run mysqldump by including the following arguments with your mysqldump command: –routines=0 –triggers=0 –events=0.

how to remove mysql completely

sudo apt-get remove –purge mysql-server mysql-client mysql-common sudo apt-get autoremove sudo apt-get autoclean then try to install it again.

sudo apt-get install mysql-server if you installing with dpkg command and if it show any dependency on other package then run command :

sudo apt-get install -f

What is disabled by default is remote root access. If you want to enable that, run this SQL command locally:

GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘' WITH GRANT OPTION; FLUSH PRIVILEGES; And then find the following line and comment it out in your my.cnf file, which usually lives on /etc/mysql/my.cnf on Unix/OSX systems. If it's a Windows system, you can find it in the MySQL installation directory, usually something like C:\Program Files\MySQL\MySQL Server 5.5\ and the filename will be my.ini.

Change line

bind-address = 127.0.0.1 to

#bind-address = 127.0.0.1 And restart the MySQL server for the changes to take effect.

sudo apt-get install mysql-server

update t_supplier_subproject set attachInfo=’‘;commit;

update t_supplier set email=’gin_369@163.Cnn’ where supplierID=7;

select * from t_supplier where userid=’6156354693465407499’ and email=’gin_369@163.COM’;

ALTER TABLE t_quoted_adopt4tbq MODIFY adoptRemark VARCHAR(255);

查询分包商数量 SELECT count(*) FROM etender.t_supplier;

ALTER TABLE etender.t_supplier CHANGE COLUMN email email VARCHAR(40) NULL DEFAULT ‘’ COMMENT ‘供应商邮箱’;

新增列 询价类型

alter table t_project_query add column inquiryType varchar(255) DEFAULT ‘2’ COMMENT ‘询价类型’;

alter table t_quoted_billitem4tbq add column itemType varchar(255) DEFAULT ‘1’ COMMENT ‘清单类型’;

SELECT COUNT(*),t2.email AS ‘总包邮箱’, t1.userID FROM etender.t_user t2 LEFT JOIN etender.t_supplier t1 ON t1.userID = t2.userID WHERE t1.logicDelete !=1 GROUP BY t2.userID ;

Making a Copy of a Database

$mysqldump -u root -pg1d etender > dump.sql
$mysqladmin -u root -pg1d create hongq
$mysql -u root -pg1d hongq < dump.sql

export to csv

SELECT b.email,a.name supplierName,a.email supplierEmail,a.telephone,a.trade,a.level,a.address,a.contacts FROM t_supplier a LEFT JOIN t_user b ON a.userID = b.userid and a.logicDelete !=1  ORDER BY a.userID limit 1000,1000 INTO OUTFILE '/var/lib/mysql-files/subcon1000.csv' FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Creating SSH Tunnel From Linux for mysql

$ ssh -L 3306:rdb:3306 ubuntu@ec2

use ip instead of hostname to avoid channel X on ubuntu 16 “channel X: open failed: administratively prohibited”