Version 10, 2021-01-27
https://github.com/LinuxAtDuke/Intro-to-MySQL/
Instructor
Andy Ingham (andy.ingham AT duke.edu)
Table of Contents
- Lab 0: Creating a Personal Linux VM
- Unit 1: Access control / User management
- Lab 1: Initial user lockdown
- Unit 2: Databases, schema
- Unit 3: Adding/modifying tables and indexes
- Lab 2/3: Working with databases and tables
- Unit 4: Populating database with data
- Lab 4: Adding data to your database
- Unit 5: Writing queries to retrieve data
- Lab 5: Practice with INSERT, UPDATE, DELETE, and SELECT (with JOIN!)
- Unit 6: Useful ancillary information
- A brief tangent to discuss architecture == https://github.com/LinuxAtDuke/Intro-to-MySQL/blob/master/client-server-architecture.pdf
- Using a web browser, go to https://vcm.duke.edu/
- Login using your Duke NetId.
- Select "Reserve a VM" (near the middle of the page)
- On the next screen, select the "Lamp Stack" link from the list under "Linux Apps"
- If you encounter a pop-up window about SSH keys (which displays if you do not have them set up for your netID), you may need to select the less secure option until you've done that step (which is outside the scope of this class).
- After agreeing to the Terms of Use, the VCM web page will display the name of your VM along with available usernames. You must first connect to the University VPN (if not "on campus"), THEN initiate an ssh session as the Admin User (vcm) -- do this via the "Terminal" app on your Mac or via "PuTTY" (available at https://www.chiark.greenend.org.uk/~sgtatham/putty/ ) on your Windows machine.
Example (after establishing a University VPN session, if off campus): ssh [email protected] [Answering "yes" to "Are you sure you want to continue connecting (yes/no)?" and then entering the password behind "View Password" when prompted]
-
how access is controlled (https://dev.mysql.com/doc/refman/8.0/en/default-privileges.html )
shell>> sudo -i
shell>> mysql -u root (NO INITIAL PASSWORD EXISTS)
[
NOTE that MySQL may not be installed. If an error is encountered here, install mysql with:shell>> apt install -y mysql-server
shell>> mysql -u root (NO INITIAL PASSWORD EXISTS)
]mysql>> SELECT Host, User, plugin, authentication_string from mysql.user where User='root';
Host User plugin authentication_string localhost root auth_socket -
general structure of the DBMS
mysql>> status
mysql>> show status;
mysql>> show databases;
mysql>> use DATABASE; (e.g. use mysql;)
mysql>> show tables;
TAB COMPLETION
COMMAND HISTORY
-
Login to MySQL as 'root', change that user's password, and remove unnecessary authorizations
shell>> sudo -i
shell>> mysql -u root (NO INITIAL PASSWORD EXISTS)
mysql>> update mysql.user set plugin='mysql_native_password' where user='root' and host='localhost';
mysql>> flush privileges;
mysql>> SET PASSWORD FOR 'root'@'localhost' = 'SUPER_GREAT_PASSWORD_HERE';
mysql>> SELECT Host, User, plugin, authentication_string from mysql.user where User='root';
[take note of how this output looks different than it did before]mysql>> SELECT Host, User, plugin, authentication_string from mysql.user;
-
Removing or creating databases is very simple
mysql>> CREATE DATABASE colab_class;
mysql>> show databases;
mysql>> DROP DATABASE colab_class;
mysql>> show databases;
-
Schema development is best done via an ER diagram and/or a whiteboard - consider these:
- what are the entities? (the "things" or "concepts" that form the basis of our data)
- what relationships do they have with one another?
- what are the important attributes of the entities?
- what are the data types and metadata (is NULL allowed? are there default values?) for those attributes?
- what will determine uniqueness in each table? (will the primary key be simple or compound?)
- what queries are users likely to run? (this will inform index creation)
- what indexes are needed? (to supplement the primary key)
-
Some (albeit simple and somewhat silly) examples:
- https://www.edrawsoft.com/templates/pdf/pet-store-er-diagram.pdf
- https://github.com/LinuxAtDuke/Intro-to-MySQL/blob/master/pet-store-example-schemaOwner.pdf
- https://github.com/LinuxAtDuke/Intro-to-MySQL/blob/master/pet-store-example-schemaPet.pdf
- https://github.com/LinuxAtDuke/Intro-to-MySQL/blob/master/pet-store-example-schemaPetClinic.pdf
- ESPECIALLY PROBLEMATIC: https://github.com/LinuxAtDuke/Intro-to-MySQL/blob/master/pet-store-example-schemaTreatments.pdf
- IN LIGHT OF THE ABOVE: https://dzone.com/articles/how-to-handle-a-many-to-many-relationship-in-datab
- https://github.com/LinuxAtDuke/Intro-to-MySQL/blob/master/pet-store-example-schemaPetStore.pdf
- https://www.safaribooksonline.com/library/view/learning-mysql/0596008643/ch04s04.html
- https://www.edrawsoft.com/templates/pdf/pet-store-er-diagram.pdf
-
A tutorial to help with schema development:
-
Fine-tuning of schema...
- referential integrity - data types consistent across linking fields (foreign keys)
- data types (https://dev.mysql.com/doc/refman/8.0/en/data-types.html) should be as prescriptive and compact as possible
- index creation should be done where needed, but not elsewhere
- index creation is always faster BEFORE data is loaded into the table
- verify that data is "reasonably" normalized (e.g., data generally de-duplicated)
-
Some examples
mysql>> describe LCL_genotypes;
Field Type Null Key Default Extra IID varchar(16) NO PRI NULL SNPpos varchar(512) NO PRI NULL rsID varchar(256) NO MUL NULL genotype varchar(512) NO NULL mysql>> describe phenotypes;
Field Type Null Key Default Extra LCL_ID varchar(16) NO PRI NULL phenotype varchar(128) NO PRI NULL phenotypic_value1 decimal(20,10) YES NULL phenotypic_value2 decimal(20,10) YES NULL phenotypic_value3 decimal(20,10) YES NULL phenotypic_mean decimal(20,10) YES NULL mysql>> describe snp;
Field Type Null Key Default Extra rsID varchar(256) NO PRI NULL Chromosome tinyint(3) unsigned NO NULL Position int(10) unsigned NO NULL Allele1 varchar(128) NO NULL Allele2 varchar(128) NO NULL DistanceToNearGene varchar(32) NO NULL Gene varchar(32) NO NULL SNPtype varchar(64) NO NULL
-
Looking at the syntax for creating the above tables...
CREATE TABLE `LCL_genotypes` ( `IID` varchar(16) NOT NULL, `SNPpos` varchar(512) NOT NULL, `rsID` varchar(256) NOT NULL, `genotype` varchar(512) NOT NULL, PRIMARY KEY (`IID`,`SNPpos`), KEY `idx_rsID` (`rsID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `phenotypes` ( `lcl_ID` varchar(16) NOT NULL, `phenotype` varchar(128) NOT NULL, `phenotypic_value1` decimal(20,10) DEFAULT NULL, `phenotypic_value2` decimal(20,10) DEFAULT NULL, `phenotypic_value3` decimal(20,10) DEFAULT NULL, `phenotypic_mean` decimal(20,10) DEFAULT NULL, PRIMARY KEY (`lcl_ID`,`phenotype`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `snp` ( `rsID` varchar(256) NOT NULL, `Chromosome` bigint(20) unsigned NOT NULL, `Position` int(10) unsigned NOT NULL, `Allele1` varchar(1024) NOT NULL, `Allele2` varchar(1024) NOT NULL, `DistanceToNearGene` varchar(1024) NOT NULL, `Gene` varchar(256) NOT NULL, `SNPtype` varchar(64) NOT NULL, PRIMARY KEY (`rsID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -
How was the "idx_rsID" index actually created?
mysql>> CREATE INDEX idx_rsID ON LCL_genotypes(rsID);
Query OK, 358244487 rows affected (2 hours 33 min 15.53 sec) Records: 358244487 Deleted: 0 Skipped: 0 Warnings: 0mysql>> SHOW INDEX from LCL_genotypes;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | LCL_genotypes | 0 | PRIMARY | 1 | IID | A | 5 | NULL | NULL | | BTREE | | | | LCL_genotypes | 0 | PRIMARY | 2 | SNPpos | A | 5 | NULL | NULL | | BTREE | | | | LCL_genotypes | 1 | idx_rsID | 1 | rsID | A | 2 | NULL | NULL | | BTREE | | | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) -
A brief tangent to discuss backups! (via 'mysqldump')
shell>> mysqldump -p --no-data colab_class > COLAB_WITHOUT_DATA.sql
-
Create new database and populate it...
mysql>> CREATE DATABASE colab_class;
mysql>> show databases;
mysql>> exit
-
grab the class files from the github repository
shell>> git clone https://github.com/LinuxAtDuke/Intro-to-MySQL.git
-
load the file into your MySQL instance
shell>> mysql -u root -p colab_class < /root/Intro-to-MySQL/COLAB_WITHOUT_DATA.sql
-
now check out the results of the import
shell>> mysql -u root -p colab_class;
mysql>> show tables;
mysql>> DESCRIBE LCL_genotypes;
-
now manually modify the table schema
mysql>> ALTER TABLE LCL_genotypes MODIFY genotype VARCHAR(2048) NOT NULL;
mysql>> ALTER TABLE LCL_genotypes MODIFY SNPpos VARCHAR(767) NOT NULL;
mysql>> DESCRIBE LCL_genotypes;
[take note of how this output looks different than it did before]mysql>> DESCRIBE gwas_results;
mysql>> ALTER TABLE gwas_results MODIFY study_population VARCHAR(16) NOT NULL;
mysql>> DESCRIBE gwas_results;
[take note of how this output looks different than it did before]
-
Data can be added either record by record...
-
mysql>> INSERT INTO tbl_name () VALUES();
- E.g., mysql>> INSERT INTO LCL_genotypes (IID,SNPpos,rsID,Genotype) VALUES('HG02463','10:60523:T:G','rs112920234','TT');
-
mysql>> INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
- E.g., mysql>> INSERT INTO LCL_genotypes (IID,SNPpos,rsID,Genotype) VALUES('HG02466','10:60523:T:G','rs112920234','TT'),('HG02563','10:60523:T:G','rs112920234','TT'),('HG02567','10:60523:T:G','rs112920234','00');
-
mysql>> INSERT INTO tbl_name SET col_name=expr, col_name=expr, ...
- E.g., mysql>> INSERT INTO phenotypes SET LCL_ID='HG02461', phenotype='Cells_ml_after_3_days', phenotypic_value1='878000', phenotypic_value2='732000', phenotypic_value3='805000', phenotypic_mean='805000';
-
-
Or in bulk (from an INFILE)
- mysql>> LOAD DATA LOCAL INFILE '/root/Intro-to-MySQL/snp-data.infile' INTO TABLE snp FIELDS TERMINATED BY '\t';
-
WATCH OUT FOR WARNINGS! [NOTE: As of MySQL version 5.7, THIS COMMAND RETURNS A FATAL ERROR AS OPPOSED TO A WARNING] E.g., mysql>> INSERT INTO LCL_genotypes (IID,SNPpos,rsID,Genotype) VALUES('HG024638392382903957','10:60523:T:G','rs112920234','TT');
Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1265 | Data truncated for column 'IID' at row 1 | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from LCL_genotypes; +------------------+--------------+-------------+----------+ | IID | SNPpos | rsID | genotype | +------------------+--------------+-------------+----------+ | HG02463 | 10:60523:T:G | rs112920234 | TT | | HG02463839238290 | 10:60523:T:G | rs112920234 | TT | | HG02466 | 10:60523:T:G | rs112920234 | TT | | HG02563 | 10:60523:T:G | rs112920234 | TT | | HG02567 | 10:60523:T:G | rs112920234 | 00 | +------------------+--------------+-------------+----------+ 5 rows in set (0.00 sec) -
Also possible (obviously) to change records that already exist (either one at a time or in bunches)...
mysql> UPDATE tbl_name SET col_name=expr, col_name=expr, ... WHERE where_condition E.g., UPDATE LCL_genotypes SET IID='HG0246383' WHERE IID='HG02463839238290'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from LCL_genotypes; +-----------+--------------+-------------+----------+ | IID | SNPpos | rsID | genotype | +-----------+--------------+-------------+----------+ | HG02463 | 10:60523:T:G | rs112920234 | TT | | HG0246383 | 10:60523:T:G | rs112920234 | TT | | HG02466 | 10:60523:T:G | rs112920234 | TT | | HG02563 | 10:60523:T:G | rs112920234 | TT | | HG02567 | 10:60523:T:G | rs112920234 | 00 | +-----------+--------------+-------------+----------+ 5 rows in set (0.00 sec) -
Or to remove records (either one at a time or in bunches). [First lets look at the table contents BEFOREHAND]
mysql> select * from phenotypes; +---------+-----------------------+--------------------+--------------------+-------------------+--------------------+ | LCL_ID | phenotype | phenotypic_value1 | phenotypic_value2 | phenotypic_value3 | phenotypic_mean | +---------+-----------------------+--------------------+--------------------+-------------------+--------------------+ | HG02461 | Cells_ml_after_3_days | 878000.0000000000 | 732000.0000000000 | 805000.0000000000 | 805000.0000000000 | | HG02462 | Cells_ml_after_3_days | 742000.0000000000 | 453000.0000000000 | 348000.0000000000 | 514333.3000000000 | | HG02463 | Cells_ml_after_3_days | 1200000.0000000000 | 1140000.0000000000 | 960000.0000000000 | 1100000.0000000000 | +---------+-----------------------+--------------------+--------------------+-------------------+--------------------+ 3 rows in set (0.00 sec) -
Now remove...
-
mysql>> DELETE FROM tbl_name WHERE where_condition; MAKE SURE YOU SUPPLY A WHERE CLAUSE UNLESS YOU WANT TO DELETE ALL ROWS!
- E.g., mysql> DELETE FROM phenotypes WHERE LCL_ID='HG02463';
Query OK, 1 row affected (0.01 sec)
-
-
How does it look now?
mysql> select * from phenotypes; +---------+-----------------------+-------------------+-------------------+-------------------+-------------------+ | LCL_ID | phenotype | phenotypic_value1 | phenotypic_value2 | phenotypic_value3 | phenotypic_mean | +---------+-----------------------+-------------------+-------------------+-------------------+-------------------+ | HG02461 | Cells_ml_after_3_days | 878000.0000000000 | 732000.0000000000 | 805000.0000000000 | 805000.0000000000 | | HG02462 | Cells_ml_after_3_days | 742000.0000000000 | 453000.0000000000 | 348000.0000000000 | 514333.3000000000 | +---------+-----------------------+-------------------+-------------------+-------------------+-------------------+ 2 rows in set (0.00 sec)
-
First, make the necessary configuration change to allow this functionality:
mysql>> set global local_infile=true;
mysql>> show global variables like 'local_infile';
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | ON | +---------------+-------+ 1 row in set (0.00 sec) -
Re-launch mysql client with ability enabled from the client:
mysql>> exit
shell>> mysql --local_infile=1 -u root -p colab_class;
-
Quickly add data to three tables...
mysql>> LOAD DATA LOCAL INFILE '/root/Intro-to-MySQL/snp-data.infile' INTO TABLE snp FIELDS TERMINATED BY '\t';
mysql>> LOAD DATA LOCAL INFILE '/root/Intro-to-MySQL/lcl_genotypes-data.infile' INTO TABLE LCL_genotypes FIELDS TERMINATED BY '\t';
mysql>> show warnings;
mysql>> LOAD DATA LOCAL INFILE '/root/Intro-to-MySQL/phenotypes-data.infile' INTO TABLE phenotypes FIELDS TERMINATED BY '\t';
-
Simplest queries
mysql> select * from LCL_genotypes; +------------------+--------------+-------------+----------+ | IID | SNPpos | rsID | genotype | +------------------+--------------+-------------+----------+ | HG02463 | 10:60523:T:G | rs112920234 | TT | | HG02463839238290 | 10:60523:T:G | rs112920234 | TT | | HG02466 | 10:60523:T:G | rs112920234 | TT | | HG02563 | 10:60523:T:G | rs112920234 | TT | | HG02567 | 10:60523:T:G | rs112920234 | 00 | +------------------+--------------+-------------+----------+ 5 rows in set (0.00 sec) mysql> SELECT IID,rsID from LCL_genotypes WHERE genotype = 'TT'; +------------------+-------------+ | IID | rsID | +------------------+-------------+ | HG02463 | rs112920234 | | HG02463839238290 | rs112920234 | | HG02466 | rs112920234 | | HG02563 | rs112920234 | +------------------+-------------+ 4 rows in set (0.00 sec) mysql> SELECT COUNT(*) from snp; +----------+ | COUNT(*) | +----------+ | 5 | +----------+ 1 row in set (0.04 sec) mysql> select * from snp; +-------------+------------+----------+---------+---------+----------------------+------------+------------+ | rsID | Chromosome | Position | Allele1 | Allele2 | DistanceToNearGene | Gene | SNPtype | +-------------+------------+----------+---------+---------+----------------------+------------+------------+ | rs112920234 | 10 | 60523 | G | T | dist=NONE;dist=32305 | NONE,TUBB8 | intergenic | | rs147855157 | 10 | 61372 | CA | C | . | . | . | | rs536439816 | 10 | 61386 | A | G | dist=NONE;dist=31442 | NONE,TUBB8 | intergenic | | rs536478188 | 10 | 60803 | G | T | dist=NONE;dist=32025 | NONE,TUBB8 | intergenic | | rs569167217 | 10 | 60684 | C | A | dist=NONE;dist=32144 | NONE,TUBB8 | intergenic | +-------------+------------+----------+---------+---------+----------------------+------------+------------+ 5 rows in set (0.00 sec) -
Slightly more complex queries
mysql> select * from LCL_genotypes WHERE IID LIKE 'HG0246%'; +------------------+--------------+-------------+----------+ | IID | SNPpos | rsID | genotype | +------------------+--------------+-------------+----------+ | HG02463 | 10:60523:T:G | rs112920234 | TT | | HG02463839238290 | 10:60523:T:G | rs112920234 | TT | | HG02466 | 10:60523:T:G | rs112920234 | TT | +------------------+--------------+-------------+----------+ 3 rows in set (0.00 sec) -
"JOIN" GUIDANCE: https://stackoverflow.com/questions/6294778/mysql-quick-breakdown-of-the-types-of-joins
-
MORE "JOIN" GUIDANCE: https://www.javatpoint.com/mysql-join
-
(The default "JOIN" in MySQL is an "INNER JOIN")
mysql> SELECT * FROM LCL_genotypes JOIN snp ON LCL_genotypes.rsID = snp.rsID; +------------------+--------------+-------------+----------+-------------+------------+----------+---------+---------+----------------------+------------+------------+ | IID | SNPpos | rsID | genotype | rsID | Chromosome | Position | Allele1 | Allele2 | DistanceToNearGene | Gene | SNPtype | +------------------+--------------+-------------+----------+-------------+------------+----------+---------+---------+----------------------+------------+------------+ | HG02463 | 10:60523:T:G | rs112920234 | TT | rs112920234 | 10 | 60523 | G | T | dist=NONE;dist=32305 | NONE,TUBB8 | intergenic | | HG02463839238290 | 10:60523:T:G | rs112920234 | TT | rs112920234 | 10 | 60523 | G | T | dist=NONE;dist=32305 | NONE,TUBB8 | intergenic | | HG02466 | 10:60523:T:G | rs112920234 | TT | rs112920234 | 10 | 60523 | G | T | dist=NONE;dist=32305 | NONE,TUBB8 | intergenic | | HG02563 | 10:60523:T:G | rs112920234 | TT | rs112920234 | 10 | 60523 | G | T | dist=NONE;dist=32305 | NONE,TUBB8 | intergenic | | HG02567 | 10:60523:T:G | rs112920234 | 00 | rs112920234 | 10 | 60523 | G | T | dist=NONE;dist=32305 | NONE,TUBB8 | intergenic | +------------------+--------------+-------------+----------+-------------+------------+----------+---------+---------+----------------------+------------+------------+ 5 rows in set (0.00 sec) mysql> SELECT IID,Position,Gene FROM LCL_genotypes JOIN snp ON LCL_genotypes.rsID = snp.rsID; +------------------+----------+------------+ | IID | Position | Gene | +------------------+----------+------------+ | HG02463 | 60523 | NONE,TUBB8 | | HG02463839238290 | 60523 | NONE,TUBB8 | | HG02466 | 60523 | NONE,TUBB8 | | HG02563 | 60523 | NONE,TUBB8 | | HG02567 | 60523 | NONE,TUBB8 | +------------------+----------+------------+ 5 rows in set (0.00 sec) mysql> SELECT IID,Position,Gene FROM LCL_genotypes JOIN snp ON LCL_genotypes.rsID = snp.rsID where LCL_genotypes.rsID = 'rs536478188'; Empty set (0.00 sec) mysql> SELECT IID,Position,Gene FROM LCL_genotypes JOIN snp ON LCL_genotypes.rsID = snp.rsID where snp.rsID = 'rs536478188'; Empty set (0.00 sec) mysql> SELECT IID,Position,Gene FROM LCL_genotypes JOIN snp ON LCL_genotypes.rsID = snp.rsID where IID = 'HG02466'; +---------+----------+------------+ | IID | Position | Gene | +---------+----------+------------+ | HG02466 | 60523 | NONE,TUBB8 | +---------+----------+------------+ 1 row in set (0.00 sec) -
What if I want the output to go directly into a file instead of to the screen?
mysql> SELECT * INTO OUTFILE '/var/lib/mysql-files/colab_class_result.txt' \ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' \ LINES TERMINATED BY '\n' \ FROM LCL_genotypes JOIN snp ON LCL_genotypes.rsID = snp.rsID; Query OK, 5 rows affected (0.00 sec) mysql> SELECT IID,Position,Gene INTO OUTFILE '/var/lib/mysql-files/colab_class_result2.txt' \ FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '' ESCAPED BY '' \ LINES TERMINATED BY '\n' \ FROM LCL_genotypes JOIN snp ON LCL_genotypes.rsID = snp.rsID; Query OK, 5 rows affected (0.00 sec) mysql> exit Bye root@vcm-XXXX:~$ cat /var/lib/mysql-files/colab_class_result.txt "HG02463","10:60523:T:G","rs112920234","TT","rs112920234",10,60523,"G","T","dist=NONE;dist=32305","NONE,TUBB8","intergenic" "HG02463839238290","10:60523:T:G","rs112920234","TT","rs112920234",10,60523,"G","T","dist=NONE;dist=32305","NONE,TUBB8","intergenic" "HG02466","10:60523:T:G","rs112920234","TT","rs112920234",10,60523,"G","T","dist=NONE;dist=32305","NONE,TUBB8","intergenic" "HG02563","10:60523:T:G","rs112920234","TT","rs112920234",10,60523,"G","T","dist=NONE;dist=32305","NONE,TUBB8","intergenic" "HG02567","10:60523:T:G","rs112920234","00","rs112920234",10,60523,"G","T","dist=NONE;dist=32305","NONE,TUBB8","intergenic" root@vcm-XXXX:~$ cat /var/lib/mysql-files/colab_class_result2.txt HG02463 60523 NONE,TUBB8 HG02463839238290 60523 NONE,TUBB8 HG02466 60523 NONE,TUBB8 HG02563 60523 NONE,TUBB8 HG02567 60523 NONE,TUBB8
- Take some time to play around with queries we've talked about above...
-
please note that VCM VMs now default to powering down every morning at 06:00 am, so if you can't connect (starting tomorrow), the first thing to do is to login to https://vcm.duke.edu to verify that your VM is actually powered on.
-
sudo -- allows certain commands to be run with elevated privileges. First, without:
vcm@vcm-XXXX:~$ service mysql restart ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units === Authentication is required to restart 'mysql.service'. Authenticating as: root Password: -
And now, with:
vcm@vcm-XXXX:~$ sudo service mysql restart vcm@vcm-XXXX:~$ ps -aef | grep mysql -
To REBOOT the server itself: note that this can also be done from the VCM webUI via "Power off" and then "Power on"
vcm@vcm-XXXX:~$ sudo shutdown -r now Connection to vcm-XXXX.vm.duke.edu closed by remote host. Connection to vcm-XXXX.vm.duke.edu closed -
To change the configuration of the MySQL server, edit the "my.cnf" file AND THEN RESTART THE mysql PROCESS!!
vcm@vcm-XXXX:~$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf [making necessary edits to the file and saving them] vcm@vcm-XXXX:~$ sudo service mysql restart -
To check the error log, use "cat" (or "more" or "less"...)
vcm@vcm-XXXX:~$ sudo cat /var/log/mysql/error.log