Install MySQL on Solaris 11.3

Let’s see whats available
=========================

root@solaris11:/# pkg search -r mysql|grep summary|grep Database
pkg.summary set MySQL Database Management System (Base) pkg:/database/mysql-common@5.11-0.175.3.0.0.30.0
pkg.summary set MySQL 5.1 Database Management System pkg:/database/mysql-51@5.1.37-0.175.3.0.0.30.0
pkg.summary set MySQL 5.5 Database Management System pkg:/database/mysql-55@5.5.43-0.175.3.0.0.30.0
pkg.summary set MySQL 5.6 Database Management System pkg:/database/mysql-56@5.6.21-0.175.3.0.0.30.0
root@solaris11:/#

looks like there are 3 versions out there. Lets go for the latest, but first check it

Check it before installing
==================
root@solaris11:/# pkg info -r mysql-56
Name: database/mysql-56
Summary: MySQL 5.6 Database Management System
Category: Development/Databases
State: Not installed
Publisher: solaris
Version: 5.6.25
Build Release: 5.12
Branch: 5.12.0.0.0.90.0
Packaging Date: January 21, 2016 05:42:10 PM
Size: 447.83 MB
FMRI: pkg://solaris/database/mysql-56@5.6.25,5.12-5.12.0.0.0.90.0:20160121T174210Z

Install It:
===========
root@solaris11:/# pkg install mysql-56
Packages to install: 2
Services to change: 2
Create boot environment: No
Create backup boot environment: No

DOWNLOAD PKGS FILES XFER (MB) SPEED
Completed 2/2 231/231 90.0/90.0 143k/s

PHASE ITEMS
Installing new actions 269/269
Updating package state database Done
Updating package cache 0/0
Updating image state Done
Creating fast lookup database Done
Updating package cache 1/1
root@solaris11:/#

Is it there?
============
root@solaris11:/# svcs -a | grep mysql
disabled 12:38:21 svc:/application/database/mysql:version_56
root@solaris11:/#

Enable the MySQL Service
=====================
root@solaris11:/# svcadm enable mysql
root@solaris11:/# svcs -a |grep mysql
online 13:11:28 svc:/application/database/mysql:version_56
root@solaris11:/#

Install the client  (optional)
==================
root@solaris11:/usr/mysql/5.6# pkg install mysql-56/client
Packages to install: 1
Mediators to change: 1
Create boot environment: No
Create backup boot environment: No

DOWNLOAD PKGS FILES XFER (MB) SPEED
Completed 1/1 36/36 25.3/25.3 255k/s

PHASE ITEMS
Installing new actions 60/60
Updating package state database Done
Updating package cache 0/0
Updating image state Done
Creating fast lookup database Done
Updating package cache 1/1
root@solaris11:/usr/mysql/5.6#

Let’s connect to it
===================
root@solaris11:~# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> Ctrl-C — exit!
Aborted
root@solaris11:~#

MySQL: recover lost root password

Log in as root and stop the mysql daemon.

service mysqld stop

Then start up the mysql daemon and skip the grant tables which stores the passwords and other priviledge information

mysqld_safe --skip-grant-tables &

Now you should be able to connect to mysql without a password.

mysql --user=root mysql
mysql> update user set Password=PASSWORD('new password') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

and then restart the mysql daemon (it will stop the mysql-safe) as you would normally do

service mysqld start

10 tips for optimizing mysql queries

1. use the explain command
Use multiple-row INSERT statements to store many rows with one SQL statement.

The explain command can tell you which indexes are used with the specified query and many other pieces of useful information that can help you choose a better index or query.

Example of usage: explain select * from table

explanation of row output:
* table—The name of the table.
* type—The join type, of which there are several.
* possible_keys—This column indicates which indexes MySQL could use to find the rows in this table. If the result is NULL, no indexes would help with this query. You should then take a look at your table structure and see whether there are any indexes that you could create that would increase the performance of this query.
* key—The key actually used in this query, or NULL if no index was used.
* key_len—The length of the key used, if any.
* ref—Any columns used with the key to retrieve a result.
* rows—The number of rows MySQL must examine to execute the query.
* extra—Additional information regarding how MySQL will execute the query. There are several options, such as Using index (an index was used) and Where (a WHERE clause was used).
2. use less complex permissions

The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements.
3. specific mysql functions can be tested using the built-in “benchmark” command

If your problem is with a specific MySQL expression or function, you can perform a timing test by invoking the BENCHMARK() function using the mysql client program. Its syntax is BENCHMARK(loop_count,expression). The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute
4. optimize where clauses
* Remove unnecessary parentheses
* COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.
* If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table
5. Run optimize table

This command defragments a table after you have deleted a lot of rows from it.
6. avoid variable-length column types when necessary

For MyISAM tables that change frequently, you should try to avoid all variable-length columns (VARCHAR, BLOB, and TEXT). The table uses dynamic row format if it includes even a single variable-length column.
7. insert delayed

Use insert delayed when you do not need to know when your data is written. This reduces the overall insertion impact because many rows can be written with a single disk write.
8. use statement priorities
* Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.
* Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is executed even if there is another client waiting.
9. use multiple-row inserts

Use multiple-row INSERT statements to store many rows with one SQL statement.
10. synchronize data-types

Columns with identical information in different tables should be declared to have identical data types so that joins based on the corresponding columns will be faster.

Using MySQL – basic commands

Shutting down MySQL:
$ mysqladmin -u root -p shutdown

Starting MySQL:
# cd /usr/local/mysql
# bin/safe_mysql &

Creating a new database:
$ mysqladmin -u root -p create new_database_name

Granting Permissions:
mysql> GRANT DELETE, INSERT, ALTER, SELECT, UPDATE, CREATE, DROP, INDEX
ON DatabaseName.* TO username@localhost IDENTIFIED BY ‘password’;
mysql> flush privileges;
Typically you want to do yourself, root, and whatever user you run the the web server as. Optionally, you may wish to restrict which tables are accessable rather than just using *.

Database names are case sensitive. To list them:
mysql> show databases;

To select a database:
mysql> use DatabaseName;

To see what tables are in a database:
mysql> show tables;

To see what makes up a given table:
mysql> describe TableName;

To create a database:
mysql> create database DatabaseName;

To add a table:
mysql> create table TableName ( colname coltype( precision ), … );

To add a column:
mysql> alter table TableName ADD COLUMN ( column defintion );

backup and restore in mysql

Backup MySQL Database to a file:
——————————-

(this can be run from cron)

Syntax:

mysqldump -h localhost -u root -pmypassword databasename > dumpfile.sql

Example:

mysqldump -h localhost -u root -p2Uad7as9 database01 > dumpfile.sql

This will give you a text file containing all the commands required to recreate the database.

Restore MySQL database from a backup file:
—————————————–

Syntax:

mysql -h hostname -u username -pthepassword databasename < dumpfile.sql Example: mysql -h localhost -u root -p72aDufi8 db01 < thedumpfile.sql Dump just the table structure to a file in MySQL: ------------------------------------------------ Syntax: mysqldump -d -h localhost -u root -pmypassword databasename > dumpfile.sql

The only option that is different than creating an entire backup is the -d switch, which tells mysqldump not to output the data.

Example:

mysqldump -d -h localhost -u root -p2Uad7as9 database01 > dumpfile.sql

MySQL Reference: ORDER BY

Using ORDER BY will sort the results in a specific order based on the values of the desired column: see the example for more details…

MySQL Example: (!)
SELECT * FROM table ORDER BY name DESC;

Description:

This will SELECT all the records from the db table and sort them via ‘name’ is a DESCending order.

ASC can also be used.

MySQL Example: (!)
Select Name,Category FROM authors ORDER BY Category,Name;

Description:

Will allow you to sort by categories listed in a seperate table
IF the category column in this primary table contains ID values
from your ID column in your second reference table.

So your first "authors" table looks like:

id name category
1 Henry Miller 2
3 June Day 1
3 Thomas Wolf 2

and your second reference table looks like:

id category
1 Modern
2 Classics

Now when the order of categories is changed in the second table
the order of categories will be reflected in the primary table.

Then just select the categories from the reference table and put
the list into a numbered array. Then in your script when you run
across a category number from the first recordset just reference
the value from the index in the second array to obtain the value.
In php in the above example it might look like:

foreach ($recordset as $key => $record) {
echo $record["id"] . ":" . $record["name"] . ":" . $ordered_cats[$record["category"]];
}

This may seem obvious to some but I was pulling my hair out
trying to figure out how to order a recordset based on a list
from a different table. Hope this helps someone.

MYSQL function space error

By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.

Example:
When you sort using "ORDER BY FIELD" if there is a space after that it doesn’t
work.

For example, this works:

SELECT * FROM people WHERE person_id IN (10, 20) ORDER BY FIELD(person_id, 20,
10)

but this does not:

SELECT * FROM people WHERE person_id IN (10, 20) ORDER BY FIELD (person_id,
20, 10)

All just because I put in a space between "FIELD" and the "(".

Commands in mysql4 break in mysql5

Both of the below query statements work exactly the same on mysql4 but the top one will break badly on mysql5 – why? notice the "(" and ")" preceding the join command and following the from command

OLD

$sql="select p.title,p.content,p.date_posted,p.category,p.url,p.submitted_user_id,p.ID,pc.cat_title,u.username,pc.ID as cat_id,count(distinct pv.ID) as vote_count, count(distinct pcom.ID) as comment_count from posts p, post_cats pc, users u left join post_votes pv on pv.post_id=p.ID left join post_comments pcom on pcom.post_id=p.ID where pc.ID=p.category and u.ID=p.submitted_user_id ";

NEW

$sql="select p.title,p.content,p.date_posted,p.category,p.url,p.submitted_user_id,p.ID,p.ip_origin,pc.cat_title,u.username,pc.ID as cat_id,count(distinct pv.ID) as vote_count, count(distinct pcom.ID) as comment_count from (posts p, post_cats pc, users u) left join post_votes pv on pv.post_id=p.ID left join post_comments pcom on pcom.post_id=p.ID where pc.ID=p.category and u.ID=p.submitted_user_id ";

Commands in mysql4 break in mysql5

Both of the below query statements work exactly the same on mysql4 but the top one will break badly on mysql5 – why? notice the "(" and ")" preceding the join command and following the from command

OLD

$sql="select p.title,p.content,p.date_posted,p.category,p.url,p.submitted_user_id,p.ID,pc.cat_title,u.username,pc.ID as cat_id,count(distinct pv.ID) as vote_count, count(distinct pcom.ID) as comment_count from posts p, post_cats pc, users u left join post_votes pv on pv.post_id=p.ID left join post_comments pcom on pcom.post_id=p.ID where pc.ID=p.category and u.ID=p.submitted_user_id ";

NEW

$sql="select p.title,p.content,p.date_posted,p.category,p.url,p.submitted_user_id,p.ID,p.ip_origin,pc.cat_title,u.username,pc.ID as cat_id,count(distinct pv.ID) as vote_count, count(distinct pcom.ID) as comment_count from (posts p, post_cats pc, users u) left join post_votes pv on pv.post_id=p.ID left join post_comments pcom on pcom.post_id=p.ID where pc.ID=p.category and u.ID=p.submitted_user_id ";