krypted.com

Tiny Deathstars of Foulness

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: , , , , , , , ,

OS X might be the easiest platform to install MySQL on. To do so, simply download the MySQL installation package from the MySQL Download site. I like to use the third link (the DMG). Screen Shot 2016-02-13 at 10.26.11 PM Once downloaded, run the package. The package will ask you a few questions and you can easily just select the default choice during the installation process. Screen Shot 2016-02-13 at 10.26.04 PM Once installed, you’ll be prompted that a temporary password has been used for your MySQL instance. Screen Shot 2016-02-13 at 10.26.52 PM The password will get you in the first time, so you can change it. Once you have documented the password, open System Preferences and click on MySQL in the bottom row of System Preference Panes. Screen Shot 2016-02-13 at 10.27.34 PM Click Start MySQL Server and then when prompted, authenticate to the system. If you’d like to do this programmatically and don’t need the System Preference pane, you can do so with homebrew. If you have homebrew installed, simply run the brew command with the install verb and mysql as the package: brew install mysql Whichever way you install SQL, once installed, you’ll want to set the root password to something other than the intuitionally difficult to remember password provided at install time. To do so, first connect to the mysql instance now running on your computer. As the tools are installed in /usr/local/mysql/bin, run the following: /usr/local/mysql/bin/mysql -u root Then, set the password using the ALTER statement along with the USER option and then the username followed by IDENTIFIED BY and ultimately the password, as follows: ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysupersecretpassword'; Once done, you’ll then be able to connect to mysql normally.

February 18th, 2016

Posted In: SQL

Tags: , , , ,

Create a backup copy of a table called Customers into a table called Customers2 on a running database called Backup: SELECT * INTO Customers2 IN 'Backup.mdb' FROM Customers; You can also specify multiple tables to pull data from when bringing that data into a new table, effectively merging data into a backup database: SELECT Customers.Site, IPs.IP INTO CustomerIPsbackup FROM Customers LEFT JOIN IPs ON Customers.Site=IPs.IP; Since this is more like replication than backup, MySQL also has a mysqldump command, used to dump a sql database to a file or screen, or whatever. Here, we’ll export all of our databases in a running MySQL instance into a file called dumpfile.sql: mysqldump --all-databases > dumpfile.sql

February 17th, 2016

Posted In: SQL

Tags: , , , ,

You can use the SELECT INTO statement finds data and then copies that data between tables or databases. To do so, use the following syntax: SELECT * INTO newtablename FROM tablename; So to copy that Customers table to a new table called Customers2: SELECT * INTO Customers2 FROM Customers; Or to copy only certain columns into Customers2, we’d use the following: SELECT ID,Site INTO Customers2 FROM Customers;

February 16th, 2016

Posted In: SQL

Tags: , , , , ,

So you’re ready to write some software? Or test some cool stuff. Or build something awesome. You can use the CREATE DATABASE statement to get started, by creating a database. To do so is pretty easy, simply run that statement followed by a name for the database (called Customers): CREATE DATABASE Customers; Once you’ve created a database, it’s time to create tables, which can be done using the CREATE TABLE statement. The Syntax of that statement looks something like this, defining a set of columns, their data type and the size of the column (in the form of a maximum length), all wrapped in parenthesis with each column separated by a comma: CREATE TABLE nameoftable ( column datatype(size), column datatype(size), ); So to create the Customers table that we’ve been using through these articles, we’ll use the following SQL Statement: CREATE TABLE Customers ( ID integer(255), Site varchar(255), Contact varchar(255), Address varchar(255), City varchar(255), Zip integer(255), Country varchar(255), ); Columns are created with data types. In the previous example, we named columns of integers and varchars. The available data types include the following:
  • CHARACTER: A string of characters using a defined length
  • VARCHAR: A variable length string of characters using a maximum length
  • BINARY: A binary string
  • BOOLEAN: TRUE or FALSE
  • VARBINARY: Binary string in a variable length, with a defined maximum length
  • INTEGER: A number with no decimals
  • SMALLINT: A 5 digit or less number with no decimals
  • BIGINT: A 19 digit or less number with no decimals
  • DECIMAL: Number with decimals
  • FLOAT: Floating number in base 10
  • REAL: Number
  • DOUBLEPRECISION: Approximate number
  • DATE: Year, month, and day in separated values
  • TIME: Hour, minute, and second in separated values
  • TIMESTAMP: Time in the form of year, month, day, hour, minute, and second
  • INTERVAL: A period of time
  • ARRAY: Ordered collection of data
  • MULTISITE: Unordered collection of data
  • XML: Stores XML

February 15th, 2016

Posted In: SQL

Tags: , , , , , , , , , , , , , , ,

Previously, we covered creating tables in SQL. To create a column in a table, use the ALTER TABLE statement, along with the ADD and then define a column name followed by the data type: ALTER TABLE nameoftable ADD nameofcolumn datatype To delete a column in a table:: ALTER TABLE nameoftable DROP COLUMN nameofcolumn To change the type of data stored in a column, use MODIFY: ALTER TABLE nameoftable MODIFY COLUMN nameofcolumn datatype 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 Let’s add a column called “ExpDate” in the “Customers” table with a date type, which will oddly hold a date. To do so, use the following SQL statement: ALTER TABLE Customers ADD ExpDate date The new columns are empty. Once you’ve created a column, you can then change the data type of that column.Let’s just change this to year, assuming all the ExpDate fields have the same actual day for their expiration: ALTER TABLE Customers ALTER COLUMN ExpDate year Crap, I promised love. How about this, give all the things away for free. Now let’s delete the column: ALTER TABLE Customers DROP COLUMN ExpDate Obviously, this would delete all of the data in the column as well, so be careful!

February 13th, 2016

Posted In: SQL

Tags: , , , , , , , , , , , ,

« Previous PageNext Page »