MySQL

MySQL (/ˌmaɪˌɛsˌkjuːˈɛl/ "My S-Q-L")[5] is an open source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language. The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by Oracle Corporation. For proprietary use, several paid editions are available, and offer additional functionality.

MySQL is a central component of the LAMP open-source web application software stack (and other "AMP" stacks). LAMP is an acronym for "Linux, Apache, MySQL, Perl/PHP/Python". Applications that use the MySQL database include: TYPO3, MODx, Joomla, WordPress, Simple Machines Forum, phpBB, MyBB, and Drupal. MySQL is also used in many high-profile, large-scale websites, including Google (though not for searches), Facebook, Twitter, Flickr, and YouTube.

Service Control

   mysql  {start|stop|restart|reload|force-reload|status} 

The mysqladmin utility is also used for MySQL management. You can find a list of mysqladmin options by invoking the utility without any arguments:

   mysqladmin

Basic Commands

These command assume you are running them as root. If not, you need to specify a user with '-u user_name -p' Logging In

Log into MySQL prompt as the user:

    mysql -u vnessa5_test -p

Log into MySQL prompt as root:

    mysql

or:

    mysql -u root -p

Importing/Exporting Databases

Importing Databases

phpMyAdmin is unable to import databases larger than 50M unless otherwise set within the settings. You can import large database from the command line so long as you have a sql dump and the username and password for the database on the server.

Import a database as the user (replace username with their username or the username from their database credentials - it will prompt you for the password):

   mysql -p -u username  db_name < file.sql

Importing the database as root:

   mysql db_name < file.sql

You may have to check the first few lines of the file to make sure there are no 'CREATE DATABASE' and 'USE DATABASE' commands in the file and, if there are, remove of comment them out as another database will be created.

Importing multiple databases:

When running this command as root, check the 'CREATE DATABASE' and 'USE DATABASE' commands to ensure the correct database names are being used:

   mysql -u root < file.sql

Warning: this is not recommended for security reasons. If you cannot review the entire content of the file, you will not know if you are running malicious commands! For example, a user could easily add 'drop database user' and remove all users from mysql. In short avoid doing this - import using individual databases.

Exporting a database

Dump a Database (on other servers):

   mysqldump -Q --add-drop-table db_name > file.sql

Dump a Database, 4.0 compatible (works on 4.1 or higher servers):

   mysqldump --compatible=mysql40 --add-drop-table --quote-name db_name > file.sql

Dump Multiple Databases:

   mysqldump -Q --add-drop-table --databases db_name1 db_name2 > file.sql

Managing Databases and Tables

Show Databases: (will only show databases the user has access to. Root has all.)

   show databases;

Drop a whole database:

   drop database databasename;

Create a database: *only the root mysql user can use this command

   create database databasename;

Select a database to work on:

   use databasename;

Drop a specific table:

   drop table wp_options;

Admin Commands

Process Management

Show all MySQL Processes:

   show full processlist;

Kill a process (use 'show full processlist' to get PID)

   kill 9843;

To view various mysql stats

   mysqladmin version

To view current queries

   mysqladmin processlist

User & Privilege Management

Grant superuser privileges. Be careful with this.

   GRANT ALL PRIVILEGES ON *.* TO user_name@localhost;

Grant superuser privileges to a user for just this database.

   GRANT USAGE ON *.* TO 'user'@'host' GRANT Select, Insert, Update, Delete, Create, Drop ON `database`.* TO 'user'@'host' FLUSH PRIVILEGES;

Have privileges take effect without having to restart.

   FLUSH PRIVILEGES;  

List of Privileges:

    CREATE DROP GRANT OPTION REFERENCES EVENT ALTER DELETE INDEX INSERT SELECT UPDATE
    TRIGGER EXECUTE FILE PROCESS PROCESS SUPER
    CREATE VIEW
    SHOW VIEW
    ALTER ROUTINE
    CREATE ROUTINE 
    CREATE TEMPORARY TABLES
    LOCK TABLES
    CREATE USER 
    RELOAD : flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload
    REPLICATION CLIENT
    REPLICATION SLAVE
    SHOW DATABASES

Key Directories, Files, and Scripts

/var/lib/mysql 
   MySQL root folder (contains all databases, named per folder)

/var/lib/mysql/<hostname>.pid

   MySQL process ID file

/var/lib/mysql/<hostname>.err

   MySQL error log

/var/log/slowqueries

   Slow queries log, if enabled in my.cnf

/var/log/mysql*

   Various MySQL logs as specified in my.cnf (varies)

/etc/my.cnf

   MySQL main configuration file

perror

   A Unix utility provided by MySQL to translate error codes

cPanel Scripts

/scripts/fixmysql

   Fixes common MySQL problems (mysql.sock, /var/lib/mysql ownership/perms, stable PID)

/scripts/mysqlup [--force]

   Upgrades/reinstalls MySQL (version specified in /var/cpanel/cpanel.conf)

/scripts/mysqladduserdb

   Adds a MySQL user

/scripts/killmysqluserprivs

   Removed a MySQL user's privileges

/scripts/dropmysqldb

   Deletes a MySQL database

/scripts/grabmysqlprivs

   Shows MySQL user privilege table (warning: not filtered = a lot of results)

/scripts/securemysql

   contains options for securing MySQL

/scripts/suspendmysqlusers

   Suspends a MySQL user

/scripts/unsuspendmysqlusers

   Unsuspends a MySQL user

/scripts/mysqlconnectioncheck

   Checks for MySQL connections, re-establishes mysql.sock

/scripts/updatemysqlquota

   Updates quotas for all MySQL users

Dumping and Restoring Databases

To dump a database use:

   mysqldump -u username -p database1 > dump-lbry.sql

To restore that database:

   mysql -u username -p database1 < dump-lbry.sql

To destroy a database from CLI:

   mysqladmin -u username -p drop database1

To create a database from CLI:

   mysqladmin -u username -p create database1

Perform backup of all mysql databases

   mkdir /root/dbbackups; touch /root/dbbackups/list; for db in $( mysql -e 'show databases' | grep -v "Database\|information_schema" | awk '{print $1}' ) ; do mysqldump --add-drop-table $db > /root/dbbackups $db.sql && echo $db >> list; done

MySQL recovery mode

Start with "1" and go to "3"; try not to go above "3" or you will start to see dropped tables and further corruption. Use above "3" only with a backup.

• echo "innodb_force_recovery = 1" >> /etc/my.cnf; /scripts/restartsrv_mysql ;

Mass MySQL database repair

Repair all MySQL databases:

   mysqlcheck -reA

Repair all MyISAM databases:

   for i in $(find /var/lib/mysql/ -name '*.MYI'); do myisamchk -r -f $i; done

MySQL optimizations

Query Cache

in /etc/my.cnf:

   query_cache_size=16M
   query_cache_type=1

These settings will enable caching for queries except for those that are specifically marked as no caching enabled. See Mysqltuner.pl for adjusting values.

More information is available here: MySql:_my.cnf_settings#Enabling_the_Query_Cache

MySQLtuner.pl

Additionally, a tool exists that can provide further insight into tuning MySQLd: https://github.com/major/MySQLTuner-perl

You can obtain this tool by running:

   wget http://mysqltuner.pl -O mysqltuner.pl

You start the tool by running:

   perl mysqltuner.pl

Be aware that incorrectly changing to recommended setting may cause OoM kills or other undesirable behavior.