mysqldump , create table if not exists

//=== http://stackoverflow.com/questions/8603182/how-to-update-table-if-table-exists-with-mysql-dump

mysqldump -uadmin -p > dump.sql

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
`major` int(11) NOT NULL DEFAULT '0',
...
)


how to change it to
IF TABLE EXISTS, UPDATE IT ???




//=== http://stackoverflow.com/questions/2220469/create-table-if-not-exists-from-mysqldump

* first dump containing table definitions (--no-data --skip-add-drop-table)
* second dump with only data (--no-create-info --skip-add-drop-table)



$ sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' dump_schema.sql

RDS as replica slave, RDS as replica master

//=== http://it.3hd.me/2015/05/mysql-replication-from-remote-rds-to.html

on master side (remote RDS)

mysql> call mysql.rds_set_configuration('binlog retention hours', 24);"
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'repl_passwd';

[Q] is 'on *.*' must? why not only on the target database 'on db_to_be_replicated.*' ?
[Q] 'repl_user'@'%' shd be changed to 'repl_user'@'slave_public_ip' ?
[Q] 'repl_user'@'slave_public_ip' --> 'repl_user'@'slave_host_domain_name' ??
[Q] is REPLICATION CLIENT optional?




//=== http://it.3hd.me/2015/04/mysql-replication-set-up.html

on slave side (RDS as replica slave)

*** since RDS not allow stop slave/start slave/change master to
-->
mysql> CALL mysql.rds_stop_replication;

mysql> CALL mysql.rds_set_external_master ('master.masters.com', 3306,
'repl_user', 'password', 'myrds-bin.00003', 107, 0);

mysql> CALL mysql.rds_start_replication


*** Do not use mysql.rds_set_external_master to manage replication between two Amazon RDS DB instances.
*** Use it only when replicating with an instance of MySQL running external to RDS,
i.e.
one RDS instance as master,
the other one external instance as slave




//===

* logon to master RDS by repl_user to verify the granted privileges
$ mysql -h rds_master -u repl_user -p
mysql> show grants;

amazon ec2 instance migration

*** sop for amazon ec2 instance migration from one account(old) to another(new)

*** account number:
support -> support Center


0. [optional] scp data to digitalocean

1. create and share private AMI on the old account
[instance -> Actions -> create image ?, then register ?]

[
http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/creating-an-ami-ebs.html
http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/creating-an-ami-ebs.html

* create a snapshot first
more efficient to create snapshots of your volumes prior to creating your AMI. This way, only small, incremental snapshots need to be created when the AMI is created, and the process completes more quickly

* create image
choose Instances and select your instance. Choose Actions, Image, and Create Image.

* register ami
]

[
To create an AMI from a snapshot using the console :

* Open the Amazon EC2 console at https://console.aws.amazon.com/ec2/.

* In the navigation pane, under Elastic Block Store, choose Snapshots.

* Choose the snapshot and choose Actions, Create Image.

* In the Create Image from EBS Snapshot dialog box, complete the fields to create your AMI, then choose Create. If you're re-creating a parent instance, then choose the same options as the parent instance.

]


*** Both the AMI and the snapshot incur charges to your account until you delete them.





//=== SOP
1. create and share private AMI on the old account

2. find and copy the shared AMI on the new account

3. no need to restore ~/.ssh/authorized_keys [already recovered within the AMI]

4. elastic ip change and domain name update
allocate new elastic ip
assign elastic ip to the new instance
[on elastic ip dashboard -> actions -> associate address -> new instance id/tag]


5. restore security groups

6. release old elastic ip

7. release old ami/snapshot
[ in the old account, modify permission to remove the shared ami for the new account number;
deregister the ami
]
[ in the old account, modify permission for the snapshot;
delete the snapshot
delete the volume
]

8. delete key pairs
9. delete security groups

heroku how to set the current active app ?

[Q] heroku how to set the current active app ?
[Q] heroku how to select the target app?
[Q] heroku how to switch from one app to another ?


* if already in the local folder with git corresponding to the remote heroku app,
then the current folder is just the active app

* if not ,
heroku clone the app from remote heroku
$ heroku git:clone -a app_name
$ cd app_name




//=== https://devcenter.heroku.com/articles/git
local git , push to remote heroku server

heroku create command creates a new application on Heroku – along with a git remote




//=== pull or clone from heroku
To clone the source of an existing application from Heroku using Git,
$ heroku git:clone -a app_name

$ heroku git: clone -a app_name
! `git:` is not a heroku command.
! Perhaps you meant `auth` or `list`.
! See `heroku help` for a list of available commands.

--> cannot have space between "git:" and "clone"
--> $ heroku git:clone -a app_name



//===
https://devcenter.heroku.com/articles/using-the-cli
$ heroku login
$ heroku apps
$ heroku apps:info

to specify an explicit app name as follows:
$ heroku apps:info --app app_name



$ heroku apps --help

to delete an app
$ heroku apps:destroy --app app_name




//=== http://stackoverflow.com/questions/12169228/heroku-toolbelt-specify-app

$ heroku login

$ heroku logs
$ heroku logs -a app_name
$ hreoku logs --app app_name



//=== https://devcenter.heroku.com/articles/renaming-apps

Renaming app and Updating git remote alias

* if you are using the CLI to rename an app from inside the Git checkout directory, your remote will be updated automatically.
$ heroku apps:rename newname

* If you rename from the website or have other checkouts, such as those belonging to other developers, these will need to be updated manually.
$ heroku apps:rename newname --app oldname
$ git remote rm heroku
$ heroku git:remote -a newname



//=== https://devcenter.heroku.com/articles/cedar-14-migration
Create Cedar-14 app
$ heroku create --remote heroku14 --stack cedar-14 app14

$ git push heroku14 master


Upgrade the production app to Cedar-14
Once you have verified that your source code works correctly on the Cedar-14 staging app, you are ready to update the production app from Cedar to Cedar-14.

$ heroku stack:set cedar-14
$ git commit --allow-empty -m "Upgrading to Cedar-14"
$ git push heroku master

Tungsten Replication from Amazon RDS


//=== http://docs.continuent.com/tungsten-replicator-3.0/deployment-fromamazonrds.html

Roles: RDS instance, Tungsten Replicator, destination database instance(dest_db)

* no ssh access to RDS instance is provided yet?

* install Tungsten Replicator on a certain host
The replicator can be installed either within a standard (non-RDS) Amazon EC2 instance
or
on another host which can write to the dest_db

* read bin_log from RDS, transfer format and write to dest_db
- "Service Alpha" extracts the binary log information directly from RDS instance and loads into THL.
-- ("Service Alpha" is a utility running on Tungsten Replicator)
-- (THL: Transaction History Log )

- Tungsten Replicator then writes the information from the THL and writes the data to the target database.



//=== http://docs.continuent.com/tungsten-replicator-3.0/deployment-fromamazonrds-preparation.html

dest_db data format prerequisites : [take mysql for example]

* Table format should be updated to UTF8 by updating the MySQL configuration (/etc/mysql/my.cnf):
- character-set-server=utf8
- collation-server=utf8_general_ci

* To prevent timezone configuration storing zone adjusted values,
fix the timezone configuration to use UTC within the configuration file (my.cnf):
- default-time-zone='+00:00'


RDS instance config
http://docs.continuent.com/tungsten-replicator-3.0/deployment-fromamazonrds-rdsconfig.html

to change RDS config by RDS client
http://docs.continuent.com/tungsten-replicator-3.0/deployment-amazonrds-rdsconfig.html


//=== download and install Tungsten
http://docs.continuent.com/tungsten-replicator-3.0/deployment-fromamazonrds-installation.html

* Download the latest version of Tungsten Replicator.
https://code.google.com/p/tungsten-replicator/wiki/Downloads


Expand the release:
shell> tar zxf tungsten-replicator-3.0.1-64

Change to the staging directory:
shell> cd tungsten-replicator-3.0.1-64


Run tpm to install the replicator:
[shd create repli-user first]

shell> ./tools/tpm install alpha \
--install-directory=/opt/continuent \
--replication-user=tungsten \
--replication-password=secret \
--replication-port=3306 \
--direct-replication-port=3306 \
--privileged-master=false \
--skip-validation-check=MySQLDumpCheck \
--topology=direct \
--master=db1 \
--direct-datasource-host=xxxx.cnlhon44f2wq.eu-west-1.rds.amazonaws.com \
--direct-datasource-user=rds_user \
--direct-datasource-password=rds_password \
--start-and-report



//=== http://docs.continuent.com/continuent-tungsten-4.0/terminology-thl.html
Tungsten Replicator --> Continuent-Tungsten

THL: Transaction History Log

"... stores transactional data from different data servers in a universal format
that is then used to exchange and transfer the information between replicator instances.

Because the THL is stored and 'independently' managed from the data servers that it reads and writes,
the data can be moved, exchanged, and transmuted during processing.
..."""




.


mysql create user


mysql> CREATE USER 'repl'@'%.myslaves.com' IDENTIFIED BY 'slavepass';

//=== https://dev.mysql.com/doc/refman/5.1/en/create-user.html
mysql> CREATE USER 'jeff'@'localhost' IDENTIFIED BY 'mypass';

* to enable the user to connect with no password, remove "IDENTIFIED BY" clause:
mysql> CREATE USER 'jeff'@'localhost';

* to assign the hashed password directly, use "IDENTIFIED BY PASSWORD" clause:
mysql> CREATE USER 'jeff'@'localhost' IDENTIFIED BY PASSWORD '*90Exfydsajd12324959dfare5ere895989';




show granted privileges for a certain user in mysql


https://dev.mysql.com/doc/refman/5.0/en/show-grants.html

mysql> SHOW GRANTS FOR 'root'@'localhost';
mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR CURRENT_USER;
mysql> SHOW GRANTS FOR CURRENT_USER();

mysql> select user,host from mysql.user;
mysql> show grants for repli_user@3hd.me ;

GRANT REPLICATION SLAVE ON *.* TO 'repli_user'@'3hd.me' IDENTIFIED BY PASSWORD 'xvf1E7fda4dsf9xxxxx'


Tungsten Replicator for heterogeneous databases


http://docs.continuent.com/tungsten-replicator-3.0/deployment-hetero-operation.html

*** All heterogeneous replication deployments use row-based replication.

"""...
Heterogeneous replication works slightly differently compared to the native MySQL to MySQL replication.
... The SQL dialects are different, so that an SQL statement on MySQL is not the same as an SQL statement on Oracle, and differences in the dialects mean that either the statement would fail, or would perform an incorrect operation.

On targets that do not support SQL of any kind, such as MongoDB, replicating SQL statements would achieve nothing since they cannot be executed at all.

All heterogeneous replication deployments therefore use row-based replication.
This extracts only the raw row data, not the statement information.
... raw-data, it can be easily re-assembled or constructed into another format,
including statements in other SQL dialects,
native appliers for alternative formats, such as JSON or BSON, or external CSV formats ...

..."""

* Native applier:
to replicate to MongoDB, data is reformatted into BSON and then applied to MongoDB
using the native insert/update/delete API calls.






nodejs fork, spawn, exec


http://www.hacksparrow.com/difference-between-spawn-and-exec-of-node-js-child_process.html

Difference between spawn and exec of Node.js child_process
"""...
The most significant difference between child_process.spawn and child_process.exec is
in what they return -
spawn returns a stream and
exec returns a buffer.

... child_process.spawn is "asynchronously asynchronous",
meaning it starts sending back data ... as soon as the child process starts executing


...
child_process.exec returns the whole buffer output from the child process.
By default the buffer size is set at 200k.

If the child process returns anything more than that, your program will
crash with the error message "Error: maxBuffer exceeded".

... child_process.exec is "synchronously asynchronous", ...
it waits for the child process to end and tries to return all the buffered data at once



..."""



nodejs exec and execFile


https://nodejs.org/api/child_process.html#child_process_child_process_exec_command_options_callback

* child_process.exec(command[, options], callback)
* child_process.execFile(file[, args][, options][, callback])


child_process.execFile() is similar to child_process.exec() except it does not execute a subshell
but rather the specified file directly.
This makes it slightly leaner than child_process.exec.


* child_process.fork(modulePath[, args][, options])
This is a special case of the spawn() functionality for spawning Node processes.

In addition to having all the methods in a normal ChildProcess instance,
the returned object has a communication channel built-in.

These child Nodes are still whole new instances of V8.
Assume at least 30ms startup and 10mb memory for each new Node.
That is, cannot create many thousands of them.




mysql examples


//===
show index from table1;

ALTER DATABASE db1
DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_unicode_ci' ;

alter table phrase add column freq int unsigned;

show create table t1;


http://www.w3schools.com/sql/sql_alter.asp
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
alter table phrase modify column val char(8);

//=== mysql data type char(8)
varchar(500)
char(254)


https://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html

CREATE TABLE t
(
c1 VARCHAR(20) CHARACTER SET utf8,
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);



//=== http://www.w3schools.com/sql/sql_unique.asp
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

ALTER TABLE Persons
ADD UNIQUE (P_Id)


ALTER TABLE Persons
DROP INDEX uc_PersonID



//=== http://dev.mysql.com/doc/refman/5.1/en/create-table.html
CREATE TABLE new_tbl LIKE orig_tbl;
CREATE TABLE new_tbl SELECT * FROM orig_tbl;

CREATE TABLE IF NOT EXISTS t1 (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`val` varchar(200) NOT NULL,
PRIMARY KEY (`id`),
index `val` (`val`)
) DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'


//=== mysql datatype int
https://dev.mysql.com/doc/refman/5.0/en/integer-types.html

INTEGER (or INT) , SMALLINT, TINYINT, MEDIUMINT, BIGINT.

http://stackoverflow.com/questions/5562322/difference-between-int-and-int3-data-types-in-my-sql
Difference between “int” and “int(3)” data types in my sql

INT(10) specifies an INT with a display width of 10 digits
and doesn't affect the size of the integer.






//====
http://dba.stackexchange.com/questions/6570/setting-default-charset-collation-for-mysql-database

"""...
CREATE DATABASE IF NOT EXISTS foo
DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'


ALTER DATABASE foo
DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'
..."""


http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

utf8_general_ci , utf8_unicode_ci ,


"""...
You should never, ever use utf8_general_ci: it simply doesn’t work. It’s a throwback to the bad old days of ASCII stooopeeedity from fifty years ago

...
In short: utf8_unicode_ci uses the Unicode Collation Algorithm as defined in the Unicode standards,
whereas utf8_general_ci is a more simple sort order which results in "less accurate" sorting results.


..."""


vim indent for python, ftplugin


[ex1.py]
#!/usr/bin/env python

"""
Python docString :description about ex1.py
"""

# vim: tabstop=8 expandtab shiftwidth=4 softtabstop=4

# ======= write your code below =========

....

# =======================================




//=== https://github.com/tarmack/vim-python-ftplugin
https://github.com/tarmack/vim-python-ftplugin.git

need pyflakes for python syntax check
$ sudo apt-get install pyflakes



//=== https://wiki.python.org/moin/Vim

"""...
A useful addition to Python source files is this comment:
# vim: tabstop=8 expandtab shiftwidth=4 softtabstop=4

This may need the modeline option enabled in your ~/.vimrc file:
set modeline
(In Debian and Ubuntu, for example, the modeline option has been disabled for security reasons.)

The above # vim: ... text, when embedded in a source file, tells Vim that when the file is loaded, tabs are always expanded to spaces and that the width of each tab is four characters. Type the following in command mode to achieve the same effect:

:set tabstop=8 expandtab shiftwidth=4 softtabstop=4

Or:
:set ts=8 et sw=4 sts=4


... An alternative method is adding...

set tabstop=8
set expandtab
set softtabstop=4
set shiftwidth=4
filetype indent on
...to your ~/.vimrc file.
[but this will effect other types of files such as .js, .php, .c ...]


..."""

----->

"""...
add the following to ~/.vim/ftplugin/python.vim. Create the directory and/or file if it does not already exist.

set tabstop=8
set expandtab
set shiftwidth=4
set softtabstop=4

..."""


http://vim.wikia.com/wiki/Keep_your_vimrc_file_clean

"""...
move all the language-specific stuff from your .vimrc file into a file named
.vim/ftplugin/language.vim
(or $HOME/vimfiles/ftplugin/language.vim on Windows).


" File ~/.vimrc
" ($HOME/_vimrc on Windows)
" Global settings for all files (but may be overridden in ftplugin).
set tabstop=2
set shiftwidth=2
set noexpandtab



" File ~/.vim/ftplugin/python.vim
" ($HOME/vimfiles/ftplugin/python.vim on Windows)
" Python specific settings.
setlocal tabstop=4
setlocal shiftwidth=4
setlocal expandtab
setlocal autoindent
setlocal smarttab
setlocal formatoptions=croql


...
if there is a filetype plugin distributed with Vim that you want to completely disable, make your own (perhaps empty) settings file and adding this line:

let b:did_ftplugin = 1


..."""




//=== ftplugin !== ftp plugin

ftplugin: file type plugin
--> http://vim.wikia.com/wiki/File_type_plugins

"""...
A file type plugin (ftplugin) is a script that is run automatically
when Vim detects the type of file when the file is created or opened.

The type can be detected from the file name (for example, file sample.c has file type c),
or from the file contents.


...
to disable all ftplugins, or disable a particular default ftplugin,
see :help :filetype and :help ftplugin-overrule.




..."""



ftp plugin for vim
--> http://www.marksanborn.net/software/modify-remote-files-with-vims-built-in-ftp-plugin/

$ vim ftp://username@yourdomain.com//the/path/to/yourfile.php








vim plugin management, pathogen


$ git clone https://github.com/tpope/vim-pathogen.git
$ cd vim-pathogen/autoload
$ mkdir -p ~/.vim/autoload ~/.vim/bundle
$ cp pathogen.vim ~/.vim/autoload

Add the following to your ~/.vimrc
-- execute pathogen#infect()
-- syntax on
-- filetype plugin indent on


*** install plugins you want to a subdirectory under ~/.vim/bundle,
and they will be added to the 'runtimepath'

[ex] js syntax plugin for vim
$ git clone https://github.com/pangloss/vim-javascript.git ~/.vim/bundle/vim-javascript

or step by step
$ git clone https://github.com/pangloss/vim-javascript.git
$ cp -r vim-javascript ~/.vim/bundle/


*** vim7.4 problem with js regex
If you are stuck on an older version of Vim 7.4 with no way to update,
then simply perform the following commands to fix your current buffer:

:set regexpengine=1
:syntax enable


[ref]
https://github.com/tpope/vim-pathogen
https://github.com/pangloss/vim-javascript




mysql connections parallel insert



//=== node-mysql
https://github.com/felixge/node-mysql/
*** need multiple connections to execute queries in parallel.


//=== python, mysql
http://themattreid.com/wordpress/2010/08/30/easy-python-threading-mysql-connections/

"""...
You will run into performance problems with the Python threading module because of the GIL.
... Eventlet for non-blocking asynchronous calls (eventlet.GreenPool and similar) or
using the multiprocessing Python 2.6+ module.


..."""




"...
... single threaded database operations are simply too slow. In these cases it’s a matter of course that you’ll use multi-threading or forking to spawn secondary processes to handle the database actions.

...



import threading
import sys
import MySQLdb
import pdb

class threader(threading.Thread):
    def __init__(self,db):
        threading.Thread.__init__(self)
        self.db = db
    def run(self):
        run_insert(self.db)

def run_insert(db):
    sql = "INSERT INTO `test` (`id`,`col_a`,`col_b`,`col_c`) VALUES (NULL,'0','0','0');"
    print "thread executing sql:%s"%(sql)
    try:
        cursor = db.cursor()
        cursor.execute(sql)
        cursor.close()
        db.commit()
    except:
        print "insert failed"

    return

def init_thread(): 
    backgrounds = []
    for db in connections:
        print "connection: %s"%(db)
        background = threader(db)
        background.start()
        backgrounds.append(background)

    for background in backgrounds:
        background.join()


def main():
    try:
        init_thread()
    except:
        print "failed to initiate threads"
    sys.exit(0)




if __name__ == "__main__":
    ....
    threads = int(4) #quantity of execution threads and size of connection pool

    connections = []
    for thread in range(threads):
        try:
            connections.append(MySQLdb.connect(host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db, port=mysql_port))
            #pdb.set_trace()

        except MySQLdb.Error, e:
            print "Error %d: %s"%(e.args[0], e.args[1])
            pdb.set_trace()            
            sys.exit (1)


    main()



..."""




//=== http://stackoverflow.com/questions/12919047/mysql-connector-pooling-with-many-async-inserts
C#, mysql connector, pool

"""...
It is suggested to not use a global MySqlConnection object ...
Further, it's suggested to use the MySqlHelper instead of working with MySqlCommand objects.

...

MySqlHelper.ExecuteNonQuery(CONNECTION_STRING,
@"INSERT INTO `table1` (`col3`,`col4`) VALUES (@col3, @col4 );",
(new List() {
new MySqlParameter("@col3", @"another value"),
new MySqlParameter("@col4", @"some value")
}).ToArray()
);
Now, imagine that I make the above call a few thousand times in async parallel tasks


added this in my connection string:
Pooling=True;minimumPoolSize=0;maximumpoolsize=100;

...
Yet, I still get max connections exceeded

need multiple connections to execute queries in parallel.
...
[on server side] In addition to max_connections, the max_user_connections is what is important here. It can be queried for:
show variables like 'max_user_connections';

...
To avoid exceeding max connections for your MySQL user you'll need to make sure the maximumpoolsize (in the connection string) is set to something lower than max_user_connections



..."""


*** python threading or multiprocessing ?
//=== http://stackoverflow.com/questions/3044580/multiprocessing-vs-threading-python

GIL: Global Interpreter Lock

"""...

... multiprocessing gets around the Global Interpreter Lock, but what other advantages are there

...
 The threading module uses threads, the multiprocessing uses processes. 
The difference is that threads run in the same memory space, 
while processes have separate memory. 
This makes it a bit harder to share objects between processes with multiprocessing. 
Since threads use the same memory, precautions have to be taken 
or two threads will write to the same memory at the same time. 
This is what the global interpreter lock is for.

Spawning processes is a bit slower than spawning threads. 
Once they are running, there is not much difference.


...
Threading's job is to enable applications to be responsive. Suppose you have a database connection and you need to respond to user input.
... By splitting off the database connection into a separate thread you can make the application more responsive. Also because both threads are in the same process, they can access the same data structures - good performance, plus a flexible software design.

Note that due to the GIL the app isn't actually doing two things at once, but what we've done is 
put the resource lock on the database into a separate thread so that 
CPU time can be switched between it and the user interaction. 
...


Multiprocessing is for times when you really do want more than one thing to be done at any given time. 
Suppose your application needs to connect to 6 databases and perform a complex matrix transformation on each dataset. 
Putting each job in a separate thread might help a little because when one connection is idle another one could get some CPU time, but the processing would not be done in parallel ... 

By putting each job in a Multiprocessing process, each can run on it's own CPU and run at full efficiency.




..."""

mysql consecutive inserts on one connection lead to race?


http://stackoverflow.com/questions/19283825/mysql-transactions-not-stopping-race-conditions-from-for-loop

select ... for update to prevent race condition ?

"""...
var testTransaction = function (count) {
connection.beginTransaction(function(err) {
if (err) throw err;
db.query('SELECT * FROM myTable WHERE id = 1 FOR UPDATE', function(err, rows, result) {
if (err) {
connection.rollback(function() {
throw err;
});
}
connection.query('UPDATE myTable SET myField=? WHERE id=1', (count + 1), function(err, result) {
if (err) {
db.rollback(function() {

...
}

for (var i = 0; i < 2; i++) { testTransaction(i); } ...""" 


//===================================
--> uses a different connection for each transaction.

function conn() {
var connection = mysql.createConnection({
host : 'localhost',
user : 'user',
password : 'pass',
database : 'test'
});
return connection;
}

for (var i = 0; i < 2; i++) { testTransaction(conn(),i); } 


--> or promise pattern
transaction1.then(transaction2);




mysql multiple-row insert, single-row insert, or transaction insert


http://stackoverflow.com/questions/1793169/which-is-faster-multiple-single-inserts-or-one-multiple-row-insert

"""...
Which is faster: multiple single INSERTs or one multiple-row INSERT?

...
use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.

...
in general, multiple inserts will be slower because of the connection overhead. Doing multiple inserts at once will reduce the cost of overhead per insert.


..."""


INSERT INTO tablename (col1, col2) VALUES (1, 2), (3, 4), (5, 6), (7, 8)
Which will insert 4 rows.



//=== https://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
"""...
The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

Connecting: (3)

Sending query to server: (2)

Parsing query: (2)

Inserting row: (1 × size of row)

Inserting indexes: (1 × number of indexes)

Closing: (1)
..."""



//=== https://github.com/felixge/node-mysql/
"""...
connection.beginTransaction(function(err) {
if (err) { throw err; }
connection.query('INSERT INTO posts SET title=?', title, function(err, result) {
if (err) {
connection.rollback(function() {
throw err;
});
}

var log = 'Post ' + result.insertId + ' added';

connection.query('INSERT INTO log SET data=?', log, function(err, result) {
if (err) {
connection.rollback(function() {
throw err;
});
}
connection.commit(function(err) {
if (err) {
connection.rollback(function() {
throw err;
});


.."""



grep to find word in files


$ man grep

-r search recursively if the given path is a directory
-i case-insensitive search
-n print the line number

-H print the filename [implied when multiple files are searched, i.e. no need to add -H
when the given path stands for mutliple files]

-I ignore binary files (complement: -a treat all files as text)
-F treat search term as a literal, not a regular expression


--exclude=GLOB
Skip files whose base name matches GLOB (using wildcard
matching). A file-name glob can use *, ?, and [...] as
wildcards, and \ to quote a wildcard or backslash character
literally.

--exclude-from=FILE
Skip files whose base name matches any of the file-name globs
read from FILE (using wildcard matching as described under
--exclude).

--exclude-dir=DIR
Exclude directories matching the pattern DIR from recursive searches.
[ e.g, to exlcude .svn and .git
xxx $ grep -r "non-sense" --exclude-dir='\.svn|\.git' (regex not supported for --exclude-dir ??)
--> $ grep -r "non-sense" --exclude-dir=.svn --exclude-dir=.git

http://superuser.com/questions/66715/recursive-grep-exclude-specific-directories

http://stackoverflow.com/questions/19962645/bash-function-grep-exclude-dir-not-working
the version of grep may not be able to use --exclude-dirs.

]


-E, --extended-regexp
-e PATTERN, --regexp=PATTERN
Use PATTERN as the pattern. This can be used to specify
multiple search patterns, or to protect a pattern beginning with
a hyphen (-). (-e is specified by POSIX.)



-R, --dereference-recursive
Read all files under each directory, recursively. Follow all
symbolic links, unlike -r.




-w, --word-regexp [to find a string that is a separate word (enclosed by spaces)]
Select only those lines containing matches that form whole
words. The test is that the matching substring must either be
at the beginning of the line, or preceded by a non-word
constituent character. Similarly, it must be either at the end
of the line or followed by a non-word constituent character.
Word-constituent characters are letters, digits, and the
underscore.


-L, --files-without-match [only print filenames that are not matched]
Suppress normal output; instead print the name of each input
file from which no output would normally have been printed. The
scanning will stop on the first match.

-l, --files-with-matches [only print the matching filenames]
Suppress normal output; instead print the name of each input
file from which output would normally have been printed. The
scanning will stop on the first match. (-l is specified by
POSIX.)


[examples]
$ grep -r word_to_find .
[to search "word_to_find" recursively under the current directory ]


$ grep -n "> tmp1.txt" *.sh
[to find string "> tmp1.txt" in all files with extension name ".sh" in the current folder, not recursively]

xxx often mistake
$ grep myutil.js .
grep: .: Is a directory
xxx

-->
$ grep -r myutil.js .
$ grep myutil.js *.js
$ grep "myutil.js" *.js

$ grep *.js *.js
$ grep ".*js" *.js
[here, ".*js" stands for a regex in bash]

$ grep -l "> tmp1.txt" *.sh
[list all the names of files which contain "> tmp1.txt" ]

$ grep -r "myutil.js" . --exclude-dir=.git
$ grep -r "myutil.js" . --exclude-dir=?git
[though works as well, it means to exclude all subfolders whose name is even like agit, bgit, mgit, .git, etc. ]



[ref]
http://tldp.org/LDP/Bash-Beginners-Guide/html/sect_04_02.html

bash regex
http://tldp.org/LDP/Bash-Beginners-Guide/html/sect_04_01.html
http://tldp.org/LDP/abs/html/x17129.html

http://ubuntu.elfscript.com/2013/09/ubuntu.html



mongo, mongodb client shell, query



//=== http://docs.mongodb.org/manual/tutorial/getting-started-with-the-mongo-shell/
$ mongo
> db
> use another_db
> db.collectionName.find();

//=== http://docs.mongodb.org/v2.2/reference/method/db.collection.find/

db.products.find( { qty: { $gt: 25 } }, { item: 1, qty: 1 } )

This operation returns all the documents from the collection "products"
where "qty" is greater than 25.
The documents in the results y include the
"_id", "item", and "qty" fields

using “inclusion” projection by the parameter { item: 1, qty: 1 }

find() always returns the _id field, even when not explicitly included:

Results:
{ "_id" : 11, "item" : "pencil", "qty" : 50 }
{ "_id" : ObjectId("50634d86be4617f17bb159cd"), "item" : "bottle", "qty" : 30 }
{ "_id" : ObjectId("50634dbcbe4617f17bb159d0"), "item" : "paper", "qty" : 100}

"exlcude" projection
db.products.find( { qty: { $gt: 25 } }, { _id: 0, qty: 0 } )

Results:
{ "item" : "pencil", "type" : "no.2" }
{ "item" : "bottle", "type" : "blue" }
{ "item" : "paper" }



//=== http://docs.mongodb.org/manual/tutorial/write-scripts-for-the-mongo-shell/

*** mongo client shell
$ mongo
> db = connect("localhost:27017/db1");
> show collections;
> db.collect1.insert({k1:'v1',k2:'v2', k3:v3});

> db=connect("db2");
> show collections;

Differences Between Interactive and Scripted mongo ?


To set the db global variable, use the getDB() method or the connect() method. You can assign the database reference to a variable other than db.

You cannot use any shell helper (e.g. use , show dbs, etc.) inside the JavaScript file because they are not valid JavaScript.


hell Helpers
JavaScript Equivalents

show dbs, show databases
db.adminCommand('listDatabases')

use
db = db.getSiblingDB('')

show collections
db.getCollectionNames()

show users
db.getUsers()

show roles
db.getRoles({showBuiltinRoles: true})

show log
db.adminCommand({ 'getLog' : '' })

show logs
db.adminCommand({ 'getLog' : '*' })



it
cursor = db.collection.find()
if ( cursor.hasNext() ){
cursor.next();
}







mongodb adaptor for nodejs


//=== http://stackoverflow.com/questions/24736999/how-can-i-require-mongodb-within-casperjs

install mongodb driver for nodejs
$ npm install mongodb

* this mongodb module is just an adaptor for nodejs to access the actual mongodb server,
you have to setup the mongodb server beside the module.



//=== https://mongodb.github.io/node-mongodb-native/api-generated/mongoclient.html

MongoClient class's instance member functions:
connect(), open(), close(), db()

connect(url[, options], callback)
open(callback)
close(callback)
db(dbName)



"""...
var Db = require('mongodb').Db,
MongoClient = require('mongodb').MongoClient,
Server = require('mongodb').Server,
ReplSetServers = require('mongodb').ReplSetServers,
ObjectID = require('mongodb').ObjectID,
Binary = require('mongodb').Binary,
GridStore = require('mongodb').GridStore,
Grid = require('mongodb').Grid,
Code = require('mongodb').Code,
BSON = require('mongodb').pure().BSON,
assert = require('assert');

// Set up the connection to the local db
var mongoclient = new MongoClient(new Server("localhost", 27017), {native_parser: true});

// Open the connection to the server
mongoclient.open(function(err, mongoclient) {

// Get the first db and do an update document on it
var db = mongoclient.db("integration_tests");
db.collection('mongoclient_test').update({a:1}, {b:1}, {upsert:true}, function(err, result) {
assert.equal(null, err);
assert.equal(1, result);

// Get another db and do an update document on it
var db2 = mongoclient.db("integration_tests2");
db2.collection('mongoclient_test').update({a:1}, {b:1}, {upsert:true}, function(err, result) {
assert.equal(null, err);
assert.equal(1, result);

// Close the connection
mongoclient.close();
});
});
});

..."""


//=== static member function, MongoClient.connect(url, cb)
Connect to MongoDB using a url as documented


var MongoClient = require('mongodb').MongoClient;
var assert = require('assert')
var ObjectId = require('mongodb').ObjectID;
var url = 'mongodb://myMongo1:27017/test';


db.collection('restaurants1').insertOne();

MongoClient.connect(url, function(err, db){});









nodejs and phantomjs


//=== phantomjs-node module for nodejs
https://github.com/sgentle/phantomjs-node
"""...
This module expects the phantomjs binary to be in PATH somewhere. In other words, type this:

$ phantomjs
If that works, so will phantomjs-node.

..."""

0. verfiy that phantomjs binary already works by
$ phantomjs --version

1. then install phantomjs-node module for nodejs by
$ npm install phantom


[ex]
var phantom = require('phantom');

phantom.create(function (ph) {
ph.createPage(function (page) {
page.open("http://www.google.com", function (status) {
console.log("opened google? ", status);
page.evaluate(function () { return document.title; }, function (result) {
console.log('Page title is ' + result);
ph.exit();
});
});
});
});



"""...
... How do you communicate with a process that doesn't support shared memory, sockets, FIFOs, or standard input?

Well, there's one thing PhantomJS does support, and that's opening webpages. In fact, it's really good at opening web pages. So we communicate with PhantomJS by spinning up an instance of ExpressJS, opening Phantom in a subprocess, and pointing it at a special webpage that turns socket.io messages into alert() calls. Those alert() calls are picked up by Phantom and there you go!

The communication itself happens via James Halliday's fantastic dnode library, which fortunately works well enough when combined with browserify to run straight out of PhantomJS's pidgin Javascript environment.

..."""



//=== phantomjs module for nodejs
https://www.npmjs.com/package/phantomjs
[ An NPM wrapper for PhantomJS ]


$ npm install phantomjs

Or grab the source and

$ node ./install.js

What this installer is really doing is just grabbing a particular "blessed" version of PhantomJS

"""...
PhantomJS is not a library for NodeJS. It's a separate environment and code written for node is unlikely to be compatible. In particular PhantomJS does not expose a Common JS package loader.

This is an 'NPM wrapper' and can be used to conveniently make Phantom available.
It is not a 'NodeJS wrapper'.

... drive from within a node program by spawning phantom in a child process.

... tell NPM and the PhantomJS installer to skip validation of ssl keys with NPM's strict-ssl setting:

$ npm set strict-ssl false



..."""


//=== node-phantom module for nodejs
https://www.npmjs.com/package/node-phantom
[ This is a bridge between PhantomJs and Node.js. ]

0. The bridge assumes that the "phantomjs" binary is available in the PATH.

1. The only other dependency is socket.io

*** need to manually install socket.io and node-phantom

[ex]
var phantom=require('node-phantom');
phantom.create(function(err,ph) {
return ph.createPage(function(err,page) {


//=== phantomjs on nodejs-docker
[err] phantomjs: error while loading shared libraries: libjpeg.so.8: cannot open shared object file: No such file or directory




how to transfer a docker image from one computer to another


http://stackoverflow.com/questions/23935141/how-to-copy-docker-images-from-one-host-to-another-without-via-repository
http://it.3hd.me/2015/03/docker-exportimport-pullcommit-saveload.html


//=== save/load

0. save the docker image as a tar file on pc1

$ docker save -o savefname.tar imgname
or
$ docker save imgname > savefname.tar


1. scp savefname.tar to pc2
[to save bandwidth, compress-> send-> decompress is recommended]

2. load savefname.tar to image on pc2
$ docker load < savefname.tar
or
$ docker load -i savefname.tar


3. verify 'imgname' is restored on pc2
$ docker images -a imgname



//=== export/import
[operating on container(running or not), the resultant img will lose previous history and layers]

0. on pc1:
$ docker export container1 > c1.tar

1. copy c1.tar from pc1 to pc2

2. on pc2:
$ cat c1.tar | docker import - new_imgname:tagname




//=== http://unix.stackexchange.com/questions/70581/scp-and-compress-at-the-same-time-no-intermediate-save
[Q] does scp compress file implicitly ???

"""...
scp itself can compress the data

-C Compression enable. ...

$ scp -C source user@server:/path/to/backup


...
7z should not be used for backups on Linux. As stated on the 7z man page:

DO NOT USE the 7-zip format for backup purpose on Linux/Unix because :
- 7-zip does not store the owner/group of the file.


..."""


$ tar zcvf - MyBackups | ssh user@server "cat > /path/to/backup/foo.tgz"





html element scrollWidth, clientWidth , scrollbar width


scrollWidth(including non-viewable part) 與 clientWidth (only viewable part) 的差異在於 "viewable" or not

//=== http://www.w3schools.com/jsref/prop_element_scrollwidth.asp
"""...
The scrollWidth property returns the entire width of an element in pixels, including padding, but not the border, scrollbar or margin.

...
The scrollWidth and scrollHeight properties return the entire height and width of an element, including the height and width that is not viewable (because of overflow).



..."""


//=== http://www.w3schools.com/jsref/prop_element_clientwidth.asp

clientWidth
"""...
The clientWidth property returns the viewable height of an element
in pixels, including padding, but not the border, scrollbar or margin.
..."""




//=== https://developer.mozilla.org/en-US/docs/Web/API/Element/scrollWidth
"""...
The Element.scrollWidth read–only property returns either the width in pixels of the content of an element or the width of the element itself, whichever is greater. If the element is wider than its content area (for example, if there are scroll bars for scrolling through the content), the scrollWidth is larger than the clientWidth.

This property will round the value to an integer. If you need a fractional value, use element.getBoundingClientRect().

..."""



//=== scrollbar width

http://stackoverflow.com/questions/19172936/javascript-get-window-width-minus-scrollbar-width

"""...
the scrollbar is a feature of the browser and not the web page self.
Measurement should be done dynamically.
A measurement with a scrollbar and a measurement without a scrollbar
will resolve into calculating the difference in width.

..."""

the width of the scrollbar is not standard and
varies from system to system and browser to browser







html element offsetWidth, clientWidth


//=== http://www.w3schools.com/jsref/prop_element_clientwidth.asp
"""...
The clientWidth property returns the viewable height of an element
in pixels, including padding, but not the border, scrollbar or margin.
..."""


//=== http://www.w3schools.com/jsref/prop_element_offsetwidth.asp
"""...
The offsetWidth property returns the viewable width of an element
in pixels, including padding, border and scrollbar, but not the margin.
..."""


//=== http://www.w3schools.com/css/css_boxmodel.asp

css box :
from outside toward inside
margin --> border --> padding --> content

the 'width' in css style means the 'content' width --> css width ?

"""...
div {
width: 320px;
padding: 10px;
border: 5px solid gray;
margin: 0;
}


Total element width =
width
+ left padding + right padding
+ left border + right border
+ left margin + right margin

Total element height =
height
+ top padding + bottom padding
+ top border + bottom border
+ top margin + bottom margin

..."""





html element's innerText or textContent?


//=== https://developer.mozilla.org/en-US/docs/Web/API/Node/textContent

"""...
Differences from innerText

Internet Explorer introduced element.innerText. The intention is similar but with the following differences:

While textContent gets the content of all elements, including script> and style> elements, the IE-specific property innerText does not.
innerText is aware of style and will not return the text of hidden elements, whereas textContent will.
As innerText is aware of CSS styling, it will trigger a reflow, whereas textContent will not.


..."""



//=== http://stackoverflow.com/questions/24427621/innertext-vs-innerhtml-vs-label-vs-text-vs-textcontent-vs-outertext
text or innerText or jQuery.text()

http://www.w3schools.com/jsref/prop_option_text.asp
The text property sets or returns the text of an "option" element.




html node or element ? document or window ?


//=== node or element ?
http://www.w3schools.com/jsref/dom_obj_all.asp
"""...
In the HTML DOM (Document Object Model), everything is a node:

The document itself is a document node
All HTML elements are element nodes
All HTML attributes are attribute nodes
Text inside HTML elements are text nodes
Comments are comment nodes

..."""



//=== parentElement or parentNode ?
http://www.w3schools.com/jsref/prop_node_parentelement.asp


parentElement returns null if the parent node is not an element node

document.documentElement.parentNode; // Returns the "document" node
document.documentElement.parentElement; // Returns null

document.body.parentNode; // Returns the element
document.body.parentElement; // Returns the element


//=== document or window ?
http://www.w3schools.com/jsref/obj_window.asp

"""...
The window object represents an open window in a browser.

If a document contain frames (iframe tags), the browser creates one window object for the HTML document, and one additional window object for each frame.
..."""


//=== document object (Document object)
http://www.w3schools.com/jsref/dom_obj_document.asp

"""...
When an HTML document is loaded into a web browser, it becomes a document object.

The document object is the root node of the HTML document and the "owner" of all other nodes:
(element nodes, text nodes, attribute nodes, and comment nodes).

..."""



git, how to show history for a certain file


http://stackoverflow.com/questions/278192/view-the-change-history-of-a-file-using-git-versioning
"""...
use
$ git log -p filename
to let git generate the patches for each log entry.

...
use
$ gitk
to browse the changes visually.

...
$ git log --follow -p -- file
In other words, if the file named bar was once named foo, then git log -p bar (without the --follow option) will only show the file's history up to the point where it was renamed -- it won't show the file's history when it was known as foo.

Using git log --follow -p bar will show the file's entire history, including any changes to the file when it was known as foo.
...


..."""

after
$ git log -p file1

then scroll down to the target date you need,
copy the commit-id,

then
$ git checkout commit-id file1

$ cp file1 file1_old.bak

# restore file1 to that on master branch
$ git checkout master file1

# extract what you need from file1_old.bak to merge into file1 on branch master ...







mysql, error 1221, grant replication slave


//===
http://stackoverflow.com/questions/13552206/grant-file-on-just-one-database

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant file on johndatabase.* to 'john'@'localhost';

--> grant file on *.* to 'john'@'localhost';


mysql> grant replication slave on db1.* to 'repl001'@'192.168.42.30'

--> grant replication slave on *.* to 'repl001'@'192.168.42.30'


Both "file" and "replication slave" are Global privileges,
which cannot be granted to a specific database.



//=== confusing and conflicting setup for replication user repl_u13
mysql> grant replication slave on *.* to repl_u13@'%' identified by 'pass1';
mysql> grant replication slave on *.* to repl_u13@'192.168.42.30' identified by 'pass2';

taking either one, not both, to avoid troubles.



mysql upgrade logs


after adding mysql apt repo ( /etc/apt/sources.list.d/mysql.list will be created )
$ sudo apt-get update
$ sudo apt-get install mysql-server

"""...
...
Once you are satisfied with the configuration then select last option 'Apply' to
save the configuration. Advanced users can always change the configurations
later, depending on their own needs.

1. Server 2. Workbench 3. Connector-Python 4. Utilities 5. Apply

Which MySQL product do you wish to configure? 1


...
If you are not sure which version to
choose for yourself, do not change the auto-selected version. Advanced users can
always change the version later, depending on their own needs.

1. mysql-5.6 2. mysql-5.7-dmr 3. none

Which server version do you wish to receive? 1


...
Once you are satisfied with the configuration then select last option 'Apply' to
save the configuration. Advanced users can always change the configurations
later, depending on their own needs.

1. Server 2. Workbench 3. Connector-Python 4. Utilities 5. Apply

Which MySQL product do you wish to configure? 5

..."""


//=== backup old conf on another terminal
cp /etc/mysql/my.cnf my.cnf.bak


"""...
Configuration file '/etc/mysql/my.cnf'
==> Modified (by you or by a script) since installation.
==> Package distributor has shipped an updated version.
What would you like to do about it ? Your options are:
Y or I : install the package maintainer's version
N or O : keep your currently-installed version
D : show the differences between the versions
Z : start a shell to examine the situation
The default action is to keep your current version.
*** my.cnf (Y/I/N/O/D/Z) [default=N] ? Y
Installing new version of config file /etc/mysql/my.cnf ...
Setting up libapparmor1:amd64 (2.8.95~2430-0ubuntu5.1) ...
Setting up libapparmor-perl (2.8.95~2430-0ubuntu5.1) ...

..."""


//===
$ nano /etc/mysql/my.cnf
$ sudo service mysql start
No directory, logging in with HOME=/
150508 06:45:50 mysqld_safe Can't log to error log and syslog at th Remove all --log-error configuration options for --syslog to take
......
* MySQL Community Server 5.6.24 did not start. Please check logs fls.

$ cat /var/log/mysql/error.log

--> $ nano /etc/mysql/my.cnf [to correct typo errors]

$ sudo service mysql start
No directory, logging in with HOME=/
150508 06:48:53 mysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect.
......
* MySQL Community Server 5.6.24 is started


-->
http://serverfault.com/questions/441195/mysqld-safe-cant-log-to-error-log-and-syslog-at-the-same-time-remove-all-log
http://shinguz.blogspot.hk/2010/01/mysql-reporting-to-syslog.html
https://bugs.launchpad.net/mylvmbackup/+bug/1091950
http://dev.mysql.com/doc/refman/5.1/en/error-log.html


??? add "--skip-syslog" to the options

http://dba.stackexchange.com/questions/10117/why-is-mysql-logging-to-syslog
"""...
From 5.1.20 on, mysqld_safe has two additional error-logging options, --syslog and --skip-syslog.

In 5.1.21 and up, the default with no logging options is --skip-syslog, which is compatible with the default behavior of writing an error log file for releases prior to 5.1.20. To explicitly specify use of an error log file, specify --log-error=file_name to mysqld_safe, and mysqld_safe will arrange for mysqld to write messages to a log file. To use syslog instead, specify the --syslog option.

In 5.1.20 only, the following conditions apply: 1) The default is to use syslog, which is not compatible with releases prior to 5.1.20. 2) Logging to syslog may fail to operate correctly in some cases; if so, use --skip-syslog or --log-error.
..."""


$ cat /etc/mysql/conf.d/my.cnf
[mysqld]
bind-address=0.0.0.0

$ cat /etc/mysql/conf.d/mysqld_safe_syslog.cnf
[mysqld_safe]
syslog


-->
[mysqld_safe]
skip-syslog






mysql, how to clear master.info


https://dev.mysql.com/doc/refman/5.0/en/reset-slave.html
"""...
RESET SLAVE makes the slave forget its replication position in the master's binary log.
This statement is meant to be used for a clean start:
It deletes the master.info and relay-log.info files,
all the relay log files, and starts a new relay log file.
To use RESET SLAVE, the slave replication threads must be stopped ...
..."""


$ sudo cat /var/lib/mysql/master.info
"""
23
mysql-bin-changelog.012909
120
mysql1.chswwb1olfin.us-west-2.rds.amazonaws.com
repl_u13
pass_u13
3306
60
0
...
0
1800.000
...
0
"""

$ mysql -uroot -p
mysql> reset slave;
mysql > exit

$ sudo cat /var/lib/mysql/master.info
[sudo] password for u13:
cat: /var/lib/mysql/master.info: No such file or directory


//===
mysql> \! nano ~/change_master_to.sql
mysql> source ~/change_master_to.sql
Query OK, 0 rows affected, 2 warnings (0.65 sec)

mysql> show warnings;

| Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. |
| Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |

mysql> start slave password='pass_u13'


mysql> show slave staus \G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'staus' at line 1


--> typo error
mysql> show slave status \G;
"""...
1593 Last_IO_Error: Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated.
..."""


-->
mysql> start slave user='repl_u13' password='pass_u13'


mysql> \! sudo cat /var/lib/mysql/master.info

[Q] why is master_password different from that in "start slave ... " ???

[Q] after reset slave and source the new change_master_to.sql,
master.info still shows the wrong password?




mysql reovke examples


mysql> REVOKE INSERT ON *.* FROM 'jeff'@'localhost';

mysql> REVOKE Replication Client ON *.* FROM 'repl'@'%';

mysql> REVOKE Replication Client ON *.* FROM 'repl'@'192.168.1.1';

mysql> REVOKE Replication Slave ON *.* FROM 'repl'@'%';

mysql> REVOKE Replication Slave ON *.* FROM 'repl'@'192.168.1.1';

mysql> show grants for repl;

mysql> show grants for repl@'%';

mysql> show grants for repl@'192.168.1.1';

mysql> select user,host from mysql.user;



"GRANT Replcation Slave ON *.* TO 'repl'@'%' IDENTIFIED BY PASSWORD '***********';"
disappeared

but
"GRANT USAGE ON *.* TO 'repl'@'%' IDENTIFIED BY PASSWORD '***********';"
is still there

--> http://dba.stackexchange.com/questions/13083/cant-remove-grant-usage
"""...
When you ran the REVOKE command, you simply removed the row from mysql.db. This did not touch the row in mysql.user.

...
You can't actually revoke USAGE, without dropping the user.USAGE is a global level privilege.

..."""


-->
mysql> drop user repl@'%';



//=== https://dev.mysql.com/doc/refman/5.0/en/revoke.html
To revoke all privileges,
( drops all global, database, table, column, and routine privileges for the named user or users)

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM u11,u12,u13;





webcrawler


//=== 2015.05.07
google search webcrawler

'WebCrawler' is a registered trademark
'web crawler' is a general term to stand for the program which 'crawls' web page automatically.
--> internet bot, web scraper, web spider, web ant, web indexer?



//===
1. https://www.webcrawler.com/
Search via Google Yahoo!
hosted by InfoSpace LLC. (Bluecora Inc. ?)

main categories: web, images, videos, news

2. http://en.wikipedia.org/wiki/WebCrawler

metasearch engine


"""...
WebCrawler is a metasearch engine that blends the top search results from Google Search and Yahoo! Search.
WebCrawler also provides users the option to search for images, audio, video, news, yellow pages and white pages.
WebCrawler is a registered trademark of InfoSpace, Inc.
It went live on April 20, 1994 and was created by Brian Pinkerton at the University of Washington.

...
WebCrawler was the first Web search engine to provide 'full text search'.
It was bought by America Online on June 1, 1995 and sold to Excite on April 1, 1997.
WebCrawler was acquired by InfoSpace in 2001 ...

InfoSpace also owns and operates the metasearch engines Dogpile and MetaCrawler.

...
WebCrawler was originally a separate search engine with its own database ...
More recently it has been repositioned as a metasearch engine, providing a composite of
... search results from most of the popular search engines




..."""




3. http://en.wikipedia.org/wiki/Web_crawler

"""...
Web search engines and some other sites use Web crawling or spidering software
to update their web content or indexes of others sites' web content.
Web crawlers can copy all the pages they visit for later processing by a search engine
which "indexes" the downloaded pages so the users can search much more efficiently.

Crawlers can validate hyperlinks and HTML code. They can also be used for web scraping ...

...
"Given that the bandwidth for conducting crawls is neither infinite nor free,
it is becoming essential to crawl the Web in not only a scalable, but efficient way, ...
A crawler must carefully choose at each step which pages to visit next.


...
Crawlers usually perform some type of URL normalization in order to avoid crawling the same resource more than once.
The term URL normalization, also called URL canonicalization,
refers to the process of modifying and standardizing a URL in a consistent manner.

There are several types of normalization that may be performed including
conversion of URLs to lowercase,
removal of "." and ".." segments, and
adding trailing slashes to the non-empty path component. ...



... general open source crawlers, such as Heritrix, must be customized to filter out other MIME types, or a middleware is used to extract these documents (pdf, word, ps...) out and import them to the focused crawl database and repository....
Identifying whether these documents are academic or not is challenging and can add a significant overhead to the crawling process, so this is performed as a
post crawling process using machine learning or regular expression algorithms. ...


Coffman et al. ... they propose that a crawler must minimize the fraction of time pages remain outdated.
They also noted that the problem of Web crawling can be modeled as a multiple-queue, single-server polling system,
on which the Web crawler is the server and the Web sites are the queues.
Page modifications are the arrival of the customers, and
switch-over times are the interval between page accesses to a single Web site.

Under this model, mean waiting time for a customer in the polling system is equivalent to the average age for the Web crawler




...
Crawling the deep web
A vast amount of web pages lie in the deep or invisible web. ... typically only accessible by submitting queries to a database,
and regular crawlers are unable to find these pages if there are no links that point to them.
Google's Sitemaps protocol ... intended to allow discovery of these deep-Web resources.

...
Pages built on AJAX are among those causing problems to web crawlers. Google has proposed a format of AJAX calls that their bot can recognize and index.


...
Visual vs programmatic crawlers
...
The latest generation of "visual scrapers" like outwithub[...] and import.io[...] remove the majority of the programming skill needed to be able to program and start a crawl to scrape web data.

...
The visual scraping/crawling methodology relies on the user "teaching" a piece of crawler technology,
which then follows patterns in semi-structured data sources.
The dominant method for teaching a visual crawler is by highlighting data in a browser and
training columns and rows.
While the technology is not new, for example it was the basis of Needlebase which has been bought by Google ...
there is continued growth and investment in this area by investors and end-users


..."""


4. http://searchengineland.com/apple-confirms-their-web-crawler-applebot-220423

Applebot

"""...
Apple said, Applebot is the web crawler for Apple. AppleBot is “used by products including Siri and Spotlight Suggestions,” ...

The user-agent ... will contain “Applebot” in it always:

Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.2.5 (KHTML, like Gecko) Version/8.0.2 Safari/600.2.5 (Applebot/0.1)

Apple says it will respect the customary robots.txt rules and robots meta tags. AppleBot currently originates in the 17.0.0.0 net block. If you do not mention AppleBot in your robots.txt directive, Apple will follow what you mention for Googlebot. So if you want to block AppleBot and GoogleBot, you can just block GoogleBot, but I’d recommend you block each individually.


...
Apple is “rapidly-expanding internal search group” to build their own version of a web search engine via Spotlight.


..."""





2015 網路報稅

tax.nat 跟 etax.nat 指向不一樣的內容!!
綜所稅申報
請連 http://www.tax.nat.gov.tw/
別連 http://www.etax.nat.gov.tw/


* 電子報繳稅服務已停用SSL V3.0傳輸加密服務，請用戶改用TLS V1.0或 V1.1傳輸加密。


報稅程式在 英文版win8.1 還是有問題
[ 自動安裝版[15,957,456 位元組]建議使用。下載完成後直接執行即可進行安裝。]
中文字變成一堆亂碼
自然人憑證IC卡 久未使用
拔插了幾次 還是不行
--> unknown smart card

換用金融卡登入網路ATM , 沒問題
所以讀卡機是正常的

HiCosClient Inspector(x64) 測試
在第三項失敗 停下來 middle version?

換用舊電腦 中文繁體 win xp
安裝報稅程式, 雖然文字正常顯示
但還是一樣讀不到自然人憑證 unknown smart card

過了約20分鐘 正打算放棄之際
燈號忽然閃爍顯示有在讀取資料
執行 HiCosClient Inspector, 測試通過

執行報稅程式 讀取自然人憑證 提示輸入PIN ,
登入下載所得資料 ok

--> 不信邪, 再回到win8.1 試試, 雖然中文字亂碼,
但這回至少 讀的到 自然人憑證, 也有提示輸入PIN,
真邪門 ...



//=== 稅額試算服務

* 線上確認稅額試算書 就不用郵寄回函
* 線上確認稅額試算, 國稅局核定符合應退稅額者，退稅日期為104年7月31日

http://www.ntbna.gov.tw/etwmain/front/ETW118W/CON/1281/4938089589790683393?tagCode=

"""...
退稅案件及不繳不退案件可利用 "網路或電語語音"(退稅案件僅限沿用上年度帳戶資料)辦理回復確認，
或將書面確認申報書送交國稅局才算完成103年度綜合所得稅結算申報。

稅額試算服務線上登錄回復

1.若使用IE 7.0以上瀏覽器，建議先將服務網址 https://rtn.tax.nat.gov.tw 加入信任的網站。
2.若使用晶片金融卡線上繳稅，建議先將繳稅服務網址 https://paytax.nat.gov.tw 加入信任的網站。
3.若下載103年度稅額試算書表電子檔案，建議先將服務網址 https://pfiles.tax.nat.gov.tw 加入信任的網站。
4.若使用憑證登入下載103年度稅額試算書表電子檔案，需先安裝系統提供的ActiveX元件。 IE7及IE8如何設定安裝ActiveX元件說明； IE9如何設定安裝ActiveX元件說明。
..."""



* 稅額試算服務線上登錄(含已繳稅填寫滿意度調查)
https://rtn.tax.nat.gov.tw/ibx/ibx_login.jsp

* 103年度稅額試算書表電子檔案下載
https://pfiles.tax.nat.gov.tw/ibxpdf/ibx_pdfDownload.jsp


* 回復確認結果查詢
http://tax.nat.gov.tw/info_IBX_search.html?id=6#


//=== 要繳證所稅???
針對大戶, 小小散戶如我 毋須煩惱 ...

https://www.ntbt.gov.tw/etwmain/front/ETW118W/CON/926/8373102627425182656?tagCode=

"""...
Q20:
自102年1月1日起，證券交易所得課稅的種類有哪些？有哪些證券的證券交易所得不課稅？

A:

(一)
課徵綜合所得稅的證券：上市、上櫃、興櫃及未上市、未上櫃且未登錄興櫃(以下簡稱未上市未上櫃)的股票（包括新股權利證書、股款繳納憑證及表明其權利的證書，以下同）。

(二)
課徵基本稅額的證券：私募證券投資信託基金的受益憑證。

(三)
公債、公司債、金融債券、可轉換公司債、共同信託基金、證券投資信託基金或期貨信託基金的受益憑證、指數股票型基金(ETF)、認購(售)權證、存託憑證及證券化商品等，均非屬所得稅法第4條之1但書規定的課稅範圍，目前仍停徵綜合所得稅。

Q21:
哪些情況下的個人證券交易所得，應主動核實辦理申報？

A:
(一)
中華民國境內居住的個人有下列情形之一者：

1.
當年度出售興櫃股票數量合計在10萬股以上。

2.
初次上市、上櫃前取得的股票，於上市、上櫃以後出售。但有下列情形之一者，不包括在內：
(1)屬101年12月31日以前初次上市、上櫃的股票。
(2)屬承銷取得各該初次上市、上櫃公司股票數量在1萬股以下。

3.
出售未上市未上櫃股票。

4.
自107年起，當年度上市、上櫃及興櫃股票出售金額合計超過10億元（排除核實課稅標的及透過信託基金出售之金額）者：
課稅方式採「設算為主、核實為輔」，由國稅局歸戶後，就股票出售超過10億元之金額部分，另行發單課徵1‰所得稅。但納稅義務人亦得於結算申報時選擇就股票出售金額全數核實課稅。

(二)
非中華民國境內居住的個人出售股票。

..."""



mysql replication from remote RDS to localhost


//=== http://dba.stackexchange.com/questions/15440/replicate-rds-mysql-to-a-non-amazon-host

[errors]

ERROR 1045 (28000): Access denied for user 'repl_user'@'%' (using password: YES)

Slave I/O: error connecting to master 'repl_user@xxxx.xxxx.us-west-2.rds.amazonaws.com:3306' - retry-time: 60 retries: 11, Error_code: 1045


Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'


//=== https://www.percona.com/blog/2014/10/08/mysql-replication-got-fatal-error-1236-causes-and-cures/
max_allowed_packet
"""...
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:
‘log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; ...

This is a typical error on the slave(s) server. It reflects the problem around max_allowed_packet size. max_allowed_packet refers to single SQL statement sent to the MySQL server as binary log event from master to slave.

This error usually occurs when you have a different size of max_allowed_packet on the master and slave (i.e. master max_allowed_packet size is greater then slave server).

When the MySQL master server tries to send a bigger packet than defined on the slave server, the slave server then fails to accept it and hence the error.

In order to alleviate this issue please make sure to have the same value for max_allowed_packet on both slave and master.
..."""


"""...
Got fatal error 1236 from master when reading data from binary log:
‘Could not find first log file name in binary log index file’

This error occurs when the slave server required binary log for replication no longer exists on the master database server.
In one of the scenarios for this,
your slave server is stopped for some reason for a few hours/days and
when you resume replication on the slave it fails with above error.
..."""





[tips]
//=== on master side (remote RDS)
mysql> call mysql.rds_set_configuration('binlog retention hours', 24);"
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'repl_passwd';

[Q] is 'on *.*' must? why not only on the target database 'on db_to_be_replicated.*' ?
[Q] 'repl_user'@'%' shd be changed to 'repl_user'@'slave_public_ip' ?
[Q] is REPLICATION CLIENT optional?



//=== on slave side(localhost)

* logon to remote RDS by repl_user to verify the granted privileges
mysql -h rds_master -u repl_user -p
mysql> show grants;


* logon to localhost
mysql -uroot -p
mysql> stop slave
mysql> change master to
MASTER_HOST = 'rds.',
MASTER_PORT = 3306,
MASTER_LOG_FILE='mysql-bin.000526',
MASTER_LOG_POS=4;
xxx MASTER_USER = 'repl_user',
xxx MASTER_PASSWORD = 'xxx';


[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE ...


[ in mysql-5.6, for security concern
remove repl_user and repl_passwd info from change master to ;
use start slave user='...' password='...' ]
mysql> start slave user='repl_user' password='repl_passwd'





mysql apt repo


//=== http://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/

Steps to add add the MySQL APT Repository

0. Go to the download page for the MySQL APT repository

1. Select and download the release package for your platform.
http://dev.mysql.com/downloads/file.php?id=457235
--> http://dev.mysql.com/get/mysql-apt-config_0.3.5-1ubuntu14.04_all.deb

2. Install mysql-apt-config_xxxx.deb

shell> sudo dpkg -i /PATH/platform-and-version-specific-package-name.deb

For version w.x.y-z of the package for Ubuntu 14.04 LTS
-->
$ sudo dpkg -i mysql-apt-config_0.3.5-1ubuntu14.04_all.deb


3. Config options: You can choose none if you do not want a particular component to be installed.
After making the choices for all components, choose Apply to finish the installation of the release package.
(You can change config later)

4. $ sudo apt-get update

5. $ sudo apt-get install mysql-server (will upgrade if mysql already installed)

*** To see the packages you have installed from the MySQL APT repository,
$ dpkg -l | grep mysql | grep ii



//=== Replacing a Native Distribution of MySQL Using the MySQL APT Repository

You can replace a native distribution of MySQL installed
with a distribution from the MySQL APT repository in a few steps:

0. Backing Up Your Database
To avoid loss of data, always back up your database before trying to replace your MySQL installation using the MySQL APT repository. See Backup and Recovery for instructions.

1. Adding the MySQL APT Repository and Selecting a Release Series
Add the MySQL APT repository to your system's repository list and select the release series you want as above

2. Replacing the Native Distribution by an APT Update
By design, the MySQL APT repository replaces your native distribution of MySQL when you perform upgrades on the MySQL packages.

To perform the upgrades, just
$ sudo apt-get update
$ sudo apt-get install mysql-server

* during upgrade, choose "K" to keep the existent config?



mysqld and mysqld_safe


//=== https://dev.mysql.com/doc/refman/5.5/en/mysqld-safe.html

"""...
mysqld_safe is the recommended way to start a mysqld server on Unix.
mysqld_safe adds some safety features such as
restarting the server when an error occurs and
logging runtime information to an error log file

...
mysqld_safe tries to start an executable named mysqld.

...
Many of the options to mysqld_safe are the same as the options to mysqld.

...
mysqld_safe reads all options from the [mysqld], [server], and [mysqld_safe] sections in option files



..."""





mysql disable MyISAM


//=== http://stackoverflow.com/questions/19637477/how-to-disable-creation-of-myisam-table-in-mysql

* MyISAM engine is used by mysql system tables

* disable MyISAM storage engine is imposible
--> disable CREATE NEW MyISAM tables.

* set the default_storage_engine server variable to innodb



//=== https://mariadb.com/kb/en/mariadb/disabling-storage-engines/
"""...
mysqld --help
For all engines that can be disabled, you will see a corresponding --skip-* command line option. If the --skip-* option does not exist for a certain engine, this engine is mandatory and can not be disabled at all.

..."""

-->
$ mysqld --verbose --help
...
2015-05-06 11:42:25 1989 [Note] Binlog end
2015-05-06 11:42:25 1989 [Note] Shutting down plugin 'MyISAM'
2015-05-06 11:42:25 1989 [Note] Shutting down plugin 'CSV'




* check information_schema.tables for engines

mysql> SELECT engine,GROUP_CONCAT(DISTINCT TABLE_SCHEMA) Table_Schema_List,COUNT(*) FROM information_schema.tables GROUP BY engine;

+--------------------+--------------------------+----------+
| engine | Table_Schema_List | COUNT(*) |
+--------------------+--------------------------+----------+
| CSV | mysql | 2 |
| InnoDB | employees,fex | 7 |
| MEMORY | information_schema | 49 |
| MyISAM | mysql,information_schema | 32 |
| PERFORMANCE_SCHEMA | performance_schema | 17 |
+--------------------+--------------------------+----------+
5 rows in set (0.17 sec)






mysql table_cache


//=== https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size
"system variable"
* table_cache

Command-Line Format --table_cache=#
System Variable Name table_cache
Variable Scope Global
Dynamic Variable Yes
Permitted Values Type integer
Default 64
Min Value 1
Max Value 524288

"""...
The number of open tables for all threads.
Increasing this value increases the number of file descriptors that mysqld requires.
You can check whether you need to increase the table cache by
checking the Opened_tables status variable.
..."""


//=== "status variable"
* Opened_tables
--> https://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

mysql> SHOW GLOBAL STATUS;


* Open_tables : The number of tables that are open.

* Opened_tables : The number of tables that have been opened.
If Opened_tables is big, your table_cache value is probably too small.






mysql key_buffer_size


//=== https://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

the two most important variables for tuning mysql server performance are
* key_buffer_size
* table_cache.

"""...
If you have at least 256MB of memory and many tables and want maximum performance with a moderate number of clients, you should use something like this:

shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \
--sort_buffer_size=4M --read_buffer_size=1M &

..."""



//=== https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

To see the values that a server will use based on its "compiled-in defaults" and any option files that it reads
$ mysqld --verbose --help

To see the values that a server will use based on its compiled-in defaults, "ignoring the settings in any option files,"
$ mysqld --no-defaults --verbose --help




* key_buffer_size

Command-Line Format --key_buffer_size=#
System Variable Name key_buffer_size
Variable Scope Global
Dynamic Variable Yes
Permitted Values Type integer
Default 8388608
Min Value 8
Max Value 4294967295

Index blocks for MyISAM tables are buffered and are shared by all threads.
key_buffer_size is the size of the buffer used for index blocks.
The key buffer is also known as the key cache.

"""...
You can increase the value to get better index handling for all reads and multiple writes;
on a system ... using the MyISAM storage engine,
25% of the machine's total memory is an acceptable value for this variable.
..."""



--> http://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam

"""...
InnoDB has row-level locking, MyISAM can only do full table-level locking.
InnoDB has better crash recovery.
MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6 (Feb 2013).
InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not
..."""

* Another major difference between MyISAM and InnoDB storage engine is how caching is done:

MyISAM --> key cache(key_buffer_size).
It only caches index pages from .MYI files.

InnoDB --> InnoDB Buffer Pool(Innodb_buffer_pool_size).
It caches data and index pages from the accessed InnoDB tables.



//=== show variables for the running mysql server
$ mysqladmin variables -uroot -p
$ mysqladmin extended-status -uroot -p

or login mysql server first
$ mysql -uroot -p

then
mysql> SHOW VARIABLES;
mysql> SHOW STATUS;





mysql phpmyadmin export ERD to pdf


ERD : entity relationship diagram

//=== phpmyadmin 4.3.13
* first, select the target database on the left-side panel

* on the top horizontal menu bar --> right most "More" --> Designer
then
on the 2nd horizontal menu bar --> right most icon --> Export schema



//=== older version of phpmyadmin
* first, select the target database on the left-side panel
* on the top horizontal menu bar --> "Operations"
then
click the link "Edit or export relational schema" present at the page bottom



mysql query cache


* mysql query cache

//=== https://dev.mysql.com/doc/refman/5.1/en/query-cache.html

"""...
The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

...
The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

...
To verify that enabling the query cache is beneficial, test the operation of your MySQL server with the cache enabled and disabled

..."""


//====
http://www.percona.com/blog/2015/01/02/the-mysql-query-cache-how-it-works-and-workload-impacts-both-good-and-bad/

roughly speaking, query cache === (query, result) dictionary

The MySQL query cache is a global one shared among the sessions.
It caches the select 'query' along with the 'result' set


mysql> SHOW STATUS LIKE "qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031320 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |


when you trigger a select query,
if it is available in the cache, it fetches from there
otherwise
it considers the query as a new one and will go to the parser.



//=== MySQL query cache has its own downsides

* Anytime you have a “frequently updated table” means you’re probably not going to get any benefit from query cache.

* On circumstances where there is no chance of identical selects and in this case it is important to set the query_cache_size and query_cache_type variable to zero

* On a highly concurrent environment with chances of query cache "mutex", which may become the source of a bottleneck.
Setting the query_cache_type to zero will avoid the query cache mutex,

* Contention often makes query cache the bottleneck instead of help when you have "many CPU cores".

* Generally, query cache should be off unless proven useful for your workload.

* Not to keep the query cache relatively high value at 256 MB as the "Qcache invalidation" becomes costly.

mysql> show variables like 'query_cache_size';
+------------------+---------+

"""...
Qcache_lowmem_prunes is the status variable which indicates how many times MySQL had to clear/prune some data from the Qcache to make space for the outputs of other queries.
..."""







//=== https://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html

mysql> SHOW VARIABLES LIKE 'have_query_cache';
mysql> SET GLOBAL query_cache_size = 40000;
mysql> SHOW WARNINGS\G
mysql> SHOW VARIABLES LIKE 'query_cache_size';


//=== https://dev.mysql.com/doc/refman/5.1/en/query-cache-status-and-maintenance.html

mysql> SHOW STATUS LIKE 'Qcache%';

* Com_select === Qcache_inserts + Qcache_not_cached + queries with errors found during the column-privileges check

* total number of select queries === Com_select+ Qcache_hits+ queries with errors found by parser

* FLUSH QUERY CACHE defragment the query cache to better utilize its memory.
* After FLUSH QUERY CACHE, only a single free block remains.
[ but it does not remove any queries from the cache. ]

* RESET QUERY CACHE statement removes all query results from the query cache.
FLUSH TABLES statement also does this.


"""...
Qcache_lowmem_prunes status variable can help you tune the query cache size.
It counts the number of queries that have been removed from the cache to free up memory for caching new queries.
..."""





//=== https://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html

Query cache is "not" used for the following types:

* Queries that are a subquery of an outer query
* Queries executed within the body of a stored function, trigger, or event
* referring to user-defined functions (UDFs) or stored functions.
* referring to user variables or local stored program variables.
* referring to tables in the mysql or INFORMATION_SCHEMA system database
* referring to any partitioned tables.
* referring to TEMPORARY tables. ?

* SELECT ... LOCK IN SHARE MODE
[Statements within transactions that use SERIALIZABLE isolation level]
* SELECT ... FOR UPDATE
* SELECT ... INTO OUTFILE ...
* SELECT ... INTO DUMPFILE ...
* SELECT * FROM ... WHERE autoincrement_col IS NULL

* not using any tables.
* generating warnings.
* column-level privilege for any of the involved tables.

* containing any of the functions shown below:
[time/date, user/database/connection, lock, rand, encrypt, sleep, ...]
BENCHMARK(), CONNECTION_ID(),
CONVERT_TZ(), CURDATE(), CURRENT_DATE(),
CURRENT_TIME(), CURRENT_TIMESTAMP(), CURTIME(),
DATABASE(), ENCRYPT() with one parameter,
FOUND_ROWS(), GET_LOCK()
LAST_INSERT_ID(), LOAD_FILE(), MASTER_POS_WAIT()
NOW(), RAND(), RELEASE_LOCK(),
SLEEP(), SYSDATE(), UNIX_TIMESTAMP() with no parameters
USER(), UUID(), UUID_SHORT()



//=== https://dev.mysql.com/doc/refman/5.1/en/query-cache-in-select.html
?
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
?




mysql buffer pool (data cache)


//=== https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
* for mysql InnoDB storage engine
* buffer pool: a storage area(memory space) for caching data and indexes
* the larger the buffer pool, the more InnoDB acts like an "in-memory database"
* the buffer pool not only caches read data but also "write data" (caused by insert and update operations), so that disk writes can be grouped together for better performance.



* Under 64-bit systems with large memory sizes, you can split the buffer pool into multiple parts [multiple buffer pool instance] to minimize contention among concurrent operations.


//===

* LRU-variant algorithm [LRU: least recently used algorithm ]
* “midpoint insertion strategy” treats the list as two sublists:
* head sublist for new blocks;
* tail sublist for old blocks
* midpoint is the boundary where head sublist meets tail sublist




//=== system variables control the size of the buffer pool

innodb_buffer_pool_size

innodb_buffer_pool_instances
[ innodb_buffer_pool_size at least 1G ]
(Divides the buffer pool into a user-specified number of separate regions, each with its own LRU list and related data structures, to reduce contention during concurrent memory read and write operations.)


innodb_old_blocks_pct
[ The default value is 37 (that is, 3/8 of the pool). ]

innodb_old_blocks_time
?


//=== https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-buffer-pool-tables.html
mysql> SHOW ENGINE INNODB STATUS


mysql> show variables like '%INNODB_BUFFER_POOL%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 627048448 |
+-------------------------------------+----------------+



mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_BUFFER%';
+-----------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_BUFFER%) |
+-----------------------------------------------+
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_POOL_STATS |
+-----------------------------------------------+
3 rows in set (0.00 sec)



mysql> SELECT DISTINCT PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);

+-------------------+
| PAGE_TYPE |
+-------------------+
| SYSTEM |
| IBUF_BITMAP |
| INODE |
| IBUF_INDEX |
| INDEX |
| TRX_SYSTEM |
| UNDO_LOG |
| FILE_SPACE_HEADER |
| UNKNOWN |
+-------------------+
9 rows in set (0.20 sec)








mysql binlog_checksum


[Q] how to disable binlog_checksum on mysql master server?

http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html
*** binlog_checksum has been introduced since mysql 5.6.2 (not available on mysql 5.5)

mysql> show variables like '%checksum';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| binlog_checksum | NONE |
| master_verify_checksum | OFF |
| slave_sql_verify_checksum | ON |
+---------------------------+-------+
3 rows in set (0.01 sec)



***
on ubuntu --> /etc/mysql/my.cnf
on RDS --> DB parameter group


//=== http://mysqlmusings.blogspot.com/2011/04/replication-event-checksum.html

//=== binlog_checksum
binlog_checksum == NONE or CRC32 (CRC-32 checksum === ISO-3309)


"""...
Setting binlog_checksum to NONE means that no checksum is generated,
while setting it to CRC32 means that an ISO-3309 CRC-32 checksum is added to each binary log event.
..."""




//=== master_verify_checksum
"""...
Setting this flag can be useful to verify that the event really written to the binary log is uncorrupted, but it is typically not needed in a replication setting since the slave should verify the event on reception.
..."""

master> SET GLOBAL MASTER_VERIFY_CHECKSUM=1;

//===
slave_sql_verify_checksum
"""...
indicates that the SQL thread should verify the checksum when reading it from the relay log on the slave. Note that this means that the I/O thread writes a checksum to the event written to the relay log, regardless of whether it received an event with a checksum or not.
..."""



//=== SHOW BINLOG EVENTS FROM pos;

"""...
master> SHOW BINLOG EVENTS FROM 261;
+-------------------+-----+------------+-----------+-------------+-----------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+------------+-----------+-------------+-----------------------------------------------------------+
| master-bin.000001 | 261 | Query | 1 | 333 | BEGIN |
| master-bin.000001 | 333 | Intvar | 1 | 365 | INSERT_ID=1 |
..."""


//=== mysqlbinlog --verify-binlog-checksum
$ mysqlbinlog --verify-binlog-checksum master-bin.000001