Install MySQL on Linux

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

Using Functions in SQL

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;

Allow Remote Connections To MySQL

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.

SQL TOP

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;  

Reset A Lost MySQL Password

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

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.

User And Permissions Management In MySQL

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 }

Install MySQL On OS X

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.

A Couple Of Ways To Backup SQL Data

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

Use SELECT INTO To Copy Data Between Tables

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;