MySQL

MySQL (/ˌmaɪˌɛsˌkjuːˈɛl/ "My S-Q-L") 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

Really dumb way to do it several times:

   mysqlcheck -reA && for db in $(for i in `ls /var/cpanel/users | grep -v "./"`; do /opt/dedrads/lil-cpanel $i mysql list dbs; done); do mysqlcheck -r $db; mysqlcheck -o $db; done && 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.

Troubleshooting

InnoDB Crashes

InnoDB crashes can be very complex to resolve.

First, try setting InnoDB to recovery mode. Edit /etc/my.cnf and add:

   innodb_force_recovery = 1

Then restart MySQL. If it does not started up, keep increasing the recovery level up to 3 until it does start. If it doesn't start at this point, get a developer.

Once it does start, reverse the change you made and restart again normally. If InnoDB does not initialize after doing this, you may be dealing with a more complex issue that might require re-importing InnoDB data.

Root Login Failure

If this is a cPanel server, simply run:

   /scripts/mysqlpass root 'password'

Otherwise:

Add this line to /etc/my.cnf and restart MySQL:

   skip-grant-tables

Follow these steps to reset the MySQL root password:

   mysql -u root
   mysql> FLUSH PRIVILEGES;
   mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'ourmysqlpassword' WITH GRANT OPTION;
   mysql> FLUSH PRIVILEGES;
   mysql> exit;

   service mysql restart

Remove the line you added to my.cnf and restart again. 

Disable innodb on MySQL 5.6

If you're using MySQL 5.6+ and want to disable InnoDB, don't forget "--default-tmp-storage" or it won't work.

To disable InnoDB, use --innodb=OFF or --skip-innodb. In this case, because the default storage engine is InnoDB, the server will not start unless you also use --default-storage-engine and --default-tmp-storage-engine to set the default to some other engine for both permanent and TEMPORARY tables.

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#option_mysqld_ignore-builtin-innodb

You can add this to your my.cnf:

   [mysqld]
   innodb=OFF
   ignore-builtin-innodb
   skip-innodb
   default-storage-engine=myisam
   default-tmp-storage-engine=myisam

Missing libmysqlclient.so.14

error while loading shared libraries: libmysqlclient.so.14: cannot open shared object file: No such file or directory

Run this command:

   cp /usr/lib/mysql/libmysqlclient.so.14 /usr/lib

InnoDB Errors

If you see the following error while attempting to start (or restart) MySQL

   InnoDB: corruption in the InnoDB tablespace. Please refer to
   InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
   InnoDB: about forcing recovery.

Add the following line to my.cnf:

   innodb_force_recovery = 4

If you see a large number of databases with the following error:

   Incorrect information in file: './database/table.frm'

It means the innodb engine has locked up and crashed. You need to comment out the innodb lines in /etc/my.cnf, restart mysql, uncomment the innodb lines again, restart mysql.

Slow queries

The slow query parser tool can be used to parse the slow query log. Running 'slowqueryparser.py' by itself will return a list sorted by number of queries and including total time, total lock time, total rows sent, and total rows received. It is recommended that you pipe only the last 50k lines to this script, since the slow query log is infrequently rotated:

   tail -50000 /var/log/slowqueries | slowqueryparser.py

As with the general query parser, per-user output can be extracted with the '--user=' option:

   tail -50000 /var/log/slowqueries | slowqueryparser.py --user=username --output=/home/username/slowqueries.log

Full help output:

   usage: slowqueryparser.py [options] [filename]

[filename] is optional and defaults to stdin.

   options:
    -h, --help            show this help message and exit
    -v, --verbose         Print info on stderr (default: True)
    -q, --quiet           Suppress stderr output
    -o FILE, --output=FILE
                          Write output to FILE (default: stdout)
    -u USER, --user=USER  Output USER's queries instead of tallys
    -a, --average         Print averages per query instead of totals

mysqldumpslow parses MySQL slow query log files and prints a summary of their contents. Normally, mysqldumpslow groups queries that are similar except for the particular values of number and string data values. It abstracts these values to N and S when displaying summary output. The -a and -n options can be used to modify value abstracting behaviour.

   usage: mysqldumpslow <path to slowquerylog>
   options:
     -a                Do not abstract all numbers to N and strings to ´S´.
     -g pattern        Consider only queries that match the (grep-style) pattern.
     -l                Do not subtract lock time from total time.
     -n N              Abstract numbers with at least N digits within names.
     -s sort_type      How to sort the output. The value of sort_type should be chosen from the following list:
             ·   t, at: Sort by query time or average query time
             ·   l, al: Sort by lock time or average lock time
             ·   r, ar: Sort by rows sent or average rows sent
             ·   c: Sort by count
                 By default, mysqldumpslow sorts by average query time (equivalent to -s at).

To parse the default slow query log:

   mysqldumpslow /var/log/slowqueries 

You can also pass data to it via stdin by passing '-' as the log name. You may also find it beneficial to use this tool in tandem with the slowqueryparser to provide the customer with an easy to understand report of their slow query activity:

   slowqueryparser.py -u username | mysqldumpslow - > ~username/slow-query-report.txt