krypted.com

Tiny Deathstars of Foulness

MySQL usually pulls settings from a my.cnf file. However, you can end up with settings in include files, which can be defined in the my.cnf using the following directives: include /home/mydir/myopt.cnf includedir /home/mydir Because of this, and the fact that you might not have access to all locations of .cnf files on a filesystem, you can also grab them using the SHOW VARIABLES option within SQL, obtained by /usr/local/mysql/bin/mysql -uroot -p mypassword -e "SHOW VARIABLES;" > /tmp/SQLSettings.txt In the above command, -uroot defines we’ll be accessing with the root user, -p defines the password (listed as mypassword) and the -e defines that we want to execute a command and then quit. We then use > to dump the output into the defined file.

April 21st, 2017

Posted In: Mac OS X, SQL

Tags: , , ,

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

April 17th, 2016

Posted In: Mac OS X, Mac OS X Server, Mac Security

Tags: , , , ,

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';

March 19th, 2016

Posted In: SQL

Tags: , , , ,

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);

March 8th, 2016

Posted In: SQL

Tags: , , , , ,

SQL has some really great features for managing dates. Given that dates are a very common thing to store in databases, that’s a pretty logical thing to get to a good, mature point. But the most challenging aspect of working with date and time in SQL is formatting. There are lots of different ways to format dates around the world, and even different structures (e.g. epoch) that are often complicated by adding time to records. But, you need to do things in a consistent fashion with how SQL expects them, if you’ll be using built in functions to manage dates. Dates can be stored in a variety of formats, which include the following:
  • DATE: Appears as YYYY-MM-DD
  • DATETIME: Appears as YYYY-MM-DD HH:MI:SS
  • TIMESTAMP: Appears as YYYY-MM-DD HH:MI:SS
  • YEAR: Appears as YY or YYYY
What are those functions?
  • CONVERT: Shows the date/time data in the specified format
  • CURATE: Shows the current date
  • CURTIME: Shows the current time
  • DATE: Extracts the date from a date/time field
  • DATE_ADD: Adds the time to a date
  • DATE_SUB: Removes the time from a date
  • DATE_FORMAT: Shows the date and time data in different formats
  • DATEDIFF: Shows the days between two specified dates
  • EXTRACT: Shows part of a date and time formatted value
  • NOW: Shows the current date and time, frequently used to capture the date during signups, etc
In this article, we’ll use the same “Customers” table from our first articles, but we’ll add a column for 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 Let’s show (SELECT) records with an SignupDate of 2005-01-01 from the table above. SELECT * FROM Customers WHERE OrderDate='2005-01-01' Overall, dates are pretty easy. Times are a little more challenging, and take up much more space in a database. But overall, provided you build the column with the right format you want to use, the functions make managing date tasks all the easier. I’m not going to cover all of the options, but let’s look at EXTRACT real quick. Here, we’ll serialize the year, month, and day from the previous select. To do so, we’ll use the SELECT statement, call the EXTRACT function, and then split the three parts of the date into variables we’re making up (so you can use your own strings for the names), as SignupYear, SignupMonth, and SignupDay: SELECT EXTRACT(YEAR FROM SignupDate) AS SignupYear, EXTRACT(MONTH FROM SignupDate) AS SignupMonth, EXTRACT(DAY FROM SignupDate) AS SignupDay, FROM Customers WHERE OrderDate='2005-01-01' Obviously, we knew the OrderDate, but we could have used any other column and pattern to match for that column in the WHERE to serialize date information based on other parameters. Fun stuff.

March 6th, 2016

Posted In: SQL

Tags: , , , , , , ,

By default, MySQL allows other services on the computer you’re running the daemon to connect to the database and denies any connections from hosts outside that computer. However, it’s pretty easy to provide access to the database from another host (for example, if you’re splitting up the back-end and front-end of a site, clustering, etc. To get started, you’ll edit your my.cnf file and find the [mysqld] section of the file. Then, locate the bind-address, which you will need to set as the IP of your server and comment out the line for skip-networking. Let’s say we’re going to open access for 192.168.2.2. The section would look similar to the following: [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/English bind-address = 192.168.2.2 # skip-networking Then restart MySQL and a listener should be running on the system. You can connect using the mysql command, with the -u option to define a user (root) and then the -h to define an IP (in this case 192.168.2.2): mysql -u root –h 192.168.2.2 –p You can also use the telnet command to attempt a connection into a given port, which in the MySQL case would be 3306: telnet 192.168.2.2 3306 Now, just because you can connect remotely doesnt necessarily mean that another computer can actually get into any databases. Next, we’ll GRANT access to ALL resources for a user called krypted for all tables on a new database, coming from an IP of 192.168.2.3: GRANT ALL ON *.* TO krypted@'192.168.2.3' IDENTIFIED BY 'mysupersecretpassword'; Or for an existing database called mydatabase, using the same IP and account as before: UPDATE DB set Host='192.168.2.3' where Db='mydatabase'; UPDATE USER set Host='192.168.2.3' where user='krypted'; You’ll also need to open up port 3306 coming in, whether that’s using a firewall or opening traffic in from the internets. Once that’s done, you should be able to connect and use the database as needed.

February 25th, 2016

Posted In: SQL

Tags: , , , , ,

No, SQL top doesn’t look at the status of a database the way the TOP command in bash does. It looks at the top number of records returned in a query, so a bit more like head. This makes SELECT TOP useful with larger tables where this will be millions of records getting loaded into memory during a query. In this article, we’ll use the same “Customers” table from our first articles to test out TOP: 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 The following SELECT TOP statement shows the top 20% of the records from “Customers”: SELECT TOP 20 PERCENT * FROM Customers; Or just SELECT TOP the first three records: SELECT TOP 3 * FROM Customers;  

February 23rd, 2016

Posted In: SQL

Tags: , , , ,

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.

February 22nd, 2016

Posted In: SQL

Tags: , , , , , ,

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.

February 21st, 2016

Posted In: SQL

Tags: , , , , , , , , ,

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 }

February 20th, 2016

Posted In: SQL

Tags: , , , , , , , ,

Next Page »