Avatar
# for historical purposes:
[acool@localhost ~]$ date
Thu Jan 24 18:44:23 PST 2019
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ cat /etc/redhat-release
Fedora release 28 (Twenty Eight)
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker --version
Docker version 1.13.1, build 1556cce-unsupported
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker network create mysql-cluster
72af69d434e8cefa031555d2ba6039c2260aa73fe27df380b9454e3790c89337
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker network ls
NETWORK ID NAME DRIVER SCOPE
5dfcb11eac94 bridge bridge local
c146b3afd86e host host local
72af69d434e8 mysql-cluster bridge local
1299a7349094 none null local
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ tree mysql-fun
mysql-fun
├── master
│ ├── config
│ │ └── mysqld.cnf
│ └── data
└── slave
├── config
│ └── mysqld.cnf
└── data
6 directories, 2 files
[acool@localhost ~]$
[acool@localhost ~]$ cat mysql-fun/master/config/mysqld.cnf
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin-master
[acool@localhost ~]$
[acool@localhost ~]$ cat mysql-fun/slave/config/mysqld.cnf
[mysqld]
server_id = 2
relay_log = /var/log/mysql/mysql-relay-slave-bin
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ getenforce
Permissive
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
docker.io/mysql 5.7 5cd9bd4c8a5e 3 days ago 372 MB
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker run -v /home/acool/mysql-fun/slave/config:/etc/mysql/conf.d -v /home/acool/mysql-fun/slave/data/:/var/lib/mysql/:rw --detach --network mysql-cluster --name=slave-mysql --env="MYSQL_ROOT_PASSWORD=temp123" mysql:5.7
599f302193cda964ada60d42e90324705c8d5b578c805c57f395958b97ed6702
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker run -v /home/acool/mysql-fun/master/config:/etc/mysql/conf.d -v /home/acool/mysql-fun/master/data/:/var/lib/mysql/:rw --detach --network mysql-cluster --name=master-mysql --env="MYSQL_ROOT_PASSWORD=temp123" mysql:5.7
32d0c2ecc7e6d17ae99ebabd6c75f0566846d60451bfb953b747b767dda92129
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
32d0c2ecc7e6 mysql:5.7 "docker-entrypoint..." 15 seconds ago Up 14 seconds 3306/tcp, 33060/tcp master-mysql
599f302193cd mysql:5.7 "docker-entrypoint..." 46 seconds ago Up 44 seconds 3306/tcp, 33060/tcp slave-mysql
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker inspect master-mysql |grep \"IPAddress\"
"IPAddress": "",
"IPAddress": "172.18.0.3",
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker inspect slave-mysql |grep \"IPAddress\"
"IPAddress": "",
"IPAddress": "172.18.0.2",
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ mysql -h 172.18.0.3 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> CREATE USER 'replication_user'@'%' IDENTIFIED BY 'ru_temp123';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW MASTER STATUS;
+-------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000003 | 763 | | | |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ mysql -h 172.18.0.2 -p -u root
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST = '172.18.0.3', MASTER_USER = 'replication_user', MASTER_PASSWORD = 'ru_temp123', MASTER_LOG_FILE = 'mysql-bin-master.000003', MASTER_LOG_POS = 763;
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.0.3
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-master.000003
Read_Master_Log_Pos: 763
Relay_Log_File: mysql-relay-slave-bin.000002
Relay_Log_Pos: 327
Relay_Master_Log_File: mysql-bin-master.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 763
Relay_Log_Space: 540
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 60c1469e-2049-11e9-89ca-0242ac120003
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[acool@localhost ~]$
[acool@localhost ~]$ # create new database in master
[acool@localhost ~]$ mysql -h 172.18.0.3 -p -u root
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> CREATE DATABASE users;
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| users |
+--------------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[acool@localhost ~]$ # confirm database creation was replicated to slave
[acool@localhost ~]$
[acool@localhost ~]$ mysql -h 172.18.0.2 -p -u root
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| users |
+--------------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[acool@localhost ~]$
[acool@localhost ~]$ # Exercise finished! SQL statements are being replicated from master to slave :)
[acool@localhost ~]$
[ view entry ] ( 749 views ) | print article
1.- The column names are in the first line, get them:
[aesteban@localhost BizEquityData]$ head -1 bizDataFile.tsv | tr '\t' '\n'
id
name
dbaname
address
city
state
postcode
latitude
longitude
phone
faxphone
contact
firstname
lastname
yearestablished
businessstatus
webaddress
corpemployees
localemployees
corpamount
localamount
localamount
stockexchangecode
stocktickersymbol
ceoname
cioname
cfoname
name
code
1a..- find out total lines in file:
[aesteban@localhost BizEquityData]$ cat bizDataFile.tsv | wc -l
1785338
2.- Create a table, rename any duplicate field names:
CREATE TABLE `bizDataTable` (
id varchar(255),
name varchar(255),
dbaname varchar(255),
address varchar(255),
city varchar(255),
state varchar(255),
postcode varchar(255),
latitude varchar(255),
longitude varchar(255),
phone varchar(255),
faxphone varchar(255),
contact varchar(255),
firstname varchar(255),
lastname varchar(255),
email varchar(255),
yearestablished varchar(255),
businessstatus varchar(255),
webaddress varchar(255),
corpemployees varchar(255),
localemployees varchar(255),
corpamount varchar(255),
localamount varchar(255),
localamount2 varchar(255),
stockexchangecode varchar(255),
stocktickersymbol varchar(255),
ceoname varchar(255),
cioname varchar(255),
cfoname varchar(255),
name2 varchar(255),
code varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.- Connect to the server and load the local file:
[aesteban@localhost BizEquityData]$ mysql -u USERNAME -h dev.example.com -p --local-infile bizDataDB
MySQL [bizDataDB]> load data local infile '/home/aesteban/Documents/BizEquityData/bizDataFile.tsv' into table bizDataTable fields terminated by '\t' lines terminated by '\n' ignore 1 lines;
MySQL [bizData]> select count(*) from bizDataTable;
+----------+
| count(*) |
+----------+
| 1785337 |
+----------+
1 row in set (0.00 sec)
Enjoy!!
BONUS: Dump select statement locally as a TSV file:
[aesteban@localhost FilteredReports]$ mysql -u USERNAME -h dev.example.com -p bizDataDB -e "select * from bizDataTable where email != 'NULL'" > bizData_email.tsv
If it's a CSV file, some fields may contain a comma, try something like the following:
MySQL [jupiter]> load data local infile 'e360Data.csv' into table e360MasterOnline fields optionally enclosed by '"' terminated by ',' lines terminated by '\n' ignore 1 lines;
[ view entry ] ( 1404 views ) | print article
Ranking field for result sets.
MySQL does not have any system function like SQL Server’s row_number () to generate the row number for each row. However, it can be generated using the variable in the SELECT statement.
The following table has five rows.
CREATE TABLE mysql_testing(db_names VARCHAR(100));
INSERT INTO mysql_testing
SELECT 'SQL Server' UNION ALL
SELECT 'MySQL' UNION ALL
SELECT 'Oracle' UNION ALL
SELECT 'MongoDB' UNION ALL
SELECT 'PostGreSQL';
Now you can generate the row number using a variable in two methods
Method 1 : Set a variable and use it in a SELECT statement
SET @row_number:=0;
SELECT @row_number:=@row_number+1 AS row_number,db_names FROM mysql_testing
ORDER BY db_names;
Method 2 : Use a variable as a table and cross join it with the source table
SELECT @row_number:=@row_number+1 AS rank,db_names FROM mysql_testing,
(SELECT @row_number:=0) AS t
ORDER BY db_names;
Both the above methods return the following result
row_number db_names
1 MongoDB
2 MySQL
3 Oracle
4 PostGreSQL
5 SQL Server
//source (as of 6-2-2014)
http://blog.sqlauthority.com/2014/03/08 ... -variable/
//my 2 cents -ac
select @rn := @rn+1 AS RANK,t1.* from (
select * from Franchises limit 500
) t1, (SELECT @rn:=0) t2;";
[ view entry ] ( 1512 views ) | print article
//Server and client "Common Name" in certificates must be different than CA's :
http://stackoverflow.com/questions/2045 ... 4#23599624
1.- generate CA key and certificate(2 commands create 2 files)
openssl genrsa 2048 >ca.key //creates ca.key
openssl req -new -x509 -nodes -days 3600 -key 'ca.key' > 'ca.crt' //creates certificate
2.- generate server key and signed certificate(2 commands create 3 files)
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout 'server.key' > server.csr' //create key and csr
openssl x509 -req -in 'server.csr' -days 3600 -CA 'ca.crt' -CAkey 'ca.key' -set_serial 01 > 'server.crt' //creates certificate
3.- generate client key and certificate (2 commands create 3 files)
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout 'client.key' > 'client.csr' //creates key and csr
openssl x509 -req -in 'client.csr' -days 3600 -CA 'ca.crt' -CAkey 'ca.key' -set_serial 01 > 'client.crt'
4.- create SSL user:
GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%' IDENTIFIED BY 'secret-passwd' REQUIRE SSL;
5.- update my.cnf
[mysqld]
ssl-ca = "ca.crt"
ssl-cert = "server.crt"
ssl-key = "server.key"
[client]
ssl-ca=ca.crt
ssl-cert=client.crt
ssl-key=client.key
//MySQL workbench, use: ca.key, client.crt and client.key without password:
openssl rsa -in client.key -out client-nopasswd.key
[ view entry ] ( 7225 views ) | print article
//dump a database
mysqldump -u UserName -p --host=dev.barney.com db_name > db_name_backup.sql
//compressed dump
mysqldump -u UserName -p --host=dev.barney.com db_name | gzip > db_name_backup.sql
//restore database
mysql -u UserName -p target_db_name < db_name_backup.sql
//dump and restore
mysqldump -u UserName -p --host=dev.barney.com db_name | mysql -u UserName -p target_db_name
[ view entry ] ( 1251 views ) | print article
MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake.
The syntax of REPLACE is REPLACE(text_string, from_string, to_string)
MySQL reference describes REPLACE as function that returns the string text_string with all occurrences of the string from_string replaced by the string to_string, where matching is case-sensitive when searching for from_string. text_string can be retrieved from the a field in the database table too. Most SQL command can be REPLACE() function, especially SELECT and UPDATE manipulation statement.
For example:
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);
update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’)
The above statement will replace all instances of ‘Old Company’ to ‘New Company’ in the field of company_name of client_table table.
Another example:
SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’);
Above statement will return ‘WwWwWw.mysql.com’ as result.
http://www.mydigitallife.info/how-to-fi ... using-sql/
[ view entry ] ( 1811 views ) | print article
Suppose you want to drop an existing column i from above MySQL table then you will use DROP clause along with ALTER command as follows:
mysql> ALTER TABLE testalter_tbl DROP i;
A DROP will not work if the column is the only one left in the table.
To add a column, use ADD and specify the column definition. The following statement restores the i column to testalter_tbl:
mysql> ALTER TABLE testalter_tbl ADD i INT;
After issuing this statement, testalter will contain the same two columns that it had when you first created the table, but will not have quite the same structure. That's because new columns are added to the end of the table by default. So even though i originally was the first column in mytbl, now it is the last one.
mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
To indicate that you want a column at a specific position within the table, either use FIRST to make it the first column or AFTER col_name to indicate that the new column should be placed after col_name. Try the following ALTER TABLE statements, using SHOW COLUMNS after each one to see what effect each one has:
ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT FIRST; ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT AFTER c;
The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.
:)
http://www.tutorialspoint.com/mysql/mys ... ommand.htm
[ view entry ] ( 6964 views ) | print article
SELECT
CAST(CASE eoy
WHEN 0 THEN 'No Voted'
ELSE category/* Keep Original Value*/
END as CHAR) AS Result
FROM `vote_email`
//see all
SELECT eoy FROM vote_email
[ view entry ] ( 1659 views ) | print article
The CASE expression evaluates to a value, i.e. it is used to evaluate to one of a set of results, based on some condition.
Example:
SELECT CASE
WHEN type = 1 THEN 'foo'
WHEN type = 2 THEN 'bar'
ELSE 'baz'
END AS name_for_numeric_type
FROM sometable`
The CASE statement executes one of a set of statements, based on some condition.
Example:
CASE
WHEN action = 'update' THEN
UPDATE sometable SET column = value WHERE condition;
WHEN action = 'create' THEN
INSERT INTO sometable (column) VALUES (value);
END CASE
You see how they are similar, but the statement does not evaluate to a value and can be used on its own, while the expression needs to be a part of an expression, e.g. a query or an assignment. You cannot use the statement in a query, since a query cannot contain statements, only expressions that need to evaluate to something (the query itself is a statement, in a way), e.g. SELECT CASE WHEN condition THEN UPDATE table SET something; END CASE makes no sense.
http://stackoverflow.com/questions/1243 ... -statement
[ view entry ] ( 1404 views ) | print article
// view all users
SELECT user,host FROM mysql.user;
//drop a user
DROP USER 'dev-webuser'@'localhost';
//create a user and assign password
GRANT ALL PRIVILEGES ON db_name.* TO 'webuser'@'localhost' IDENTIFIED BY 'secret123'
// disply logged in grants
SHOW GRANTS;
//
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
//
GRANT SELECT,INSERT,UPDATE,DELETE ON my_db.* TO 'michael'@'localhost';
//
GRANT ALL ON my_db.* TO 'michael'@'localhost';
//
GRANT ALL ON my_db.* TO 'my_user'@localhost IDENTIFIED BY 'my_pass';
//update user's password:
SET PASSWORD FOR 'webuser'@'localhost' = PASSWORD('dev123');
// MySQL 8 :
root@5f288b2166ba:/# mysql -p
...
mysql>
mysql> CREATE USER 'coolUser'@'%' IDENTIFIED WITH mysql_native_password BY 'YYYYYY';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> grant all on *.* to 'coolUser'@'%';
Query OK, 0 rows affected (0.01 sec)
#Starting with MySQL 8 you no longer can (implicitly) create a user using the GRANT command. Use CREATE USER instead, followed by the GRANT statement:
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
[ view entry ] ( 1729 views ) | print article
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | Next> Last>>