MySQL

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout
https://stackoverflow.com/questions/917640/any-way-to-select-without-causing-locking-in-mysql
https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/
http://wagnerbianchi.com/blog/?p=847

MariaDB is a serious contender. It is a drop-in replacement for MySQL. And Percona's Xtradb (included in MariaDB) is a drop-in replacement for InnoDB
25+ Alternative & Open Source Database Engines
http://www.theregister.co.uk/2013/09/12/google_mariadb_mysql_migration/
NoSQL

Beginning MySQL Database Design and Optimization - Jon Stephens and Chad Russell

Tools
Articles
Videos
Resources
Questions

CREATE TABLE
ALTER TABLE
DROP TABLE
RENAME TABLE
TRUNCATE TABLE

SELECT
WHERE vs HAVING
SUB-QUERIES
Using EXPLAIN
Optimizations
JOINS (SIMPLE JOIN, STANDARD JOIN, INNER JOIN, CROSS JOIN, SELF JOIN, NATURAL JOIN, OUTER JOIN)
UNION / Compound Queries
Normalization
Interesting SQL
Temporary Tables
INSERT
REPLACE
Differences between REPLACE and ON DUPLICATE
UPDATE
DELETE
Multi-table UPDATE and DELETE

Indexes and performance
CREATE INDEX
DROP INDEX
Foreign keys constraints

Prepared statement

Date/Time functions

Troubleshooting and debugging

VIEWS
STORED PROCEDURES / FUNCTIONS
Triggers
Functions
Transaction

OLTP vs OLAP
Full Text Search

Best practices
Configurations, Installation, and Administration
Replication
Purge Binary Log Files
Grant / Revoke Privileges
Variables
User variables
Character Set and Collation
Locking
Obtaining metadata
Large and Complex Table Structure Changes and Maintenance
Comments
max_connections
Per-Client Parameters
Server and Environment
SQL modes
Loading time zone tables

Storage engines (INNODB, MyISAM, MEMORY, NDB)
Choosing appropriate storage engine
MyISAM
InnoDB
MERGE
MEMORY

Table Cache
Thread Cache
Query Cache

Importing and Exporting Data
Dump and reload
Backup and Recovery

SHOW
The mysql client
mysqlshow
mysqldumpslow
mysqladmin

JSON
Miscellaneous
Questions
Questions and Answers
scenarios
Determining and estimating the size of the database

Miscellaneous
Miscellaneous Performance
Performance monitoring
Trivia

How can we show information about a table?

Use the DESCRIBE statement:

DESCRIBE tableName;

What is the purpose of the mysql_insert_id() function?

mysql_insert_id() returns the number assigned to the last inserted entry by the auto_increment feature in MySQL.

What Perl modules do we need to install in order to use Perl with MySQL?

  1. Data-Dumper
  2. DBI
  3. Msql-Mysql

How can we connect to the default MySQL database?

The name of the default database is mysql. MySQL uses this database to store the users and other data. To connect to the default database using the command line client:

mysql -u root -p mysql

How can we run a MySQL query from the OS shell?

mysql -u username -p -h IP -P port -D mon  
    -e "select ip,environment from machines where environment='production'" 
    -s -s
    dbName

select -e "select host,db,user from db" mysql;

How can we recover the root's password?

  1. Shutdown the server using: kill <pid> (Do not use kill -9 unless absolutely have to)
  2. Restart the server using: safe_mysqld —skip-grant-tables
  3. Change root's password:
    1. mysql -u root **
    2. use mysql
    3. UPDATE user SET Password=PASSWORD("new password") WHERE user="root"
    4. quit
    5. mysqladmin flush-privileges**
  4. Instruct MySQL server to reload the grant table to begin requiring password. Just to be safe, shutdown the MySQL service, and restart the MySQL service.

How can we instruct MySQL to generate an update log?

safe_mysqld --log-update=update

The above command instruct MySQL to keep an update log, stored on the server's data directory (/usr/local/mysql/var).

How can we apply the update log?

mysql -u root -ppassword --one-database dbName < updateLogFile.100

What is the purpose of the update log and how can we use it?

I NEED TO REVIEW THIS.

What is the purpose of the 'flush status' command?

NOT SURE.

How can we create an empty database?

CREATE DATABASE [IF NOT EXISTS] dbName [CHARACTER SET utf8] [COLLATE utf8_danish_ci]

The IF NOT EXISTS clause can be useful in applications that needs to ensure that a given database is available, without disrupting any existing database with the same name.

The CREATE DATABASE statement has two optional clauses, CHARACTER SET and COLLATE. These settings are stored in db.opt in the database directory.

How can we use the ALTER DATABASE statement?

ALTER DATABASE dbName CHARACTER SET latin1 COLLATE latin1_swedish_ci

Changing the default character set or collation affects only creation of new tables. It does not affect existing tables. The database name is optional. If no database is specified, then ALTER DATABASE use the default (currently selected) database. You cannot use ALTER TABLE to rename a database. One way to accomplish this is to dump the database, create a database with the new name, reload the data into the new database, and drop the old database.

How can we rename a database?

You cannot use ALTER TABLE to rename a database. One way to accomplish this is to dump the database, create a database with the new name, reload the data into the new database, and drop the old database.

How can we drop a database?

DROP DATABASE IF EXISTS dbName;

DROP DATABASE does not require the database to be empty. Before dropping the database, MySQL removes any objects that it contains, such as tables, stored routines, and triggers.

How can we display a list of databases that reside on the server?

SHOW DATABASES;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License