Mysql command options: –auto-rehash Enable or disable automatic rehashing –auto-vertical-output Automatically display output of commands vertically rather than horizontally –batch Do not write to the history file –binary I use this when I have large results timing out due to blobs being in there –bind-address Specify a network interface when connecting to the server –character-sets-dir Indicate a custom directory where character sets are installed –column-names Output column names when running queries –column-type-info Show metadata –comments Enable or disable commenting when sending statements to the server –compress Compresses data sent to/from client and server –connect_timeout Seconds of inactivity before a timeout –database The database to connect to –debug Write debugging log; supported only if MySQL was built with debugging support –debug-check Print debugging information when program exits –debug-info Print debugging information, memory, and CPU statistics when program exits –default-auth Authentication plugin to use –default-character-set Specify default character set –defaults-extra-file Read named option file in addition to usual option files –defaults-file Read only named option file –defaults-group-suffix Option group suffix value –delimiter Set the statement delimiter –enable-cleartext-plugin Enable cleartext authentication plugin –execute Execute the statement and quit –force Continue even if an SQL error occurs –help Display help message and exit –histignore Patterns specifying which statements to ignore for logging –host Connect to MySQL server on given host –html Produce HTML output –ignore-spaces Ignore spaces after function names –init-command SQL statement to execute after connecting –line-numbers Write line numbers for errors –local-infile Enable or disable for LOCAL capability for LOAD DATA INFILE –login-path Read login path options from .mylogin.cnf –max_allowed_packet Maximum packet length to send to or receive from server –max_join_size The automatic limit for rows in a join when using –safe-updates –named-commands Enable named mysql commands –net_buffer_length Buffer size for TCP/IP and socket communication –no-auto-rehash Disable automatic rehashing –no-beep Do not beep when errors occur –no-defaults Read no option files –one-database Ignore statements except those for the default database named on the command line –pager Use the given command for paging query output –password Password to use when connecting to server –pipe On Windows, connect to server using named pipe –plugin-dir Directory where plugins are installed –port TCP/IP port number to use for connection –print-defaults Print default options –prompt Set the prompt to the specified format –protocol Connection protocol to use –quick Do not cache each query result –raw Write column values without escape conversion –reconnect If the connection to the server is lost, automatically try to reconnect –safe-updates Only process UPDATE and DELETE statements when they have values –secure-auth Do not send passwords to server in old (pre-4.1) format 5.7.5 –select_limit The automatic limit for SELECT statements when using –safe-updates –server-public-key-path Path name to file containing RSA public key –shared-memory-base-name The name of shared memory to use for shared-memory connections –show-warnings Show warnings after each statement if there are any –sigint-ignore Ignore SIGINT signals (typically the result of typing Control+C) –silent Run silently (necessary when daemonizing) –skip-auto-rehash Disable automatic rehashing –skip-column-names Do not write column names in results –skip-line-numbers Skip line numbers for errors –skip-named-commands Disable named mysql commands –skip-pager Disable paging –skip-reconnect Disable reconnecting –socket For connections to localhost, the Unix socket file to use –ssl Enable secure connection –ssl-ca Path of file that contains list of trusted SSL CAs –ssl-capath Path of directory that contains trusted SSL CA certificates in PEM format –ssl-cert Path of file that contains X509 certificate in PEM format –ssl-cipher List of permitted ciphers to use for connection encryption –ssl-crl Path of file that contains certificate revocation lists –ssl-crlpath Path of directory that contains certificate revocation list files –ssl-key Path of file that contains X509 key in PEM format –ssl-mode Security state of connection to server 5.7.11 –ssl-verify-server-cert Verify server certificate Common Name value against host name used when connecting to server –syslog Log interactive statements to syslog 5.7.1 –table Display output in tabular format –tee Append a copy of output to named file –tls-version TLS versions Protocols permitted for secure connections –unbuffered Constantly flush buffers –user The user name to use when connecting to server (SQL users are not the same as users on an OS) –verbose As with most other commands, output verbosely –version Show the MySQL version number –vertical Show one line per column in query outputs –wait Retry when a connection fails –xml Output data into an XML format

Slow queries can cause poor database performance. MySQL has a slow query log, which lets you log queries that take too long (with too long a user-configurable setting). This allows you to quickly find craptastic queries in your logs. To enable the slow query log, use SET GLOBAL and then set the slow_query_log option to ON: SET GLOBAL slow_query_log = 'ON'; By default, the slow query log identifies any query that takes 10 seconds or longer. To change long_query_time using SET GLOBAL. Here, we’ll set it to 7 seconds: SET GLOBAL long_query_time = 7; You can also set the log file to something other than the default location of /var/lib/mysql/hostname-slow.log. Again, use SET GLOBAL and then provide a path for slow_query_log_file: SET GLOBAL slow_query_log_file = '/var/logs/slowquery.log'; To disable the slow query log: SET GLOBAL slow_query_log = 'OFF';

You can grant access to certain columns to view in SQL without providing access to specific users to see the whole database. This is pretty useful when delegating reporting to users, without giving them access to all of the data in your database. For example, a user might be able to see a column with an address, but not a column with a credit card number, increasing database security while allowing you to delegate certain tasks when appropriate. In this article, we’ll use the same “Customers” table from our first articles, signupdate: ID Site Contact Address City Zip Country SignupDate 1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01 2 Apple Tim Cook spaceship Cupertino 95014 US 2015-12-05 3 Microsoft Satya Nadella campus Redmond 98053 US 2014-11-01 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 2010-03-10 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 2016-01-01 Next, we’ll create a view called SignupDate that only has customers that signed up on January 1st of 2005. This view returns the data set of contacts and signup dates: CREATE VIEW signupdate AS SELECT * FROM Customers WHERE OrderDate='2005-01-01'; The syntax is similar to a SELECT, but with CREATE VIEW followed by the name of the view and then AS followed by the SELECT statement. The view is a virtual table containing the output of the query rather than data. Once created, use use the signupdate view in a query: SELECT * FROM signupdate; This SQL statement returns the following results: 1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01 You can also use the view to contain a query with just the columns you want, according to how you structure your query, thus granting access to specific columns, without granting access to all of the columns in a table.

If you have growing sets of data, one of the best ways to speed up database performance is to make sure each column in larger tables is indexed. You can easily index a column following this syntax (using the name of your table in the place of tablename and the name of your column in the place of columnname): ALTER TABLE tablename ADD INDEX (columnname); So if you have a table called Customers and the following columns (as in the case of my Customers database from the previous exercises): ID Site Contact Address City Zip Country You would index them all as follows: ALTER TABLE Customers ADD INDEX (ID); ALTER TABLE Customers ADD INDEX (Site); ALTER TABLE Customers ADD INDEX (Contact); ALTER TABLE Customers ADD INDEX (Address); ALTER TABLE Customers ADD INDEX (City); ALTER TABLE Customers ADD INDEX (Zip); ALTER TABLE Customers ADD INDEX (Country);

Flyway is a tool that allows you to perform version-controlled management of SQL schemas. You can download flyway at It doesn’t require a build, so once downloaded, drop it where you want it to live and you can just summon the binary in scripts. Once installed, the following is the basic structure of commands for Flyway: flyway [options] command By default, the configuration will be read from conf/flyway.conf. Options passed from the command-line override the configuration. Commands ======== migrate : Migrates the database clean : Drops all objects in the configured schemas info : Prints the information about applied, current and pending migrations validate : Validates the applied migrations against the ones on the classpath baseline : Baselines an existing database at the baselineVersion repair : Repairs the metadata table Options (Format: -key=value) ======= driver : Fully qualified classname of the jdbc driver url : Jdbc url to use to connect to the database user : User to use to connect to the database password : Password to use to connect to the database schemas : Comma-separated list of the schemas managed by Flyway table : Name of Flyway’s metadata table locations : Classpath locations to scan recursively for migrations resolvers : Comma-separated list of custom MigrationResolvers sqlMigrationPrefix : File name prefix for Sql migrations sqlMigrationSeparator : File name separator for Sql migrations sqlMigrationSuffix : File name suffix for Sql migrations encoding : Encoding of Sql migrations placeholderReplacement : Whether placeholders should be replaced placeholders : Placeholders to replace in Sql migrations placeholderPrefix : Prefix of every placeholder placeholderSuffix : Suffix of every placeholder target : Target version up to which Flyway should use migrations outOfOrder : Allows migrations to be run “out of order” callbacks : Comma-separated list of FlywayCallback classes validateOnMigrate : Validate when running migrate cleanOnValidationError : Automatically clean on a validation error baselineVersion : Version to tag schema with when executing baseline baselineDescription : Description to tag schema with when executing baseline baselineOnMigrate : Baseline on migrate against uninitialized non-empty schema configFile : Config file to use (default: conf/ configFileEncoding : Encoding of the config file (default: UTF-8) jarDirs : Dirs for Jdbc drivers & Java migrations (default: jars) Add -X to print debug output Add -q to suppress all output, except for errors and warnings Example ======= flyway -target=1.5 -placeholders.user=my_user info More info at

Installing MySQL on Linux is pretty easy. You can use yum (or your favorite package manager for most installs. Here, we’ll pull a list of packages from yum using repolist: yum repolist enabled | grep "mysql.*-community.*" You’ll then get a list of community edition MySQL packages that are available. Then let’s say you’re installing on RHEL 6, we’ll pull a string from the repolist of an appropriate package and then do a localinstall of it: sudo yum localinstall mysql57-community-release-el6-157.noarch.rpm We could also grab mysql and all the other stuffs we want to have with it: yum install mysql mysql-server mysql-libs mysql-server And then start it up: service mysql start

SQL has a number of built-in functions that can be used to find common query results, such as averaging data, summing up a column of data, rounding information off, formatting data, etc. SQL also has a number of options for building your own custom functions (and triggering them to run). I usually like to use functions when I’m looking for data and reporting. I don’t like using them in code, as the language I’m performing a task in is typically better suited to manage data than is SQL, comparably. SQL functions come in three types. Aggregate, Scalar, and Custom. Aggregate functions (those that take multiple objects as an input):
  • AVG() – Show the average value in a defined set of data
  • COUNT() – Output the number of rows that match a pattern
  • FIRST() – Output the first value in a set of objects
  • LAST() – Returns the last value in a set of objects
  • MAX() – Show the largest value given a set of objects
  • MIN() – Output the smallest value of a set of objects
  • SUM() – Output the sum defined objects
Scalar functions, which return a value (such as a number or new text):
  • FORMAT() – Formats data in a field
  • LEN() – Shows the length of text
  • LCASE() – Converts the specified data into lower case
  • MID() – Pulls specified characters from text
  • NOW() – Shows the system date and time
  • ROUND() – Rounds a decimal number to the decimals indicated
  • UCASE() – Converts specified data into upper case
Now let’s look at actually using a SQL function. We’ve covered running a SELECT Statement in SQL. But what if you want to count the number of objects returned with that statement. You could dump the results into a csv and view them in Excel. Or, you could save yourself a little time by using the SQL COUNT() function, which shows the number of rows that match a given criteria. In its most basic form, the COUNT function is used as follows (where column is a column in a database and table is the table the column is in: SELECT COUNT(column) FROM table; To put this in action, you can look at all records in the Customers table using the following: SELECT COUNT(*) FROM Customers; In this article, we’ll use the same “Customers” table from our first articles to add and edit columns: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US So the output of the first COUNT would be 5. To constrain, you can use WHERE  following the initial COUNT line. For example, the following SQL statement counts the number of customers from “City”=Minneapolis from the “Customers” table: SELECT COUNT(City) AS InMinneapolis FROM Customers WHERE City="Minneapolis"; You can also use the DISTINCT option to look at how many unique customer cities we have: SELECT COUNT(DISTINCT City) AS CitieswithCustomers FROM Customers;

The first step to reset a password is to stop the MySQL daemon. This will cause mysqld to accept no new connections and terminate existing connections. But this can all be done in a matter of seconds, usually. To stop MySQL on Mac, use the System Preference pane or launchctl. To stop on Linux, use init.d: sudo /etc/init.d/mysql stop Or if it’s mysqld instead: sudo /etc/init.d/mysqld stop Then start the SQL daemon using the –skip-grant-tables option: sudo mysqld_safe --skip-grant-tables & Next, login to mysql, which won’t require a password running in this mode: mysql -u root And use the UPDATE USER statement to set a new password: UPDATE USER set password=PASSWORD("mysecretpassword") WHERE USER='root'; Then flush the privileges: flush privileges; Viola, start things back up normally and you’re off to the races.

SQL constraints the data that can be in a table. A violation of a constraint causes an action to be aborted. Constraints can be defined upon creation or using the ALTER TABLE statement once created. The general syntax of a CREATE (or use ALTER instead of CREATE) when defining constraints is as follows: CREATE TABLE tablename ( columnname datatype(size) constraintname, columnname datatype(size) constraintname, columnname datatype(size) constraintname, columnname datatype(size) constraint name, columnname datatype(size) constraint name, ); Obviously, replace columnname with the name of each of your column, datatype with the types of data your column contains and constraint name with the constraint you wish to use. You have the following constraints available:
  • CHECK: Verify that values meet the defined condition
  • DEFAULT: Sets a default value for new rows in a column
  • FOREIGN KEY: Verify referential integrity of data in a table to match values in another
  • NOT NULL – Columns cannot store a NULL value (be empty)
  • PRIMARY KEY – Columns cannot store a NULL value AND values in rows must be unique
  • UNIQUE – Each row in a column must be unique
For example, the NOT NULL constraint would be defined as follows: CREATE TABLE testingnotnull ( telephonenumber int NOT NULL, ); If you have an app sitting in front of a database, then use these with caution, as if SQL just terminates an operation your app might have unexpected integrity issues.

By default, MySQL comes with a root user configured. You can also create additional users, change passwords for users, and assign what databases and tables they have access to. From MySQL, you can can create a basic user using the CREATE USER statement, providing a user, a location, and then using IDENTIFIED BY followed by a password. In production, this would look similar to the following, using krypted as the user and mysecretpassword as the password: CREATE USER 'krypted'@'localhost' IDENTIFIED BY 'mysecretpassword'; Once you’ve created a user, you’ll want to assign what the user can access. Here, the * wildcard is pretty handy. In the following command, we’ll use the GRANT statement along with ALL PRIVILEGES to give this new krypted user access to all of the databases running on MySQL: GRANT ALL PRIVILEGES ON * . * TO 'krypted'@'localhost'; Pretty easy so far. Just flush the permissions with the FLUSH PRIVILEGES statement and krypted’s now all good to access anything that exists in MySQL when the command was run. FLUSH PRIVILEGES; Once you’ve flushed, you can see what a user can access using the SHOW GRANTS statement: SHOW GRANTS FOR 'krypted'@'localhost'; If you create new databases, do so again. To login as the user, you can then just run mysql followed by the -u option to define the user: mysql -u krypted -p To remove permissions, use the REVOKE statement. Let’s remove the ALL PRIVILEGES from krypted: REVOKE ALL PRIVILEGES ON *.* FROM 'krypted'@'localhost'; To delete a user, use the DROP statement, and then USER, followed by who we’re deleting: DROP USER ‘krypted’@‘localhost’; If you were then going to create the user and provide a different level of privileges you could replace ALL PRIVILEGES with one of the following:
  • ALL PRIVILEGES: Gives the user full access to a database (or all included with a wildcard)
  • CREATE: Gives a user the ability to create new databases or tables within a database the access is provided
  • DROP: Gives a user the ability to use the DROP statement to remove tables or databases
  • DELETE: Gives a user the ability to delete rows from tables
  • GRANT OPTION: Gives a user the ability to add and remove privileges for other users
  • INSERT: Gives a user the ability to create rows into tables using the INSERT statement
  • SELECT: Gives a user the ability to use SELECT statements (similar to read in POSIX)
  • UPDATE: Gives a user the ability to update table rows only
We can also string some of this together in one statement, such as if we wanted the krypted password to expire in 60 days: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE INTERVAL 60 DAY; A full list of options would include the following syntax, with options including maximum queries, max connections, ssl, auto-lock, etc: CREATE USER [IF NOT EXISTS] user_specification [, user_specification] ... [REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}] [WITH resource_option [resource_option] ...] [password_option | lock_option] ... user_specification: user [ auth_option ] auth_option: { IDENTIFIED BY ‘auth_string’ | IDENTIFIED BY PASSWORD ‘hash_string’ | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin BY ‘auth_string’ | IDENTIFIED WITH auth_plugin AS ‘hash_string’ } tsl_option: { SSL | X509 | CIPHER ‘cipher’ | ISSUER ‘issuer’ | SUBJECT ‘subject’ } resource_option: { MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count } password_option: { PASSWORD EXPIRE | PASSWORD EXPIRE DEFAULT | PASSWORD EXPIRE NEVER | PASSWORD EXPIRE INTERVAL N DAY } lock_option: { ACCOUNT LOCK | ACCOUNT UNLOCK }