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
SHOW INDEX FROM yourtable; SHOW INDEX FROM mytable FROM mydb; SHOW INDEX FROM mydb.mytable; SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_schema';
WHERE vs HAVING
JOINS (SIMPLE JOIN, STANDARD JOIN, INNER JOIN, CROSS JOIN, SELF JOIN, NATURAL JOIN, OUTER JOIN)
UNION / Compound Queries
Differences between REPLACE and ON DUPLICATE
Multi-table UPDATE and DELETE
Configurations, Installation, and Administration
Purge Binary Log Files
Grant / Revoke Privileges
Character Set and Collation
Complex Table Structure Changes
Server and Environment
Loading time zone tables
How can we show information about a table?
Use the DESCRIBE statement:
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?
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?
- Shutdown the server using: kill <pid> (Do not use kill -9 unless absolutely have to)
- Restart the server using: safe_mysqld —skip-grant-tables
- Change root's password:
- mysql -u root **
- use mysql
- UPDATE user SET Password=PASSWORD("new password") WHERE user="root"
- mysqladmin flush-privileges**
- 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?
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?
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?