295b9dbab341deac96c7f6166bea56f0fc7fa770 max Fri Feb 12 05:38:32 2021 -0800 changes after code review, refs #26951. Since all README files were merged into mirrorManual.txt in 2016, to avoid further confusion, I am removing them now. I manually merged all changes made since then (just 3-4 changes) into mirrorManual.txt now. mirrorManual.txt is automatically converted to https://genome.ucsc.edu/goldenPath/help/mirrorManual.html by the makefile in mirrorDocs. This makes sure that we have installation instructions that can be read with vi or less and at the same time we have a nice html file that is easy to read, looks like official documentation and is indexed by Google well. The big no.1 advantage of mirrorManual.txt is that the sections have an order in increasing complexity and the user knows where to start. The old README files had no order at all and the names were misleading (e.g. quickstart was not about a quickstart) diff --git src/product/README.mysql.setup src/product/README.mysql.setup deleted file mode 100644 index fe466b2..0000000 --- src/product/README.mysql.setup +++ /dev/null @@ -1,342 +0,0 @@ - -This file is from: - http://genome-source.soe.ucsc.edu/gitlist/kent.git/blob/master/src/product/README.mysql.setup - -MySQL DATABASE SETUP - -ENABLE "LOAD DATA LOCAL INFILE" - -Set these in /etc/my.cnf or /etc/mysql/my.cnf: - -[mysqld] -local-infile=1 - -[client] -local-infile=1 - - -STORAGE ENGINE - -MySQL default storage engine. -In recent versions of MySQL, the default storage engine has changed from -myisam to innodb. -However the myisam engine should be used with the UCSC Genome Browser. - -Set it in /etc/my.cnf or /etc/mysql/my.cnf: - -[mysqld] -default-storage-engine=MYISAM - -Always restart your mysql server after making changes to these -configuration files. - -USERS - -There are three cases of identity to consider when providing -access to the MySQL system for the browser CGI binaries -and browser developers: - -1. A MySQL user that needs read-only access to the - genome databases. The browser CGI binaries - require read-only access to the genome databases. -2. A MySQL user that has write permissions to one database. - The CGI binaries require write permissions to one particular - database (hgcentral) for maintaining user's cart information to - store the user's browser cookie settings. -3. A MySQL user that has general write permissions to all - browser and genome databases to be used by developers - -The cgi-bin binaries obtain the first two of these MySQL identities from -the text file: $WEBROOT/cgi-bin/hg.conf - -Developers of the browser databases obtain their MySQL identities -from a text file in their home directory: ~/.hg.conf -Note the initial dot in the name: .hg.conf -This file in a user's directory will specify a higher-privileged user -to allow read/write access to the MySQL databases. -This file must be set to mode 600 to provide security of the user -and password to the database: - $ chmod 600 ~/.hg.conf -All kent source code commands use this file to access the MySQL -databases. Since this file contains password information it -requires the permissions to be set at 600 to keep it secret. -The kent source code commands will enforce this access and not -function unless it is set at 600 permissions. - -Therefore you will want to create three different MySQL users -for these purposes. - - (The examples listed below are implemented in the - shell script: ex.MySQLUserPerms.sh - You can execute that script to set up these example users.) - -An example full read/write access user: "browser", is created with -the following procedure. - -For the following it is assumed that your root account -has access to the mysql database. You should be able -to perform the following: - -$ export SQL_PASSWORD=mysql_root_password -$ mysql -u root -p${SQL_PASSWORD} -e "show tables;" mysql - -Create a MySQL user called "browser" with password -"genome" and give access to selected MySQL commands -for the following list of databases. When you add other -databases, you will need to add these permissions to your -databases. This procedure of adding permissions specifically -for a set list of databases is a more secure method than allowing -the MySQL "browser" user to have access to any database. - -( MySQL version 5.5 requires the LOCK TABLES permission here ) -( FILE, CREATE, DROP, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES on ${DB}.* ) - -for DB in cb1 hgcentral hgFixed hg38 proteins140122 sp140122 go140213 uniProt go proteome -do - mysql -u root -p${SQL_PASSWORD} -e "GRANT SELECT, INSERT, UPDATE, DELETE, \ - FILE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES on ${DB}.* \ - TO browser@localhost \ - IDENTIFIED BY 'genome';" mysql -done - -The above granted permissions are recommended for browser developers. -The WEB browser CGI binaries need SELECT, INSERT and CREATE TEMPORARY -TABLES permissions. For example, you should create a special user for -the browser genome databases only. In this example, user: "readonly" -with password: "access" - -for DB in cb1 hgcentral hgFixed hg38 proteins140122 sp140122 go140213 uniProt go - proteome -do - mysql -u root -p${SQL_PASSWORD} -e "GRANT SELECT \ - on ${DB}.* TO \ - readonly@localhost IDENTIFIED BY 'access';" mysql -done - -Create a database to hold temporary tables: - - mysql -u root -p${SQL_PASSWORD} -e "create database hgTemp" - - mysql -u root -p${SQL_PASSWORD} -e "GRANT SELECT, INSERT, \ - CREATE TEMPORARY TABLES \ - on hgTemp.* TO \ - readonly@localhost IDENTIFIED BY 'access';" mysql - -A third MySQL user should be created with read-write access to only -the hgcentral database. For example, a user: "readwrite" -with password: "update" - -for DB in hgcentral -do - mysql -u root -p${SQL_PASSWORD} -e "GRANT SELECT, INSERT, UPDATE, DELETE, \ - CREATE, DROP, ALTER on ${DB}.* TO readwrite@localhost \ - IDENTIFIED BY 'update';" mysql -done - -The cgi-bin binaries obtain their MySQL identities from -the hg.conf file in the cgi-bin directory. The file in this -directory: ex.hg.conf -demonstrates the use of the "readonly" user for genome database -access and the "readwrite" user for hgcentral database access. - -==================================================================== - -Developers can access the browser databases via the 'hgsql' -command which can be built in the source-tree at: - kent/src/hg/hgsql/ - -This 'hgsql' command provides a convenient front-end to -the standard 'mysql' command by reading the user's ~/.hg.conf -file to provide access to the browser databases with the -appropriate identity. Each user creates a ~/.hg.conf file -(same format as the above mentioned cgi-bin/hg.conf file) -and the specified database user identity is used for accesses -to the browser databases. - -This same function of reading ~/.hg.conf for database access -is built into all the source-tree binaries which modify the genome -databases. - -The above example hg.conf could be used as a user's ~/.hg.conf -file with the change of db.user, db.password, central.user, -and central.password to be the fully permitted read-write user: - -db.user=browser -db.password=genome -central.user=browser -central.password=genome -central.db=hgcentral - -To test this access with your ~/.hg.conf file in place: - -$ hgsql -e "show tables;" hgcentral -$ hgsql -e "show grants;" hgcentral - - -==================================================================== -SSL - Configuring SSL connections. - -MySQL is typically compiled with SSL capability from OpenSSL or yaSSL. - -To see if your server supports ssl, login to mysql and run this command: - -mysql> show variables like '%ssl%'; -+---------------+----------+ -| Variable_name | Value | -+---------------+----------+ -| have_openssl | DISABLED | -| have_ssl | DISABLED | -| ssl_ca | | -| ssl_capath | | -| ssl_cert | | -| ssl_cipher | | -| ssl_crl | | -| ssl_crlpath | | -| ssl_key | | -+---------------+----------+ - -If your mysql was compiled with SSL support, which is true of virtually all mysql packages -being provided today, you can easily enable SSL by adding settings to /etc/my.cnf: - -------- -my.cnf: -------- - -[mysqld] -ssl -ssl-key=/somepath/server-key.pem -ssl-cert=/somepath/server-cert.pem -ssl-ca=/somepath/ca.pem -ssl-capath=/somepath/ -ssl-cipher=DHE-RSA-AES256-SHA:AES128-SHA -# mysql 5.6.3 or later -ssl-crl=/someCrlPath/some-crl.pem -ssl-crlpath=/someCrlPath/ -# mysql5.7 or later require all connections to be encrypted -require_secure_transport server - -After making changes to my.cnf, be sure to restart your mysqld service. - -The key means private key here, and should be kept secured. -The cert is a certificate acting like a public key, signed by a trusted authority (CA). - -If a key and cert are available, that means you can authorize. -And it proves the key exists. The key is not sent to the other party. The cert is. -If a ca is available it can show what certs to trust. - -You do not need all the settings, but some versions of mysql -do not activate SSL unless at least one of these is found: key, cert, ca, capath, cipher -If you configure a key for the server or client, you will also provide its cert. - -We cannot teach you how to create SSL certificates here. -There are many websites including mysql that have information about -making keys and certs and ca. - -If you just add the ssl option to the top, -it will try to use SSL, or make it available. - -The ca is the certificate authority cert that you are using. -It could be just a local self-signed authority you made up, -or it can be a commercial authority like veriSign. -This typically is used to sign the certificate for the -server and users. The capath is a directory where ca-certs exist (OpenSSL only). - -The crl is a certificate revocation list. (OpenSSL only). -The crlpath is a directory where revocation lists exist (OpenSSL only). -This crl options are a new feature in 5.6.3, not sure it works right yet. - -After making a key for the server, and signing a cert for it with ca, -you can create SSL connections. - -Do not specify a passphrase when creating your server keys. - -The cipher setting is a colon-separated list of SSL ciphers that are supported. - -The security files like certs etc. that are specified in the above settings -must be readable by the unix account that mysqld runs under, default is "mysql". - -SELinux or apparmor may block access to certain locations. -/etc/mysql is the default location for .pem files on some platforms. - -yaSSL, which is still often used with the MySQL Community Edition, -expects keys to be in the PKCS #1 format and doesn't support the PKCS #8 format used by OpenSSL 1.0 and newer. -You can convert the key to the old format using openssl rsa: - openssl rsa -in key_in_pkcs1_or_pkcs8.pem -out key_in_pkcs1.pem - -yaSSL requires that all components of the CA certificate tree be contained within a single CA certificate file -and that each certificate in the file has a unique SubjectName value. -To work around this limitation, concatenate the individual certificate files comprising the certificate tree -into a new file and specify that file as the value of the --ssl-ca option. -For example, - cd my-certs-dir - cat ca-cert.pem server-cert.pem (etc) > yaSSL-ca-cert.pem - chmod +r yaSSL-ca-cert.pem -Now use my-certs-dir/yaSSL-ca-cert.pem for certificate authority (ca) for clients. - --------- -hg.conf: --------- - -These are the SSL settings which can be placed into your hg.conf for CGIs or .hg.conf for utility programs: - -db.key=/sompath/someuser-key.pem -db.cert=/sompath/someuser-cert.pem -db.ca=/somepath/ca.pem -db.caPath=/somepath -db.crl=/someCrlPath/some-crl.pem -db.crlPath=/someCrlPath/ -db.verifyServerCert=1 -db.cipher=DHE-RSA-AES256-SHA:AES128-SHA - -The key and certificate for "someuser" above are signed by a ca. - -The verifyServerCert setting if it exists tells the client -to verify that the CN field in the server's cert matches the -hostname to which it is connecting. This prevents Man-In-the-Middle attacks. - -The caPath and crlPath options only work with OpenSSL. - -The example shows the most common use for the profile "db". -But the SSL settings work with any profile in the hg.conf file. - -Of course you can stick SSL settings into your [client] section of my.cnf, -but the CGIs and utils would not see them. Only mysql and hgsql would see them. - - ------------------------ -MySQL Accounts and SSL: ------------------------ - -Configuring SSL requirements for mysql user accounts: - -You can tell mysql to require SSL for a user's account like this: - -GRANT ALL PRIVILEGES ON *.* TO 'someuser'@'%' - REQUIRE SSL; - - -You can tell mysql to use SSL for a user's account and to -further require the client to use their key and x509 certificate to connect by saying: - -GRANT ALL PRIVILEGES ON *.* TO 'someuser'@'%' - REQUIRE x509; - -There are more-specific requirements that may be added: - -GRANT ALL PRIVILEGES ON *.* TO 'someuser'@'%' - REQUIRE SUBJECT '/C=US/ST=CA/L=Santa Cruz/O=YourCompany/OU=YourDivision/CN=someuser/emailAddress=someuser@YourCompany.com' - AND ISSUER '/C=US/ST=CA/L=Santa Cruz/O=YourCompany/OU=YourDivision/CN=YourCompanyCA/emailAddress=admin@YourCompany.com' - AND CIPHER 'DHE-RSA-AES256-SHA'; - -You can see the cert details like this: - openssl x509 -in /somepath/someuser-cert.pem -text - -In later versions of MySQL, it is a requirement that the CN of the CA cert must DIFFER -from the CN of the user and server certs. - -Further MySQL SSL documentation is available here: - https://dev.mysql.com/doc/refman/5.6/en/ssl-connections.html - -==================================================================== -Information last updated: 7 August 2015 -====================================================================